未加星标

Filtered Indexes in SQL Server

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏
(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Today I want to talk about a specific kind of index in SQL Server: Filtered Indexes, which were introduced back with SQL Server 2008 so already very long time ago. A Filtered Index is a traditional Non-Clustered Index , which includes a Filter Predicate that restricts the rows that are indexes in that specific index.

Advantages of Filtered Indexes

Let’s start now by looking at the advantages of Filtered Indexes, and where they can be beneficial. Let’s have a look at the column PurchaseOrderNumber of the table Sales.SalesOrderHeader of the AdventureWorks2014 database. As you can see from the following T-SQL query, there are a lot of NULL values in that column. In my case 27659 records out of 31465 records have a NULL value.

SELECT COUNT(*), COUNT(PurchaseOrderNumber) FROM Sales.SalesOrderHeader GO

In the next step I create a Covering Non-Clustered Index for a specific query that I want to speed up.

-- Create a supporting Non-Clustered Index for the query CREATE NONCLUSTERED INDEX idxTest ON Sales.SalesOrderHeader(PurchaseOrderNumber, SalesPersonID) INCLUDE (OrderDate, ShipDate) GO -- When we execute the query, SQL Server can use the previous created -- Non-Clustered Index, but we have indexed a huge amount of NULL values that -- we don't request from this query... SELECT PurchaseOrderNumber, OrderDate, ShipDate, SalesPersonID FROM Sales.SalesOrderHeader WHERE PurchaseOrderNumber LIKE 'PO6%' AND SalesPersonID IS NOT NULL GO

When you run the query, you can see in the Execution Plan that the Non-Clustered Index is referenced by the Query Optimizer.


Filtered Indexes in SQL Server

When we check the size of the index through the Dynamic Management Function (DMF) sys.dm_db_index_physical_stats , you can see that the Non-Clustered Index consists of 132 pages: 1 index root page, and 131 pages in the leaf level.

-- The previous created Non-Clustered Index has 132 pages (1 + 131) SELECT * FROM sys.dm_db_index_physical_stats ( DB_ID('AdventureWorks2014'), OBJECT_ID('Sales.SalesOrderHeader'), NULL, NULL, 'DETAILED' ) WHERE index_id = (SELECT index_id FROM sys.indexes WHERE name = 'idxTest') GO

To make now the index structure more efficient, you can also create a Filtered Non-Clustered Index. When you think back to our query, we only request column values with a Non-NULL value in the column PurchaseOrderNumber . Therefore we can just filter the NULL values out from the index structure. So let’s recreate now the Non-Clustered Index as a Filtered Non-Clustered Index.

-- Recreate the Non-Clustered Index as a Filtered Index and filter out the NULL values CREATE NONCLUSTERED INDEX idxTest ON Sales.SalesOrderHeader(PurchaseOrderNumber, SalesPersonID) INCLUDE (OrderDate, ShipDate) WHERE PurchaseOrderNumber IS NOT NULL AND SalesPersonID IS NOT NULL WITH (DROP_EXISTING = ON) GO

When we check now again the index size through the DMF sys.dm_db_index_physical_stats , you will see now that the Filtered Non-Clustered Index consists of only 29 pages: 1 index root page, and only 28 pages in the leaf level.

In our case the query performance does’t get better, but you have created a much smaller index, and therefore you introduce less overhead in your database. Your Index Reorganize and Index Rebuild operations will be much faster, because they are dealing with a smaller index structure.

Disadvantages of Filtered Indexes

By now you know the advantages of Filtered Indexes in SQL Server. Let’s talk now about the disadvantages. Yes, there are also disadvantages! There are always some pro’s and con’s about everything. Like in real life!

Let’s work now with parameterized T-SQL statements. In my case I create a simple Stored Procedure that accepts a @PurchaseOrderNumber parameter.

CREATE PROCEDURE RetrieveData ( @PurchaseOrderNumber NVARCHAR(25) ) AS BEGIN SELECT PurchaseOrderNumber, OrderDate, ShipDate, SalesPersonID FROM Sales.SalesOrderHeader WHERE PurchaseOrderNumber = @PurchaseOrderNumber END GO

But when you execute now the Stored Procedure and you check the Execution Plan, you will see that the Query Optimizer hasn’t chosen the Filtered Index. You are scanning the whole Clustered Index of the table Sales.SalesOrderHeader .


Filtered Indexes in SQL Server

Why is the Query Optimizer not referencing the Filtered Non-Clustered Index? Because it’s not safe! Imagine that initially the Filtered Index is referenced in the Execution Plan. That Execution Plan get’s cached in the Plan Cache . When you now execute the Stored Procedure again with a different parameter value, SQL Server can reuse the already cached Execution Plan from the Plan Cache.

But what happens if you specifiy a NULL value as input parameter value? Then the cached plan wouldn’t return you a result, because the Filtered Non-Clustered Index doesn’t know anything about the NULL values. So the query result would be nothing, which is wrong! And therefore the Query Optimizer can’t reference a Filtered Index in a parameterized T-SQL statement!

Summary

Filtered Non-Clustered Indexes are great as long as you know their limitations. Normally parameterized T-SQL statements are great, because you don’t pollute the Plan Cache with Adhoc queries. But in combination with Filtered Non-Clustered Indexes in SQL Server they are somehow counterproductive. So please keep that in mind when you work on your indexing strategy.

Thanks for your time,

-Klaus

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Filtered Indexes in SQL Server
本站链接:https://www.codesec.net/view/610769.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(109)