SQL Server Reference Guide

If you do a search here on InformIT, on other SQL Server sites, or even the Internet at large, you’ll find no lack of tips, discussions, training and other information on Performance Tuning. I have an entire section dedicated to it at this site.

Most every performance tuning effort revolves around time. In fact, time is exactly what you’re trying to save, which is the very reason for performance tuning in specific. As part of reducing the time spent on actions within the server, you have to be able to see the time actions take, or in a word, monitor them. In other articles I’ve showed you how to use several methods to track things in SQL Server. Armed with these monitoring tips, you can track that time. You can track the time it takes for lots of things, but in general there are three buckets of things to track:

• Queries — You should the longest running queries with their query plans.
• Maintenance — From how long each backup takes to index reorgs and rebuilds, this information is invaluable.
• Jobs and Scheduled Tasks — Most all of us have SQL Server Agent Jobs, and developing a schedule of how long they are running is also useful. This bucket also includes things like replication, Database Mirroring on so on.

For each of these, I track the minimum, maximum and average times. I look for outliers — things that suddenly change and so on. Again, there are lots of articles, even beyond the ones I’ve written here about the performance tuning methodologies and tracking.

In this tutorial, I want to focus on the last two sets of time measures — not the ones that deal with performance per-se, but those that show longer-running activity on the system, say a few minutes to an hour or so. There are many uses for this information, from performance tuning to developing a recovery plan, knowing how long things take is vital.

I’m going to focus on the methods that you have available for this process more than the actual numbers themselves. I’ll mention a couple of things you can actually track, and I’ll point to locations for finding the others, but I think it’s more important that you’re able to know how the general process works more than the specifics of tracking things. I’ll then reference this article with more targeted scripts elsewhere on this site.

In this part of the tutorial I’ll cover the setup of what you need to monitor, where to put it, and your options for collecting it. In part two I’ll give you some examples to show you how I’ve done it with my systems.

A Place to put the results

You can track the information you capture in real-time if you wish, perhaps if you’re just checking on a particular measurement. In some of these methods (especially the ones using the Windows CMD or PowerShell functions) you can send the data directly to a report, file or web page (or all three). You can even mail the results to yourself, or place the measurements in an Excel spreadsheet for graphical analysis.

But most of the time you’re going to want to save that data somewhere for later use. By storing the data in a database you have the best of all worlds, because you can make the data available to lots of outputs or even just by manipulating the data like you would any relational store.

I’ve decided that I’ll use my SQLCMS for this, which you can read more about here, since it’s a central location for me to keep information about all of my Instances, and it’s flexible enough to handle any changes I throw at it. Not only that, I’m also storing performance and growth numbers there already, so if I do want to correlate or compare these longer-running tasks I can.

One thing is important to note, however. The metrics you’ll collect in these examples work on a different time scale than the ones for performance tuning. For instance, in most performance tuning exercises, you’re not often interested in the year, month or day length of the tasks. A backup, however, might take several hours — something that you would become quite alarmed at for a query!

You have two methods of dealing with this. The first is to collapse the data that you retrieve and cast it as a DATETIME value. In this case you need to take the inputs you receive from the various operations and send them to the database as a single value, which you may later have to break down into the units that matter for that situation. And when you follow this method you have the issue of assigning some meaningless data to the NULL data you collect. For instance, if the year is not important for backup operations that you are tracking (or when you have another value that does not contain the year) then you’ll have to add one to make the DATETIME work. Of course you can use the new DATE and TIME values in SQL Server 2008 and higher, but I’m talking about using the DATETIME that other values are stored in. And a large part of the DBA in me says that “faking” a value is a bad idea all around – but it is valid if you want to do it that way.

The second method is to break out the units by storing them as discrete time elements. In other words, you can store the year, day, month and so on in fields and then store either the unit as a foreign key or have columns for each of those values. While this works, it does have drawbacks as well. For one, you have to consider validating that you don’t have 99 seconds — or perhaps that’s exactly what you want. And that’s the trouble. You have to decide from the outset that an operation reported in seconds (such as the SQL Agent Job Steps) will be stored that way or calculated out to minutes, seconds and so on. Keeping all that straight can be a problem.

In any case, once you decide on a methodology, document it and stick with it so that your reporting solution for the project works.

Methods for collection

