Get Performance Tips Directly From SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ian Stirk » Get Performance Tips Directly From SQL Server...

36 posts, Page 2 of 4

««1 2 3 4 »»»

Get Performance Tips Directly From SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

paul weegar

Posted Friday, June 12, 2009 9:10 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 12, 2011 7:20 AM
Points: 94, Visits: 30

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

Post #733944

mishaluba

Posted Saturday, June 13, 2009 4:42 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525, Visits: 617

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?

Post #734457

Greg Larsen

Posted Sunday, June 14, 2009 3:49 PM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255

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

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com

Post #734708

ianstirk

Posted Monday, June 15, 2009 1:42 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:22 AM
Points: 47, Visits: 762

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

Post #734824

ianstirk

Posted Monday, June 15, 2009 1:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:22 AM
Points: 47, Visits: 762

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

Post #734825

ianstirk

Posted Monday, June 15, 2009 1:54 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:22 AM
Points: 47, Visits: 762

Hi Gregory,

Thanks for the updated routine. The derivation of the database from the plan attributes is very useful!

Thanks
Ian

Post #734826

Rudy Panigas

Posted Thursday, June 18, 2009 8:24 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 5:32 PM
Points: 306, Visits: 1,013

Excellent piece of work! Great job on the article too.

Rudy

Post #737599

pmohan

Posted Wednesday, July 29, 2009 10:36 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 08, 2011 9:47 AM
Points: 95, Visits: 158

I used this and found a proc which is doing a table scan. I put index and then reran this proc again. It is still saying as table scan. But when I captured the actual execution plan by running the proc it is showing an index seek.

Post #761778

Jonathan Kehayias

Posted Monday, August 03, 2009 8:49 AM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

I'm curious why you chose to use an expensive wildcard string pattern match instead of using XQuery with the XML Namespace to query this information? An example of how you can parse the XML Doc using the DOM can be found on the following blog post:

Digging into the SQL Plan Cache: Finding Missing Indexes

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs

Post #764111

ianstirk

Posted Tuesday, August 04, 2009 5:57 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:22 AM
Points: 47, Visits: 762

Hi Jonathan

Thanks for your question.

In a word... “simplicity!”

I wanted the code to be simple and understandable to a wide audience. Not everyone will be familiar with using XML and XQUERY, also the code tends to be bulky and less easy to maintain/extend (compare the code in the link you’ve supplied with the code in this article).

It was a question I asked myself when I was considering the article Wink

It could be argued if you’re interested in performance the XQUERY should be done inside the CLR...

Thanks
Ian

Post #764730

« Prev Topic | Next Topic »

36 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions