March 24, 2010 at 9:16 am
I'm looking for thoughts/feedback on the following idea:
NOTE: First off let me say that I can’t provide any actual DDL because of an NDA with our software vendor but in this case the question is not tied to my DB and is applicable to any so DDL shouldn’t be necessary (I think) to exam this idea. In addition, our DB is part of our software application and so we had no part in the DB’s design so if anything about the design of the DB seems odd or way off know that it was designed over time almost exclusively by procedural programmers who are familiar with the SQL language.
In our business we have cyclical periods; weekly, monthly & yearly. While the day to day operations vary there are definite patterns of work or how our DB is accessed/used at certain times of the week and Year but more importantly at certain times of a month. What I am thinking about and would like feedback on is taking the results of the DMV (data Management View) DM_DB_INDEX_USAGE_STATS and storing them in a table each day for at least 1 month and then going back and looking at some aggregation of the values to get a idea of how each Index is being used over the monthly cycle.
We have some highly volatile tables that see high levels of different types of activity (SELECT, INSERT, UPDATE) at different times of the month. A couple of these tables have several indexes which I believe are never even used at any point during that monthly period. But to be certain I am seeing how the indexes are being used over the full monthly cycle I was planning to store (daily) the results of this DMV to a table for later review. If my suspicions are correct and there are a number of indexes that are never used (i.e. in seeks for SELECTs) then I would think they’d be best served if they were disabled so that SQL Server has X less indexes to manage during INSERTS/UPDATES/DELETES.
Has anyone on this site ever done something like this and or does anyone have any thoughts on dong this; perhaps some suggestions or tips I may not have thought of?
Thanks
Kindest Regards,
Just say No to Facebook!March 25, 2010 at 2:00 am
Very valid and good approach you are going to adapt.
but before doing this how come you know that few indexes are not being used ?
Did you see any bottleneck/bad performance in related sql code ?
i would suggest you that dont drop index unless they are creating any bad impact on application
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 25, 2010 at 10:05 am
Bhuvnesh (3/25/2010)
Very valid and good approach you are going to adapt.but before doing this how come you know that few indexes are not being used ?
Did you see any bottleneck/bad performance in related sql code ?
i would suggest you that don’t drop index unless they are creating any bad impact on application
I am making somewhat of an assumption on some of the unused indexes because spot checks over the last few days (on a couple of key tables which are gigabyte sized and millions of rows) shows the same handful of indexes with all zero values in ever metric this DMV returns. In addition the name of the index and of course the column(s) the index is made of also hints at its lack of use since those column(s) al have 0 values. They could actually end up being used some over a long period (we reboot every day) but I’d be surprised if that’s the case.
I'd love nothing more than to drop these bum indexes should my theory of their lack of use prove correct but if i do that and our vendor runs into a problem they cannot resolve they will point to these missing indexes as the source of the problem as sure as the tax man will take your property for non-payment of your tax dues.
It doesn't matter if that there is no real correlation between the problem they are troubleshooting and those indexes; they will still point the finger at the dropped indexes. If I just disable them I can at least easily re-enable them verses having to add them back and build them.
On a similar note, this same vendor has a half dozen table triggers that contain IF ELSE logic and for us this logic in the trigger has always and always will return false. They will always return false because the triggers are specific to a Industry that we don’t provide parts to and almost certainly never will. Yet the vendor won't give us the OK to drop the things nor even to just disable them so they aren’t firing for no reason. I'm stuck with this situation because the higher ups don't want to risk losing our support with the vendor.
I can somewhat bend the rules on not changing the schema by turning off or disabling things like unused Index but I don't dare drop/delete them altogether.
Thanks for the reply. I had a feeling this was a reasonable approach but it’s still nice to get a second opinion.
Kindest Regards,
Just say No to Facebook!March 26, 2010 at 12:13 am
i am sure you must be aware of below text but i am just giving you bit support
Query to Identify Indexes that are not being used use dbname
go
SELECT o.name Object_Name,
i.name Index_name,
i.Type_Desc
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.object_id = i.object_id
LEFT OUTER JOIN
sys.dm_db_index_usage_stats AS s
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE o.type = 'u'
-- Clustered and Non-Clustered indexes
AND i.type IN (1, 2)
-- Indexes without stats
AND (s.index_id IS NULL) OR
-- Indexes that have been updated by not used
(s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 );
The output of this view assists in determining how valuable indexes have been in resolving queries. Using this information decisions can be made regarding the need to modify, and/or drop some indexes. Remember that the information in the “sys.dm_db_index_usage_stats” index only contains statistics that have been gathered since SQL Server was started, the database was opened, or when the index was created. If the system hasn’t been up very long then potentially the stats that this DMV produces might not be very useful in representing a true picture of the index utilization. Ensure statistics are representative of the sample of queries executed to determine what maintenance activities are required based on the output of this DMV.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 26, 2010 at 1:26 pm
Thanks for the reply & code but because we reboot each day I have the same problem with this query and that is that just because the index isn;t used today that doesn;t mean it's not getting used at some point over the 30 day cycle.
Thanks
Kindest Regards,
Just say No to Facebook!March 26, 2010 at 2:58 pm
There are a few ways to do this. You can create a table that has a list of indexes used each day (remove the check for null stats change the checks on stats usage to > 0 with an OR) and add any news ones each day before the reboot.
You can create a table and populate it with Bhuvnesh's query then at the end of the day remove any record that doesn't turn up in that query.
Create a table and add a date to Bhuvnesh's query and have it run in all indexes with the number of seeks, scans, and lookups for the day.
I'm sure there are more too (I can think of at least one other feasible way) but you'll need to decide what the best way to handle it is.
March 30, 2010 at 11:21 am
cfradenburg (3/26/2010)
There are a few ways to do this. You can create a table that has a list of indexes used each day (remove the check for null stats change the checks on stats usage to > 0 with an OR) and add any news ones each day before the reboot.You can create a table and populate it with Bhuvnesh's query then at the end of the day remove any record that doesn't turn up in that query.
Create a table and add a date to Bhuvnesh's query and have it run in all indexes with the number of seeks, scans, and lookups for the day.
I'm sure there are more too (I can think of at least one other feasible way) but you'll need to decide what the best way to handle it is.
Thanks for replying. Even though I only mentioned the locating of indexes not being used my goals are broader and so I'm not only looing for unused indexes but alo to what extent used indexes are getting used. And so to that end the sample code provided by Bhuvnesh's query, while great for finding unsed indexes, is not quote broad enough. I however have bookmakred/saved it for later reference.
The suggestion you;ve provided with teh table is what I am doing currently and was looking for feedback on so you've confirmed my approach. Now if only I could get the wife to confirm my stance on...
Thanks again
Kindest Regards,
Just say No to Facebook!Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply