SQL Server 2012 introduces a new type of nonclustered index called the columnstore index, which is available only if you're using the Enterprise version of SQL Server . Instead of storing the data rows contiguously across pages, all the data contained within a column is stored contiguously down a set of pages . Figure 6-5 illustrates how the data is stored .
CHAPTER 6 Building and maintaining indexes 81 C1 C2 C3 C4 Segment Row Gr oup C5
FIGURE 6-5 The columnstore index structure .
So instead of storing all the data in one structure, it is horizontally partitioned into row groups . Within a columnstore index is a segment. A segment contains all the values from one column of a row. The segments are broken down into row groups . Segments for the same sets of rows are stored within a row group .
This is a very different type of index than the traditional clustered and nonclustered indexes . Therefore, you should carefully consider your needs prior to using the columnstore index in your database . Here are few scenarios in which you should use a columnstore index:
■
■ The database is mostly read . ■
■ Most updates are appending the new data . ■
■ The database is a data warehouse .
These characteristics are uncommon in traditional operational databases. As a result, it is recom- mended that columnstore indexes be used only on data warehouses. Another contributing factor to why columnstore indexes should currently be used only on data warehouses is that you cannot update a table that has a columnstore index . This may or may not change in future releases of SQL Server. The index has to be removed for any updates to occur. As a result, you should avoid creating this type of index on tables that are frequently updated or that require small lookup queries .
82 PART II Designing Databases
Adding index options
Now that you have some basic index creation skills, it’s time to add a few options to your indexes to make them a little more flexible and robust. While SQL Server indexes boast a long list of available options, in this section you will focus on only the more common ones. A brief description will be pro- vided for those options that are not fully explained and described .
The most common option is FILLFACTOR. If you think about the name of the option, you can almost derive its purpose . Each data page on the leaf level of a clustered index holds a maximum amount of data, approximately 8,060 bytes . There are some variances, but describing them is beyond the scope of this book. FILLFACTOR tells SQL Server how full the leaf-level pages of the index should be when rebuilding or reorganizing an index . Rebuilding and reorganizing and index are part of index maintenance and will be discussed at length in Chapter 21, “Managing and Maintaining Indexes and Statistics.” If you do not specify a FILLFACTOR during index creation, it will be 100. This means the data pages will be completely full . When the data changes through insert, update, or delete opera- tions, then the page will have to change or, as we say in the database world, split . When a page splits, 50 percent of the data will be on one page and 50 percent will be on the other. A few page splits are not too bad, but if this is a regular occurrence, the performance of your database could suffer . While the explanation of how to determine what a FILLFACTOR should be is a very advanced topic, as a baseline, if you have a table that is frequently modified, consider setting the FILLFACTOR between 70 and 80. If a table is not updated too often, a FILLFACTOR of 90 should be sufficient.
Note FILLFACTOR can be set at the index level, but it can also be set at the server level. If
you open the Properties dialog box on the server and go to Database Settings, you can set the Default Index fill factor.
The next option, which is tightly coupled with FILLFACTOR, is PAD_INDEX. This option has the same effect on pages in the index structures as FILLFACTOR, but instead of data pages, it controls how full the intermediate-level pages will be. Unlike with FILLFACTOR, you cannot set a value for PAD_INDEX; it inherits the value from FILLFACTOR.
When an index is created, the data has to be sorted, and this requires the containing database to have sufficient space for this operation, which could cause performance problems. However, using the SORT_IN_TEMPDB option relocates the sort operations to tempdb. As a best practice, tempdb should be stored on a separate set of disks from other databases. Not only can doing this improve perfor- mance, but it also allows you to transfer disk space requirements to tempdb .
CHAPTER 6 Building and maintaining indexes 83
TABLE 6-1 Additional Index Options
Option Description
IGNORE_DUP_KEY During a multirow insert that contains duplicate key values, setting this option to ON will ensure that only one unique row is inserted and the integrity of the index is not violated .
STATISTICS_NORECOMPUTE Statistics are vital to SQL Server with regard to determining how a query will be executed. As such, statistics need to be updated regularly. You can stop statistics from automatically recomputing by setting this option to ON.
ONLINE Index maintenance is pivotal . You must rebuild and reorganize your indexes on a regular basis . However, when these operations are performing, users cannot access the data. Setting this option to ON allows data access. Please note that you must own the Enterprise version of SQL Server to use this option; Chapter 21 discusses this option further .
ALLOW_ROW_LOCKS When accessing data, if this option is set to ON, SQL Server will lock the accessed rows .
ALLOW_PAGE_LOCKS When accessing data, if this option is set to ON, SQL Server will lock the accessed pages .
MAX_DOP Using this option, you can control how many processors are used during index creation .
DATA_COMPRESSION This option is available only in the Enterprise version of SQL Server . There are two types of compression: ROW and PAGE. Both are discussed in detail in Chapter 7, “SQL Server Compression .”
Take some time and explore the index options . Create indexes with some of these options for prac- tice and to further extend your knowledge .