Nav developer: SQL Query to show tables, their indexes and index usage

[Blog bot]

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


IFOBJECT_ID('z_IUQ2_Temp_Index_Keys','U')ISNOTNULL


DROPTABLE z_IUQ2_Temp_Index_Keys;


-- Generate list of indexes with key list


createtable z_IUQ2_Temp_Index_Keys(


[l1] [int] NOTNULL,


[F_Obj_ID] [int] NOTNULL,


[F_Schema_Name] [nvarchar] (128)NULL,


[F_Table_Name] [nvarchar] (128)NOTNULL,


[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)NOTNULL,


[F_Index_ID] [int] NOTNULL,


[F_Column_Name] [nvarchar] (128)NOTNULL,


[F_User_Updates] [int] NULL,


[F_User_Reads] [int] NULL,


[F_Last_Used] [datetime] NULL,


[F_Index_Type] [nvarchar] (128)NOTNULL,


[F_Index_Column_ID] [int] NOTNULL,


[F_Last_Seek] [datetime] NULL,


[F_Last_Scan] [datetime] NULL,


[F_Last_Lookup] [datetime] NULL,


[Index_Key_List] [nvarchar] (MAX)NULL


)


go


CREATENONCLUSTEREDINDEX [Object_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]


([F_Obj_ID] ASC


)


go


CREATENONCLUSTEREDINDEX [Index_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]


([F_Index_ID] ASC


)


go


CREATENONCLUSTEREDINDEX [RowCount_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]


([F_Row_Count] ASC


)


go





insertinto


z_IUQ2_Temp_Index_Keys


SELECT


(row_number()over(orderby 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,


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


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


endas 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


FROMsys.dm_db_partition_stats ps


GROUPBY ps.object_id)AS a1


LEFTOUTERJOIN


(SELECT


it.parent_id,


SUM(ps.reserved_page_count)AS reserved,


SUM(ps.used_page_count)AS used


FROMsys.dm_db_partition_stats ps


INNERJOINsys.internal_tables it ON(it.object_id= ps.object_id)


WHERE it.internal_type IN(202,204)


GROUPBY it.parent_id)AS a4 ON(a4.parent_id = a1.object_id)


INNERJOINsys.all_objects a2 ON( a1.object_id= a2.object_id)


INNERJOINsys.schemas a3 ON(a2.schema_id= a3.schema_id)


innerjoinsys.indexes SI ON(SI.object_id= a1."object_id")---


innerjoinsys.index_columns SIC ON(SIC.object_id= SI.object_idand SIC.index_id = SI.index_id)


leftouterjoinsys.dm_db_index_usage_stats US ON(US.object_id= SI.object_idand US.index_id = SI.index_id)


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


orderby row_count desc


go


-- Populate key string


declare IndexCursor cursorfor


select F_Obj_ID, F_Index_ID from z_IUQ2_Temp_Index_Keys


forupdateof Index_Key_List


declare @objID int


declare @IndID int


declare @KeyString VARCHAR(MAX)


set @KeyString =NULL


open IndexCursor


setnocounton


fetchnextfrom 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


ORDERBY 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


wherecurrentof IndexCursor


fetchnextfrom IndexCursor into @ObjID, @IndID


end;


close IndexCursor


deallocate IndexCursor


go


-- clean up table to one line per index


deletefrom 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


orderby F_Row_Count desc, F_Table_Name, [F_Index_ID]












Читать дальше