What tables and columns are this database Sp's using?

  • Hi: I’m trying to find a way to know what tables and what columns of those tables the deferent sp's of a DB are using so I can determine what are the best Index options for all of the sp’s.

    Any buddy has an Idea?

  • Try using The DMV's to tell you what indexes need to be built (or dropped)

    The output of this query can be interpreted according to Microsoft best practices:

    Index Advantage above 5k means one should consider and index

    Index Advantage above 10k means an index needs to be built

    I swiped some of the code from somewhere, but most of it is mine including the 'index advantage' calculation which I swiped from Microsoft's MCITP books when I was taking the exams.

    SELECT

    user_seeks * avg_total_user_cost * (avg_user_impact *0.01) as 'index advantage'

    ,mig.index_group_handle

    ,mid.index_handle ,

    CONVERT ( decimal ( 28 , 1 ), migs.avg_total_user_cost * migs.avg_user_impact *

    ( migs.user_seeks + migs.user_scans )) AS improvement_measure

    , 'CREATE INDEX missing_index_' + CONVERT ( varchar , mig.index_group_handle ) + '_'

    + CONVERT ( varchar , mid.index_handle ) + ' ON ' + mid.statement

    + ' (' + ISNULL ( mid.equality_columns , '' )

    + CASE WHEN mid.equality_columns IS NOT NULL

    AND mid.inequality_columns IS NOT NULL

    THEN ','

    ELSE ''

    END

    + ISNULL ( mid.inequality_columns , '' ) + ')'

    + ISNULL ( ' INCLUDE (' + mid.included_columns + ')' , '' ) AS create_index_statement

    , migs.*

    , mid.database_id

    , mid.[object_id]

    FROM sys.dm_db_missing_index_groups mig

    INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

    WHERE CONVERT ( decimal ( 28 , 1 ), migs.avg_total_user_cost * migs.avg_user_impact * ( migs . user_seeks + migs.user_scans )) > 10

    ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans ) DESC

    If the code doesn't work, it's a copy/paste error from my website. a bunch of the formatting was goofy so it might have some spaces where it shouldn't.

  • Hi! Thanks for the help!

    For what I see this query doest makes the indexes it only suggest it right?

  • Yup, and you should test any recommendations out on a dev system to be sure that they really do help. Also check the recommendations to see if there's an existing index that can be modified (by adding columns) to produce what's recommended.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • fjmorales (10/22/2008)


    Hi: I’m trying to find a way to know what tables and what columns of those tables the deferent sp's of a DB are using so I can determine what are the best Index options for all of the sp’s.

    In general, the best way to go about this is to run each of the procs in management studio and used the execution plan to see what the query's doing, then add indexes and see if the queries use them. I would suggest that to start with, you pick the 5 or so worst performing procs that you have (use profiler to determine that) and optimise those, then pick another 5, etc

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • gail always provides perfect solution

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • Cool, I'll try it... If I find the time!!! I'll let you know when I do it.

    Thanks a lot for the help!

Viewing 7 posts - 1 through 6 (of 6 total)

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