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

[Blog bot]

Источник: https://blogs.msdn.microsoft.com/nav...cks-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


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


[F_Blocks] [int] NULL,


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


– 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


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 <FONT color=#808080 size=2><FONT color=#808080 size=2>