Tabelle e indici partizionatiPartitioned Tables and Indexes

SQL ServerSQL Server supporta il partizionamento di tabelle e indici. supports table and index partitioning. I dati di tabelle e indici partizionati vengono divisi in unità distribuibili tra più filegroup in un database.The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. I dati sono partizionati in senso orizzontale, in modo che per gruppi di righe venga eseguito il mapping in singole partizioni.The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. Tutte le partizioni di un singolo indice o di una singola tabella devono trovarsi nello stesso database.All partitions of a single index or table must reside in the same database. La tabella o indice viene gestito come singola entità logica quando si eseguono query o aggiornamenti sui dati.The table or index is treated as a single logical entity when queries or updates are performed on the data. Nelle versioni precedenti a SQL Server 2016SQL Server 2016 SP1, le tabelle e gli indici partizionati sono disponibili solo in alcune edizioni di SQL ServerSQL Server.Prior to SQL Server 2016SQL Server 2016 SP1, partitioned tables and indexes were not available in every edition of SQL ServerSQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL ServerSQL Server, vedere Edizioni e funzionalità supportate per SQL Server 2016.For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

Importante

Per impostazione predefinita, in SQL Server 2017SQL Server 2017 viene supportato un massimo di 15.000 partizioni. SQL Server 2017SQL Server 2017 supports up to 15,000 partitions by default. Nelle versioni precedenti di SQL Server 2012SQL Server 2012, il numero di partizioni è stato limitato a 1.000 per impostazione predefinita. Nei sistemi x86, creare una tabella o un indice con più di 1000 partizioni è possibile, ma non è supportato.In versions earlier than SQL Server 2012SQL Server 2012, the number of partitions was limited to 1,000 by default.On x86-based systems, creating a table or index with more than 1000 partitions is possible, but is not supported.

Vantaggi del partizionamentoBenefits of Partitioning

Il partizionamento di tabelle o indici di grandi dimensioni può offrire i vantaggi in termini di gestibilità e prestazioni descritti di seguito.Partitioning large tables or indexes can have the following manageability and performance benefits.

  • È possibile trasferire o accedere a subset di dati in modo rapido ed efficiente, salvaguardando al contempo l'integrità della raccolta di dati.You can transfer or access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. Operazioni quali il caricamento di dati da un sistema OLTP a un sistema OLAP richiedono ad esempio solo pochi secondi invece che minuti o addirittura ore necessari invece quando i dati non sono partizionati.For example, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes when the data is not partitioned.

  • È possibile eseguire più rapidamente operazioni di manutenzione su una o più partizioni.You can perform maintenance operations on one or more partitions more quickly. Le operazioni risultano più efficienti perché vengono applicate solo a subset di dati e non all'intera tabella.The operations are more efficient because they target only these data subsets, instead of the whole table. È ad esempio possibile scegliere di comprimere i dati in una o più partizioni oppure ricompilare una o più partizioni di un indice.For example, you can choose to compress data in one or more partitions or rebuild one or more partitions of an index.

  • È possibile ottenere migliori prestazioni con le query in base alle tipologie eseguite con maggiore frequenza e alla configurazione hardware in uso.You may improve query performance, based on the types of queries you frequently run and on your hardware configuration. Ad esempio, Query Optimizer è in grado di elaborare query di tipo equijoin tra due o più tabelle partizionate in modo più rapido quando le colonne di partizionamento nelle tabelle corrispondono, in quanto è possibile unire in join le partizioni stesse.For example, the query optimizer can process equi-join queries between two or more partitioned tables faster when the partitioning columns in the tables are the same, because the partitions themselves can be joined.

    Quando in SQL ServerSQL Server viene eseguito l'ordinamento dei dati per le operazioni di I/O, i dati vengono innanzitutto ordinati in base alla partizione.When SQL ServerSQL Server performs data sorting for I/O operations, it sorts the data first by partition. SQL ServerSQL Server accede a un'unità per volta, il che può comportare una riduzione delle prestazioni. accesses one drive at a time, and this might reduce performance. Per migliorare le prestazioni di ordinamento dei dati, eseguire lo striping dei file di dati delle partizioni tra più dischi configurando un sistema RAID.To improve data sorting performance, stripe the data files of your partitions across more than one disk by setting up a RAID. In questo modo, benché tramite SQL ServerSQL Server i dati vengano comunque ordinati in base alla partizione, è possibile accedere a tutte le unità di ogni partizione simultaneamente.In this way, although SQL ServerSQL Server still sorts data by partition, it can access all the drives of each partition at the same time.

    È inoltre possibile migliorare le prestazioni abilitando l'escalation blocchi a livello di partizione invece che di intera tabella.In addition, you can improve performance by enabling lock escalation at the partition level instead of a whole table. Ciò consente di ridurre gli effetti di contesa dei blocchi per la tabella.This can reduce lock contention on the table.

