Thoughts on SQL Server

Cost to Compile a Query

Bill Graziano — Wed, 24 Jun 2009 11:49:09 GMT

It’s pretty easy to determine the CPU and disk resources that a given query or stored procedure will use. It’s more difficult to determine the resources that were used to compile that query plan. You can start by looking at sys.dm_exec_cached_plans. It has a column called “size_in_bytes” that will tell you how much memory the query plan is using.

If you generate an XML query plan through SSMS or Profiler you can get some additional information. The XML plan includes this snippet:

If you review the schema for the XML query plan you can find a little bit about these values.

CachedPlanSize is in kilobytes.
CompileTime is in milliseconds (1/1000th of a second) and was introduced in SQL Server 2005 SP2.
CompileCPU is in milliseconds (1/1000th of a second) and was introduced in SQL Server 2005 SP2.
CompileMemory is in kilobytes and was introduced in SQL Server 2005 SP2.

There are also entries for MemoryGrant (KB) and DegreeOfParallelism but I haven’t used those as much.

What did you learn at PASS?

Bill Graziano — Thu, 04 Jun 2009 15:51:35 GMT

In many of the PASS Conferences I’ve attended I can usually point to one thing I learned that was the most valuable. Usually this was enough to justify the cost of the entire conference. I learned something that would save me so much time or money it would “pay” for the conference. This made it pretty easy for me to justify to myself (or my boss) that it was money well spent.

Quite a few years back I was sitting in a session on performance tuning. The speaker demonstrated a little utility named Read80Trace that I’d never seen before. At the time I was working to improve performance on a client’s database server. This mostly involved hearing anecdotal stories about what was slow and running Profiler to catch queries with long durations, high reads or high CPU usage. We were making progress but nothing dramatic. Profiler was only catching those queries that rarely ran but where huge consumers of resources. I could make those faster but it didn’t have that much of an impact on overall performance.

Read80Trace would capture all the activity and group identical statements together. I could see in aggregate what was consuming the most disk and the most CPU. We spent a week working through those queries and stored procedures. Many weren’t very slow individually but would run 5,000 times an hour. Even a small improvement helped. We spent a week working on it – mostly adding indexes and occasionally rewriting queries. After a week we’d cut our disk and CPU usage in half and eliminated the need to buy a new high-end server.

Read80Trace completely changed my approach to performance tuning and has helped every client since then. I liked the tool so much I wrote my own version of it when Microsoft didn’t release the 2005 version in a timely manner. And that is the Best Thing I Learned at PASS.

PASS would like to get more stories like this. I know they’re out there. I’ve heard many of them in the years I’ve been attending the conference. Tell the story of what you learned and how it helped you. We’re running a contest through July 1st to capture these stories. Post your entry on your blog and email us the link. If you don’t have a blog, just email us your entry.

We’re limiting entries to 250 words or less. Mine clocked in at a svelte 244 words. Of course, I’m not eligible to win since I’m on the Board but our volunteers are and so is most everyone reading this. We’re giving away a number of prizes for this including a free registration or paying your hotel costs. And if you win one of these prizes you can give it to someone else. (The rules as published while I’m writing this say that you can’t transfer your prize to another winner. That’s being updated and should be corrected by the end of the day.)

We want to take these stories and add them to our ROI page. We think that having concrete examples of how people get value from the conference will help justify it in these tough economic times. We’re giving away ten prizes that include the free registration/hotel, free pre-conference session and free set of our DVDs from last year.

Take a second and write something up. Tell us the Best Thing you Learned at PASS.

Storage in the Cloud

Bill Graziano — Wed, 03 Jun 2009 00:51:03 GMT

I wrote earlier on ways I’m storing some of my files offsite. Since then I’ve made a small change.

Previously I used JungleDisk to copy shared folders on my HP Windows Home Server to Amazon S3. The newer HP MediaSmart’s come with special HP software that performs the same function. Last week HP released the MediaSmart Server Online Backup for S3 for owners of older devices and I migrated from JungleDisk. I never had any problems with JungleDisk. I just felt more comfortable with officially released and supported software.

