Natrafiłem niedawno na blog Maćka Pileckiego (SQL MVP). Ponieważ na tym blogu istnieje tylko jeden wpis techniczny pozwolę go sobie tutaj w części przytoczyć aby… ocalić od zapomnienia. Cały wpis pochodzi z blogu: MACIEJ PILECKI – SQL SERVER LESSONS FROM THE FIELD.
Clearing your ad-hoc SQL plans while keeping your SP plans intact
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…
Całą resztę, a więc zapytanie oraz kilka wskazówek znajdziecie na blogu SQL SERVER LESSONS FROM THE FIELD
0 Comments.