Componenti e concettiComponents and Concepts

I termini seguenti sono applicabili al partizionamento di tabelle e indici.The following terms are applicable to table and index partitioning.

Funzione di partizionePartition function
Oggetto di database che definisce la modalità con cui viene eseguito il mapping delle righe di una tabella o di un indice a un set di partizioni in base ai valori di una determinata colonna, denominata colonna di partizionamento.A database object that defines how the rows of a table or index are mapped to a set of partitions based on the values of certain column, called a partitioning column. Ovvero, la funzione di partizione definisce il numero di partizioni che la tabella avrà e come sono definiti i limiti delle partizioni.That is, the partition function defines the number of partitions that the table will have and how the boundaries of the partitions are defined. Ad esempio, data una tabella che contiene i dati degli ordini di vendita, si può decidere di partizionare la tabella in dodici partizioni (mensili) basate su una colonna di tipo datetime , come ad esempio una data di vendita.For example, given a table that contains sales order data, you may want to partition the table into twelve (monthly) partitions based on a datetime column such as a sales date.

Schema di partizionePartition scheme
Oggetto di database che mappa le partizioni di una funzione di partizione a un set di filegroup.A database object that maps the partitions of a partition function to a set of filegroups. Il principale motivo per cui inserire le partizioni in filegroup separati consiste nel fatto che in tal modo è possibile eseguire operazioni di backup nelle partizioni in modo indipendente,The primary reason for placing your partitions on separate filegroups is to make sure that you can independently perform backup operations on partitions. in quanto è possibile eseguire backup in filegroup singoli.This is because you can perform backups on individual filegroups.

Colonna di partizionamentoPartitioning column
Colonna di una tabella o di un indice utilizzata da una funzione di partizione per partizionare la tabella o l'indice.The column of a table or index that a partition function uses to partition the table or index. Le colonne calcolate che partecipano a una funzione di partizione devono essere contrassegnate in modo esplicito come PERSISTED.Computed columns that participate in a partition function must be explicitly marked PERSISTED. È possibile utilizzare come colonna di partizionamento tutti i tipi di dati che possono essere utilizzati come colonne di indice, eccetto il tipo di dati timestamp.All data types that are valid for use as index columns can be used as a partitioning column, except timestamp. Non è possibile specificare i tipi di dati ntext, text, image, xml, varchar(max), nvarchar(max)o varbinary(max) .The ntext, text, image, xml, varchar(max), nvarchar(max), or varbinary(max) data types cannot be specified. Inoltre, non è possibile specificare colonne di tipo definito dall'utente Common Language Runtime (CLR) di Microsoft .NET Framework né colonne di tipo di dati alias.Also, Microsoft .NET Framework common language runtime (CLR) user-defined type and alias data type columns cannot be specified.

Indice allineatoAligned index
Indice basato sullo stesso schema di partizione della relativa tabella corrispondente.An index that is built on the same partition scheme as its corresponding table. Se una tabella e i relativi indici sono allineati, in SQL Server è possibile cambiare le partizioni in modo rapido ed efficiente, mantenendo inalterata la struttura della tabella e degli indici.When a table and its indexes are in alignment, SQL Server can switch partitions quickly and efficiently while maintaining the partition structure of both the table and its indexes. Per poter essere allineato alla relativa tabella di base, non è necessario che un indice sia inserito nella stessa funzione di partizione denominata.An index does not have to participate in the same named partition function to be aligned with its base table. Le funzioni di partizione dell'indice e della tabella di base devono tuttavia condividere alcune caratteristiche, ad esempio i tipi di dati degli argomenti devono essere uguali, il numero di partizioni definito deve corrispondere e i valori limite delle partizioni devono essere uguali.However, the partition function of the index and the base table must be essentially the same, in that 1) the arguments of the partition functions have the same data type, 2) they define the same number of partitions, and 3) they define the same boundary values for partitions.

