SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ian Stirk / Targeted Index Performance Improvements / Latest Posts

RE: Targeted Index Performance Improvements

Eddie Kramer — Tue, 07 Aug 2012 08:33:52 GMT

Include usage of indexes on views?Something like:Change: INNER JOIN sys.tables st ON st.[object_id] = si.[object_id]To: INNER JOIN sys.objects st ON st.[object_id] = si.[object_id] AND st.type IN ('U','V')Your script will be of use to me. Thanks.

RE: Targeted Index Performance Improvements

Ness — Fri, 03 Aug 2012 07:58:56 GMT

Hi Guys, Nice script but I would not advise putting this on a production box due to the SQL injection risk of the -- Execute passed SQL. EXEC (@SQLToRun) part of the proc. :w00t:

RE: Targeted Index Performance Improvements

mtassin — Fri, 03 Aug 2012 07:31:05 GMT

[quote][b]rja.carnegie (9/17/2009)[/b][hr]I need to read this article carefully, but my immediate reaction is that the indexes that aren't used or don't even exist are the ones that should be considered for attention. And that's what the Tuning Wizard is for - that, and statistics. I will admit that I do not yet "get" statistics.I am often surprised when I design tables with carefully chosen indexes and SQL Server ignores them, whereas if I was still programming a database "row by agonising row" I myself would use the index strategy I designed for the query. Maybe it's the statistics. I look at the Execution Plan and I'm like, "Why aren't you using the nice index I made for you!" Incidentally, I'm single.[/quote]This normally happens for one of two reasons.1. The index doesn't cover the query 100% and the amount of data being queried is large enough that the index won't help the query.2. You use non SARG-able statements in the where clause or the Join criteria. Things like DATEADD, DATEDIFF, LEFT, RIGHT, SUBSTRING, etc in the where clause cause SQL to start table or at best NC index scanning.

RE: Targeted Index Performance Improvements

Neha05 — Fri, 03 Aug 2012 06:00:31 GMT

Very well written.

RE: Targeted Index Performance Improvements

G Bryant McClellan — Fri, 03 Aug 2012 05:47:33 GMT

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.

RE: Targeted Index Performance Improvements

mark.pointon — Fri, 03 Aug 2012 04:55:44 GMT

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

RE: Targeted Index Performance Improvements

Squig — Fri, 03 Aug 2012 03:32:38 GMT

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.

RE: Targeted Index Performance Improvements

SQLRNNR — Tue, 08 Feb 2011 15:47:21 GMT

Thanks for the article.

RE: Targeted Index Performance Improvements

ianstirk — Sat, 05 Feb 2011 00:06:38 GMT

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

RE: Targeted Index Performance Improvements

curious_sqldba — Fri, 04 Feb 2011 13:35:36 GMT

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?

RE: Targeted Index Performance Improvements

ianstirk — Fri, 04 Feb 2011 11:59:02 GMT

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 [url=http://www.manning.com/stirk]www.manning.com/stirk[/url] which contains 100+ scripts like this one.Chapter 1 can be downloaded for free and includes scripts for:A simple monitorFinding your slowest queriesFind your missing indexesIdentifying what SQL is running nowQuickly find a cached planThanksIan

RE: Targeted Index Performance Improvements

curious_sqldba — Fri, 04 Feb 2011 09:48:55 GMT

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 functionsii) few viewsiii) few tables.Could you please say why is the output different or is it intended to use for different purpose? Thanks

RE: Targeted Index Performance Improvements

Christoph D — Fri, 04 Feb 2011 05:11:10 GMT

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 ;-)

RE: Targeted Index Performance Improvements

rja.carnegie — Fri, 18 Sep 2009 04:41:08 GMT

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 [i]that[/i] procedure returning dynamic SQL text which you'd execute locally...

RE: Targeted Index Performance Improvements

JJ B — Thu, 17 Sep 2009 15:30:46 GMT

Thank you for sharing your utility, taking the time to explain how it works, and going over the strengths and weaknesses. As you replied to a previous poster, your results are easy to read. This is a nice tool to add to the toolbox.Thanks!

RE: Targeted Index Performance Improvements

ianstirk — Thu, 17 Sep 2009 12:16:47 GMT

Hi,yes the code uses sys.indexes, which is database specific, so it needs to run in each database separately.ThanksIan

RE: Targeted Index Performance Improvements

Mister Sachmo — Thu, 17 Sep 2009 12:09:18 GMT

so this proc needs to be installed in each database where you want to run it?

RE: Targeted Index Performance Improvements

rja.carnegie — Thu, 17 Sep 2009 09:42:26 GMT

I need to read this article carefully, but my immediate reaction is that the indexes that aren't used or don't even exist are the ones that should be considered for attention. And that's what the Tuning Wizard is for - that, and statistics. I will admit that I do not yet "get" statistics.I am often surprised when I design tables with carefully chosen indexes and SQL Server ignores them, whereas if I was still programming a database "row by agonising row" I myself would use the index strategy I designed for the query. Maybe it's the statistics. I look at the Execution Plan and I'm like, "Why aren't you using the nice index I made for you!" Incidentally, I'm single.

RE: Targeted Index Performance Improvements

ianstirk — Thu, 17 Sep 2009 08:05:17 GMT

Hi Toby,I would hope the output from this utility is much easier to read and interpret, with consolidated results for a batch of SQL. Additionally, the ‘Actual Executing Plan’ option can take quite a lot of resources and time to produce (and interpret).Also, there may be times when you want the results from all the SQL running on the box over a given time period, in this case reading and consolidating the individual plans would be troublesome. That said, you’ve quite rightly identified a degree of overlap.ThanksIan

RE: Targeted Index Performance Improvements

Toby White — Thu, 17 Sep 2009 07:45:43 GMT

I am trying to identify the advantage of using this utility versus an "Actual Execution Plan". It seems to give all the same information about which indexes where used, scans/seeks and row count. The execution plan has the additional advantage of only showing stats for the query in question.

RE: Targeted Index Performance Improvements

MattTheDBA — Thu, 17 Sep 2009 03:17:05 GMT

Cheers :-)

RE: Targeted Index Performance Improvements

ianstirk — Thu, 17 Sep 2009 03:11:17 GMT

Hi Mathew,The utility records all index activity on the database, not just that which belongs to the SQL you’re running. So it might be better to schedule the utility to run at a time when you know yours is the only SQL running, or run it on a stand-alone database where you can ensure the only SQL running is yours.Hope this helpsIan

RE: Targeted Index Performance Improvements

MattTheDBA — Thu, 17 Sep 2009 02:53:23 GMT

If this is run on a production server, would other queries interfer with the results. I assume because you are doing a snapshot, you do not want anything else to be running at the time?

Targeted Index Performance Improvements

ianstirk — Thu, 17 Sep 2009 00:20:36 GMT

Comments posted to this topic are about the item [B]Targeted Index Performance Improvements[/B]