SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Expert? Ha!

dunceHow do you define an expert? My personal definition: An expert is the person that is a chapter ahead of you in the book.

Why am I talking about this? Just that I’m feeling more stupid than usual lately. In the last two weeks I’ve had people bring up through various discussions, documents, what have you, four different SQL Server trace flags that will affect how SQL Server builds execution plans and I’ve never heard of any of them before.

I’ve never, ever, thought of myself as an expert in execution plans, despite having written a book about them. I just thought I had a good grasp on how they worked and I was willing to share. I didn’t know everything and never pretended to, but I thought I knew a lot. Then, in two weeks I find four different trace flags that I’ve never heard of, addressing interesting issues that maybe I should have known about. Holy cow!

I’ll try to put up a blog post on some of these trace flags that I’ve found. I’m also doing a little searching to see how many others I’ve missed. Here are the four:

2861: Includes zero cost plans in the cache.

2335: Extra memory messes up execution plans (by extra they mean >512GB)

2389: Ascending values stats cheat (pretty cool)

2390: A second, complimentary, ascending values stats cheat

I’ve talk about other trace flags in the past (and the fact that I was discovering them for the first time too). Microsoft’s “official” list is pretty small and only includes a single one relating to execution plans. Although, to be fair, that one is actually huge. But there are lots of others documented in various Knowledge Base articles but not listed as such in the core documentation.

More to learn, more to learn. That is a great, cool and humbling thing.

By the way, if you’ve ever been in the room when I got introduced as an “expert” and you saw me laugh at the introduction, this is why. I’m not an expert.

Oh, and worth mentioning, you should be very, very cautious when using trace flags. Check out this short blog post from Paul Randal (blog|twitter) on just that topic.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Posted by Andy Leonard on 22 August 2011

Hi Grant,

  You're wrong. You are an expert. You're the technologist many of us look to for answers about execution plans and performance tuning. We know you don't know everything. The fact you're so willing to admit that is awesome and makes us want to contact you when we have questions. Why? It's cool to ask a question of someone who doesn't think they know everything - it makes you more approachable.

  You rock.


Posted by Jason Brimhall on 22 August 2011

Thanks Grant.  I agree with Andy - you definitely qualify as an expert.

Posted by Stephanie J Brown on 29 August 2011

"An expert is someone from outside the company, carrying a briefcase."

Seriously, you qualified yourself as an expert the moment you admitted you don't know everything on the subject.  IMHO, only a expert has 1) the knowledge of how much else there is to learn, and 2) the self-confidence to admit it to the world.

THAT'S your qualification, as far as I'm concerned.  Well, that and the fact that you've to more answers, and you communicate them well, and your the go-to guy, and...  well, you get the picture.

Posted by Stephanie J Brown on 29 August 2011

Dang!  I meant "... you've GOT more answers..."

Posted by brandon.leach on 2 September 2011

Grant, the fact that you don't think of yourself as an expert and admit you don't know everything, is exactly what makes you an expert.  I don't think any of us expect you to know it all.  But the fact that you are willing to share what you do know really does help.

I've spent the last 7 months since I started this job mainly doing Performance tuning on our SQL Server infrastructure.  Your book "SQL Server 2008 Query Performance Tuning Distilled" has been my most used reference during this time.  

Leave a Comment

Please register or log in to leave a comment.