Wednesday, February 17, 2010

Presenting At SQL Saturday #33 - Charlotte

Just a quick note to let you know that I'm presenting two sessions at SQL Saturday #33 in Charlotte on March 6:

Getting started in blogging and technical speaking
Are you thinking of starting a blog? Or are you interested in presenting at events like SQL Saturday but not sure what to do to get started? This panel style session with established SQL bloggers will give you tips on blogging platforms, choosing topics, techniques for giving presentations, and sage advice from years of experience.

The (Solid) State Of Drive Technology
Solid State Drives are being heralded as game changers in the world of SQL IO. What was once was a costly technology only available to those with deep pockets is now becoming affordable for mainstream use. This session look into the how SSDs work, how they perform compared to traditional hard drives, and highlight the things you need to consider when implementing SSDs on your SQL Servers. Be sure not to miss a live demonstration of solid state technology in action!

I'm really excited about the solid state drive session as I've done a lot of work with them in the last 6 months and I think the DBA community is really starting to get interested in SSDs. On that note, I'm looking for other people's insight into how SSDs have (or have not) worked for them in their SQL Servers. If you wouldn't mind sharing your opinions and observations with me, please contact me at kendal-dot-vandyke-at-gmail-dot-com.

As of the time of this post there are 9 tracks and 56 sessions (including 2 vendor sponsored sessions). The Charlotte SQL Server Users Group is hosting this event and has done a fantastic job of putting together a great lineup of speakers. Don't take my opinion though – go check out the schedule for yourself.

Registration is still open and there's a minimal charge to cover lunch. If you're anywhere near the Charlotte area this is one event you won't want to miss!

Friday, February 12, 2010

A Tale Of Two Execution Plans – Solved!

Earlier this week I wrote about a perplexing problem I was having where identical servers were producing different execution plans for the same ad-hoc query. I got a lot of great feedback, and Wes Brown (Blog | Twitter) even contacted Conor Cunningham (Blog), query optimizer team lead, for me. But it was this suggestion from Paul Randal (Blog | Twitter) and an IM from Jonathan Kehayias (Blog | Twitter) that helped me uncover what was going on:

image

Quick Recap
Recall that my problem started out like this: A simple ad-hoc query executed against 9 servers with (more or less) identical hardware, identical schema, identical data, and identical index statistics produced different execution plans. I looked at a variety of things to try and figure it out: SET options on the connection, collations, compatibility levels, server configuration options, build versions, and index statistics. Ultimately everyone who looked at it figured that the root cause of the problem was probably related to this: In Plan "A" the estimated number of rows for an index seek differed from Plan "B", and that in turn lead the query optimizer to choose an index seek + key lookup vs. an index scan on a different table later in the execution plan.

Plan "A" Plan "B"

Doesn't That Look Like A Problem With Statistics?
Yes, you'd think that…except that I updated statistics for the index in question (well, for every index on the table actually) with FULLSCAN specified. That should have taken care of the problem, right? Not exactly. One thing I didn't think about was that AUTO_CREATE_STATISTICS was set to ON for every database (read more about this option here). With this option on the query optimizer will create statistics on individual columns, as necessary, to improve estimates for query plans. You can see which columns have had statistics created for them by using sp_helpstats (BOL entry here). 

A Eureka Moment
A check against the 9 servers revealed that the 4 servers currently with plan "B" had statistics auto created for the column used in the query's join and the 5 servers with plan "A" did not. I picked one of the servers using plan "B", dropped the auto created statistics, and…the query optimizer was now picking plan "A"! I did the same against every server using plan "B", and every server switched to using plan "A". Ahh, I love repeatability! (Note that dropping auto created statistics isn't something you want to casually do – the query optimizer created them for a reason, after all)

So The Answer Is…
In the end, it had nothing to do with build versions, slight variations in CPU models, fragmentation, SET options, server options, or any of the multitude of things I looked at…it was the auto created column statistics that affected which plan the query optimizer choose. The takeaway? Add that as one of the things to check when a query isn't behaving like you think it should.

Thanks again to Paul Randal and Jonathan Kehayias for pointing me in the right direction. I can once again sleep at night now knowing that there's a reasonable explanation for what was happening.

Wednesday, February 10, 2010

A Tale Of Two Execution (Updated)

Update: Scroll to the bottom for more information added after originally posting.

Earlier this week a coworker asked me for help figuring out why an execution plan was different for a query run against two identically configured servers. I've since spent the better part of the week trying to figure out why, and after several suggestions from folks on Twitter and two MVPs I still don't have an answer.

First, A Little Background
My company has 9 SQL Servers that are used by web servers to render pages for a web application. Each SQL server is more or less identical to one another – Dell 2950, 4 GB RAM, Windows 2003 R2 x86, and SQL 2005 Standard Edition. The /3GB switch is set to let SQL use 3GB of RAM, and these are dedicated SQL servers with no other processes running on them. Every server is a subscriber to transactional replication so they've got identical data and schema. There's no SSIS, SSAS, or SS-anything else-S at play on these boxes. The only difference is that a few servers have dual-dual CPUs and a few have dual-quad CPUs, but as you'll read in a minute that doesn't seem to matter.

The Problem
Now here's the problem…At the beginning of the week, for this simple query 1 of the 9 servers resulted in execution plan "A" and the other 8 used plan "B". Things have shifted; now 7 of the 9 are using plan "A", but 2 servers are still using plan "B".