You can specify which shares you want to backup and during what hours the backup should run. I set mine to run from 11PM to 6AM. When you configure storage in S3 you give it a bucket name to store the backups.

I used a different bucket than I used for my JungleDisk backups. Now I was going to have every file up there twice and get charged for it. I started looking for a way to delete my old bucket. Amazon doesn’t provide any tools to manage what’s stored on S3 that I could find. They give you a full-featured API and let you do the rest. After some searching I stumbled across a number of interesting S3 utilities. The one I’m using now is SpaceBlock. It’s open sourced through CodePlex. It’s not a “fully featured” application but it’s been able to accomplish the limited number of tasks I’ve needed. It also includes a browser for searching through S3 buckets for files.

One interesting thing about S3 is that you can’t delete a bucket until you’ve deleted all the files in the bucket. SpaceBlock is currently 38% of the way through my 42,547 items. It probably has another hour or two to go.

There are quite a few other interesting S3-related utilities available on CodePlex.

C# Library and Code for Amazon S3. This is basically a C# wrapper for the Amazon S3 API.
Manager for Amazon CloudFront. ClouldFront is a content delivery network (CDN). It uses files stored on S3 and gives them a public URL that distributes to a number of edge servers. This provides a super-easy low-cost way to get started with a CDN. I’ve been looking at this for SQLTeam.com for some of the larger files that get downloaded a lot.
S3 Browser for LiveWriter. This WLW plug-in allows you to insert references in your post to S3 uploaded items. You can also upload items to S3.
Amazon S3 File System for Windows. This little utility shows the S3 buckets as an \\S3 server on your computer. I haven’t installed it but it certainly looks interesting.

Azure has quite a few applications also. Unfortunately until my Windows Home Server lets me backup to Azure I’m an S3 guy.

Speaking at SqlTeach in Vancouver June 9th

Bill Graziano — Mon, 01 Jun 2009 23:04:32 GMT

I’m speaking Tuesday morning on Understanding the Procedure Cache at DevTeach\SQLTeach in Montreal. This is the same presentation I delivered at the recent PASS European Conference. At PASS I missed being the top speaker in the Application Development track by 0.8%. Guess I’ll have to step it up here :)

This session is what you need to know after you learn how to read a query plan and improve it. I spend a lot of time talking about stored procedures vs. dynamic SQL and the different types of dynamic SQL. I talk about parameterization, compilation and why procedures run fast one time and slow the next. I do lots and lots and lots of demos to illustrate everything. Many of the demos show how the same code can perform very differently depending on how it’s called, how compilation is set and what’s already in the cache. The demos cycle through explaining the setup, showing the code, running it, evaluating the performance and query plan and explaining why it behaved the way it did. Then we change one little thing and try it again. Then we change one more thing and try it again. And again… I counted 67 SELECT statements or stored procedures that I’ll cover in the presentation.

I’ve spoken at Jean-Rene’s conference before and it’s a great experience. They take good care of their speakers and put on a good show. If you’ve considered going I encourage you to attend. Their lineup of SQL Server speakers is very impressive and includes Kevin Kline, Joe Webb, Jessica Moss, Brad McGehee, Peter DeBetta, Don Kiely and Roman Rehak.

ClearTrace 2008.34

Bill Graziano — Wed, 27 May 2009 12:04:39 GMT

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, etc.) were loaded after all the trace files were loaded. Also using SqlBulkCopy of course. It was fast! It was also pretty horrible if two people were processing traces at the same time into the same database. The same dimensions ended up in the database multiple times with different values or the program just errored out. If you happened to run a query while a trace was processing you got “interesting” results as the joins would partially fail. It also forced me to take some design shortcuts in my database.

Build 34 is very understanding that multiple people might be loading and querying the repository at the same time. Dimensions are cached locally but the database remains the official copy of the truth. Dimensions are stored in the database as they are read from the trace file. You should now be able to query a trace while someone else (or the command-line version) is loading it. The performance hit turned out to be small enough that I couldn’t measure it with the second hand on my watch.

