Partitioned Index OperationsPartitioned Index Operations
DBCC CommandsDBCC Commands
QueriesQueries
Therefore, the more memory you have over 16 GB, the less likely you are to encounter performance and memory
issues.
Memory limitations can affect the performance or ability of SQL Server to build a partitioned index. 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.
Memory limitations can affect the performance or ability of SQL Server to build a partitioned index. This is
especially the case with nonaligned indexes. Creating and rebuilding nonaligned indexes on a table with more than
1,000 partitions is possible, but is not supported. Doing so may cause degraded performance or excessive memory
consumption during these operations.
Creating and rebuilding aligned indexes could take longer to execute as the number of partitions increases. 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.
When SQL Server performs sorting to build partitioned indexes, it first builds one sort table for each partition. 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. Each sort table requires a minimum amount of memory to build. 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. However, when you are building a nonaligned partitioned index, the sort tables are built at the same time.
As a result, there must be sufficient memory to handle these concurrent sorts. The larger the number of partitions,
the more memory required. The minimum size for each sort table, for each partition, is 40 pages, with 8 kilobytes
per page. For example, a nonaligned partitioned index with 100 partitions requires sufficient memory to serially
sort 4,000 (40 * 100) pages at the same time. If this memory is available, the build operation will succeed, but
performance may suffer. If this memory is not available, the build operation will fail. 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.
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. This is because the greater the degrees
of parallelism, the greater the memory requirement. 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. If the partitioned index is aligned, the memory requirement is reduced to
four processors sorting 40 pages, or 160 (4 * 40) pages. You can use the MAXDOP index option to manually reduce
the degrees of parallelism.
With a larger number of partitions, DBCC commands could take longer to execute as the number of partitions
increases.
Queries that use partition elimination could have comparable or improved performance with larger number of
partitions. Queries that do not use partition elimination could take longer to execute as the number of partitions
increases.
For example, assume a table has 100 million rows and columns A , B , and C . In scenario 1, the table is divided
into 1000 partitions on column A . In scenario 2, the table is divided into 10,000 partitions on column A . A query
on the table that has a WHERE clause filtering on column A will perform partition elimination and scan one
partition. That same query may run faster in scenario 2 as there are fewer rows to scan in a partition. A query that
has a WHERE clause filtering on column B will scan all partitions. The query may run faster in scenario 1 than in
scenario 2 as there are fewer partitions to scan.
Queries that use operators such as TOP or MAX/MIN on columns other than the partitioning column may
评论0
最新资源