Tables Without a Clustered Index

When a table exists without a clustered index, it is known as a heap. A heap consists of an IAM (index allocation map) page(s) and a series of data pages that are not linked together or stored in order. The only way SQL Server can determine the pages of the table is by reading the IAM page(s). When a table is stored as a heap, without an index, then every time the table is accessed, SQL Server must read every single page in the table, even if you only want to return one row. The diagram in Figure 8-1 illustrates how a heap is structured.

When data is stored on a heap, SQL Server needs to maintain a unique identifier for each row. It does this by creating a RID (row identifier). A RID has a format of FileID: Page ID: Slot Number, which is a physical location. Even if a table has nonclustered indexes, it is still stored as a heap, unless there is a clustered index. When nonclustered indexes are created on a heap, the RID is used as a pointer so that nonclustered indexes can link back to the correct row in the base table.

Tables with a Clustered Index

When you create a clustered index on a table, a B-tree structure is created. This B-tree is based on the values of the clustered key, and if the clustered index is not unique, it also includes a uniquifier. A uniquifier is a value used to identify rows if their key values are the same. This allows SQL Server to perform more efficient search operations by creating a tiered set of pointers to the data, as illustrated in Figure 8-2. The page at the top level of this hierarchy is called the root node. The bottom level of the structure is called the leaf level, and with a clustered index, the leaf level consists of the actual data pages of the table. B-tree structures can have one or more intermediate levels, depending on the size of the table.

Figure 8-2 shows that although the leaf level is the data itself, the levels above contain pointers to the pages below them in the tree. This allows SQL Server to perform a seek operation, which is a very efficient method of returning a small number of rows. It works by navigating its way down the B-tree, using the pointers, to find the row(s) it requires. In this figure, we can see that if required, SQL Server can still scan all pages of the table in order to retrieve the required rows—this is known as a clustered index scan. Alternatively, SQL Server may decide to combine these two methods to perform a range scan. Here, SQL Server seeks the first value of the required range and then scans the leaf level until it encounters the first value that is not required. SQL Server can do this, because the table is ordered by the index key, which means that it can guarantee that no other matching values appear later in the table.

Clustering the Primary Key

The primary key of a table is often the natural choice for the clustered index, because many OLTP applications access 99% of data through the primary key. In fact, by default, unless you specify otherwise, or unless a clustered index already exists on the table, creating a primary key automatically generates a clustered index on that key. There are circumstances when the primary key is not the correct choice for the clustered index. An example of this that I have witnessed is a third-party application that requires the primary key of the table to be a GUID.

Creating a clustered index on a GUID introduces two major problems if the clustered index is to be built on the primary key. The first is size. A GUID is 16 bytes long. When a table has nonclustered indexes, the clustered index key is stored in every nonclustered index. For unique nonclustered indexes, it is stored for every row at the leaf level, and for nonunique nonclustered indexes, it is also stored at every row in the root and intermediate levels of the index. When you multiple 16 bytes by millions of rows, this drastically increases the size of the indexes, making them less efficient.

The second issue is that when a GUID is generated, it is a random value. Because the data in your table is stored in the order of the clustered index key for good performance, you need the values of this key to be generated in sequential order. Generating random values for your clustered index key results in the index becoming more and more fragmented every time you insert a new row. Fragmentation is discussed later in this chapter.

There is a workaround for the second issue, however. SQL Server has a function called NEWSEQUENTIALID(). This function always generates a GUID value that is higher than previous values generated on the server. Therefore, if you use this function in the default constraint of your primary key, you can enforce sequential inserts.

If the primary key must be a GUID or another wide column, such as a Social Security Number, or if it must be a set of columns that form a natural key, such as Customer ID, Order Date, and Product ID, then it is highly recommended that you create an additional column in your table. You can make this column an INT or BIGINT, depending on the number of rows you expect the table to have, and you can use either the IDENTITY property or a SEQUENCE in order to create a narrow, sequential key for your clustered index.

Administering Clustered Indexes

You can create a clustered index by using the CREATE CLUSTERED INDEX statement, as shown in Listing 8-1. Other methods you can use to create a clustered index are using the ALTER TABLE statement with a PRIMARY KEY clause and using the INDEX clause in the CREATE TABLE statement, as long as you are using SQL Server 2014 or higher. This script creates a database called Chapter8 and then a table called CIDemo. Finally, it creates a clustered index on the ID column of this table.

As mentioned earlier in this chapter, if you create a primary key on a table, then unless you specify the NONCLUSTERED keyword, or a clustered index already exists, a clustered index is created automatically to cover the column(s) of the primary key. Also, remember that at times you may wish to move the clustered index to a more suitable column if the primary key is wide or if it is not ever-increasing.

In order to achieve this, you need to drop the primary key constraint and then re-create it using the NONCLUSTERED keyword. This forces SQL Server to cover the primary key with a unique nonclustered index. Once this is complete, you are able to create the clustered index on the column of your choosing.

Covering Indexes

Although having all required columns within the nonclustered index means that you do not have to retrieve data from the underlying table, the trade-off is that having many columns within a nonclustered index can lead to very wide, inefficient indexes. In order to gain a better balance, SQL Server offers you the option of included columns.

Included columns are included at the leaf level of the index only, as opposed to the index key values, which continue to be included at every level of the B-tree. This feature can help you cover your queries while maintaining the narrowest index keys possible. This concept is illustrated in Figure 8-4. This diagram illustrates that the index has been built using Balance as the index key, but the FirstName and LastName columns have also been included at the leaf level. You can see that CustomerID has also been included at all levels; this is because CustomerID is the clustered index key. Because the clustered index key is included at all levels, this implies that the index is not unique. If it is unique, then the clustered key is only included at the leaf level of the B-tree. This means that unique, nonclustered indexes are always narrower than their nonunique equivalents. This index is perfect for a query that filters on Balance in the WHERE clause and returns the FirstName and LastName columns. It also covers queries that returned CustomerID in the results.

You can also use the index illustrated in Figure 8-4 to cover queries that filter on FirstName or LastName in the WHERE clause providing that other columns from the table are not returned. To process the query, however, SQL Server needs to perform an index scan, as opposed to an index seek or range scan, which is, of course, less efficient.

Administering Nonclustered Indexes

You can drop the index in the same way that we dropped the clustered index earlier in this chapter—using a DROP INDEX statement. In this case, the full statement would be DROP INDEX NCI_Balance ON dbo.CustomersDisk.

Filtered Indexes

A filtered index is an index built on a subset of the data stored within a table, as opposed to one that is built on all of the data in the table. Because the indexes are smaller, they can lead to improved query performance and reduced storage cost. They also have the potential to cause less overhead for DML operations, since they only need to be updated if the DML operation affects the data within the index. For example, if an index was filtered on OrderDate >= '2019-01-01' AND OrderDate <= '2019-12-31' and subsequently updated all rows in the table where the OrderDate >= '2020-01-01', then the performance of the update would be the same as if the index did not exist.

Filtered indexes are constructed by using a WHERE clause on index creation. There are many things that you can do in the WHERE clause, such as filter on NULL or NOT NULL values; use equality and inequality operators, such as =, >, <, and IN; and use logical operators, such as AND and OR. There are also limitations, however. For example, you cannot use BETWEEN, CASE, or NOT IN. Also, you can only use simple predicates so, for example, using a date/time function is prohibited, so creating a rolling filter is not possible. You also cannot compare a column to other columns.

Columnstore Indexes

As you have seen, traditional indexes store rows of data on data pages. This is known as a rowstore. SQL Server also supports Columnstore indexes. These indexes flip data around and use a page to store a column, as opposed to a set of rows. This is illustrated in Figure 8-5.

A Columnstore index slices the rows of a table into chunks of between 102,400 and 1,048,576 rows each. Each slice is called a rowgroup. Data in each rowgroup is then split down into columns and compressed using VertiPaq technology. Each column within a rowgroup is called a column segment.

