Sql-server – High cpu usage on SQL Server

sql-server-2008

We have a customer server containing SQL Server 2008R2 with about 50 databases. The issue is that the CPU usage reaches 100% (or close to it) and it lasts for a couple hours. This typically happens in the morning (usually somewhere around 6am – 9am). Average CPU usage is 30%

We looked at the scheduled reports and couldn't find anything – we did decide to schedule them earlier in the morning and that didn't do anything.

I'm going to remote in tomorrow to investigate the issue. We've rebuilt the indexes and that didn't help. I have a set of DMVs to check for queries with highest worker time and longest running time.

Do the SQL Server track all the queries run so that the DMVs can access the information? What other sql snippets would be helpful to diagnose the problem?

edit: Memory usage is pretty high too – about 80-90%. Actually another server is experiencing the same issue at the same time, I believe they're both hosted on a citrix server.

We've also done an index rebuild.

Best Answer

Do the SQL Server track all the queries run so that the DMVs can access the information?

I would suggest taking a look at a free e-book Red Gate published here: Performance Tuning with SQL Server Dynamic Management Views. It is free and a good read. It will point out those DMVs that provide cumulative data versus point-in-time (what is currently in the buffer). As long as the server has not been rebooted you are in good shape to capture some good information.

What other sql snippets would be helpful to diagnose the problem?

You can also use sp_whoisactive that will provide much more information if you can run it during the hours noted. Returns CPU, Memory, and I/O usage of each query. You can even have it pull the execution plan of the offending query as well.

Do a basic Bing/Google search for SQL Server DMV and high CPU queries or something will likely bring up some other good scripts/blog post on researching your particular situation.

Just a note on your comment regarding moving the scheduled reports, I would also look at ad-hoc reports on those that users may run on their own. Especially if you are running SSRS on the same server as the database engine. I usually take a look at the ExecutionLog table in the SSRS database (default name ReportServer). There is a wealth of information in there on execution time and rendering time of the reports.

Related Question