DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQL Server Profiler: How It Works, Best Practices, and Tutorials

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 ...

Angela Stringfellow user avatar by
Angela Stringfellow
·
Aug. 31, 17 · Tutorial
Like (4)
Save
Tweet
Share
51.52K Views

Join the DZone community and get the full member experience.

Join For Free

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.

MySQL workplace Microsoft SQL Server Relational database IT Event

Published at DZone with permission of Angela Stringfellow, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Secure APIs: Best Practices and Measures
  • Java Code Review Solution
  • 5 Software Developer Competencies: How To Recognize a Good Programmer
  • Reliability Is Slowing You Down

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: