Best Practices for Designing for Performance from the Start - Professional SQL Server 2005 Performance Tuning - page 518

, row_lock_wait_ms = row_lock_wait_in_ms
, [avg_row_lock_waits_ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_
wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) s
,sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
And row_lock_wait_count <> 0
END

MonitoringHighCPUUsage
To tune a system for CPU usage, you need to have an idea of which database objects are creating the most
contention for CPU resources. You can find the total amount of CPU time and the number of executions
of any database object in the DMV sys.dm_exec_query_stats. To make this data useful, you'll need to
aggregate the statistics by object and store them historically. Here is the TSQL that can be run to store
this information periodically.

CREATE PROC usp_StoreObjectCPUHistory
/*STORE CPU USAGE HISTORY
=======================================================*/

AS
BEGIN
INSERT INTO Benchmark_Object_CPUHistory
SELECT

objectid
, getdate() As SampleDateTime
, total_cpu_time
, total_execution_count
, number_of_statements

FROM (
SELECT

qs.sql_handle
, sum(qs.total_worker_time) as total_cpu_time
, sum(qs.execution_count) as total_execution_count
, count(*) as number_of_statements

FROM

sys.dm_exec_query_stats qs
GROUP BY qs.sql_handle ) dt
Cross Apply sys.dm_exec_sql_text(dt.sql_handle) eqp
WHERE ObjectId is not null
END

High CPU usage can be caused by many different design decisions as well as external influences like
hardware configurations. Some of the things to look for to reduce CPU usage are:

❑ Aggregate operations: Look for high-cost Stream Aggregate operators in query execution plans.

❑ Intensive calculations: Look for TSQL operations that have intermediate calculations that use

Stream Aggregate operators or string parsing activity

Table variables: Look for instances where too many rows are being inserted into table variables.
Use #temp tables instead.

❑