Post reply

Seeking a way to correlate each missing index with SQL and Object Name (that will benefit from each index to be created).

  • March 23, 2016 at 5:42 am

    #309341

    We do not want to create ALL indexes suggested by DMVs as Missing Indexes, we want to see how often it is called, and what the calling SQL and/or SP or FN is that calls that SQL..., this is exactly why I am looking to find out what SQL (and preferrably Calling_Object_Name (SP or FN...) that is associated with each missing indexes, so that we look through the calling SQL (that would benefit from each missing index), so that out of 200 suggested indexes we pick like 20 to create , that would be most benefitial/

    Attached is the screenshot of the best result i got so far (can see SQL statements in second column (Doc attached) . this is the best i got so far.

    but looking if it is possible to add just one more column: Calling_Object_Name

    Likes to play Chess

  • March 23, 2016 at 8:12 am

    #1867090

    Jason Strates article lists missing indexes along with usecounts, costs, etc.

    i'd start there and enhance as needed.

    http://sqlmag.com/blog/missing-indexes-correlated-against-execution-costs-and-counts

    othercolumns ...include_columns usecounts Cost AggregateCost Impact

    [CurStatusID] 4123 30.9236 127498.0028 27.3751

    [DateExported] 989 45.4212 44921.5668 44.0512

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

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