Retaining historical index usage statistics for SQL Server Part 2 of 3

By:   |   Updated: 2009-06-05   |   Comments (3)   |   Related: 1 | 2 | 3 | More > Indexing

Problem
In part one of this series of persisting Dynamic Management View data that is stored in the cache and sourced from the Query Optimizer we examined the process for persisting data for the sys.dm_dm_index_usage_stats DMV.  That DMV stores usage metrics (as the name implies) for all the indexes on a Microsoft SQL Server instance.  Welcome to part two, in which we look at performing the same task for the sys.dm_db_index_operational_stats Dynamic Management Function (or DMF.)  The process is identical, the code is however quite different due to the structural differences between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.  To recap why we would want to do this I suggest reviewing part one of the series.  In short, we do so because the information is cached and whenever the SQL Server service is restarted that information, which is extremely valuable for performance tuning as well as diagnosing locking and blocking issues over time, is lost forever.

Solution
If you have been following along with this series you will have already created a dedicated database for the objects we'll be creating in this tip.  The database is similar to one I use on all my supported SQL Server instances for storing metadata for various administrative purposes from automated job code executed via SQL Server Agent, functions and stored procedures used for diagnosing performance and space consumption, and of course, persisted data from various Dynamic Management Objects such as what we're examining in this series.  However, for those of you just joining us in this tip, then you'll need to run the CREATE DATABASE Transact-SQL statement below as well as the code required to create the MetaBot schema that owns the objects in this series. 

Step One:  Create the Administrative Database and Necessary Supporting Objects

I separate my database objects within my administrative database by schema.  You'll see below that after creation of the database, I create a MetaBot schema that will serve as the schema for all objects of this process type.

USE [master]
GO
CREATE DATABASE [iDBA] ON  PRIMARY 
  
(
  
NAME N'iDBA'
  
FILENAME N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\iDBA.mdf' 
  
SIZE 10MB 
  
FILEGROWTH 5MB
  
)
  
LOG ON 
  
(
  
NAME N'iDBA_log'
  
FILENAME N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\iDBA_log.ldf' 
  
SIZE 5120KB 
  
FILEGROWTH 5120KB
  
)
GO

USE [iDBA]
GO
CREATE SCHEMA [MetaBot] AUTHORIZATION [dbo]
GO

From here we only require two user-created database objects:  the repository table that will persist the data (MetaBot.dm_db_index_operational_stats) and the stored procedure used to collect it. 

