Sql server 2005 Waits and Queues
|
|  Yüklə 0.86 Mb.
|
Query optimizer, query plans and statisticsThe SQL Server 2005 Query Optimizer compiles a query plan for a user’s query. It is the job of the SQL Server Query Optimizer to determine the lowest cost strategy that will be used to retrieve or modify the data. The query plan contains the strategy or series of steps to be executed in the query plan. For more information about SQL Server 2005 Query Optimizer, Query plans, and Statistics refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx. Query plan reuse and DMVsHigh query plan reuse is important for OLTP applications where there are many identical transactions. The advantage of plan reuse means you will not incur the CPU cost of optimization for each execution of the same plan. The statements with the lowest plan reuse can be found using DMVs as follows: --- DMV reports statements with lowest plan reuse --- SELECT TOP 50 qs.sql_handle ,qs.plan_handle ,cp.cacheobjtype ,cp.usecounts ,cp.size_in_bytes ,qs.statement_start_offset ,qs.statement_end_offset ,qt.dbid
,qt.objectid ,qt.text
,SUBSTRING(qt.text,qs.statement_start_offset/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end -qs.statement_start_offset)/2) as statement FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle where cp.plan_handle=qs.plan_handle
ORDER BY [Usecounts] ASC Query plan reuse and Performance countersThe Perfmon object SQL Server:SQL Statistics contains counters that can be used to compute plan reuse. The idea is to compare batch requests to initial compilations. An initial compilation occurs when a plan is not found in cache. OLTP applications should have high plan reuse, > 90%. Initial Compilations = SQL Compilations/sec – SQL Re-Compilations/sec Plan reuse = (Batch requests/sec – Initial Compilations/sec) / Batch requests/sec Memory pressure can cause query plans to be discarded and therefore result in reduced plan reuse. See OLTP Blueprints for memory pressure. Statement level recompilation and DMVsIn SQL Server 2005, individual statements inside a stored procedure can be recompiled. The recompilation feature can be beneficial. For example, assume that you have a stored procedure that creates a table called MyTable, populates the table, and then joins MyTable to other tables. Because these operations occur AFTER the initial compile, the final row size and rowcount of MyTable is not known until run time. It would be possible that when MyTable is joined, it can contain 1 million rows. SQL Server tracks the statistics of MyTable and recompiles the join statement to take advantage of the new statistics for MyTable. For more information about SQL Server 2005 Optimizer and Statistics refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx Recompilation is not always a good idea; for example, when the recomplied plan is the same as the orginal plan. In these cases, you will want to identify the recompiled statements. For more information about SQL Server 2005 recompilation refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx. The following script returns statements that have been recompiled. ---- Recompilation and SQL.sql ---- (plan_generation_num) and sql statements ---- A statement has been recompiled WHEN the plan generation number is incremented ---- select top 25 --sql_text.text, sql_handle, plan_generation_num, substring(text,qs.statement_start_offset/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), text)) * 2 else qs.statement_end_offset end - qs.statement_start_offset)/2) as stmt_executing, execution_count, dbid,
objectid from sys.dm_exec_query_stats as qs Cross apply sys.dm_exec_sql_text(sql_handle) sql_text where plan_generation_num >1 order by sql_handle, plan_generation_num
Tracking down blocking issues.Long blocks can be trapped with Profiler and sp_configure. The blocked process threshold, set with sp_configure “blocked process threshold”, is the mechanism for reporting any blocks that exceed this configured number of seconds. Make sure that the threshold is not set too low as it can capture false positives. After setting the blocked process threshold using sp_configure, Profiler is then used to capture the blocker and blocked statements using the Errors and Warnings object, Blocked Process Report event. In order to see the main objects of blocking contention, the following code lists the table and index with most blocks: ----Find Row lock waits declare @dbid int select @dbid = db_id() Select dbid=database_id, objectname=object_name(s.object_id) , indexname=i.name, i.index_id --, partition_number , row_lock_count, row_lock_wait_count , [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2)) , row_lock_wait_in_ms , [avg row lock waits in 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 (@dbid, 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 order by row_lock_wait_count desc Notice the average block time reported in the above script is in milliseconds. You must convert the average block time to seconds in order to set the ‘blocked process threshold’ with sp_configure. This should give you a hint on how to set the sp_configure ‘blocked process threshold’ if you are not sure where to start. Remember not to set the blocked process threshold (seconds) too low as this will generate false positives. The blocked process threshold fires a trace event (Blocked Process Report) for any block that exceeds the configured number of seconds.
|