Get Performance Tips Directly From SQL Server

  • ianstirk

    Ten Centuries

    Points: 1310

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

  • Rampall Sona

    SSC Journeyman

    Points: 77

    Another way is to use :

    select * from sys.dm_db_missing_index_details

  • Mark Jerrom

    SSC Enthusiast

    Points: 120

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

    Incorrect syntax near '.'.

    Any ideas anyone? THe SQL looks good to me.

  • ianstirk

    Ten Centuries

    Points: 1310

    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.

    Thanks

    Ian

  • Mark Jerrom

    SSC Enthusiast

    Points: 120

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

  • brian.bosman

    SSC Rookie

    Points: 33

    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?

  • ianstirk

    Ten Centuries

    Points: 1310

    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

    Ian

  • tom.christopher

    SSC Journeyman

    Points: 82

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

  • ianstirk

    Ten Centuries

    Points: 1310

    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….

    Thanks

    Ian

  • Chris Rutherford

    SSC Eights!

    Points: 967

    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

  • paul weegar

    SSC Enthusiast

    Points: 106

    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:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

    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....

  • Misha_SQL

    SSCertifiable

    Points: 5385

    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?

  • Greg Larsen

    SSC-Insane

    Points: 20605

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

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROC [dbo].[dba_SearchCachedPlans]

    @StringToSearchFor VARCHAR(255)

    ,@DBNAME VARchar(255) = ''

    ,@COUNT INT = 20

    AS

    /*----------------------------------------------------------------------

    Purpose: Inspects cached plans for a given string.

    ------------------------------------------------------------------------

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

    Revision History:

    03/06/2008 Ian_Stirk@yahoo.com Initial version

    06/11/2008 GregALarsen@msn.com - 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%'

    -----------------------------------------------------------------------*/

    BEGIN

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

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SELECT TOP (@COUNT)

    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 ''

    ELSE COALESCE(DB_NAME(st.dbid),

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

    'Resource') END

    IN (RTRIM(@DBNAME),RTRIM(@DBNAME) + '*')

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

    ORDER BY cp.usecounts DESC

    END

    Gregory A. Larsen, MVP

  • ianstirk

    Ten Centuries

    Points: 1310

    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: http://msdn.microsoft.com/en-gb/magazine/cc135978.aspx

    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

    Ian

  • ianstirk

    Ten Centuries

    Points: 1310

    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: http://msdn.microsoft.com/en-gb/magazine/cc135978.aspx

    Thanks

    Ian

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

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