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 ««123»»

Targeted Index Performance Improvements Expand / Collapse
Author
Message
Posted Friday, September 18, 2009 4:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:08 AM
Points: 77, Visits: 169
I suppose you could pass the database name as another parameter. I'm not sure what happens with a temporary stored procedure that looks in local-database objects if you try to run it against two different databases, does it keep looking in the first database? I have an idea I addressed that one time by making the whole body of the temp. s.p. be "dynamic SQL", i.e. EXEC sp_executesql...

I also don't remember the scope of the trick for making a non-temporary procedure accessible anywhere like master procedures from Microsoft, or whatether it was a good idea. If you don't like typing too much and you don't want no mess withimaster then you could place utility procedures in a database with a convenient short name, maybe on a linked server likewise. Let's see, you'd need that procedure returning dynamic SQL text which you'd execute locally...
Post #790249
Posted Friday, February 4, 2011 5:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 5:34 AM
Points: 227, Visits: 279
Hi,
I have another query that offers nearly the same output but what I miss here are two informations.
is_unique (sys.indexes) because sometimes you do have an unique index for consistency even if it is expensive.
And the size of the index (I take it from sys.dm_db_partition_stats) so you may see if the index is necessary or not. If the Clustered has a size of lets say 1 GB, a specific index may be pretty good even if the ratio between seek and updates is 1/100.
And if there are thousands of scans on a table with one page it does not matter or at least at the moment.

Thanks for sharing this script, it helps to find the bad guys out there that are torturing the servers
Post #1058635
Posted Friday, February 4, 2011 9:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:01 AM
Points: 1,299, Visits: 3,003

Thanks for the wonderful article Ian. As soon as i read this article i tested this out on one of our development environments. I compared the results from your code and from the execution plan. The results are not really the same. In the execution plan i can see there are many clustered scans going on and the result from your script doesn't show any scans at all. Mentioned below is my script:

EXEC dbo.dba_ShowIndexUsage @SQLToRun ='exec usp_myproc @Id=1237405'

This procedures has following objects:

i) few functions
ii) few views
iii) few tables.

Could you please say why is the output different or is it intended to use for different purpose? Thanks
Post #1058843
Posted Friday, February 4, 2011 11:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 7:53 AM
Points: 49, Visits: 850
Hi,

I’m glad you liked the article. I’m wondering if the reason why the output doesn’t match the cached plan is because the plan contains information about all paths of execution. If the parameter you pass results in only a few paths being executed, then the results will not match ALL of the cached plan.

You can discover a lot more about DMVs in my forthcoming book: SQL Server DMVs in Action www.manning.com/stirk which contains 100+ scripts like this one.

Chapter 1 can be downloaded for free and includes scripts for:

A simple monitor
Finding your slowest queries
Find your missing indexes
Identifying what SQL is running now
Quickly find a cached plan

Thanks
Ian
Post #1058932
Posted Friday, February 4, 2011 1:35 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:01 AM
Points: 1,299, Visits: 3,003

Thanks for quick reply. I am trying to understand this in a better way, if your script doesn't match the indexes what are actually used then where can i use this script?
Post #1059009
Posted Saturday, February 5, 2011 12:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 7:53 AM
Points: 49, Visits: 850
Hi,

Your results suggest, for the passed parameters, only certain indexes are being used. I'm sorry I can't give a more detailed answer, but I don't know the specifics of system you are using.

It might be worthwhile running the routine with some other stored procedures.

It might also be worthwhile running SQL profiler with your stored procedure, to determine what individual SQL statements within your stored procedure are being executed.

Thanks
Ian
Post #1059115
Posted Tuesday, February 8, 2011 3:47 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 18,060, Visits: 16,090
Thanks for the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1060715
Posted Friday, August 3, 2012 3:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 5, 2014 6:02 AM
Points: 308, Visits: 319
Thanks for sharing this, your article is well written and very easy to follow as is the output of the sproc. Any tool that can join the toolbox of a DBA is always welcome.
Post #1339705
Posted Friday, August 3, 2012 4:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 14, 2014 2:57 PM
Points: 8, Visits: 150
Just wanted to extend my thanks for this. I like what its doing but I think that for my purpose I'm running it over a week. I've stored some results for the intial select and i'll do the same select and run a comparison next week. This gives me more change to capture all activity across business critical databases.
Thanks
Post #1339727
Posted Friday, August 3, 2012 5:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 847, Visits: 478
If you with to do longer term analysis, save the results so that you can measure deltas. Also remember that restarting the instance resets the counters to 0.

------------
Buy the ticket, take the ride. -- Hunter S. Thompson
Post #1339749
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse