SQL Server Profiler: How It Works, Best Practices, and Tutorials

If you work with SQL, you understand the importance of being able to monitor your queries, not to mention how critical it is to have an accurate analysis of how long queries take. Today, we'll take a look at SQL Server Profiler, a tool for doing just that, how it works, some shortcomings (most notably, depreciation of its features), and alternatives.

What Is SQL Server Profiler?

It's a tool for tracing, recreating, and troubleshooting problems in MS SQL Server, Microsoft's relational database management system (RDBMS). The profiler lets developers and database administrators (DBAs) create and handle traces and replay and analyze trace results. In a nutshell, it's like a dashboard that shows the health of an instance of MS SQL Server.

While it's a robust tool, many features are being deprecated by Microsoft. This is happening because most developers and DBAs feel that a server-side trace is a more robust option.

How Does SQL Server Profiler Work?

It works by giving DBAs and developers a high-level view of the operation of a system. Users create traces to capture data and monitor errors and other problems. They then use the profiler to store, retrieve, and view the results of many traces graphically for purposes of troubleshooting and repair. This function all happens on the client-side, meaning it uses resources on the same machine it's monitoring.

To start the tool, go to the Start menu > All Programs > Microsoft SQL Server 2016 > Performance Tools > SQL Server Profiler.

Benefits

Below is a short list of the benefits of this tool to both developers and DBAs. For a complete list, reference this document from Microsoft.

• Clarity.It can reveal how an instance works when it's interacting with a client.
• Troubleshoot problems. It can help zero-in on trouble spots by allowing us to capture and replay key events. This function also helps with stress testing and identifying slowly executing queries.
• Allow non-administrator users to create traces securely. It can cater to the needs of DBAs, developers, database designers, business intelligence specialists, IT professionals, and even accountants.
• Compare activity to baselines. It lets users save trace data and compare it to newer data to spotlight new trouble spots.
• Capture traces for Transact-SQL, SSIS, and Analysis Services.

Alternative Profiler Tools

Though popular, it's not the only way to trace and monitor an SQL server. The following alternatives perform a similar function.

• Prefix. One of the great things you can do with Prefix is view SQL queries right from your application code for insights that aren't just accurate but also in context. It's free, incredibly robust, and provides a detailed trace of every web request.
• ExpressProfiler. ExpressProfiler is an alternative that uses a simple, fast graphical user interface.
• Neor Profile SQL. Neor Profile SQL provides similar features for MySQL users.
• Server Side Trace. A server-side trace is considered superior because client-side traces may drain enough local resources to skew results.

As you can see in the screenshot above, Prefix provides a detailed performance view that offers a variety of insights including:

• The number of times a query is executed (shown by the dark blue vertical bars).
• The average time it took (light blue shading).
• The total number of calls.
• Average time.
• Number of requests per minute.

Benefits of Server-Side Trace

Traces may actually use enough client-side resources to skew the results. Server-side traces are considered superior because they don't use client-side resources. A server-side trace requires less overhead and performs more accurate monitoring and reporting.

Extended Events is Microsoft's replacement for the profiler. Extended Events happens all on the server side, performing tracing, logging, and other functions efficiently.

For more info on server-side traces and Extended events, see the following:

• This Stack Overflow discussion on Server Profiler vs. Extended Events.

• This post from SQLPerformance.com.

• This MS introduction to Extended Events.

Best Practices

Use this short list of best practices for efficiency. For a more comprehensive list of best practices, see this blog post.

• Use filters to limit the volume of data in your trace. This is especially true with frequently recurring events.
• Don't capture every single event. It's easy to make a trace table so huge that any useful info in it becomes a needle in a haystack.
• Don't add redundant events. Redundant events are another way to gather too much data and make troubleshooting more difficult.
• Run it on a remote system. It can eat up a lot of memory, so running it on a remote system is always best.
• Create templates. While Microsoft does offer a selection of trace templates, it's a good idea to keep a toolbox of ready-to-customize templates on hand for issues that crop up and require immediate attention.

Additional Resources and Tutorials

This post is an introduction to the profiler. The following tutorials provide a more in-depth look at how to use it in practice:

• Microsoft’s Tutorial

• MS SQL Server Profiler Basics video tutorial

• Step by Step

• How to Use an SQL Server Profiler to Create a Trace

• Download the 307-page e-book Mastering SQL Server Profiler by Brad McGehee.