Columnstore indexes offer several benefits over traditional indexes, given appropriate usage scenarios. First, because they are highly compressed, they can improve I/O efficiency and reduce memory overhead. They can achieve such a high compression rate because data within a single column is often very similar between rows. Also, because a query is able to retrieve just the data pages of the column it requires, I/O can again be reduced. This is helped even further by the fact that each column segment contains a header with metadata about the data within the segment. This means that SQL Server can access just the segments it needs, as opposed to the whole column. A new query execution mechanism has also been introduced to support Columnstore indexes. It is called batch execution mode, and it allows data to be processed in chunks of 1000 rows, as opposed to on a row-by-row basis. This means that CPU usage is much more efficient. Columnstore indexes are not a magic bullet, however, and are designed to be optimal for data warehouse–style queries that perform read-only operations on very large tables. OLTP-style queries are not likely to see any benefit and, in some cases, may actually execute slower. SQL Server supports both clustered and nonclustered Columnstore indexes, and these are discussed in the following sections.

Clustered Columnstore Indexes

Clustered Columnstore indexes cause the entire table to be stored in a Columnstore format. There is no traditional rowstore storage for a table with a clustered Columnstore index; however, new rows that are inserted into the table may temporarily be placed into a rowstore table, called a deltastore. This is to prevent the Columnstore index from becoming fragmented and to enhance performance for DML operations. The diagram in Figure 8-6 illustrates this.

The diagram shows that when data is inserted into a clustered Columnstore index, SQL Server assesses the number of rows. If the number of rows is high enough to achieve a good compression rate, SQL Server treats them as a rowgroup or rowgroups and immediately compresses them and adds them to the Columnstore index. If there are too few rows, however, SQL Server inserts them into the internal deltastore structure. When you run a query against the table, the database engine seamlessly joins the structures together and returns the results as one. Once there are enough rows, the deltastore is marked as closed and a background process called the tuple compresses the rows into a rowgroup in the Columnstore index.

There can be multiple deltastores for each clustered Columnstore index. This is because when SQL Server determines that an insert warrants using a deltastore, it attempts to access the existing deltastores. If all existing deltastores are locked, however, then a new one is created, instead of the query being forced to wait for a lock to be released.

When a row is deleted in a clustered Columnstore index, then the row is only logically removed. The data still physically stays in the rowgroup until the next time the index is reorganized or until the next time a background thread runs. The background thread was introduced in SQL Server 2019 and provides improved index quality, by compressing small deltastores and merging small rowgroups. SQL Server maintains a B-tree structure of pointers to deleted rows in order to easily identify them. If the row being deleted is located in a deltastore, as opposed to the index itself, then it is immediately deleted, both logically and physically. When you update a row in a clustered Columnstore index, then SQL Server marks the row as being logically deleted and inserts a new row into a deltastore, which contains the new values for the row.

It is also possible to create nonclustered B-Tree indexes on a Clustered Columnstore index. It provides efficient seek operations against the underlying columnar data.

SQL Server 2022 introduces a performance optimization for clustered Columnstore indexes, called Ordered Clustered Columnstore Indexes. This allows improved performance by offering better elimination of segments, by ordering the data before it is compressed. The script in Listing 8-7 creates the same index as the script in Listing 8-6, except that it uses the ORDER clause to cause the data to be ordered by the OrderDate column.

Nonclustered Columnstore Indexes

A Nonclustered Columnstore index is essentially the same as a Clustered Columnstore Index. The difference is that a copy of the data is made from the columns that the index covers instead of the converting the data that is stored within the table to a columnar format, meaning that a table can have both B-Tree (clustered and nonclustered) indexes, as well as nonclustered Columnstore indexes. Obviously, this increases the storage space required for the data, but it provides the benefit of allowing you to target operational workloads at a clustered B-Tree while targeting analytical workloads against the nonclustered Columnstore. Given that a ten times compression ratio is typical within a Columnstore index, it will likely be worth the additional storage.

In-Memory Indexes

As we saw in Chapter 7, SQL Server provides two types of index for memory-optimized tables: nonclustered and nonclustered hash. Every memory-optimized table must have a minimum of one index. All in-memory indexes cover all columns in the table because they use a memory pointer to link to the data row.

Indexes on memory-optimized tables must be created in the CREATE TABLE statement. There is no CREATE INDEX statement for in-memory indexes. Indexes built on memory-optimized tables are always stored in memory only and are never persisted to disk, regardless of your table’s durability setting. They are then re-created after the instance restarts, from the table’s underlying data. You do not need to worry about fragmentation of in-memory indexes, since they never have a disk-based structure.

