Monday, October 8, 2007

How many times stored procedures have recompiled?

I have recently visited our client and we found out that He has a very high number of stored procedures that have recompiled. Certainly, we have observed some perfomance decreasing. Fortunately , the client has alredy upgraded to SQL Server 2005(SP2) and apart from running SQL Server Profile to indentify those stored procedures we came out with the following query.

It gives you the top 10 stored procedures that have been recompiled.

select *
from sys.dm_exec_query_optimizer_info

select top 10
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc

Note that the plan_generation_num indicates the number of times the stored procedure has recompiled. Very cool feature.

4 comments:

xiaoqiu said...

Even it got me crazy enough to buy Guild Wars Prophecies with some my own GuildWars Gold and Nightfall and Eye of the North. I wonder to know why are not you guys and gals who like the PVP concept buying this awesome game. okey, it is expensive to buy gw gold all parts. If you lack the GuildWars money, but it is worth it and no monthly costs and the game gets updated weekly at least. Once again, if anyone wants to play Guild Wars sometime add me to friends here Guild Wars Gold. And realize how much cheap gw gold I bought in the last couple of weeks, months, years. So I found this Guild Wars Factions and installed it.

products said...

China Wholesale has been described as the world’s factory. This phenomenom is typified by the rise ofbusiness. Incredible range of products available with China Wholesalers “Low Price and High Quality” not only reaches directly to their target clients worldwide but also ensures that wholesale from china from China means margins you cannot find elsewhere and buy products wholesaleChina Wholesale will skyroket your profits.wedding dressescheap naruto cosplayanime cosplay

products said...

Women’s nike tn Shox Rivalry est le modèle féminin le plus tendance de baskets pour le sport. tn chaussuresConcernant la semelle :spyder jacketsCheap Brand Jeans Shop - True Religion Jeans cheap nike shox & Puma Shoes Online- tn nike,Diesel Jeans le caoutchouc extérieur, l’EVA intermédiaire Levis Jeanset le textile intérieur s’associent pour attribuer à la.ed hardy shirts pretty fitCharlestoncheap columbia jackets. turned a pair of double plays to do the trick.Lacoste Polo Shirts, puma basket, Burberry Polo Shirts.wholesale Lacoste polo shirts and cheap polo shirtswith great price.Thank you so much!!cheap polo shirts men'ssweate,gillette mach3 razor bladesfor men.As for Cheap Evisu JeansCheap Armani Jeanspolo shirtsPuma shoes

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!