Indexes - general question

We have a database that has been in operation for several years now.
Periodically, as the data has grown, we've performed index analyses and
added new indexes to help boost performance.
Is there a way of running some sort of trace on SQL Server to show which
indexes were useful but are now no longer being used because they've been
superseded by other (possibly multi-columned) indexes? I don't see any
point in maintaining indexes that are of no use... Unfortunately, I can't
drop all indexes and start from scratch!
Thanks
GriffA good start would be to run your most frequently used SQL/Sps through the
Query Analyser Indeex Tuning Wizard and see what the results say. If you
have pretty good indexes in place, it shouldnt recommend too many changes.
But also, be warned, QA may not always be 100% correct with its decisions,
but will definately give you some good grounding!
Immy
"Griff" <howling@.the.moon> wrote in message
news:eNZfz7emGHA.2280@.TK2MSFTNGP03.phx.gbl...
> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I
> can't drop all indexes and start from scratch!
> Thanks
> Griff
>|||See if this helps.
How to Identify Non-Active SQL Server Indexes
http://www.sql-server-performance.com/lm_index_elimination_english.asp
AMB
"Griff" wrote:
> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can't
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||If your environment permits, trace for execution plans for a complete cycle
(e.g. a day, a week, etc). This captures the real query plans used of most of
your SQL statements, not the stimated query plans. You can then summarize
what query operators are used in these plans and what indexes are used by the
query operators. Compare that with all the indexes you have in that database
would give you a list of indexes not being used.
To summarize the index usage this way, I typically dump the plans out to a
text file and then parse that text files for the query operators and index
names.
Linchi
"Griff" wrote:
> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can't
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||Of course, if this is SQL2005, you can inspect DMV
sys.dm_db_index_usage_stats for (whatelse) the index usage info without going
through all that trouble with tracing and parsing.
Linchi
"Griff" wrote:
> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can't
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||... and also the ...missing_indexes... DMVs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:FDC00205-B38B-455B-B8D5-8541FC5DD192@.microsoft.com...
> Of course, if this is SQL2005, you can inspect DMV
> sys.dm_db_index_usage_stats for (whatelse) the index usage info without going
> through all that trouble with tracing and parsing.
> Linchi
> "Griff" wrote:
>> We have a database that has been in operation for several years now.
>> Periodically, as the data has grown, we've performed index analyses and
>> added new indexes to help boost performance.
>> Is there a way of running some sort of trace on SQL Server to show which
>> indexes were useful but are now no longer being used because they've been
>> superseded by other (possibly multi-columned) indexes? I don't see any
>> point in maintaining indexes that are of no use... Unfortunately, I can't
>> drop all indexes and start from scratch!
>> Thanks
>> Griff
>>|||The basics on how to use the missing index DMVs are listed in this blog
entry:
http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Z22fShmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> ... and also the ...missing_indexes... DMVs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:FDC00205-B38B-455B-B8D5-8541FC5DD192@.microsoft.com...
>> Of course, if this is SQL2005, you can inspect DMV
>> sys.dm_db_index_usage_stats for (whatelse) the index usage info without
>> going
>> through all that trouble with tracing and parsing.
>> Linchi
>> "Griff" wrote:
>> We have a database that has been in operation for several years now.
>> Periodically, as the data has grown, we've performed index analyses and
>> added new indexes to help boost performance.
>> Is there a way of running some sort of trace on SQL Server to show which
>> indexes were useful but are now no longer being used because they've
>> been
>> superseded by other (possibly multi-columned) indexes? I don't see any
>> point in maintaining indexes that are of no use... Unfortunately, I
>> can't
>> drop all indexes and start from scratch!
>> Thanks
>> Griff
>>
>|||Linchi Shea (LinchiShea@.discussions.microsoft.com) writes:
> Of course, if this is SQL2005, you can inspect DMV
> sys.dm_db_index_usage_stats for (whatelse) the index usage info without
> going through all that trouble with tracing and parsing. >
But beware that the information only applies to when SQL Server was
last restarted, or when the database was restored. So the indexes that
is needed for that report your boss runs at the end of the month may
appear as unused. :-)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx