April 18, 2012 at 10:07 am
Hi,
If after several days of using an application, where all the operations are used, the database has some indexes that only have writes and don't have any reads should these indexes be removed?
Thanks,
Pedro
April 18, 2012 at 10:21 am
My first question is... Why do you want to remove them? There are several reasons that the index may not be used. Refer to this thread:
http://www.sqlservercentral.com/Forums/Topic1285037-391-1.aspx
The removal of an index should be based on knowledge of the business, applications, and reports. Not usage statistics.
Jared
CE - Microsoft
April 18, 2012 at 10:39 am
Lets just say that there are several developers writing code on our program and every one thinks they know the right index to use... They create indexes and now I have to analyse the database and find out what's being used and not.
I found several indexes with the same columns, others with just one column less than others, ....
Some of these indexes have over 10.000 writes and 0 reads for over 3 months (from Dec to Feb).
All processes that could be executed, reports, ... have been..
Thanks,
Pedro
April 18, 2012 at 10:45 am
Its your job, so do what you think is right. The thing is, you cannot look at statistics to make the decision. Only outside information can help you to make the decision.
Lastly, I would say to not let developers who don't know what they are doing create indexes. Or go through a review with them asking them why they have created each particular index. Do you really know what duplicate indexes are? It is possible you have true duplicate indexes or ones that will never be used. However, the stats will not tell you that.
As mentioned in the thread I linked you to, if you feel that you need to remove an index... Script it out and save it. Put whatever details you need along with it in case you need to put it back. Document why you removed it. I'm going to ask again, though, why do you feel you need to remove indexes. Do you have a space problem? Is your maintenance taking too long? What reasons do you have to justify spending time analyzing this. I'm not saying that there are not good reasons, but if it is not causing any issues... Leave it.
Jared
CE - Microsoft
April 18, 2012 at 10:46 am
You will need to analyze the indexes, get with the developers and determine what indexes are really needed and which ones can be dropped.
At another employer I put together a spreadsheet putting together all the indexes, showing which ones were duplicate and which were overlapping. I even recommended some indexes where it was appropriate.
April 18, 2012 at 11:00 am
Will SQL Profiler help me determine what indexes are really needed and not when running its trace file on the Engine Tuning Advisor?
I remember that a long time ago, DB2 had a problem with indexes columns orders.
If I had an index with ColA and ColB but in the query I had ColB = ... AND ColA = ... the index wouldn't be used.. This has long been resolved but are there any issues, not like this but similar, that I should take in concern when designing indexes?
Say I have the above index and on the WHERE clause only use ColB = ... Will the index be used (scan or seek)?
Should I always have ColA IS NOT NULL AND ColB = .... ?
Thanks,
Pedro
April 18, 2012 at 11:10 am
PiMané (4/18/2012)
Will SQL Profiler help me determine what indexes are really needed and not when running its trace file on the Engine Tuning Advisor?I remember that a long time ago, DB2 had a problem with indexes columns orders.
If I had an index with ColA and ColB but in the query I had ColB = ... AND ColA = ... the index wouldn't be used.. This has long been resolved but are there any issues, not like this but similar, that I should take in concern when designing indexes?
Say I have the above index and on the WHERE clause only use ColB = ... Will the index be used (scan or seek)?
Should I always have ColA IS NOT NULL AND ColB = .... ?
Thanks,
Pedro
No, profiler may not help you determine what indexes are really needed. You really need to analyze the indexes on a table by table basis and look at the different access paths to those tables (views, stored procedures, etc) and determine in cooperation with your developers which indexes are really needed.
April 18, 2012 at 11:20 am
PiMané (4/18/2012)
Lets just say that there are several developers writing code on our program and every one thinks they know the right index to use... They create indexes and now I have to analyse the database and find out what's being used and not.I found several indexes with the same columns, others with just one column less than others, ....
Some of these indexes have over 10.000 writes and 0 reads for over 3 months (from Dec to Feb).
All processes that could be executed, reports, ... have been..
Thanks,
Pedro
Is that 0 reads, 0 lookups, 0 seeks between both user and system?
I am usually hesitant to remove an index.
A very big reason is that I have seen query hints use a specific index. I have also seen an index be used only once every 3 or 4 months.
Run the query from this link and see what it says for overall reads.
http://jasonbrimhall.info/2012/03/20/seldom-used-indexes/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 18, 2012 at 11:21 am
PiMané (4/18/2012)
Will SQL Profiler help me determine what indexes are really needed and not when running its trace file on the Engine Tuning Advisor?I remember that a long time ago, DB2 had a problem with indexes columns orders.
If I had an index with ColA and ColB but in the query I had ColB = ... AND ColA = ... the index wouldn't be used.. This has long been resolved but are there any issues, not like this but similar, that I should take in concern when designing indexes?
Say I have the above index and on the WHERE clause only use ColB = ... Will the index be used (scan or seek)?
Should I always have ColA IS NOT NULL AND ColB = .... ?
Thanks,
Pedro
The only way to determine which indexes to remove safely is to work with your developers and understand why the indexes are there to begin with. That is it.
Nobody here is going to tell you that there is a tool to guarantee that the index is not there for a good reason. Assuming you read the thread that I linked you to, there was a response the Jeff Moden added about an index never being "used," but necessary nonetheless.
Jared
CE - Microsoft
April 18, 2012 at 11:49 am
I used this query to determine indexes without "hints" (user_scans, user_seeks, user_lookups).
create view vw_index_usage as
select object_name(a.object_id) "tablename",
c.name "indexname",
c.type_desc "indextype",
case c.is_unique
when 1 then
case is_primary_key
when 1 then
'Primary Key'
else
'Unique'
end
else
case c.is_unique_constraint
when 1 then
'Unique Constraint'
else
'Performance'
end
end "IndexUsage",
(select used/128 from sysindexes b where b.name=c.name and c.index_id=b.indid and b.id=c.object_id) "MB",
(select count(*) from sys.index_columns d where a.object_id=d.object_id and a.index_id=d.index_id and d.is_included_column = 0) "cols",
(select count(*) from sys.index_columns d where a.object_id=d.object_id and a.index_id=d.index_id and d.is_included_column = 1) "included",
(a.user_seeks + a.user_scans + a.user_lookups) "hits",
(a.user_updates) "updates",
a.last_user_update "stats_date",
cast(a.user_seeks + a.user_scans + a.user_lookups as real) / cast(case a.user_updates when 0 then 1 else a.user_updates end as real) * 100 "ratio",
'alter index [' + c.name + '] on [' + object_name(a.object_id) + '] disable;' "SQLCmd"
from sys.dm_db_index_usage_stats a
join sysobjects as o on (a.object_id = o.id)
join sys.indexes as c on (a.object_id = c.object_id and a.index_id = c.index_id)
where o.type='U' -- exclude system tables
and c.type <> 0 -- exclude HEAPs
and c.is_disabled = 0-- only active indexes
and a.database_id = DB_ID()-- for current database only
Now only I create indexes on the database, developers are forbidden to do that :).
I keep analyzing the data and determine what to do with indexes. Will the default reports SQL Server 2008 has (Index Usage Statistics, Object Execution Statistics, ...) help me on this task or is there any other tool I can use?
I already have all the queries with filters the application uses...
Thanks,
Pedro
April 18, 2012 at 11:50 am
PiMané (4/18/2012)
I used this query to determine indexes without "hints" (user_scans, user_seeks, user_lookups).
create view vw_index_usage as
select object_name(a.object_id) "tablename",
c.name "indexname",
c.type_desc "indextype",
case c.is_unique
when 1 then
case is_primary_key
when 1 then
'Primary Key'
else
'Unique'
end
else
case c.is_unique_constraint
when 1 then
'Unique Constraint'
else
'Performance'
end
end "IndexUsage",
(select used/128 from sysindexes b where b.name=c.name and c.index_id=b.indid and b.id=c.object_id) "MB",
(select count(*) from sys.index_columns d where a.object_id=d.object_id and a.index_id=d.index_id and d.is_included_column = 0) "cols",
(select count(*) from sys.index_columns d where a.object_id=d.object_id and a.index_id=d.index_id and d.is_included_column = 1) "included",
(a.user_seeks + a.user_scans + a.user_lookups) "hits",
(a.user_updates) "updates",
a.last_user_update "stats_date",
cast(a.user_seeks + a.user_scans + a.user_lookups as real) / cast(case a.user_updates when 0 then 1 else a.user_updates end as real) * 100 "ratio",
'alter index [' + c.name + '] on [' + object_name(a.object_id) + '] disable;' "SQLCmd"
from sys.dm_db_index_usage_stats a
join sysobjects as o on (a.object_id = o.id)
join sys.indexes as c on (a.object_id = c.object_id and a.index_id = c.index_id)
where o.type='U' -- exclude system tables
and c.type <> 0 -- exclude HEAPs
and c.is_disabled = 0-- only active indexes
and a.database_id = DB_ID()-- for current database only
Now only I create indexes on the database, developers are forbidden to do that :).
I keep analyzing the data and determine what to do with indexes. Will the default reports SQL Server 2008 has (Index Usage Statistics, Object Execution Statistics, ...) help me on this task or is there any other tool I can use?
I already have all the queries with filters the application uses...
Thanks,
Pedro
Nobody here is going to tell you that there is a tool to guarantee that the index is not there for a good reason.
Jared
CE - Microsoft
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply