Uniqueifier details in SQL Server

This is a follow up post for <https://blogs.msdn.microsoft.com/psssql/2018/02/16/uniqueifier-cons…ns-and-error-666/>, for the ones that want to delve into the subject.

First a quick background on uniqueifiers...

A uniqueifier (or uniquifier as reported by SQL Server internal tools) is a 4-byte value used to make each key unique in a clustered index that allow duplicate key values. This uniqueness is required because each clustering key must point to exactly one record, without the uniqueifier a non-unique clustering key could relate to two or more records.

Since those values are not public exposed, we need to rely on undocumented/unsupported commands to check the details of it (for instance, DBCC PAGE - https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/06/10/how-to-use-dbcc-page/)

You can use the script 01 to check uniqueifier values for different keys, adjusting it to your environment. Please note that you need a way to identify the most recent records inserted, adding a tiebreaker to get the latest uniqueifier used.

Script 01 – Checking uniqueifier values

 -- CREATE DATABASE UNIQUIFIER

USE UNIQUIFIER
GO

IF OBJECT_ID('dbo.UniqTable') IS NOT NULL
  DROP TABLE dbo.UniqTable
go

CREATE TABLE dbo.UniqTable
(SystemCode INT NOT NULL DEFAULT (1)
, SampleEntry CHAR(2000) NOT NULL
, RecordDate DATETIME DEFAULT (SYSDATETIME()))
GO

CREATE CLUSTERED INDEX idxSystemCode
ON dbo.UniqTable(SystemCode)
GO

-- INSERT Rows
INSERT INTO dbo.UniqTable (SystemCode, SampleEntry)
SELECT TOP 10 1, mc.type
FROM sys.dm_os_memory_clerks AS MC
WAITFOR DELAY '00:00:00.100'

INSERT INTO dbo.UniqTable (SystemCode, SampleEntry)
SELECT TOP 10 2, mc.type
FROM sys.dm_os_memory_clerks AS MC
WAITFOR DELAY '00:00:00.100'
GO 5

INSERT INTO dbo.UniqTable (SystemCode, SampleEntry)
SELECT TOP 10 1, mc.type
FROM sys.dm_os_memory_clerks AS MC
GO

-- Checking pages with latest records
SELECT *, sys.fn_physlocformatter(%%PHYSLOC%%)
FROM dbo.UniqTable AS UT
ORDER BY UT.RecordDate, UT.SystemCode
GO

/*
Last entry for each SystemCode
1      MEMORYCLERK_SQLSTORENG     2018-01-29 12:01:40.700    (1:525:2)
2      MEMORYCLERK_SQLSTORENG     2018-01-29 12:01:40.583    (1:522:2)
*/

-- Checking uniqueifiers on each page:
DBCC TRACEON(3604)
DBCC PAGE ('UNIQUIFIER', 1, 525, 3)
GO

/*
Slot 2 Column 0 Offset 0x7e7 Length 4 Length (physical) 4
UNIQUIFIER = 59
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
SystemCode = 1
*/

DBCC PAGE ('UNIQUIFIER', 1, 522, 3)
/*
Slot 2 Column 0 Offset 0x7e7 Length 4 Length (physical) 4
UNIQUIFIER = 49
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
SystemCode = 2
*/

If some records are deleted from the table, is possible to notice gaps in the UNIQUEIFIER sequence, and a REBUILD ALL ONLINE will reset those uniqueifiers (see script 02).

It is also important to point out one specific aspect that Paul Randal blogged some time ago, related to uniqueifiers changes from SQL Server 2000 to 2005: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2007/06/07/what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed/

"Aha! This is different from SQL Server 2000. SQL Server 2005 will RE-USE the old uniqueifier values so the cluster keys don’t change. This means that non-clustered indexes are NOT rebuilt in this case – that’s very cool!"

As of February 2018, the design goal for the storage engine is to not reset uniqueifiers during REBUILDs. As such, rebuild of the index ideally would not reset uniquifiers and issue would continue to occur while inserting new data with key value for which the uniquifiers were exhausted. But current engine behavior is different for one specific case, if you use the statement ALTER INDEX ALL ON <TABLE> REBUILD WITH (ONLINE = ON) , it will reset the uniqueifiers (across all version starting SQL Server 2005 to SQL Server 2017).

Important: This is something that is not documented and can change in future versions, so our recommendation is that you should review table design to avoid relying on it.

Script 02 – Resetting uniqueifier values

 -- Adding some gaps to the sequence:
;WITH C AS (
SELECT *, ROW_NUMBER()  OVER(PARTITION BY SystemCode ORDER BY RecordDate ASC) AS Ord FROM dbo.UniqTable
)
DELETE FROM C
WHERE (C.Ord % 2) = 1
GO

-- Checking pages with latest records
SELECT *, sys.fn_physlocformatter(%%PHYSLOC%%)
FROM dbo.UniqTable AS UT
ORDER BY UT.RecordDate, UT.SystemCode
GO

-- Checking uniqueifiers on each page:
DBCC PAGE ('UNIQUIFIER', 1, 537, 3)
/*
Slot 0 Column 0 Offset 0x20b Length 4 Length (physical) 4
UNIQUIFIER = 53
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
SystemCode = 1

... next entry ...

Slot 1 Column 0 Offset 0x20b Length 4 Length (physical) 4
UNIQUIFIER = 55
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
SystemCode = 1

... next entry ...

Slot 2 Column 0 Offset 0x20b Length 4 Length (physical) 4
UNIQUIFIER = 57
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
SystemCode = 1
*/

DBCC PAGE ('UNIQUIFIER', 1, 536, 3)

