{"id":2179,"date":"2012-06-10T12:39:42","date_gmt":"2012-06-10T12:39:42","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2179"},"modified":"2012-06-10T12:39:42","modified_gmt":"2012-06-10T12:39:42","slug":"how-to-analysis","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/06\/10\/how-to-analysis\/","title":{"rendered":"how to analysis"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\nWhen looking at the wait statistics being tracked by SQL Server, it&#39;s important that these\r\nwait types are eliminated from the analysis, allowing the more problematic waits in the\r\nsystem to be identified. One of the things I do as a part of tracking wait information is to\r\nmaintain a script that filters out the non-problematic wait types, as shown in Listing 1.2.&#39;\r\n\r\nTroubleshooting SQL Server\r\nA Guide for the Accidental DBA\r\n\r\n--1,wait types,if for i\/o,then sys.dm_io_virtual_file_stats\r\n--2,sqlserver perfmon counts view\r\n\r\nSELECT TOP 10\r\nwait_type ,\r\nmax_wait_time_ms wait_time_ms ,\r\nsignal_wait_time_ms ,\r\nwait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,\r\n100.0 * wait_time_ms \/ SUM(wait_time_ms) OVER ( )\r\nAS percent_total_waits ,\r\n100.0 * signal_wait_time_ms \/ NULLIF(SUM(signal_wait_time_ms) OVER ( ),0)\r\nAS percent_total_signal_waits ,\r\n100.0 * ( wait_time_ms - signal_wait_time_ms )\r\n\/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits\r\nFROM sys.dm_os_wait_stats\r\nWHERE wait_time_ms &gt; 0 -- remove zero wait_time\r\nAND wait_type NOT IN -- filter out additional irrelevant waits\r\n( &#39;SLEEP_TASK&#39;, &#39;BROKER_TASK_STOP&#39;, &#39;BROKER_TO_FLUSH&#39;,\r\n&#39;SQLTRACE_BUFFER_FLUSH&#39;,&#39;CLR_AUTO_EVENT&#39;, &#39;CLR_MANUAL_EVENT&#39;,\r\n&#39;LAZYWRITER_SLEEP&#39;, &#39;SLEEP_SYSTEMTASK&#39;, &#39;SLEEP_BPOOL_FLUSH&#39;,\r\n&#39;BROKER_EVENTHANDLER&#39;, &#39;XE_DISPATCHER_WAIT&#39;, &#39;FT_IFTSHC_MUTEX&#39;,\r\n&#39;CHECKPOINT_QUEUE&#39;, &#39;FT_IFTS_SCHEDULER_IDLE_WAIT&#39;,\r\n&#39;BROKER_TRANSMITTER&#39;, &#39;FT_IFTSHC_MUTEX&#39;, &#39;KSOURCE_WAKEUP&#39;,\r\n&#39;LAZYWRITER_SLEEP&#39;, &#39;LOGMGR_QUEUE&#39;, &#39;ONDEMAND_TASK_QUEUE&#39;,\r\n&#39;REQUEST_FOR_DEADLOCK_SEARCH&#39;, &#39;XE_TIMER_EVENT&#39;, &#39;BAD_PAGE_PROCESS&#39;,\r\n&#39;DBMIRROR_EVENTS_QUEUE&#39;, &#39;BROKER_RECEIVE_WAITFOR&#39;,\r\n&#39;PREEMPTIVE_OS_GETPROCADDRESS&#39;, &#39;PREEMPTIVE_OS_AUTHENTICATIONOPS&#39;,\r\n&#39;WAITFOR&#39;, &#39;DISPATCHER_QUEUE_SEMAPHORE&#39;, &#39;XE_DISPATCHER_JOIN&#39;,\r\n&#39;RESOURCE_QUEUE&#39; )\r\nORDER BY wait_time_ms DESC\r\n\r\n\r\nCXPACKET\r\nOften indicates nothing more than that certain queries are executing with parallelism;\r\nCXPACKET waits in the server are not an immediate sign of problems\r\n\r\nSOS_SCHEDULER_YIELD\r\nThis may indicate that the server is under CPU press\r\n\r\nTHREADPOOL\r\nrequiring an increase in the number of\r\nCPUs in the server, to handle a highly concurrent workload, or it can be a sign of\r\nblocking\r\n\r\n\r\nLCK_*\r\nThese wait types signify that blocking is occurring in the system and that sessions\r\nhave had to wait to acquire a lock of a specific typ\r\nThis problem can be investigated further using the information\r\nin the sys.dm_db_index_operational_stats\r\n\r\n\r\nPAGEIOLATCH_*, IO_COMPLETION, WRITELOG\r\nThese waits are commonly associated with disk I\/O bottlenecks, though the root\r\ncause of the problem may be,PAGEIOLATCH_* waits are\r\nspecifically associated with delays in being able to read or write data from the database\r\nfiles. WRITELOG waits are related to issues with writing to log files.\r\nThese waits\r\nshould be evaluated in conjunction with the virtual file statistics as well as Physical\r\nDisk performance counters\r\n\r\n\r\nPAGELATCH_*\r\nA lot of times\r\nPAGELATCH_* waits are associated with allocation contention issues. One of\r\nthe best-known allocations issues associated with PAGELATCH_* waits occurs in\r\ntempdb when the a large number of objects are being created and destroyed in\r\ntempdb and the system experiences contention on the Shared Global Allocation\r\nMap (SGAM), Global Allocation Map (GAM), and Page Free Space (PFS) pages in the\r\ntempdb database.\r\n\r\n\r\n\r\n\r\nLATCH_*\r\nDetermining the\r\nspecific latch class that has the most accumulated wait time associated with it can\r\nbe found by querying the sys.dm_os_latch_stats DMV.\r\n\r\nASYNC_NETWORK_IO\r\nThis wait is often incorrectly attributed to a network bottleneck,In fact, the most\r\ncommon cause of this wait is a client application that is performing row-by-row\r\nprocessing of the data being streamed from SQL Server as a result set (client accepts\r\none row, processes, accepts next row, and so on). Correcting this wait type generally\r\nrequires changing the client-side code so that it reads the result set as fast as possible,\r\nand then performs processing\r\n\r\n\r\n\r\nAfter fixing any problem in the server, in order to validate that the problem has indeed\r\nbeen fixed, the wait statistics being tracked by the server can be reset using the code in\r\nListing 1.3.\r\n\r\nDBCC SQLPERF(&#39;sys.dm_os_wait_stats&#39;, clear)\r\n\r\n\r\n--------------disk I\/O bottleneck.-----------\r\nwill provide cumulative physical I\/O statistics, the number of reads\r\nand writes on each data file, and the number of reads and writes on each log file, for\r\nthe various databases in the instance, from which can be calculated the ratio of reads to\r\nwrites. This also shows the number of I\/O stalls and the stall time associated with the\r\nrequests, which is the total amount of time sessions have waited for I\/O to be completed\r\non the file.\r\nwhether heavy-read or heavy-write, and at the average latency associated with the I\/O, as this will\r\ndirect further investigation and possible solutions.\r\n\r\n\r\nSELECT DB_NAME(vfs.database_id) AS database_name ,\r\n\r\nvfs.database_id ,\r\nvfs.FILE_ID ,\r\nio_stall_read_ms \/ NULLIF(num_of_reads, 0) AS avg_read_latency ,\r\nio_stall_write_ms \/ NULLIF(num_of_writes, 0)\r\nAS avg_write_latency ,\r\nio_stall \/ NULLIF(num_of_reads + num_of_writes, 0)\r\nAS avg_total_latency ,\r\nnum_of_bytes_read \/ NULLIF(num_of_reads, 0)\r\nAS avg_bytes_per_read ,\r\nnum_of_bytes_written \/ NULLIF(num_of_writes, 0)\r\nAS avg_bytes_per_write ,\r\nvfs.io_stall ,\r\nvfs.num_of_reads ,\r\nvfs.num_of_bytes_read ,\r\nvfs.io_stall_read_ms ,\r\nvfs.num_of_writes ,\r\nvfs.num_of_bytes_written ,\r\nvfs.io_stall_write_ms ,\r\nsize_on_disk_bytes \/ 1024 \/ 1024. AS size_on_disk_mbytes ,\r\nphysical_name\r\nFROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs\r\nJOIN sys.master_files AS mf ON vfs.database_id = mf.database_id\r\nAND vfs.FILE_ID = mf.FILE_ID\r\nORDER BY avg_total_latency DESC\r\n\r\n\r\n\r\nSELECT * FROM sys.dm_os_performance_counters\r\n\r\nDECLARE @CounterPrefix NVARCHAR(30)\r\nSET @CounterPrefix = CASE WHEN @@SERVICENAME = &#39;MSSQLSERVER&#39;\r\nTHEN &#39;SQLServer:&#39;\r\nELSE &#39;MSSQL$&#39; + @@SERVICENAME + &#39;:&#39;\r\nEND ;\r\n-- Capture the first counter set\r\nSELECT CAST(1 AS INT) AS collection_instance ,\r\n[OBJECT_NAME] ,\r\ncounter_name ,\r\ninstance_name ,\r\ncntr_value ,\r\ncntr_type ,\r\nCURRENT_TIMESTAMP AS collection_time\r\nINTO #perf_counters_init\r\nFROM sys.dm_os_performance_counters\r\nWHERE ( OBJECT_NAME = @CounterPrefix + &#39;Access Methods&#39;\r\nAND counter_name = &#39;Full Scans\/sec&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;Access Methods&#39;\r\nAND counter_name = &#39;Index Searches\/sec&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;Buffer Manager&#39;\r\nAND counter_name = &#39;Lazy Writes\/sec&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;Buffer Manager&#39;\r\nAND counter_name = &#39;Page life expectancy&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;General Statistics&#39;\r\nAND counter_name = &#39;Processes Blocked&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;General Statistics&#39;\r\nAND counter_name = &#39;User Connections&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;Locks&#39;\r\nAND counter_name = &#39;Lock Waits\/sec&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;Locks&#39;\r\nAND counter_name = &#39;Lock Wait Time (ms)&#39;\r\n)\r\n\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;SQL Statistics&#39;\r\nAND counter_name = &#39;SQL Re-Compilations\/sec&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;Memory Manager&#39;\r\nAND counter_name = &#39;Memory Grants Pending&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;SQL Statistics&#39;\r\nAND counter_name = &#39;Batch Requests\/sec&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;SQL Statistics&#39;\r\nAND counter_name = &#39;SQL Compilations\/sec&#39;\r\n)\r\n-- Wait on Second between data collection\r\nWAITFOR DELAY &#39;00:00:01&#39;\r\n-- Capture the second counter set\r\nSELECT CAST(2 AS INT) AS collection_instance ,\r\nOBJECT_NAME ,\r\ncounter_name ,\r\ninstance_name ,\r\ncntr_value ,\r\ncntr_type ,\r\nCURRENT_TIMESTAMP AS collection_time\r\nINTO #perf_counters_second\r\nFROM sys.dm_os_performance_counters\r\nWHERE ( OBJECT_NAME = @CounterPrefix + &#39;Access Methods&#39;\r\nAND counter_name = &#39;Full Scans\/sec&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;Access Methods&#39;\r\nAND counter_name = &#39;Index Searches\/sec&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;Buffer Manager&#39;\r\nAND counter_name = &#39;Lazy Writes\/sec&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;Buffer Manager&#39;\r\nAND counter_name = &#39;Page life expectancy&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;General Statistics&#39;\r\nAND counter_name = &#39;Processes Blocked&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;General Statistics&#39;\r\nAND counter_name = &#39;User Connections&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;Locks&#39;\r\nAND counter_name = &#39;Lock Waits\/sec&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;Locks&#39;\r\nAND counter_name = &#39;Lock Wait Time (ms)&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;SQL Statistics&#39;\r\nAND counter_name = &#39;SQL Re-Compilations\/sec&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;Memory Manager&#39;\r\nAND counter_name = &#39;Memory Grants Pending&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;SQL Statistics&#39;\r\nAND counter_name = &#39;Batch Requests\/sec&#39;\r\n)\r\nOR ( OBJECT_NAME = @CounterPrefix + &#39;SQL Statistics&#39;\r\nAND counter_name = &#39;SQL Compilations\/sec&#39;\r\n)\r\n-- Calculate the cumulative counter values\r\nSELECT i.OBJECT_NAME ,\r\ni.counter_name ,\r\ni.instance_name ,\r\nCASE WHEN i.cntr_type = 272696576\r\nTHEN s.cntr_value - i.cntr_value\r\nWHEN i.cntr_type = 65792 THEN s.cntr_value\r\nEND AS cntr_value\r\nFROM #perf_counters_init AS i\r\nJOIN #perf_counters_second AS s\r\nON i.collection_instance + 1 = s.collection_instance\r\nAND i.OBJECT_NAME = s.OBJECT_NAME\r\nAND i.counter_name = s.counter_name\r\nAND i.instance_name = s.instance_name\r\nORDER BY OBJECT_NAME\r\n-- Cleanup tables\r\nDROP TABLE #perf_counters_init\r\nDROP TABLE #perf_counters_second\r\n\r\n\r\nThe two Access Methods counters provide information about the ways that tables\r\nare being accessed in the database. The most important one is the Full Scans\/sec\r\ncounter, which can give us an idea of the number of index and table scans that are\r\noccurring in the SYSTEM\r\n\r\nIn general, I want the number\r\nof Index Searches\/sec to be higher than the number of Full Scans\/sec by a factor\r\nof 800&ndash;1000. If the number of Full Scans\/sec is too high, refer to Chapter 5, Missing\r\nIndexes to determine if there are missing indexes in the database, resulting in excess\r\nI\/O operations.\r\n\r\nPage Life Expectancy (PLE) which is the number of seconds a page will remain\r\nin the data cache\r\nthe question VALUE &lt;= (max server memory\/4)*300s,\r\nWrites\/sec, which are page flushes from the buffer cache outside of the normal CHECKPOINT\r\nprocess, then the server is most likely experiencing data cache memory pressure,\r\nwhich will also increase the disk I\/O being performed by the SQL SERVER,At this point\r\nthe Access Methods counters should be investigated to determine if excessive table or\r\nindex scans are being performed on the SQL SERVER\r\n\r\n\r\nThe General Statistics\\Processes Blocked, Locks\\Lock Waits\/sec,\r\nIf these counters return a value other\r\nthan zero, over repeated collections of the data, then blocking is actively occurring in one\r\nof the databases , Blocking should be used to\r\ntroubleshoot the problems further\r\n sp_configure (&#39;show advanced options&#39;)\r\n \r\n The higher the number of SQL Compilations\/\r\nsec in relation to the Batch Requests\/sec, the more likely the SQL Server is\r\nexperiencing an ad hoc workload that is not making optimal using of plan caching. The\r\nhigher the number of SQL Re-Compilations\/sec in relation to the Batch Requests\/\r\nsec, the more likely it is that there is an inefficiency in the code design that is forcing\r\na recompile of the code being executed in the SQL Server. In either case, investigation\r\nof the Plan Cache\r\n show advanced options\r\n \r\n The Memory Manager\\Memory Grants Pending performance counter provides\r\ninformation about the number of processes waiting on a workspace memory grant in\r\nthe instance.If this counter has a high value,there may be query inefficiencies in the instance that are causing excessive\r\nmemory grant requirements, for example, large sorts or hashes that can be resolved by\r\ntuning the indexing or queries being executed\r\n\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>When looking at the wait statistics being tracked by SQ [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2179","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2179"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2179\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}