Last week I was speaking at SQLDay 2019 conference in Wroclaw, Poland. My session was about things you should focus on during work with Legacy Databases. One of the topics I discussed was concerning the database usage statistics collection and aggregation (mainly indexes and stored procedures).
After the session, one of the attendees came to me and ask me if I know that actions causing check of Foreign Keys and that use indexes underneath, don’t update index usage stats DMV. I was very surprised because, in my opinion, such behavior would be a huge SQL Server defect. So I decided to check it out…
Index usage stats
SQL Server provides use very useful dynamic management view that describes database index usage statistics: sys.dm_db_index_usage_stats
. It returns counts of different types of index operations (seek, scan, lookup, and update) that were performed since the last SQL Server restart. We will use it to perform our test.
Test case
To perform our test we will create a new database with two tables. One of them will have a Foreign Key constraint referencing another one. It could be also only one table, but in my opinion, an example with two tables is simpler.
CREATE DATABASE [CheckIndexUsage]; GO USE [CheckIndexUsage]; GO CREATE TABLE dbo.Parent( [ParentId] int IDENTITY NOT NULL, [Name] varchar(50) NOT NULL, CONSTRAINT pk_Parent PRIMARY KEY CLUSTERED (ParentId) ); CREATE TABLE dbo.Child( [ChildId] int IDENTITY NOT NULL, [ParentId] int NOT NULL, [Name] varchar(50) NOT NULL, CONSTRAINT pk_Child PRIMARY KEY CLUSTERED (ChildId), CONSTRAINT fk_ParentId FOREIGN KEY (ParentId) REFERENCES dbo.Parent (ParentId) );
As a second step let’s add the first row to the dbo.Parent
table and check current index usage statistics for this database.
INSERT INTO dbo.Parent ([Name]) VALUES ('Ned Stark'); SELECT OBJECT_NAME(ius.object_id) AS [TableName], i.[Name] AS [IndexName], ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates FROM sys.dm_db_index_usage_stats ius JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id WHERE ius.database_id = DB_ID();
We can see that only one Update operation was executed on the pk_Parent
index. Good, this is something that I expected. Now, as a second step lets add a new row to the dbo.Child
table. This will trigger Foreign Key check and we will check if the index is used, and if it is reflected in index usage stats DMV.
INSERT INTO dbo.Child ([ParentId],[Name]) VALUES (1, 'Arya Stark');
In the Query Execution Plan we see Clustered Index Seek on the pk_Parent
index.
Let’s check if it is reflected in our DMV.
SELECT OBJECT_NAME(ius.object_id) AS [TableName], i.[Name] AS [IndexName], ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates FROM sys.dm_db_index_usage_stats ius JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id WHERE ius.database_id = DB_ID();
Hurray! It’s here. To be 100% sure that it always works let’s make another insert. This one will fail now.
INSERT INTO dbo.Child ([ParentId],[Name]) VALUES (2, 'Jon Snow');
Msg 547, Level 16, State 0, Line 44 The INSERT statement conflicted with the FOREIGN KEY constraint "fk_ParentId". The conflict occurred in database "CheckIndexUsage", table "dbo.Parent", column 'ParentId'. The statement has been terminated.
How our statistics look?
SELECT OBJECT_NAME(ius.object_id) AS [TableName], i.[Name] AS [IndexName], ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates FROM sys.dm_db_index_usage_stats ius JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id WHERE ius.database_id = DB_ID();
Everything is good.
To clean up after our test, execute this:
USE [master]; GO DROP DATABASE [CheckIndexUsage]; GO
Summary
As you can see, it turned out that the case described by this attendee wasn’t true. The sys.dm_db_index_usage_stats
DMV is updated even when indexes are used for checking Foreign Key constraints integrity. I’ve tested it on SQL Server 2014, 2016, and 2017.
-Marek
Share it: