Solved

Validate performance before/after index change

Posted on 2014-03-26
24
346 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 5
  • +1
24 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 200 total points
ID: 39956917
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 200 total points
ID: 39956979
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
ID: 39956984
that may work.  let me take a look
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 40

Expert Comment

by:lcohan
ID: 39956985
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 39956989
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 33

Expert Comment

by:ste5an
ID: 39957006
Duplicate and unused indices are normally not used by a plan...)
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39957008
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:Scott Pletcher
ID: 39957604
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
ID: 39957692
>>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
ID: 39957700
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:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 39957713
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
ID: 39957786
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
 
LVL 40

Expert Comment

by:lcohan
ID: 39961890
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
ID: 39961948
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:Scott Pletcher
ID: 39961955
>>  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
ID: 39961988
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 40

Expert Comment

by:lcohan
ID: 39961989
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:Scott Pletcher
ID: 39962064
>> 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:Scott Pletcher
ID: 39962095
>> 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:Scott Pletcher
ID: 39962102
>> 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
ID: 39962125
>>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:Scott Pletcher
ID: 39962168
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:Scott Pletcher
ID: 39962205
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
ID: 39962216
Thank you very much, Mr. Pletcher.  And lcohan.  Your additional input is very valuable, and very much appreciated.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Sum Over Multiple Tables 20 32
Building JSON Results Table FROM DB 9 32
access to sql migration 5 23
UPDATE JOIN multiple tables 5 19
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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

756 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