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?
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_statscollects information about the query execution. the queries with top values in
total_worker_timeare the ones that are consuming most CPU. Cross apply
sys.dm_exec_sql_textto retrieve the query text.
sys.dm_exec_procedure_statssimilar to query stats, but for procedures. Only in SQL Server 2008.
sys.dm_db_index_usage_statscollects information on how tables and indexes are accessed. Look at the
user_lookupsto see how often and on what way is the table read. Look at
user_updatesto see how often it is written into.
sys.dm_db_index_operational_statscollects information about index access contention, among other things. Look at the various
xxx_wait_countcolumns 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
sys.dm_db_index_usage_statsfor indexes with many records, large numbers for
- Table Value Functions that are invoked too often in execution. This can be identified by having very large
execution_countin 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.
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.