In-Memory Nonclustered Hash Indexes

A nonclustered hash index consists of an array of buckets. A hash function is run on each of the index keys, and then the hashed key values are placed into the buckets. The hashing algorithm used is deterministic, meaning that index keys with the same value always have the same hash value. This is important because repeated hash values are always placed in the same hash bucket. When many keys are in the same hash bucket, performance of the index can degrade, because the whole chain of duplicates needs to be scanned to find the correct key. Therefore, if you are building a hash index on a nonunique column with many repeated keys, you should create the index with a much larger number of buckets. This should be in the realm of 20 to 100 times the number of distinct key values, as opposed to 2 times the number of unique keys that is usually recommended for unique indexes. Alternatively, using a nonclustered index on a nonunique column may offer a better solution. The second consequence of the hash function being deterministic is that different versions of the same row are always stored in the same hash bucket.

Even in the case of a unique index where only a single, current row version exits, the distribution of hashed values into buckets is not even, and if there are an equal number of buckets to unique key values, then approximately one-third of the buckets is empty, one-third contains a single value, and one-third contains multiple values. When multiple values share a bucket, it is known as a hash collision, and a large number of hash collisions can lead to reduced performance. Hence, the recommendation for the number of buckets in a unique index is twice the number of unique values expected in the table.

Tip

When you have a unique nonclustered hash index, in some cases, many unique values may hash to the same bucket. If you experience this, then increasing the number of buckets helps, in the same way that a nonunique index does.

As an example, if your table has one million rows, and the indexed column is unique, the optimum number of buckets, known as the BUCKET_COUNT, is two million. If you know that you expect your table to grow to two million rows, however, then it may be prudent to create four million hash buckets. This number of buckets is low enough to not have an impact on memory. It also still allows for the expected increase in rows, without there being too few buckets, which would impair performance. An illustration of potential mappings between index values and hash buckets is illustrated in Figure 8-7.

Tip

The amount of memory used by a nonclustered hash index always remains static, since the number of buckets does not change.

Hash indexes are optimized for seek operations with the = predicate. For the seek operation, however, the full index key must be present in the predicate evaluation. If it is not, a full index scan is required. An index scan is also required if inequality predicates, such as < or >, are used. Also, because the index is not ordered, the index cannot return the data in the sort order of the index key.

Note

You may remember that in Chapter 7, we witnessed superior performance from a disk-based table than from a memory-optimized table. This is explained by us using the > predicate in our query; this meant that although the disk-based index was able to perform an index seek, our memory-optimized hash index had to perform an index scan.

Let’s now create a memory-optimized version of our OrdersDisk table, which includes a nonclustered hash index on the OrderID column, using the script in Listing 8-9. Initially, this row has one million rows, but we expect the number to grow to two million, so we use a BUCKET_COUNT of four million.

CREATE TABLE dbo.OrdersMemHash

(

        OrderNumber    INT    NOT NULL    IDENTITY    PRIMARY KEY

                                 NONCLUSTERED HASH WITH(BUCKET_COUNT = 4000000),

        OrderDate      DATE    NOT NULL,

        CustomerID     INT     NOT NULL,

        ProductID      INT     NOT NULL,

        Quantity       INT     NOT NULL,

        NetAmount      MONEY   NOT NULL,

        DeliveryDate   DATE    NULL,

) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) ;

INSERT INTO dbo.OrdersMemHash(OrderDate,CustomerID,ProductID,Quantity,NetAmount,DeliveryDate)

SELECT OrderDate

        ,CustomerID

        ,ProductID

        ,Quantity

        ,NetAmount

        ,DeliveryDate

FROM dbo.OrdersDisk ;

Listing 8-9

Creating a Table with a Nonclustered Hash Index

If we now wish to add an additional index to the table, we need to drop and re-create it. We already have data in the table, however, so we first need to create a temp table and copy the data in so that we can drop and re-create the memory-optimized table. The script in Listing 8-10 adds a nonclustered index to the OrderDate column.

--Create and populate temp table

SELECT * INTO #OrdersMemHash

FROM dbo.OrdersMemHash ;

--Drop existing table

DROP TABLE dbo.OrdersMemHash ;

--Re-create the table with the new index