With the location set, you’ll need to decide how to collect the metrics. I advise against a “one tool fit’s all” approach here. For some activities, tracking via the built-in counters in Performance Monitor (also called perfmon or the Windows System Monitor) is the best approach, and in others using the SQL Server system tables or Dynamic Management views are appropriate. I also use the Windows Management Interface, or WMI calls, as a method of accessing some data.

All of these methods, and many others, have advantages and disadvantages. I recommend determining what you want to capture, and then reading up on how it is instrumented. More than likely there will be several methods for collecting the numbers you want, and what I do is to fund out the best way for the measurement process based on the situation I’m in.

For instance, I often track Windows Server uptime using a WMI call. But in one instance I was not the system administrator on the box where SQL Server was installed. In this situation I usually ask the administrator to run the code for me on an automated fashion, but in this case she couldn’t do that for me. So I picked another method where I examined the Windows Event (application) Logs for an item I knew started when the box started. Since that log was open to my account, I could track the information using that method. The key is that you need to be flexible.

The primary methods I research for the collection of timed events relating to SQL Server are:

• Querying the system databases and tables in SQL Server
• Querying the system functions and stored procedures in SQL Server
• Querying the Dynamic Management Views (DMV’s)
• Accessing the Performance Monitor (or Windows System Monitor) counters and objects
• Using the Windows Management Interface
• Accessing the Windows Event Logs
• Reading the SQL Server Error Logs (lots of ways to do this, from T-SQL functions to text file reads)
• Reading other system log files or mechanisms

As you can see, you have a lot of choices. The key is to research them and find what works best for you.

Methods for population

Depending on the method you choose for collecting the data, you have a few options for how you want to place it into the database.

I normally use a standard INSERT statement when I am accessing the counter values from and kind of T-SQL statements. I’ve explained how to do these inserts in the SQLCMS series I mentioned earlier, so I won’t re-cover that information here. I’ve also got code elsewhere on this site that shows you how to get the data from a stored procedure or function into an INSERT statement, which is a bit trickier.

I’ve also used a method where I collect the data using a command-line or other logging tool into a text file, and then later used bcp or SSIS to import the file into the database. The nice thing about using those methods is that you can collect the data “natively”, and then format it in any way you need. I have an entire article on how to import data here.

Yet another option is to use one tool to both query the data and do the insert. This is simple within T-SQL of course, but more difficult when you’re reading in a data set from Performance Monitor or a text file. That’s when I turn to PowerShell, since it works “natively” with all .NET objects on my servers, from Windows to SQL Server and just about everything else. You can read more about PowerShell and SQL Server here, including how to perform both queries and inserts from that tool. It’s the one I’ll show throughout this article, but you shouldn’t feel that you have to use it. If your’re more familiar with Perl or some other tool, by all means use that. The point is to select the best method based on the needs you have.

Sometimes I’ll begin using one method, but them come back and change that at a later date when I learn a better way.

Methods for display

Now you’ve decided on where to place your results, and how you can collect them.

The final part of the process is to figure out how you’re going to display the results. One again, I recommend on picking just one method. Let people use the tools they are the most familiar with.

A web page is often a great way to show the data, since it can be consumed by so many programs. Most Microsoft products, from SQL Server Management Studio to Excel and Outlook can display web pages natively, and of course you can use SQL Server Reporting Services for the output. And of course other non-Microsoft programs can show web pages just fine.

Examples of Items to Track

Now you’re down to which items you want to track. There are several candidates, but my list includes the following:

• SQL Server Agent Jobs
• Maintenance Tasks
• System Uptime
• Database Mirroring Sessions
• Replication Activity

I actually started collecting this data for a Disaster Recovery “RTO” or Recovery Time Objective number. I wanted dot be able to tell how long it would take to get everything back to where I started if I had to completely rebuild all of my systems. That’s where I started, and then I added in things like the Agent history at a later time to find out how my systems were being used for a consolidation effort later.

In part two I’ll follow up this series with a few examples. I’ll focus not only on a few sample queries and scripts, but more on what I ran into and what choices I had to make.

InformIT Articles and Sample Chapters

If you’re interested in a more pre-prepared method of data collection, System Center may be for you. You can check out more info on it in the article Introduction to the Microsoft System Center Enterprise Suite.

Books and eBooks

And there’s a full book on System Center here that you can read to find out more, Microsoft System Center Enterprise Suite Unleashed.

Online Resources

More on system tables and monitoring is here.