why does it have to read 19 billon rows?
Why is this query doing a Clustered Index Seek, with a 19 billion row estimate?
There is a primary key and the there should only be 2.5 million rows returned.
Best regards,
Henrik
My table looks like this
CREATE TABLE oda.GDATWTGShadow10MinData ( GDATWTGShadow10MINDATAID BIGINT IDENTITY (1, 1) NOT NULL, ODSDataSourceID SMALLINT NOT NULL, EXTExtractJobID INT NOT NULL, LotsOfOtherColumns CHAR(200) not NULL, CONSTRAINT PK_GDATWTGShadow10MINDATA PRIMARY KEY CLUSTERED (GDATWTGShadow10MINDATAID ASC, ODSDataSourceID ASC) WITH (DATA_COMPRESSION = ROW) ON PS_ODSDataSourceID ([ODSDataSourceID]), ); CREATE UNIQUE NONCLUSTERED INDEX IX_EXTExtractJobID_TWTGShadow10MINDATAID_ODSDataSourceID ON oda.GDATWTGShadow10MinData(EXTExtractJobID ASC, TWTGShadow10MINDATAID ASC, ODSDataSourceID ASC) WITH (DATA_COMPRESSION = PAGE) ON PS_ODSDataSourceID (ODSDataSourceID);
and the partition scheme and function:
CREATE PARTITION SCHEME [PS_ODSDataSourceID] AS PARTITION [PF_ODSDataSourceID] TO ([OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData], [OperationalData]) GO CREATE PARTITION FUNCTION [PF_ODSDataSourceID](smallint) AS RANGE LEFT FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169) GO
Even there were a suitable column uniqueness guarantee, SQL Server will not necessarily infer that a range of whole-number values will limit the rows returned, in the way a human naturally would. Estimates are generally computed from statistics, which may be adjusted for data modifications since they were collected, the sampling level (partitioned indexes no longer automatically get full scan stats from 2012), ascending keys (which is less than perfect in the new CE), and so on.
The original CE had modelling extensions under TF 2301 that would apply whole-number inferences in some cases, but that flag does not apply to the new CE. You could try compiling the query under the original CE (TF 9481) with and without 2301 to see what impact that has.
I would check the statistics and investigate how the new CE ascending key logic might be being applied.
Thank you very much for your answer. In the meantime, I ran
UPDATE STATISTICS oda.[GDATWTGShadow10MinData] (pk_GDATWTGShadow10MinData )
and now the query has an estimated cost of 14 (previously 272000), and it completed in 2 minutes (probably most of the time due to SSMS).
I understand that there is not uniqueness constraint on the tableID column alone, but until recently our SQL Server has not made these huge mistakes everywhere. I suspect that a bug has been introduced with CU2?
Either that, or our (Ola Hallengren) index maintenance jobs are taking a nap. 🙂
Best regards,
Henrik