Indice non allineatoNonaligned index
Indice partizionato in modo indipendente rispetto alla relativa tabella corrispondente.An index partitioned independently from its corresponding table. Ciò significa che l'indice presenta uno schema di partizione diverso oppure che si trova in un filegroup separato rispetto alla tabella di base.That is, the index has a different partition scheme or is placed on a separate filegroup from the base table. La progettazione di un indice partizionato non allineato può risultare utile nei casi seguenti:Designing an nonaligned partitioned index can be useful in the following cases:

  • La tabella di base non è stata partizionata.The base table has not been partitioned.

  • La chiave dell'indice è univoca e non contiene la colonna di partizionamento della tabella.The index key is unique and it does not contain the partitioning column of the table.

  • Si desidera che la tabella di base sia inserita in join collocati con più tabelle che utilizzano colonne di join diverse.You want the base table to participate in collocated joins with more tables using different join columns.

    Eliminazione di partizioni Processo mediante il quale Query Optimizer accede solo alle partizioni rilevanti per soddisfare i criteri di filtro della query.Partition elimination The process by which the query optimizer accesses only the relevant partitions to satisfy the filter criteria of the query.

Linee guida relative alle prestazioniPerformance Guidelines

Il nuovo limite massimo di 15.000 partizioni influisce sulla memoria, sulle operazioni degli indici partizionati, sui comandi DBCC e sulle query.The new, higher limit of 15,000 partitions affects memory, partitioned index operations, DBCC commands, and queries. In questa sezione vengono descritte le implicazioni relative alle prestazioni determinate dall'aumento del numero di partizioni al di sopra di 1000 e vengono illustrate soluzioni alternative a seconda delle necessità.This section describes the performance implications of increasing the number of partitions above 1,000 and provides workarounds as needed. Con l'aumento del limite al numero massimo di partizioni fino a 15.000, è possibile archiviare dati per periodi prolungati.With the limit on the maximum number of partitions being increased to 15,000, you can store data for a longer time. Tuttavia, è consigliabile mantenere i dati solo per il tempo strettamente necessario e preservare l'equilibrio tra livello di prestazioni e numero di partizioni.However, you should retain data only for as long as it is needed and maintain a balance between performance and number of partitions.

Linee guida sui core del processore e sul numero di partizioniProcessor Cores and Number of Partitions Guidelines

Per ottimizzare le prestazioni con operazioni parallele, è consigliabile usare lo stesso numero di partizioni come core del processore, fino a un massimo di 64 (che è il numero massimo di processori paralleli utilizzabili da SQL Server).To maximize performance with parallel operations, we recommend that you use the same number of partitions as processor cores, up to a maximum of 64 (which is the maximum number of parallel processors that SQL Server can utilize).

Utilizzo della memoria e linee guidaMemory Usage and Guidelines

È consigliabile disporre di almeno 16 GB di RAM se si utilizza un numero elevato di partizioni.We recommend that you use at least 16 GB of RAM if a large number of partitions are in use. Se il sistema non dispone di memoria sufficiente, le istruzioni DML (Data Manipulation Language) e DDL (Data Definition Language) nonché altri tipi di operazioni potrebbero avere esito negativo.If the system does not have enough memory, Data Manipulation Language (DML) statements, Data Definition Language (DDL) statements and other operations can fail due to insufficient memory. Nei sistemi provvisti di 16 GB di RAM in cui vengono eseguiti numerosi processi che richiedono un'elevata quantità di memoria, quest'ultima potrebbe esaurirsi in caso di operazioni su un numero elevato di partizioni.Systems with 16 GB of RAM that run many memory-intensive processes may run out of memory on operations that run on a large number of partitions. Pertanto, la probabilità che si verifichino problemi di prestazioni o di memoria si riduce in funzione della quantità di memoria disponibile oltre i 16 GB.Therefore, the more memory you have over 16 GB, the less likely you are to encounter performance and memory issues.

Le limitazioni relative alla memoria possono influire sulle prestazioni o sulla capacità stessa di compilare un indice partizionato in SQL Server,Memory limitations can affect the performance or ability of SQL Server to build a partitioned index. specialmente se l'indice non è allineato alla relativa tabella di base o al relativo indice cluster eventualmente applicato.This is especially the case when the index is not aligned with its base table or is not aligned with its clustered index, if the table already has a clustered index applied to it.

Operazioni relative agli indici partizionatiPartitioned Index Operations

