{"id":587,"date":"2013-06-19T06:00:43","date_gmt":"2013-06-19T13:00:43","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=587"},"modified":"2017-09-25T19:23:18","modified_gmt":"2017-09-26T02:23:18","slug":"the-accidental-dba-tools-for-on-going-monitoring","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/the-accidental-dba-tools-for-on-going-monitoring\/","title":{"rendered":"The Accidental DBA (Day 19 of 30): Tools for On-Going Monitoring"},"content":{"rendered":"

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our\u00a0Immersion Event for The Accidental\/Junior DBA<\/a>, which we present\u00a0several times each year<\/a>. You can find all the other posts in this series at\u00a0http:\/\/www.SQLskills.com\/help\/AccidentalDBA<\/a>. Enjoy!<\/em><\/p>\n

In yesterday\u2019s post<\/a> I covered the basics of baselines and how to get started.\u00a0 In addition to setting up baselines, it\u2019s a good idea to get familiar with some of the free tools available to DBAs that help with continued monitoring of a SQL Server environment.<\/p>\n

Performance Monitor and PAL<\/strong><\/p>\n

I want to start with Performance Monitor (PerfMon).\u00a0 I\u2019ve been using PerfMon since I started working with computers and it is still one of my go-to tools.\u00a0 Beginning in SQL Server 2005, Dynamic Management Views and Functions (DMVs and DMFs) were all the rage, as they exposed so much more information than had been available to DBAs before.\u00a0 (If you don\u2019t believe me, try troubleshooting a parameter sniffing issue in SQL Server 2000.)\u00a0 But PerfMon is still a viable option because it provides information about Windows as well as SQL Server.\u00a0 There are times that it\u2019s valuable to look at that data side-by-side.\u00a0 PerfMon is on every Windows machine, it\u2019s reliable, and it\u2019s flexible.\u00a0 It provides numerous configuration options, not to mention all the different counters that you can collect.\u00a0 You have the ability to tweak it for different servers if needed, or just use the same template every time.\u00a0 It allows you to generate a comprehensive performance profile of a system for a specified time period, and you can look at performance real-time.<\/p>\n

If you\u2019re going to use PerfMon regularly, take some time to get familiar it. When viewing live data, I like to use config files<\/a> to quickly view counters of interest.\u00a0 If I\u2019ve captured data over a period of time and I want to get a quickly view and analyze the data, I use PAL.\u00a0 PAL stands for Performance Analysis of Logs and it\u2019s written and managed by some folks at Microsoft.\u00a0 You can download PAL from CodePlex<\/a>, and if you don\u2019t already have it installed, I recommend you do it now.<\/p>\n

Ok, once PAL is installed, set up PerfMon to capture some data for you.\u00a0 If you don\u2019t know which counters to capture, don\u2019t worry.\u00a0 PAL comes with default templates that you can export and then import into PerfMon and use immediately.\u00a0 That\u2019s a good start, but to get a better idea of what counters are relevant for your SQL Server solution, plan to read Jonathan\u2019s post on essential PerfMon counters (it goes live this Friday, the 21st<\/sup>).\u00a0 Once you\u2019ve captured your data, you can then run it through PAL, which will do all the analysis for you and create pretty graphs. \u00a0For step-by-step instructions on how to use PAL, and<\/i> to view some of those lovely graphs, check out this post from Jonathan, Free Tools for the DBA: PAL Tool<\/a>.\u00a0 Did you have any plans for this afternoon?\u00a0 Cancel them; you\u2019ll probably have more fun playing with data.<\/p>\n

SQL Trace and Trace Analysis Tools<\/strong><\/p>\n

After PerfMon, my other go-to utility was SQL Trace.\u00a0 Notice I said \u201cwas.\u201d\u00a0 As much as I love SQL Trace and its GUI Profiler, they\u2019re deprecated in SQL Server 2012.\u00a0 I\u2019ve finally finished my mourning period and moved on to Extended Events.\u00a0 However, many of you are still running SQL Server 2008R2 and earlier so I know you\u2019re still using Trace.\u00a0 How many of you are still doing analysis by pushing the data into a table and then querying it?\u00a0 Ok, put your hands down, it\u2019s time to change that.\u00a0 Now you need to download ClearTrace<\/a> and install it.<\/p>\n

ClearTrace is a fantastic, light-weight utility that will parse and normalize trace files.\u00a0 It uses a database to store the parsed information, then queries it to show aggregated information from one trace file, or a set of files.\u00a0 The tool is very easy to use \u2013 you can sort queries based on reads, CPU, duration, etc.\u00a0 And because the queries are normalized, if you group by the query text you can see the execution count for the queries.<\/p>\n

A second utility, ReadTrace, provides the same functionality as ClearTrace, and more.\u00a0 It\u2019s part of RML Utilities<\/a>, a set of tools developed and used by Microsoft.\u00a0 ReadTrace provides the ability to dig a little deeper into the trace files, and one of the big benefits is that it allows you to compare two trace files.\u00a0 ReadTrace also stores information in a database, and normalizes the data so you can group by query text, or sort by resource usage.\u00a0 I recommend starting with ClearTrace because it\u2019s very intuitive to use, but once you\u2019re ready for more powerful analysis, start working with ReadTrace.\u00a0 Both tools include well-written documentation.<\/p>\n

Note: If you\u2019re a newer DBA and haven\u2019t done much with Trace, that\u2019s ok.\u00a0 Pretend you\u2019ve never heard of it, embrace Extended Events.<\/em><\/p>\n

SQLNexus<\/strong><\/p>\n

If you\u2019re already familiar with the tools I\u2019ve mentioned above, and you want to up your game, then the next utility to conquer is SQLNexus<\/a>.\u00a0 SQLNexus analyzes data captured by SQLDiag and PSSDiag, utilities shipped with SQL Server that Microsoft Product Support uses when troubleshooting customer issues.\u00a0 The default templates for SQLDiag and PSSDiag can be customized, by you, to capture any and all information that\u2019s useful and relevant for your environment, and you can then run that data through SQLNexus for your analysis.\u00a0 It\u2019s pretty slick and can be a significant time-saver, but the start-up time is higher than with the other tools I\u2019ve mentioned.\u00a0 It\u2019s powerful in that you can use it to quickly capture point-in-time representations of performance, either as a baseline or as a troubleshooting step.\u00a0 Either way, you\u2019re provided with a comprehensive set of information about the solution \u2013 and again, you can customize it as much as you want.<\/p>\n

Essential DMVs for Monitoring<\/strong><\/p>\n

In SQL Server 2012 SP1 there are 178 Dynamic Management Views and Functions.\u00a0 How do you know which ones are the most useful when you\u2019re looking at performance?\u00a0 Luckily, Glenn had a great set of diagnostic queries to use for monitoring and troubleshooting.\u00a0 You can find the queries on Glenn\u2019s blog<\/a>, and he updates them as needed, so make sure you follow his blog or check back regularly to get the latest version.\u00a0 And even though I rely on Glenn\u2019s scripts, I wanted to call out a few of my own favorite DMVs:<\/p>\n