SQL Server
Manutenzione del database semplificata con la partizione delle tabelle
Noah Gomez
Panoramica:
- Creazione di tabelle partizionate
- Aggiunta e unione di partizioni
- Creazione e gestione di indici partizionati
Scarica il codice per questo articolo: GomezPartitioning2007_03.exe (156KB)
In passato, gli amministratori di database che dovevano gestire tabelle contenenti milioni di righe di dati erano costretti a creare più tabelle. Una volta partizionate queste tabelle, l'amministratore doveva unirle
durante l'esecuzione di molte query. Unire le partizioni significava creare una vista partizionata oppure un wrapper per stored procedure in grado di intuire la posizione dei dati e di eseguire un'altra stored procedure solo per le partizioni necessarie in modo da restituire il dataset.
Sebbene questi metodi funzionassero, erano comunque molto complessi. L'amministrazione di tabelle multiple e dei relativi indici, così come i metodi utilizzati per cercare di tenerle insieme, creavano spesso problemi in termini di gestione. Inoltre, la creazione di tabelle multiple per partizionare i dati determinava un notevole livello di rigidità, dal momento che le stored procedure, le operazioni di manutenzione, le operazioni DTS (Data Transformation Services), il codice applicativo e gli altri processi dovevano riconoscere la natura del partizionamento. Quindi, per consentire all'amministratore di aggiungere o eliminare queste partizioni senza modificare il proprio codice, questi elementi venivano di regola creati in maniera non dinamica, pertanto erano inefficienti.
Le edizioni Enterprise e Developer di SQL Server™ 2005 consentono di dividere grandi quantità di dati contenuti in un'unica tabella in diverse partizioni più piccole che possono essere gestite in maniera più efficace. La capacità di creare segmenti di dati accessibili da un unico punto di entrata riduce molti dei problemi di amministrazione riscontrati con la procedura precedente. L'utilizzo di un unico punto di entrata (il nome della tabella o dell'indice) nasconde i segmenti dei dati multipli del codice applicativo e consente all'amministratore o allo sviluppatore di modificare le partizioni in caso di necessità senza dover modificare il codice di base.
In breve, è possibile creare più partizioni, spostarle, eliminare le meno recenti e perfino modificare il modo in cui i dati vengono partizionati senza dover modificare il codice applicativo. Il codice applicativo continua semplicemente a richiamare lo stesso nome di base di tabella o di indice. Nel frattempo, è possibile ridurre la quantità di dati contenuti nei singoli indici, diminuendo a sua volta i tempi di gestione di questi indici ed è possibile aumentare la velocità di caricamento dei dati in partizioni vuote.
Dal punto di vista tecnico, ogni tabella di SQL Server 2005 è partizionata, vale a dire dispone di almeno una partizione. In pratica, SQL Server 2005 consente agli amministratori di database di creare partizioni aggiuntive su ogni tabella. Le partizioni delle tabelle e degli indici sono partizioni fisse e a livello di riga (non è consentito il partizionamento per colonne) che consentono un solo punto di entrata (il nome della tabella o dell'indice) senza che il codice applicativo debba riconoscere il numero di partizioni oltre il punto di entrata. Le partizioni possono esistere sia sulla tabella di base che sugli indici associati alla tabella stessa.
Creazione di tabelle partizionate
Per creare una tabella in grado di crescere oltre la singola partizione predefinita, si utilizzano le funzioni e gli schemi di partizionamento. Questi oggetti consentono di dividere i dati in segmenti specifici e di controllare la posizione di questi dati in base al relativo progetto di archiviazione. Ad esempio, è possibile organizzare i dati su matrici di unità multiple in base alla relativa data di creazione o ad altri differenziatori comuni. Una tabella può essere partizionata in base a una colonna della tabella e ogni partizione deve contenere dati che non possono essere collocati in altre partizioni.
Funzioni di partizione Quando si effettua la partizione di una tabella, è necessario prima decidere in quale modo i dati verranno divisi in segmenti diversi. Una funzione di partizionamento viene utilizzata per associare le singole righe di dati in diverse partizioni. Queste singole righe di dati possono essere associate a qualunque tipo di colonna tranne text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), tipi di dati alias o tipi CLR definiti dall'utente. Comunque, la funzione di partizionamento deve consentire di collocare una riga di dati in un'unica partizione di tabelle: una riga di dati non può appartenere a più partizioni allo stesso tempo.
Per effettuare la partizione di una tabella, è necessario creare la colonna di partizionamento all'interno della tabella interessata. Questa colonna di partizionamento può esistere nello schema della tabella quando questa è già stata creata oppure è possibile modificare la tabella e aggiungere la colonna in seguito. La colonna può accettare i valori NULL ma tutte le righe contenenti questi valori saranno collocate per impostazione predefinita nella partizione più a sinistra della tabella. Per evitare che ciò accada, è necessario specificare che i valori NULL vengano collocati nella partizione più a destra della tabella durante la creazione della funzione di partizionamento. L'uso della partizione sinistra o destra è un'importante decisione perché si andrà a modificare lo schema di partizionamento e ad aggiungere più partizioni oppure a eliminare quelle esistenti.
Quando si crea una funzione di partizionamento, è possibile scegliere tra LEFT (sinistra) o RIGHT (destra). Una partizione LETF o RIGHT stabilirà il luogo in cui verrà posizionato l'intervallo di valori nello schema di partizionamento. Le partizioni LEFT (impostazione predefinita) includono l'intervallo di valori all'interno della partizione mentre le partizioni RIGHT collocano questo intervallo nella partizione successiva.
Per capire meglio questo concetto, verranno mostrate di seguito una partizione LEFT e una RIGHT:
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
CREATE PARTITION FUNCTION Right_Partition (int) AS RANGE RIGHT
FOR VALUES (1,10,100)
Nella prima funzione (Left_Partition), i valori 1, 10 e 100 sono collocati rispettivamente nella prima, seconda e terza partizione. Nella seconda funzione (Right_Partition), i valori sono collocati nella seconda, terza e quarta partizione.
Quando si crea una tabella partizionata, è importante garantire alle partizioni il livello più elevato di uniformità. In tal modo si potrà comprendere quanto spazio sarà necessario per una partizione. L'utilizzo di una partizione LEFT o RIGHT determina il luogo in cui i dati verranno collocati e, di conseguenza, determina anche la dimensione della partizione e la dimensione di ogni indice creato su quella partizione.
È possibile determinare il numero di partizioni in cui verrà collocato un valore dati tramite la funzione $PARTITION, come mostrato di seguito:
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Right_Partition (10)
Nella prima istruzione SELECT il risultato sarà 2. La seconda istruzione SELECT darà come risultato 3.
Schemi di partizione Dopo aver creato la funzione e deciso il modo in cui verranno divisi i dati, è necessario stabilire il luogo in cui le singole partizioni verranno create nel sottosistema del disco. Per creare questo layout del disco, si utilizzano gli schemi di partizione. Gli schemi di partizione gestiscono l'archiviazione su disco di partizioni individuali utilizzando filegroup per collocare ogni partizione in un punto del sottosistema del disco. È possibile configurare gli schemi di partizione in modo da collocare tutte le partizioni in un unico filegroup, in differenti filegroup oppure in filegroup che condividono più partizioni. Quest'ultimo metodo consente all'amministratore di database molta flessibilità nella propagazione dell'I/O del disco.
La Figura 1 illustra alcuni dei metodi con cui è possibile assegnare uno o più filegroup a uno schema di partizione. I filegroup utilizzati dallo schema di partizione devono essere già presenti all'interno del database prima di creare lo schema di partizione.
Figure 1 Assegnazione di filegroup a uno schema di partizione
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the different filegroups
CREATE PARTITION SCHEME Different_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup3, Filegroup4)
--Filegroups must currently exist in database
--Place multiple partitions into the different filegroups
CREATE PARTITION SCHEME Multiple_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup1, Filegroup2)
--Filegroups must currently exist in database
Se si creano le funzioni di partizione come mostrato nella Figura 1 e si utilizza lo schema di partizione per creare una tabella, è possibile determinare dove collocare le singole righe di dati nelle tabelle appena partizionate. Successivamente è possibile visualizzare la distribuzione di tali righe di dati dopo averle inserite nella tabella partizionata. Il codice da utilizzare per effettuare questa operazione sarà molto simile a quello illustrato nella Figura 2.
Figure 2 Posizionamento di righe di dati e visualizzazione della distribuzione
--Prepare database
IF OBJECT_ID('Partitioned_Table') IS NOT NULL
DROP TABLE Partitioned_Table
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
ALL TO ([PRIMARY])
CREATE TABLE Partitioned_Table
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Determine where values will be placed (this is not required)
--You should try to do this before executing the code
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (2)
SELECT $PARTITION.Left_Partition (3)
SELECT $PARTITION.Left_Partition (4)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (11)
SELECT $PARTITION.Left_Partition (12)
SELECT $PARTITION.Left_Partition (13)
SELECT $PARTITION.Left_Partition (14)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
SELECT $PARTITION.Left_Partition (102)
SELECT $PARTITION.Left_Partition (103)
SELECT $PARTITION.Left_Partition (104)
--Insert data into partitioned table
INSERT INTO Partitioned_Table VALUES (1,'Description')
INSERT INTO Partitioned_Table VALUES (2,'Description')
INSERT INTO Partitioned_Table VALUES (3,'Description')
INSERT INTO Partitioned_Table VALUES (4,'Description')
INSERT INTO Partitioned_Table VALUES (10,'Description')
INSERT INTO Partitioned_Table VALUES (11,'Description')
INSERT INTO Partitioned_Table VALUES (12,'Description')
INSERT INTO Partitioned_Table VALUES (13,'Description')
INSERT INTO Partitioned_Table VALUES (14,'Description')
INSERT INTO Partitioned_Table VALUES (100,'Description')
INSERT INTO Partitioned_Table VALUES (101,'Description')
INSERT INTO Partitioned_Table VALUES (102,'Description')
INSERT INTO Partitioned_Table VALUES (103,'Description')
INSERT INTO Partitioned_Table VALUES (104,'Description')
--View the distribution of data in the partitions
SELECT ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('Partitioned_Table')
Modifica delle tabelle partizionate
Malgrado un'attenta pianificazione, a volte è necessario regolare le tabelle partizionate dopo averle create e popolate. Lo schema di partizione può funzionare a dovere ma potrebbe essere necessario, ad esempio, aggiungere nuove partizioni man mano che si accumulano nuovi dati o, magari, eliminare grandi quantità di dati partizionati contemporaneamente. Fortunatamente, le tabelle partizionate e le strutture di partizionamento sottostanti consentono di effettuare modifiche dopo aver creato e riempito di dati le tabelle.
Aggiunta di partizioni Molti progetti di partizionamento consentono di aggiungere una nuova partizione in un secondo momento. Si potrebbe trattare di una particolare data oppure di una data dipendente da un valore presente in una colonna di identità incrementale. Pertanto, se non è stato stabilito in partenza quando aggiungere nuove partizioni ad una tabella partizionata è possibile farlo in seguito. Considerare la tabella creata nella Figura 2. È possibile aggiungere una nuova partizione a questa tabella per contenere valori maggiori di 500, come il seguente:
--Determine where values live before new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 4
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(500)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 5
La possibilità di aggiungere nuove partizioni offre grande flessibilità. La Figura 3 illustra come aggiungere una partizione sul lato sinistro della funzione. In questo caso, è necessario indicare allo schema di partizionamento dove inserire la nuova partizione poiché, quando è stato creato lo schema di partizionamento, sono stati utilizzati tutti i filegroup. Anche se si utilizza il filegroup PRIMARY per tutte le partizioni, è necessario indicare allo schema di partizionamento di riutilizzare il filegroup PRIMARY per la nuova partizione.
Figure 3 Aggiunta di una partizione al lato sinistro della funzione
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 2
--Add new filegroups to the partitioning scheme
ALTER PARTITION SCHEME Primary_Left_Scheme
NEXT USED [PRIMARY]
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(5)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 3
Unione di due partizioni SQL Server consente di eliminare singole partizioni da una tabella pur conservando i dati. Questa funzione può essere utilizzata per unire i dati attivi meno recenti con i dati archiviati oppure per ridurre il numero di partizioni a disposizione, semplificando la gestione della tabella partizionata. È possibile utilizzare questa funzione anche per unire più partizioni, per spostare dati da un filegroup all'altro per liberare spazio su disco in specifici array di unità. Il codice nella Figura 4 illustra come spostare i dati da una partizione all'altra nello stesso filegroup.
Figure 4 Spostamento di dati da una partizione all'altra
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
--Merge two partitions
ALTER PARTITION FUNCTION Left_Partition()
MERGE RANGE (10)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
Spostamento di una tabella di partizione in una tabella partizionata Durante le normali attività di caricamento, è spesso necessario caricare grandi quantità di dati nel database e modificarli o aggregarli prima che vengano spostati nella tabella di dati effettiva. Il partizionamento di SQL Server 2005 consente di spostare una singola tabella di partizione in una tabella con più partizioni. Ciò significa che è possibile caricare i dati in una singola tabella di caricamento, modificarli e spostare l'intera tabella in una tabella esistente senza dover spostare ogni singola riga di dati. Questo livello di partizionamento non implica la modifica della struttura di partizionamento sottostante, ma solo la modifica della tabella partizionata. Il codice nella Figura 5 illustra come effettuare l'operazione.
Figure 5 Spostamento di un'intera tabella in una tabella esistente
--Prepare database
IF OBJECT_ID(‘multiple_partition’) IS NOT NULL
DROP TABLE multiple_partition
GO
IF OBJECT_ID(‘single_partition’) IS NOT NULL
DROP TABLE single_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = ‘Primary_Left_Scheme’)
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = ‘Left_Partition’)
DROP PARTITION FUNCTION Left_Partition
GO
--Create single partition table
CREATE TABLE single_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
--Table must have a CHECK Constraint
ALTER TABLE single_partition
WITH CHECK
ADD CONSTRAINT CK_single_partition
CHECK (col1 > 100)
INSERT INTO single_partition VALUES (101,’Description’)
INSERT INTO single_partition VALUES (102,’Description’)
INSERT INTO single_partition VALUES (103,’Description’)
INSERT INTO single_partition VALUES (104,’Description’)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,’Description’)
INSERT INTO multiple_partition VALUES (2,’Description’)
INSERT INTO multiple_partition VALUES (3,’Description’)
INSERT INTO multiple_partition VALUES (4,’Description’)
INSERT INTO multiple_partition VALUES (10,’Description’)
INSERT INTO multiple_partition VALUES (11,’Description’)
INSERT INTO multiple_partition VALUES (12,’Description’)
INSERT INTO multiple_partition VALUES (13,’Description’)
INSERT INTO multiple_partition VALUES (14,’Description’)
INSERT INTO multiple_partition VALUES (100,’Description’)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_
ID(‘single_partition’))
--Move the single table into the partitioned table
ALTER TABLE single_partition SWITCH TO multiple_partition PARTITION 4
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_ID(‘single_partition’))
Spostamento di una partizione da una tabella all'altra Una comune attività amministrativa consiste nello spostare i dati meno recenti in tabelle di archivi separate. Il processo di archiviazione implica generalmente una serie di istruzioni che possono creare un utilizzo di risorse aggiuntivo nei log delle transazioni. Il passaggio della proprietà di una partizione da una tabella all'altra, comunque, è un metodo semplice per l'archiviazione di grandi quantità di dati senza l'overhead del log delle transazioni. Questa funzionalità consente all'amministratore di database di spostare i segmenti di dati meno recenti dalle tabelle attive alle tabelle archiviate. Ma poiché i dati non vengono effettivamente spostati, la quantità di tempo impiegato è decisamente inferiore rispetto a quello necessario per spostare singole righe di dati. La Figura 6 mostra come effettuare questa operazione.
Figure 6 Spostamento di dati meno recenti in tabelle archiviate
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Archive_Scheme')
DROP PARTITION SCHEME Archive_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Archive_Partition')
DROP PARTITION FUNCTION Archive_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Create archive function
CREATE PARTITION FUNCTION Archive_Partition (int) AS RANGE LEFT
FOR VALUES (100,200,300)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Archive_Scheme
AS PARTITION Archive_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Archive_Scheme (col1)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
INSERT INTO archive_data VALUES (200,'Description')
INSERT INTO archive_data VALUES (300,'Description')
INSERT INTO archive_data VALUES (400,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data PARTITION 1
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
Utilizzo di una singola partizione per creare una nuova tabella È possibile spostare una singola partizione da una tabella partizionata esistente in una tabella vuota non partizionata. In tal modo, un amministratore di database è in grado di eseguire la manutenzione degli indici sulla singola partizione o eliminare con facilità grandi quantità di dati senza registrare il processo di eliminazione. L'esempio nella Figura 7 mostra come spostare una partizione in una tabella vuota e quindi utilizzare questa nuova tabella per eliminare i dati.
Figure 7 Spostamento ed eliminazione di dati
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Drop all archive data without logging
DROP TABLE archive_data
GO
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
Indici partizionati
Oltre alla possibilità di partizionare i dati di una tabella è possibile anche creare indici partizionati. Questa procedura consente all'amministratore di database di progettare la struttura degli indici in base ai dati divisi e non ai dati dell'intera tabella. La creazione di indici partizionati conduce alla formazione di singole strutture B-trees sugli indici partizionati. La divisione degli indici provoca la creazione di indici più piccoli e più facilmente gestibili dal motore di archiviazione durante la modifica, l'aggiunta e l'eliminazione di dati. Questi indici più piccoli possono essere gestiti anche individualmente dall'amministratore di database, consentendo quindi una migliore gestione degli indici su dataset di grandi dimensioni.
Creazione di indici partizionati Quando si creano indici partizionati è possibile creare indici allineati o non allineati. Nel primo caso l'indice viene creato con un collegamento diretto ai dati partizionati. (Per gli indici non allineati, scegliere uno schema di partizionamento differente).
Se si crea la tabella di partizione e poi gli indici, senza specificare uno schema di partizionamento diverso, il metodo preferito, eseguito automaticamente, è quello che prevede la creazione di indici allineati. L'utilizzo di indici allineati garantisce la flessibilità di creare partizioni aggiuntive sulla tabella e la capacità di trasferire la proprietà di una partizione a un'altra tabella. Queste funzionalità sono spesso la ragione principale per cui gli amministratori di database creano tabelle partizionate, pertanto l'utilizzo dello schema di partizionamento della tabella per i relativi indici è il metodo ideale per raggiungere gli obiettivi prefissati.
È possibile creare degli indici in tabelle in cui i dati dell'indice non sono allineati con i dati della tabella. Se i dati sono posizionati in una tabella partizionata, questo procedimento consente di unire i dati in diversi modi (i dati partizionati possono essere uniti in modo efficace ad altri dati partizionati tramite il Query Optimizer). Altrimenti, è possibile utilizzare una tabella non partizionata che consente di creare un indice partizionato (su una singola tabella di partizione) in modo da poter effettuare con facilità la manutenzione degli indici.
Il codice nella Figura 8 crea un indice partizionato ma non raggruppato in una tabella partizionata. L'indice non raggruppato verrà allineato con la tabella e utilizzerà la colonna di partizionamento della tabella come chiave di indice non raggruppata.
Figure 8 Indice partizionato e non raggruppato su una tabella partizionata
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Verify index partitions
SELECT partition_id, index_id FROM sys.partitions pt
WHERE pt.[object_id] = OBJECT_ID('multiple_partition')
Il codice nella Figura 9 crea un indice non allineato e non raggruppato in una tabella partizionata. Questo indice non raggruppato utilizzerà colonne diverse per la relativa chiave che può essere utilizzata per confrontare le unioni su altre tabelle partizionate.
Figure 9 Indice non allineato e non raggruppato su una tabella partizionata
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Index_primary_Left_Scheme')
DROP PARTITION SCHEME Index_primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Index_Left_Partition')
DROP PARTITION FUNCTION Index_Left_Partition
GO
--Create partitioned index function
CREATE PARTITION FUNCTION Index_Left_Partition (int) AS RANGE LEFT
FOR VALUES (10,50,100)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all index partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Index_primary_Left_Scheme
AS PARTITION Index_Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 INT
) ON Primary_Left_Scheme (col1)
--Create non-aligned partitioned nonclustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col2)
ON Index_primary_Left_Scheme (col2)
INSERT INTO multiple_partition VALUES (1,10)
INSERT INTO multiple_partition VALUES (2,10)
INSERT INTO multiple_partition VALUES (3,10)
INSERT INTO multiple_partition VALUES (4,10)
INSERT INTO multiple_partition VALUES (10,50)
INSERT INTO multiple_partition VALUES (11,50)
INSERT INTO multiple_partition VALUES (12,50)
INSERT INTO multiple_partition VALUES (13,50)
INSERT INTO multiple_partition VALUES (14,50)
INSERT INTO multiple_partition VALUES (100,100)
INSERT INTO multiple_partition VALUES (101,100)
INSERT INTO multiple_partition VALUES (102,100)
INSERT INTO multiple_partition VALUES (103,100)
INSERT INTO multiple_partition VALUES (104,100)
--Verify row count on partitioned data
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id = 0
--Verify row count on partitioned index
--Row counts will not match those found in the data partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id <> 0
Gestione di indici partizionati In passato, la manutenzione di indici su tabelle di grandi dimensioni contenenti milioni o anche miliardi di righe di dati spesso richiedeva più tempo di quello che gli amministratori di database avevano a disposizione. La manutenzione spesso non veniva eseguita perché i dati erano bloccati mentre l'indice veniva ricreato. Con SQL Server 2005, l'amministratore di database può eseguire la manutenzione degli indici in linea senza bloccare la tabella sottostante per un lungo periodo di tempo. Ma anche quest'approccio (che consiste nell'eseguire la manutenzione degli indici durante l'accesso degli utenti ai dati) può rallentare il sistema a causa del grande utilizzo di risorse. Un approccio migliore consiste nel partizionare gli indici in segmenti più piccoli ed esegue poi la manutenzione degli indici su quelle partizioni più piccole. Ad esempio, per eseguire la manutenzione degli indici su una partizione è possibile aggiungere semplicemente il frammento di codice alla fine del codice illustrato nella Figura 8.
ALTER INDEX cl_multiple_partition
ON multiple_partition
REBUILD Partition = 2
Si noti che questa manutenzione degli indici su singole partizioni deve essere eseguita non in linea e può provocare il blocco della tabella. Per evitare che ciò avvenga è possibile spostare la singola partizione in una partizione separata, eseguire la manutenzione degli indici e riportare la partizione nella tabella principale. Questo processo causerà dei problemi di prestazioni dal momento che la partizione viene riportata nella tabella e l'indice raggruppato viene aggiornato, ma risulta meno problematico rispetto al blocco di un'intera tabella e richiede minori risorse di sistema.
Riepilogo
Come si può notare, il partizionamento delle tabelle con SQL Server 2005 offre una maggiore flessibilità per l'archiviazione e la gestione di dati in tabelle di grandi dimensioni senza dover rielaborare il codice applicativo o i processi di SQL Server. Con queste capacità, SQL Server si presenta come una piattaforma efficace per la gestione di database aziendali importanti.
Noah Gomez è un amministratore di database senior di Verizon esperto in VLDB e applicazioni su larga scala. È membro dell'Associazione Professionale di SQL Server (PASS) e ha fatto parte del team di amministratori di database di Verazon che ha lavorato sui VLDB di svariati terabyte vincendo nel 2003 il Winter Corp. Top Ten Grand Prize.
© 2008 Microsoft Corporation e CMP Media, LLC. Tutti i diritti riservati. È vietata la riproduzione completa o parziale senza autorizzazione.