posts - 230, comments - 424, trackbacks - 27

My Links



Follow billgraziano on Twitter

Article Categories


Post Categories


SQL Server


ClearTrace Build 51 Available

I’m pleased to announce that ClearTrace Build 51 is now available for download.  If you’re using a previous build (especially build 50) I encourage you to download this version. This build should load the latest version of the SMO libraries installed on your machine.  If you’re using SQL Server 2016, you need the SQL Server engine AND SQL Server Management Studio installed.  I believe this is the only way to get the complete SMO library.  This also means that when SQL Server 2036 is released it will automatically support it – and hopefully all the releases between now and...

posted @ Tuesday, July 12, 2016 5:44 PM | Feedback (0) | Filed Under [ ClearTrace ]

ClearTrace Build 47 Available

You can download the latest version of ClearTrace from my consulting site: scaleSQL Consulting.  I’ve decided to stop using the year as part of the version.  I did that to match SQL Server branding but it doesn’t work for ClearTrace.  The current build supports SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014.  It tries to load the most recent SMO and goes backwards until it successfully loads an SMO library.  Always use the most recent version of ClearTrace and it should support any current SQL Server. If you’re having issues with...

posted @ Sunday, July 05, 2015 11:06 PM | Feedback (0) | Filed Under [ ClearTrace ]

ClearTrace for SQL Server 2012

I’ve updated the beta for ClearTrace that support SQL Server 2012.  This requires SQL Server 2012 to be installed on the computer where ClearTrace is running.  It will read traces from SQL Server 2008 R2, SQL Server 2008 and SQL Server 2005. It includes some minor improvements in performance and handling large SQL statements. It should also give better errors. If you do find any of those errors, please report them in the support forum.

posted @ Tuesday, June 12, 2012 7:23 AM | Feedback (1) | Filed Under [ ClearTrace ]

ClearTrace Performance on 170GB of Trace Files

I’ve always worked to make ClearTrace perform well.  That’s probably because I spend so much time watching it work.  I’m often going through two or three gigabytes of trace files but I rarely get the chance to run it on a really large set of files. One of my clients wanted to run a full trace for a week and then analyze the results.  At the end of that week we had 847 200MB trace files for a total of nearly 170GB. I regularly use 200MB trace files when I monitor production systems.  I usually get around 300,000...

posted @ Thursday, April 26, 2012 7:22 AM | Feedback (0) | Filed Under [ ClearTrace ]

ClearTrace Beta for SQL Server 2012

I have a beta version of ClearTrace that supports 2012.  You can download it at  Please let me know if you find any issues.

posted @ Wednesday, April 25, 2012 8:10 AM | Feedback (0) | Filed Under [ ClearTrace ]

TraceTune display Duration in milliseconds. Finally.

SQL Server 2008 started reporting the statement duration in microseconds in scripted traces.  That’s one millionth of a second.  Previously they were reported in milliseconds.  Profiler automatically corrects for this and always displays in milliseconds regardless of the version of SQL Server that generated the trace.  However SMO reported them in milliseconds or microseconds depending on what version of SQL Server generated the trace.  Worse, SMO has no way to tell you what version of SQL Server generated a trace.  Or what unit of measure was used to return the value. That left me reading traces with no way...

posted @ Tuesday, July 05, 2011 9:20 AM | Feedback (2) | Filed Under [ ClearTrace ]

TraceTune shows Reads graphically

TraceTune now shows a graphical view of logical reads for each SQL statement in a trace file.  The width of the colored bar in the screen capture below is the percentage of logical reads for that statement.  The absolute number of reads is shown to the right. Any statement that has a user entered comment is shown in bold.  If you hover over the statement it will show the most recent comment for that statement.

posted @ Monday, June 27, 2011 9:00 AM | Feedback (0) | Filed Under [ Utilities ClearTrace ]

TraceTune supports uploading Zip files

I’ve been using the online version of ClearTrace more and more lately.  When I get to a new client it’s just much easier to upload a trace file rather than install ClearTrace. That means I’ve finally been adding more features to it.  The two latest features are around ease of use. You can now upload a ZIP file that contains a trace file.  Trace files are already somewhat compressed.  Putting it in a ZIP file further compresses it by a factor of 8X or 9X in my testing. That means you can start with a 100MB trace...

posted @ Thursday, June 23, 2011 7:46 AM | Feedback (0) | Filed Under [ Utilities ClearTrace ]

ClearTrace Shows Execution History

The latest release of ClearTrace (Build 38) now shows the execution history of a particular statement. You’ll need to save the trace files to a trace group instead of just using the default.  That’s as easy as typing something into the trace group name when you upload the trace.  I usually put the server name in this field. Build 38 also re-enables support for statement level events.  If your trace includes RPC:StmtCompleted or SQL:StmtCompleted events those will be processed and save.  In the results tab you can choose to view statement level or batch level events.  ...

posted @ Monday, June 06, 2011 9:00 AM | Feedback (0) | Filed Under [ ClearTrace ]

How are Reads Distributed in a Workload

People have uploaded nearly one millions rows of trace data to TraceTune.  That’s enough data to start to look at the results in aggregate.  The first thing I want to look at is logical reads.  This is the easiest metric to identify and fix. When you upload a trace, I rank each statement based on the total number of logical reads.  I also calculate each statement’s percentage of the total logical reads.  I do the same thing for CPU, duration and logical writes.  When you view a statement you can see all the details like this: ...

posted @ Monday, February 21, 2011 9:00 AM | Feedback (0) | Filed Under [ SQL Server Stuff ClearTrace ]

Enter comments on queries in TraceTune

I’m trying to make TraceTune (and eventually ClearTrace) work the way I do.  My typical query tuning session goes like this: Run a trace and upload to TraceTune/ClearTrace Tune the slowest queries Goto 1 I might do this two or three times in one day and then not come back to it again for weeks or even months.  This is especially true for those clients that I only visit a few times per month.  In many cases I’ll look at a query, decide I can’t...

posted @ Monday, February 07, 2011 7:48 AM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ClearTrace ]

ClearTrace Supports SQL Server 2008 R2

It was a long time coming and I hope worth the wait.  If you have SQL Server 2008 R2 installed on the same box as ClearTrace and you download the latest ClearTrace build (36) you’ll be able to read SQL Server 2008 R2 traces.  I also fixed a bug handling very, very large SQL statements.  I encountered an INSERT statement that was 12MB in size.  It was storing XML in a database.  ClearTrace uses regular expressions to clean up the SQL it finds.  Running two dozen regular expressions over this 12MB string caused the application to crash.  In...

posted @ Friday, January 07, 2011 11:51 AM | Feedback (0) | Filed Under [ ClearTrace ]

An online version of ClearTrace

When I visit clients for the first time and conduct a performance review I introduce them to ClearTrace. It’s still the best way I know to identify exactly which queries are consuming the most resources.  The downside is that it needs to be downloaded and create a database to store the results.  I finally decided it would be easier if I could just upload a trace immediately. You can find the online version of ClearTrace at  It provides a simple way to upload a trace file and see exactly which stored procedures or SQL statements consume the most...

posted @ Friday, January 07, 2011 9:05 AM | Feedback (0) | Filed Under [ ClearTrace ]

ClearTrace Supports Statement Level Events

One of the requests I get on a regular basis is to capture the performance of statement level events.  The latest beta has this feature available.  If you’re interested in this I’d like to get some feedback. I handle the SP:StmtCompleted and the SQL:StmtCompleted events.  These report CPU, reads, writes and duration. I’m not in any way saying it’s a good idea to trace these events.  Use with caution as this can make your traces much larger. If there are statement level events in the trace file they will be...

posted @ Friday, March 12, 2010 7:26 AM | Feedback (0) | Filed Under [ ClearTrace KCTechBlog Syndication ]

ClearTrace 2008.34

