Sql-server – Table with clustered index implicitly sorting by unique nonclustered index

execution-planindexperformancequery-performancesql server

I have a table that captures the host platform that a user is running on. The table's definition is straightforward:

IF OBJECT_ID('[Auth].[ActivityPlatform]', 'U') IS NULL
BEGIN
    CREATE TABLE [Auth].[ActivityPlatform] (
        [ActivityPlatformId] [tinyint] IDENTITY(1,1) NOT NULL
        ,[ActivityPlatformName] [varchar](32) NOT NULL
        ,CONSTRAINT [PK_ActivityPlatform] PRIMARY KEY CLUSTERED ([ActivityPlatformId] ASC)
        ,CONSTRAINT [UQ_ActivityPlatform_ActivityPlatformName] UNIQUE NONCLUSTERED ([ActivityPlatformName] ASC)
    ) ON [Auth];
END;
GO

The data it stores is enumerated based on a JavaScript method that uses information from their browser (I don't know much more than that, but could find out if needed):

When I perform a basic SELECT without an explicit ORDER BY, however, the Execution Plan shows that it is using the UNIQUE NONCLUSTERED index in order to sort instead of the CLUSTERED index.

SELECT * FROM [Auth].[ActivityPlatform]

When explicitly specifying the ORDER BY, it correctly sorts by ActivityPlatformId.

SELECT * FROM [Auth].[ActivityPlatform] ORDER BY [ActivityPlatformId]

DBCC SHOWCONTIG('[Auth].[ActivityPlatform]') WITH ALL_LEVELS, TABLERESULTS shows no table fragmentation.

What am I missing that could cause for this? I thought so long that the table was created on a clustered index, it should automatically sort by it implicitly without need to specify ORDER BY. What is SQL Server's preference in choosing the UQ? Is there something I need to specify in the table's creation?

Best Answer

No, sorting is not implicit and should not be relied upon. In fact, in the first tooltip, you can see that it is explicitly stated that Ordered = False. This means SQL Server didn't do anything at all to implement any sorting. What you observe is just what it happened to do, not what it tried to do.

If you want to be able to predict a reliable sort order, type out the ORDER BY. Period. What you might observe when you don't add ORDER BY might be interesting, but it cannot be relied upon to behave consistently. In fact in this post, see #3, I show how a query's output can change simply by someone else adding an index.

T-SQL Tuesday #56 : Assumptions

What is SQL Server's preference in choosing the UQ?

The UNIQUE NONCLUSTERED index contains the clustering key, so it is covering for the query. In this case, your table only has two columns, so the clustered and nonclustered indexes contain the same data (just sorted differently). They're both the same size, so the optimizer could choose either. That it chooses the nonclustered index is an implementation detail.

I call this a "coin flip."

Related Solutions

Sql-server – Efficient INSERT INTO a Table With Clustered Index

As the other answers already indicate SQL Server may or may not explicitly ensure that the rows are sorted in clustered index order prior to the insert.

This is dependant upon whether or not the clustered index operator in the plan has the DMLRequestSort property set (which in turn depends upon the estimated number of rows that are inserted).

If you find that SQL Server is underestimating this for whatever reason you might benefit from adding an explicit ORDER BY to the SELECT query to minimize page splits and ensuing fragmentation from the INSERT operation

Example:

use tempdb;

GO

CREATE TABLE T(N INT PRIMARY KEY,Filler char(2000))

CREATE TABLE T2(N INT PRIMARY KEY,Filler char(2000))

GO

DECLARE @T TABLE (U UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),N int)

INSERT INTO @T(N)
SELECT number 
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 499

/*Estimated row count wrong as inserting from table variable*/
INSERT INTO T(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2

/*Same operation using explicit sort*/    
INSERT INTO T2(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2
ORDER BY T1.N*1000 + T2.N


SELECT avg_fragmentation_in_percent,
       fragment_count,
       page_count,
       avg_page_space_used_in_percent,
       record_count
FROM   sys.dm_db_index_physical_stats(2, OBJECT_ID('T'), NULL, NULL, 'DETAILED')
;  


SELECT avg_fragmentation_in_percent,
       fragment_count,
       page_count,
       avg_page_space_used_in_percent,
       record_count
FROM   sys.dm_db_index_physical_stats(2, OBJECT_ID('T2'), NULL, NULL, 'DETAILED')
;

Shows that T is massively fragmented

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
99.3116118225536             92535                92535                67.1668272794663               250000
99.5                         200                  200                  74.2868173956017               92535
0                            1                    1                    32.0978502594514               200

But for T2 fragmentation is minimal

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
0.376                        262                  62500                99.456387447492                250000
2.1551724137931              232                  232                  43.2438349394613               62500
0                            1                    1                    37.2374598468001               232

Conversely sometimes you might want to force SQL Server to underestimate the row count when you know the data is already pre-sorted and wish to avoid an unnecessary sort. One notable example is when inserting a large number of rows into a table with a newsequentialid clustered index key. In versions of SQL Server prior to Denali SQL Server adds an unnecessary and potentially expensive sort operation. This can be avoided by

DECLARE @var INT =2147483647

INSERT INTO Foo
SELECT TOP (@var) *
FROM Bar

SQL Server will then estimate that 100 rows will be inserted irrespective of the size of Bar which is below the threshold at which a sort is added to the plan. However as pointed out in the comments below this does mean that the insert will unfortunately not be able to take advantage of minimal logging.

Sql-server – Why index REBUILD does not reduce index fragmentatation with a page count > 1000

When you rebuild the index set MAXDOP(1). This will only use 1 CPU and although it will take longer you will have much lower fragmentation.

I suspect that currently, where the index is "fairly small" the operation is being over parellelsed. Each thread used will cause additional fragmentation, as each thread orders its own pages only, and for smaller indexes this can be more noticeable.

Best Answer

Related Solutions

Sql-server – Efficient INSERT INTO a Table With Clustered Index

Sql-server – Why index REBUILD does not reduce index fragmentatation with a page count > 1000

Related Question