Table Information including Index information (Usage, Blocks and Reads)


 


The query below combines these three queries into one:
Index Usage Query
Recent Bocking History
Table Information Query


 


It can be used to just see the number of records in each table. But also by just changing "ORDER BY", it can be used to see which index cause most blocking / wait time / updates or locks. Or to compare Index Updates with Index Reads to get an idea of cost versus benefit for each index for the purpose of index tuning.


So in short, one query gives you:
  - Index / Table Information
  - Index usage (benefits and costs information for each index)
  - Index locks, blocks, wait time and updates per read (cost/benefit).


 


The query must be run in your NAV database. It will create a table called z_IUQ2_Temp_Index_Keys and use various Dynamic Management Views to collect information for each index into this table. First time you run it, or if you want to refresh data, you must run the whole query which may take up to a minute of two for each company in the database. After that if you just want to change sorting / get the results again, then you only need to run the last part of the query, beginning with:


-- Select results



The last lines suggest various "ORDER BY"s that might be useful to enable instead of the default one, which is by Table Name.


 


 


Lars Lohndorf-Larsen


Microsoft Dynamics UK


Microsoft Customer Service and Support (CSS) EMEA


These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.


 


--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_Locks] [int] NULL,


[F_Blocks] [int] NULL,


[F_Block_Wait_Time] [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,


-- Index blocks


IStats.row_lock_count + IStats.page_lock_count ,


IStats.row_lock_wait_count + IStats.page_lock_wait_count,


IStats.row_lock_wait_in_ms + IStats.page_lock_wait_in_ms,


-- Dates


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 and US.database_id = db_id())


left outer join sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) IStats ON (IStats.object_id = SI.object_id and IStats.index_id = SI.index_id and IStats.database_id = db_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 results


select


[F_Table_Name] TableName,


[F_Row_Count] No_Of_Records,


[F_Data] Data_Size,


[F_Index_Size] Index_Size,


[F_Index_Name] Index_Name,


[F_User_Updates] Index_Updates,


[F_User_Reads] Index_Reads,


case


when F_User_Reads = 0 then F_User_Updates


else F_User_Updates / F_User_Reads


end as Updates_Per_Read,


[F_Locks] Locks,


[F_Blocks] Blocks,


[F_Block_Wait_Time] Block_Wait_Time,


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


--order by F_User_Updates desc


--order by Blocks desc


--order by Block_Wait_Time desc


--order by Updates_Per_Read desc


order by F_Table_Name


 


 


 

Comments (0)

Skip to main content