It seems like all I post anymore is ClearTrace releases.  I guess that’s not a bad thing. The next build is available. This is the full release of the multi-user functionality.  In earlier versions all I cared about was performance.  I did everything I could to make it as fast as possible.  I had just discovered SqlBulkCopy and it was my new hammer.  And all of ClearTrace looked like a nail.  The application would use SqlBulkCopy to load a summary of each trace file as it was read into memory.  All the dimensions I created (application, login, SQL statement,...

posted @ Wednesday, May 27, 2009 7:04 AM | Feedback (3) | Filed Under [ ClearTrace ]

ClearTrace Multi-User Beta

I originally envisioned ClearTrace as a service that could run in the background and process trace files.  As I wrote more and more code the service and command-line versions weren’t kept up to date.  I kept adding more and more caching to improve performance.  That had the unfortunate side effect of not working if two copies of ClearTrace were processing against the same database.  With the return of the command-line version I finally had to fix this. You can download a beta of ClearTrace build 32 and test the fix.  The ZIP file includes both the GUI and command-line...

posted @ Monday, May 18, 2009 7:13 AM | Feedback (0) | Filed Under [ ClearTrace ]

ClearTrace now available with a command-line version

This is the first release of the command-line version.  If you have any feedback, please post in the ClearTrace support forum on This provides all the trace loading features of the GUI.  You’ll still need the GUI in order to query the results though.

posted @ Friday, May 15, 2009 10:07 AM | Feedback (0) | Filed Under [ ClearTrace ]

ClearTrace now supports SQL Server 2005 *AND* SQL Server 2008

Or my alternate SEO-friendly title, “Dynamically load the proper SQL Server SMO Trace library in C#”.  I just posted a version of ClearTrace that will determine whether you have SQL Server 2005 or SQL Server 2008 installed and dynamically load the proper SMO library.  Previously I had one version of ClearTrace for each version of SQL Server (YUCK!).  I’m pretty happy with how this works even though I’m not very happy with SMO.  The method names are the same between the two versions of SMO but they aren’t both derived from the same interface.  That...

posted @ Friday, April 03, 2009 7:35 AM | Feedback (0) | Filed Under [ Utilities ClearTrace ]

ClearTrace updated to support SQL Server 2008 trace files

It was a long time coming but I finally updated ClearTrace to read SQL Server 2008 trace files.  This requires SQL Server 2008 to be installed on the computer where ClearTrace is running.  ClearTrace will process traces created in SQL Server 2000, SQL Server 2005 and SQL Server 2008.  ClearTrace is the tool I wrote to make performance tuning easier.  Many of my client engagements are to make SQL Server faster and ClearTrace is the tool I use to do that.  ClearTrace will read a series of trace files and aggregate the results so you can tune the...

posted @ Sunday, March 29, 2009 12:07 PM | Feedback (5) | Filed Under [ SQL Server Stuff Utilities ClearTrace ]

ReadTrace for SQL Server 2005

Microsoft just released an updated Read80Trace that works on SQL Server 2005.  The name is simplified to just ReadTrace.  ReadTrace is a command-line utility that analyzes a SQL Server trace file and summarizes performance.  The new version has a more graphical interface.  The original was one of the most helpful performance tuning utilities I've ever used.  I expect this one will be just as useful. I typically use the tool to summarize performance by SQL statement.  ReadTrace will "normalize" the SQL statements by replacing constants with placeholders.  This lets you identify which SQL statements are using...

posted @ Wednesday, December 19, 2007 7:59 PM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ClearTrace ]

Read90Trace coming soon!

Bob Ward announced in his PASS pre-conference session that Microsoft will be releasing a SQL Server 2005 version of Read80Trace in roughly 30 days.  It's currently going through a security review.  They're planning to call it ReadTrace instead of Read90Trace though.  I don't know what this will do to my plans for more work on ClearTrace.  I'll have to wait and see exactly what they release.

posted @ Sunday, September 23, 2007 10:20 PM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ClearTrace ]

ClearTrace 2.0.27 Released

I posted another version of ClearTrace just a minute ago.  This should process 64-bit trace files on either x86 or x64 machines.  If you have trouble please post in the ClearTrace Support Forum.  Please visit the ClearTrace download page for the software.  Don't forget to enter the beta code "jayhawk" to unlock the capability to save an unlimited number of traces.

posted @ Friday, July 20, 2007 11:28 AM | Feedback (0) | Filed Under [ ClearTrace ]

Saving Multiple Traces in ClearTrace

Back when I actually had time to work on ClearTrace I had visions of selling the software.  I don't think that's going to happen anytime soon.  I don't have time and there's a bug in SMO that causes problems with traces generated on 64-bit servers.  In the meantime, there is additional functionality in the software that people can take advantage of.  Under Tools -> Options you can enter "jayhawk" in the Beta Code field.  This will allow you to create multiple trace groups.  You can use trace groups for anything you'd like.  You could create one per server or...

posted @ Monday, July 09, 2007 11:04 AM | Feedback (0) | Filed Under [ ClearTrace ]

ClearTrace Video