Le limitazioni relative alla memoria possono influire sulle prestazioni o sulla capacità stessa di compilare un indice partizionato in SQL Server,Memory limitations can affect the performance or ability of SQL Server to build a partitioned index. specialmente del caso di indici non allineati.This is especially the case with nonaligned indexes. La creazione e la ricompilazione di indici non allineati per una tabella con oltre 1.000 partizioni sono possibili, ma non supportate.Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but is not supported. Questo tipo di operazioni può causare riduzioni delle prestazioni e un eccessivo consumo della memoria.Doing so may cause degraded performance or excessive memory consumption during these operations.

La creazione e la ricompilazione di indici allineati possono richiedere più tempo a seconda del numero di partizioni.Creating and rebuilding aligned indexes could take longer to execute as the number of partitions increases. È consigliabile non eseguire più comandi di creazione e ricompilazione degli indici contemporaneamente poiché potrebbero verificarsi problemi di prestazioni e memoria.We recommend that you do not run multiple create and rebuild index commands at the same time as you may run into performance and memory issues.

Quando in SQL Server viene eseguito l'ordinamento per compilare indici partizionati, viene innanzitutto creata una tabella di ordinamento per ciascuna partizione.When SQL Server performs sorting to build partitioned indexes, it first builds one sort table for each partition. Compila quindi le tabelle di ordinamento nel filegroup di ogni partizione o in tempdbse viene specificata l'opzione per gli indici SORT_IN_TEMPDB.It then builds the sort tables either in the respective filegroup of each partition or in tempdb, if the SORT_IN_TEMPDB index option is specified. Per poter compilare una tabella di ordinamento, è necessaria una quantità di memoria minima che varia in base alla tabella.Each sort table requires a minimum amount of memory to build. Quando si compila un indice partizionato allineato alla relativa tabella di base, le tabelle di ordinamento vengono compilate una alla volta e la quantità di memoria necessaria è minore.When you are building a partitioned index that is aligned with its base table, sort tables are built one at a time, using less memory. Quando invece si compila un indice partizionato non allineato, le tabelle di ordinamento vengono compilate simultaneamente.However, when you are building a nonaligned partitioned index, the sort tables are built at the same time. È pertanto necessaria una quantità di memoria sufficiente a gestire simultaneamente tali ordinamenti.As a result, there must be sufficient memory to handle these concurrent sorts. Maggiore è il numero di partizioni e maggiore sarà la quantità di memoria necessaria.The larger the number of partitions, the more memory required. La dimensione minima di ogni tabella di ordinamento per ogni partizione è di 40 pagine, ognuna delle quali contiene 8 kilobyte.The minimum size for each sort table, for each partition, is 40 pages, with 8 kilobytes per page. Ad esempio, per un indice partizionato non allineato con 100 partizioni è necessaria una quantità di memoria sufficiente per ordinare simultaneamente in modo seriale 4.000 (40 * 100) pagine.For example, a nonaligned partitioned index with 100 partitions requires sufficient memory to serially sort 4,000 (40 * 100) pages at the same time. Se la memoria è disponibile, l'indice verrà compilato anche se è possibile che l'operazione influisca negativamente sulle prestazioni.If this memory is available, the build operation will succeed, but performance may suffer. Se la memoria non è disponibile, l'indice non verrà compilato.If this memory is not available, the build operation will fail. Per un indice partizionato allineato con 100 partizioni è invece necessaria solo la memoria per l'ordinamento di 40 pagine, perché gli ordinamenti non vengono eseguiti simultaneamente.Alternatively, an aligned partitioned index with 100 partitions requires only sufficient memory to sort 40 pages, because the sorts are not performed at the same time.

È possibile che i requisiti di memoria per gli indici allineati e non allineati siamo maggiori se SQL Server applica gradi di parallelismo alla compilazione in un computer multiprocessore.For both aligned and nonaligned indexes, the memory requirement can be greater if SQL Server is applying degrees of parallelism to the build operation on a multiprocessor computer. Ciò dipende dal fatto che maggiori sono i gradi di parallelismo, maggiore sarà la quantità di memoria richiesta.This is because the greater the degrees of parallelism, the greater the memory requirement. Se ad esempio SQL Server imposta i gradi di parallelismo su 4, per un indice partizionato non allineato con 100 partizioni sarà necessaria una quantità di memoria che consenta a quattro processori di ordinare simultaneamente 4.000 pagine, ovvero 16.000 pagine in totale.For example, if SQL Server sets degrees of parallelism to 4, a nonaligned partitioned index with 100 partitions requires sufficient memory for four processors to sort 4,000 pages at the same time, or 16,000 pages. Se l'indice partizionato è allineato, la quantità di memoria si riduce alla quantità necessaria a quattro processori per ordinare 40 pagine, ovvero 160 (4 * 40) pagine.If the partitioned index is aligned, the memory requirement is reduced to four processors sorting 40 pages, or 160 (4 * 40) pages. Per ridurre manualmente i gradi di parallelismo, è possibile utilizzare l'opzione di indice MAXDOP.You can use the MAXDOP index option to manually reduce the degrees of parallelism.

