SQL server-focused filtered index improves query performance (10)

Objective

In this section we continue to talk about index knowledge, before we clustered index, nonclustered index and overlay index, there is also a filter index, through the index filter we can also improve query performance, short content, in-depth understanding.

Filter indexes, create nonclustered indexes on query criteria (1)

Filtering an index is a new feature of SQL 2008 that is applied to some of the rows in the table, so using a filtered index can improve the query, which reduces the cost of index maintenance and index storage relative to full table scanning. The index is filtered when we apply the Where condition on the index. That is, the following format is satisfied:

CREATE nonclustered INDEX <index name><table> (<columns><criteria>; GO

Let's look at a simple query

 - GO

No indexes are established in the above columns, except, of course, the clustered index created by default is Salesorderdetailid, in which case we can guess that the query plan it executes must be a clustered index scan created by the primary key, as follows

As we have said above, we have not created an index on the query condition at this time, so it is necessary to go to the clustered index created by the primary key, then we first create the nonclustered index on the UnitPrice column to improve the query performance.

CREATE nonclustered INDEX Idx_salesorderdetail_unitpriceon sales.salesorderdetail (UnitPrice)

Now let's compare the two query costs.

 - GO SELECT salesorderdetailid, Unitpricefrom    -

After a nonclustered index has been established on the query criteria, the query overhead is significantly increased by more than 90% because the nonclustered index also references the clustered index created by the primary key, so this time it does not cause a bookmark lookup or key lookup. Next we'll add a nonclustered index with a condition that filters the index

1000

Now let's take a look at the performance cost differences between the filtered indexes and the previous nonclustered indexes:

 - SELECT salesorderdetailid, Unitpricefrom    -

At this point we know that the nonclustered filtered index created is nearly half as close to our query as the traditional nonclustered index created.

Unique Filtered Index

A unique filtered index is a good solution for all columns that must be unique and NOT NULL (only one null exists), so you need to exclude null values when creating a unique filtered index, such as the following:

CREATE UNIQUE nonclustered INDEX uq_fix_customers_emailon Customers (email) WHERE Email is not Nullgo

Filter index with include

When we add an extra column, the clustered index that is created with the default primary key is then clustered index Scan, then we create a filtered index on the query condition, when we force the filter index, the addition of additional columns will result in the need to return to the base table to get the data, so it also causes the key Lookup lookup, as follows:

Unitpricediscount  - GO

At this point we need to include additional columns with include.

CREATE nonclustered INDEX [Idx_salesorderdetail_unitprice] on Sales.SalesOrderDetail (UnitPrice) INCLUDE ( Unitpricediscount)

We'll create a filter index and include additional columns

2000

The next step is to compare the performance query differences between adding a filtered index and not adding a filtered index, including additional columns.

 - SELECT salesorderdetailid, UnitPrice, Unitpricediscountfrom    -

Performance with include to include additional column performance has also been improved somewhat.

filtering indexes, creating nonclustered indexes on primary keys (2)

In the first case, we can create a nonclustered index directly on the query column, because its type is a numeric type, and if the query condition is a character type? Preferred now let's create a test table first

Use Tsql2012gocreate TABLE dbo. TestData (    RowID       integer IDENTITY not null,     somevalue   VARCHAR (max) is not null,          startdate   Date Not NULL,    CONSTRAINT pk_data_rowid        PRIMARY KEY CLUSTERED (RowID));

Add 100,000 test data

Use tsql2012goinsert dbo. TestData with (Tablockx)    (somevalue, StartDate) SELECT    'jeffckywang',     1  to ' 20140101 ' ) from dbo. Nums as Nwhere     1     100001;

If we need to get somevalue = ' jeffckywang ' in table testdata, we want to create a nonclustered index on somevalue and then filter it, as follows

' Jeffckywang '

SQL Server 2012 Error Unable to create a nonclustered index on a character type, we need to create a nonclustered index on the primary key, we create a filtered index on the primary key rowid and somevalue = ' Jeffckywang ', and then return the data as follows:

' Jeffckywang '

Let's compare the results of the query plan before and after the filter index is established:

' Jeffckywang '  'jeffckywang'

Then, combining the previous studies, remove key Lookup and include the filtered index created.

' Jeffckywang '

As you can see from here, whether you create a filtered index on a query condition or create a filtered index on a primary key, we are able to improve query performance by combining what we learned earlier.

We've been talking since the beginning. Create filtered indexes, so what are the conditions that create the benefits of filtering indexes?

(1) can only be created from a nonclustered index.

(2) If you create a filtered index on a view, this view must be a persisted view.

(3) You cannot create a filtered index on a full-text index.

Advantages of filtering indexes

(1) Reduce the cost of index maintenance: it is not expensive to add, delete, or change operations, because rebuilding a filtered index does not take much time.

(2) Reduce storage costs: the storage footprint of the filtered index is small.

(3) More accurate statistics: it is more accurate to create a filtered index on the where condition than the full table statistic results.

(4) Optimization of query performance: Through the query plan can be seen its efficiency.

So far, it has been stated that the benefits and advantages of filtering index, has been put into the sky, in fact, its shortcomings are obvious.

Filter Index Disadvantage

The biggest drawback is the restriction of query conditions. Its query criteria are limited to

<filter_predicate>:: =       <conjunct> [and <conjunct> ]    <conjunct>:: =      <disjunct> | <comparison>    <disjunct>:: =          column_name in (constant,... N)  

Filter conditions are limited to and, |, in. Comparison conditions are limited to {is | is not | = | <> |! = | > | >= |!> | < | <= |!<}, so use like no

' jeffckywang% '

The following can

' 2008-01-01 ' ' 2008-01-07 ' GO

But not as follows.

= GETDATE () GO

Effects of variables on filtered indexes

Above we create a string that is directly defined by the filtered index on the query condition, as follows:

1000

If you define a variable, what happens when you compare it with a variable? First we create a filter index

870

Using variables to compare with query criteria, forcing the use of filtered indexes (clustered index by default)

870  = @ProductID

Looking at the query execution plan results, we need to add option recompile as follows:

870  = @ProductIDOPTION (RECOMPILE) 

The above-mentioned use of variables to query last through option recompilation is tested in SQL Server 2012 so that other versions are unknown, references to "the Pains of Filtered Indexes".

Summarize

In this section, we have learned to improve query performance by filtering the index, and also give its different scenarios and the advantages and disadvantages of its use. Short content, deep understanding, we'll see you next day, good night.

SQL server-focused filtered index improves query performance (10)