Finally - a quick way to figure out which indices to get rid off, and which to defrag/update

Reposted from the Sustained Engineering blog (http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2008/02/26/index-usage-query.aspx)

The column “No_Of_Updates” basically shows you the cost of this index, since every update requires a lock as well as a write to the database. The next column, “User_Reads”, shows you how often this index has been used, either from the UI, or by C/AL code. Compare these two, and you have way to compare the cost against the benefits of each index, as shown in the column “Cost_Benefit”, which is simply “No_Of_Updates” / “User_Reads”. The column “Last_Used_For_Reads” shows you when an index was actually used for reading.

The query sorts the indexes by “No_Of_Updates”, with the most updated (most costly) index first. At the last line of the query you can change the sorting to “order by Cost_Benefit desc”, and you are likely to see a different picture.

The data shown by the query is reset every time SQL Server restarts. So if you have recently restarted SQL Server, then the query may not show you the most precise picture of how the indexes are being used over time.

IF OBJECT_ID (‘z_IUQ_Temp_Index_Keys’, ‘U’) IS NOT NULL

DROP TABLE z_IUQ_Temp_Index_Keys;

IF OBJECT_ID (‘zIUQ_Temp_Index_Usage’, ‘U’) IS NOT NULL

DROP TABLE zIUQ_Temp_Index_Usage

– Generate list of indexes with key list

create table z_IUQ_Temp_Index_Keys(

[F_Obj_ID] [int] NOT NULL,

[F_Obj_Name] [nvarchar] (128) NULL,

[F_Ind_ID] [int] NOT NULL,

[Index_Column_ID] [int] NOT NULL,

[Index_Key] [nvarchar] (128) NULL,

[Index_Key_List] [nvarchar] (MAX) NULL,

CONSTRAINT [z_IUQ_TempPK] PRIMARY KEY(

[F_Obj_ID],

[F_Ind_ID],

[Index_Column_ID]

)

)

Insert into z_IUQ_Temp_Index_Keys

select

object_id,

object_name(object_id),

index_id,

Index_Column_ID,

index_col(object_name(object_id),index_id,Index_Column_ID),

‘’

from sys.index_columns

go

– populate key string

declare IndexCursor cursor FOR

select F_Obj_ID, F_Ind_ID from z_IUQ_Temp_Index_Keys

FOR UPDATE of Index_Key_List

declare @ObjID int

declare @IndID int

DECLARE @KeyString VARCHAR(MAX)

set @KeyString = NULL

open IndexCursor

set nocount on

fetch next from IndexCursor into @ObjID, @IndID

while @@fetch_status = 0 begin

SET @KeyString = ‘’

SELECT @KeyString = COALESCE(@KeyString, ‘’) + Index_Key + ', ’

FROM z_IUQ_Temp_Index_Keys

where F_Obj_ID = @ObjID and F_Ind_ID = @IndID

ORDER BY F_Ind_ID, Index_Column_ID

SET @KeyString = LEFT(@KeyString,LEN(@KeyString) - 2)

update z_IUQ_Temp_Index_Keys

set Index_Key_List = @KeyString

where current of IndexCursor

fetch next from IndexCursor into @ObjID, @IndID

end;

close IndexCursor;

deallocate IndexCursor;

– Generate list of Index usage

create table zIUQ_Temp_Index_Usage(

[F_Table_Name] nvarchar NOT NULL,

[F_Ind_ID] [int] NOT NULL,

[F_Index_Name] nvarchar NULL,

[No_Of_Updates] [int] NULL,

[User_Reads] [int] NULL,

[Last_Used_For_Reads] [datetime] NULL,

[Index_Type] nvarchar NOT NULL,

[last_user_seek] [datetime] NULL,

[last_user_scan] [datetime] NULL,

[last_user_lookup] [datetime] NULL,

[Index_Keys] [nvarchar] (255) NULL

)

insert into zIUQ_Temp_Index_Usage

select

object_name(US.object_id) Table_Name,

US.index_id Index_ID,

SI.name Index_Name,

US.user_updates No_Of_Updates,

US.user_seeks + US.user_scans + US.user_lookups User_Reads,

case

when (ISNULL(US.last_user_seek,‘00:00:00.000’) >= ISNULL(US.last_user_scan,‘00:00:00.000’)) and (ISNULL(US.last_user_seek,‘00:00:00.000’) >= ISNULL(US.last_user_lookup,‘00:00:00.000’)) then US.last_user_seek

when (ISNULL(US.last_user_scan,‘00:00:00.000’) >= ISNULL(US.last_user_seek,‘00:00:00.000’)) and (ISNULL(US.last_user_scan,‘00:00:00.000’) >= ISNULL(US.last_user_lookup,‘00:00:00.000’)) then US.last_user_scan

else US.last_user_lookup

end as Last_Used_For_Reads,

SI.type_desc Index_Type,

US.last_user_seek,

US.last_user_scan,

US.last_user_lookup,

‘’

from sys.dm_db_index_usage_stats US, sys.indexes SI where SI.object_id = US.object_id and SI.index_id = US.index_id

order by No_Of_Updates desc

go

– Select and join the two tables.

select

TIU.F_Table_Name Table_Name,

–TIU.F_Ind_ID Index_ID,

–TIU.F_Index_Name Index_Name,

TIK.Index_Key_List,

TIU.No_Of_Updates,

TIU.User_Reads,

case

when TIU.User_Reads = 0 then TIU.No_Of_Updates

else TIU.No_Of_Updates / TIU.User_Reads

end as Cost_Benefit,

TIU.Last_Used_For_Reads,

TIU.Index_Type

from zIUQ_Temp_Index_Usage TIU, z_IUQ_Temp_Index_Keys TIK where TIK.F_Obj_Name = TIU.F_Table_Name and TIK.F_Ind_ID = TIU.F_Ind_ID and TIK.Index_Column_ID = 1

and TIU.F_Table_Name not in (‘zIUQ_Temp_Index_Usage’,‘z_IUQ_Temp_Index_Keys’)

order by No_Of_Updates desc

–order by Cost_Benefit desc

Try and run it with Giselle at starboard. I’m curious what we will get.

Very dangerous query… Restart the SQL Server, run the query right away, and none of the indexes will show any activity… Does that mean we can disable all of them?

yes that’s the only drawback. On a sql server that has been run for months, the information would definitely be usefull.