== 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