W jaki sposób duże pola INCLUDE wpłyną na wydajność systemu?

To pytanie dotyczy wydajności indeksu SQL Server z indeksem zakrywającym varchar(2000)jako INCLUDEa.

Próbuję poprawić wydajność w powolnej i niestabilnej aplikacji bazy danych. W niektórych przypadkach dane są dostępne za pośrednictwem dużych ciągów varchar, z zapytaniami w tym multple operacji strunowych, takich jak SUBSTRING(), SPACE()i DATALENGTH(). Oto uproszczony przykład dostępu;

update fattable set col3 =  
   SUBSTRING(col3,1,10) + '*' + 
   SUBSTRING(col3,12,DATALENGTH(col3)-12)
from fattable where substring(col3,10,1) = 'A' and col2 = 2

Schemat wygląda następująco:

CREATE TABLE [dbo].[FatTable]( 
    [id] [bigint] IDENTITY(1,1) NOT NULL, 
    [col1] [nchar](12) NOT NULL, 
    [col2] [int] NOT NULL, 
    [col3] [varchar](2000) NOT NULL, ... 

Zdefiniowano następujący indeks z zakrywającym polem w dużej kolumnie tekstowej.

CREATE NONCLUSTERED INDEX [IndexCol2Col3] ON [dbo].[FatTable]  ( [col2] ASC ) 
    INCLUDE( [col3] )

Z tego, co przeczytałem, ZŁE jest umieszczanie dużych pól danych w indeksie. Czytałem kilka artykułów, w tym http://msdn.microsoft.com/en-us/library/ms190806.aspx, które omawiają wpływ stronicowania i rozmiaru dysku na wydajność indeksu. To powiedziawszy, plan zapytań zdecydowanie używa indeksu obejmującego. Nie mam wystarczających informacji, aby ustalić, ile faktycznie mnie to kosztuje pod względem obciążenia systemu. Wiem, że ogólnie system działa słabo i martwię się, że jest to jeden z problemów. Pytania:

• Czy umieszczenie tej varchar(2000)kolumny w indeksie INCLUDEjest dobrym pomysłem?

• Ponieważ INCLUDEpola są przechowywane w węzłach liści, czy mają one duży wpływ na indeks wydajności?

Aktualizacja: Dziękujemy za doskonałe odpowiedzi! Jest to w pewnym sensie niesprawiedliwe pytanie - jak mówicie, nie ma absolutnie właściwej odpowiedzi bez faktycznych statystyk i profilowania. Podobnie jak w przypadku wielu problemów z wydajnością, odpowiedź brzmi „to zależy”.

Zawsze jest wielkie słowo, ale generalnie nie, nie umieściłbym pola varchar (2000) w polu INCLUDE.

I tak, sposób przechowywania danych na poziomie strony może poważnie wpłynąć na wydajność indeksu, w zależności od sposobu użycia indeksu.

Chodzi o to, że im więcej wierszy danych można wcisnąć na stronę, tym mniej stron trzeba uzyskać dostęp, tym szybciej system jest przeważnie. Dodanie naprawdę dużej kolumny oznacza mniej informacji przechowywanych na stronie, więc w przypadku szukania zasięgu lub skanowania potrzeba więcej stron do odczytania w celu odzyskania danych, co znacznie spowalnia pracę.

Aby się upewnić, czy jest to problem w zapytaniu lub w systemie, należy monitorować odczyty, zwłaszcza liczbę stron używanych przez zapytanie.

Czy możesz przejrzeć aktualny klastrowany klucz indeksu i być może utworzyć col2klastrowany klucz indeksu? W ten sposób otrzymujesz zachowanie „uwzględnij” (ponieważ indeksy klastrowe są zawsze „obejmujące” wszystko) bez duplikowania danych. Jest to oczywiście przedmiotem wielu ifi butmimo to być może warto je rozważyć. Oczywiście, jeśli bieżący indeks klastrowy wymusza ograniczenie (klucz podstawowy, unikalny), wspomniane ograniczenie musiałoby zostać przeniesione do indeksu nieklastrowego.

Trudno odpowiedzieć. Wszystko będzie zależeć od twojego współczynnika odczytu: zapisu. Czy przetestowałeś obciążenie pracą lub symulowałeś cały cykl biznesowy w systemie testowym z dołączoną kolumną i bez niej? Wyszukiwanie bez niego może kosztować dużo, ale jeśli aktualizujesz dane częściej niż je czytasz, może to być w porządku.

Wiem, że jestem spóźniony na tę imprezę, ale indeksowałbym dokładnie wyrażenia użyte do zlokalizowania wierszy, takie jak podłańcuch (kol3,10,1). Jeśli kiedykolwiek użyje się całego col3, zaindeksowałbym CHECKSUM (col3) (rozumiejąc, że oczywiście mogą wystąpić kolizje).