Bill Graziano's SQL Server Blog

ClearTrace Performance on 170GB of Trace Files

Bill Graziano — Thu, 26 Apr 2012 12:22:29 GMT

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 statements in a file that size if it’s mostly stored procedures. So those 847 trace files contained roughly 250 million statements. (That’s 730 bytes per statement if you’re keeping track. Newer trace files have some compression in them but I’m not exactly sure what they’re doing.) On a system running 1,000 statements per second I get a new file every five minutes or so.

It took 27 hours to process these files on an older development box. That works out to 1.77MB/second. That means ClearTrace processed about 2,654 statements per second. You can query the data while you’re loading it but I’ve found it works better to use a second instance of ClearTrace to do this. I’m not sure why yet but I think there’s still some dependency between the two processes.

ClearTrace is almost always CPU bound. It’s really just a huge, ugly collection of regular expressions. It only writes a summary to its database at the end of each trace file so that usually isn’t a bottleneck. At the end of this process, the executable was using roughly 435MB of RAM. Certainly more than when it started but I think that’s acceptable.

The database where all this is stored started out at 100MB. After processing 170GB of trace files the database had grown to 203MB. The space savings are due to the “datawarehouse-ish” design and only storing a summary of each trace file.

You can download ClearTrace for SQL Server 2008 or test out the beta version for SQL Server 2012. Happy Tuning!

ClearTrace Beta for SQL Server 2012

Bill Graziano — Wed, 25 Apr 2012 13:10:40 GMT

I have a beta version of ClearTrace that supports 2012. You can download it at http://www.scalesql.com/cleartrace/ClearTrace.2012.40.beta.zip. Please let me know if you find any issues.

Update to SQL Server Configuration Scripting Utility

Bill Graziano — Mon, 12 Mar 2012 00:49:20 GMT

Last spring I released a utility to script SQL Server configuration information on CodePlex. I’ve been making small changes in this application as my needs have changed. The application is a .NET 2.0 console application.

This utility serves two needs for me. First it helps with disaster recovery. All server level objects (logins, jobs, linked servers, audits) are scripted to a single file per object type. This enables the scripts to be easily run against a DR server. If these are checked into source control you can view the history of the script and find out what changed and when.

The second goal is to capture what changed inside a database. Objects inside a database (tables, stored procedures, views, etc.) are each scripted to their own file. This makes it easier to track the changes to an object over time. This does include permissions and role membership so you can capture security changes. My assumption is that a database backup is the primary method of disaster recovery for databases so this utility is designed to capture changes to objects.

You can find the full list of changes from the original on the Downloads page on CodePlex.

Speaking in Omaha: December 7, 2011

Bill Graziano — Thu, 01 Dec 2011 16:30:45 GMT

I’m presenting in Omaha on Writing Faster SQL at 6PM on December 7th. You can find meeting details on the Omaha SQL Server User Group page. The meeting location requires an RSVP so building security has a list of attendees.

The presentation is a series of suggestions on improving performance. It ranges from simple things like comparing indexed columns to scalar values up to tips for reducing query compiles and asynchronous processing patterns. Nearly all of these come from specific issues I’ve encountered working on poorly performing SQL Servers.

PASS: Total Registrations

Bill Graziano — Sat, 08 Oct 2011 19:23:04 GMT

At the Summit you’ll see PASS announce the total attendance and the “total registrations”. The total registrations is the sum of the conference attendees and the pre-conference registrations. A single person can be counted three times (conference plus two pre-cons) in the total registration count.

When I was doing marketing for the Summit this drove me nuts. I couldn’t figure out why anyone would use total registrations. However, when I tried to stop reporting this number I got lots of pushback. Apparently this is how conferences compare themselves to each other. Vendors, sponsors and Microsoft all wanted to know our total registration number. I was even asked why we weren’t doing more “things” that people could register for so that our number would be even larger. This drove me nuts.

I understand that many of you are very detail oriented. I just want to make sure you understand what numbers you’re seeing when we include them in the keynote at the Summit.

PASS: Board Q&A at the Summit

Bill Graziano — Wed, 05 Oct 2011 02:47:57 GMT

The last two years we’ve put the Board in front of the members and taken questions. We’re going to do that again this year. It will be in Room 307/308 from 12:15 to 1:30 on Friday.

Yes, this time overlaps with the Birds of a Feather Lunch and the start of afternoon sessions – but only partially. You can attend the Q&A and still get to parts of both of those. There just isn’t a great time to do this. Every time overlaps with something. We can’t do it after the last session on Friday. We can’t fit it between the last session and the evening events on Wednesday or Thursday. We had some discussion around breakfast time but I didn’t think that was realistic. This is the least bad time we could come up with.

Last year we had 60-70 people attend. These are the items that were specific things that I could work on:

The first question was whether to increase transparency around individual votes of Board members. We approved this at the Board meeting the following day. The only caveat was that if the Board is given confidential information as a basis for their vote then we may not be able to disclose individual votes. Putting a Director in a position where they can’t publicly defend the reason for their vote is a difficult situation. Thanks Kendal!
Can we have a Board member discretionary fund? As background, I took a couple of people to lunch so we could have a quiet place to talk. I bought lunch but wasn’t able to expense it back to PASS. We just don’t have a budget item for things like this. I think we should. I would guess the entire Board would like it also. It was in an earlier version of the budget but came out as part of a cost-cutting move to balance the budget. I’d like to see it added back in but we’ll have to see.
I know there were a comments about the elections. At this point we had created the Election Review Committee. I’ve already written at length about this process.
Where does IT work go? PASS started to publish our internal management reports starting in December 2010. You can find them on our Governance page. These aren’t filtered at all and include a variety of information about IT projects. The most recent update had roughly a page of updates related to IT. Lots of the work was related to Summit and the Orator tool that we use to manage speaker submissions.
There were numerous requests that Tina Turner not be repeated. Done. I don’t think we’ll do anything quite like that again.
We had a request for a payment plan for Summit. We looked into this briefly but didn’t take any action. We didn’t think the effort was worth the small number of people that would use it. If you disagree, submit this on our Summit Feedback site and get some votes.
There were lots of suggestions around the first-timers events – especially from first timers. You can find all our current activities related to first-timers at the First Timers page on the Summit web site. Plus links to 34 (!) blog posts on suggestions for first-timers. And a big THANK YOU to Confio and Red Gate for sponsoring this.

I hope you get the chance to attend. These events are very helpful to me as a Board member. I like being able to look around the room as comments are being made and see the audience reaction. It helps me gauge the interest in an idea.

I’d also like to direct you to the Summit Feedback site. You can submit and vote on ideas to make the Summit a better experience. As of right now we have the suggestions from last year still up. We may reset these prior to the Summit though.

PASS: Budget Status

Bill Graziano — Tue, 27 Sep 2011 14:43:08 GMT

Our budget situation is a little different this year than in years past. We were late getting an initial budget approved. There are a number of different reasons this occurred. We had different competing priorities and the budget got pushed down the list. And that’s completely my fault for not making the budget a higher priority and getting it completed on time.

That left us with initial budget approval in early August rather than prior to June 30th. Even after that there were a number of small adjustments that needed to be made. And one large glaring mistake that needed to be fixed. We had a typo in the budget that made it through twelve versions of review. In my defense I can only say that the cell was red so of course it had to be negative! And that’s one more mistake I can add to my long and growing list of Mistakes I’ll Never Make Again.

Last week we passed a revised budget (version 17) with this corrected. This is the version we’re cleaning up and posting to the web site this week or next.

How to Name Linked Servers

Bill Graziano — Mon, 15 Aug 2011 21:03:57 GMT

