Cached Execution Plans in SQL Server

I have been working on a performance problem recently, so you might see several blog entries with information that help me. Hopefully they will help you.

Update: Here is a query to get the execution plan of the most frequently used queries.

Getting the SQL Server execution plan from a production can be difficult, since you are not running the code within Enterprise Manager. You can still get the execution plan of any running statement and display the graphical representation in Enterprise Manager.

If your script is currently running, you can lookup its execution plan in the plan cache. The steps are:

  1. Run a query to get the the plan_handle of currently running code
  2. Lookup the execution plan for the plan_handle.

The first query is:

SELECT
sder.session_id AS [SPID],
sder.sql_handle as [SQL_Handle],
sder.plan_handle as [PLAN_Handle],
sdes.login_name AS [Login],
sd.name AS [DBName],
sder.start_time AS [Start Time],
sder.status AS [Status],
sder.command AS [Command],
sdet.text AS [SQL Text],
sder.percent_complete AS [Pct Cmplt],
sder.estimated_completion_time AS [Est Cmplt Time],
sder.wait_type AS [Wait],
sder.wait_time AS [Wait Time],
sder.last_wait_type AS [Last Wait],
sder.cpu_time AS [CPU Time],
sder.total_elapsed_time AS [Total Elpsd Time],
sder.reads AS [Reads],
sder.writes AS [Writes],
sder.logical_reads AS [Logical Reads]
FROM
sys.dm_exec_Requests sder
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sdet
JOIN sys.dm_exec_sessions sdes on sder.session_id = sdes.session_id
JOIN sys.databases sd on sder.database_id = sd.database_id
WHERE
sder.session_id <> @@SPID and sder.session_id > 50

The second query is:

SELECT * FROM sys.dm_exec_query_plan ( <PLAN_Handle> )
--here <PLAN_Handle> is supplied based on
--the results from the first query.

This gives you the XML execution plan that you can copy and paste into notepad, save it with a *.sqlplan extension, and double click on the file to view the graphical version of the execution plan.

About Clay Lenhart

I am a DBA/Technical Architect for Latitude Group and love technology.
This entry was posted in SQL Server Administration and tagged , , , , , , . Bookmark the permalink.