I also improved performance for large trace files. An early, early version of ClearTrace stored each trace row in the database instead of storing just summary information. When I removed that code I didn’t get everything completely removed. There was still an object being created for each trace row but never used. It wasn’t much of a big deal until reading traces over 1GB. At that point the application would slow down and possibly throw memory errors. Thanks to Jérôme for pointing this out. Actually what he did was take my binary, disassemble it, find the code in error, patch the binary, test it and then tell me the exact line that needed fixing. I wish all my bug reports were this good! Keep using ClearTrace Jérôme!

SQL Server sites I visit

Bill Graziano — Fri, 22 May 2009 12:16:31 GMT

Google Reader just introduced functionality to create “bundles” of feeds. I subscribe to roughly 60 SQL Server blogs that I read on a regular basis. You can download an OPML file of these.

More interestingly you can just subscribe to the individual RSS feeds of these sites. If you subscribe to it in Google Reader it will create create a new tag labeled “SQL Server feeds” which is what I named my bundle. You will be subscribed to each of the individual feeds in my bundle. If you are already subscribed to one of the feeds it will just add this tag to the feed – you won’t be subscribed twice. I’m not sure how readers other than Google Reader will handle this.

Tips for Writing PASS Abstracts

Bill Graziano — Wed, 20 May 2009 14:49:19 GMT

I’ve really enjoyed reading the blog posts and tweets from people that are reviewing the PASS abstracts for the Summit this fall. I was involved in that process for four years and still have fond memories of it. It’s a tremendous amount of work to review each abstract and give it the attention it deserves. And the process of actually selecting sessions was very painful. There are always deserving abstracts that don’t get selected. I commend the program committee volunteers for the time they take to read through the hundreds of abstracts and make the tough choices they do.

Jason posted some tips for writing PASS abstracts and that reminded me of a similar post I wrote a couple of years ago. Some of the similarities are striking. The biggest common denominator was that writing matters. More than half his comments referred to the quality of the writing. If I found two abstracts that were otherwise equal I was certainly going to give the nod to the person that took the time to write a good abstract. If someone isn’t willing to expend the effort to proof read their own submission do I trust them to deliver a good presentation?

When I was running the program committee I wrote a little web application to help with the ratings. We rated each abstract on a couple of attributes including quality of the abstract, quality of the speaker, etc. After the conference I took our ratings and compared them to the actual scores the speakers received. Back then we only had 400 abstracts submitted and we could only choose 60 for the Summit. I could only test the results of the selected abstracts. I did find a positive correlation between our ratings and the session scores so it seems we were able to identify the better sessions.

The other area that I feel strongly about is having a passion for the topics you end up submitting. Many people are technically proficient enough to research a topic, write some good examples and deliver an adequate session. But I think the best presentations, the ones people remember, the ones that make people change their behavior, come from something you feel strongly about. Something where you have some experience – especially some pain that you’d keep to keep others from experiencing. I know I struggled to identify which of multiple abstracts a person really wanted to present.

With all the great comments I’ve been reading I’m really looking forward to seeing the program get released and being able to attend some of the sessions. See you in November!

Using Trace Groups in ClearTrace

Bill Graziano — Tue, 19 May 2009 11:59:49 GMT

I originally started writing ClearTrace because Microsoft hadn’t released Read80Trace for SQL Server 2005. They finally did release it but by then I had a pretty handy little utility. Because I was writing it for myself I made some “interesting” design decisions along the way. One of these is now called a “Trace Group”.

I do a lot of SQL Server performance tuning for for my clients. I was struggling with a way to organize the traces I collect. At various times I want to categorize by client, application, server and/or whatever I happen to be tracing at that time. And if I run the traces inside their networks then I really don’t need the client part. And if they only have one server then I really don’t need the server part but I’d be willing to keep it just to make things consistent. After many failed fancy hierarchal database designs I just put in a free form text box and called it a Trace Group and used it as a way to group traces together. Trace Group was the most generic phrase I could think of.

