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?
Sql-server – SQL Server Using 100% CPU
sqlsql server
Related Topic
- Sql-server – VMware + SQL Server – sqlserver.exe not using both CPU cores
- Sql-server – SQL Server 2008 CPU usage goes to 100% – troubleshooting help needed
- Sql-server – Unexplained CPU and Disk activity spikes in SQL Server 2005
- Sql-server – Catching all Server with SQL Server (any Version) installed using SSCM
Best Answer
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 fromsys.dm_db_missing_index_details
and present it in a easy readable format, more precisely it shows the suggestedCREATE 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:
user_scans
count onsys.dm_db_index_usage_stats
for indexes with many records, large numbers fortotal_physical_reads
insys.dm_exec_query_stats
.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...).Success!