Get Performance Tips Directly From SQL Server

  • Hi Gregory,

    Thanks for the updated routine. The derivation of the database from the plan attributes is very useful!



  • Excellent piece of work! Great job on the article too.



  • I used this and found a proc which is doing a table scan. I put index and then reran this proc again. It is still saying as table scan. But when I captured the actual execution plan by running the proc it is showing an index seek.

  • I'm curious why you chose to use an expensive wildcard string pattern match instead of using XQuery with the XML Namespace to query this information? An example of how you can parse the XML Doc using the DOM can be found on the following blog post:

    Digging into the SQL Plan Cache: Finding Missing Indexes

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Hi Jonathan

    Thanks for your question.

    In a word... “simplicity!”

    I wanted the code to be simple and understandable to a wide audience. Not everyone will be familiar with using XML and XQUERY, also the code tends to be bulky and less easy to maintain/extend (compare the code in the link you’ve supplied with the code in this article).

    It was a question I asked myself when I was considering the article 😉

    It could be argued if you’re interested in performance the XQUERY should be done inside the CLR...



  • Thanks for this helpful utility.

    Just a heads-up: on our 8-way 64-bit cluster that's rarely rebooted and the cache is rarely flushed, it takes about 4 mins to run this...

    Worth the wait, though 😉

  • Great article, very useful information.

    Over time it has become clear to me that there is gold in them thar system tables. I wonder if anyone could point me to a reference specifically about the system databases, what is contained and where, and how best to mine that gold.


  • I realize that this is an older post but I discovered it just now when it came in the recent mailing.

    My problem with this.

    getting error on the following line:

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    I have my compatibility set to 90.

    Any ideas. I tried this in both 2005 and 2008

  • Curious...

    It's telling me it's missing an index: Table InventoryGrouping, Column ClassValuePK, but I already have an index:

    CREATE NONCLUSTERED INDEX [IXClassValuePK] ON [dbo].[InventoryGrouping]


    [ClassValuePK] ASC


    What am I not understanding here?

    [font="Arial"]Halfbubble ------------
    You just can't do this stuff unless you're at least half a bubble off center.[/font]

  • Hi,

    What is the error you're getting?

    Do you have access to the DMVs? You need VIEW SERVER STATE permission.



  • Hi,

    What index does it say is missing? You should be able to see it's definition in the cached plan.



  • Hi dbowlin,

    Have a look at I'm working on a book containing about 100 sample scripts similar to the one in the article. You can download the first chapter (with some nice samples) for free.



  • Um, I don't know. Can't seem to get the same result from the stored procedure again.

    [font="Arial"]Halfbubble ------------
    You just can't do this stuff unless you're at least half a bubble off center.[/font]

  • Nice article Ian. Thanks.

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

  • Thanks Ian. I will definitely check it out.

Viewing 15 posts - 16 through 30 (of 36 total)

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