SQL Server Reference Guide

In a previous article, I explained a process to follow for beginning a performance tuning effort. I think you should start with a good approach first, but it is often tempting to learn a particular tool and just start with something you know, and let the process develop from there.

This isn't always a bad idea — a third-party tool or something like Microsoft's System Center can often spot things you might not think of looking for, or quickly locate an area for you to focus on. But I think it's a better idea to be familiar with what you have available for each specific area of investigation, so in this overview I'll briefly cover each of the tool offerings you have "in the box" with SQL Server, or ones that are easily located and downloaded for no cost. I'll mention some of the other options you have in the third-party area, but only briefly. For those offerings I'll point you to the vendor sites and you can find out more about what they offer there.

Keep in mind this isn't meant to be a comprehensive overview of all of the tools available, and it's inevitable that I'll miss a few of the downloadable software offerings, so feel free to post comments at the end of the article if you know a tool that you like a great deal.

I'll start with the "holistic" approach, meaning I'll start with the Windows platform and then move on from there. Sure, you can focus on the database alone, but knowing how to evaluate each component will make the process go faster and get you to a resolution. I've covered some of these tools in other articles in more depth, so I'll make sure that I point those out. I'll also point out some books, articles, sites and other references as well.

Microsoft Windows Performance Tuning Tools

SQL Server runs on a physical computer, so obviously you should buy the fastest system you can afford. If you have to prioritize the money you spend, I recommend focusing on more memory, and a faster storage system before other considerations. Of course, if your application is CPU-intensive, then you should opt for a faster processor, but in any case you need to focus on the following sub-systems: CPU, Network Interface, Memory and I/O, or storage.

After your hardware is set up, you'll need to set up and install the Windows Operating System on it. Use the latest you can, with all of the latest Service Packs that you've tested successfully with your applications. I have a two-article series on setting up the Windows Platform for SQL Server starting here: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=330

The primary "out of the box" tools for working with the Windows Platform — starting with Windows Server 2008 - are:

Windows Task Manager: The first and simplest tool to start with is Task Manager. You can access it by right-clicking in the grey area on the Task Bar in Windows, or by selecting CTRL-ALT-DEL and clicking on it from the list that appears, or just click CTRL-SHIFT-ESC. Shows processes, CPU/Memory/IO/Network use, general performance graphs, and even light networking and connection information.

In addition to the article above, you can find the official documentation for it here: http://support.microsoft.com/kb/323527

Windows Resource Monitor: A step-up from the Task Manager is the Resource Monitor, which you can get to from the command line (resmon), from the Task Manager via a button, or in the Start Menu. It shows similar information to the Task Manager, but in a more segmented way, and has good graphs.

In addition to the information in my article above, you can learn more about the Resource Monitor in this screencast: http://technet.microsoft.com/en-us/edge/windows-7-screencast-resource-monitor-resmon.aspx

Windows System Monitor (Perfmon): Perfmon is technically another tool entirely, but since most people call System Monitor by that name, I'll use it here and there throughout my articles. The System Monitor allows you to collect and/or watch multiple "objects" and "counters" on those objects. It's probably one of the best baselining and monitoring tools for Windows, since it covers so many areas in Windows, and is also instrumented for software running on Windows.

In addition to the article I mentioned above, I've written an article on this tool here: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=196 I've also got an article here on making an unattended log with this tool: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=197 and even an article on extending your own SQL Server counters into it: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=198 and I have another article on the tool here: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=28

Reliability and Performance Monitor: Starting in Windows 7 and Windows Server 2008, Microsoft created a single tool that not only contains many of the products referenced above, but even has a full analysis section with charts, graphs and the ability to compare this information over time. It's the one-stop-shop for general Windows tuning.

In addition to the article I mentioned above, the full documentation for this product is here: http://technet.microsoft.com/en-us/library/cc771692(WS.10).aspx. It's worth the time to read this information and to work with this tool until you're familiar with it.

Microsoft Network Monitor: This is a free download from Microsoft (here) that you can use to watch the network traffic back and forth between your SQL Server system and a client application. You should rarely have to go to this level of monitoring, but I have had to use it to find a "chatty" application. It also is helpful in determining security or connection issues with an application as well.

In addition to the information in the help files for this product, which are quite good, there is a rich community around it with blogs, whitepapers, videos and more. You can find that here: http://blogs.technet.com/b/netmon/

SQL Server Platform and General Performance Tuning Tools

To fully monitor SQL Server, you need tools to evaluate the platform it runs on. This involves the SQL Server configuration settings, how the system is using memory overall, and so on. There are a few tools you can use for this purpose, listed below.

Many of these tools — in fact most of them — provide deep information on most every aspect of SQL Server, meaning that you can use them not only for the platform monitoring and settings, but also for all of the areas that follow. I'll mention them here, but remember that they work as tools to show information on all of the areas after them.

SQL Server Management Studio Standard Reports: Believe it or not, the Standard Reports that come with SQL Server built right in to SQL Server Management Studio (SSMS) have performance data in them — including things like the configuration and settings, the highest "cost" queries and more. I highly recommend that you start with them first.

I've written about these reports here: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=280 And I've documented them all on my blog here: http://blogs.msdn.com/b/buckwoody/archive/2008/04/17/sql-server-management-studio-standard-reports-the-full-list.aspx

SQL Server Activity Monitor: Prior to SQL Server 2008, you couldn't gain a lot of useful information from the Activity Monitor panel built in to SSMS. In SQL Server 2008, this tool was completely re-written, and it now shows a good real-time stream of information on the system and the overall pressure from the system as it is running. There are some small issues with it, but in general it shows great information.

