Is there any quick way to find out what site on a server would be using all the CPU with a SQL query? As randomly the CPU is just sitting as 100% and I don't know why?
-
Why would it not? If the SQL server needs the CPU to process your quer it should grab it all, if it can. To give a meaningful answer to your question, we'd need to see the SQL query in you are asking about.– Neil ButterworthAug 22, 2009 at 15:34
-
Someone/You may consider tagging it to 'performance' as well– M.NAug 24, 2009 at 7:46
4 Answers
It would definitely help knowing the version of SQL Server you're using.
I'll consider you use SQL Server 2005 as is the most common these days. To identify the cause of the problem I recommend you download the Performance Dashboard reports. After you installed the custom reports and run then deployment script as detailed in Additional Information section of the download page, you open the custom performance_dashboard_mail.rdl
report in SQL Server Management Studio. From there you have links to reports on CPU utilization that will show the total time consumed by each query in your system. The topmost queries in these reports are the ones most likely to drive the system to 100% CPU. You'll have to analyze the execution plan for those queries and identify why they are so expensive. The SSMS plan visualizer goes a long way into helping this, just follow the thick lines in the plan because they represent large data flows. At the end of these expensive data flows you'll most likely find Clustered Index Scan operators that return all the rows in the table. You'll need to understand the query and the table structure to decide why is a clustered index scan choose, and you'll likely have to decide on adding an index or two. The Performance Dashboard can again help, as it actually has reports that leverage the somehow esoteric info from sys.dm_db_missing_index_details
and present it in a easy readable format, more precisely it shows the suggested CREATE INDEX
statement.
Other resources of interest are:
sys.dm_exec_query_stats
collects information about the query execution. the queries with top values intotal_worker_time
are the ones that are consuming most CPU. Cross applysys.dm_exec_sql_text
to retrieve the query text.sys.dm_exec_procedure_stats
similar to query stats, but for procedures. Only in SQL Server 2008.sys.dm_db_index_usage_stats
collects information on how tables and indexes are accessed. Look at theuser_seeks
,user_scans
,user_lookups
to see how often and on what way is the table read. Look atuser_updates
to see how often it is written into.sys.dm_db_index_operational_stats
collects information about index access contention, among other things. Look at the variousxxx_wait_ms
andxxx_wait_count
columns to understand where contention occurs. Since you have the CPU at 100% is unlikely though that the problem is contention, but I have to present this information anyway for other readers going over this.
With this investigations completed, you should be able to identify the problem:
- Table scans. This would show up as queries in the Performance Dashboard that are executed relatively few times, but they last long time each execution. Other symptoms are high
user_scans
count onsys.dm_db_index_usage_stats
for indexes with many records, large numbers fortotal_physical_reads
insys.dm_exec_query_stats
. - Table Value Functions that are invoked too often in execution. This can be identified by having very large
execution_count
in the stats view and in Performance Dashboards. Typical examples are functions to split a comma delimited string into a relational representation (if I'd have a dime for each time...). - Spooling and Sorting. I won't go into details, first do the homework and investigate the simple scenarios above.
Success!
There is an Activity Monitor in Microsoft SQL Management Studio that will show the most expensive queries and their cpu time. Just right click on the server in the server explorer window and click "Activity Monitor"
You should run SQL Server Profiler to help you determine where there are some inefficient queries that are causing large read/write times.
As for quick... Well, you could run it for a minute or two, and that should give you an indication of whether there are some SPs/queries that are being run more than expected.
Please also read this question, and its accepted answer.
If SQL Server Profiler won't help, you may start to think about any possible worm attack.