Solved

Validate performance before/after index change

Posted on 2014-03-26
24
342 Views
Last Modified: 2014-03-28
I was performing a database assessment recently, and I identified MANY duplicate and unused indexes.  All of which, of course, we know cannot be too kind to application performance.  

So, I structured the cleanup, to remove the dupes and unused indexes.  But, before it can be run in production, I need to prove why this is even necessary, or why it should be done.  I've been asked to run the same thing in staging to prove its worth, before doing so in production.

Basically, what is the most effective way of showing a before and after, or something that would help me substantiate that yes, duplicate indexes are an impediment, and should be removed.  

Unfortunately, I am having problems coming up with the best way to validate this effort.  

Any ideas?
0
Comment
Question by:dbaSQL
  • 9
  • 8
  • 5
  • +1
24 Comments
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 200 total points
Comment Utility
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 200 total points
Comment Utility
I suggest you use SQL SSMS Query Plans before and after - there's an option "Display Estimated Query plan" or better "Include actual execution Plan" on the Query Menu option.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
that may work.  let me take a look
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
yeah, lcohan.  I was thinking the same thing.  the query plan before and after.  or with the dupe and unused indices, and then without.

let me see what I can do.  i'll be back.
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Duplicate and unused indices are normally not used by a plan...)
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Other quick ways are to run the two statements below prior to running the same query without then with indexes and compare the outputs - at least number of physical reads must be (gone) much lower, logical reads, and CPU

set statistics time on;
set statistics io on;



Just trying to help...
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Does someone really want proof that totally duplicate indexes aren't needed?  Yikes; cautiousness is one thing, but that's just silly.

To demonstrate that duplicate indexes are an impediment, get statistics for all INSERT, all DELETE and any UPDATE statements which affect column(s) in those indexes.  The I/O will be higher the more indexes there are; thus, removing index(es) will reduce the I/O required to complete the SQL statement.

As to unused indexes, you need to make sure that SQL has been running for a significant period of time.  "Significant" can vary based on db requirements; I usually like to have at least 31 full days' worth of activity.

Can you provide the query you're using to determine "unused" status?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
>>Does someone really want proof that totally duplicate indexes aren't needed?
Yes.  And I could not agree more.

I gathered the stats from sys.dm_db_index_usage_stats and sys.dm_exec_query_stats, for several of the relevant tables, and associated procedures -- in production and staging, where I had already implemented the index cleanup in staging.  I also captured the query plan for the same procs that I referenced the DMV for.  The numbers are very, very obvious.   I can only hope it's enough to substantiate the effort.

I've used Brent Ozar's piece for my index analysis -- dupes, missing, unused.  
http://www.brentozar.com/blitzindex/

I also use this one:
SELECT
      object_name(idx.object_id),
      idx.name,
      user_seeks,
      user_scans,
      user_lookups,
      user_updates,
      last_user_seek,
      last_user_scan
FROM
      sys.indexes idx LEFT OUTER JOIN sys.dm_db_index_usage_stats stat
        ON idx.index_id  = stat.index_id
        AND idx.object_id = stat.object_id
        AND stat.database_id = db_id()
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
The only piece I think is outstanding, is to answer the question -- which index is SQL using?  I've gone in and looked at the stats, everything is very clear.  But I still wonder -- two identical indexes.  Is there an actual preference?  Will one ever NOT be used, if the other is removed?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
Comment Utility
Looks very thorough overall.

SQL could possibly randomly use either one of duplicate indexes.


>> But I still wonder -- two identical indexes.  Is there an actual preference?  Will one ever NOT be used, if the other is removed? <<

You can 100% safely remove one.  The remaining one will be used if it's better for the query.
0
 
LVL 17

Author Closing Comment

by:dbaSQL
Comment Utility
lcohan and ste5an, thank you both for looking this way.  Mr. Pletcher, thank you for checking my approach, and for your feedback regarding the safety of my duplicate removal.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 39

Expert Comment

by:lcohan
Comment Utility
may be very well known however...here is what I always do in particular after an index drop:

UPDATE STATISTICS TableName;
EXEC sp_recompile TableName; -- this one should clear any old plans using dropped index from cache as the code using the TableName is re-compiled. Caution - may be sever on very busy 24x7OLTP systems against that TableName.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
I've got that stats update per table in  my plan, but I hadn't thought of the recompile.  Thank you very much!
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>>  what I always do in particular after an index drop ... UPDATE STATISTICS TableName; <<

