SQL Server – Using SQL Profiler on a Production Database

profilersql server

As a developer, I use SQL Profiler quite often. It's a good debugging tool, both to track what my code is doing and to analyse performance problems.

But I've always used it on my development environment, and in a very controlled way.

Start my application, and get it into a specific state
Start a trace on the profiler
Perform a specific sequence of actions on my application
Stop the trace and examine the results.

Can the SQL Profiler be practically used in an in-production environment?

My first concern is that it would degrade the performance.

My second concern is that, because it's in production, you aren't triggering the interesting actions itself. You would have to leave the profiler running for a long period then analyse the results. Would the result set become too unwieldy? (Taking up too much disk space and being too hard to query).

Does anyone use the SQL Profiler in production?

Best Answer

Using Sql Server Profiler (GUI tool) to trace a production server is not a good idea. But it depends on load. Use server-side sql tracing (see sp_trace_XXX procedures) instead of it. Also I have found articles:

Performance Impact: Profiler Tracing vs. Server Side SQL Tracing,

Automating Server Side Tracing in SQL Server

Avoid Causing Problems with Profiler

maybe it will be interested and useful.

Book Online says:

Run Profiler remotely instead of directly on server
Avoid including events that occur frequently (e.g. Lock:Acquired) unless absolutely needed
Include only event classes needed
Specify limiting filters to reduce the number of events
Avoid redundant data (e.g. SQL:BatchStarting and SQL:BatchCompleted)
Avoid running large traces with Profiler; consider a server-side SQL Trace instead
Limit server-side trace file size and manage space usage

Related Solutions

Sql-server – How to analyse a set of SQL Profiler-generated rolling trace files

Laerte Junior has a nice article on how to read these using Powershell. Go down to the "Reading Multiple Trace Files and Insert into SQL table." section for the exact statement.

Sql-server – Slow SSRS Report in production

You've either got a slow running query or blocking/deadlocking going on. To see blocking, you could run this query:

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255), 
      BlkBy  VARCHAR(255),DBName  VARCHAR(255), 
      Command VARCHAR(255),CPUTime INT, 
      DiskIO INT,LastBatch VARCHAR(255), 
      ProgramName VARCHAR(255),SPID2 INT, 
      REQUESTID INT) 
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      * 
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       DBName <> 'master'
-- Add any sorting of the results here :
ORDER BY    DBName ASC

DROP TABLE #sp_who2

Look for anything in the BlkBy column and you can see what session is blocking your queries.

You didn't mention a deadlock error, which you should see in SSRS logs. I'd take a close look at those to see the exact error and proceed with that knowledge. It's down in the log dir of the SSRS install.

SSRS will have timeouts set for it, and you may be hitting those times, but your SSRS log should tell you if it's a application timeout or a deadlock.

Best Answer

Related Solutions

Sql-server – How to analyse a set of SQL Profiler-generated rolling trace files

Sql-server – Slow SSRS Report in production

Related Question