All we need is an easy explanation of the problem, so here it is.
I recently asked a question about the best indexing strategy for a table that must record events, in order, against a GUID. The GUID is a nuisance but it’s unavoidable because it’s an FK to elsewhere.
In terms of the queries I want to do they are essentially of the form SELECT TOP N SomeColumn FROM t WHERE GuidColumn = 'someguid' ORDER BY AutoIncInt DESC
I’ve done some experimentation and a good tradeoff approach seems to be:
- Create the
AutoIncInt
column on the table as the unique clustering key - Create a nonunique nonclustered index on
GuidColumn
, andINCLUDE SomeColumn
At first I had the nonclustering index on SomeGuid ASC, AutoIncInt DESC
, thinking it would help the ORDER BY AutoIncInt DESC
part of the query but the plan remained the same when I removed it – no sort operation was needed and the query just hit the NC index, did a top and a select; it seems that SQLServer is somehow smart enough to know, even when AutoIncInt is not cited in the nonclustering index at all, there’s a way to easily get the data out in order / easily calculate which last N rows are needed for the TOP
How does this mechanism work in practice? Does the nonclustering index have an implicit order thanks to referring to the clustering index, such that SQLS can quickly and easily deduce which N index nodes I want the SomeColumn from?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
Each leaf node row in a SQL Server b-tree index uniquely identifies a single row in the table by a unique key. In a non-clustered index, this unique key consists of the declared key columns plus the row locator. The row locator is the clustered index key (including uniqueifier for when the CI key value is not unique). With a heap (no clustered index on the table), the row locator is the physical location of the row (file,page,slot).
The DBCC PAGE
output of this demo script shows both GuidColumn
and AutoIncInt
as index keys even though AutoIncInt
was not specified:.
DROP TABLE IF EXISTS dbo.t;
CREATE TABLE dbo.t (
AutoIncInt int IDENTITY NOT NULL CONSTRAINT pk_t PRIMARY KEY CLUSTERED
, GuidColumn uniqueidentifier NOT NULL
, SomeColumn char(5000) NOT NULL
)
CREATE INDEX idx ON t(GuidColumn) INCLUDE (SomeColumn);
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','3')
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','1')
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','2')
DECLARE @dbid int, @fileid int, @pageid int;
SELECT TOP (1)
@dbid = DB_ID()
, @fileid = allocated_page_file_id
, @pageid = allocated_page_page_id
FROM sys.dm_db_database_page_allocations( DB_ID(), OBJECT_ID(N'dbo.t','U'), 2, 1, 'DETAILED')
WHERE
page_type_DESC = 'INDEX_PAGE'
ORDER BY
allocated_page_file_id
, allocated_page_page_id;
DBCC PAGE(@dbid,@fileid,@pageid,3);
GO
Results:
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+
| FileId | PageId | Row | Level | GuidColumn (key) | AutoIncInt (key) | SomeColumn | KeyHashValue | Row Size |
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+
| 1 | 424 | 0 | 0 | 00000000-0000-0000-0000-000000000001 | 1 | 3 | (63f9557c63d5) | 5038 |
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+
This index architecture has positive performance implications for your query because the AutoIncInt
column is not only implicitly included in the non-clustered index as the row locator, it is also part of the unique key and thus ordered. This allows the plan for your query to use an index seek for the GuidColumn
WHERE
predicate plus a backwards scan for the ORDER BY...DESC
clause.
Below is an excerpt from the actual query plan shows a backwards scan of the index. The resultant non-clustered index will be the same regardless of whether AutoIncInt
is explicitly specified as a key column.
<IndexScan Ordered="true" ScanDirection="BACKWARD"
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0