Sql server 2005 Waits and Queues
|
|  Yüklə 0.86 Mb.
|
Typical resource bottlenecksResource bottlenecks can be identified by correlating waits and queues information. Typical bottlenecks include memory pressure, IO, CPU, network, and blocking. Depending on the application, resources can be used differently and frequently have different performance bottlenecks. An analysis of the application profile helps identify objectives for database design, resource usage, and performance. For more information about using the waits and queues methodology, see the http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx, which includes helpful DMV scripts and samples. Memory pressure and IO subsystem issuesLatencies caused by disk to memory transfers frequently surface as PageIOLatch waits. Memory pressure or disk IO subsystem issues can also increase PageIOLatch waits. When a user needs a page that is not in buffer cache, SQL Server has to first allocate a buffer page, and then puts a exclusive PageIOLatch_ex latch on the buffer while the page is transferred from disk to cache. Meanwhile, SQL Server puts a PageIOLatch_sh request on the buffer on behalf of the user. After the write to cache finishes, the PageIOLatch_ex latch is released. This allows the user to read the buffer page after which the PageIOLatch_sh is released. Consequently, high values for both PageIOLatch_ex and PageIOLatch_sh wait types can indicate IO subsystem issues. Pertinent performance counters include Physical disk: disk seconds/read and Physical disk: disk seconds/write and SQL Server Buffer Manager: Page Life Expectancy. See counters for more information. IO StallsThe table valued dynamic management function, sys.dm_io_virtual_file_stats provides a breakdown of SQL Server reads, writes, and io_stalls for a particular database or transaction log file. IO_stalls is the total cumulative time, in milliseconds, that users waited for I/O to be completed on the file since the last restart of SQL Server.
If IO_stalls is inordinately high for one or more files, it is possible that there is either a disk bottleneck or that high reads and writes are occurring on one drive. Average IO Waits per read or write can distinguish between consistently high IO queues or a temporary IO spike. A significantly higher average value for IO stalls on one particular drive indicates consistently high IO requests. This should be corroborated with Performance Monitor counters Physical Disk: Average Disk Seconds/Read and Average Disk Seconds/Write. The following script can also compute the Average Disk Seconds/Read and Average Disk Seconds/Write using sys.dm_io_virtual_file_stats. ---- average stalls per read, write and total ---- adding 1.0 to avoid division by zero errors select database_id, file_id ,io_stall_read_ms ,num_of_reads ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms' ,io_stall_write_ms ,num_of_writes ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms' ,io_stall_read_ms + io_stall_write_ms as io_stalls ,num_of_reads + num_of_writes as total_io ,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms' from sys.dm_io_virtual_file_stats(null,null) order by avg_io_stall_ms desc
Missing or poorly formed indexesMissing or poorly formed indexes can also cause excessive memory pressure or cache flushes. In some cases, SQL Server 2005 optimizer identifies potentially useful indexes to benefit a specific query (figure 1). The computed benefit of the index can be seen in the column avg_user_impact (percentage improvement with suggested index). It should be noted that this benefit applies to the individual query only where the maintenance cost is borne by inserts, updates, and delete operations. The following is a list of useful indexes. select d.* , s.avg_total_user_cost , s.avg_user_impact , s.last_user_seek ,s.unique_compiles from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g ,sys.dm_db_missing_index_details d where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle order by s.avg_user_impact desc go --- suggested index columns and usage declare @handle int select @handle = d.index_handle from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g ,sys.dm_db_missing_index_details d where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle select * from sys.dm_db_missing_index_columns(@handle) order by column_id Largest IO queries.The underlying purpose of an index suggested by sys.dm_db_missing_index_columns, is to avoid doing large amounts of IO for the query in question. Therefore, you can expect such queries to rank among the highest IO queries. To find the highest IO queries, you can use the following sample code: --- top 50 statements by IO SELECT TOP 50 (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO], 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 query_text, qt.dbid,
qt.objectid FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt ORDER BY [Avg IO] DESC |