The first thing that came to mind was out of date or updated statistics. So I isolated one server using plan "A" and one using plan "B" and ran an UPDATE STATISTICS with FULLSCAN on all the indexes for the two tables included in the query. I verified the statistics from both servers matched by reviewing the results of DBCC SHOWSTATISTICS. No change in execution plans. (For what it's worth, AUTO_UPDATE_STATISTICS is enabled and we have an agent job that runs sp_updatestats throughout the day to keep statistics up to date)

I looked at SET options for connections. Identical.

I looked at collations. Identical.

I looked at the data itself. Identical.

I looked at server configuration options (sp_configure). Identical.

I looked at database compatibility levels. Identical.

I looked at memory available to SQL Server. Identical as far as I can tell.

I looked at SQL server versions. A-ha!

  • Servers with plan "A":
    • 9.00.4230.00
    • 9.00.4226.00 (6 of these)
  • Servers with plan "B":
    • 9.00.4262.00
    • 9.00.3054.00

So maybe there's something there with the version difference. Except that the server that started the week on plan "A" is the one running 9.00.4230.00. The 6 servers running 9.00.4226.00? They were using plan "B" and have since switched to plan "A". And what about the server running 9.00.4262.00? That's a later version than the one running 9.00.4230.00, yet it's using plan "B". That makes me doubt that it's something related to the version number.

Just for fun and to add to the confusion, I changed the table variable in the query to a temporary table…and the plans matched!

Think You Can Figure It Out?
I'm stumped, and I'd love to hear what you think the reason for the difference in plans might be. I've linked to a the schema for the tables, the output of DBCC SHOWSTATISTICS for the index that I think is the root cause of the problem, the query itself, and execution plans "A" and "B" below. Object names and servers have been changed to protect the innocent. If there's something I'm missing that you want to know more about, contact me on Twitter (@SQLDBA) or leave a comment.

I'd love to figure out what's going on!

Download the schema and queries here

 

UPDATE: OK, as some people were quick to point out, the XML execution plans I included showed two different builds of SQL. So I went poking around and found that one of the 9.00.4266.00 builds has gone back to using plan "B" again. That means I have two different servers on the same build with different execution plans. To be sure it wasn't obviously statistics related I ran another UPDATE STATISTICS with FULLSCAN (and verified that statistics matched across servers), cleared the buffer cache, and the plans were still different. I also realized that in the course of changing object names I ended up with a duplicate column. I have fixed the schema script and updated the download with the plan "A" and plan "B" from the 9.00.4266.00 builds.

Thursday, February 4, 2010

Three Things That Got Me Here

Tim Mitchell (Blog | Twitter) and Jack Corbett (Blog | Twitter) both tagged me in the latest meme circulating through the SQL blogger community; this one was started by Paul Randal (Blog | Twitter) who asks the question "What three things or events brought you to where you are today?"

Early Introduction To Computers
Back in my elementary school days personal computers were just making their way into schools and homes. From around 8 on through my early teens I had the chance to work on a lot of different hardware: A Tomy Tutor, the original MacIntosh; Apple II; Franklin (Apple II clone); Commodore 64; Amiga; and a KayPro 4, to name a few. Those were all pre 286\386\486 days, and looking back it's laughable that we had 64KB of memory, 16 colors, and were loading programs off cassette tapes and those giant 5 inch floppy disks (especially when I can run any of those computers in an emulator on the phone in my pocket today)…but man were they the coolest things ever! I remember writing programs in BASIC; playing games like BC's Quest For Tires, M.U.L.E., and Choplifter; and wardialing with a 300 baud modem. Tinkering around on all those different computers at an early age planted a seed in me that would be nurtured many years down the road.

The Air Force Academy
And by this I mean not going. I spent all of my time in high school involved with Air Force Jr. ROTC. I've always loved airplanes and flying; At 15 I completed ground school and at 17 I had earned my private pilot's license. I was sure that I wanted to go to the Air Force Academy and become a fighter pilot. I had the grades, the congressional nomination, the backing from my JRTOC unit commander (who was a retired Air Force Colonel, no less), and I had passed the physical requirements. Only along the way I found out that I had an odd condition with my eyes: Despite being able to see colors clearly, every "standard" medical test I took indicated I was color blind. I took proficiency tests which demonstrated that I wasn't color blind, but getting a slot as a fighter pilot is such a selective process that the odds were stacked against me. So I reconsidered whether that was what I was meant to do in life. Eventually I decided that it wasn't meant to be and I withdrew my application.

An interesting side story: Although I had my pilot's license I still didn't have a driver's license. I was more than happy to take friends up for a flight…as long as they could give me a ride to the airport first. :-P

Good Advice
After changing my plans to go into the Air Force I ended up at Florida State University trying to figure out what to do next. I'm the youngest of seven and many of my siblings earned degrees in engineering so it seemed logical that I should give that a try too. I declared my major in mechanical engineering figuring that if I couldn't fly airplanes that I'd design them instead. A year's worth of classes later and while I didn't hate the material I wasn't necessarily passionate about engineering. One of my non-engineering brothers had noticed how I had tinkered with computers throughout my life and suggested that I look into computer science. It turned out to be really good advice; The next semester I changed majors, took Computer Science 101, and in the 14 years since then I've never looked back!

 

I've known Andy Warren (Blog | Twitter) for a couple of years now but the story of how he got into computers has never come up. So Andy: Tag, you're it – tell us your story!