How often an index is hit

Anyone have a script/process that would count how often indexes are actually
used? Are there sys optimizer tabs or logs or such?
Mike
If data falls in the woods and nobody is there to see it ...... ?Are you talking SQL 2005? If so then yes there are some dynamic management
views that give you all sorts of details for index usage. In 2000 there is
no built in way to do this.
Andrew J. Kelly SQL MVP
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:D209E173-1D85-4969-B2FE-C17A2F4B63A9@.microsoft.com...
> Anyone have a script/process that would count how often indexes are
> actually
> used? Are there sys optimizer tabs or logs or such?
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?|||In SQL Server 2005 you have a variety of DMVs to use, for example:
DECLARE @.TableName SYSNAME;
SET @.TableName = N'YourTableName';
DECLARE
@.ObjectID INT,
@.DBID INT;
SELECT
@.ObjectID = OBJECT_ID(@.TableName),
@.DBID = DB_ID();
SELECT
-- s.* just to illustrate:
i.name, s.*
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON s.object_id = i.object_id
AND s.index_id = i.index_id
WHERE s.database_id = @.DBID
AND s.object_id = @.ObjectID
AND i.object_id = @.ObjectID;
This will tell you number of and most recent scan/s/lookup/update, both
user and system. Variety of useful applications for this data, if you are
heavy into data mining for tuning opportunities.
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:D209E173-1D85-4969-B2FE-C17A2F4B63A9@.microsoft.com...
> Anyone have a script/process that would count how often indexes are
> actually
> used? Are there sys optimizer tabs or logs or such?
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?|||Its surprising that we don't have any in SQL Server 2000