3

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.

|improve this question

migrated from stackoverflow.com Oct 18 '13 at 1:09

This question came from our site for professional and enthusiast programmers.

4

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.

|improve this answer
  • In that first question, I was referring to queries that are repeatedly recompiled (ex. procs that use temp tables). Thank you for sp_whoisactive. I discovered the issue was a missing index on a column. The query, although by itself quick, was being called thousands of times in a short timespan - each time doing a table scan for just one record in the result. – Gabe Nov 4 '13 at 20:08
  • If you want to find the procedures that use temp tables you can always search through sys.sql_modules. You would filter on the definition column. It is not the prettiest way of doing it but it works. technet.microsoft.com/en-us/library/ms175081.aspx – Shawn Melton Nov 4 '13 at 20:53
  • Another option that I use if I can install it on the server/client I am using is RedGate's SQL Search. Free tool and very useful if you are dealing with large database (object count): red-gate.com/products/sql-development/sql-search – Shawn Melton Nov 4 '13 at 20:54
3

I would use select * from sys.dm_exec_requests to see what requests that is executed against the databases. You will see wait types, wait resources, etc. It will give you more info then sp_who2. High CPU usage can be caused by a lot of reasons like:

  • Bad execution plans caused by bad statistics
  • Locks and blocks
  • DBCC CHECKDB
  • Index rebuild
  • etc
|improve this answer

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.