Loading…

READY TO DEAL WITH YOUR DATA?

Get some tips on how to do that!
Start exploring

SQL Server 2014 – STATISTICS IO a klastrowane indeksy kolumnowe

Wspomnień z SQLDay 2014 ciąg dalszy :-)

W trakcie przygotowań do prezentacji poświęconej indeksom kolumnowym w SQL Server 2014 (“Clustered ColumnStore Index Deep Dive”) odkryłem kolejny scenariusz, w którym śledzenie liczby odczytów za pomocą SET STATISTICS IO ON jest złym pomysłem.

Historycznie takich sytuacji było wiele, że wspomnę o przekłamaniach przy zapytaiach z użyciem funkcji skalarnych (wpis Gail Shaw) czy problemach z ilością odczytów przy planach wykorzystujących parallelism (wpis Aarona Bertranda).

Co tym razem? Po kolei.

Kłamca, kłamca…

Na początek na instancji SQL Server 2014 założyłem bazę danych i stworzyłem w niej tabelę, na której założyłem klastrowany indeks kolumnowy, po czym dodałem trochę rekordów (do poprawnego działania kodu potrzebna jest baza AdventureWorksDW2012).

USE master;
GO
 
IF DB_ID('DemoDB') IS NOT NULL
BEGIN
  ALTER DATABASE DemoDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE DemoDB;
END;
GO
 
CREATE DATABASE DemoDB;
GO
 
USE DemoDB;
GO
 
IF OBJECT_ID('dbo.FactInternetSalesBig') IS NOT NULL
  DROP TABLE dbo.FactInternetSalesBig;
GO
 
SELECT TOP 0
  SalesOrderNumber,
  SalesOrderLineNumber,
  ProductKey, 
  OrderDateKey, 
  CustomerKey, 
  PromotionKey, 
  CurrencyKey, 
  SalesTerritoryKey, 
  OrderQuantity, 
  UnitPrice, 
  SalesAmount, 
  TaxAmt, 
  Freight
INTO dbo.FactInternetSalesBig
FROM AdventureWorksDW2012.dbo.FactInternetSales;
GO
 
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactInternetSalesBig 
ON dbo.FactInternetSalesBig;
GO
 
INSERT INTO dbo.FactInternetSalesBig (
  SalesOrderNumber,
  SalesOrderLineNumber,
  ProductKey, 
  OrderDateKey, 
  CustomerKey, 
  PromotionKey, 
  CurrencyKey, 
  SalesTerritoryKey, 
  OrderQuantity, 
  UnitPrice, 
  SalesAmount, 
  TaxAmt, 
  Freight
)
SELECT 
  SalesOrderNumber,
  SalesOrderLineNumber,
  ProductKey, 
  OrderDateKey, 
  CustomerKey, 
  PromotionKey, 
  CurrencyKey, 
  SalesTerritoryKey, 
  OrderQuantity, 
  UnitPrice, 
  SalesAmount, 
  TaxAmt, 
  Freight
FROM AdventureWorksDW2012.dbo.FactInternetSales;
GO 20

Co mamy w indeksie?

SELECT * FROM sys.column_store_row_groups 
WHERE object_id = OBJECT_ID('dbo.FactInternetSalesBig');
GO

Jeśli od wstawienia danych nie minęło 5 minut (taki jest interwał działania procesu Tuple Mover, który kompresuje zamknięte grupy wierszy), to widzimy dwie grupy wierszy – jedną w statusie OPEN, drugą w statusie CLOSED (obie to row store):

image

 

Teraz test zapytania:

SET STATISTICS IO ON;
GO
 
SELECT COUNT(*) FROM dbo.FactInternetSalesBig;
GO
 
SET STATISTICS IO OFF;
GO

Ilość odczytów wg STATISTICS IO jest ciekawa:

Table ‘FactInternetSalesBig’. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Zero odczytów? :-) Coś tu nie gra, prawda? W takim razie SQL Server Profiler w dłoń i patrzymy, czy i on powie zero odczytów:

image

 

 

 

 

A więc nie zero, a trochę więcej :-)

Teraz spróbujmy przebudować indeks, żeby grupy wierszy przesunęły się z row store do column store.

ALTER INDEX CCI_FactInternetSalesBig
ON dbo.FactInternetSalesBig REBUILD;
GO

Po wykonaniu tej operacji w indeksie trzy grupy plików poddane kompresji (wszystkie są już w indeksie kolumnowym).

image

 

Gdy teraz przetestujemy zapytanie, dla którego wcześniej wg STATISTICS IO mieliśmy zero odczytów, wskazania są nieco inne:

Table ‘FactInternetSalesBig’. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Acha, czyli jednak są jakieś odczyty! :-) Co ciekawe, zarówno Profiler, jak i Extended Events pokazują, że tych odczytów było nie 16, a 34 :-) Nie udało mi się, póki co, dojść do tego, skąd biorą się różnice w tym przypadku (zero w pierwszym teście oznacza najpewniej, że STATISTICS IO nie widzi odczytów z row store).

Wniosek: STATISTICS IO w przypadku operacji na indeksach kolumnowych nie pokazuje prawdy. I wygląda na to, że nie dotyczy to wyłącznie sytuacji, gdy mamy dane w row store.

Nie tylko STATISTICS IO

I na koniec jeszcze jeden “kwiatek” dotyczący indeksów kolumnowych i metadanych. Sprawdziłem przed przebudową indeksu i po przebudowie, co pokazuje procedura sp_spaceused.

EXEC sp_spaceused 'dbo.FactInternetSalesBig';
GO

Wyniki są ciekawe, bo okazuje się, że przed przebudową, gdy wszystkie dane były w row store, procedura pokazała niezerowy rozmiar indeksu, a po przebudowie, gdy dane zostały poddane kompresji i przeszły do postaci kolumnowej, rozmiar indeksu wg procedury wynosił zero KB :-)

image
 
image
 

Podsumowanie

Ja już od dawna przy badaniu wydajności zapytań nie używam STATISTICS IO. Są narzędzia, które pokazują prawdę w temacie ilości czytanych i modyfikowanych stron danych. Przypadek indeksów kolumnowych potwierdza tylko, że lepiej używać sprawdzonych narzędzi (XE, SQL Trace / Profiler). Widać też, że pojawiające się nowe funkcjonalności są sporym wyzwaniem w warstwie metadanych systemu i niektóre obiekty systemowe operujące na metadanych nie radzą sobie z tym wyzwaniem.

One thought on “SQL Server 2014 – STATISTICS IO a klastrowane indeksy kolumnowe

  1. Pingback: dotnetomaniak.pl

Leave a Reply