Indexes - general question

A 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.c...ion_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 o
f
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 th
e
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 goin
g
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...[vbcol=seagreen]
> Of course, if this is SQL2005, you can inspect DMV
> sys.dm_db_index_usage_stats for (whatelse) the index usage info without go
ing
> 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|||A 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.c...ion_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 o
f
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 th
e
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 goin
g
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
>
>