Handy Tool – ClearTrace

I’ve been using an awesome tool for a while, and I haven’t heard a lot of #sqlfamily  talk about it so I knew I had to put out a blog about it. The tool I’m talking about is ClearTrace and it’s used to normalize and aggregate the data from a SQL Profiler trace. I know, I know, we should all be using XE (Extended Events), sorry Erin Stellato(t), but I still deal with a lot of customers who have SQL 2008/R2 instances, so this makes my life a lot easier.

This tool makes it really really easy to analyze trace data and find out where your resources are being spent.

We’ll get a trace set up against the AdventureWorks2012 database using the following trace properties…

Trace File Properties

And here is the sample workload we’ll execute while the trace is running.

EXECUTE [dbo].[uspGetBillOfMaterials] @StartProductID = 680 ,
@CheckDate = '1/1/2005'
GO
EXECUTE [dbo].[uspGetBillOfMaterials] @StartProductID = 722 ,
@CheckDate = '1/1/2005'
GO
EXECUTE [dbo].[uspGetBillOfMaterials] @StartProductID = 723 ,
@CheckDate = '1/1/2005'
GO
EXECUTE [dbo].[uspGetManagerEmployees] @BusinessEntityID = 5
GO
EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 5
GO
EXECUTE [dbo].[uspGetManagerEmployees] @BusinessEntityID = 55
GO
EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 55
GO
EXECUTE [dbo].[uspGetManagerEmployees] @BusinessEntityID = 150
GO
EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 150
GO
EXECUTE [dbo].[uspGetManagerEmployees] @BusinessEntityID = 1
GO
EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 1
GO

Once the  SQL is done executing, save the trace file.

Trace File Save

Now we’ll move on to the fun stuff.

ClearTrace001
Open up ClearTrace and bowse out to select the trace file you just saved.

ClearTrace003

And select the Import Files button. Note: If your trace consists of multiple files due to file size cutoff, ClearTrace will import all of them if they’re in the folder.

Once the trace file has been imported, you will see the 3 stored procs that were executed, along with the count of executions and aggregations of CPU, Reads, Writes, and Duration. If you’d like to see averages, check the box.

 

ClearTrace004
Batches

 

 

This info is very helpful, but we’re looking at Batches and since stored procs can contain a lot of code, it’s helpful to drill into the individual statement executions. When we change the event selection to Statements, we get a more granular view into our trace data

ClearTrace005
Statements

At any time when looking at Batches or Statements, we can double-click on a cell and get the Text Data Detail.

 

ClearTrace006
Text Detail

 

I hope you found this post helpful, I look forward to doing a few more HandyTools blog posts.

 

One thought on “Handy Tool – ClearTrace

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s