Exporting Execution Plans - SQL Spackle

  • Comments posted to this topic are about the item Exporting Execution Plans - SQL Spackle[/url]

    Thanks to Adam Machanic for pointing me to the fix for the special character problem (code with all the replaces) related to pulling out the code to dump it in the [processing-instruction] where I had been victim to it's bug in the past (see my previous article on sp_Who2 [/url] ).

    Adam pointed out that he had that bug fixed in his sp_whoisactive script which can be downloaded here.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • How about some Powershell love to help with this: Export top n SQLPlans

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Might be me, but not sure where 'Second' is?

  • Jason,

    Thanks for taking the time to post this article. One question though: What is the purpose of the WHERE clause:

    WHERE st1.text like '%sys.sql_modules%'

    Thanks again,

    Lee

  • ALZDBA (10/29/2013)


    How about some Powershell love to help with this: Export top n SQLPlans

    Thanks Johan. That is a welcome addition.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Michael.Beeby (10/29/2013)


    Might be me, but not sure where 'Second' is?

    I'm checking now to find where that section of the article disappeared to.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Lee Linares (10/29/2013)


    Jason,

    Thanks for taking the time to post this article. One question though: What is the purpose of the WHERE clause:

    WHERE st1.text like '%sys.sql_modules%'

    Thanks again,

    Lee

    st1.text in this example was used to filter down to queries that I knew had that text I wanted to query. Replace that with the text of a known string (such as the name of a stored procedure or a table involved in the query) in the query you are trying to find.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Makes sense. Thanks again. This is a very useful query.

    Lee

  • Lee Linares (10/29/2013)


    Makes sense. Thanks again. This is a very useful query.

    Lee

    You're welcome.

    Glad it could be of use.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • There was a problem with some information missing in the article. My apologies for that. I have corrected the text.

  • Steve Jones - SSC Editor (10/29/2013)


    There was a problem with some information missing in the article. My apologies for that. I have corrected the text.

    Thanks Steve

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Nice article Jason.

    However, seeing as this is a SQL Spackle article, you did forget something...

    Crack filled.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WayneS (10/29/2013)


    Nice article Jason.

    However, seeing as this is a SQL Spackle article, you did forget something...

    Crack filled.

    In that case, it's a good thing you came along and filled the crack

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • The query to extract the plan cache fails when there is an offline database on the server.

  • Cheetah (10/30/2013)


    The query to extract the plan cache fails when there is an offline database on the server.

    I have two databases that are offline on my instance currently and the query succeeds. I don't think the issue is related explicitly to an offline database. Maybe you have queries returning in your results for the offline database based on the filter you are using. Maybe you have some queries that join to these offline databases that are returned in your result set based on the filter you are using.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply