Recommended Tool: Express Profiler for SQL Server Databases

NOTE:  As I was writing up this post I discovered the news that SQL Profiler is deprecated as of the release of SQL Server 2016.  If this also affects the underlying SQL Server tracing APIs, then this news may affect the long-term future of the Express Profiler.  For now, however, it is a tool that I recommend.

Express Profiler is a simple Open Source alternative to the SQL Profiler that ships with the full SQL Server Management Studio.  This is particularly useful when working with SQL Server Express databases, as the Express version of the Management Studio does NOT include the SQL Profiler.

Usage of the Express Profiler should be self-explanatory to anyone familiar with the SQL Profiler.

Here are some details about Express Profiler from the project page:

  • ExpressProfiler (aka SqlExpress Profiler) is a simple and fast replacement for SQL Server Profiler with basic GUI
  • Can be used with both Express and non-Express editions of SQL Server 2005/2008/2008r2/2012/2014 (including LocalDB)
  • Tracing of basic set of events (Batch/RPC/SP:Stmt Starting/Completed, Audit login/logout, User error messages, Blocked Process report) and columns (Event Class, Text Data,Login, CPU, Reads, Writes, Duration, SPID, Start/End time, Database/Object/Application name) – both selectable
  • Filters on most data columns
  • Copy all/selected event rows to clipboard in form of XML
  • Find in "Text data" column
  • Export data in Excel’s clipboard format

While I have found Express Profiler to be a good and useful tool, it is not as fully-featured as the SQL Profiler.  Here are some key "missing" features in Express Profiler:

  • No way to load a saved trace output, although that feature is on the roadmap for the tool.
  • No way to save trace output directly to a database table.
  • Fewer columns can be included in the trace output, and many fewer events can be traced.  In my experience, however, the columns and events that I find myself using in most cases are all available.
  • As there are fewer columns in the output, there are fewer columns on which to filter.  Again, the most common/useful columns and events are covered.
  • No way to create trace templates for use with future traces.

Despite these limitations, I recommend this tool for situations where the full SQL Profiler is not available.

Advertisements

3 Responses to Recommended Tool: Express Profiler for SQL Server Databases

  1. Wade Harvey says:

    https://github.com/harvey007y/IdealSqlTracer is a free open source alternative to sql profiler.

    • mlichtenberg says:

      Does this tool capture login names, CPU, Reads, Writes, Duration, etc? Or is it strictly for capturing the SQL being sent to the server?

      Your YouTube description of the tool states that is allows a developer to “create customized traces that reduce a lot the noise that normally occurs when using SQL Profiler”. IMO, details such as Reads/Writes/Duration/etc are not “noise”, and can be very useful when tracking down the source of problems. Typically, the raw SQL is not the first place that I look.

      • Wade Harvey says:

        Thanks for watching the video. IdealSqlTracer just captures all of the sql and formats it so it is easily accessible. SQL Profiler gives you info on every sql statement that ran, but it puts the sql in an unformatted manner into rows in the output. This makes it very difficult for one to retrieve all of the sql that was generated behind a web page in a way that it can be easily browsed or executed. IdealSqlTracer only has this one function of grabbing all of sql that is generated and puts it into a formatted structure. SQL Profiler has many other functions that are indispensable for sql tuning, etc., as you mentioned.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: