5

Context: Careful DBAs

We've been bought out a few times and our latest overlords have DBAs who keep carefully guard their production database servers.

Overall this is good.

However, we have run into one point of contention between developers and DBAs: they will not run traces against production database servers.

Our experience as developers: SQL Server trace + RML Utilities rocks

In the past, when application instances ran sluggish, we would run a trace via an .sql script, zip up the output, and process it with Microsoft's RML Utilities.

Note that we do not run the GUI-based profiler tool as that dramatically slows down performance (the database needs to wait on updating the GUI). These traces simply "log activity" to the database server's local filesystem. Our scripts run a trace for a prescribed number of minutes at a time (for example, 10 minutes).

The combination of trace and RML Utilities works great: The report is easy to use. It shows hotspots (see below), the reports give helpful information, they allow drilling down to the actual execution plans, etc. It's much better than anything Oracle offers (or at least a few years ago).

(Hotspot: if a query takes 1/10 second, but executes 400,000 times, it wil appear higher in the report, higher than the once-executed query that takes 30 seconds.)

I've done a bit of performance work over the years. My experience is that capturing 'actual data' from 'real databases under real load' trumps programmers 'taking a guess at' and simulating the problem they imagine. This is why I think that the SQL Server trace is pretty much 'silver bullet' for killing the problem.

My Questions

  • What are the main, legitimate concerns against running SQL Server traces in production (filling up the disk, slowing down performance, etc.)?
  • Are these concerns valid? If so, how can we address them?
  • Taking a trace and profiling the "actual problem" seems like the clearest step toward understanding and solving the situation. What are the alternatives?
Improve this question
5
  • 3
    (1) trace is deprecated, so it will simply cease to exist as an option at some point (2) Extended Events = lower overhead in most cases, or same in a couple (e.g. post-execution showplan). Trace isn't the devil, but it can often be the problem rather than help you find a problem - if you're trying to solve a performance problem that's making your box run slow, adding a trace on top of that is likely to make it worse. Key is always going to be filtered & targeted - bare minimum columns you need, filtered to sessions/database/application etc.
    – Aaron Bertrand
    Jul 30, 2015 at 15:13
  • Considering SQL Server continuously runs a trace in the background I'd say trace definitely has its place, when done correctly.
    – Hannah Vernon ♦
    Jul 30, 2015 at 15:14
  • 1
    As Aaron just pointed out, Extended Events are an excellent alternative.
    – Hannah Vernon ♦
    Jul 30, 2015 at 15:14
  • 2
    "You won't run a trace? That's fine. So, the users are waiting for the queries to complete so they perform their jobs. What alternative do you propose to identify the hotspots? " They may come back and say XE but I've often found that in larger organizations, it's the monkey banana spray scenario - someone set a rule once and no one left knows why the rule is there but it's the rule
    – billinkc
    Jul 30, 2015 at 15:31
  • We know why, the default RML template contains the SQL statement level tracing events which could easily bring a server to its knees under the wrong conditions, for example a scalar function in a SELECT statement running across a big table.
    – wBob
    Jul 30, 2015 at 15:34

2 Answers 2

Reset to default
6

The latest version of the RML Utilities (09.04.0051 as at today) support Extended Events tracing, so maybe you could work with your DBAs to do some controlled tracing. A screenshot from the help file: enter image description here

They do however have a point because the default trace template provided with RML includes statement-level events (SQL:StmtStarting, SQL:StmtCompleted, SP:StmtStarting, SP:StmtCompleted), ie every statement in every proc. This could easily bring a server to its knees under the wrong conditions, eg scalar function in a SELECT statement running across a big table.

Extended Events (XE) were a bit limited in versions prior to 2012, but it is more lightweight. You still need to be careful as SQL statement-level events will still incur overhead for very intensive workloads (sqlserver.sp_statement_starting, sqlserver.sp_statement_completed, sqlserver.sql_statement_starting, sqlserver.sql_statement_completed). Execution plan capture (sqlserver.query_post_execution_showplan) can also be intensive. Consider using the XETraceCaptureDefLightWeight.sql template which is provided and includes less events.

In summary I suggest moving to XE and using the lightweight XE definition provided with the latest versions of RML.

Improve this answer
2

we have run into one point of contention between developers and dba's: they will not run traces against production database servers.

It depends on what you asking your DBAs to trace (I am only talking about server side trace).

We have a 24x7 trace running which captures minimal stuff which helps us in troubleshooting specific scenarios e.g. User error messages, logins ,applications which can be useful when we want to move the database to a different server or when we want to decommission a server, etc

Being a DBA, I would not run a trace that will capture execution plans, statement level executions, etc. These events can bring down your server. Instead you can use sp_whoisactive - log it to a table or use Glenn's DMV queries using tsql agent job on a regular schedule. This will help baselining your server and spot anomalies easily.

In his blog post Measuring “Observer Overhead” of SQL Trace vs. Extended Events - Jonathan Kehayias concludes

For systems running on SQL Server 2012, Extended Events provide the least amount of overhead and provide similar capabilities for events and columns as SQL Trace.

So if you are using SQL Server 2012 and up, I would suggest to migrate all your traces to equivalent Extended events (this is what we are in the process of implementing).

What the main, legitimate concerns against running MSSQL Traces in production? (Filling up disk, slowing down performance, etc)

No, the main concern is what events have you configured to being trace. Also, you can have a job that puts all your trace files to a different server and you do processing of trace files using RML utility away from PROD server. You can even have a WMI alert that will fire and push that trace file on a different server.

Are these concerns valid? If so, how can we address them

Disk space is a concern too if you are not specifying an upper limit on how big will be the file and rollover time. The trace files can get quiet bigger if an upper limit MB is not specified and you dont rollover after certain duration.

Taking a trace and profiling the "actual problem" seems like the clearest step toward understanding and solving the situation. What are alternatives?

As Aaron and wBob has mentioned, start making a leap from Profiler (server side trace) to XEvents (Erin's PASS 2014 video).

As a side note, for analyzing trace data, another alternative is to use SQLNexus or ClearTrace

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.