Webcast: Filtered Indexes and Statistics in SQL Server 2008

sgovoni — Wed, 22 Dec 2010 22:07:00 GMT

Sul portale BEIT - MSDN/TechNet - è stato pubblicato un altro Webcast (in Italiano) dedicato alle funzionalità rilasciate con la versione 2008 di SQL Server. Questa volta parleremo di Filtered Indexes and Statistics, ovvero la possibilità di specificare una clausola WHERE nei comandi CREATE INDEX e CREATE STATISTICS.

Abstract

Con SQL Server 2008 presentiamo gli indici e le statistiche filtrate: si tratta delle strutture che conosciamo già, a cui però viene applicato un criterio di filtro. In questo webcast parleremo prevalentemente dei primi, cercando di capire quando è conveniente utilizzarli e quando la loro applicazione può migliorare le performance delle query.

Webcast

SQL Server 2008: Filtered Indexes [view/download webcast]

Ringrazio lo Sponsor, TechSmith, che ci ha donato una licenza di Camtasia, ottimo software per registrare screencast e demo!

Buona visione! ...e se è stato di vostro gradimento, votatelo.

Filtered Indexes

sgovoni — Sat, 12 Sep 2009 14:02:00 GMT

Con SQL Server 2008 fanno il loro debutto gli indici e le statistiche filtrare; sono le strutture che conosciamo già, a cui però viene applicato un criterio di filtro. In questo post parleremo degli indici filtrati, cercando di capire quando la loro applicazione può migliorare le performance delle query.

Il comando CREATE INDEX può ora accettare la clausola WHERE utilizzata per specificare il criterio di filtro.

Consideriamo, ad esempio, un indice standard definito su due colonne di una tabella (col_1 e col_2), l’ultimo livello (foglia) contiene la combinazione dei valori presenti nelle due colonne. Diversamente, se specifichiamo un criterio nella clausola WHERE del comando CREATE INDEX, per selezionare solo alcuni valori di una colonna, (ad esempio col_1 > 10) le righe che non soddisfano il criterio verranno ignorate, proprio durante la costruzione dell’indice, a prescindere dal valore contenuto nella seconda colonna. Il risultato è una struttura B-Tree più piccola in cui l’ultimo livello (foglia) contiene soltanto i dati che verificano il criterio specificato.

Utilizzo degli indici filtrati

La probabilità che il query optimizer scelga di utilizzare un indice filtrato è più alta quando il criterio specificato nella clausola WHERE della query è simile al criterio specificato nella clausola WHERE dell’indice.

Consideriamo, ad esempio, la tabella dbo.orderheader con la seguente struttura:

use [tempdb];
go

if (object_id('orderheader', 'U') is not null)
drop table dbo.orderheader;
go

-- create table orderheader
create table dbo.orderheader
(orderid int identity(1, 1) not null,
orderdate datetime default getdate() not null,
ordernumber as (isnull(N'SO' + convert([nvarchar](23), [orderid], 0), N'*** error ***')),
customerid int default 1 not null,
shipname varchar(20) default 'name',
shipaddress varchar(40) default 'address',
shipvia varchar(40) default 'via',
shipcity varchar(20) default 'city',
shipregion varchar(20) default 'region',
shippostalcode varchar(20) default 'postal code',
shipcountry varchar(20) default 'country',
-- delivery info
deliverydate datetime default (getdate() + datepart(ss, getdate())) not null,
deliverynote varchar(40)
primary key(orderid)
);

Assumiamo che solo pochi ordini abbiamo dei dati nella colonna deliverynote, utilizzata dai clienti per segnalare eventuali particolarità relative alla consegna, ad esempio: “non consegnare il Lunedì pomeriggio”.

Popoliamo la tabella dbo.orderheader con 500.000 ordini cliente (per semplicità inseriamo solo la testata) di cui solo 500 (uno su 1000) con dati nella colonna note di consegna (deliverynote):

declare @i as int
set @i = 0
while (@i < 500000)
begin
set nocount on

-- insert orderheader
insert into dbo.orderheader default values

if (@i % 1000) = 1
begin
    update
      dbo.orderheader
    set
      deliverynote = 'Not deliver the ' + (select ltrim(rtrim(datename(weekday, getdate() + @i)))) + ' afternoon'
    where
      (orderid = (select scope_identity()))
end

set @i = (@i + 1)
set nocount on
end;

Vogliamo ora creare un indice appropriato per la seguente query di estrazione degli ordini cliente con note e data di consegna compresa tra il 12/09/2009 ed il 12/10/2009.

create nonclustered index FIDX__orderheader_delivernote on dbo.orderheader
(
deliverydate
, deliverynote
)
include
(
ordernumber
, orderdate
);
go

select
deliverydate
, ordernumber
, orderdate
from
dbo.orderheader
where
(deliverydate between '20090912' and '20091012')
and (deliverynote is not null);

Eseguiamo la query e analizziamo il piano di esecuzione e le statistiche di I/O, l’indice FIDX__orderheader_delivernote precedentemente creato viene scelto dal query optimizer, per l’estrazione dei dati richiesti sono necessarie 1168 letture.

Statistiche di I/O:

(194 row(s) affected)
Table 'orderheader'. Scan count 1, logical reads 1168, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Applichiamo ora un criterio di filtro all’indice FIDX__orderheader_delivernote, aggiungiamo la clausola WHERE e specifichiamo la condizione "deliverynote is not null". Così facendo, durante la costruzione dell’indice, verranno considerate soltanto le righe aventi una nota di consegna:

drop index FIDX__orderheader_delivernote on dbo.orderheader;
go

create nonclustered index FIDX__orderheader_delivernote on dbo.orderheader
(
deliverydate
, deliverynote
)
include
(
ordernumber
, orderdate
)
where
(deliverynote is not null);

Analizziamo di nuovo il piano di esecuzione e le statistiche di I/O per la stessa query di interrogazione, ma con l’indice filtrato:

Statistiche di I/O:

(194 row(s) affected)
Table 'orderheader'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Confrontando i piani di esecuzione, osserviamo che il query optimizer, in entrambi i casi, sceglie di utilizzare l’indice FIDX__orderheader_delivernote perché questo “copre” la query, utilizzando l’indice filtrato però si è drammaticamente ridotto il numero di pagine lette.

Conclusioni

Quando è conveniente utilizzare un indice filtrato ? E’ conveniente quando il rapporto tra il numero delle righe interessate nel filtro (applicato all’indice) e il numero (totale) delle righe presenti nella tabella è basso.

L’efficacia dell’indice filtrato sarà tanto maggiore quanto minore risulterà essere questo rapporto (nell'esempio trattato il rapporto è 500/500.000 = 0.001).

Qualora più del 50% delle righe della tabella dbo.orderheader avessero le note di consegna, la manutenzione dell’indice filtrato sarebbe molto più costosa rispetto alla manutenzione dell’equivalente indice standard e la scelta di utilizzarlo sarebbe sbagliata. Gli indici filtrati risultano meno performanti degli indici standard nelle operazioni di mantenimento (durante la modifica dei dati).

SQL Server & Delphi : Filtered Indexes

Webcast: Filtered Indexes and Statistics in SQL Server 2008

Filtered Indexes