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


Targeted Index Performance Improvements


Targeted Index Performance Improvements

Author
Message
rja.carnegie
rja.carnegie
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 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...
Christoph D
Christoph D
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 357
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 ;-)
curious_sqldba
curious_sqldba
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3678 Visits: 3662
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
ianstirk
ianstirk
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 1037
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
curious_sqldba
curious_sqldba
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3678 Visits: 3662
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?
ianstirk
ianstirk
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 1037
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
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40737 Visits: 18565
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

Squig
Squig
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 348
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.
mark.pointon
mark.pointon
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 192
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
Bryant McClellan
Bryant McClellan
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 543
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
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