Database Reference
In-Depth Information
You can also reduce internal fragmentation by compressing more rows within a page, reducing free spaces within
the pages. The maximum amount of compression that can be done within the leaf pages of an index is controlled by
the fill factor, as you will see next.
When dealing with large databases and the indexes associated, it may become necessary to split up the tables
and the indexes across disks using partitioning. Indexes on partitions can also become fragmented as the data within
the partition changes. When dealing with a portioned index, you will need to determine whether you want to either
REORGANIZE
or
REBUILD
one, some, or all partitions as part of the
ALTER INDEX
command. Partitioned indexes cannot
be rebuilt online. Keep in mind that doing anything that affects all partitions is likely to be a costly operation.
If compression is specified on an index, even on a partitioned index, you must be sure to set the compression
while performing the
ALTER INDEX
operation to what it was before; if you don't, it will be lost, and you'll have to
rebuild the index again. This is especially important for nonclustered indexes, which will not inherit the compression
setting from the table.
Defragmentation and Partitions
If you have massive databases, a standard mechanism for effectively managing the data is to break it up into
partitions. While partitions can, in some rare cases, help with performance, they are first and foremost for managing
data. But, one of the issues with indexes and partitions is that if you rebuild the index, it's unavailable during the
rebuild. This means that with partitions, which are on massive indexes, you can expect to have a major portion of your
data offline during the rebuild. SQL Server 2012 introduced the ability to do an online rebuild. If you had a partitioned
index, it would look like this:
ALTER INDEX i1 ON dbo.Test1
REBUILD PARTITION = ALL
WITH (ONLINE = ON);
This can rebuild the entire partition and do it as an online operation, meaning it keeps the index largely available
while it does the rebuild. But, for some partitions, this is a massive undertaking that will probably result in excessive
load on the server and the need for a lot more
tempdb
storage. SQL Server 2014 introduced new functionality that lets
you designate individual partitions.
ALTER INDEX i1 ON dbo.Test1
REBUILD PARTITION = 1
WITH (ONLINE = ON);
This reduces the overhead of the rebuild operation while still keeping the index mostly available during the
rebuild. I do emphasize that it is “mostly” online because there is still some degree of locking and contention that will
occur during the rebuild. It's not a completely free operation. It's just radically improved over the alternative.
Talking about the locking involved with index rebuild operations in partitions, you also have one other new
piece of functionality introduced in SQL Server 2014. You can now modify the lock priority used during the rebuild
operation by again adjusting the
REBUILD
command.
ALTER INDEX i1 ON dbo.Test1
REBUILD P ARTITION = 1
WITH (ONLINE = ON(WAIT_AT_LOW_PRIORITY(MAXDURATION=20,ABORT_AFTER_WAIT=SELF)));
What this does is set the duration that the rebuild operation is willing to wait, in minutes. Then, it allows you to
determine which processes get aborted in order to clear the system for the index rebuild. You can have it stop itself or
the blocking process. The most interesting thing is that the waiting process is set to low priority, so it's not using a lot
of system resources, and any transactions that come in won't be blocked by this process.
Between these two new pieces of functionality, the management of indexes in partitions is much easier with
SQL Server 2014.
