indeks hipotetyczny

Tworzenie indeksów na tabelach używanych przez innych  użytkowników jest koszmarem, nawet jeżeli mamy bazę w trybie administracyjny albo używamy enterprisowego ONLINE to trwa to czasami bardzo długo, dodatkowo nie mamy pewności że serwer użyje właśnie tego indeksu. Podglądając pracę Database  Engine Tubing Advisora możemy zauważyć że przyt zakładaniu testowanych indeksów używa opcji WITH STATISTICS_ONLY = -1, oznacza to, że nie jest tworzony fizyczny indeks tylko statystyki. Robimy sobie tabelę

CREATE TABLE dbo.indeks_test(id INT IDENTITY(1,1) PRIMARY KEY,

      SalesOrderID int ,

      SalesOrderDetailID int ,

      CarrierTrackingNumber nvarchar(25) ,

      OrderQty smallint ,

      ProductID int ,

      SpecialOfferID int ,

      UnitPrice money ,

      UnitPriceDiscount money ,

      LineTotal  money,

      rowguid uniqueidentifier ,

      ModifiedDate datetime )

Napełniamy ją danymi

INSERT dbo.indeks_test

SELECT *  FROM Sales.SalesOrderDetail

Mamy zapytanie do zoptymalizowania

SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test

WHERE SalesOrderDetailID BETWEEN 43668 AND 43892

AND SalesOrderID =52200

AND OrderQty =1

ORDER BY ProductID

Tworzymy dwa indeksy hipotetyczne, dla celów pokazowych nie są one idealne

CREATE INDEX ix_test1 ON dbo.indeks_test (SalesOrderDetailID,SalesOrderID)

INCLUDE (OrderQty,UnitPrice) WITH STATISTICS_ONLY = -1

GO

CREATE INDEX ix_test2 ON dbo.indeks_test (SalesOrderDetailID,SalesOrderID,OrderQty)

INCLUDE (UnitPrice,ProductID) WITH STATISTICS_ONLY = -1

Sprawdzamy jak to wygląda

sp_helpindex 'indeks_test'

SELECT name,is_hypothetical FROM sys.indexes

WHERE object_id=OBJECT_ID('indeks_test')

name                             is_hypothetical

-------------------------------- ---------------

PK__indeks_t__3213E83F1FEDB87C   0

ix_test1                         1

ix_test2                         1

Indeksy mają swoje statystyki

DBCC SHOW_STATISTICS ('indeks_test','ix_test1')

DBCC SHOW_STATISTICS ('indeks_test','ix_test2')

Problem zaczyna się jeżeli chcemy ich użyc, w zapytaniu nie zostaną użyte

SET SHOWPLAN_TEXT ON

GO

SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test

WHERE SalesOrderDetailID BETWEEN 43668 AND 43892

AND SalesOrderID =52200

AND OrderQty =1

ORDER BY ProductID

GO

SET SHOWPLAN_TEXT OFF

StmtText

----------------------------------------------------------------------------------------------

  |--Sort(ORDER BY:([AdventureWorks].[dbo].[indeks_test].[ProductID] ASC))

       |--Clustered Index Scan(OBJECT:([AdventureWorks].[dbo].[indeks_test].[PK__indeks_t__3213E83F1FEDB87C]), WHERE:([AdventureWorks].[dbo].[indeks_test].[SalesOrderDetailID]>=(43668) AND [AdventureWorks].[dbo].[indeks_test].[SalesOrderDetailID]<=(43892)

Nie można też wymusić ich użycia

SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test  WITH(index=ix_test1)

WHERE SalesOrderDetailID BETWEEN 43668 AND 43892

AND SalesOrderID =52200

AND OrderQty =1

ORDER BY ProductID

Msg 308, Level 16, State 1, Line 1

Index 'ix_test1' on table 'dbo.indeks_test' (specified in the FROM clause) does not exist.

Żeby uzyc indeksu hipotetycznego musimy zastosowac kombinację DBCC AUTOPILOT i SET AUTOPILOT

select DB_ID(),OBJECT_ID('indeks_test')-- =5,503672842

DBCC AUTOPILOT (0,5,503672842,5,0,0) --dla ix_test1

GO

SET AUTOPILOT ON

GO

SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test

WHERE SalesOrderDetailID BETWEEN 43668 AND 43892

AND SalesOrderID =52200

AND OrderQty =1

ORDER BY ProductID

Dostajemy wynik

Teraz drugi indeks

SET AUTOPILOT OFF

GO

DBCC AUTOPILOT (0,5,503672842,6,0,0) --dla ix_test2

GO

SET AUTOPILOT ON

GO

SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test

WHERE SalesOrderDetailID BETWEEN 43668 AND 43892

AND SalesOrderID =52200

AND OrderQty =1

ORDER BY ProductID

Dostajemy wynik

Możemy zobaczyć, że z wybranych indeksów najlepszy jest ix_test2 i to jego wybierze optymalizator.

Brakuje jasnej dokumentacji DBCC AUTOPILOT ale mam wrażenie, że to potężne narzędzie