Creiamo un ambiente di test
USE MASTER
Go
CREATE DATABASE DB_Test_Partition
Go
ALTER DATABASE [DB_TEST_PARTITION] ADD FILEGROUP [PartizioneUno]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILEGROUP [PartizioneDue]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILEGROUP [PartizioneTre]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILEGROUP [PartizioneQuattro]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILE ( NAME = N'p1', FILENAME = N'C:\Sql Server 2012\UserDataBase\p1.ndf') TO FILEGROUP [PartizioneUno]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILE ( NAME = N'p2', FILENAME = N'C:\Sql Server 2012\UserDataBase\p2.ndf') TO FILEGROUP [PartizioneDue]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILE ( NAME = N'p3', FILENAME = N'C:\Sql Server 2012\UserDataBase\p3.ndf') TO FILEGROUP [PartizioneTre]
GO
ALTER DATABASE [DB_TEST_PARTITION] ADD FILE ( NAME = N'p4', FILENAME = N'C:\Sql Server 2012\UserDataBase\p4.ndf') TO FILEGROUP [PartizioneQuattro]
GO
USE DB_TEST_PARTITION
Go
CREATE PARTITION FUNCTION MYPARTITIONFUNCTION (int)
AS RANGE LEFT FOR VALUES (500, 1000, 1500) ;
GO
CREATE PARTITION SCHEME MYPARTITIONSCHEME
AS PARTITION MYPARTITIONFUNCTION
TO (PartizioneUno, PartizioneDue, PartizioneTre, PartizioneQuattro) ;
GO
--Creo la tabella in modo che ogni record allochi una datapages
--e la partiziono con il partitioning schema appena creato
CREATE TABLE MYPARTITIONTABLE
(
col1 int not null,
col2 varchar(50) not null,
col3 varchar(50) not null,
col4 varchar(50) not null,
col5 char(5000)
)ON MYPARTITIONSCHEME (col1) ;
GO
--La popolo con 2000 record, 500 per ogni partizione
with CtePopolaTab
as
(Select 1 Riga union All Select 1 Riga),
Cte4 as (Select A.* from CtePopolaTab A,CtePopolaTab B),
Cte16 as (Select A.* from Cte4 A,Cte4 B),
Cte256 as (Select A.* from Cte16 A,Cte16 B),
Cte65536 as (Select A.* from Cte256 A,Cte256 B),
Ctex as (Select A.* from Cte65536 A,Cte65536 B),
Risultato as (Select ROW_NUMBER() over(Order by riga) Riga from Ctex )
Insert Into MYPARTITIONTABLE
Select Riga
,Riga
,Riga
,Riga
,Riga
from Risultato
Where Riga <= 2000
Sulle tabella MYPARTITIONTABLE possiamo creare indici allineati e indici non allineati. Indici Allineati
Gli indici allineati sono creati sulla base dello schema di partizionamento su cui è creata la tabella, nel nostro caso MYPARTITIONSCHEME(col1). By Design tutti gli indici su tabelle partizionate vengono creati in modo allineato.
Indici NON Allineati
Gli indici non allineati sono creati su una funzione di partizionamento differente da quella su cui è creata la tabella oppure su filegroup differenti.
Struttura Indici Allineati
Creiamo ora un indice cluster sulla nostra tabella. By design viene creato in modo allineato, quindi viene creato un b-tree per ogni subset di dati di ogni partizione.
create clustered Index idx on MYPARTITIONTABLE(col2)
Non ho dovuto specificare nessuna keyword particolare per generare il nostro clustered index allineato/partizionato. Nell'esempio ho creato l'indice cluster con Key col2 che NON è la colonna di partizionamento.
Vediamo la struttura dell'indice idx appena creato
Select
i.name
,pis.Index_type_desc
,pis.partition_number
,pis.index_depth
,pis.index_level
,pis.page_count
,pis.record_count
from sys.dm_db_index_physical_stats(db_id(),object_id('MYPARTITIONTABLE'),null,null,'DETAILED') pis
inner join sys.indexes i on i.object_id = pis.object_id and i.index_id = pis.index_id
Order by pis.partition_number, pis.Index_level desc
L'indice è suddiviso su 4 partizioni ognuna delle quali ha il proprio b-tree.
Nel nostro esempio il b-tree è composto da 3 livelli (potrebbero naturalmente essere n).
Il livello 2 è la root del clustered index nella partizione n-esima
Il livello 1 è l'intermediete del clustered index nella partizione n-esima
Il livello 0 è il leaf level, ovvero la tabella stessa (idx è clustered), nella partizione n-esima.
Nell'immagine ho evidenziato il leaf level che è composto da 500 data pages in ogni partizione (500 * 4 = 2000, i record inseriti).
Vediamo ora quali pagine compongono l'indice e dove sono fisicamente allocate.
Escludo dalla ricerca le pagine dei leaf level.
create table #ind
(
PageFID tinyint
,PagePID int
,IAMFID tinyint
,IAMPID int
,ObjectID int
,IndexID tinyint
,PartitionNumber tinyint
,PartitionID bigint
,iam_chain_type varchar(30)
,PageType tinyint
,IndexLevel tinyint
,NextPageFID tinyint
,NextPagePID int
,PrevPageFID tinyint
,PrevPagePID int
)
go
/* eseguo il comando per recuperare info su un'indice cluster*/
insert #ind exec ('DBCC IND (''DB_Test_Partition'', ''MYPARTITIONTABLE'', -1) with tableresults')
go
select i.name
,df.physical_name
,pageFID
,pagePID
,pageType
,indexLevel
,case when pageType = 10 then 'IAM'
when pageType = 2 then 'index page'
when pageType = 1 then 'data pages'
else 'non definito'
end as pageType, nextPagePID, prevPagePID
from #ind loc
Inner join sys.indexes i
on i.object_id = loc.ObjectID and i.index_id = loc.IndexID
Inner join Sys.database_files df
on loc.PageFID = df.file_id
Where pagetype = 2
order by isnull(indexLevel,255) desc
Ogni file che vedete è il contenitore del subset di dati partizionato e del b-tree associato. Ogni file è indipendente da tutti gli altri file contenenti i dati della tabella partizionata.
La colonna pagePID contiene l'indirizzo di ogni index page nelle 4 partizioni.
La colonna pageFID contiene l'id del file in cui la pagina stessa è allocata.
Cosa contiene per esempio il root level del subset di dati contenuto nel file "C:\Sql Server 2012\UserDataBase\p3.ndf" ?
DBCC TRACEON(3604);
GO
DBCC PAGE ('DB_Test_Partition', 5, 21, 3)with tableresults;
GO
Contiene il valore della colonna indicizzata [col2] ma anche il valore della colonna [col1]. Questo perchè l'engine, internamente, aggiunge alla Key dell'indice clustered anche la colonna di partizionamento per rendere maggiormente efficienti le operazioni di ricerca. Qui trovate ulteriori dettagli.
Se riguardiamo la funzione di partizionamento e lo schema utilizzato possiamo assumere che :
Valori <=500 vengono inseriti nel filegroup PartizioneUno
Valori >=501 e <= 1000 vengono inseriti nel filegroup Partizionedue
Valori >=1001 e <= 1500 vengono inseriti nel filegroup PartizioneTre
Valori >=1501 vengono inseriti nel filegroup PartizioneQuattro
Il file P3.ndf è associato al filegroup PartizioneTre, quindi conterrà solo i record con valori di col1 compresi tra 1001 e 1500, nonchè il b-tree associato al subset dei dati.
Vediamo se il file p3.ndf contiene solo il subset di dati di sua competenza.
Per fare questa verifica utilizzo una funzione non documentata, sys.fn_PhysLocFormatter( %%Physloc%% ), che restituisce "fileid:Datapage:slot" in cui è allocato un record. Cerco ovviamente quelli che hanno col1 compreso tra 1001 e 1500. Salvo il risultato nella tabella temporanea #Locationtable e successivamente cerco quanti record hanno fileid <> 5, cioè non risiedono nel file p3.ndf.
Select col1,sys.fn_PhysLocFormatter( %%Physloc%% ) Location Into #Locationtable from MYPARTITIONTABLE Where col1 between 1001 and 1500 Select count(*) from #Locationtable where substring(Location,2,1) <> 5
Ed ecco che il risultato è 0
La struttura dell'indice partizionato, nonchè i dati, sono contenuti nel filegroup di pertinenza determinato dall'engine attraverso il partitioning schema all'atto dell'inserimento del record stesso.
Nell'esempio ho utilizzato un'indice clustered ma lo stesso ragionamento è valido anche per gli indici non clustered creati in modo allineato.
Struttura Indici NON Allineati.
Creo ora un indice non clustered e NON allineato.
Per crearlo non allineato dobbiamo specificare un filegroup non compreso nel Partitioning schema MYPARTITIONSCHEME. In questo modo verrà creato all'interno del file associato al filegroup specificato, nel mio caso PRIMARY.
Create Index idxnP on MYPARTITIONTABLE(col3)ON[PRIMARY]
Vediamo la struttura.
Select
i.name
,pis.Index_type_desc
,pis.partition_number
,pis.index_depth
,pis.index_level
,pis.page_count
,pis.record_count
from sys.dm_db_index_physical_stats(db_id(),object_id('MYPARTITIONTABLE'),null,null,'DETAILED') pis
inner join sys.indexes i on i.object_id = pis.object_id and i.index_id = pis.index_id
where name = 'idxnP'
Order by pis.partition_number, pis.Index_level desc
Come potete vedere root level e leaf level sono contenuti nella medesima partizione ( colonna partition_number) ed il leaf level è composto da 6 data pages contenenti 2000 record.
L'intera struttura e l'intero set di dati dell'indice idxnP sono contenuti nel medesimo file del filegroup PRIMARY.
Recupero le pages che compongono l'indice
drop table #ind
Go
create table #ind
(
PageFID tinyint
,PagePID int
,IAMFID tinyint
,IAMPID int
,ObjectID int
,IndexID tinyint
,PartitionNumber tinyint
,PartitionID bigint
,iam_chain_type varchar(30)
,PageType tinyint
,IndexLevel tinyint
,NextPageFID tinyint
,NextPagePID int
,PrevPageFID tinyint
,PrevPagePID int
)
go
insert #ind exec ('DBCC IND (''DB_Test_Partition'', ''MYPARTITIONTABLE'', -1) with tableresults')
go
select i.name
,df.physical_name
,pageFID
,pagePID
,pageType
,indexLevel
,case when pageType = 10 then 'IAM'
when pageType = 2 then 'index page'
when pageType = 1 then 'data pages'
else 'non definito'
end as pageType, nextPagePID, prevPagePID
from #ind loc
Inner join sys.indexes i
on i.object_id = loc.ObjectID and i.index_id = loc.IndexID
Inner join Sys.database_files df
on loc.PageFID = df.file_id
Where i.name = 'idxnP'
order by isnull(indexLevel,255) desc
Le Index Pages, sia root che leaf level, sono interamente contenute nel file associato al filegroup PRIMARY all'interno del quale ho creato l'indice non partizionato/Allineato idxnP.
Un indice NON allineato/partizionato su una tabella partizionata potrebbe essere utile quando :
1.La ricerca non contiene la colonna di partizionamento
2.La tabella è coinvolta in join le cui condizioni non comprendono la colonna di partizionamento
Ciao
Luca
Nessun commento:
Posta un commento