Sims (SQL) performance issues..

[SpaceInvader83]

Hi, our Sims server has been working great for the past year and as quick as we could hope for but the past 2 weeks we have been suffering random slow downs where the Sims server gets bogged down completely. If you give it 5 minutes it gets going again and all seems normal, I can see in activity monitor that tasks are queued up hence the bogging down but i'm struggling to find exactly what is causing the slow downs, any advice?

 

The trouble is not only do we have Sims, we have Insight, Pars and Datasec which all pull data off the Sims server so I wouldn't be surprised if it's one of these that's killing the server, any help greatly appreciated!

 

Thanks,

 

Simon

[3s-gtech]

What's the full spec of the server? And what version of SQL Server do you use?

[SHimmer45]

have you recently been migrated to SQL2012/2014?

[SpaceInvader83]

Server 2008r2, SQL 2012 standard, 8 core @ 2.49 GHz , 10GB ram, storage is HP Left Hand SAN - my main thinking is it could do with more memory but it's been fine up until the past 2 weeks ,maybe it's just hit that point of late.

 

The server hasn't changed for getting on for 18 months, no change to SQL version etc.

Edited January 29, 2015 by SpaceInvader83
update

[PhilNeal]

It has to be something heavy duty being called for a period - possibly a sync to other systems.

[SpaceInvader83]

Agreed Phil that's my thinking aswell, is there a good way to trace what is causing the heavy load? I thought Recent Expensive Queries might help but because it just shows the actually query i'm struggling to track down where this has come from.

[localzuk]

In my experience, the biggest culprit for this sort of slow down has been a malformed CTF file import.

[zag]

Give MSSQL profiler a go

 

Edited January 29, 2015 by zag