SQL Query to show tables, their indexes and index usage

  • [アーティクル]

The SQL query below shows a list of tables sorted by the largest tables first. For each table, the indexes in the table are shown. For each index it shows when the index was last used. The query is designed to collect various pieces of information in one place, and give an overview to help supporting decisions when doing index tuning.

The left hand columns show data for the table (No. of receords, data- and index size) to have a view of the impact of having indexes on the table.

The right hand columns show data for each index, including Updates (costs) and Reads (benefits) and when it was last used sine the last time SQL Server was restarted.

 

Further comments:

  • The query only works on SQL Server 2005 and later.
  • The numbers in the query are reset every time SQL Server restarts.
  • The query may take up to a few minutes to run.
  • The query is provided "as is", with no warranties and confers no rights. You assume all risk for your use.

 

If you have comments or feedback, please feel free to post them here.

 

 

Best regards

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

 

 

--use NavisionDatabase

IF

OBJECT_ID ('z_IUQ2_Temp_Index_Keys', 'U') IS NOT NULL

DROP

TABLE z_IUQ2_Temp_Index_Keys;

-- Generate list of indexes with key list

create

table z_IUQ2_Temp_Index_Keys(

[l1] [int]

NOT NULL,

[F_Obj_ID] [int]

NOT NULL,

[F_Schema_Name] [nvarchar]

(128) NULL,

[F_Table_Name] [nvarchar]

(128) NOT NULL,

[F_Row_Count] [int]

NULL,

[F_Reserved] [int]

NULL,

[F_Data] [int]

NULL,

[F_Index_Size] [int]

NULL,

[F_UnUsed] [int]

NULL,

[F_Index_Name] [nvarchar]

(128) NOT NULL,

[F_Index_ID] [int]

NOT NULL,

[F_Column_Name] [nvarchar]

(128) NOT NULL,

[F_User_Updates] [int]

NULL,

[F_User_Reads] [int]

NULL,

[F_Last_Used] [datetime]

NULL,

[F_Index_Type] [nvarchar]

(128) NOT NULL,

[F_Index_Column_ID] [int]

NOT NULL,

[F_Last_Seek] [datetime]

NULL,

[F_Last_Scan] [datetime]

NULL,

[F_Last_Lookup] [datetime]

NULL,

[Index_Key_List] [nvarchar]

(MAX) NULL

)

go

CREATE

NONCLUSTERED INDEX [Object_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]

(

[F_Obj_ID] ASC

)

go

CREATE

NONCLUSTERED INDEX [Index_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]

(

[F_Index_ID] ASC

)

go

CREATE

NONCLUSTERED INDEX [RowCount_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]

(

[F_Row_Count] ASC

)

go

insert

into

z_IUQ2_Temp_Index_Keys

SELECT

(row_number() over(order by a3.name, a2.name))%2 as l1,

a1

.object_id, ---

a3

.name AS [schemaname],

a2

.name AS [tablename],

a1

.rows as row_count,

(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,

a1

.data * 8 AS data,

(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,

(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused,

-- Index Description

SI

.name,

SI

.Index_ID,

index_col

(object_name(SIC.object_id),SIC.index_id,SIC.Index_Column_ID),

-- Index Stats

US

.user_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,

SIC

.index_column_id,

US

.last_user_seek,

US

.last_user_scan,

US

.last_user_lookup,

''

FROM

(SELECT

ps

.object_id,

SUM (

CASE

WHEN (ps.index_id < 2) THEN row_count

ELSE 0

END

) AS [rows],

SUM (ps.reserved_page_count) AS reserved,

SUM (

CASE

WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)

ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)

END

) AS data,

SUM (ps.used_page_count) AS used

FROM sys.dm_db_partition_stats ps

GROUP BY ps.object_id) AS a1

LEFT

OUTER JOIN

(SELECT

it

.parent_id,

SUM(ps.reserved_page_count) AS reserved,

SUM(ps.used_page_count) AS used

FROM sys.dm_db_partition_stats ps

INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)

WHERE it.internal_type IN (202,204)

GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)

INNER

JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )

INNER

JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

inner

join sys.indexes SI ON (SI.object_id = a1."object_id") ---

inner

join sys.index_columns SIC ON (SIC.object_id = SI.object_id and SIC.index_id = SI.index_id)

left

outer join sys.dm_db_index_usage_stats US ON (US.object_id = SI.object_id and US.index_id = SI.index_id)

WHERE

a2.type <> N'S' and a2.type <> N'IT'

order

by row_count desc

go

-- Populate key string

declare

IndexCursor cursor for

select

F_Obj_ID, F_Index_ID from z_IUQ2_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,'') + F_Column_Name + ', '

from

z_IUQ2_Temp_Index_Keys

where

F_Obj_ID = @ObjID and F_Index_ID = @IndID

ORDER

BY F_Index_ID, F_Index_Column_ID

set

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

update

z_IUQ2_Temp_Index_Keys

set

Index_Key_List = @KeyString

where

current of IndexCursor

fetch

next from IndexCursor into @ObjID, @IndID

end

;

close

IndexCursor

deallocate

IndexCursor

go

-- clean up table to one line per index

delete

from z_IUQ2_Temp_Index_Keys

where

[F_Index_Column_ID] > 1

go

select

[F_Table_Name] TableName

,

[F_Row_Count] No_Of_Records

,

[F_Data] Data_Size

,

[F_Index_Size] Index_Size

,

[F_UnUsed] UnUsed_Space

,

[F_Index_Name] Index_Name

,

[F_User_Updates] Index_Updates

,

[F_User_Reads] Index_Reads

,

[F_Last_Used] Index_Last_Used

,

[F_Index_Type] Index_Type

,

[Index_Key_List] Index_Fields

from

z_IUQ2_Temp_Index_Keys

order

by F_Row_Count desc, F_Table_Name, [F_Index_ID]

Comments

  • Anonymous
    June 10, 2009
    Great post - thanks again Lars. However; I tend to get 2 entries for several indexes, where the only differences are these colums: "Index_Updates" and "Index_Reads" and "Index_Last_Used". Do you have any explanation for these entries? (I'll be happy to send you my spreadsheet if you like)

  • Anonymous
    June 14, 2009
    Thank you! And yes, please send me your spreadsheet - I guess it's nothing to do with tables where DataPerCompany = No? Lars

  • Anonymous
    April 04, 2011
    There is a problem if you are using "Included Columns". There is also a problem with schema names.