Maciej Pilecki - SQL Server lessons from the field

Conference season opened

2008-09-25T17:14:00Z

OK, so I have opened my autumn conference season today with two presentations at SQLCON 2008 (part of BASTA!) in Mainz, Germany. If not for the fact that I was quite miserable due to extreme pain in my lower back (I could hardly walk and there was no position in which I would be comfortable), it was great fun! Luckily, they had a masseuse on site and she did a great job fixing me. Many thanks to the organizers and attendees of that fine conference! I hope to be back next spring.

So, what's next? Well, quite a bit - this autumn I am doing "a few" conferences. Here is a list of the confirmed ones:

September 29th - October 1st - MS Partner Conference in Riga, Latvia - I am doing both the main conference as well as 2-day post-conf SQL 2008 workshop.
October 1st - 3rd - MS Partner Conference in Vilnius, Lithuania - I will miss the main conference but I will be there in time to do the workshop on Thursday and Friday.
October 13th - 14th - DevReach in Sofia, Bulgaria. I will be there only on Monday (doing 2 sessions), but this conference is one of the highlights in my schedule - it's one of the best in CEE.
October 14th - 17th - Sinergija 08 in Novi Sad, Serbia. First time for me but I am looking forward to being part of this great conference.
October 21st - 22nd - TrivadisOPEN 2008 in Zurich, Switzerland. Dual first time: first time at this conference and first time in Switzerland. Wow, some new country for me to visit. :)
November 3rd - 7th - TechEd ITPro EMEA in Barcelona, Spain. A classic one, not to be missed.
November 18th - 21st - PASS Commuity Summit 2008 in Seattle, WA. First time for me at PASS Summit in US. This one is a must for any SQL Server professional!
November 25th - 26th - SQL Server Energy 2008 in Vienna, Austria. Lots of friends there - it will be a fun conference, I am sure.

This may seem like a very busy schedule but in fact, it's nothing new to me. Actually, I still have a few gaps in my calendar so if you are organizing a conference and would like to invite me, feel free to give me a call. :)

I am looking forward to seeing you at one (or two) of those conferences! Make sure you join me at one of my sessions.

And for my Polish readers: There is a big gaping hole in this schedule that some of you may notice - it's the Microsoft Technology Summit in Warsaw in less than 2 weeks. Since many people asked me about this, I feel compelled to answer: I will not be there because I have not been invited, not because I don't have time. Believe me, I wish I could join you there, but it was not for me to decide. If you want to see me there next year, make sure that you let the organizers know. I will be happy to come.

Enjoy the conference season this year!

Clearing your ad-hoc SQL plans while keeping your SP plans intact

2008-09-20T22:40:00Z

The problem of the procedure cache being inflated with ad-hoc SQL plans that are almost never reused has been discussed many times. You can find an interesting post about this by Tony Rogerson here. Another post by Lara Rubbelke that touches on the same problem is here.

Just to summarize: on systems that have a lot of ad-hoc SQL queries, the procedure cache can get rather large. That is especially true for some 64bit systems with a lot of memory as the procedure cache has higher thresholds for memory pressure there. The effect is usually the size of procedure cache being disproportionally large in comparison with the total system memory and the size of buffer pool used for data pages. I have personally seen systems with several GBs of memory used for procedure cache that was filled mostly with ad-hoc and seldom reused plans. I have even seen this occurring on systems that were using stored procedures exclusively, as there were still a lot of ad-hoc queries coming from SQL Agent engine as well as various parts of SQL Server management tools...

I could go for hours explaining the reasons why this problem occurs and different ways to solve it. I cover this topic to some extent in my "Dude, Where Is My Memory?" conference session (you can see this session at many conferences throughout Europe this fall, including TechEd ITForum). I am contemplating writing an article about this, but no promises at this point...

OK, back to our problem... How to check if your server is experiencing this problem? The easiest way is to run this simple query:

select objtype,
count(*) as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts) as avg_use_count
from sys.dm_exec_cached_plans
group by objtype

If you have problems with ad-hoc plans, you are likely to see several thousand of plans with objtype "Adhoc", occupying as much as several GBs (bulk of all memory consumed by the procedure cache) and having very low avg_use_count. The ad-hoc plans generally accumulate over time (up to the memory pressure limits of the procedure cache) so you may not see this right after a server restart or proc cache flush - but you will see the number constantly growing until it reaches the limit.

If you want to get rid of the ad-hoc plans, one simple and effective yet somewhat dangerous way is to clear the procedure cache completely with DBCC FREEPROCCACHE. I have seen many people do it, even having it scheduled as a regular job. It works great to get rid of all the clutter in procedure cache but the problem is that since you are clearing the cache completely, you are also getting rid of your valuable SP plans. This can cause a noticeable system slowdown right after, due to the cost of compiling any incoming SP call and possible contention in case of many compilations occurring at the same time (compilations in SQL Server are throttled). And of course you will have to do it again in a few hours/minutes as the ad-hoc plans will start building up again...

So, I recently started wondering how to be a little more selective in what we throw out of the procedure cache. The revelation came to me about a week ago and I already had a chance to test it on a few systems. The idea is so simple that I am actually very surprised that I have never seen it described anywhere before. The key to understanding how this works (and how I arrived at my solution) is to realize that there is no single procedure cache. The procedure cache actually consists of 4 distinct cache stores that hold different types of plans. Those cache stores are:

CACHESTORE_OBJCP - these are "Object Plans" - stored procedures, functions and triggers. Generally, the good stuff.
CACHESTORE_SQLCP - these are "SQL Plans" - ad-hoc SQL statements (including parameterized ones) and prepared statements. This is the stuff that we are after.
CACHESTORE_PHDR - so-called "Bound Trees" for views, constraints and defaults. Irrelevant for the problem discussed.
CACHESTORE_XPROC - not really execution plans but rather pointers to the entry points of your extended SPs.

With all this information, I am sure you can guess where I am going: Can we clear just one part of the procedure cache? Just the cache store holding the ad-hoc plans?

And the answer is: YES!

All you have to do is run:

DBCC FREESYSTEMCACHE('SQL Plans')

and you will see all your ad-hoc and prepared plans vaporize, while your SP plans will remain intact. This is much nicer than the overkill of DBCC FREEPROCCACHE and will have much lower performance impact on the system. And, in case you wonder, the parameter in the statement above is the name of the cache store of type CACHESTORE_SQLCP.

I am curious to hear your comments and whether this solution works for your scenario...

Time to get started?

2008-09-20T21:06:00Z

Well, I have created this blog some time ago but somehow never got around to write anything here. While this is hardly surprising given my very busy schedule (and the fact that I have 3 small kids), I think this is also a major failure on my part. Seems like anyone who can write has a blog (or two) these days so not having one seems like a major oversight. I guess I should start blogging before my kids do (or blogging goes out of fashion, in favor of something else)... :)

So, I decided that it is time to get started. Mostly because I have a bunch of technical stuff that I would like to share with the community, but I would also like to use this blog to connect with people out there - customers, friends, fellow MVPs and people who attend my conference sessions and training. I would like to use it to answer some of the questions I am receiving after my presentations, share the lessons learned while consulting my customers (hence the name of the blog) or just share some thoughts and observations I may have about SQL Server and its surroundings. And I promise that I will try not to insult or annoy anyone while I am doing that... :)

Stay tuned!