User_Updates value from sys.dm_db_index_usage_stats can be misleading

I was doing some investigating the other day and trying to find which tables in our environment were the most written to. This was because my buddies in the IT department had gotten a bunch of new storage that was optimized for a write-intensive workload. To identify which tables had the most writes, I used the following query:

DECLARE @dbid INT = DB_ID();
SELECT TableName = OBJECT_NAME(s.object_id),
       Reads = SUM(user_seeks + user_scans + user_lookups),
       Writes = SUM(user_updates)
FROM sys.dm_db_index_usage_stats AS s
    INNER JOIN sys.indexes AS i
        ON s.object_id = i.object_id
           AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
      AND s.database_id = @dbid
GROUP BY OBJECT_NAME(s.object_id)
ORDER BY Writes DESC;
GO

This pulls both reads and writes from the sys.dm_db_index_usage_stats dynamic management view. A read is defined as either a seek, scan, or lookup and a write is defined as an update. All seemed good until I noticed something strange. One of the top written to tables was, based on our naming convention, a lookup table. That seemed odd. A lookup table should have lots of reads, but only few writes. The query above showed my lookup table had almost twice as many writes as reads!

I dug around a bit and found two stored procedures that referenced that particular table. I checked them out, but nothing seemed out of the ordinary to me, so I dug a little deeper and discovered something strange: the user_updates value of sys.dm_db_index_usage_stats can get incremented even when there is no actual update to the table!!

Let’s check it out. First, create a table. I’m going to go with a lookup table, as that’s what I was dealing with. It’s a simple table – one identity column and one varchar column for a machine name.

CREATE TABLE [dbo].[Lkp_Machine]
(
[IdMachine] [INT] IDENTITY(1, 1) NOT NULL,
[MachineName] [VARCHAR](50) NULL,
CONSTRAINT [PK__Lkp_Machine]
PRIMARY KEY CLUSTERED ([IdMachine] ASC)
);

Now let’s insert a couple rows of data to work with:

INSERT INTO Lkp_Machine
(
MachineName
)
VALUES
('Machine01'),
('Machine02'),
('Machine03');

Finally, let’s see what our DMV reports: (I’ve modified it slightly to only show our table.)

DECLARE @dbid INT = DB_ID();
SELECT TableName = OBJECT_NAME(s.object_id),
       Reads = SUM(user_seeks + user_scans + user_lookups),
       Writes = SUM(user_updates)
FROM sys.dm_db_index_usage_stats AS s
    INNER JOIN sys.indexes AS i
        ON s.object_id = i.object_id
           AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
      AND s.database_id = @dbid
	  AND OBJECT_NAME(s.object_id)='Lkp_Machine'
GROUP BY OBJECT_NAME(s.object_id)
ORDER BY Writes DESC;
GO

Here’s the output:

OK. We inserted 3 rows, but got a write count of 1. That’s normal. Books Online says of the user_updates field: “Number of updates by user queries. This includes Insert, Delete, and Updates representing number of operations done not the actual rows affected. For example, if you delete 1000 rows in one statement, this count increments by 1”

Doesn’t really explain my situation though. It did, however, give me an idea. I decided to do another test using this code:

DECLARE @TestVariable CHAR(1);
UPDATE Lkp_Machine
SET @TestVariable = 'a';

This query does not actually update the table. It simply sets a variable. Now when we run the write count query again, what do we get?

Look at that! We got one read and one more write! The user_updates count was incremented even though there was no actual update to the table.

It would seem this DMV increments the update count whenever the table is referenced in an update command.

Why would someone do this? I admit, my test query is somewhat silly. However, you may very well encounter this situation disguised as a MERGE statement. This was where I found it.

Our developers wanted to check the lookup table for the existence of a certain machine name. If that name did not exist, they wanted to insert it into the table and get the id that was created for it, but if it did exist, they only needed to get the id value of that machine. Here is their code:

MERGE Lkp_Machine AS A
USING
(SELECT @machineName AS machineName) AS B
ON B.machineName = A.MachineName
WHEN MATCHED THEN
    UPDATE SET @idMachine = A.idMachine
WHEN NOT MATCHED THEN
    INSERT
    (
        machineName
    )
    VALUES
    (B.machineName)
OUTPUT Inserted.idMachine
INTO @inserts;

The syntax of the MERGE statement requires that the command after the WHEN MATCHED clause be either an UPDATE or a DELETE. We don’t really need to update the table here – we just want to set a variable – but the syntax forces the UPDATE command.

Granted, this probably isn’t the best way to perform this action. However, it is how this particular developer coded it and it’s what I, as the DBA, have to live with. Just be aware that some of your tables might not actually be getting updated as often as this DMV implies!

 

One thought on “User_Updates value from sys.dm_db_index_usage_stats can be misleading

Leave a Reply

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

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.