I did another SQL Server migration over the weekend that dealt with linked servers. I’ve seen all kinds of odd naming schemes and there are a few I like and a few I suggest you avoid.

Don’t name your linked server for its IP address. At some point whatever is on the other end of that IP address will move. You’ll probably need to point your linked server to a new IP address but not change the name of the linked server. And then you’ve completely lost any context around this. Bonus points if a new SQL Server eventually ends up at the old IP address further adding confusion when you’re trying to troubleshoot.

Don’t name your linked server based on its instance name. This one is less obvious. It sounds nice to have a linked server named [VSRV1\SQLTRAN01]. You know what it is and it’s easy to use. It’s less nice when you’ve got 200 stored procedures that all reference this linked server but the database they reference has moved to a new instance. Now when you query this you’re actually querying a different instance.

(Please note: I’m not saying it’s a good idea to have 200 stored procedures that all reference a linked server. I’m just saying it’s not all that uncommon.)

Consider naming your linked server something that you can easily search on. See my note above. You can also get around this by always enclosing the name in brackets. That is harder to enforce unless you use some odd characters in it.

Consider naming your linked server based on the function. For example, I’ve had some luck having a linked server named [DW] that points to our data warehouse server. That server can change names or physically move and all I need to do is update the linked server to point to the new destination. The descriptive name of the linked server is still accurate. No code needs to change and people still know what it is just by looking at it.

Consider naming your linked server for the database. I’m still thinking through this one. It may mean you have multiple linked servers that point to the same instance. I’ve found that database names rarely change. It also makes it easier to move individual databases to new servers.

Consider pointing your linked servers to DNS entries and not IP addresses. I’ve done this for reporting databases and had some success. Especially for read-only snapshots that can get created on the main database or on the mirror.

What issues have you had with linked server names? What has worked for you? Where are the holes in my approach?

PASS Updates–July 2011

Bill Graziano — Fri, 15 Jul 2011 21:21:25 GMT

There are a two big items in flight that I want to discuss.

Elections

We recently published the ERC recommendations and new election procedures. Unfortunately getting from the recommendations to the new plan took longer than I’d hoped. And it was mostly me doing the taking longer part. Plus calling what I wrote “procedures” is really a stretch. It’s really more like a rough description of what the procedures should be. Add that to the fact that the Summit is nearly a month earlier this year and we have a scheduling problem. We can try to rush and get it done or go slow and make sure we get all the details right.

We’re still working through the critical dates but it looks like we won’t have the election completed prior to the Summit. Our bylaws require us to complete the election prior to the end of the calendar year – not prior to the Summit. Years ago we used to finish the election and announce the results at the Summit. Recently we’ve tried to complete the elections prior to the Summit. This year it looks like we won’t be done until after the Summit.

Budget

And if you thought the elections were behind you should see our budget! Our new fiscal year started July 1st and we don’t have an approved budget. Fortunately that’s not the end of the world. We can continue to pay vendors and work based on last year’s budget. We recently emailed version 10 to the Board for review. I believe we’ll either have or be voting on a final budget by the end of the month. After it gets approved we’ll get it cleaned up and published.

Bill Graziano
Executive Vice-President – Finance
PASS

TraceTune display Duration in milliseconds. Finally.

Bill Graziano — Tue, 05 Jul 2011 14:20:00 GMT

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 to figure out the unit of measure for duration.

After a fair bit of research I’ve finally figured it out and TraceTune now displays all durations in milliseconds. In case you’re curious, it appears that traces from SQL Server 2008 and higher all start with the characters “FFFE”.

TraceTune shows Reads graphically

Bill Graziano — Mon, 27 Jun 2011 14:00:00 GMT

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.

TraceTune supports uploading Zip files

Bill Graziano — Thu, 23 Jun 2011 12:46:16 GMT

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 and end up with a 10Mb-12MB ZIP file to upload. I’m consistently able to get over 150,000 events in a 100MB ZIP file. That gives me a pretty good look at a system.

