Kolejność sortowania określona w kluczu podstawowym, ale sortowanie odbywa się w SELECT

Przechowuję dane czujnika w tabeli SensorValues . Tabela i klucz podstawowy są następujące:

CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL,
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED 
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
) WITH (
    FILLFACTOR=75,
    DATA_COMPRESSION = PAGE,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
  ON [MyPartitioningScheme]([Date])

Jednak gdy wybieram wartość czujnika ważną przez określony czas, plan wykonania mówi mi, że robi coś w rodzaju. Dlaczego?

Myślałem, że skoro przechowuję wartości posortowane według kolumny Data, sortowanie nie nastąpi. A może dlatego, że indeks nie jest wyłącznie sortowany według kolumny Data, tzn. Nie może zakładać, że zestaw wyników jest sortowany?

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC

Edycja: czy mogę to zrobić zamiast tego?

Ponieważ tabela jest posortowana DeviceId, SensorId, Date i wykonuję SELECT, określając tylko jeden DeviceId i jeden SensorId , zestaw wyjściowy powinien być już posortowany według daty DESC . Zastanawiam się więc, czy poniższe pytanie dałoby taki sam wynik we wszystkich przypadkach?

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010

Według @Catcall poniżej kolejność sortowania nie jest taka sama jak kolejność przechowywania. Tzn. Nie możemy zakładać, że zwrócone wartości są już w posortowanej kolejności.

Edycja: Wypróbowałem to rozwiązanie CROSS APPLY, bez powodzenia

@Martin Smith zasugerował, że spróbuję ZEWNĘTRZNIE zastosować mój wynik względem partycji. Znalazłem post na blogu ( Wyrównane nieklastrowane indeksy na partycjonowanej tabeli ) opisujący ten podobny problem i wypróbowałem nieco podobne rozwiązanie do sugestii Smitha. Jednak tutaj nie ma szczęścia, czas wykonania jest na równi z moim oryginalnym rozwiązaniem.

WITH Boundaries(boundary_id)
AS
(
  SELECT boundary_id
  FROM sys.partition_functions pf
  JOIN sys.partition_range_values prf ON pf.function_id = prf.function_id
  WHERE pf.name = 'PF'
  AND prf.value <= 1339225010
  UNION ALL
  SELECT max(boundary_id) + 1
  FROM sys.partition_functions pf
  JOIN sys.partition_range_values prf ON pf.function_id = prf.function_id
  WHERE pf.name = 'PF'
  AND prf.value <= 1339225010
),
Top1(SensorValue)
AS
(
  SELECT TOP 1 d.SensorValue
  FROM Boundaries b
  CROSS APPLY
  (
    SELECT TOP 1 SensorValue
      FROM SensorValues
      WHERE  SensorId = 53
        AND DeviceId = 3819
        AND "Date" < 1339225010
        AND $Partition.PF(Date) = b.boundary_id
        ORDER BY Date DESC
  ) d
  ORDER BY d.Date DESC
)
SELECT SensorValue
FROM Top1

W przypadku niepartycjonowanej tabeli otrzymuję następujący plan

Jest jeden predykat wyszukiwania Seek Keys[1]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010 .

Oznacza to, że SQL Server może przeprowadzić wyszukiwanie równości w pierwszych dwóch kolumnach, a następnie rozpocząć wyszukiwanie zakresu od 1339225010i uporządkowane FORWARD(ponieważ indeks jest zdefiniowany za pomocą[Date] DESC )

The TOPOperator zatrzyma zainteresowanie dwa rzędy z szukają pierwszy rząd jest emitowany.

Kiedy tworzę schemat partycji i funkcję

CREATE PARTITION FUNCTION PF (int)
AS RANGE LEFT FOR VALUES (1000, 1339225009 ,1339225010 , 1339225011);
GO
CREATE PARTITION SCHEME [MyPartitioningScheme]
AS PARTITION PF
ALL TO ([PRIMARY] );

I wypełnij tabelę następującymi danymi

INSERT INTO [dbo].[SensorValues]    
/*500 rows matching date and SensorId, DeviceId predicate*/
SELECT TOP (500) 3819,53,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0))           
FROM master..spt_values
UNION ALL
/*700 rows matching date but not SensorId, DeviceId predicate*/
SELECT TOP (700) 3819,52,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0))           
FROM master..spt_values
UNION ALL 
/*1100 rows matching SensorId, DeviceId predicate but not date */
SELECT TOP (1100) 3819,53,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 1339225011      
FROM master..spt_values

Plan na SQL Server 2008 wygląda następująco.

Rzeczywista liczba wierszy emitowanych z wyszukiwania wynosi 500. Plan pokazuje predykaty poszukiwania

Seek Keys[1]: Start: PtnId1000 <= 2, End: PtnId1000 >= 1, 
Seek Keys[2]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010

Wskazując, że jest za pomocą pominąć skanowanie podejście opisane tutaj

optymalizator zapytań został rozszerzony, aby można było wykonać operację wyszukiwania lub skanowania z jednym warunkiem na PartitionID (jako logicznej wiodącej kolumnie) i ewentualnie innych kolumnach klucza indeksu, a następnie można przeprowadzić wyszukiwanie drugiego poziomu z innym warunkiem w jednej lub kilku dodatkowych kolumnach, dla każdej odrębnej wartości, która spełnia kwalifikację do operacji wyszukiwania pierwszego poziomu.

Ten plan jest planem szeregowym, więc dla konkretnego zapytania wydaje się, że jeśli SQL Server zapewnił, że przetworzy partycje w malejącej kolejności dateniż oryginalny plan zTOP nadal będzie działał i może przestać przetwarzać po pierwszym dopasowanym wierszu znaleziono zamiast kontynuować i generować pozostałe 499 dopasowań.

W rzeczywistości plan na 2005 r. Wygląda na to, że przyjmuje takie podejście

Nie jestem pewien, czy uzyskanie tego samego planu w 2008 roku jest proste, a może potrzebowałoby OUTER APPLYwłączenia, sys.partition_range_valuesaby go zasymulować.

Wiele osób uważa, że ​​indeks klastrowy gwarantuje porządek sortowania na wyjściu. Ale to nie to, co robi; gwarantuje kolejność przechowywania na dysku.

Zobacz na przykład ten post na blogu i dłuższą dyskusję .

Spekuluję, że SORT jest potrzebny z powodu równoległego planu. Opieram to na jakimś słabym i odległym artykule na blogu: ale znalazłem to na MSDN, co może, ale nie musi, uzasadniać tego

Wypróbuj MAXDOP 1 i zobacz, co się stanie ...

Zasugerował również w @sql kiwi na blogu na prostych Dyskusja pod „Operator Exchange” myślę. I tutaj „zależność od DOP”

Zasadniczo masz rację - ponieważ klucz podstawowy ma kolejność „DeviceId, SensorId, Date”, dane w kluczu nie są sortowane według daty, więc nie można ich użyć. Jeśli Twój klucz był w innej kolejności „Data, DeviceId, SensorId”, dane w kluczu byłyby posortowane według daty, więc można go użyć ...