Expert? Ha!

How 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.