/*
Slot 0 Column 0 Offset 0x20b Length 4 Length (physical) 4
UNIQUIFIER = 35
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
SystemCode = 2

... next entry ...

Slot 1 Column 0 Offset 0x20b Length 4 Length (physical) 4
UNIQUIFIER = 37
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
SystemCode = 2

... next entry ...

Slot 2 Column 0 Offset 0x20b Length 4 Length (physical) 4
UNIQUIFIER = 39
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
SystemCode = 2
*/

-- TRIGGER uniquifier reset
ALTER INDEX ALL ON dbo.UniqTable REBUILD WITH(ONLINE = ON)
GO

-- Checking pages with latest records
SELECT *, sys.fn_physlocformatter(%%PHYSLOC%%)
FROM dbo.UniqTable AS UT
ORDER BY UT.RecordDate, UT.SystemCode
GO

/*
Last entry for each SystemCode
1      MEMORYCLERK_SQLSTORENG     2018-01-29 12:40:50.357    (1:554:14)
2      MEMORYCLERK_SQLSTORENG     2018-01-29 12:40:50.243    (1:556:9)
*/

-- Checking uniqueifiers on each page:
DBCC PAGE ('UNIQUIFIER', 1, 554, 3)
/*
-- Now maximum uniquifier value is:
Slot 14 Column 0 Offset 0x20b Length 4 Length (physical) 4
UNIQUIFIER = 29
Slot 14 Column 1 Offset 0x4 Length 4 Length (physical) 4
SystemCode = 1
*/

DBCC PAGE ('UNIQUIFIER', 1, 556, 3)
/*
-- Now maximum uniquifier value is:
Slot 9 Column 0 Offset 0x20b Length 4 Length (physical) 4
UNIQUIFIER = 24
Slot 9 Column 1 Offset 0x4 Length 4 Length (physical) 4
SystemCode = 2
*/

To help identifying table candidates that are using uniqueifiers (heavily or not), you can leverage the following query:

 SELECT OBJECT_NAME(I.object_id) AS ObjectName
, I.object_id
, I.name AS IndexName
, I.is_unique
, C.name AS ColumnName
, T.name AS TypeName
, C.max_length
, C.precision
, C.scale
, P.partition_id
, P.partition_number
, P.rows
FROM sys.indexes AS I
INNER JOIN sys.index_columns AS IC
ON I.index_id = IC.index_id
AND I.object_id = IC.object_id
INNER JOIN sys.columns AS C
ON IC.column_id = C.column_id
AND IC.object_id = C.object_id
INNER JOIN sys.types AS T
ON C.system_type_id = T.system_type_id
INNER JOIN sys.partitions AS P
ON P.object_id = I.object_id
AND P.index_id = I.index_id
WHERE I.is_unique = 0 AND I.index_id = 1
ORDER BY I.object_id, C.column_id DESC

For a specific database it will list the non-unique cluster indexes ordered by number of rows descending, with columns and its types. So even not showing the number of repeated rows for each cluster value, it is a good starting point to check tables starting from the ones with more records, going thru all the objects with a similar design.

Without resetting the uniqueifier, sometimes you can reach its limit in a sliding window scenario, with fewer rows than the value limit of 2,147,483,648. For example: a table keep records for the past 10 days, with daily insert of 20 million records and deleting oldest 20 million, which will cause the uniqueifier to be constantly growing and reach its limit with only 200 million rows in the table (sample in script 03 for a general idea).

Script 03 – Sliding window scenario

 USE UNIQUIFIER
GO

IF OBJECT_ID('dbo.UniqSliding') IS NOT NULL
  DROP TABLE dbo.UniqSliding
go

CREATE TABLE dbo.UniqSliding
(ID INT IDENTITY(1,1) NOT NULL
, IDNonUnique SMALLINT NOT NULL   DEFAULT (1)
, RecordDate DATETIME DEFAULT (SYSDATETIME()))
GO

CREATE CLUSTERED INDEX idxCL_NonUnique
ON dbo.UniqSliding (IDNonUnique)
GO

-- Use Itzik Ben-Gan GetNums function
-- Reference: https://tsql.solidq.com/SourceCodes/GetNums.txt
IF OBJECT_ID('dbo.GetNums') IS NOT NULL
  DROP FUNCTION dbo.GetNums;
GO

CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO

INSERT INTO dbo.UniqSliding (IDNonUnique)
SELECT 1
FROM dbo.GetNums(20000);
go

INSERT INTO dbo.UniqSliding (IDNonUnique)
SELECT 1
FROM dbo.GetNums(20000);
DELETE TOP (20000)
FROM dbo.UniqSliding
GO 10

select count(*) from dbo.UniqSliding;
-- 20000 rows

-- Checking pages with latest records
SELECT TOP 10 *, sys.fn_physlocformatter(%%PHYSLOC%%)
FROM dbo.UniqSliding
ORDER BY ID DESC
GO

-- 220000     1      2018-01-29 13:51:40.623    (1:6916:237)

DBCC TRACEON (3604)
DBCC PAGE ('UNIQUIFIER', 1, 6916, 3)
-- Current UNIQUEIFIER value = 219999 for 20000 rows

/*
Slot 237 Column 0 Offset 0x19 Length 4 Length (physical) 4
UNIQUIFIER = 219999
Slot 237 Column 2 Offset 0x4 Length 2 Length (physical) 2
IDNonUnique = 1
Slot 237 Column 1 Offset 0x6 Length 4 Length (physical) 4
ID = 220000
*/

The goal for this post is to give insight on how uniqueifier works and allow you to manually check for potential issues in your environment, avoiding error 666.

Hope you find this article interesting and helpful.

Best Regards,

Luciano Caixeta Moreira - {Luti}

luticm79@hotmail.com https://www.linkedin.com/in/luticm www.twitter.com/luticm