Hey! I Learned Something!

Day 15 (6.10.10):  My kingdom for coffee! Or what’s left of it. Not that I had much of a kingdom to begin with. *sigh* Had a cup of coffee at home this morning. Dropped little one off at summer preschool / daycare. Picked up a large coffee at my favorite drive-thru awesome coffee place. Still yawning. Need more coffee.  Note to self: next time you decide to bring coffee for DBA buddy or anyone else, make sure you get some of those sleeves for the coffee.  Coffee is hot. Burning hands is not a fun sensation. Especially when one has to stumble from the 3rd floor of the parking garage, across the alley, into the building (thankfully someone was there to open the door for me and work the elevator), and up to the correct floor. Whew!

Found our adopted developer giggling and muttering to herself in the corner… again… *sigh* I was afraid to ask so I left her alone. Hey, as long as she’s giggling it must be okay… right…? Note to self: check her “coffee” cup when she leaves…

My DBA buddy and I installed SQL Server 2008 onto a new server today. At the beginning of the install, we were prompted to install kb 942288-v4 (i.e. windows installer 4.5). My partner in crime could not resist and emailed Server Guy from her phone indicating we needed him to do a patch on the server a.s.a.p.. Of course, he came over promptly. When he saw which patch needed installing, the look he gave us said it all! It was the “why can’t you just click the *!$%?^?*!! button?” look.  Without missing a beat, she tells him we’re not allowed to touch it because it’s a server patch. He just shook his head and left. Lol I almost felt sorry for him… almost…  Plan “Drive Server Guy Crazy – Phase 1” completed.

The little yellow packets had an interesting affect on the so-called money plant. It’s now a jungle in here. Hmm… must have overdone it just a wee bit. On the positive side, we have a new hiding place… ha! they’ll never find us!

Hey, I learned something new today!  Granted, it’s nothing huge or extremely fascinating, but I thought it was kinda neat. For giggles, I decided to look at the cached plans for one of our SQL Server 2008 systems that has had memory issues. Note: this particular memory issue appears to be due to a renegade operating system DLL sucking up memory. Ze boss installed a patch courtesy of MicroSoft and so far so good! Only time will tell at this point.

Anyway, here’s the code I used to check the cache. You can also select from syscacheobjects if you want to. I received the same results with both.

select cacheobjtype
,objtype
,usecounts
,refcounts
,[text]
,objectid
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where db_name(dbid) = 'databaseA'
order by usecounts desc;

While I was shaking my head at all the adhoc query plans I spied (select * from this and that… in shock and awe I numbly sat *groan*), I noticed something that seemed odd. In the text column (or sql column if you’re using syscacheobject), there was a NULL value all by its lonesome… a NULL? Why would there be a null plan? Note: I was going to insert a picture of it but it wasn’t cooperating so I gave up. Here’s a description of what I found… The cacheobjtype was Compiled Plan. The objtype was Proc. The usecounts was over 140,000. The null seemed weird to me. So I tried to Google/Bing the answer. No luck. I twittered it. And then it dawned on me (smacked self up side head)… maybe I should check what object it belongs to. *duh!*

I found the mystery object’s name by including object_name(objectid) in the select statement.

select cacheobjtype
,objtype
,usecounts
,refcounts
,[text]
,objectid
,object_name(objectid)
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where db_name(dbid) = 'databaseA'
order by usecounts desc;

I then looked up the procedure itself only to find that it’s encrypted. Mystery solved! This is a vendor system so I’ve left out the name of the actual object just in case. I would rather not get myself in trouble. So I’m playing it safe. Anyway, when I thought about it, it made sense. If it’s encrypted, you shouldn’t be able to peek at the code via the cache. Very interesting, to me anyway. So that was the new thing I learned today. AND it was SQL-related… for once! *yay me!*

Our giggling adopted developer left for a few minutes. Checked her “coffee” cup which appeared to be filled with… coffee… hmmm… Note to self: the giggling must be a result of the project she’s working on… or it’s a side effect of our conversion process to convert her to the dark side, err.. I mean our side of the data fence… must bring her more dark chocolate next week to continue the conversion process…

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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