Index Usage Information Tool

Index Usage Information Tool

  • Comments 4

Attached at the end of this post is a set of NAV objects that collects index usage information, and displays it in a NAV client so that you can sort tables by no. of Indexes / Index Reads / (Index) Block wait time, etc.:

 

IUITList

The tool is using these three SQL Queries / DMVs:

  • sys.indexes: Basic information about indexes
  • sys.dm_db_index_usage_stats: No. of Index Reads, updates, etc
  • sys.dm_db_index_operational_stats: Index blocking / wait time etc.

The information that is collected is already described in these posts:

So the new thing is that now the information is read into NAV, making it more easy to browse through it, get an overview, and having information from various queries collected in one place.

Note:

  • The tool, relying on Dynamic Management Views (DMVs) will only work for SQL2005 and later (not SQL2000).
  • The information that is collected is since last time SQL Server was restarted.

The tool contains the following objects:

Type ID Name
Table 74100 IUIT Setup
Table 74101 IUIT Index
Table 74102 IUIT Table
Form 74100 IUIT Setup
Form 74101 IUIT Index List
Form 74102 IUIT Table List
Codeunit 74100 IUIT Mgt

And it assumes that you have a partner's developers license.

To run it:

Run form 74102 "IUIT Table List". First set up the SQL connection by clicking Table -> Setup, and specify SQL Server Name, Database Name and credentials.

Then go back to the "IUIT Table List"-form, and click Functions -> Update.

After the tool has collected the information (takes a few minutes), you can sort your tables by a number of factors which can all affect performance. And you can look at indexes for each table (Table -> Indexes (Ctrl+F5)), and get statistics for each individual index.

 

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

Lars Lohndorf-Larsen ( Lohndorf)

Microsoft Dynamics UK
Microsoft Customer Service and Support (CSS) EMEA

Attachment: iuit.fob
Leave a Comment
  • Please add 3 and 4 and type the answer here:
  • Post
  • Do you have a version with Pages instead of Forms?

  • Unfortunately no page-based tool is currently planned.

    Nowadays I would just run the query, especially "Index Usage Query" linked above on SQL Server, and get the results into Excel.

  • I have transformed the pages and edited the code so it now runs on 2013. Do you want the new objects?

    Changes are both marked in code and on version list.

  • Just a mail to tl [REMOVE] @ [REMOVE] navtilus.dk

Page 1 of 1 (4 items)