Sql-server – Index usage and update statistics do not correspond

index-tuningsql server

In order to discover unused and wrong indexes in sql server I went through the following article:
https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/

It gives two queries that deliver index information:

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
   I.[NAME] AS [INDEX NAME], 
   A.LEAF_INSERT_COUNT, 
   A.LEAF_UPDATE_COUNT, 
   A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A 
   INNER JOIN SYS.INDEXES AS I 
     ON I.[OBJECT_ID] = A.[OBJECT_ID] 
        AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
   I.[NAME] AS [INDEX NAME], 
   USER_SEEKS, 
   USER_SCANS, 
   USER_LOOKUPS, 
   USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
   INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] 
AND I.INDEX_ID = S.INDEX_ID 
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
   AND S.database_id = DB_ID()

Trying the queries my suspect was confirmed that several existing indexes are simply useless.

The article mentions that the results of query #1 shound correspond with results of query #2:

"The updates refers to how many times the index was updated due to data changes which should correspond to the first query above."

So LEAVE(insert/update/delete) Counts sould be = USER_Updates.

In my case they do not correspond at all. e.g:

A.LEAF_INSERT_COUNT=26219
A.LEAF_UPDATE_COUNT=0
A.LEAF_DELETE_COUNT=0

vs.

USER_UPDATES =1.550.063

So my question is, what could possibly cause my data to dis-correspond that much?

Best Answer

About: sys.dm_db_index_operational_stats

The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats (Transact-SQL).

The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available.

See: sys.dm_db_index_operational_stats (Transact-SQL)

On the other hand, sys.dm_db_index_usage_stats, which is mentioned above, is only reset when the service is restarted:

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started.

Related Solutions

Sql-server – Filtered index statistics refresh threshold

You've got two questions in here:

Is the above data sufficient information to base an index rebuild decision upon or would I need to have additional metrics involved?

One missing link is the size of the index. If you're talking about an object with less than, say, 1000 pages, then index rebuilds aren't all that critical.

Another missing link would be the churn of the index. Typically I see filtered indexes used when they're a very, very small subset of the entire table, and the subset changes fast. Guessing by the name of your filtering field (OfferStatus = 0), it sounds like you're indexing just the rows where you haven't made an offer yet, and then you're going to immediately turn around and make an offer. In situations like that, the data's changing so fast that index rebuilds usually don't make sense.

Or, for filtered indexes do we even care about fragmentation and should just explicitly update statistics at X interval?

SQL Server updates stats on objects when ~20% of the data changes, but filtered indexes & stats are a special case. They're also updated when 20% of the data changes - but it's 20% of the base table, not 20% of the filtered subset. Because of that, you probably want to manually update stats on them periodically. I love Ola Hallengren's maintenance scripts for this - the index maintenance stored proc has a parameter for updating statistics, and another parameter for choosing what level of sampling you want, and another parameter for choosing whether to update stats on all objects or only the ones with changed rows. It's fantastic.

Sql-server – Referencing database programmatically via T-SQL

Dynamic SQL comes in handy for these types of administrative tasks. Here is a snippet from a stored procedure I wrote that not only gets the defragmentation levels, but also generates the code to do the defragmentation:

select @SQL = 
'
select getdate(),
       ''' + @@ServerName + ''',
       ''' + @DatabaseName + ''',
       so.Name,
       si.Name,
       db_id(''' + @DatabaseName + '''),
       ips.object_id,
       ips.index_id,
       ips.index_type_desc,
       ips.alloc_unit_type_desc,
       ips.index_depth,
       ips.avg_fragmentation_in_percent,
       ips.fragment_count,
       avg_fragment_size_in_pages,
       ips.page_count,
       ips.record_count,
       case
         when ips.index_id = 0 then ''alter table [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
         else ''alter index '' + si.name + '' on [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
       end
  from sys.dm_db_index_physical_stats(db_id(''' + @DatabaseName + '''),null,null,null, ''' + @SampleMode + ''') ips
  join [' + @DatabaseName + '].sys.objects so  on so.object_id = ips.object_id
  join [' + @DatabaseName + '].sys.schemas ss  on ss.schema_id = so.schema_id
  join [' + @DatabaseName + '].sys.indexes si  on si.object_id = ips.object_id
                      and si.index_id  = ips.index_id
order by so.Name, ips.index_id
'

exec (@SQL)

Best Answer

Related Solutions

Sql-server – Filtered index statistics refresh threshold

Sql-server – Referencing database programmatically via T-SQL

Related Question