Random thoughts on my trip to the conference

I had a great time on my brief stint in Denver for the PASS Summit. We had a good session and a good crowd, and Kevin Kline did a marvelous job of running the whole thing. He had lots of great questions for me during the interview, and the crowd had many good ones, too. I hope those of you who attended felt it was worth your time.

 

One thing that came up during the talk was the status of SQL Nexus and what its future is. As I said when asked about it:

 

• You can currently download the latest version of SQL Nexus from www.sqlnexus.net. For those who don’t know, SQL Nexus is a SQL Server performance analysis tool written by Bart Duncan and me. It loads the diagnostics collected by SQL Server’s SQLDiag facility (and similar tools) into a data warehouse (see the Books Online if you aren’t familiar with SQLDiag), then provides graphs and charts over that data. You can use it to find your most expensive queries, troubleshoot performance bottlenecks, investigate blocking, etc. It was originally released on the CD accompanying the book, SQL Server Practical Troubleshooting: The Database Engine .

  Nexus is something Bart and I dreamed up nearly seven years ago before Microsoft had released any tools of this kind for SQL Server. Its influence is evident in similar tools that have appeared since then. For example, many of you are familiar with the ReadTrace tool. When Bart and I first proposed automated performance analysis to our management within Microsoft, ReadTrace had not yet been released to the public and was merely a trace file splitter used by SQL Server Support—it split Profiler trace files by SPID and wrote them out to separate files. This facilitated more easily seeing which users were running which queries rather than having to filter on the SPID column in Profiler. It did not do any analysis of the trace files it read back then—it was intended merely to simplify the job of seeing which users did what and play that work back as necessary. Some time after we’d made our proposal regarding automated performance analysis, some of these ideas found their way into the next release of ReadTrace. If you’ve ever wondered about the incongruity between the tool’s name and its analysis feature (which is probably its most useful feature from an end-user perspective), that’s why it’s there—ReadTrace didn’t originally do any analysis. Similarly, the Data Collector and Management Data Warehouse features in Katmai were influenced by our work with PSSDiag/SQLDiag and SQL Nexus. (Bart and I have been involved with the DC/MDW project from the start, and Bart recently joined the team fulltime.) We also know that various third party tools have built on the concepts we originally championed. From that one seed, lots of goodness has grown, and we couldn’t be happier about it.

• Bart and I have given Microsoft’s Customer Support Services organization permission to release the source code to SQL Nexus via Microsoft’s CodePlex site. Current word is that this should be out internally by Thanksgiving (I don’t know when it will be available publicly on CodePlex, but I would expect it to shortly follow). CodePlex, for those of you who don’t know, is Microsoft’s shared source initiative, not unlike SourceForge and similar sites. Releasing the Nexus source will allow those of you in the user community who are so inclined to help develop and evolve Nexus further.

  A concern we in SQL Server development had about releasing the Nexus source was that we might want to use parts of it in future products. To that end, we’ve asked CSS not to release the source code to a handful of Nexus components. One of these components is Nexus’ TraceBuster facility. TraceBuster, you may recall, is the Profiler trace loader in Nexus. It performs a similar function to the ReadTrace utility some of you may be familiar with, but differs in certain ways:

 

• • Like the rest of Nexus, TraceBuster is written in managed code. This means it integrates more seamlessly and more efficiently with the rest of the architecture.

    Nexus offers a pluggable architecture wherein you can add new loaders to import diagnostic data into the data warehouse by merely editing an XML file or, for more involved work, by creating a simple managed code assembly and dropping it into a folder. You can easily implement new reports by designing them with the Reporting Services designer (in Visual Studio, for example) and dropping them into Nexus’ custom reports folder. TraceBuster reads Profiler trace files, normalizes the query text it finds in them by removing literals, and loads all this into the data warehouse. It also includes several reports for analyzing the most expensive queries in a workload, drilling into those queries, gauging their cost using a variety of metrics, etc.

    While we also built a Nexus loader for ReadTrace, ReadTrace is not managed code and is a console app rather than an assembly, so the integration isn’t quite as efficient or seamless. The Nexus loader for ReadTrace must shell to a separate application rather than running it in the Nexus process itself as happens with TraceBuster.

  • TraceBuster does not require the code it analyzes to be fully parseable. This means that it’s more tolerant of the broken T-SQL sometimes seen in Profiler traces. Because of the vagaries of networks and SQL Server’s trace architecture, bad T-SQL in Profiler traces is more common than you might think. Missing quotes, truncated statements, missing pair members—these are not that uncommon in trace files. Utilities that analyze the T-SQL in trace files must be more tolerant of anomalies in it than, say, the database engine would be. TraceBuster didn’t begin as a full T-SQL parser that was then special-cased to handle all the eccentricities of traced T-SQL. It began as a no-frills “literal identifier” that merely replaces literals in query text with generic parameter markers so that you can easily compare instances of the same query with different filter values. It does not know and does not care whether the code it examines is perfectly intact. It does no more and no less than exactly what it needs to in order to provide meaningful query cost analysis. This makes it an order of magnitude simpler and more robust than full parser-based solutions.

  • TraceBuster uses SQL Server’s own facilities for reading Profiler trace files. It defaults to using SQL Server’s fn_trace_gettable function if the trace files you provide it are on the server machine (or accessible by it) and falls back on the SMO facility for reading trace files if fn_trace_gettable can’t get to them. It never directly accesses the trace files themselves. This means that TraceBuster is immune to file format changes across releases. It does not need (or want) intimate knowledge of the Profiler trace file format. The file format changed between SQL Server 2000 and SQL Server 2005, but the same version of TraceBuster can read trace files written by either release because it uses SQL Server’s built-in facilities for doing so.