CREATE TABLE dbo.OrdersMemHash

(

        OrderNumber     INT     NOT NULL    IDENTITY    PRIMARY KEY

                                      NONCLUSTERED HASH WITH(BUCKET_COUNT = 4000000),

        OrderDate       DATE    NOT NULL        INDEX NCI_OrderDate NONCLUSTERED,

        CustomerID      INT     NOT NULL,

        ProductID       INT     NOT NULL,

        Quantity        INT     NOT NULL,

        NetAmount       MONEY   NOT NULL,

        DeliveryDate    DATE    NULL,

) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) ;

GO

--Allow values to be inserted into the identity column

SET IDENTITY_INSERT OrdersMemHash ON ;

GO

--Repopulate the table

INSERT INTO

dbo.OrdersMemHash(OrderNumber,OrderDate,CustomerID,ProductID,Quantity,NetAmount,DeliveryDate)

SELECT *

FROM #OrdersMemHash ;

--Stop further inserts to the identity column and clean up temp table

SET IDENTITY_INSERT OrdersMemHash OFF ;

DROP TABLE #OrdersMemHash ;

Listing 8-10

Adding an Index to a Memory-Optimized Table

We can examine the distribution of the values in our hash index by interrogating the sys.dm_db_xtp_hash_index_stats DMV. The query in Listing 8-11 demonstrates using this DMV to view the number of hash collisions and calculate the percentage of empty buckets.

SELECT

  OBJECT_SCHEMA_NAME(HIS.OBJECT_ID) + '.' + OBJECT_NAME(HIS.OBJECT_ID) 'Table Name',

  I.name as 'Index Name',

  HIS.total_bucket_count,

  HIS.empty_bucket_count,

  FLOOR((CAST(empty_bucket_count AS FLOAT)/total_bucket_count) * 100) 'Empty Bucket Percentage',

  total_bucket_count - empty_bucket_count 'Used Bucket Count',

  HIS.avg_chain_length,

  HIS.max_chain_length

FROM sys.dm_db_xtp_hash_index_stats AS HIS

INNER JOIN sys.indexes AS I

        ON HIS.object_id = I.object_id

                AND HIS.index_id = I.index_id ;

Listing 8-11

sys.dm_db_xtp_hash_index_stats

From the results in Figure 8-8, we can see that for our hash index, 78% of the buckets are empty. The percentage is this high because we specified a large BUCKET_COUNT with table growth in mind. If the percentage was less than 33%, we would want to specify a higher number of buckets to avoid hash collisions. We can also see that we have an average chain length of 1, with a maximum chain length of 5. This is healthy. If the average chain count increases, then performance begins to tail off, since SQL Server has to scan multiple values to find the correct key. If the average chain length reaches 10 or higher, then the implication is that the key is nonunique and there are too many duplicate values in the key to make a hash index viable. At this point, we should either drop and re-create the table with a higher bucket count for the index or, ideally, look to implement a nonclustered index instead.

Missing Indexes

When you run queries, the Database Engine keeps track of any indexes that it would like to use when building a plan to aid your query performance. When you view an execution plan in SSMS, you are provided with advice on missing indexes, but the data is also available later through DMVs.

In order to demonstrate this functionality, we can execute the query in Listing 8-12 and choose to include the actual execution plan.

Once we have run this query, we can examine the execution plan and see what it tells us. The execution plan for this query is shown in Figure 8-9.

At the top of the execution plan in Figure 8-9, you can see that SQL Server is recommending that we create an index on the CustomerID column of the OrdersDisk table and include the NetAmount column at the leaf level. We are also advised that this should provide a 75% performance improvement to the query.

The results of this query are shown in Figure 8-10. They show the following: the name of the table with the missing index; the column(s) that SQL Server recommends should form the index key; the columns that SQL Server recommends should be added as included columns at the leaf level of the B-tree; the number of times that queries that would have benefited from the index have been compiled; how many seeks would have been performed against the index, if it existed; the number of times that the index has been scanned if it existed; the average cost that would have been saved by using the index; and the average percentage cost that would have been saved by using the index. In our case, we can see that the query would have been 95% less expensive if the index existed when we ran our query.

Index Fragmentation

