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]