USE [iDBA]
GO
CREATE TABLE [MetaBot].[dm_db_index_operational_stats](
 
[database_id] [smallint] NOT NULL,
 
[object_id] [int] NOT NULL,
 
[index_id] [int] NOT NULL,
 
[partition_number] [int] NOT NULL,
 
[leaf_insert_count] [bigint] NOT NULL,
 
[leaf_delete_count] [bigint] NOT NULL,
 
[leaf_update_count] [bigint] NOT NULL,
 
[leaf_ghost_count] [bigint] NOT NULL,
 
[nonleaf_insert_count] [bigint] NOT NULL,
 
[nonleaf_delete_count] [bigint] NOT NULL,
 
[nonleaf_update_count] [bigint] NOT NULL,
 
[leaf_allocation_count] [bigint] NOT NULL,
 
[nonleaf_allocation_count] [bigint] NOT NULL,
 
[leaf_page_merge_count] [bigint] NOT NULL,
 
[nonleaf_page_merge_count] [bigint] NOT NULL,
 
[range_scan_count] [bigint] NOT NULL,
 
[singleton_lookup_count] [bigint] NOT NULL,
 
[forwarded_fetch_count] [bigint] NOT NULL,
 
[lob_fetch_in_pages] [bigint] NOT NULL,
 
[lob_fetch_in_bytes] [bigint] NOT NULL,
 
[lob_orphan_create_count] [bigint] NOT NULL,
 
[lob_orphan_insert_count] [bigint] NOT NULL,
 
[row_overflow_fetch_in_pages] [bigint] NOT NULL,
 
[row_overflow_fetch_in_bytes] [bigint] NOT NULL,
 
[column_value_push_off_row_count] [bigint] NOT NULL,
 
[column_value_pull_in_row_count] [bigint] NOT NULL,
 
[row_lock_count] [bigint] NOT NULL,
 
[row_lock_wait_count] [bigint] NOT NULL,
 
[row_lock_wait_in_ms] [bigint] NOT NULL,
 
[page_lock_count] [bigint] NOT NULL,
 
[page_lock_wait_count] [bigint] NOT NULL,
 
[page_lock_wait_in_ms] [bigint] NOT NULL,
 
[index_lock_promotion_attempt_count] [bigint] NOT NULL,
 
[index_lock_promotion_count] [bigint] NOT NULL,
 
[page_latch_wait_count] [bigint] NOT NULL,
 
[page_latch_wait_in_ms] [bigint] NOT NULL,
 
[page_io_latch_wait_count] [bigint] NOT NULL,
 
[page_io_latch_wait_in_ms] [bigint] NOT NULL,
 
[last_poll_leaf_insert_count] [bigint] NOT NULL,
 
[last_poll_leaf_delete_count] [bigint] NOT NULL,
 
[last_poll_leaf_update_count] [bigint] NOT NULL,
 
[last_poll_leaf_ghost_count] [bigint] NOT NULL,
 
[last_poll_nonleaf_insert_count] [bigint] NOT NULL,
 
[last_poll_nonleaf_delete_count] [bigint] NOT NULL,
 
[last_poll_nonleaf_update_count] [bigint] NOT NULL,
 
[last_poll_leaf_allocation_count] [bigint] NOT NULL,
 
[last_poll_nonleaf_allocation_count] [bigint] NOT NULL,
 
[last_poll_leaf_page_merge_count] [bigint] NOT NULL,
 
[last_poll_nonleaf_page_merge_count] [bigint] NOT NULL,
 
[last_poll_range_scan_count] [bigint] NOT NULL,
 
[last_poll_singleton_lookup_count] [bigint] NOT NULL,
 
[last_poll_forwarded_fetch_count] [bigint] NOT NULL,
 
[last_poll_lob_fetch_in_pages] [bigint] NOT NULL,
 
[last_poll_lob_fetch_in_bytes] [bigint] NOT NULL,
 
[last_poll_lob_orphan_create_count] [bigint] NOT NULL,
 
[last_poll_lob_orphan_insert_count] [bigint] NOT NULL,
 
[last_poll_row_overflow_fetch_in_pages] [bigint] NOT NULL,
 
[last_poll_row_overflow_fetch_in_bytes] [bigint] NOT NULL,
 
[last_poll_column_value_push_off_row_count] [bigint] NOT NULL,
 
[last_poll_column_value_pull_in_row_count] [bigint] NOT NULL,
 
[last_poll_row_lock_count] [bigint] NOT NULL,
 
[last_poll_row_lock_wait_count] [bigint] NOT NULL,
 
[last_poll_row_lock_wait_in_ms] [bigint] NOT NULL,
 
[last_poll_page_lock_count] [bigint] NOT NULL,
 
[last_poll_page_lock_wait_count] [bigint] NOT NULL,
 
[last_poll_page_lock_wait_in_ms] [bigint] NOT NULL,
 
[last_poll_index_lock_promotion_attempt_count] [bigint] NOT NULL,
 
[last_poll_index_lock_promotion_count] [bigint] NOT NULL,
 
[last_poll_page_latch_wait_count] [bigint] NOT NULL,
 
[last_poll_page_latch_wait_in_ms] [bigint] NOT NULL,
 
[last_poll_page_io_latch_wait_count] [bigint] NOT NULL,
 
[last_poll_page_io_latch_wait_in_ms] [bigint] NOT NULL,
 
[date_stamp] [datetime] NOT NULL
 
CONSTRAINT [PK_dm_db_index_operational_stats] PRIMARY KEY CLUSTERED 
   
(
   
[database_id] ASC,
   
[object_id] ASC,
   
[index_id] ASC,
   
[partition_number] ASC
   
)
   
WITH 
     
(
     
PAD_INDEX  = OFF
     
STATISTICS_NORECOMPUTE  = OFF
     
IGNORE_DUP_KEY = OFF
     
ALLOW_ROW_LOCKS  = ON
     
ALLOW_PAGE_LOCKS  = ON
       
ON [PRIMARY]
ON [PRIMARY]

The table is (for the most part) a duplicate in structure of the sys.dm_db_index_operational_stats Dynamic Management Function with a few notable exceptions:

  • collection of columns prefixed with "last_poll_"
  • date_stamp column

The last_poll_... columns are vital for the calculations that go into the incrementing and updating of usage counts each time the stored procedure will run against sys.dm_db_index_operational_stats.  If your instance never is subject to a service restart these columns would not be necessary, however we all know that is not the case.  Even the best-constructed environments must still have routine maintenance that will require bringing the system down.  The date_stamp column will record when the last refresh of MetaBot.dm_db_index_operational_stats occurred.  It also serves to determine how the usage values are incremented in that it is compared to the last time the services were cycled.  Depending on whether that occurred pre or post the last refresh occurred, a different calculation is used to update the related columns.  This process is identical to what I outlined for the sys.dm_db_index_usage_stats DMV, taking the structural changes (far more columns; none of which are date-based) into consideration. 

USE [iDBA]
GO
CREATE PROCEDURE MetaBot.usp_persist_dm_db_index_operational_stats AS 
DECLARE 
@last_service_start_date datetime
DECLARE @last_data_persist_date datetime

--Determine last service restart date based upon tempdb creation date
SELECT @last_service_start_date =  
 
SD.[create_date] 
 
FROM sys.databases SD 
 
WHERE SD.[name] 'tempdb'
 
--Return the value for the last refresh date of the persisting table
SELECT @last_data_persist_date 
 MAX
(MDDIOS.[date_stamp]
 
FROM [iDBA].[MetaBOT].[dm_db_index_operational_stats] MDDIOS

--Take care of updated records first
IF @last_service_start_date @last_data_persist_date
 
BEGIN
   
--Service restart date > last poll date
   
PRINT 'The latest persist date was ' 
     
CAST(@last_data_persist_date AS VARCHAR(50)) + 
     
'; no restarts occurred since ' 
     
CAST(@last_service_start_date AS VARCHAR(50)) +
     
'  (' CAST(DATEDIFF(d@last_service_start_date@last_data_persist_dateAS VARCHAR(10)) + 
     
' days ago.)'

   
UPDATE MDDIOS
   
SET 
     
MDDIOS.[leaf_insert_count] MDDIOS.[leaf_insert_count] + (SDDIOS.[leaf_insert_count] MDDIOS.[last_poll_leaf_insert_count]),
     
MDDIOS.[leaf_delete_count] MDDIOS.[leaf_delete_count] + (SDDIOS.[leaf_delete_count] MDDIOS.[last_poll_leaf_delete_count]),
     
MDDIOS.[leaf_update_count] MDDIOS.[leaf_update_count] + (SDDIOS.[leaf_update_count] MDDIOS.[last_poll_leaf_update_count]),
     
MDDIOS.[leaf_ghost_count] MDDIOS.[leaf_ghost_count] + (SDDIOS.[leaf_ghost_count] MDDIOS.[last_poll_leaf_ghost_count]),
     
MDDIOS.[nonleaf_insert_count] MDDIOS.[nonleaf_insert_count] + (SDDIOS.[nonleaf_insert_count] MDDIOS.[last_poll_nonleaf_insert_count]),
     
MDDIOS.[nonleaf_delete_count] MDDIOS.[nonleaf_delete_count] + (SDDIOS.[nonleaf_delete_count] MDDIOS.[last_poll_nonleaf_delete_count]),
     
MDDIOS.[nonleaf_update_count] MDDIOS.[nonleaf_update_count] + (SDDIOS.[nonleaf_update_count] MDDIOS.[last_poll_nonleaf_update_count]),
     
MDDIOS.[leaf_allocation_count] MDDIOS.[leaf_allocation_count] + (SDDIOS.[leaf_allocation_count] MDDIOS.[last_poll_leaf_allocation_count]),
     
MDDIOS.[nonleaf_allocation_count] MDDIOS.[nonleaf_allocation_count] + (SDDIOS.[nonleaf_allocation_count] MDDIOS.[last_poll_nonleaf_allocation_count]),
     
MDDIOS.[leaf_page_merge_count] MDDIOS.[leaf_page_merge_count] + (SDDIOS.[leaf_page_merge_count] MDDIOS.[last_poll_leaf_page_merge_count]),
     
MDDIOS.[nonleaf_page_merge_count] MDDIOS.[nonleaf_page_merge_count] + (SDDIOS.[nonleaf_page_merge_count] MDDIOS.[last_poll_nonleaf_page_merge_count]),
     
MDDIOS.[range_scan_count] MDDIOS.[range_scan_count] + (SDDIOS.[range_scan_count] MDDIOS.[last_poll_range_scan_count]),
     
MDDIOS.[singleton_lookup_count] MDDIOS.[singleton_lookup_count] + (SDDIOS.[singleton_lookup_count] MDDIOS.[last_poll_singleton_lookup_count]),
     
MDDIOS.[forwarded_fetch_count] MDDIOS.[forwarded_fetch_count] + (SDDIOS.[forwarded_fetch_count] MDDIOS.[last_poll_forwarded_fetch_count]),
     
MDDIOS.[lob_fetch_in_pages] MDDIOS.[lob_fetch_in_pages] + (SDDIOS.[lob_fetch_in_pages] MDDIOS.[last_poll_lob_fetch_in_pages]),
     
MDDIOS.[lob_fetch_in_bytes] MDDIOS.[lob_fetch_in_bytes] + (SDDIOS.[lob_fetch_in_bytes] MDDIOS.[last_poll_lob_fetch_in_bytes]),
     
MDDIOS.[lob_orphan_create_count] MDDIOS.[lob_orphan_create_count] + (SDDIOS.[lob_orphan_create_count] MDDIOS.[last_poll_lob_orphan_create_count]),
     
MDDIOS.[lob_orphan_insert_count] MDDIOS.[lob_orphan_insert_count] + (SDDIOS.[lob_orphan_insert_count] MDDIOS.[last_poll_lob_orphan_insert_count]),
     
MDDIOS.[row_overflow_fetch_in_pages] MDDIOS.[row_overflow_fetch_in_pages] + (SDDIOS.[row_overflow_fetch_in_pages] MDDIOS.[last_poll_row_overflow_fetch_in_pages]),
     
MDDIOS.[row_overflow_fetch_in_bytes] MDDIOS.[row_overflow_fetch_in_bytes] + (SDDIOS.[row_overflow_fetch_in_bytes] MDDIOS.[last_poll_row_overflow_fetch_in_bytes]),
     
MDDIOS.[column_value_push_off_row_count] MDDIOS.[column_value_push_off_row_count] + (SDDIOS.[column_value_push_off_row_count] MDDIOS.[last_poll_column_value_push_off_row_count]),
     
MDDIOS.[column_value_pull_in_row_count] MDDIOS.[column_value_pull_in_row_count] + (SDDIOS.[column_value_pull_in_row_count] MDDIOS.[last_poll_column_value_pull_in_row_count]),
     
MDDIOS.[row_lock_count] MDDIOS.[row_lock_count] + (SDDIOS.[row_lock_count] MDDIOS.[last_poll_row_lock_count]),
     
MDDIOS.[row_lock_wait_count] MDDIOS.[row_lock_wait_count] + (SDDIOS.[row_lock_wait_count] MDDIOS.[last_poll_row_lock_wait_count]),
     
MDDIOS.[row_lock_wait_in_ms] MDDIOS.[row_lock_wait_in_ms] + (SDDIOS.[row_lock_wait_in_ms] MDDIOS.[last_poll_row_lock_wait_in_ms]),
     
MDDIOS.[page_lock_count] MDDIOS.[page_lock_count] + (SDDIOS.[page_lock_count] MDDIOS.[last_poll_page_lock_count]),
     
MDDIOS.[page_lock_wait_count] MDDIOS.[page_lock_wait_count] + (SDDIOS.[page_lock_wait_count] MDDIOS.[last_poll_page_lock_wait_count]),
     
MDDIOS.[page_lock_wait_in_ms] MDDIOS.[page_lock_wait_in_ms] + (SDDIOS.[page_lock_wait_in_ms] MDDIOS.[last_poll_page_lock_wait_in_ms]),
     
MDDIOS.[index_lock_promotion_attempt_count] MDDIOS.[index_lock_promotion_attempt_count] + (SDDIOS.[index_lock_promotion_attempt_count] MDDIOS.[last_poll_index_lock_promotion_attempt_count]),
     
MDDIOS.[index_lock_promotion_count] MDDIOS.[index_lock_promotion_count] + (SDDIOS.[index_lock_promotion_count] MDDIOS.[last_poll_index_lock_promotion_count]),
     
MDDIOS.[page_latch_wait_count] MDDIOS.[page_latch_wait_count] + (SDDIOS.[page_latch_wait_count] MDDIOS.[last_poll_page_latch_wait_count]),
     
MDDIOS.[page_latch_wait_in_ms] MDDIOS.[page_latch_wait_in_ms] + (SDDIOS.[page_latch_wait_in_ms] MDDIOS.[last_poll_page_latch_wait_in_ms]),
     
MDDIOS.[page_io_latch_wait_count] MDDIOS.[page_io_latch_wait_count] + (SDDIOS.[page_io_latch_wait_count] MDDIOS.[last_poll_page_io_latch_wait_count]),
     
MDDIOS.[page_io_latch_wait_in_ms] MDDIOS.[page_io_latch_wait_in_ms] + (SDDIOS.[page_io_latch_wait_in_ms] MDDIOS.[last_poll_page_io_latch_wait_in_ms]),
     
MDDIOS.[last_poll_leaf_insert_count] SDDIOS.[leaf_insert_count],
     
MDDIOS.[last_poll_leaf_delete_count] SDDIOS.[leaf_delete_count],
     
MDDIOS.[last_poll_leaf_update_count] SDDIOS.[leaf_update_count],
     
MDDIOS.[last_poll_leaf_ghost_count] SDDIOS.[leaf_ghost_count],
     
MDDIOS.[last_poll_nonleaf_insert_count] SDDIOS.[nonleaf_insert_count],
     
MDDIOS.[last_poll_nonleaf_delete_count] SDDIOS.[nonleaf_delete_count],
     
MDDIOS.[last_poll_nonleaf_update_count] SDDIOS.[nonleaf_update_count],
     
MDDIOS.[last_poll_leaf_allocation_count] SDDIOS.[leaf_allocation_count],
     
MDDIOS.[last_poll_nonleaf_allocation_count] SDDIOS.[nonleaf_allocation_count],
     
MDDIOS.[last_poll_leaf_page_merge_count] SDDIOS.[leaf_page_merge_count],
     
MDDIOS.[last_poll_nonleaf_page_merge_count] SDDIOS.[nonleaf_page_merge_count],
     
MDDIOS.[last_poll_range_scan_count] SDDIOS.[range_scan_count],
     
MDDIOS.[last_poll_singleton_lookup_count] SDDIOS.[singleton_lookup_count],
     
MDDIOS.[last_poll_forwarded_fetch_count] SDDIOS.[forwarded_fetch_count],
     
MDDIOS.[last_poll_lob_fetch_in_pages] SDDIOS.[lob_fetch_in_pages],
     
MDDIOS.[last_poll_lob_fetch_in_bytes] SDDIOS.[lob_fetch_in_bytes],
     
MDDIOS.[last_poll_lob_orphan_create_count] SDDIOS.[lob_orphan_create_count],
     
MDDIOS.[last_poll_lob_orphan_insert_count] SDDIOS.[lob_orphan_insert_count],
     
MDDIOS.[last_poll_row_overflow_fetch_in_pages] SDDIOS.[row_overflow_fetch_in_pages],
     
MDDIOS.[last_poll_row_overflow_fetch_in_bytes] SDDIOS.[row_overflow_fetch_in_bytes],
     
MDDIOS.[last_poll_column_value_push_off_row_count] SDDIOS.[column_value_push_off_row_count],
     
MDDIOS.[last_poll_column_value_pull_in_row_count] SDDIOS.[column_value_pull_in_row_count],
     
MDDIOS.[last_poll_row_lock_count] SDDIOS.[row_lock_count],
     
MDDIOS.[last_poll_row_lock_wait_count] SDDIOS.[row_lock_wait_count],
     
MDDIOS.[last_poll_row_lock_wait_in_ms] SDDIOS.[row_lock_wait_in_ms],
     
MDDIOS.[last_poll_page_lock_count] SDDIOS.[page_lock_count],
     
MDDIOS.[last_poll_page_lock_wait_count] SDDIOS.[page_lock_wait_count],
     
MDDIOS.[last_poll_page_lock_wait_in_ms] SDDIOS.[page_lock_wait_in_ms],
     
MDDIOS.[last_poll_index_lock_promotion_attempt_count] SDDIOS.[index_lock_promotion_attempt_count],
     
MDDIOS.[last_poll_index_lock_promotion_count] SDDIOS.[index_lock_promotion_count],
     
MDDIOS.[last_poll_page_latch_wait_count] SDDIOS.[page_latch_wait_count],
     
MDDIOS.[last_poll_page_latch_wait_in_ms] SDDIOS.[page_latch_wait_in_ms],
     
MDDIOS.[last_poll_page_io_latch_wait_count] SDDIOS.[page_io_latch_wait_count],
     
MDDIOS.[last_poll_page_io_latch_wait_in_ms] SDDIOS.[page_io_latch_wait_in_ms],      
     
MDDIOS.date_stamp GETDATE()
   
FROM [sys].[dm_db_index_operational_stats] (NULL, NULL, NULL, NULL) SDDIOS INNER JOIN 
     
[iDBA].[MetaBot].[dm_db_index_operational_stats] MDDIOS
       
ON SDDIOS.[database_id] MDDIOS.[database_id]
         
AND SDDIOS.[object_id] MDDIOS.[object_id]
         
AND SDDIOS.[index_id] MDDIOS.[index_id]
         
AND SDDIOS.[partition_number] MDDIOS.[partition_number]
END
E
LSE
BEGIN
   
--Service restart date < last poll date
   
PRINT 'Lastest service restart occurred on ' 
     
CAST(@last_service_start_date AS VARCHAR(50)) + 
     
' which is after the latest persist date of ' 
     
CAST(@last_data_persist_date AS VARCHAR(50))
   
   
UPDATE MDDIOS
   
SET 
     
MDDIOS.[leaf_insert_count] MDDIOS.[leaf_insert_count]SDDIOS.[leaf_insert_count],
     
MDDIOS.[leaf_delete_count] MDDIOS.[leaf_delete_count]SDDIOS.[leaf_delete_count],
     
MDDIOS.[leaf_update_count] MDDIOS.[leaf_update_count]SDDIOS.[leaf_update_count],
     
MDDIOS.[leaf_ghost_count] MDDIOS.[leaf_ghost_count]SDDIOS.[leaf_ghost_count],
     
MDDIOS.[nonleaf_insert_count] MDDIOS.[nonleaf_insert_count] SDDIOS.[nonleaf_insert_count],
     
MDDIOS.[nonleaf_delete_count] MDDIOS.[nonleaf_delete_count] SDDIOS.[nonleaf_delete_count],
     
MDDIOS.[nonleaf_update_count] MDDIOS.[nonleaf_update_count] SDDIOS.[nonleaf_update_count],
     
MDDIOS.[leaf_allocation_count] MDDIOS.[leaf_allocation_count] SDDIOS.[leaf_allocation_count],
     
MDDIOS.[nonleaf_allocation_count] MDDIOS.[nonleaf_allocation_count]SDDIOS.[nonleaf_allocation_count],
     
MDDIOS.[leaf_page_merge_count] MDDIOS.[leaf_page_merge_count]SDDIOS.[leaf_page_merge_count],
     
MDDIOS.[nonleaf_page_merge_count] MDDIOS.[nonleaf_page_merge_count]SDDIOS.[nonleaf_page_merge_count],
     
MDDIOS.[range_scan_count] MDDIOS.[range_scan_count]SDDIOS.[range_scan_count],
     
MDDIOS.[singleton_lookup_count] MDDIOS.[singleton_lookup_count] SDDIOS.[singleton_lookup_count],
     
MDDIOS.[forwarded_fetch_count] MDDIOS.[forwarded_fetch_count] SDDIOS.[forwarded_fetch_count],
     
MDDIOS.[lob_fetch_in_pages] MDDIOS.[lob_fetch_in_pages] SDDIOS.[lob_fetch_in_pages],
     
MDDIOS.[lob_fetch_in_bytes] MDDIOS.[lob_fetch_in_bytes] SDDIOS.[lob_fetch_in_bytes],
     
MDDIOS.[lob_orphan_create_count] MDDIOS.[lob_orphan_create_count] SDDIOS.[lob_orphan_create_count],
     
MDDIOS.[lob_orphan_insert_count] MDDIOS.[lob_orphan_insert_count] SDDIOS.[lob_orphan_insert_count],
     
MDDIOS.[row_overflow_fetch_in_pages] MDDIOS.[row_overflow_fetch_in_pages] SDDIOS.[row_overflow_fetch_in_pages],
     
MDDIOS.[row_overflow_fetch_in_bytes] MDDIOS.[row_overflow_fetch_in_bytes] SDDIOS.[row_overflow_fetch_in_bytes],
     
MDDIOS.[column_value_push_off_row_count] MDDIOS.[column_value_push_off_row_count] SDDIOS.[column_value_push_off_row_count],
     
MDDIOS.[column_value_pull_in_row_count] MDDIOS.[column_value_pull_in_row_count] SDDIOS.[column_value_pull_in_row_count],
     
MDDIOS.[row_lock_count] MDDIOS.[row_lock_count] SDDIOS.[row_lock_count],
     
MDDIOS.[row_lock_wait_count] MDDIOS.[row_lock_wait_count] SDDIOS.[row_lock_wait_count],
     
MDDIOS.[row_lock_wait_in_ms] MDDIOS.[row_lock_wait_in_ms] SDDIOS.[row_lock_wait_in_ms],
     
MDDIOS.[page_lock_count] MDDIOS.[page_lock_count] SDDIOS.[page_lock_count],
     
MDDIOS.[page_lock_wait_count] MDDIOS.[page_lock_wait_count] SDDIOS.[page_lock_wait_count],
     
MDDIOS.[page_lock_wait_in_ms] MDDIOS.[page_lock_wait_in_ms] SDDIOS.[page_lock_wait_in_ms],
     
MDDIOS.[index_lock_promotion_attempt_count] MDDIOS.[index_lock_promotion_attempt_count] SDDIOS.[index_lock_promotion_attempt_count],
     
MDDIOS.[index_lock_promotion_count] MDDIOS.[index_lock_promotion_count] SDDIOS.[index_lock_promotion_count],
     
MDDIOS.[page_latch_wait_count] MDDIOS.[page_latch_wait_count] SDDIOS.[page_latch_wait_count],
     
MDDIOS.[page_latch_wait_in_ms] MDDIOS.[page_latch_wait_in_ms] SDDIOS.[page_latch_wait_in_ms],
     
MDDIOS.[page_io_latch_wait_count] MDDIOS.[page_io_latch_wait_count] SDDIOS.[page_io_latch_wait_count],
     
MDDIOS.[page_io_latch_wait_in_ms] MDDIOS.[page_io_latch_wait_in_ms] SDDIOS.[page_io_latch_wait_in_ms],
     
MDDIOS.[last_poll_leaf_insert_count] SDDIOS.[leaf_insert_count],
     
MDDIOS.[last_poll_leaf_delete_count] SDDIOS.[leaf_delete_count],
     
MDDIOS.[last_poll_leaf_update_count] SDDIOS.[leaf_update_count],
     
MDDIOS.[last_poll_leaf_ghost_count] SDDIOS.[leaf_ghost_count],
     
MDDIOS.[last_poll_nonleaf_insert_count] SDDIOS.[nonleaf_insert_count],
     
MDDIOS.[last_poll_nonleaf_delete_count] SDDIOS.[nonleaf_delete_count],
     
MDDIOS.[last_poll_nonleaf_update_count] SDDIOS.[nonleaf_update_count],
     
MDDIOS.[last_poll_leaf_allocation_count] SDDIOS.[leaf_allocation_count],
     
MDDIOS.[last_poll_nonleaf_allocation_count] SDDIOS.[nonleaf_allocation_count],
     
MDDIOS.[last_poll_leaf_page_merge_count] SDDIOS.[leaf_page_merge_count],
     
MDDIOS.[last_poll_nonleaf_page_merge_count] SDDIOS.[nonleaf_page_merge_count],
     
MDDIOS.[last_poll_range_scan_count] SDDIOS.[range_scan_count],
     
MDDIOS.[last_poll_singleton_lookup_count] SDDIOS.[singleton_lookup_count],
     
MDDIOS.[last_poll_forwarded_fetch_count] SDDIOS.[forwarded_fetch_count],
     
MDDIOS.[last_poll_lob_fetch_in_pages] SDDIOS.[lob_fetch_in_pages],
     
MDDIOS.[last_poll_lob_fetch_in_bytes] SDDIOS.[lob_fetch_in_bytes],
     
MDDIOS.[last_poll_lob_orphan_create_count] SDDIOS.[lob_orphan_create_count],
     
MDDIOS.[last_poll_lob_orphan_insert_count] SDDIOS.[lob_orphan_insert_count],
     
MDDIOS.[last_poll_row_overflow_fetch_in_pages] SDDIOS.[row_overflow_fetch_in_pages],
     
MDDIOS.[last_poll_row_overflow_fetch_in_bytes] SDDIOS.[row_overflow_fetch_in_bytes],
     
MDDIOS.[last_poll_column_value_push_off_row_count] SDDIOS.[column_value_push_off_row_count],
     
MDDIOS.[last_poll_column_value_pull_in_row_count] SDDIOS.[column_value_pull_in_row_count],
     
MDDIOS.[last_poll_row_lock_count] SDDIOS.[row_lock_count],
     
MDDIOS.[last_poll_row_lock_wait_count] SDDIOS.[row_lock_wait_count],
     
MDDIOS.[last_poll_row_lock_wait_in_ms] SDDIOS.[row_lock_wait_in_ms],
     
MDDIOS.[last_poll_page_lock_count] SDDIOS.[page_lock_count],
     
MDDIOS.[last_poll_page_lock_wait_count] SDDIOS.[page_lock_wait_count],
     
MDDIOS.[last_poll_page_lock_wait_in_ms] SDDIOS.[page_lock_wait_in_ms],
     
MDDIOS.[last_poll_index_lock_promotion_attempt_count] SDDIOS.[index_lock_promotion_attempt_count],
     
MDDIOS.[last_poll_index_lock_promotion_count] SDDIOS.[index_lock_promotion_count],
     
MDDIOS.[last_poll_page_latch_wait_count] SDDIOS.[page_latch_wait_count],
     
MDDIOS.[last_poll_page_latch_wait_in_ms] SDDIOS.[page_latch_wait_in_ms],
     
MDDIOS.[last_poll_page_io_latch_wait_count] SDDIOS.[page_io_latch_wait_count],
     
MDDIOS.[last_poll_page_io_latch_wait_in_ms] SDDIOS.[page_io_latch_wait_in_ms],      
     
MDDIOS.date_stamp GETDATE()
   
FROM [sys].[dm_db_index_operational_stats] (NULL, NULL, NULL, NULL)  SDDIOS INNER JOIN 
     
[iDBA].[MetaBot].[dm_db_index_operational_stats] MDDIOS
       
ON SDDIOS.[database_id] MDDIOS.[database_id]
         
AND SDDIOS.[object_id] MDDIOS.[object_id]
         
AND SDDIOS.[index_id] MDDIOS.[index_id]
         
AND SDDIOS.[partition_number] MDDIOS.[partition_number]
 
END

--Take care of new records next
INSERT INTO [iDBA].[MetaBot].[dm_db_index_operational_stats]
 
(
 
[database_id] ,[object_id][index_id][partition_number][leaf_insert_count],
 
[leaf_delete_count][leaf_update_count][leaf_ghost_count][nonleaf_insert_count],
 
[nonleaf_delete_count][nonleaf_update_count][leaf_allocation_count],
 
[nonleaf_allocation_count][leaf_page_merge_count][nonleaf_page_merge_count],
 
[range_scan_count][singleton_lookup_count][forwarded_fetch_count],
 
[lob_fetch_in_pages][lob_fetch_in_bytes][lob_orphan_create_count],
 
[lob_orphan_insert_count][row_overflow_fetch_in_pages][row_overflow_fetch_in_bytes],
 
[column_value_push_off_row_count][column_value_pull_in_row_count][row_lock_count],
 
[row_lock_wait_count][row_lock_wait_in_ms][page_lock_count][page_lock_wait_count],
 
[page_lock_wait_in_ms][index_lock_promotion_attempt_count][index_lock_promotion_count],
 
[page_latch_wait_count][page_latch_wait_in_ms][page_io_latch_wait_count],
 
[page_io_latch_wait_in_ms][last_poll_leaf_insert_count][last_poll_leaf_delete_count],
 
[last_poll_leaf_update_count][last_poll_leaf_ghost_count][last_poll_nonleaf_insert_count],
 
[last_poll_nonleaf_delete_count][last_poll_nonleaf_update_count][last_poll_leaf_allocation_count],
 
[last_poll_nonleaf_allocation_count][last_poll_leaf_page_merge_count],
 
[last_poll_nonleaf_page_merge_count][last_poll_range_scan_count],
 
[last_poll_singleton_lookup_count][last_poll_forwarded_fetch_count][last_poll_lob_fetch_in_pages],
 
[last_poll_lob_fetch_in_bytes][last_poll_lob_orphan_create_count],
 
[last_poll_lob_orphan_insert_count][last_poll_row_overflow_fetch_in_pages],
 
[last_poll_row_overflow_fetch_in_bytes][last_poll_column_value_push_off_row_count],
 
[last_poll_column_value_pull_in_row_count][last_poll_row_lock_count][last_poll_row_lock_wait_count],
 
[last_poll_row_lock_wait_in_ms][last_poll_page_lock_count][last_poll_page_lock_wait_count],
 
[last_poll_page_lock_wait_in_ms][last_poll_index_lock_promotion_attempt_count],
 
[last_poll_index_lock_promotion_count][last_poll_page_latch_wait_count],
 
[last_poll_page_latch_wait_in_ms][last_poll_page_io_latch_wait_count],
 
[last_poll_page_io_latch_wait_in_ms][date_stamp]
 
)
SELECT SDDIOS.[database_id] ,SDDIOS.[object_id]SDDIOS.[index_id]SDDIOS.[partition_number]SDDIOS.[leaf_insert_count],
 
SDDIOS.[leaf_delete_count]SDDIOS.[leaf_update_count]SDDIOS.[leaf_ghost_count]SDDIOS.[nonleaf_insert_count],
 
SDDIOS.[nonleaf_delete_count]SDDIOS.[nonleaf_update_count]SDDIOS.[leaf_allocation_count],
 
SDDIOS.[nonleaf_allocation_count]SDDIOS.[leaf_page_merge_count]SDDIOS.[nonleaf_page_merge_count],
 
SDDIOS.[range_scan_count]SDDIOS.[singleton_lookup_count]SDDIOS.[forwarded_fetch_count],
 
SDDIOS.[lob_fetch_in_pages]SDDIOS.[lob_fetch_in_bytes]SDDIOS.[lob_orphan_create_count],
 
SDDIOS.[lob_orphan_insert_count]SDDIOS.[row_overflow_fetch_in_pages]SDDIOS.[row_overflow_fetch_in_bytes],
 
SDDIOS.[column_value_push_off_row_count]SDDIOS.[column_value_pull_in_row_count]SDDIOS.[row_lock_count],
 
SDDIOS.[row_lock_wait_count]SDDIOS.[row_lock_wait_in_ms]SDDIOS.[page_lock_count]SDDIOS.[page_lock_wait_count],
 
SDDIOS.[page_lock_wait_in_ms]SDDIOS.[index_lock_promotion_attempt_count]SDDIOS.[index_lock_promotion_count],
 
SDDIOS.[page_latch_wait_count]SDDIOS.[page_latch_wait_in_ms]SDDIOS.[page_io_latch_wait_count],
 
SDDIOS.[page_io_latch_wait_in_ms]SDDIOS.[leaf_insert_count],
 
SDDIOS.[leaf_delete_count]SDDIOS.[leaf_update_count]SDDIOS.[leaf_ghost_count]SDDIOS.[nonleaf_insert_count],
 
SDDIOS.[nonleaf_delete_count]SDDIOS.[nonleaf_update_count]SDDIOS.[leaf_allocation_count],
 
SDDIOS.[nonleaf_allocation_count]SDDIOS.[leaf_page_merge_count]SDDIOS.[nonleaf_page_merge_count],
 
SDDIOS.[range_scan_count]SDDIOS.[singleton_lookup_count]SDDIOS.[forwarded_fetch_count],
 
SDDIOS.[lob_fetch_in_pages]SDDIOS.[lob_fetch_in_bytes]SDDIOS.[lob_orphan_create_count],
 
SDDIOS.[lob_orphan_insert_count]SDDIOS.[row_overflow_fetch_in_pages]SDDIOS.[row_overflow_fetch_in_bytes],
 
SDDIOS.[column_value_push_off_row_count]SDDIOS.[column_value_pull_in_row_count]SDDIOS.[row_lock_count],
 
SDDIOS.[row_lock_wait_count]SDDIOS.[row_lock_wait_in_ms]SDDIOS.[page_lock_count]SDDIOS.[page_lock_wait_count],
 
SDDIOS.[page_lock_wait_in_ms]SDDIOS.[index_lock_promotion_attempt_count]SDDIOS.[index_lock_promotion_count],
 
SDDIOS.[page_latch_wait_count]SDDIOS.[page_latch_wait_in_ms]SDDIOS.[page_io_latch_wait_count],
 
SDDIOS.[page_io_latch_wait_in_ms]GETDATE()
FROM sys.[dm_db_index_operational_stats](NULL, NULL, NULL, NULL) SDDIOS 
 
LEFT JOIN [iDBA].[MetaBot].[dm_db_index_operational_stats] MDDIOS
   
ON SDDIOS.[database_id] MDDIOS.[database_id]
   
AND SDDIOS.[object_id] MDDIOS.[object_id]
   
AND SDDIOS.[index_id] MDDIOS.[index_id]
   
AND SDDIOS.[partition_number] MDDIOS.[partition_number]
WHERE MDDIOS.[database_id] IS NULL 
 AND 
MDDIOS.[object_id] IS NULL
 AND 
MDDIOS.[index_id] IS NULL
 AND 
MDDIOS.[partition_number] IS NULL

The code is comprised of a few different logical sections:

  • Variable declarations and value assignments
  • Update of existing values with new usage counts.  The two variables in the stored procedure determine which UPDATE script runs in the confines of the stored procedure:
    • If a restart of services has not occurred since the last collection of metadata the formula for updating the relevant column with new data is: MDDIOS.COL + (DMF.COL - MDDIOS.LAST_POL_COL) Simply put, it's the currently stored value for the column (MDDIOS.COL) + the difference between the current value of the corresponding column in the DMF (DMF.COL) and the last value persisted from the DMF (MDDIOS.LAST_POL_COL).
    • If a restart of services has occurred since the last data collection the formula used will be: MDDIOS.COL + DMF.COL, since the values stored in the Dynamic Management Function will have reset upon service restart.
  • Insertion of new rows associated with indexes that have, up to this point, not been used in any read or write processes.

When a unique record is first encountered it is inserted into the persisted table.  A unique record is based upon the combination key of database_id, object_id, index_id, and partition_number in the case of the sys.dm_db_index_operational_stats DMV.  If this combination of identifiers already exists in the persisted table, the stored procedure relies upon the underlying determination of whether or not a system restart has been encountered since the last time the metadata was persisted.  If it has not, then the usage columns are incremented by adding the existing value of that field to the difference between the new value from the DMV and the last value that was read from the DMV when the last run of the stored procedure executed.  If a restart did occur, then the counters are all reset to 0.  This means that the formula previously outlined would result in incorrect numbers being persisted.  Instead you must take the sum of the existing column value and the corresponding value from the DMV as the new value to be persisted.  In all cases the calculation for the last_poll column value will remain unchanged as will the code associated with the INSERT of new index usage rows.  Just as with sys.dm_db_index_usage_stats, you may notice that we do risk the loss of counter data.  The only way to ensure that all usage data is persisted would be if this stored procedure would run prior to any service restart - not a viable option.  However, unlike user data, we can live with data loss in this case.  We are using this metadata for analyzing locking, blocking, and detailed use of the indexes over a period of time for the purposes of tuning our indexing structure of the databases on the instance.  So long as we record significant data over the lifetime of the instance we have no need to capture all usage values.

Just as with sys.dm_db_index_usage_stats, the final step is to create a SQL Server Agent Job for the execution of this stored procedure.  I'll leave that up to you.  In our environment I run this process six times daily.  It's very lightweight and I've not found it to interfere with normal user operations or backup cycles.  I do not run it during the timeframe of scheduled index maintenance, but that is by choice on my part.  I encourage you to test with schedules that fit your individual needs.

In the next tip of the series we will create a similar process for the missing index Dynamic Management Views and Functions; these require different handling since their structure is so much different than the two DMOs we've studied so far in this series.

Next Steps



Last Updated: 2009-06-05


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Sunday, March 10, 2013 - 8:24:05 AM - Greg Robidoux Back To Top

@Ackers - thanks these have been fixed.

 


Sunday, March 10, 2013 - 6:27:41 AM - Ackers Back To Top

Very Good articles...

In your proc --> CREATE PROCEDURE MetaBot.usp_persist_dm_db_index_operational_stats AS 

 

found two typos

1. __    --> _

2. loob_fetch_in_bytes --> lob_fetch_in_bytes

 

 

 


Tuesday, December 21, 2010 - 3:59:44 PM - TheAmilingDBA Back To Top

Tim,

Have you considered placing the data in Month stamp rows rather than accumlating the values after a restart?

We know that the 3rd staturyda is maintenance, which mena s a restart. So, we collect the same or simliar data (not so detailed) every AM on the third Saturday of the Month.

This gives us a view from month to month. And if we need total, we sum ourselves.

Great post, thanks for the info. it is good to see some ideas We have implementing at work or used by profressional like you. Helps validate what we are doing.

Thomas



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools