Sql server 2005 Waits and Queues
|
|  Yüklə 0.86 Mb.
|
Waiter List and Wait TypesWhen a SQL Server 2005 session_id goes into a suspended status, a wait state is assigned indicating the reason why the session_id is suspended. The waiter list, shown in a DMV called sys.dm_os_waiting_tasks, contains currently suspended sessions and reasons for the suspension including the session_id, wait_type and the session’s accumulated wait time for this wait type in the column wait_duration_ms. If the wait is due to blocking where a lock cannot be obtained until another session releases their lock, the session holding as the lock, also known as the blocker and blocked resource are shown in the columns blocking_session_id and resource. The current wait list can be seen in sys.dm_os_waiting_tasks. The current runnable queue is found in sys.dm_exec_requests where the status is “runnable”. The total time that is spent waiting in sys.dm_os_waiting_tasks is found in the column wait_time_ms and the time that is spent waiting for CPU in the runnable queue is called signal_wait _time_ms. Resource waits can be computed by subtracting signal_wait_time_ms from wait_time_ms. A runnable queue is unavoidable with an OLTP workload because there are large volumes of identical transactions. The key question is not the length of the runnable queue but rather how much time is spent waiting for CPU compared to the resource waits of the waiter list. The difference between resource and signal waits shows the extent of CPU pressure, if any, on overall performance. A low signal (where signal is less than 25% of the total waits) to resource wait ratio indicates there is little CPU pressure. Dynamic Management Views (DMVs) and Functions (DMFs)Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) expose changing server state information that typically spans many sessions, many transactions, and many requests. Dynamic management views and functions reflect what’s going on inside the server process itself or across all sessions in the server. They are useful for diagnostics, memory and process tuning, and monitoring potentially across all sessions in the server. Useful DMVs for performance tuning purposes include sys.dm_exec_requests, sys.dm_os_waiting_tasks, and sys.dm_os_wait_stats. Sys.dm_exec_requestsEach SQL Server session has a unique session_id in the system DMV sys.dm_exec_requests. The stored procedure sp_who2 provides a list of these sessions in addition to other connection information such as command, resource, wait types, wait time, and status. User queries will have a session_id > 50. Common status values are ‘running’, ‘runnable’ and ‘suspended’, as described in the Execution Model discussion. A session status of ‘Sleeping’ indicates SQL Server is waiting for the next SQL Server command. Sys.dm_os_waiting_tasksThe waiter list that shows all waiting sessions and the reasons for the waits can be found in the DMV sys.dm_os_waiting_tasks. The session_id, wait type, and associated wait time can be seen. In addition, if the session is blocked from acquiring a lock, the session holding (known as blocking) the lock as well as the blocked resource is shown in the columns blocking_session_id and resource. Sys.dm_os_wait_statsSys.dm_os_wait_stats is the DMV that contains wait statistics, which are aggregated across all session ids since the last restart of SQL Server or since the last time that the wait statistics were reset manually using DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR). Resetting wait statistics can be helpful before running a test or workload. Anytime a session_id waits for a resource, the session_id is moved to the waiter list along with an associated wait type. The DMV sys.dm_os_waiting_tasks shows the waiter list at a given moment in time. Waits for all session_ids are aggregated in sys.dm_os_wait_stats. The stored procedures track_waitstats_2005 and get_waitstats_2005 can be used to measure the wait statistics for a given workload.
Track_waitstats_2005 stored procedureTrack_waitstats_2005 is a stored procedure that captures wait statistics from the DMV sys.dm_os_wait_stats and provides a ranking of descending order based on percentage. You can use this ranking to identify the greatest opportunities for performance improvements. The script location is: http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/waitstats/sql05vb049.mspx Get_waitstats_2005 stored procedureThe stored procedure get_waitstats_2005 reports the wait types that are collected by track_waitstats_2005. The get_waitstats_2005 procedure can be run during the execution of track_waitstats or after track_waitstats is completed. Running get_waitstats_2005 during the execution of track_waitstats_2005 will return a report of intermediate results while running get_waitstats_2005 at the conclusion of track_waitstats_2005 will return the final wait statistics report. The report provides a detailed picture of different wait types during the time measured, and the accumulated wait time for each. Get_waitstats_2005 reports information about waits. Total wait time is composed of resource waits and signal waits. Resource waits are computed by subtracting signal waits from total waits. Because signal waits represent the amount of time spent waiting in the runnable queue for CPU resources, they are a measure of CPU pressure. The application blueprints identify the significance CPU pressure by comparing signal waits with total waits. The script location is: http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/perf/sql05vb021.mspx
Performance and Tuning Blue PrintsIn this white paper, we examine different types of applications, how they use resources, and how you can performance tune the applications for different workloads. An OLTP workload differs significantly from a relational data warehouse or reporting application workload and it is useful to understand these differences and how it affects the objectives for high performance. Although resource bottlenecks differ from application to application, the performance and tuning methodology called Waits and Queues is highly accurate and the results are reproducible. Once you resolve one bottleneck, there will be another as you scale the application larger. An OLTP workload is generally characterized by high numbers of small identical transactions. In contrast, a data warehouse or reporting application is characterized by a few large transactions, each very different. These distinctions result in very different objectives and resource usage profiles. The blueprints reflect these distinctions.
|