SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get Performance Tips Directly From SQL Server


Get Performance Tips Directly From SQL Server

Author
Message
paul weegar
paul weegar
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 31
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
Misha_SQL
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2214 Visits: 1023
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
Greg Larsen
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6847 Visits: 290
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
ianstirk
ianstirk
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 1037
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
ianstirk
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 1037
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
ianstirk
ianstirk
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 1037
Hi Gregory,

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

Thanks
Ian
Rudy Panigas
Rudy Panigas
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3824 Visits: 1325
Excellent piece of work! Great job on the article too.

Rudy



pmohan
pmohan
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 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.
Jonathan Kehayias
Jonathan Kehayias
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10373 Visits: 1819
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
ianstirk
ianstirk
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 1037
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search