Ninety percent of the time a trace group is just the name of a server. This lets me quickly review performance for any individual server. You can create a new trace group by typing a name into the trace group down down list box. You can add new trace files to an existing trace group by choosing the trace group from the drop down list box. The command-line version has similar features. If a trace group you specify isn’t found in the database it’s added.

I pull the date and time out of the trace file itself so I can go back and look at previous performance.

All the traces I read into ClearTrace are generated by a script to generate SQL Server traces that I wrote. ClearTrace will handle any trace file as long as the required fields are present but this script gives me the minimum I need to capture performance data.

I’ve also used a “client name – server name” format when I’m processing traces on my machine into my ClearTrace database. I also use names like “Quick test of the prod server to see how badly this new code broke our server” but I try to avoid names like that.

I also store the name of the trace file within the trace group. If you reprocess a file within a trace group I replace what was previously in the database for that file. It’s important that a trace file name be unique within a trace group. In my original planning for ClearTrace I wanted to it to run as a service and handle reprocessing files gracefully. My little script above automatically adds a date and time stamp to each trace file and I’d encourage you to do the same.

There’s a special trace group called “(One Time Trace Analysis)”. Any time you start to process a file for this trace group all the detail for this trace group is deleted. This was my quick and dirty way to just read a trace file and see what’s going on. The data will be saved but only until you process another set of files for this trace group.

Hopefully this gives you some ideas on what you can use the Trace Group. I’d be curious to hear any thoughts you have on this and how you use it.

ClearTrace Multi-User Beta

Bill Graziano — Mon, 18 May 2009 12:13:25 GMT

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 versions. In build 31 and earlier I just populated the local cache with lookup values as I went and bulk loaded those into the database when the file was processed. Now each lookup value is checked against the database before being placed in the local cache. In my testing this resulted in roughly one hundred extra insert statements to process a 50MB file. The performance difference wasn’t measureable.

If you’re running the command-line version in some automated fashion, please test out these new builds. It should make the application easier to work with.

ClearTrace now available with a command-line version

Bill Graziano — Fri, 15 May 2009 15:07:42 GMT

This is the first release of the command-line version. If you have any feedback, please post in the ClearTrace support forum on SQLTeam.com.

This provides all the trace loading features of the GUI. You’ll still need the GUI in order to query the results though.

Why am I being forced to learn PowerShell?

Bill Graziano — Wed, 13 May 2009 00:41:24 GMT

I spent an annoying afternoon with SQL Server 2008 today. When we installed it on our cluster it created a job called “syspolicy_purge_history”. I assume it does this in most installations but I haven’t checked.

The job was generating an error every time it ran. A quick Google search turned up an article on the problems with syspolicy_purge_history and clusters. Now all I needed was to figure out the PowerShell syntax to correctly refer to this instance.

PowerShell? Oh yes. They couldn’t just call a stored procedure. Oh no. Rather than giving me the choice to learn and then use PowerShell I’m being forced to wade through the syntax while troubleshooting. I’m not exactly happy with that decision right now. I thought this software was supposed to make me productive? Was PowerShell really needed for this?

The KB article does a good job explaining what to do if you’re running on a named instance but there wasn’t an explanation on what to do if you’re not running on a named instance. Technically I guess our instance was named MSSQLSERVER but that didn’t work. It turns out you need to add the keyword DEFAULT as I did below.

(Get-Item SQLSERVER:\SQLPolicy\DBCLUSTER1\DEFAULT).EraseSystemHealthPhantomRecords()

I really hope this job truly needed PowerShell.

Storing My Files in the Cloud

Bill Graziano — Mon, 04 May 2009 12:17:15 GMT

(Written on my snappy new Windows 7 RC installation. Operating systems are so much faster before they get those pesky applications installed.)

I’ve been gradually transitioning all my file storage and backup to online services, aka “The Cloud”. I’m nearly done and thought I’d share what I’ve done in hopes of getting even better ideas or helping someone else out.

Live Mesh

