[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Validate performance before/after index change

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
dbaSQL
Asked:
dbaSQL
  • 9
  • 8
  • 5
  • +1
3 Solutions
 
ste5anSenior DeveloperCommented:
0
 
lcohanDatabase AnalystCommented:
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
 
dbaSQLAuthor Commented:
that may work.  let me take a look
0
Independent Software Vendors: 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!

 
lcohanDatabase AnalystCommented:
0
 
dbaSQLAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
Duplicate and unused indices are normally not used by a plan...)
0
 
lcohanDatabase AnalystCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
dbaSQLAuthor Commented:
>>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
 
dbaSQLAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
dbaSQLAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
dbaSQLAuthor Commented:
I've got that stats update per table in  my plan, but I hadn't thought of the recompile.  Thank you very much!
0
 
Scott PletcherSenior DBACommented:
>>  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
 
dbaSQLAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
Scott PletcherSenior DBACommented:
>> 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
 
Scott PletcherSenior DBACommented:
>> 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
 
dbaSQLAuthor Commented:
>>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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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
 
dbaSQLAuthor Commented:
Thank you very much, Mr. Pletcher.  And lcohan.  Your additional input is very valuable, and very much appreciated.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 8
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now