I've written more about this tool here, and I provide additional links in the article: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=291

Management Data Warehouse: The Management Data Warehouse, and the tool that fills it (the Data Collector) is also new for SQL Server 2008 and higher. It is a central repository that holds specific counters, scans, and the results of various Dynamic Management Views (DMV's — more on those in a moment) in consolidated reports that are fairly easy to navigate.

I've written more about this tool here: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=294

SQL Server Profiler and SQL Trace: For all things SQL Server, you need to understand how to use SQL Server Profiler, and the files it creates along with SQL Trace. They are the SQL Server Equivalent to the Windows System Monitor — they give you very detailed information, but they take a little education on how to collect the information and how to interpret it. The important thing to keep in mind is that the Profiler tool is a good graphical component to work with collecting this data, but you're better off learning SQL Trace as early as you can. There is overhead to the graphical tool, and you shouldn't run it against a remote server.

I've written about this tool here: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=199

Extended Events: Actually, the SQL Trace isn't even the "lowest" level of data collection on Performance and other information on SQL Server. Extended Events (sometimes abbreviated XE) is part of a full "eventing" system for SQL Server. Eventing means that rather than actively querying a computing construct, you have it react to things that are happening in real-time. This means that the monitoring takes less of an impact on the system you are monitoring.

Extended Events are not trivial to implement, but they aren't that hard. My friend Mladen Prajdic has a very good introduction to Extended Events in SQL Server here: http://www.sqlteam.com/article/introduction-to-sql-server-2008-extended-events

Dynamic Management Views and System Views: Many of the tools above use multiple Transact-SQL statements that query a set of internal procedures called Dynamic Management Views, or more commonly DMV's. If you work with SQL Server either in programming or managing systems, you need to learn them.

I've written briefly about these commands here: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=179 and you can find much more in this excellent reference: http://www.red-gate.com/products/dba/sql-monitor/entrypage/dmv-2

SQL Nexus, SQL Diag and PAL: These are a series of third-party tools that bring together many other counters and objects in a single location.

You can find out more about them here, as well as the links for each: http://blogs.msdn.com/b/pamitt/archive/2011/02/25/how-to-use-the-sqldiag-the-sqlnexus-and-the-pal-tools-to-analyze-performance-issues-in-sql-server.aspx 

SQL Server Index Performance Tuning Tools

Indexes are one of the most impactful ways you can tune performance. Using correct indexes speeds up a system dramatically, but using them incorrectly can actually slow a system down. In addition to the tools just mentioned for general performance tuning that can show index information (especially the DMV's) you can use the following tool to help you understand more about how your indexes are being used. 

Database Tuning Advisor: Starting in SQL Server 2005, the Index tuning Wizard was replaced with the Database Tuning Advisor (DBTA or DTA). You need to be extremely careful here, more so than in almost any other tool in this list. It's very easy to simply run an index evaluation and take the recommendations blindly. I use the DMV's much more than the DBTA, but I have used it for an initial discovery run.

I've written about this tool here: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=280

SQL Server Query Performance Tuning Tools

Eventually you'll drill down to the actual queries that are causing the issues with the system. You'll need tools that will drill down into the queries to show you what they are doing. In addition to the information from the general tools, you have other resources available to you.

Query Statistics: A tool that is overlooked quite often in the development process is built in to SSMS. You simply enable the Query Statistics view, run your query, make any changes, and run it again, and then again. The Query Statistics view in SSMS will show you the overall timing for the query runs, along with the differences in time between each run. This shows you how each change impacted the overall performance of the query.

Just use the "Help" feature in Management Studio for the Query Options to learn more about this feature.

Query Plans: The Query Plan in SQL Server is the expected (in one view) and actual steps (by setting it a different way) that a query takes, along with the time it takes for each step. If you plan to do any serious query performance tuning, you need to know this tool along with the DMV's.

I've covered these here:  and there is another excellent reference here: http://www.red-gate.com/specials/Grant.htm?utm_content=Grant080623

Query Plan Explorer: Query Plan analysis can be difficult to get right, at least in a short amount of time. SQL Sentry has a free tool that makes this process far easier, and you can find out more about it here: http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp

sp_who and sp_whoisactive: SQL Server comes built in with a stored procedure called sp_who (and sp_who2) that can show a great deal of information about a given query, and since it's a stored procedure you can include it in code and pipe the output somewhere else. You can read more about it here: http://dotnetyuppie.com/2008/01/03/simple-sql-performance-monitoring-with-sp_who-and-sp_who2/

A far better version of this stored procedure has been created by Adam Mechanic. It's considered an essential part of any DBA's toolkit: http://sqlblog.com/tags/Who+is+Active/sp_5F00_whoisactive/default.aspx

The primary "overall" tools I've used for monitoring a SQL Server system are listed below:

Microsoft's System Center: http://www.microsoft.com/systemcenter/en/us/operations-manager/om-management-packs.aspx

Redgate's SQL Monitor: http://www.red-gate.com/products/dba/sql-monitor/

Quest's Spotlight: http://www.quest.com/spotlight-on-sql-server-enterprise/

All of these provide excellent support for a performance tuning exercise. Each has strengths that you can use in your situation, so I recommend you learn more about what they do and how they do it.

In the articles that follow, I will show you examples of each of these tools and how you can use them to create a performance tuning run.