I created a small video to demo ClearTrace. It's only about two minutes long. This is also the technology we're planning to use at the PASS Conference to capture presentations.

posted @ Friday, July 14, 2006 1:03 PM | Feedback (1) | Filed Under [ SQL Server Stuff PASS ClearTrace ]

Yet Another ClearTrace Update

These titles seem to be getting repetitive. I updated ClearTrace again. I'm also back to using a ClickOnce installer. Unfortunately if you switch from an unsigned to a signed ClickOnce installation the existing people can't upgrade. How annoying! That was half the reason I switched to an MSI installer. Creating the package just took too much time when I wanted to release an update. It is just so easy to click the Publish Now button and be done with it. I recently added a feature to capture some anonymous performance data. I'm trying...

posted @ Monday, July 10, 2006 8:47 AM | Feedback (0) | Filed Under [ SQL Server Stuff ClearTrace ]

ClearTrace Updated

I've been updating ClearTrace for a while now and there's a new version available.  I stopped using ClickOnce and switched to an installer.  If you're interested in analyzing trace files and summarizing the performance of your SQL take a second and download it.  It does require SQL Server 2005 to run.  The performance is improved and I'm regularly using this to import and query 2GB of trace files a day.  I'd really like to get any feedback you have on the product.  There's a menu option to send me a comment from within the application.  Take a second and let...

posted @ Monday, June 19, 2006 3:32 PM | Feedback (2) | Filed Under [ ClearTrace ]

ClearTrace Updated

Since I last posted here I've updated ClearTrace.  You can download it here.  There's also a ClickOnce installation if you just want to use the GUI version.  ClearTrace now supports moving the trace files to an archive directory after they're processed.  It also stores a summarized version of the trace files so it can query it faster.  The latest release also includes numerous bug fixes.

posted @ Friday, April 07, 2006 3:21 PM | Feedback (0) | Filed Under [ SQL Server Stuff ClearTrace ]

ClearTrace 2.0.9

I released ClearTrace 2.0.9.  You can find the information here.  I moved the download to my corporate site so I can keep a static page up for information.  I'm pretty happy with this version.  It seems to be very useable.  It also reports errors back to me so give it a whirl :)  There are no huge new features added but everything works a little better.  Let me know what you think.

posted @ Thursday, March 30, 2006 4:39 PM | Feedback (4) | Filed Under [ SQL Server Stuff ClearTrace ]

ClearTrace 2.0.7

I've made some pretty significant changes to this tool. It now writes to multiple tables instead of a single table. This greatly reduces the space required and the import time. It creates the objects it needs in the database specified. All the objects have a CT prefix in the name. There is a view included to query them. There is GUI available now. Both the console version and the GUI version are included in the ZIP file below. They have the same code behind them. There are a few options on the...

posted @ Tuesday, March 21, 2006 8:20 AM | Feedback (2) | Filed Under [ SQL Server Stuff ClearTrace ]

Read90Trace ... sort of

Read80Trace generates an XML file as its result and a series of HTML files that can be used to view the XML file.  I've updated ClearTrace so it can produce a nearly identical XML file for SQL Server 2005 traces.  It only generates the batch duration, batch CPU, batch reads and batch writes sections.  I don't know if other people use other sections but those are the only ones I use. It doesn't include the related HTML files.  You'll need to pull those from the Read80Trace download.  My advice is to run Read80Trace...

posted @ Sunday, March 05, 2006 12:30 PM | Feedback (4) | Filed Under [ ClearTrace ]

Importing SQL Server 2005 Trace Files

I previously wrote a utility called ClearTrace that monitored trace files and automatically loaded them into SQL Server.  I've started a similar program for SQL Server 2005.  Right now this utility doesn't automatically import trace files on rollover.  That functionality should come soon though.  I wasn't planning on releasing the tool but it looks like we're not going to see Read90Trace anytime soon.  This utility does normalize SQL statements in a way similar to Read80Trace.  For example it takes this SQL: select       topic_id from forum_topics where   topic_id = 3467 and normalizes it to: SELECT TOPIC_ID FROM FORUM_TOPICS WHERE TOPIC_ID = {##} It should handle dynamic...

posted @ Sunday, February 26, 2006 8:04 PM | Feedback (5) | Filed Under [ ClearTrace ]

Powered by:
Powered By Subtext Powered By ASP.NET