Database Reference
In-Depth Information
Lock Timeouts/Sec and Lock Wait Time (Ms)
You should expect Lock Timeouts/sec to be 0 and Lock Wait Time (ms) to be very low. A nonzero value for Lock
Timeouts/sec and a high value for Lock Wait Time (ms) indicate that excessive blocking is occurring in the database.
Two approaches can be adopted in this case.
•
You can identify the costly queries currently in cache using data from SQL Profiler or by
querying sys. dm_exec_query_stats, and then you can optimize the queries appropriately.
•
You can use blocking analysis to diagnose the cause of excessive blocking. It is usually
advantageous to concentrate on optimizing the costly queries first because this, in turn,
reduces blocking for others. In Chapter 20, you will learn how to analyze and resolve blocking.
•
Extended Events supply a blocking event called blocked_process_report that you can enable
and set a threshold in order to capture blocking information. Extended Events will be covered
in Chapter 6, and blocked_process_report will be addressed in Chapter 20.
Just remember that some degree of locks are a necessary part of the system. You'll want to establish a baseline in
order to track thoroughly whether a given value is cause for concern.
Number of Deadlocks/Sec
You should expect to see a 0 value for this counter. If you find a nonzero value, then you should identify the victimized
request and either resubmit the database request automatically or suggest that the user do so. More importantly, an
attempt should be made to troubleshoot and resolve the deadlock. Chapter 21 shows how to do this.
Nonreusable Execution Plans
Since generating an execution plan for a stored procedure query requires CPU cycles, you can reduce the stress on the
CPU by reusing the execution plan. To analyze the number of stored procedures that are recompiling, you can look at
the counter in Table
4-6
.
Table 4-6.
Performance Monitor Counter to Analyze Execution Plan Reusability
Object(lnstance[,lnstanceN])
Counter
SQLServer:SOL Statistics
SOL Re-Compilations/sec
Recompilations of stored procedures add overhead on the processor. You want to see a value as close to 0 as
possible for the SOL Re-Compilations/sec counter, but you won't ever see that. If you consistently see values that
deviate from your baseline measures or spike wildly, then you should use Extended Events to further investigate the
stored procedures undergoing recompilations. Once you identify the relevant stored procedures, you should attempt
to analyze and resolve the cause of recompilations. In Chapter 17, you will learn how to analyze and resolve various
causes of recompilation.
General Behavior
SQL Server provides additional performance counters to track some general aspects of a SQL Server system. Table
4-7
lists a few of the most commonly used counters.
