Sql-server – All queries on the server have slowed down, cpu is normal, plenty of free memory but SQL is using very little

sql-server-2012

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?

Best Answer

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.
Related Question