Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning SQL Server: Tools and Processes
- Performance Tuning SQL Server: Tools Overview
- Creating a Performance Tuning Audit - Defining Components
- Creating a Performance Tuning Audit - Evaluation Part One
- Creating a Performance Tuning Audit - Evaluation Part Two
- Creating a Performance Tuning Audit - Interpretation
- Creating a Performance Tuning Audit - Developing an Action Plan
- Understanding SQL Server Query Plans
- Performance Tuning: Implementing Indexes
- Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 1
- Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 2
- Performance Monitoring Tools: Windows System Monitor
- Performance Monitoring Tools: Logging with System Monitor
- Performance Monitoring Tools: User Defined Counters
- General Transact-SQL (T-SQL) Performance Tuning, Part 1
- General Transact-SQL (T-SQL) Performance Tuning, Part 2
- General Transact-SQL (T-SQL) Performance Tuning, Part 3
- Performance Monitoring Tools: An Introduction to SQL Profiler
- Performance Tuning: Introduction to Indexes
- Performance Monitoring Tools: SQL Server 2000 Index Tuning Wizard
- Performance Monitoring Tools: SQL Server 2005 Database Tuning Advisor
- Performance Monitoring Tools: SQL Server Management Studio Reports
- Performance Monitoring Tools: SQL Server 2008 Activity Monitor
- The SQL Server 2008 Management Data Warehouse and Data Collector
- Performance Monitoring Tools: Evaluating Wait States with PowerShell and Excel
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Performance Monitoring Tools: An Introduction to SQL Profiler
Last updated Mar 28, 2003.
During the second phase of the performance tuning process, you need to be able to evaluate a system's components for baselines and performance tuning bottleneck evaluation. I've explained some of the tools you can use to watch the effects of an application. They show you what is happening on the various parts of a system, such as the CPU, Memory, I/O and NIC as an application runs.
To take this evaluation to the next level, you need to coordinate the code within the application to the metrics you're collecting. While that sounds pretty simple to do, sometimes applications are "black boxes" that don’t expose what they are doing. Microsoft provides a tool to show you the activity that is occurring on your system.
Once you learn how this tool works, you might be tempted to "reverse engineer" a commercial application without checking your license first. Your software license might actually prohibit this type of activity, so make sure you pull out that paperwork and take a look.
If you’re a developer, you need to know how what your application is doing in every situation. Often you'll find business logic five or six "if-then-else" layers deep, and you need to be sure what the application is asking SQL Server to do. The following process will also help you discover that information.
In this introduction, I'll use components that come with a native installation of SQL Server 2000. If you're using SQL Server 2005, you can substitute a simple query from the AdventureWorks database to trace its work.
To begin, I open the tool. To do that, I open the SQL Server item from the Start menu, and then select Profiler. It's under the SQL Server 2005 menu on that version.
That brings up the screen shown below:
As you can see, there’s nothing really going on here. The tool is waiting on you to start the process, and at a high level, that process has this general outline:
- Connect to the server
- Select the trace output
- Select the events to monitor
- Set the columns of data to capture
- Set the filters
- Start the trace
- Stop the trace
- Interpret the output
Let’s take a detailed look at each of these steps. For this article, I’ve written a very simple application in C# that hits one of the standard insert stored procedures in the Northwind sample database. What I’ll show you here is the stored procedure that runs and the parameters that are passed.
When you run the Profiler, you create a trace. A trace contains events, which are categories of things that can happen on a SQL Server. You can limit the data you look at by setting columns and filters. I’ll show you all of these constructs in this introduction. The example you’ll see is quite simplistic, but the process holds true for the most complex application.
Connect to the server
First I’ll need to start the trace definition by selecting the trace output. Notice in the screen below that I’ve selected New... and then Trace from the main menu:
Once I choose that option, I’m shown this panel:
In this section I provide the same credentials that I use with Query analyzer or any other SQL tool. You can see that I’m running this query against my own system. I then select OK to continue.
Select the trace output
Now that I’ve connected to the server, I need to set the location for the output. The choices I have available are shown below:
I’m just using this tool to find a simple issue, so I don’t need the output anywhere except the screen. If I were troubleshooting a more complex problem, I’d consider sending the output to a file. If I were monitoring for performance, I would send the output to a SQL table, so that I could slice and dice the data more efficiently later. Of course, I’d have to take into account the effect of those inserts on the performance to accurately form a baseline.
Notice that you can also set the trace to end after a certain period of time. But if you’re going to do that, I recommend you search the links section on the SQL InformIT site on using command tools to run a trace.
After I’ve made my choices here, I select the Events tab.
Select the events to monitor
In this part of the process I select the events to monitor. Now you might think that I should select a database first, but in fact I’m monitoring the server, not a database. The events that I choose from (which you can see here) may have nothing to do with a specific database. There are events that are server specific, and others that have to do with the server’s environment. In fact, there are so many events that finding the things to monitor represents the single largest challenge to using this tool. Take a look at this screen:
I’ve removed everything other than the events you see here. These are all I need of the application. You may need to add more if you’re not seeing the results you expect. If you’re not seeing the output from your application then re-run the trace and add more TSQL events, then Transactions, then Stored Procedures items. I always start with the minimum so that I don’t have to wade through a lot of data.
Set the columns of data to capture
Next I select the Data Columns tab, as shown below:
The events are similar to objects in the Windows Performance Monitor. They are containers for the actual counters the event measures. You can limit the data you see by collecting the columns you care about. I take the same route with the columns that I did with the events — less is more. The important bit for me here is the Text item — it contains most of what I’m looking for. It tells you what statements SQL is hearing.
Set the filters
Finally I click the Filters tab, as shown here:
I’ve filtered the Profiler that I’m running to just a few items; otherwise, I receive a glut of information I don’t need. I'll cover these items further in other tutorials.
This tab also lets you choose an application name to monitor – but be warned that what Windows reports as a running application isn’t necessarily what SQL reports as the name. I’ve found it safer to filter the database name, and then limit who’s doing what on that server. You can see that below:
Start the trace
I’m all done with what I want to see; now it’s time to start the trace. I click Run, then I go over to the workstation to start the application.
It’s important to keep this sequence, because applications normally run really fast, so if you start the application first, then you might miss the problem.
Stop the trace
Once the application generates the error, I wait a moment and then stop the trace using the red box icon in the tool bar.
Interpret the output
Finally the payoff — after I stop the trace, I arrow through the lines of data until I find the text output of the application. We'll follow this process in greater depth in the next steps on evaluation.
In this case, I see the application error. My application isn’t sending the second parameter to SQL Server, and so I try to insert @P1 into an integer field in the database. That’s not going to work very well, so I make the change in my code and everything works.
This example is a bit simplistic, but it illustrates the process to follow to debug an application, or just to monitor what it’s sending to the database. The key is to limit the data to just what you need, while making sure you get everything that is pertinent.
To take your Profiler skills to the next level, make sure you visit the links in the resources section on this site. There are quite a few articles out there on Profiler, and many of the books on Safari also talk about the tool.
Informit Articles and Sample Chapters
SQL Profiler is a sample chapter from Essential SQL Server 2000: An Administration Handbook (Addison Wesley, 2001, ISBN 0201742039), by Buck Woody. You can use Profiler to track SQL statements for troubleshooting, or even monitor the behavior of the server engine that you normally can’t see. In this excerpt from his book, Buck Woody provides an overview.
Using SQL Profiler to Find Errors - Sample Chapter By Rob Hawthorne. In this excerpt from SQL Server 2000 Database Development From Scratch, author Rob Hawthorne tells you how SQL Profiler, as a problem-solving tool, can show you exactly what has been sent to the SQL Server by a client application.
SQL Server: Blocking Problems - Article By Kevin Kline, Baya Pavliashvili. If your users complain about applications being slow, you may have blocking problems. This article shows you how to determine the causes of the blocking and how to resolve them.
Books and e-Books
Microsoft SQL Server 2000 Optimization Guide by Jenney Fields.
Microsoft SQL Server 2000 Unleashed, By Ray Rankins, Paul Jensen and Paul Bertucci has more information on SQL Server Profiler.
There's a great resource that shows another look at SQL Server Profiler here.