You can obtain more flexibility and control by creating your own custom maintenance plans using T-SQL or PowerShell scripts. For example, you can measure and track fragmentation using sys. First, notice the Databases drop-down box appears on the screen. Second, notice that directly below the Databases drop-down box are two more drop-down boxes: Object and Selection.
These two drop-down boxes appear for some tasks, and not others. We will talk about what they do in a moment. Also, in order to ease maintenance and avoid confusion, each task in the Plan should be applied to the same set of databases.
In this case, you might consider creating a special Maintenance Plan for the 50 GB database that uses the Reorganize Index and Update Statistics tasks, and another Maintenance Plan that applies the Rebuild Index task to the smaller databases. In this case, we can choose the option shown in Figure 1. Figure 1. The Define Rebuild Index Task screen reappears, and the two drop-down boxes I referred to earlier are displayed below the Databases drop-down box, but they are grayed out, as shown in Figure 1.
Why are these two options grayed out? The reason is that these two options are only available if you select one database on which to run the Rebuild Index task. Since we selected All user databases , these two options are not available.
To do this, select These databases from the screen shown in Figure 1. Notice that Specific databases now appears in the Databases drop-down box, the Object box is now available, and the Selection box is still, for the time being, grayed out.
What the Object and Selection options allow us to do is to selectively rebuild some of the indexes in your database, and not others. Notice that there are three choices for Object. If you leave the default option selected, Tables and views , then the Rebuild Index task will be applied to the indexes associated with all tables, and all indexed views, in the selected database.
In order to narrow the scope of the task to specific objects, you need to choose either Table or View. Having done this, the Selection drop-down box becomes available. Now, we get the option of selecting specific tables within the AdventureWorks database, to which this task should apply. For example, we could choose to rebuild only the indexes associated with the dbo. ErrorLog table, or we could select some combination of tables, by checking each of the relevant checkboxes. Why would we want to rebuild the indexes for some tables and not others?
Actually, there is a very good reason for this. By selecting only those indexes that really need defragmenting, you can reduce the time it takes to perform the Rebuild Index task, and at the same time, reduce the resource overhead associated with this task. If you are at the level where you know how to evaluate each index using the sys. Indexed views are physical views, unlike regular views, which are only materialized when they are called by a query.
Because indexed views are physical, they need rebuilding just like regular indexes. We still have several more choices to make before we are done configuring this task. Note that the discussion of these options assumes that each of your tables has a clustered index, and is not a heap. A heap is a table without a clustered index. No changes! The reason that there is still an open rowgroup is that SQL Server is waiting for more rows to be added before compressing and adding it to the columnstore index.
Having a large volume of tiny rowgroups would be inefficient as the ideal size is 2 20 rows, and the closer we can get to that magic number, the better. We can force the tuple mover to process the deltastore, close it, and commit it to compressed data within the columnstore index by using the following variant on the reorganize statement:.
The residual rowgroup no longer has any non-deleted rows in it and therefore is labelled with the ominous tombstone state. These are intermediary rowgroups that will be deallocated. We can force this along with another reorganization command, and the tombstone rowgroup is gone.
Note that there is no need to force this change. Doing so demonstrates how automated processes can be started to fast-track a given result. Given time, this cleanup will occur anyway. Therefore there is no compelling reason to do this in a production environment.
There are 23 compressed rowgroups, and the trim reason for the final rowgroup has been changed to REORG , indicating that it was opened and closed via an index reorganization process. Note that the index has the same number of rowgroups as earlier and row counts that are more than acceptable for OLAP operations. Rebuilding a clustered columnstore index is an offline operation until SQL Server when the ability to rebuild online was introduced.
As with a B-tree index, the rebuild will create a brand new columnstore index from the underlying data. Doing so will:. This operation is expensive and should be reserved for a dedicated maintenance window and only when the index has become so fragmented that no other options exist.
Note that the order of data is not changed when the columnstore index is rebuilt. Similar to when our demo table was first created, if no order is enforced, then SQL Server will build a new index on top of our data as-is.
If the columnstore index table has been maintained by adding data in order over time, then odds are good that no special work will be needed to re-order it.
For this table, the rebuild takes about 2 minutes. Reviewing all rowgroups in the table reveals the following:. The deleted rows are gone, and rows from other rowgroups were pushed into the vacancy left behind. While there are still some rowgroups that are not completely full, this index is quite efficiently structured overall.
If table partitioning is in use, then index rebuilds become significantly less expensive as the active partition s can be targeted for a rebuild and the rest ignored. In a table where a large portion of the data is old and unchanged, this can save immense system resources, not to mention time! When executed, the rebuild will only target partition 5. If this is the active partition and the others contain older static data, then this will be the only partition with fragmentation, assuming no unusual change has occurred on older data.
The rebuild of partition 5 shown above took about 20 seconds to complete. The simplest way to measure when a columnstore index needs a rebuild is to measure the quantity of deleted rows and compare it to the total rows for each partition. If a partition contains 1 million rows, of which k are flagged for deletion, then a rebuild would be a good idea to remove those soft-deleted rows from the index.
The following query groups columnstore index metadata by table, index, and partition, allowing for a relatively simple, but useful view of fragmentation:. This specific example provides insight into an index that requires no maintenance. If the count of deleted rows was high enough, though, then a rebuild of that partition could save storage, memory, and make queries against that data faster.
If a columnstore index is loaded solely via inserts no deletes or updates and the data is consistently inserted in order, then fragmentation will occur very slowly over time, and index rebuilds will be rarely if ever needed.
OLAP databases are uniquely positioned to take advantage of this fact, as their data loads are typically isolated, primarily inserts, and performed at designated intervals. This differs from OLTP databases, that are often the target of deletes, updates, software releases, and other operations that can quickly cause heavy fragmentation. The discussion of columnstore indexes thus far has focused on clustered columnstore indexes that target a primarily OLAP workload.
SQL Server also supports nonclustered columnstore indexes. A table may only have one columnstore index on it at a time, regardless of whether it is clustered or nonclustered. A nonclustered columnstore index functions similarly to a clustered columnstore index in terms of architecture, data management, the deltastore, and other nuances discussed thus far in this article.
This is executing against a table without a columnstore index, but with a nonclustered index on [Order Date Key]. The difference is that it now presents aggregated numbers instead of showing the individual rowgroups. Rebuilding a columnstore is conceptually exactly the same as rebuilding a rowstore index. SQL Server reads all data from the existing columnstore index, builds a new columnstore index on that data, then drops the old index.
After that process, the index will be as good as new, having all rowgroups except the last completely full, all deleted rows really deleted, and all open and closed rowgroups replaced by compressed rowgroups. While it may appear as if the number of rows has gone down, this is not actually the case — just remember that the reported number of rows in compressed rowgroups includes the rows that are marked as deleted.
As you can see, there are quite a few different options, which is why I split the syntax description over two diagrams.
The options in figure are used to rebuild the entire index. The optional WITH clause allows you to choose between either the normal columnstore compression, or the archive columnstore compression that saves even more space by using a more aggressive compression algorithm.
If you rebuild without specifying a WITH clause, each partition will retain its current compression type. The second set of options, in figure , rebuilds only a single partition of a clustered columnstore index and optionally changes the compression type.
Both versions allow you to specify the degree of parallelism. When not specified, the rebuild process, just like the initial build, will use all the nodes it can get.
As already mentioned briefly in level 2 , this is a more aggressive compression algorithm that reduces the on-disk size of the data even more than regular columnstore expression, but at the expense of using more CPU, both when rebuilding the columnstore index and when retrieving data from it. You would normally use this compression type only on partitioned tables, and only on the partitions that contain historical data — data that is so old that it is queried very infrequently.
For that type of data, the overhead of performing the extra decompression when retrieving the data will be outweighed by the disk space saving. But for data that is still queried on a regular basis, standard columnstore compression is the recommended level.
The code in listing once more rebuilds the entire clustered columnstore index, this time choosing archival compression, and then looks at the space taken. Since the sample sales table used for this series is not partitioned, I can only choose a single compression type for the whole table.
I want to stress that the above is for the sake of demonstration only, and not a recommended practice. If you want to use archival compression, choose a partitioning scheme that separates the rarely used data from the frequently used data, and use archival compression only for the partitions with little usage. There are only very few scenarios where archival compression for an entire table is the correct choice. So far, we have not looked at the effectiveness of rowgroup elimination for the clustered columnstore index.
But chances are that this is pretty abysmal. Looking back over the previous levels, we have created the FactOnlineSales2 table as a heap, which is always completely unordered.
We then built a clustered columnstore index directly on top of that, so even that first version of the index was unlikely to have any correlation between the data values and the rowgroups.
And then we have added rows, deleted rows, updated rows, and rebuilt the index twice without ever considering order at all. With all that, I would be very surprised if there is even a single column that will ever qualify for rowgroup elimination. Running the query in listing confirms by suspicion. CHI , pp. Graham, J. Just, M. Marshall, C. Nielson, J. Nist, S.
Reading Research and Instruction 27 1 , 12—25 Google Scholar. Olston, C. Peterson, S. Reading Research and Instruction 31 2 , 49—56 Google Scholar. Reeder, R. Robeck, M.
0コメント