with [Waits] as (select [wait_type], [wait_time_m S]/1000.0 as [waits], ([Wait_time_ms]–[signal_wait_time_ms])/1000.0 as [resources],  ; [Signal_wait_time_ms]/1000.0 as [signals], [Waiting_tasks_count] as [Wai tcount], 100.0 * [Wait_time_ms]/SUM ([Wait_time_ms]) over () as [percentage], &N Bsp Row_number () over (ORDER by [Wait_time_ms] DESC) as [rownum] from sys.dm_os_wait_stats WH ERE [Wait_type] not in ( N ' Clr_semaphore ', n ' lazywriter_sleep ',   ; n ' resource_queue ', n ' sqltrace_buffer_flush ', n ' sleep_task ', n ' sleep_systemtask ', N ' WAITFOR ', n ' HADR_FILESTREAM_ Iomgr_iocompletion ', n ' checkpoint_queue ', n ' request_for_deadlock_search ', N ' xe_timer_event ' , n ' xe_dispatcher_join ', n ' logmgr_queue ', n ' ft_ifts_scheduler_idle_wait ', n ' broker_task_stop ', n ' clr_manual_event ', N ' Clr_auto_event ', & nbsp N ' dispatcher_queue_semaphore ', n ' tracewrite ', n ' xe_dispatcher_wait ', & nbsp n ' broker_to_flush ', n ' Broker_eventhandler ', N ' Ft_iftshc_mutex ', & nbsp N ' sqltrace_incremental_flush_sleep ', N ' Dirty_page_poll ', N ' Sp_server_diagnostics_sleep ') ) select [W1]. [Wait_type] As [waittype], CAST ([W1]. [WaitS] As DECIMAL (2)) as [wait_s], CAST ([W1]. [ResourceS] As DECIMAL (2)) as [resource_s], CAST ([W1]. [Signals] As DECIMAL (14, 2) as [signal_s], [W1]. [Waitcount] As [waitcount], CAST ([W1]. [Percentage] As DECIMAL (4, 2)) as [percentage], CAST ([W1]. [WaitS]/[W1]. [Waitcount]) As DECIMAL (4)) as [avgwait_s], CAST ([W1]. [ResourceS]/[W1]. [Waitcount]) As DECIMAL (4)) as [avgres_s], CAST ([W1]. [Signals]/[W1]. [Waitcount]) As DECIMAL (4)) as [Avgsig_s]from [Waits] as [W1]inner JOIN [Waits] as [w2] on [W2]. [RowNum] <= [W1]. [RowNum] GROUP by [W1]. [RowNum], [W1]. [Wait_type], [W1]. [waits], [W1]. [ResourceS], [W1]. [Signals], [W1]. [Waitcount], [W1]. [Percentage] Having SUM ([W2]. [ Percentage]) –[W1]. [Percentage] < 95; -percentage Thresholdgo you can very easily come up with a-to-persist the results every few hours or every day a nd do some time-series an analysis to a figure out trends or automatically spot problems as they start to happen. can also use performance Dashboard-see theseGraphically in 2005 and Data Collector in 2008. On SQL Server SQLPERF (N ' waitstats ') . This SQL can be used to produce more than 95% waits.
– View the SQL for the wait type
if exists (SELECT * FROM sys.objects WHERE object_id = object_id (N ' [dbo].[ Get_statements_from_waiter_list] ') and OBJECTPROPERTY (object_id, N ' isprocedure ') = 1) drop procedure [dbo]. [Get_statements_from_waiter_list]go create proc get_statements_from_waiter_list (@wait_type nvarchar () =null) Asselect R.wait_type, R.wait_time, SUBSTRING (QT.TEXT,R.STATEMENT_START_OFFSET/2, Case whe n R.statement_end_offset =-1 Then len (convert (nvarchar (max), Qt.text)) * 2 Else R.statement_end_off Set End-r.statement_start_offset)/2) as Query_text, Qt.dbid, Dbname=db_name (qt.dbid), Qt.objectid , R.sql_handle, R.plan_handlefrom sys.dm_exec_requests rcross apply Sys.dm_exec_sql_text (r.sql_handle) as qtwhe Re r.session_id > R.wait_type = IsNull (Upper (@wait_type), r.wait_type) go EXEC get_statements_from_waiter_list< /pre>
DBCC SQLPERF (N ' sys.dm_os_wait_stats ', clear); Used to empty waiting information The author interprets the wait types that are often encountered: Cxpacket: In a concurrent query, a thread waits for another thread to finish. You can use the cost threshold for Parallelism,max configuration of degree of parallelism2 parameters, or set resource governor to reduce the waiting send, but are often not the underlying approach to solving the problem. This means that parallel operations occur, parallel execution occurs, or a worker in parallel execution is blocked do not words too literally • Do not set the server-level MAXDOP to 1, which is to disable parallel
When you configure the MAXDOP value, follow these guidelines.
SQL Server 2005 and later versions
-
MAXDOP = 8
-
MAXDOP = 0 to N
note N represents the number of processors.
-
-
-
More analysis of symptoms and solutions-pageiolatch_xx There is pageiolatch_sh waiting, which means that a wide range of scan is also observed access_methods_dataset_ Parentlatch and access_methods_scan_range_generator latch Check the request that caused the cxpacket to see if the execution plan is reasonable • One of the parallel threads takes too long ( That is, one of the threads is not blocked because of cxpacket. • Only due to parallel occurrence • Scan and expire statistics due to missing clustered index or inaccurate execution plan distinct result set cannot estimate execution plan, resulting in inappropriate execution plan, Thus generating cxpacket wait, the workaround is a temporary table (Wang Chenghui) If it is a problem • Ensure that the statistics are up-to-date and that there is an appropriate index • Set the MAXDOP of the query to consider the physical CPU cores of the Maxdop=numa • With the Load type (mix) in mind Setting the MAXDOP of the instance to consider setting the value of "cost threshold parallelism" to a higher pageiolatch_xx: sending from disk to memory is not necessarily an IO problem, it may be an execution plan issue. Or a memory pressure problem. This means: • Wait for the page to be read by disk • The most common is sh and ex sh means that the page is being used to read Ex means that the page will be modified to avoid words too literally • Do not directly judge the problem of IO system and IO channel more analysis • Decide which table/index is to be read (via DBCC PAGE) · Use Sys.dm_io_virtual_file_stats and AVG Disk secs/read performance counters to determine IO corresponding cxpacket wait, whether there is a parallel scan • View parallel scan by execution plan • See if there is an implicit conversion through the execution plan ( May cause a scan) • View the presence of buffer memory pressure through page life expectancy create a nonclustered index to reduce scan update STATISTICS transfer the affected data to a faster IO subsystem consider increasing the memory async_network_ IO: Usually when SQL Server waits for the client to fetch data, the client produces a large amount of data, resulting in slow data fetching, often due to the unreasonable programming. This means that SQL Server waits for the client to obtain the ACK feedback of the data to avoid words too literally • Do not simply consider it to be a network delay • Only after considering all other factors, consider whether the network delay more analysis • Analysis client program • Analysis NetworkNetwork Delay Solution • Client program Rbar (row-by-agonizing-row) • Analyze networking hardware, TCP configuration, etc. writelog: The Log Management system waits for the log to be sent when it is flushed to disk. Often indicates an IO subsystem problem, 1. Spread the conformance across multiple databases or shrink long transactions. You can use Sys.dm_io_virtual_file_stats to check the IO problem of logs this means • Wait for the log block to flush to the log avoid words too literally • Don't start off thinking it's an IO problem • Do not add log files directly more analysis • View Sys.dm_io_ Virtual_file_stats View Logbuffer wait to see if there is a scramble for the log buffer • View the disk waiting queue for the disk on which the log is located • View the average size of transactions • See if there is a large number of page splits (page splits can cause a large number of logs) to transfer logs to a faster IO system (Be sure to separate from the data) increase the size of the transaction to avoid a large number of log writes (such as Bulk writes) to remove useless nonclustered indexes to avoid log overhead modifying the index key or using padding to reduce the page splitting modifier architecture, distributing the load across multiple databases or servers msql_xp:sql When the server waits for an extended stored procedure to be sent, check the extended stored procedure Code lck_m_xx: Thread waits for the lock to be allocated, which means that the thread is plugged in: • Because another thread locks a resource, the thread cannot add incompatible locks to the resource avoid words too literally • Do not assume that the lock is root Cause more Analysis • Find the first blocked thread through sys.dm_os_waiting_tasks, while blocking the thread may be due to IO, network, memory, etc. • Use the blocking process report to capture the wait information solution based on the wait type of the first blocked process a lock escalation caused by a range update or scan • If possible, use a partition lock • Try to create an index to make the scan a nonclustered index lookup • Break up the bulk update transaction into smaller transactions · Try different isolation levels or snapshot isolation • Avoid unnecessary lock reads and writes should not be blocked from each other, you can try to modify the isolation level or use optimistic concurrency other blocking transaction release lock, find the basic reason io_completion: Wait for IO to complete, Often indicates IO problem Sos_scheduler_yield: When waiting for Spinlock to find that can waste a lot of CPU therefore, the thread determines to automatically give up the CPU this means that the thread runs out of time slices of 4 milliseconds, and actively abandons the CPU there is a spin lock to avoid words too literally · Not necessarily a CPU problem (CPU problems tend to manifest in a long runnable queue or a large number of signal wait) more analysis • See if there is a largeVolume scan • View wait type Note: This mode does not have a resource_wait wait type, so statements of some query wait types may not be captured • pagelatch_xx cannot be seen in Sys.dm_os_waiting_tasks: The wait for the page to appear (BUF latch). May be a hot page, GAM,SGAM,PFS may cause this problem this means • Wait to access the in-memory data file page • The common is that the SH and ex sh means that the page will be read Ex means that the page will be modified to avoid words too literally • No amount of pageiolatch_xx confusion · Does not mean that you need to increase IO and memory more analysis • Find the type of wait page select TOP * from sys.dm_os_waiting_tasks select wt.session_id, Wt.wait_type, Wt.wait_duration_ms, s.name as Schema_name, o.name as object_name, i.name as Index_namefrom Sys.dm_os_buffe R_descriptors Bdjoin ( SELECT session_id, Wait_type,wait_duration_ms,resource_description, ParseName (replace (resource_description, ': ', '. '), 1) database_id,parsename (replace (resource_description, ': ', '. '), 2) file_id,parsename (replace (resource_description, ': ', '. '), 3) page_id from Sys.dm_os_waiting_tasks WHERE wait_type like ' pagelatch% ') Wton bd.database_id = Wt.database_idand bd.file_id = Wt.file_idand bd.page _id = Wt.page_idjoin sys.allocation_units au on bd.allocation_unit_id = Au.allocatiOn_unit_idjoin sys.partitions p on au.container_id = P.partition_idjoin sys.indexes i on p.index_id = i.index_id and p.object_id = I.object_idjoin sys.objects o on i.object_id = O.object_idjoin Sys.schemas s on o.schema_id = S.SCHEMA_ID&N BSP; Processing method: http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/09/22/ resolving-pagelatch-contention-on-highly-concurrent-insert-workloads-part-1.aspx the most classic tempdb scramble • Add tempdb file · 4, if there is a scramble, add 4 more • Enable trace flag 1118 • Reduce the use of tempdb (e.g., reduce temporary tables) • Reduce the use of temporary tables, do not explicitly drop temporary tables (non-boot page tempdb scramble) (Gao Jiwei) Too many page splits • Modify the index key ( Classic GUID) • Avoid updating records that are too long • Use fill factors to insert incrementing tables to create insert hotspots • Reduce page splits by using random or key combinations and combining fill factors • Modify the program schema, insert distributed across multiple tables, databases, servers Backupxx: May be backupbuffer Backupio backupthread This means • Wait for data or data to be cached • Read database files • The third is usually due to the 0 initialization of data or disk more analysis • The first kind, The backup is based on a slow IO system or network, or the remote server's IO system is slow • The IO system on which the data file resides is slow • Generates Preemptive_os_writefilegather wait oledb this means that • using OLE DB mechanism to avoid words too literally • Do not speculate directly because of the use of linked servers more analysis • What is the query waiting for OLE DB • If a linked server is used, then what causes the delay possible solution for the linked server DBCC CHECKDB such internal use of OLE DB commands · Many DMV use OLE DB internally, so it could be a problem with some monitoring tools • Low-performance linked server &NBSP;&NBsp Latch_XX: Non-buf latch waits (latch is divided into 2 types, buf latch and non-buf latch, SQL Server 2008 internal Anatomy and Fault analysis in 6.6 in detail) Preemptive_xx: Switch to preemption mode wait preemptive_os_xx occurs when the Windows Scheduler does related actions this means that the thread directly calls the OS thread to switch to the preemptive scheduling mode • The state of the thread is running, not suspended more analysis SQL There are 194 such events in Server 2012 • There are very few such event documents • A small tip, in the XX section of MSDN Search Preemptive_os_xx, which is actually a possible solution for Windows API • To judge based on different types of wait types preemptive_os_createfile This means that the thread will call Windows to create the file • If you use FileStream, it may cause more analysis when FileStream creates a new NTFS file · View possible solutions for growing wait times • IO performance for FileStream • Use FileStream io overload • Refer to WIN32 api:http://msdn.microsoft.com/en-us/library/ windows/desktop/aa363858 (v=vs.85). Aspx preemptive_os_writefilegather This means that the thread will call windows to write the file to avoid words too literally · Don't just think of it as an IO problem more analysis • In-progress database operations • For example, restoring a database, creating a database file, growth, and automatic growth possible solutions • 0 Initialization of log when restoring database or log growth • 0 initialization of data files • Enable fast file initialization • When you perform a database restore , do not delete existing files • Refer to WIN32 api:http://msdn.microsoft.com/en-us/library/windows/desktop/aa365749 (v=vs.85) .aspx Preemptive_os_writefilegather This means that a thread calls Windows to wait for the synchronization object to change. • More analysis often occurs with network_io and Async_network_io • Follow async_ Network_io processing • See if there is a possible solution for transaction log Replication Async_network_Io when the app server and database server are on the same platform, using shared memory • When combined with network_io, it is likely that the transaction log Replication preemptive_os_dbmirrorxx sample Dbmirror_event_queue Dbmirror_send Dbmirroring_cmd Dbmirror_dbr_mutex This means • Wait for the mirrored resource to avoid words too literally • Do not just remove the mirror directly or choose high Performance mode more analysis and Analysis Dbmirror_dbr_ Possible causes of the average wait time for a mutex • If Dbmirror_dbr_mutex waits too much, it could be because there are too many mirrored databases, or too much content to mirror-possibly due to a common system bottleneck sqltrace_xx this means · Thread waits to write SQLTrace file avoid words too literally • Do not have to stop sqltrace more analysis • Use Sys.traces and sys.fn_trace_geteventinfo to track down some very frequent events · Analyze the possible causes of the IO where the trace files are located • Trace captures too many events • Rowset No quick consumption result set • Third-party products in scan trace latch_xx This means that there are non-page latches more analysis • Use Sys.dm_os_latch_ Stats to analyze which latch waits too long • View with other simultaneous wait types • For example Cxpacket and LATCH_EX and Access_methods_scan_range_ Generator often means there is a large number of possible solutions for scanning • This type of lock is not documented and needs to be Google on its own the next few pages of common locks • Microsoft white paper: http://sqlcat.com/sqlcat/b/whitepapers/ Archive/2011/07/05/diagnosing-and-resolving-latch-contention-on-sql-server.aspx threadpool: Wait for the available Workthreadsdbmirror_dbm_mutex: the wait to send buffer may be too many resouce_semaphore: The query statement waits for memory to be allocated, either the query statement is too large or the required memory is too large. Msql_dg:sql server waits for a distributed query to complete, indicating a problem with the distributed query Resouce_semaphore_query_complie: too large concurrent compilationMSSearch: Full-text query waiting, mainly recompilation and unbuffered plan
Tuning from the wait angle