Disk-based indexes are subject to fragmentation. Two forms of fragmentation can occur in B-trees: internal fragmentation and external fragmentation. Internal fragmentation refers to pages having lots of free space. If pages have lots of free space, then SQL Server needs to read more pages than is necessary to return all of the required rows for a query. External fragmentation refers to the pages of the index becoming out of physical order. This can reduce performance, since the data cannot be read sequentially from disk.

For example, imagine that you have a table with one million rows of data and that all of these data rows fit into 5000 pages when the data pages are 100% full. This means that SQL Server needs to read just over 39MB of data in order to scan the entire table (8KB * 5000). If the pages of the table are only 50% full, however, this increases the number of pages in use to 10,000, which also increases the amount of data that needs to be read to 78MB. This is internal fragmentation.

Internal fragmentation can occur naturally when DELETE statements are issued and when DML statements occur, such as when a key value that is not ever-increasing is inserted. This is because SQL Server may respond to this situation by performing a page split. A page split creates a new page, moves half of the data from the existing page to the new page, and leaves the other half on the existing page, thus creating 50% free space on both pages. They can also occur artificially, however, through the misuse of the FILLFACTOR and PAD_INDEX settings.

FILLFACTOR controls how much free space is left on each leaf level page of an index when it is created or rebuilt. By default, the FILLFACTOR is set to 0, which means that it leaves enough space on the page for exactly one row. In some cases, however, when a high number of page splits is occurring due to DML operations, a DBA may be able to reduce fragmentation by altering the FILLFACTOR. Setting a FILLFACTOR of 80, for example, leaves 20% free space in the page, meaning that new rows can be added to the page without page splits occurring. Many DBAs change the FILLFACTOR when they are not required to, however, which automatically causes internal fragmentation as soon as the index is built. PAD_INDEX can be applied only when FILLFACTOR is used, and it applies the same percentage of free space to the intermediate levels of the B-tree.

External fragmentation is also caused by page splits and refers to the logical order of pages, as ordered by the index key, being out of sequence when compared to the physical order of pages on disk. External fragmentation makes it so SQL Server is less able to perform scan operations using a sequential read, because the head needs to move backward and forward over the disk to locate the pages within the file.

Resumable Index Operations

SQL Server 2019 supports resumable online index creation and index rebuilds for both traditional (clustered and nonclustered) indexes and Columnstore indexes. Resumable index operations allow you to pause an online index operation (build or rebuild), in order to free up system resources and then restart it again, from where it left off, when resource utilization is no longer an issue. These operations also allow an online index operation to be restarted, after it has failed for common reasons, such as lack of disk space.

Some of the advantages that this functionality brings to a DBA are clear. For example, if a large index rebuild needs to fit inside a short maintenance window, then the rebuild can be paused at the end of a maintenance window and restarted at the beginning of the next, as opposed to having to abort the operation, to free up system resources. There are also other, hidden benefits, however. For example, resumable index operations do not consume large amount of log space, even when performed on large indexes. This is because all data required to restart the index operation is stored inside the database. A side note of this is that the index operation does not hold a long-running transaction while paused.

For the most part, there are very few drawbacks to using resumable index operations. The quality of defragmentation achieved is comparable to the quality of a standard online index operation, and there is no real difference in speed between resumable and standard online index operations (excluding the potential pause of course). As always, however, there is no such thing as a free lunch, and during a paused, resumable operation, there will be a degradation of write performance, to affected tables and indexes, due to two versions of the index needing to be updated. This degradation should not be more than 10%, in most cases. There should be no impact to read operations, during the pause, as they continue to use the original version of the index, until the operation has completed.

The command in Listing 8-18 will pause the index rebuild, started in Listing 8-17.

After running this script, the index rebuild operation will pause, and the message displayed in Figure 8-11 will be displayed.

Instead of turning on ONLINE and RESUMABLE options for each, individual index operation, you can turn them on globally, at the database level, by using database scoped configurations. The ELEVATE_ONLINE configuration will change the default value of ONLINE to ON, for supported index operations, within the database. The configuration ELEVATE_RESUMABLE will default the value of RESUMABLE to ON.

Both ELEVATE_ONLINE and ELEVATE_RESUMABLE can be configured as OFF (the default behavior), WHEN_SUPPORTED, or FAIL_UNSUPPORTED. When set to WHEN_SUPPORTED, noncompatible operations, such as rebuilding XML indexes, will be performed offline and unresumable. If set to FAIL_UNSUPPORTED, however, such operations will fail, throwing an error.

Partitioned Indexes

As mentioned in Chapter 7, it is possible to partition indexes as well as tables. A clustered index always shares the same partition scheme as the underlying table, because the leaf level of the clustered index is made up of the actual data pages of the table. Nonclustered indexes, on the other hand, can either be aligned with the table or not. Indexes are aligned if they share the same partition scheme or if they are created on an identical partition scheme.

In most cases, it is good practice to align nonclustered indexes with the base table, but on occasion, you may wish to deviate from this strategy. For example, if the base table is not partitioned, you can still partition an index for performance. Also, if you index key is unique and does not contain the partitioning key, then it needs to be unaligned. There is also an opportunity to gain a performance boost from unaligned nonclustered indexes if the table is involved in collated joins with other tables on different columns.

You can create a partitioned index by using the ON clause to specify the partition scheme in the same way that you create a partitioned table. If the index already exists, you can rebuild it, specifying the partition scheme in the ON clause. The script in Listing 8-21 creates a partition function and a partition scheme. It then rebuilds the clustered index of the OrdersDisk table to move it to the new partition scheme. Finally, it creates a new nonclustered index, which is partition aligned with the table.

Filtered Statistics

Filtered statistics allow you to create statistics on a subset of data within a column through the use of a WHERE clause in the statistic creation. This allows the Query Optimizer to generate an even better plan, since the statistics only contain the distribution of values within the well-defined subset of data. For example, if we create filtered statistics on the NetAmount column of our OrdersDisk table filtered by OrderDate being greater than 1 Jan 2019, then the statistics will not include rows that contain old orders, allowing us to search for large, recent orders more efficiently.

Incremental Statistics

Incremental statistics can help reduce table scans caused by statistics updates on large partitioned tables. When enabled, statistics are created and updated on a per-partition basis, as opposed to globally, for the entire table. This can significantly reduce the amount of time you need to update statistics on large partitioned tables, since partitions where the statistics are not outdated are not touched, therefore reducing unnecessary overhead.

Additionally, you cannot use incremental statistics on read-only databases or on databases that are participating in an AlwaysOn Availability Group as a readable secondary replica.

Managing Statistics

SQL Server 2019 introduces additional metadata information, to help diagnose issues that are caused by queries waiting for synchronous statistics updates to occur. Firstly, a new wait type has been added, called WAIT_ON_SYNC_STATISTICS_REFRESH. This wait type denotes the amount of time that queries have spent waiting on the completion of synchronous statistics updates. Secondly, a new command type, called SELECT (STATMAN), has been added to the sys.dm_exec_requests DMV. This command type indicates that a SELECT statement is currently waiting for a synchronous statistics update to complete, before it can continue.

Managing Cardinality

Because cardinality estimation is critical to plan optimization, if the cardinality estimator makes the wrong assumptions, it can have serious implications for query performance. Therefore, major changes to the cardinality estimator are not often made. There was a major update in SQL Server 7. The next large update was in SQL Server 2014.

The update to the cardinality estimator in SQL Server 2014 had two main impacts on behavior. Firstly, the assumption that columns are independent was replaced with an assumption that there will be correlation between values in different columns.

The behavior for estimating if no data will be returned based on joining multiple tables has also changed. The original behavior was to estimate the selectivity in the predicates of each table before joining the histograms to estimate join selectivity. The new behavior is to estimate join selectivity from the base tables before estimating predicate selectivity. This is known as base containment.

The issue is that the new cardinality estimations may be suboptimal for your workloads. If this is the case, then you can force a query to use legacy cardinality estimations by using a plan hint. New in SQL Server 2022, you can also use a Query Store hint to force the behavior without changing the query. This will be discussed in Chapter 22.

In SQL Server 2022, providing that Query Store is enabled, you can also use feedback to help the optimizer determine what cardinality assumptions should be used for a given query, based on previous optimizations of the same query. You can use the commands in Listing 8-29 to toggle this functionality on or off, within a database.