SQL Profiler: A network trace for SQL Server
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in February 2010
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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation