Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Get Performance Tips Directly From SQL Server Expand / Collapse
Author
Message
Posted Friday, June 12, 2009 9:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Saturday, June 13, 2009 4:42 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 1:05 PM
Points: 535, Visits: 726
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
Posted Sunday, June 14, 2009 3:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, April 12, 2014 6:50 AM
Points: 1,040, Visits: 274
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
Posted Monday, June 15, 2009 1:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 3:05 AM
Points: 48, Visits: 833
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
Posted Monday, June 15, 2009 1:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 3:05 AM
Points: 48, Visits: 833
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
Posted Monday, June 15, 2009 1:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 3:05 AM
Points: 48, Visits: 833
Hi Gregory,

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

Thanks
Ian

Post #734826
Posted Thursday, June 18, 2009 8:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 311, Visits: 1,079
Excellent piece of work! Great job on the article too.

Rudy



Post #737599
Posted Wednesday, July 29, 2009 10:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Monday, August 03, 2009 8:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
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
Posted Tuesday, August 04, 2009 5:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 3:05 AM
Points: 48, Visits: 833
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

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 »

Add to briefcase ««1234»»»

Permissions Expand / Collapse