• Given all this and the fact that TraceBuster won’t be included in CSS’s CodePlex release of Nexus, you might be wondering whether you need TraceBuster and how you can get it after CSS releases the new version. You’ll have to decide for yourself whether you need TraceBuster. Tentatively, CSS plans to release a new version of ReadTrace that can read SQL Server 2005 Profiler trace files around the same time Nexus is released via CodePlex. ReadTrace and TraceBuster provide much of the same functionality, so it is unlikely that you would want to use both of them. And though they are similar, there are important differences between them, as well.

  If you decide that you want to continue to use TraceBuster once it has been removed from Nexus, you can get the binaries (no source) from www.sqlnexus.net, the same site that will host Nexus itself until CSS releases it via CodePlex. The specific link is https://www.sqlnexus.net/download/tracebuster/TraceBuster.zip. If that link doesn't work, try this one: https://khenzden.googlepages.com/TraceBuster.zip. 

 

I know all of this may be a bit confusing, so let me net it out:

 

• The current version of SQL Nexus (including the TraceBuster component) is available today, without source code, via www.sqlnexus.net. It provides a rich performance analysis suite for SQL Server, including the type of Profiler trace analysis you may have done with ReadTrace in the past. It is functionally on par with the source-code version of Nexus that CSS plans to release via CodePlex. If you don’t plan to make use of the Nexus source code, there’s no reason to wait for the CodePlex release. You can get what you need today.

• The next version, due to be released internally by CSS around Thanksgiving and sometime thereafter as source code via CodePlex, won’t include TraceBuster because CodePlex releases are necessarily source code-oriented, and the SQL Server development team has reserved the right to include pieces of TraceBuster in future products. It will, however, include support for ReadTrace (which should be released by CSS concurrently), a tool that performs much the same function, but that is not as well integrated with Nexus.

• If you still want to use TraceBuster after the new version of Nexus has been released, it will be available here or here in binary form (no source), just as it is today. Once you’ve installed the CodePlex Nexus release, all you’ll need to do is copy the TraceBuster assembly and the included reports to the appropriate folders. There’s a readme file in the zip that provides specifics. When you then restart Nexus, you’ll see TraceBuster and its reports available in the GUI.

 

So, anyway, I had a good, if brief, PASS trip, and enjoyed meeting those of you who stopped by. Thanks for all the great questions during the interview, and I hope to run into you again in the future.

 

A funny thing that happened at the Avis counter at the airport:

 

Guy at the counter: Sorry, sir, the economy car you reserved isn’t available. We’ll have to upgrade you free of charge to a midsize. Is that okay?

 

Me: Okay, that’s fine. By the way, where’s the men’s room?

 

Guy: <funny look on his face> Is there something I could help you with?

 

Me: Uh, no. Just need the men’s room, thanks.

 

Guy: <a little peeved> Okay, just a moment. <then takes my paperwork and disappears through door #3 behind the counter>

 

Me: <a bemused look on my face, I’m wondering what the heck is up. I scan the place looking for hidden cameras>

 

It then occurs to me that the guy believes I’m not happy about the car switch and thinks I’ve requested a manager rather than a men’s room. Whew! I finally understand why he wanted to help :-)

 

I then tell the six women working the counter what the situation is. We’re all laughing hysterically by the time the guy returns with the manager. He is nonplussed.

 

Oh yeah, and the car I ended up with was the new, improved, quite sporty Dodge Charger of Dukes of Hazzard fame. Me likey—this one is a worthy heir to its muscle car forebears. The one thought that kept going through my mind as I zipped around Denver in the Charger was: I gotta get me one of these.