== Nov 9 update ==
In some cases we got integer overflow when running this query. So the table definitions in the query have now changed from using int to bigint to avoid this.
== end of update ==
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 (Lohndorf )
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] [bigint]
NOT
NULL,
[F_Obj_ID] [bigint]
NOT
NULL,
[F_Schema_Name] [nvarchar]
(128)
NULL,
[F_Table_Name] [nvarchar]
(128) NOT
NULL,
[F_Row_Count] [bigint]
NULL,
[F_Reserved] [bigint]
NULL,
[F_Data] [bigint]
NULL,
[F_Index_Size] [bigint]
NULL,
[F_UnUsed] [bigint]
NULL,
[F_Index_Name] [nvarchar]
(128)
NULL,
[F_Index_ID] [bigint]
NOT
NULL,
[F_Column_Name] [nvarchar]
(128)
NULL,
[F_User_Updates] [bigint]
NULL,
[F_User_Reads] [bigint]
NULL,
[F_Locks] [bigint]
NULL,
[F_Blocks] [bigint]
NULL,
[F_Block_Wait_Time] [bigint]
NULL,
[F_Last_Used] [datetime]
NULL,
[F_Index_Type] [nvarchar]
(128) NOT
NULL,
[F_Index_Column_ID] [bigint]
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