35 Responses to Cached Execution Plans in SQL Server

  1. Sanjay Kumar Dinda says:

    Hi ,
    I am closely working with large database. Frequently face problem with performance … I red your blog.. This will help me like lot of peoples ..
    Thanks for sharing the same …
    Looking more opyimization tips.
    have a nice day
    Regards
    Sanjay

  2. Rachel Ogden says:

    Hi there! I could have sworn I’ve been to this blog before but after going through a few of the articles I realized it’s new to me. Nonetheless, I’m definitely delighted I came across it and I’ll be book-marking it and checking back frequently!

  3. Before applying this topical treatment for Impetigo, one should clean the affected area first with warm water and antibacterial soap. Impetigo patients are seen with sores, and in some cases, boils and blisters on the face. Impetigo is a skin disease or infection, which is caused by bacteria known as staphylococcal or streptococcal and is contagious.

  4. Mary Harris says:

    Hello, its pleasant paragraph on the topic of media print, we all understand media is a enormous source of facts.

  5. I’ve been surfing online more than three hours these days, yet I by no means found any interesting article like yours. It is pretty worth sufficient for me. In my view, if all webmasters and bloggers made just right content material as you probably did, the internet will be a lot more useful than ever before.

  6. Wendy Hart says:

    Exceedingly alluring suggestions you’ll have remarked, many thanks for putting up.

  7. Sean Quinn says:

    Because here is a list of multiplayer games is that the leave was asked for more. At present, you have the opportunity not only to communicate but also to have fun in social networks. If your answer is “yes”, you may want to link your Facebook ad to your Facebook page.

  8. This will be a excellent site, could you be involved in doing an interview about how you developed it? If so e-mail me!

  9. Rose Harris says:

    For newest information you have to pay a visit world wide web and on web I found this website as a finest site for hottest updates.

  10. Rose Poole says:

    Your style is very unique compared to other people I’ve read stuff from. I appreciate you for posting when you have the opportunity, Guess I will just bookmark this site.

  11. I together with my pals came going through the excellent ideas from your web page while immediately got an awful suspicion I never expressed respect to the site owner for them. All of the men are already absolutely glad to learn all of them and now have clearly been using them. Thank you for simply being quite kind and for opting for such wonderful ideas millions of individuals are really desperate to know about. Our honest regret for not saying thanks to you earlier.

  12. i would like to mention many thanks with this article hope you pal xoxo.

  13. Una Coleman says:

    I am writing to make you know what a remarkable experience my wife’s girl developed viewing your web page. She realized such a lot of details, with the inclusion of what it is like to possess a great teaching mood to make the rest easily know selected complicated things. You undoubtedly did more than my expectations. I appreciate you for giving those warm and friendly, safe, explanatory and also cool tips about your topic to Mary.

  14. I almost never create remarks, however after browsing a few of the responses on this page Chabot College.

  15. Andrea Brown says:

    My Partner And I simply wish to notify you which I am certain novice to having a blog and utterly cherished your information. Very possible I am prone to bookmark your blog post . You absolutely have superb article reports. Be Thankful For it for expressing with us your site post.

  16. Heather Parr says:

    This very blog is definitely educating and besides diverting. I have picked up helluva interesting advices out of this blog. I’d love to return over and over again. Thanks a lot!

  17. wonderful issues altogether, you just gained a new reader. What may you recommend in regards to your submit that you simply made a few days ago? Any positive?

  18. There is certainly a lot to know about this subject. I really like all the points you made.

  19. I have been checking out a few of your posts and i must say clever stuff. I will make sure to bookmark your blog.

  20. James Welch says:

    I would like to thnkx for the efforts you’ve put in writing this blog. I’m hoping the same high-grade blog post from you in the upcoming as well. In fact your creative writing abilities has encouraged me to get my own web site now. Actually the blogging is spreading its wings rapidly. Your write up is a great example of it.

  21. I have been gone for a while, but now I remember why I used to love this blog. Thanks, I will try and check back more often. How often do you update your web site?

  22. [url=http://www.wtobrand.com/sbfr4.html]口コミで高評価!業界最高級品質のスーパーコピーブランド激安通販専門店!スーパーコピーのブランド時計などブランドコピー品買取やブランドバッグ、ブランド財布の格安販売!まずはお気軽にお問い合わせください。信用第一、送料無料。スーパーコピーブランドなら当店で!スーパーコピー時計などブランドコピー品買取や ブランドバッグ、ブランド財布を低価でお客様に提供します。偽物ブランドと言っても なんと本物と見分けがつかないぐらい!まずはお気軽にお問い合わせください。ネットワークの時代の中の極めて優秀な人、ブランドコピー、スーパーコピーブランドN品 超人気専門サイト問屋 !!精巧に作られたブランドコピーバッグ当サイト販売したブランド コピーなので正規品と同等品質提供した格安で完璧な品質のをご承諾します、2015ー2016の新素材-新作!人気 スーパーコピーブランド,スーパーコピー時計,スーパーコピー激安販売,ブランドスーパーコピー専門![/url]

  23. [url=http://www.ooowatch.com/tokei/hermes]”日本一流ヴィトン コピー、ヴィトン コピー 激安(N品)専門店、全国送料無料、ヴィトン コピー 通販,ヴィトン コピー商品,ヴィトン コピー 販売,当サイト販売したヴィトン コピーなので正規品と同等品質提供した格安で完璧な品質のをご承諾します、当サイトは最高級ヴィトン コピー激安通信販売店です ホームページをクリックして商品をご覧下さい.ロレックスコピー,業界No.1人気スーパーコピーロレックス腕時計専門販売ブランド サングラス 、ブランドコピー、ルイ ヴィトン サングラス、サングラス コピー、シャネル サングラス、グッチ サングラス、コーチ サングラス、D&G サングラス、偽物 サングラス 、激安 サングラス 韓国 人気 楽天市場 モンクレール 代引き 海外ブランド コピー ダミエ 偽者 ルイヴィトンコピーグッチコピーエルメスコピーロレックスコピーカルティエコピーオメガコピーブルガリ コピー 時計スーパーコピーも多彩な品揃えでお待ちしています。スーパーコピー,ブランドコピー,コピーブランド,シャネルコピー ブランド商品は信頼と安心のお店、ベルメゾンネットで[/url]

  24. [url=http://www.bagkakaku.com/vuitton_bag/2/N48180.html]プラダ バッグ コピー 2015新作、536点入荷 !トート、アウトレット、 ピンクなら豊富な品揃えの当店へ!激安 CELINEセリーヌ バッグ スーパーコピー大量入荷! ブランド コピーサングラス レイバン アルマーニ バーバリー シャネル 3000点新作 ! 2点5000円ブランド コピー Tシャツ アルマーニ D&G MONCLER 新作800点入荷! 2点以上注文20%OFF ! 母の日プレゼント特集 イヤリング 指輪 ネックレス ブレスレット ヘアピン 新作 1000点入荷![/url]

  25. [url=http://www.ooobag.com/wallet/louisvuitton/index_12.html]ブランドN級品ブランドコピー 代引き,スーパーコピー時計,ブランドN級品,楽天コピーブランド,,偽物ブラン日本最大級の最高のスーパーコピーブランド財布激安代引き販売店,スーパーコピー時計の激安老舗.!国内外No.1時計コピー工房,アフターサービスも自ら製造したスーパーコピー時計なので、技術力でお客様に安心のサポー トをご提供させて頂きます。スーパーコピー 代引きN品をご 購入の方は、こちらへ.弊社は正規品と同等品質のコピー品を低価で お客様に提供します!すべての商品は品質2年無料保証です。100%実物写真ですし、品質が完璧です!”スーパーコピーブランド財布激安 偽物財布激安コピー ルイヴィトン財布偽物,偽物財布コピー[/url]

  26. 超人気質屋

    ★最高等級時計大量入荷!
    ▽◆▽世界の一流ブランド品N級の専門ショップ★
    注文特恵中-新作入荷!-価格比較.送料無料!
    ◆主要取扱商品 バッグ、財布、腕時計、ベルト!
    ◆全国送料一律無料
    ◆オークション、楽天オークション、売店、卸売りと小売りの第一選択のブランドの店。
    ■信用第一、良い品質、低価格は 私達の勝ち残りの切り札です。
    ◆ 当社の商品は絶対の自信が御座います。
    おすすめ人気ブランド腕時計, 最高等級時計大量入荷!
    ◆N品質シリアル付きも有り 付属品完備!
    ☆★☆━━━━━━━━━━━━━━━━━━━☆★☆
    以上 宜しくお願い致します。
    (^0^)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>