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 (Lohndorf)
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



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

([F_Obj_ID] ASC



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

([F_Index_ID] ASC



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

([F_Row_Count] ASC




insert into



(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




-- Index Stats


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


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,












WHEN (ps.index_id < 2) THEN row_count



) AS [rows],

SUM (ps.reserved_page_count) AS reserved,



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)


) AS data,

SUM (ps.used_page_count) AS used

FROM sys.dm_db_partition_stats ps

GROUP BY ps.object_id) AS a1




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


-- 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


close IndexCursor

deallocate IndexCursor


-- clean up table to one line per index

delete from z_IUQ2_Temp_Index_Keys

where [F_Index_Column_ID] > 1



[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 (1)

  1. igortsk@mail.ru says:

    I work with the base MS AX 2012. Currently a very acute problem with the indexes. Base is very large (more than 2 TB). I need to get this information (from your script) only for the indexes and tables which are > xx mb, and also to see the fragmentation of this indexes

    I did script to get information about fragmentation, help to combine it with yours!

    use AX2012_Dev_spconfig

    select * from

    (select /*OBJECT_NAME(t1.object_id),*/ t1.avg_fragmentation_in_percent, t2.name as TableName, Min (t2.create_date) as CreateDate, ds.name as FileGroupName, SUM (u.total_pages) * 8/1024 as SizeMB

    FROM sys.dm_db_index_physical_stats (DB_ID ('AX2012_Dev_spconfig'), NULL, NULL, NULL, NULL) as t1

    inner join sys.tables as t2 on

    OBJECT_NAME (t1.object_id) = t2. [Name]

    inner join sys.partitions as p on t2.object_id = p.object_id

    inner join sys.allocation_units as u on p.partition_id = u.container_id

    inner join sys.data_spaces as ds on u.data_space_id = ds.data_space_id

    where avg_fragmentation_in_percent> 20 and OBJECT_NAME (t1.object_id) in

    (select obj. [name]

    from sys.tables as obj

    inner join sys.schemas as s on

      obj.schema_id = s.schema_id

    inner join sys.dm_db_partition_stats as c on

      c.object_id = obj.object_id

    WHERE (c.index_id in (0,1)) and (c.row_count> '10 ')

    group by obj. [name] with rollup)

    group by t2.name, ds.name, t1.avg_fragmentation_in_percent

    ) As r

    where r.SizeMB> 5

    order by r.SizeMB desc

    I would be very grateful for the help!

Skip to main content