The second part of this is that files are now processed asynchronously. After you upload a file you’ll be taken to a processing page that updates every few seconds with the number of rows processed. It generally takes under a minute to process a 100MB trace file but I *hated* staring at a blank screen.

Give TraceTune a try. It’s getting easier to use every day.

PASS: Election Changes for 2011

Bill Graziano — Wed, 22 Jun 2011 17:51:45 GMT

Last year after the election, the PASS Board created an Election Review Committee. This group was charged with reviewing our election procedures and making suggestions to improve the process. You can read about the formation of the group and review some of the intermediate work on the site – especially in the forums.

I was one of the members of the group along with Joe Webb (Chair), Lori Edwards, Brian Kelley, Wendy Pastrick, Andy Warren and Allen White. This group worked from October to April on our election process. Along the way we:

Interviewed interested parties including former NomCom members, Board candidates and anyone else that came forward.
Held a session at the Summit to allow interested parties to discuss the issues
Had numerous conference calls and worked through the various topics

I can’t thank these people enough for the work they did. They invested a tremendous number of hours thinking, talking and writing about our elections. I’m proud to say I was a member of this group and thoroughly enjoyed working with everyone (even if I did finally get tired of all the calls.)

The ERC delivered their recommendations to the PASS Board prior to our May Board meeting. We reviewed those and made a few modifications. I took their recommendations and rewrote them as procedures while incorporating those changes. Their original recommendations as well as our final document are posted at the ERC documents page. Please take a second and read them BEFORE we start the elections. If you have any questions please post them in the forums on the ERC site.

(My final document includes a change log at the end that I decided to leave in. If you want to know which areas to pay special attention to that’s a good start.)

Many of those recommendations were already posted in the forums or in the blogs of individual ERC members. Hopefully nothing in the ERC document is too surprising.

In this post I’m going to walk through some of the key changes and talk about what I remember from both ERC and Board discussions. I’ll pay a little extra attention to things the Board changed from the ERC. I’d also encourage any of the Board or ERC members to blog their thoughts on this.

The Nominating Committee will continue to exist. Personally, I was curious to see what the non-Board ERC members would think about the NomCom. There was broad agreement that a group to vet candidates had value to the organization.
The NomCom will be composed of five members. Two will be Board members and three will be from the membership at large. The only requirement for the three community members is that you’ve volunteered in some way (and volunteering is defined very broadly). We expect potential at-large NomCom members to participate in a forum on the PASS site to answer questions from the other PASS members.
We’re going to hold an election to determine the three community members. It will be closer to voting for Summit sessions than voting for Board members. That means there won’t be multiple dedicated emails. If you’re at all paying attention it will be easy to participate. Personally I wanted it easy for those that cared to participate but not overwhelm those that didn’t care. I think this strikes a good balance.
There’s also a clause that in order to be considered a winner in this NomCom election, you must receive 10 votes. This is something I suggested. I have no idea how popular the NomCom election is going to be. I just wanted a fallback that if no one participated and some random person got in with one or two votes. Any open slots will be filled by the NomCom chair (usually the PASS Immediate Past President). My assumption is that they would probably take the next highest vote getters unless they were throwing flames in the forums or clearly unqualified. As a final check, the Board still approves the final NomCom.
The NomCom is going to rank candidates instead of rating them. This has interesting implications. This was championed by another ERC member and I’m hoping they write something about it. This will really force the NomCom to make decisions between candidates. You can’t just rate everyone a 3 and be done with it. It may also make candidates appear further apart than they actually are. I’m looking forward talking with the NomCom after this election and getting their feedback on this.
The PASS Board added an option to remove a candidate with a unanimous vote of the NomCom. This was primarily put in place to handle people that lied on their application or had a criminal background or some other unusual situation and we figured it out.
We list an explicit goal of three candidate per open slot.
We also wanted an easy way to find the NomCom candidate rankings from the ballot. Hopefully this will satisfy those that want a broad candidate pool and those that want the NomCom to identify the most qualified candidates.
The primary spokesperson for the NomCom is the committee chair. After the issues around the election last year we didn’t have a good communication plan in place. We should have and that was a failure on the part of the Board. If there is criticism of the election this year I hope that falls squarely on the Board. The community members of the NomCom shouldn’t be fielding complaints over the election process. That said, the NomCom is ranking candidates and we are forcing them to rank some lower than others. I’m sure you’ll each find someone that you think should have been ranked differently.

