0

I have a Sql Server that runs stored procedures slowly sometimes. I’d assume it was a problem with the contents of the stored procedures, but sometimes it’s slow to update stored procedures.

Where does one start?

2

I've run into this several times: new server, hand-me-down, etc. etc.

I always start with the First Responder Kit from the BrentOzar team. It's always allowed me to quickly diagnose issues and take preventative measures to mitigate future issues.

https://www.brentozar.com/first-aid/

| improve this answer | |
  • Does reinstalling work? – Ian Warburton Jan 31 '18 at 12:53
  • Reinstalling SQL Server? Not in my experience. SQL Server is not natively slow. It's slow because something is slowing it down. That issue will come back if you reinstall. best to get to the source of the problem, and the First Responder kit should do that for you. – MguerraTorres Feb 2 '18 at 0:52
  • I think it was because my Parallels Windows instance didn't have enough memory. I've taken another 300Mb from Mac OS and it seems to have fixed it. There were several memory notifications regarding SQL Server in the Windows log. It's a shame my Mac only has 4Gb of memory. – Ian Warburton Feb 2 '18 at 16:47
  • 1
    Oh wow. Yeah 4GB for MacOS, a windows box via parallels, and a sql server instance is going to be very rough for you. Check out this article on memory usage for more info: brentozar.com/archive/2011/09/… – MguerraTorres Feb 2 '18 at 16:52
2

First Responder Kit is excellent. You could also take a quick look with SQL Profiler, watch the CPU, Reads and Writes, it may point you in the right direction. You mentioned "stored procedures slowly sometimes" , you could schedule Profile to run during this period and insert the results into db table for analysis later.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.