My main working computer is a laptop. I have a small server here but it’s only used for testing. I have a few folders in “My Documents” where I store the bulk of my non-development files. These include ClearData (work), SQLTeam, Personal, Presentations and PASS. All these files are now stored on Live Mesh and synchronized down to my laptop. Each of these is a separate folder in Mesh. When I’m working on the laptop locally I would never know these files are stored on Mesh. A little helper utility runs in the background and moves changes back and forth. I can also log into the Mesh web site and access the files from there. It’s nice to know anytime I give a presentation that a copy of the presentation is available online if I need it. I can also access my files using an iPhone or Windows Mobile device via their mobile web site.

Mesh has a 5GB limit right now and is still in beta. I’ve been using it for a few months and haven’t had any problems. I’m assuming at some point they’ll start charging for it and I’d be happy to pay. The downside of Mesh is that you can’t control when it syncs. Earlier versions had this feature but it got removed at some point. It doesn’t always sync immediately after changes but it usually happens pretty quickly. That’s been my only real complaint so far.

Another benefit of Mesh is that I can share access to folders. For example, my business partner and I can share the ClearData folder. Any changes either one of us makes are copied over the to other’s computer. That’s a handy way to share financials, contracts or whatever else we’re working on.

I also use Mesh to synchronize the files behind Windows Live Writer and Microsoft OneNote. This was as simple as right-clicking on the directory where these applications store their files and choosing “Add folder to Live Mesh”. On new installations I just sync these directories down prior to using the application the first time. You can find more detailed instructions for OneNote and Live Writer if you like. I’m in the process of buying a NetBook and possibly a desktop and this will allow me to use these two applications seamlessly on any machine. It also makes me productive on a new operating system install that much quicker. I’d like a way to synchronize OneNote data down to my iPhone but I haven’t found a way to do that yet. (And no, I don’t want to use EverNote.)

Windows Home Server

I backup my laptop to a Windows Home Server every night. I bought one of the HP MediaSmart servers when they first came out. I’ve been using it for over a year now and it works well. I’m finally getting ready to add another drive which looks like it will be a painless process. Backup and restore are dead simple. One benefit is that I kept access to older builds of my laptop as old backups. When I rebuild my laptop I rename it and then keep one backup of the previous build. WHS sees it as a separate computer so I can always go recover files from it if I need to. If I desperately needed that laptop back I could buy another laptop drive and restore the image onto it. I’m still looking for an easy way to convert a WHS backup into a virtual machine. You can restore the backup into the VM but it won’t boot until you do some repair work.

All my pictures, music, videos and older files are stored directly on the MediaSmart. I was very nervous having those in a single location. You can attach an external drive to a Windows Home Server and back it up but I wanted something automatic. I’ve started using JungleDisk for WHS to backup my files to Amazon’s S3. They are running a special on upload pricing through June. I copied up roughly 45GB of pictures, music and other “stuff” in April and got charged $3.96. It looks like they’ll charge me about $7 per month to store it each month. JungleDisk runs inside WHS and copies up changes every night on a schedule I configure.

I’m just started experimenting with the “Offline Files” feature of Windows to work with files store on my WHS. This may give me similar functionality to Mesh.

Exchange

I use an Exchange server mailbox from AppRiver for email . They’ve been good but not spectacular. They charge $13 a month for an unlimited size mailbox. Beware their $50 per month minimum though. I think you can find individual mailboxes for comparable prices. Using Exchange and its synchronization features allow me to access my mail on multiple PC’s, the web and my iPhone including my contacts and my calendar. I think I could probably do the same thing with Google at this point but I’ve already got this working the way I like.

Beanstalk (Subversion)

All my critical development projects are stored in a hosted Subversion repository run by BeanStalk. They have a free plan but we use the $15/month Personal plan. This gives you up to ten repositories, 3GB of storage and five users. Their plans scale up beyond what I’d ever use. They were able to import our existing repository so we didn’t lose any change history. Having a hosted Subversion allows my business partner and I do work jointly on projects. CodePlex now supports TortoiseSVN (my subversion client of choice) so this allows me to use a single source code control system for all the work I do.