I also want to highlight one other change to the process that we started last year and isn’t included in these documents. I think the candidate forums on the PASS site were tremendously helpful last year in helping people to find out more about candidates. That gives our members a way to ask hard questions of the candidates and publicly see their answers.

This year we have two important groups to fill. The first is the NomCom. We need three people from our membership to step up and fill this role. It won’t be easy. You will have to make subjective rankings of your fellow community members. Your actions will be important in deciding who the future leaders of PASS will be. There’s a 50/50 chance that one of the people you interview will be the President of PASS someday. This is not a responsibility to be taken lightly.

The second is the slate of candidates. If you’ve ever thought about running for the Board this is the year. We’ve never had nine candidates on the ballot before. Your chance of making it through the NomCom are higher than in any previous year. Unfortunately the more of you that run, the more of you that will lose in the election. And hopefully that competition will mean more community involvement and better Board members for PASS.

Is this the end of changes to the election process? It isn’t. Every year that I’ve been on the Board the election process has changed. Some years there have been small changes and some years there have been large changes. After this election we’ll look at how the process worked and decide what steps to take – just like we do every year.

ClearTrace Shows Execution History

Bill Graziano — Mon, 06 Jun 2011 14:00:00 GMT

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.

Please note that saving statement level events in a trace can generate HUGE trace files very quickly.

PASS: FY10 Actuals Posted

Bill Graziano — Fri, 03 Jun 2011 13:18:57 GMT

Earlier this year we published preliminary fiscal year 2010 financials to the Governance page on the PASS web site. Please remember that FY10 runs from July 1st, 2009 through June 30th, 2010 and includes the November 2009 Summit. We do our fiscal year this way so that the Summit falls earlier in the fiscal year.

The financials we had posted were P&L numbers at the portfolio level. Prior to this we had posted our detailed budget but only posted the auditors report at the end of each year. Today we updated our published financials to include:

Pre-audit actuals from FY10 at the same level as our budget. The document has both actuals and budget for FY10 side by side. This is over 20 pages of detailed financial information covering hundreds of line-items.
A letter describing key differences between our budget and actuals. I walked through each line item where the difference was greater than $25,000 and explained what happened and why.
We updated the financial graph going back to 2003 to include FY10.

This update should “close the loop” on our financials. You can now start with the published budget and compare it to the finished financials at the same level of detail. We also plan to publish the auditor’s report when that is completed -- as we do every year.

Overall I’m very happy with how FY10 turned out. Keep in mind that this was the November 2009 Summit so we were still facing economic challenges. With all that we were roughly break-even showing a $15,000 profit on $3.9 million of revenue. I didn’t find anything shocking in reviewing our actual vs. budget but there were a few things that needed explanation. You can see those in the letter on the governance page.

Please keep in mind that these are the actuals from our operating financials. The auditor may have us make adjustments for depreciation or other financial transactions. We may also account for certain transactions differently for tax purposes than we do for financial reporting purposes. I feel these financial statements give you the clearest picture of how our organization spends its money.

We were late publishing these this year. We were working through some tax issues and that delayed our ability to file our final tax forms which delayed this process. In hindsight I should have published these documents as soon as we had them and not waited for the tax issues. We’ll do this better in the future.

And on a final note, you don’t need to login to view these documents. If you have any questions you can post them here. If we get more than a few questions we may see about creating some forums for financial issues on the PASS web site.