Foreign Keys and Index Usage Stats

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.

Execution Plan

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();

Index2

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();

Index3

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:
FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail

One thought on “Foreign Keys and Index Usage Stats”

Leave a Reply

Your email address will not be published. Required fields are marked *

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close