Search results matching tags 'Best Practices', 'DBA', 'Tips', and 'Hardware'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=Best+Practices,DBA,Tips,Hardware&orTags=0Search results matching tags 'Best Practices', 'DBA', 'Tips', and 'Hardware'en-USCommunityServer 2.1 SP2 (Build: 61129.1)The Zombie PerfMon Counter That Never Dies! Quick Tiphttp://sqlblog.com/blogs/kevin_kline/archive/2012/10/08/the-zombie-perfmon-counter-that-never-dies-quick-tip.aspxMon, 08 Oct 2012 14:55:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:45480KKline<h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"></h2><h2>The PerfMon Counters That Just Won't Die</h2><div style="font-size:13px;font-weight:normal;"><br></div><img class="alignright size-medium wp-image-2093" title="zombie-baby1" width="300" height="296" style="border:1px solid black;cursor:default;float:right;font-size:13px;font-weight:normal;margin:2px;" src="http://kevinekline.com/wp-content/uploads/2012/10/zombie-baby1-300x296.jpg"><div style="font-size:13px;font-weight:normal;">One of the things that's simultaneously great and horrible about the Internet is that once something gets posted out in the ether, it basically never goes away. &nbsp;(Some day, politicians will realize this. &nbsp;We can easily fact check their consistency). &nbsp;Because of longevity of content posted to the Internet, a lot of performance tuning topics become "zombies". &nbsp;We shoot 'em in dead, but they keep coming back!</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">In other words, those old recommendations&nbsp;<em>were&nbsp;</em>a suggested best practices for long ago, for a specific version of SQL Server, but are now inappropriately for the newer version. &nbsp;It's not uncommon for me, when speaking at a conference, to encounter someone who's still clinging to settings and techniques which haven't been good practice since the days of SQL Server 2000. &nbsp;Here's an example of&nbsp;<a href="http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx">Microsoft SQL Server 2000 Best Practices that are very version-specific</a>.</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">So here's an example. &nbsp;The %Disk Time counter and the Disk Queue Length were heavily recommended as a key performance indicator for IO performance. &nbsp;SQL Server throws a lot of IO at the disks using scatter/gather to maximize the utilization of the disk-based IO subsystem. &nbsp;This approach leads to short bursts of long queue depths during checkpoints and readaheads for an instance of SQL Server. &nbsp;Sometimes the server workload is such that your disk can't keep up with the IO shoved at it and when that happens, you'll see long queue lengths too.&nbsp; The short burst scenario isn't a problem. &nbsp;The lengthening queue length scenario usually is a problem. &nbsp;&nbsp;So is that a good practice?</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;"><strong>In a word, not-so-much.</strong></div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">Those counters can still be of some use on an instance of SQL Server which only has one hard disk drive. &nbsp;But that's&nbsp;<em>exceedingly</em>&nbsp;rare these days. &nbsp;Why?</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">The PerfMon counter %Disk time is a bogus performance metric for several reasons. &nbsp;It does not take into account&nbsp;asynchronous&nbsp;I/O requests. &nbsp;It can't tell what the real performance profile is for an underlying&nbsp;&nbsp;RAID set may be, since they contain multiple disk drives. &nbsp;The PerfMon counter Disk Queue Length is also mostly useless, except on SQL Server's with a single physical disk, because the hard disk controller cache obfuscates how many IO operations are actually pending on the queue or not. &nbsp;In fact, some hard disks even have tiny write caches as well, which further muddies the water was to whether the IO is truly queued, in a cache somewhere between the OS and the disk, or has finally made it all the way to the&nbsp;<a href="http://en.wikipedia.org/wiki/Cmos">CMOS</a>&nbsp;on the disk.</div><div style="font-size:13px;font-weight:normal;"><br></div><h2>Better IO PerfMon Counters</h2><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">Instead of using those PerfMon counters, use the Ave Disk Reads /sec, Avg Disk Write /sec, and Avg Disk &nbsp;Transfers/sec&nbsp;to track the performance of disk subsystems. &nbsp;These counters track the average number of read IOs, write IOs, and combined read and write IOs to occured in the last second. &nbsp;Occassionally, I like to track the same metrics by volume of data rather than the rate of IO operations. &nbsp;So, to get that data, you may wish to give these volume-specific PerfMon counters a try:&nbsp;Avg Disk &nbsp;Transfer Bytes/sec, Ave Disk Read Bytes /sec, and Avg Disk Write Bytes/sec</div><div style="font-size:13px;font-weight:normal;"><br></div><h2>For SQL Server IO Performance, Use Dynamic Management Views (DMV)</h2><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">And unless you've been living in a cave, you should make sure to use SQL Server's Dynamic Management Views (DMVs) to check on IO performance for recent versions of SQL Server. &nbsp;Some of my favorite DMV's for IO include:</div><div style="font-size:13px;font-weight:normal;"><ul><li>Sys.dm_os_wait_stats</li><li>Sys.dm_os_waiting_tasks</li><li>Sys.dm_os_performance_counters</li><li>Sys.dm_io_virtual_file_stats</li><li>Sys.dm_io_pending_io_requests</li><li>Sys.dm_db_index_operational_stats</li><li>Sys.dm_db_index_usage_stats</li></ul></div><div style="font-size:13px;font-weight:normal;">Many of these DMVs are fully document in this Books Online article here at&nbsp;<a href="http://msdn.microsoft.com/en-us/library/ms187974.aspx">Microsoft SQL Server 2012&nbsp;Index Related Dynamic Management Views and Functions</a>.</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">So how are you tracking IO performance metrics? &nbsp;Which ones are you using?</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">I look forward to hearing back from you!</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">Enjoy,</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">-Kev</div><div style="font-size:13px;font-weight:normal;"><p>-<a href="http://twitter.com/kekline">Follow me on Twitter!</a></p><p>&nbsp;</p><p>&nbsp;</p><div><br></div></div>