Comandi DBCCDBCC Commands

In presenza di un numero elevato di partizioni, l'esecuzione di comandi DBCC può richiedere un tempo proporzionalmente maggiore.With a larger number of partitions, DBCC commands could take longer to execute as the number of partitions increases.

QueryQueries

Le query per le quali si utilizza l'eliminazione di partizioni possono offrire prestazioni analoghe o migliorate con un numero elevato di partizioni.Queries that use partition elimination could have comparable or improved performance with larger number of partitions. L'esecuzione di query che non utilizzano l'eliminazione di partizioni può richiedere più tempo a seconda del numero di partizioni.Queries that do not use partition elimination could take longer to execute as the number of partitions increases.

Si supponga ad esempio che una tabella contenga 100 milioni di righe e le colonne A, Be C.For example, assume a table has 100 million rows and columns A, B, and C. Nello scenario 1 la tabella è divisa in 1000 partizioni per la colonna A.In scenario 1, the table is divided into 1000 partitions on column A. Nello scenario 2, la tabella è divisa in 10.000 partizioni per la colonna A.In scenario 2, the table is divided into 10,000 partitions on column A. Una query eseguita su tale tabella con clausola WHERE per filtrare la colonna A utilizza l'eliminazione di partizioni ed analizza una partizione.A query on the table that has a WHERE clause filtering on column A will perform partition elimination and scan one partition. Se eseguita nello scenario 2, la stessa query risulta più rapida in quanto è presente un numero minore di righe da analizzare in una partizione.That same query may run faster in scenario 2 as there are fewer rows to scan in a partition. Una query con clausola WHERE per filtrare la colonna B analizza tutte le partizioni.A query that has a WHERE clause filtering on column B will scan all partitions. Nello scenario 1, tale query viene eseguita in meno tempo rispetto allo scenario 2 in quanto sono presenti meno partizioni da analizzare.The query may run faster in scenario 1 than in scenario 2 as there are fewer partitions to scan.

Le query che utilizzano operatori quali TOP o MAX/MIN su colonne diverse dalla colonna di partizionamento possono comportare prestazioni ridotte con il partizionamento perché tutte le partizioni devono essere valutate.Queries that use operators such as TOP or MAX/MIN on columns other than the partitioning column may experience reduced performance with partitioning because all partitions must be evaluated.

Modifiche di comportamento nel calcolo delle statistiche durante operazioni su indici partizionatiBehavior Changes in Statistics Computation During Partitioned Index Operations

A partire da SQL Server 2012SQL Server 2012le statistiche non vengono create analizzando tutte le righe nella tabella se viene creato o ricompilato un indice partizionato.Beginning with SQL Server 2012SQL Server 2012, statistics are not created by scanning all the rows in the table when a partitioned index is created or rebuilt. Query Optimizer utilizza invece l'algoritmo di campionamento predefinito per generare statistiche.Instead, the query optimizer uses the default sampling algorithm to generate statistics. Dopo avere aggiornato un database con gli indici partizionati, è possibile notare una differenza nei dati dell'istogramma relativamente a tali indici.After upgrading a database with partitioned indexes, you may notice a difference in the histogram data for these indexes. Tale cambiamento potrebbe non influire sulle prestazioni di query.This change in behavior may not affect query performance. Per ottenere statistiche sugli indici partizionati analizzando tutte le righe nella tabella, utilizzare CREATE STATISTICS o UPDATE STATISTICS con la clausola FULLSCAN.To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

AttivitàTasks ArgomentoTopic
Viene illustrato come creare funzioni e schemi di partizione e quindi applicarli a una tabella e a un indice.Describes how to create partition functions and partition schemes and then apply these to a table and index. Creare tabelle e indici partizionatiCreate Partitioned Tables and Indexes

I seguenti white paper sulle strategie e le implementazioni relative a tabelle e indici partizionati possono risultare particolarmente utili (le informazioni potrebbero essere in lingua inglese).You may find the following white papers on partitioned table and index strategies and implementations useful.