0

The other day I logged onto our server to see SQL was using 100% cpu and the server was slowed to a crawl. I decided to stop SQL and restart it. However, it refused to stop properly and stopped responding. After playing around with it eventually I got it restarted and running again.

However, all queries against it are now going 10-30 times slower, and in some cases affect the performance of the server.

I've been googling for solutions but I can't see anything addressing a global issue with SQL running slow. The CPU usage isn't abnormal and there is plenty of free ram - in fact, SQL is using only about 300mb whereas previously it used to be around 20gb, which may be part of the issue.

I created a brand new database and when I attempt to populate it, it goes about 30 times slower than it used to, and the server becomes very unresponsive.

Could it be something simple that needs resetting or a repair that needs doing after it broke before?

2

When you're trying to find out why a SQL Server is slow, start by checking these things:

  1. In Task Manager, is the CPU consumer actually sqlserver.exe, or something else? If it's something else, troubleshoot that first.
  2. If it's actually sqlserver.exe, check what queries are running right now. The easiest way is with Adam Machanic's excellent sp_WhoIsActive. Here's how to run it.
  3. Check the server's wait stats. My favorite way to do this is with sp_AskBrent, my stored proc that takes a 5-second sample of wait stats plus checks some diagnostic queries.

For more detail, check out these resources:

  • Our First Responder Kit - a PDF checklist of things to look at
  • The free book Troubleshooting SQL Server - I like this one because it's a problem/solution approach. You don't have to read the whole thing to get value out of it - just put it on your virtual shelf and when you've got a CPU problem, turn to the CPU chapter.
  • In general, SQL server uses 0% cpu unless I try to do a heavy operation. It uses 300mb of memory though which is ludicrously low. – NibblyPig Jul 4 '14 at 15:38
  • 1
    It only uses the memory that is required for your queries. What's the root problem that you're trying to solve? Low memory usage isn't a problem end users complain about. Do the heavy operation, and then do the troubleshooting while it's running. – Brent Ozar Jul 4 '14 at 15:41
  • What, @BrentOzar - no Independence Day celebrating for you? Great answer, by the way! – Max Vernon Jul 4 '14 at 16:16
  • 1
    @SLC Where exactly are you validating that SQL Server is only using 300 MB of memory? If you're using Task Manager, stop doing that. If you're using performance monitor / resource monitor / sysinternals tools or something else that has some hope of actually being accurate, then next question: how big is your database? How big are the tables you've queried so far? SQL Server will only use the memory it needs, and if it doesn't need it, like Brent said, this isn't something end users complain about. – Aaron Bertrand Jul 4 '14 at 17:41
  • 2
    Interesting. Looking forward to seeing those troubleshooting results. – Brent Ozar Jul 5 '14 at 22:23
0

I would suggest not to restart SQL server because in this way you would loose all collected stats and after restart you wont be able to find out what was causing slowness using DMV. I would ask how often you update stats, rebuild indexes. Did you tried finding out what was causing slowness. Don't just hit arrow in the bush first drill down what is actually the bottleneck is it poor query or index or may be resource crunch. Regarding 300MB memory in task manager, you should not refer to task manager for SQL server memory usage it would not show you correct value if SQL Server service account has Locked pages in memory privilege. Below is Whitepaper on troubleshooting performance problem in SQL Server http://technet.microsoft.com/en-us/library/dd672789%28v=sql.100%29.aspx

I would suggest you to look at wait stats and analyze it.It will give you clear picture about whats causing bottlenck

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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