|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > How can I determine which indexes are in use?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How can I determine which indexes are in use?
|
|
|
| I would like to know which indexes are used before I drop them. I would like
to run some kind of query which would show me the stored procedure(s) using
specified index.
Is there a way to do it? I know it is possible to in DB2....
Thanks,
-Stan
| |
| Tony Rogerson 2002-11-08, 3:23 pm |
| It don't work like that - the use of an index is determined when the plan is
compiled and not when the stored procedure is created.
A trigger may be used one day but if the data radically changes it may not
be used even with the same query.
--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]
http://www.sql-server.co.uk/tr [To Hire me]
"Stan" <nospam@yahoo.com> wrote in message
news:e0Q#Ov1hCHA.4128@tkmsftngp08...
> I would like to know which indexes are used before I drop them. I would
like
> to run some kind of query which would show me the stored procedure(s)
using
> specified index.
>
> Is there a way to do it? I know it is possible to in DB2....
>
> Thanks,
>
> -Stan
>
>
| |
| Bill Hollinshead [MS] 2002-11-08, 6:23 pm |
| Hi Stan,
The Index Tuning Wizard suggests indexes to drop and/or create. While it
takes some work to give ITW the data it needs, there is a benefit to using
it on a regular basis when the nature of the client activity and/or the
index statistics change (over time).
Thanks,
Bill Hollinshead
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
| |
| Hal Berenson 2002-11-09, 6:23 pm |
| The architectural differences between SQL Server and DB2 make this a more
difficult task in SQL Server. Generally, DB2 was designed with the
assumption that a DBA takes a SP, examines the plan that is generated when
it is compiled, and approves that plan (which is then saved). Because the
plan is saved you also save information on the dependencies it has on system
objects such as indices (so that you can either prevent them from being
dropped, or mark that the plan is invalid and needs recompilation). Of
course, once you have this dependency information you can query it to see
the dependencies. SQL Server assumes that you don't have this kind of
DBA-intensive environment and doesn't save the plans for SPs. Instead it
always recompiles the SP and generates a new plan on first use. Since plans
are not database objects, there is no dependency information saved for them.
To find out which indexes are actually being used in SQL Server you have to
use the SQL Profiler tool. You can use Profiler (which uses the Server's
Trace facility) to capture the actual index usage over a period of time, and
then make decisions about which indexes to retain. Alternatively, you can
use SQL Profiler to capture a trace for use by the Index Tuning Wizard. The
ITW will then suggest a set of optimal indices, including existing indices
that should be dropped.
--
Hal Berenson
True Mountain Consulting
"Stan" <nospam@yahoo.com> wrote in message
news:e0Q#Ov1hCHA.4128@tkmsftngp08...
> I would like to know which indexes are used before I drop them. I would
like
> to run some kind of query which would show me the stored procedure(s)
using
> specified index.
>
> Is there a way to do it? I know it is possible to in DB2....
>
> Thanks,
>
> -Stan
>
>
|
|
|
|
|