Get Performance Tips Directly From SQL Server

Question

Post reply

Get Performance Tips Directly From SQL Server

ianstirk

Ten Centuries

Points: 1310
More actions
May 28, 2009 at 12:37 am

#200811

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

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

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

Rampall Sona SSC Journeyman Points: 77 More actions · Answer 1

Another way is to use :

select * from sys.dm_db_missing_index_details

Mark Jerrom SSC Enthusiast Points: 130 More actions · Answer 2

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 More actions · Answer 3

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: 130 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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

ianstirk Ten Centuries Points: 1310 More actions · Answer 8

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 Ten Centuries Points: 1027 More actions · Answer 9

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 More actions · Answer 10

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: 5407 More actions · Answer 11

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: 20961 More actions · Answer 12

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 More actions · Answer 13

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 More actions · Answer 14

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