That's a terrible idea because it could hurt performance dramatically.  Never do a wholesale rebuild of stats on a table unless you've just converted from one version of SQL to another or you've applied other SQL maintenance that requires it.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
This brings a good point to the table -- cleanup.  I am dropping numerous indexes from production tables.  I know with an index rebuild, you don't have to update stats, because the rebuild does it for you.  However, before your statement, Scott, I thought that it may be advantageous to update stats, or even rebuild/reorg the remaining indexes, on the targeted tables, after I was done removing.

If I drop the indexes, the stats and metadata are gone.  What is the most appropriate way to cleanup after a manipulation like this?
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Sorry I don't want to get into any argument here but ....what are you talking about?

"Use the Update Statistics Task dialog to update Microsoft SQL Server information about the data in the tables and indexes. This task resamples the distribution statistics of each index created on user tables in the database. The distribution statistics are used by SQL Server to optimize navigation through tables during the processing of Transact-SQL statements."
http://technet.microsoft.com/en-us/library/ms178678(v=sql.105).aspx

...so it does the opposite right? It improves performance by updating internal SQL info so better plans can be generated.  

Proven to me and we have tables in our live SQL database where without this UPDATE STATISTICS performed every couple of hours (no sp_recompile here) the performance is down the drain but back to almost perfect after the UPDATE STATISTICS TableNAme was done.


I run UPDATE STATISTICS after EACH index REBUILD,DROP, or addition and without it I found is much worse performance. This all started with SQL 2000 and up to inclusively 2012 Standard or Enterprise editions I work with.

That's all I have to say.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> I run UPDATE STATISTICS after EACH index REBUILD,DROP, or addition and without it I found is much worse performance. <<

UPDATE STATS only samples data, i.e., reads a small percentage of rows.

If you've just done a build or rebuild on the index, the statistics are based on the entire table's data, i.e., the equivalent of UPDATE STATS ... WITH FULLSCAN (at least from SQL 2005 on), which provides much fuller, and thus much better, stats.


>> Sorry I don't want to get into any argument here but ....what are you talking about? <<

I'm talking about being an extremely experienced DBA (25+ years) that understands the details of what db commands actually do (and/or don't do).  I'm sure a quick Google search could verify what I've stated from other qualified DBAs.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> If I drop the indexes, the stats and metadata are gone.  What is the most appropriate way to cleanup after a manipulation like this? <<

Indeed, stats related to those indexes have been deleted since the indexes themselves have been deleted.  Thus, there's nothing to clean up.

You can use STATS_DATE() to see when stats were last updated for a given index.  And sys.stats for other stats info.  But, most unfortunately, SQL Server doesn't tell you what % of rows were used to construct those stats.  This makes it difficult to tell sometimes if you should rebuild stats or not.  

[I'd wait to update stats at least until sys.dm_db_index_operational_stats indicated that some significant level of data changes had occurred, otherwise you're just wasting I/O generating stats that will be (almost) identical to existing stats.  Of course, this point is mute if you don't periodically capture sys.dm_db_index_operational_stats data :-), as you won't be able to tell how many changes have occurred since the last stats update.]
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> Proven to me and we have tables in our live SQL database where without this UPDATE STATISTICS performed every couple of hours (no sp_recompile here) the performance is down the drain but back to almost perfect after the UPDATE STATISTICS TableNAme was done. <<

If you have a downtime period for those table(s), you should consider doing an UPDATE STATS ... WITH FULLSCAN during that time, which will give you much fuller stats and make subsequent table changes less out-of-sync with the overall stats.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
>>Indeed, stats related to those indexes have been deleted since the indexes themselves have been deleted.  Thus, there's nothing to clean up.

Two words;  very cool

I wonder if you could provide your thoughts on sp_recompile as well.

I like your suggestion regarding sys.dm_db_index_operational_stats.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You have to use DBCC SHOW_STATISTICS to get the % of rows, but it's a pain to have to use that too just to get the % :-) (and I've found that sometimes it's quirky).
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
I'd happily use sp_recompile anytime I think it's possibly useful, even "just to be sure".

While it takes some time to generate a query plan, a good query plan could save orders of magnitude over the time the plan itself took to gen.

In short, in my view, recompile away if you think it's needed/worthwhile.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
Thank you very much, Mr. Pletcher.  And lcohan.  Your additional input is very valuable, and very much appreciated.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dynamics crm 2011 8 43
Temporarily disable SQL Replication 7 21
Complex SQL 10 32
Log Backup 2 11
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now