Sql-server – Catching all Server with SQL Server (any Version) installed using SSCM

Recently an article was published about this issue on Microsoft's KB: Microsoft Query lists user objects and system views in the query wizard.

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 in total_worker_time are the ones that are consuming most CPU. Cross apply sys.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 the user_seeks, user_scans, user_lookups to see how often and on what way is the table read. Look at user_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 various xxx_wait_ms and xxx_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 on sys.dm_db_index_usage_stats for indexes with many records, large numbers for total_physical_reads in sys.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!