You Can Float my Boat, but Don’t Bloat My Cache

Posting yesterday’s journal entry today during lunchtime (figured it’s safe)…

Day 14 (6.9.10):  Realized today that I’ve been numbering the days wrong. Considering we’ve had to log into work from home on weekends here and there, I’ve decided today should really be Day 14 instead of Day 7. *doh!*

Ran out of sugar for the coffee today. So instead of going to the store like any normal person would (I know I’m not normal), I brought in little yellow packets. Ah ha! Maybe that’s what the plant needs. Little yellow packets mixed with the coffee!

Dear Developers, on behalf of my DBA buddy… testing should occur BEFORE a project goes live. Not AFTER it goes live. And while we’re at it, it would be SUPER if you could take a few minutes to drop us a little note letting us know that you’ve completed testing and everything looks good instead of taking off for lunch or going home for the night without a phone call, an email, or even a see-ya-wouldn’t-wanna-be-ya wave. It would be much appreciated since our telepathic powers seem to fade every so often. Thank you for your cooperation.

Mind control experiment does not appear to be working with certain developers… hmm… maybe it’s a compatibility issue…

Drove crazy self to the Kalen Delaney’s session on “SQL Server Plan Caching and Recompilation” in Denver (hence, the odd title about cache).  DBA buddy wasn’t able to go at last minute due to issues at work. I felt guilty leaving her. However, she insisted I go because it’s too good of an opportunity to miss. So I went… which is huge for me. I’m not a fan of driving if I don’t have to. I’m not a bad driver. I just don’t like to drive… cars. Driving everyone crazy is completely different. That I enjoy. 😉

Anyway, it took me 1.5 hours to get to the session which is 60 miles from where I work. South Denver my lily white ***! Seemed more like the north end of south Denver. Don’t mind me. Did I mention I don’t like driving? Left work from downtown Colorado Springs at 4 pm. Stopped for gas in Monument. Arrived in Denver at 5 pm. Guess what? It took me 30 minutes to go the last 2 flippin’ miles! Sheesh! Reminded me of when I lived in San Diego many moons ago. So I was a bit cranky and hungry when I got there. Was a bit bummed to find out there seemed to have been a communication glitch with the sponsor. Meaning, no food. Just water and soda. At least they managed to provide a snack at the last minute which helped a little. Spicy trail mix + Black pants = Orange powder on my black pants. Probably didn’t look too good. Oh well. In addition to that, I had put my sneakers on when I left work since I’m more comfortable driving wearing sneakers instead of heels. I had intended to put my heels back on when I got there. Of course, I forgot. Hope no one noticed. Not that it matters anyway.

I got to meet Kalen briefly which was pretty cool. She seems really nice and down to earth. Of course, in my rush to get to the session, I had forgotten to bring a book for her to sign. *sigh* The session, by the way, was AWESOME! I actually learned quite a bit, was able to pay attention and follow along through her entire presentation! Considering the day I had, that’s pretty good for me. Wanna know what I learned?

Disclaimer: I’m not an expert. So if I got something wrong below, please let me know! I don’t want to misinform anyone. I was tired but tried to take decent notes anyway.

What I LearnedCool Thing #1: I learned why adhoc queries suck, err… I mean why adhoc queries can suck up so much of the cache.  In order for an adhoc plan to be reused, it has to be an EXACT match. Meaning, the query that is being run MUST match up what is in the cache space for space, character for character. The case must match too! Even if the server is case insensitive, the case must match. We’re talking about THE definition of EXACT here. So even if you’re off by ONE space, you get a whole new plan. I don’t know about you, but I was shocked. OMG! That explains so much! Note: you can do a select from master..syscacheobjects to see what’s in the cache. Even cooler, you can specify which database’s plans you want to see in the cache.

Cool Thing #2: I was also stoked to learn what the new SQL 2008 option “optimize for adhoc workloads” really does. Apparently, it will create a “stub” plan the first time you run that particular adhoc query. What’s cool with that? It’s only 300 bytes! The minimum size of a non-stub plan is 16K! That’s a pretty decent savings, if you ask me. The syscacheobjects.cacheobjtype will show “Compiled Plan Stu”. Yes, that’s a typo from Microsoft. Kalen said she reported it to them. It really should say “stub”. Stu is much more fun, though. Anyway, the second time that query runs it will replace the stub with the full plan and will show “Complied Plan”. That way if you have a lot of one-time adhoc queries, they’ll take up way less space in the cache. *score!*

She also demystified parameterization for me, as in what is auto (simple) parameterization versus forced. How SQL Server auto parameterizes adhoc queries and so on.  A little light bulb above my head lit up with a “ding!”  I need to play with the scripts a bit first before I write about it. The whole session was full of extremely useful stuff! What she said made so much sense AND I can use it at work! That is so cool! As my DBA buddy would say, “Rebecca, you’re such a geek!” Why, thank you! 🙂 lol  I would highly recommend anyone to attend any of her sessions if you get the chance.

Left the session at 8:45 pm and made it home around 10 pm which is about 70 miles. I was pretty exhausted when I made it home. Note to self: buy muzzle for the whiny cat… should have done that 11 years ago…

So in summary, I’m VERY thankful this was a free session. Many thanks to Chris Shaw (Blog | Twitter) for getting this put together AND a big thanks to Dell for sponsoring it. Thanks to my honey for letting me go while he entertained the little one at home. Even though I was pretty darn tired with all the driving (did I mention I’m not a big fan of driving?), it was worth it. Next time, if I can’t bum a ride from someone, I just may stow away in the back of Chris’ truck.  lol


2 thoughts on “You Can Float my Boat, but Don’t Bloat My Cache

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s