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

How many rows before optimizer will use index

Hi
2 questions for you Gentlemen ;-)
1:
I think I have heared that SQL Server optimizer (always ?)
will use a table scan, if the row count of a table is
under a certain value even if there exist an - othervise
appropriate - index, simply because this is considered
faster.
How many rows do a table need to have before indexes are
considered usefull - does a roughly value exist?
(I think I read the number 8000 rows once)
Is there any differences between indextypes (CL, NCL,
single/multicolumn...)in the minimum "required" row count
2:
In a table A with many rows a column colA1 exists (with
a "code type" value), referencing table B with few rows
containing the list of codes.
Though a FK (and sometimes used in queries, joins etc.)
woundn't it be superfluous to index the FK column colA1,
because of the relative bad/low selectivity of the
different domain values (both if an fairly even spread of
the different few codes are assumed or if one of the codes
are in majority).
kind regards
Jakob Persson
Denmarkactually,
SQL Server will almost always try to use an index unless
there are atleast 6-12 rows, and the plan involves a NC
index and bookmark lookups
see:
http://www.sql-server-
performance.com/jc_sql_server_quantative_analysis1.asp
for more info
2. if you don't expect a plan to use the index, it may not
be necessary. i am assuming you do not delete from table B.
>--Original Message--
>Hi
>2 questions for you Gentlemen ;-)
>1:
>I think I have heared that SQL Server optimizer
(always ?)
>will use a table scan, if the row count of a table is
>under a certain value even if there exist an - othervise
>appropriate - index, simply because this is considered
>faster.
>How many rows do a table need to have before indexes are
>considered usefull - does a roughly value exist?
>(I think I read the number 8000 rows once)
>Is there any differences between indextypes (CL, NCL,
>single/multicolumn...)in the minimum "required" row count
>2:
>In a table A with many rows a column colA1 exists (with
>a "code type" value), referencing table B with few rows
>containing the list of codes.
>Though a FK (and sometimes used in queries, joins etc.)
>woundn't it be superfluous to index the FK column colA1,
>because of the relative bad/low selectivity of the
>different domain values (both if an fairly even spread of
>the different few codes are assumed or if one of the
codes
>are in majority).
>kind regards
>Jakob Persson
>Denmark
>.
>|||Jakob Persson wrote:
<snip>
> 2:
> In a table A with many rows a column colA1 exists (with
> a "code type" value), referencing table B with few rows
> containing the list of codes.
> Though a FK (and sometimes used in queries, joins etc.)
> woundn't it be superfluous to index the FK column colA1,
> because of the relative bad/low selectivity of the
> different domain values (both if an fairly even spread of
> the different few codes are assumed or if one of the codes
> are in majority).
This is only true if the values of colA1 are evenly distributed. If the
data distribution is skewed (for example, many 'A' but few 'B'), then
the index can still be beneficial when querying on 'B'.
And (as Joe mentioned), the index is also useful when deleting rows from
the referenced table.
Gert-Jan