I have roughly 1GB of various development projects that aren’t in Subversion right now. These are proof of concepts or little things I’ve worked on that never see the light of day. I think these will get moved to Mesh or WHS pretty soon. Right now they are just on my laptop and backed up to WHS.

Summary

I’m still working on a way to synchronize bookmarks. I’m sure I’ll come up with a few other holes where I’d like to synchronize the configuration for an application. For example, it annoys me to no end that I have to manually recreate my five extra mail accounts in Outlook and that I have to manually copy over my signatures when I rebuild my laptop.

Overall, I think this scenario gives me a safe, flexible environment. If my laptop and WHS were to disappear I could pull everything back down from Amazon and Mesh. If “The Cloud” were to disappear I have everything locally. I can run my standard productivity applications on any of my computers and they’re synchronized automatically.

What have you done that might work better than what I’ve got? What suggestions do you have to improve this?

Update from PASS Germany

Bill Graziano — Thu, 23 Apr 2009 16:24:24 GMT

I posted a quick update from the PASS European Conference on the PASS Blog. And it include a picture of our Executive Vice-President Rushabh Mehta. With Hair. I’m so jealous!

Interviewed on RunAs Radio

Bill Graziano — Mon, 13 Apr 2009 12:55:59 GMT

I was recently interviewed on RunAs Radio. We spent quite a bit of time talking about performance tuning. I’m spending quite a bit of time lately looking at how things get into and out of the query cache and we touched on that a bit. We also spent some time talking about ClearTrace and what it can do for you. Take a listen and try not to laugh at a “voice made for print”. I was also interviewed on Greg Low’s SQL Down Under a few years back. That show is in the SQL Down Under archive (show #13).

ClearTrace now supports SQL Server 2005 AND SQL Server 2008

Bill Graziano — Fri, 03 Apr 2009 12:35:24 GMT

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 made it a little difficult write a provider since I couldn’t strongly type anything. The methods for an object are called using InvokeMethod. Fortunately I only needed to call the InitializeAsReader method that way. After that I was able to cast the TraceFile object to an IDataReader and manipulate it just like any other data reader. My proof of concept code looks like this:

Object[] fileName = new object[] { @"\\hpserver\Development\trace\CDC10.trc" };
// Type t = Type.GetType("Microsoft.SqlServer.Management.Trace.TraceFile, 
Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral, 
PublicKeyToken=89845dcd8080cc91");
Type t = Type.GetType("Microsoft.SqlServer.Management.Trace.TraceFile, 
Microsoft.SqlServer.ConnectionInfoExtended, Version=10.0.0.0, Culture=neutral, 
PublicKeyToken=89845dcd8080cc91");

Object o = t.InvokeMember(null,
    BindingFlags.DeclaredOnly |
    BindingFlags.Public | BindingFlags.NonPublic |
    BindingFlags.Instance | BindingFlags.CreateInstance, null, null, null);

t.InvokeMember("InitializeAsReader",
    BindingFlags.DeclaredOnly |
    BindingFlags.Public | BindingFlags.NonPublic |
    BindingFlags.Instance | BindingFlags.InvokeMethod, null, o, fileName);

IDataReader t2 = (IDataReader)o;

while (t2.Read())
    Console.WriteLine(t2.GetString(t2.GetOrdinal("EventClass")));

t2.Close();

(Please remove the carriage returns in the middle of the string. I just put that in so it wouldn’t wrap. I’m sure there’s some C# guru looking at my code with the same disdain I have for people new to complex SQL statements. Take pity on a humble DBA. And I’m open to suggestions on better ways to do this.)

The downside is that I lose some accuracy on error messages. If the InitializeAsReader method fails you get the “Exception has been thrown by the target of an invocation” error message which really isn’t very helpful. That statement is wrapped in a try catch so I can provide a better error message.

Take it for a spin and let me know what you think. Now that’s I’ve got the code back in shape I should be doing more development on this in the future.