Ever wonder what's going on under the hood in SQL Server? Need to see what queries an application is really submitting?...
Want to tune your database indexes based on real-world activity and usage patterns?
Then you should get to know SQL Profiler, a utility included with the management tools of any full edition of Microsoft SQL Server. You can install these tools on almost any Windows-based client computer and use Profiler to connect to any SQL Server on your network -- provided you have profiling permissions on the server, of course.
SQL Profiler captures events, which can include query stop and start notices, the complete text of every query being executed, information on query compilation, and tons more. It's kind of like a network monitor for SQL Server where you'll capture every bit of traffic, and then have the ability to filter through and look for whatever it is you want.
Some SQL Profiler practices
First off, SQL Profiler should never be run on the computer you're actually profiling. You should run Profiler from a client computer and connect, across the network, to the SQL Server you want to profile.
SQL Profiler can save its traces to a file or database. In the case of a file, I usually set a maximum file size of 250 MB and have SQL Profiler automatically roll over to a new file as needed. This keeps the files small enough (especially when zipped) to move around later if needed. When capturing to a database, make certain that the database isn't on the server you're profiling. In other words, you'll need two SQL Server computers: one to profile and one to capture to. I usually just capture to a file, as files are easier to archive and don't require any additional infrastructure. Frankly, I think they're faster, too – Windows is awesome at writing data to files, and there's no "middle layer" of a database server in the way.
Capture as few events as possible to keep the trace log more manageable. SQL Profiler has several predefined capture templates, such as a standard one for troubleshooting queries (it's great for seeing what queries an application is submitting to SQL Server), and another for tuning (which I'll discuss in the next section). Use these templates if possible, and create new templates if you have specific capture needs that aren't met by an existing template.
SQL Profiler for performance
Every SQL Server administrator in the universe – even ones who only do it part-time because nobody else can – should at the very least be using SQL Profiler to tune database performance.
Here's how it works. First, you should point Profiler at a SQL Server and capture traffic using the Tuning template. Try to capture a solid-sized chunk of data that's really representative of your actual, real-world usage of SQL Server. For example, if your company has an order-entry application that gets hammered every day around the time Oprah is on television, that is the perfect time to run SQL Profiler.
Next, take the resulting trace file and feed it to another SQL Server utility -- the Database Engine Tuning Advisor (it has other names, like Index Tuning Wizard, in earlier versions of SQL Server). The Advisor looks at that real-world traffic and asks SQL Server for execution plans on each query it sees. It then tries to figure out how it would make all of those queries, in aggregate, run more efficiently. In most cases it will suggest adding, dropping, or modifying indexes – and it will be happy to implement its own suggestions for you (like to back up the database first, please).
SQL Profiler for troubleshooting
Developers often have a difficult time troubleshooting database applications that build dynamic queries or call stored procedures. That's because, at run-time, it's very tricky to figure out exactly which values are being fed into queries or stored procedure parameters. With SQL Profiler, however, it's easy. You can capture traffic and show developers the exact final query that was sent to SQL Server, along with procedure parameters and other information. Developers with a copy of SQL Profiler can easily load trace logs that you've captured (meaning they don't necessarily need to be given the server-wide permission to use SQL Profiler; you can do it for them) and analyze their application's activity.
Profiler is a hugely useful tool that takes just a bit of effort to learn – effort that can make you a database superhero in the end.
Miss a tip? Check out the archive page for Don's series, SQL Server for the Reluctant DBA.
ABOUT THE AUTHOR:
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at www.ConcentratedTech.com.