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?

closed as too broad by LowlyDBA, Erik Darling, Mr.Brownstone, hot2use, McNets Jan 31 '18 at 9:05

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

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/

  • 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.

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