Get Performance Tips Directly From SQL Server

  • Comments posted to this topic are about the item Get Performance Tips Directly From SQL Server

  • Another way is to use :

    select * from sys.dm_db_missing_index_details

  • Msg 102, Level 15, State 1, Procedure dba_SearchCachedPlans, Line 27

    Incorrect syntax near '.'.

    Any ideas anyone? THe SQL looks good to me.

  • Hi,

    The code in the article seems to run ok (just run it in myself). But the code in the resources section (perftips.sql ) has formatting errors!

    Remember the routine only works on SQL Server 2005 or higher.



  • Sorted it - it was the compatibility level of the database - it was set to 80 but need to be at least 90.

  • How does accessing this table directly compare to the indexing hints you get when you use the 'Display Estimated Execution Plan' command in Management Studio?

  • Hi Brian,

    I think it is the same. For indexes you won’t see much difference other than it shows you what is missing from the all plans that have been run already across all the batches/sprocs rather than an individual batch/sproc.

    As well as being useful for everyday performance maintenance, I use this routine to get a plan who’s SQL that has been running slow (perhaps the SQL takes a long time to run or I don’t have permission to run it, or I don’t know the params etc).

    Hope this helps


  • How long a period are the usecounts representing? Since db inception? Or some statistics flush?

  • Hi,

    Typically the usecount represents the number of times the plan has run since it was last recompiled (maybe due to stats changing, environment variables changing, etc), or since the sql server was rebooted, or since the service has been restarted….



  • Hi Ian,

    Thanks for this, quite nice and I can already see a few databases I'd run this one against. Appreciate your post.

    Kind regards,

    Chris Rutherford

  • Microsoft also has a FREE reporting tool called dashboard_performance that shows missing indexes, plus a lot more. It runs on SQL 2005 SP2 or above. Here is the link for those that are interested:

    What I would like to know is, how reliable/accurate are the estimations? I ask because I have a large table (2.5 million+ rows). One of the columns is called iscom (have no idea what it is used for, as this database was created from a vendors application) which SQL Server thinks should be indexed for every query. Only problem is the only value in this column is 0. Plus I see that SQL Server reports that it wants indexes included on all fields....

  • Thank you for a useful article. I have a question. In the execution plan example, there are multiple "column" elements listed under "columngroup". Does this mean that SQL recommends having a composite index on these three columns? In the order listed? Or does it mean that SQL would like to have a separate index on each of the columns?

  • I modified you proc slightly to handle identifying the potential database by using plan attributes:





    ALTER PROC [dbo].[dba_SearchCachedPlans]

    @StringToSearchFor VARCHAR(255)

    ,@DBNAME VARchar(255) = ''

    ,@COUNT INT = 20



    Purpose: Inspects cached plans for a given string.


    Parameters: @StringToSearchFor - string to search for e.g. '%%'.

    Revision History:

    03/06/2008 Initial version

    06/11/2008 - modified to add DatabaseName from the DB were batch submitted

    Also added additional parameters to control what is returned

    Example Usage:

    1. exec dbo.dba_SearchCachedPlans @StringToSearchFor='%%',@DBNAME='HRMS', @COUNT=10

    2. exec dbo.dba_SearchCachedPlans '%%'

    3. exec dbo.dba_SearchCachedPlans @StringToSearchFor '%<TableScan%', @COUNT=30

    4. exec dbo.dba_SearchCachedPlans '%CREATE PROC%MessageWrite%'



    -- Do not lock anything, and do not get held up by any locks.



    st.text AS [SQL]

    , cp.cacheobjtype

    , cp.objtype

    , COALESCE(DB_NAME(st.dbid),

    DB_NAME(CAST(pa.value AS INT))+'*',

    'Resource') AS [DatabaseName]

    , cp.usecounts AS [Plan usage]

    , qp.query_plan

    FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

    OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa

    WHERE attribute = 'dbid' AND

    CASE when @DBNAME = '' THEN ''


    DB_NAME(CAST(pa.value AS INT)) + '*',

    'Resource') END


    AND CAST(qp.query_plan AS NVARCHAR(MAX))LIKE @StringToSearchFor

    ORDER BY cp.usecounts DESC


    Gregory A. Larsen, MVP

  • Hi Paul,

    Thanks for the information about the performance dashboard, its reports are very useful for tracking down a variety of performance problems. They work off the underlying DMVs, to explore these in further detail please see my MSDN article here:

    The main purpose of this current SQL Server Central article was more about how to view potential performance problems in code that is already running on the server. However it seems many people are interested in missing indexes in particular. I would suggest you look at the MSDN article mentioned above, it should have all the relevant code you need to investigate the missing indexes in greater detail, including details of how the estimates are made.

    As to your question about the ‘iscom’ column, it may be that the column is always retrieved from the table, hence instead of doing a key lookup (bookmark lookup), the optimiser suggest having the column INCLUDEd with any index.

    One thing to note about the suggested indexes is... they really only apply to the current SQL statement, i.e. they don’t take into consideration all the other SQL that might determine if this index should really be created.

    Hope this helps


  • Hi mishaluba

    Yes, the columns represent an index with multiple columns, and the columns should be created in the order listed. The Column group ‘usage’ will inform you if the index is used for equality, inequality or include usage.

    Again, one thing to note about the suggested indexes is... they really only apply to the current SQL statement, i.e. they don’t take into consideration all the other SQL that might determine if this index should really be created.

    To get further information about the meaning of these columns I would suggest you see my MSDN article here:



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

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