Microsoft SQL Server 2008 R2 Unleashed- P155:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1484 CHAPTER 39 Monitoring SQL Server Performance FIGURE Viewing CPU utilization in the Utility Explorer. SELECT top 10 spid lastwaittype dbid uid cpu physical_io memusage status loginame program_name from ORDER BY cpu desc This statement lists the top 10 CPU resource hogs active in SQL Server. After you identify which processes are causing a burden on the CPU you can check whether they can be either turned off or moved to a different server. If they cannot be turned off or moved you might want to consider upgrading the processor. The same information is available via the new DMV SELECT top 10 session_id command database_id user_id cpu_time reads writes logical_reads from order by cpu_time desc Taking a peek at the SQL Server schedulers using the DMV also shows whether the number of runnable tasks is getting bogged down. If the runnable_tasks_count values are nonzero there aren t enough CPU time slices available to run the current SQL Server workload. The following example shows how you query the dm_os_schedulers view for this information SELECT scheduler_id current_tasks_count runnable_tasks_count FROM And finally to get an idea of the top CPU hogs in SQL Server cached SQL and stored procedures you can query the DMV and aggregate on total_worker_time to get the total CPU consumption as follows A Performance Monitoring Approach 1485 SELECT top 50 sum total_worker_time as Total_CPU sum execution_count as Total_Count count as Total_Statements plan_handle FROM GROUP BY plan_handle Order by 1 desc GO To actually see the SQL code that is behind the plan_handle you can execute the dm_exec_sql_text function to get your hog list SELECT total_worker_time FROM A CROSS APPLY AS B order by 1 desc Monitoring Memory Memory like a processor is divided into segments for each process .
