In my earlier blog, I just explain about the importance of filtered indexes. Here is the Link;
Some limitations are also there for filtered indexes. When we create indexes then by default it will also create statistics. Those statistics will be updated based on threshold values. Some of them are mention below which we will use in our blog today:
1. If number of rows in table <= 500 at the time of last statistics modified then statistics will be marked as stale if number of modification >=500
2. If number of rows in table > 500 at the time of last statistics modified then statistics will be marked as stale if 20% of data + additional 500 changes will be made.
But the problem is these rules will not be applied on filtered indexes. Let me show you this practically.
Here I am using a table which is having three partitions on id columns. Partition 1 will contain rows with values for id column 1-10000, Partition 2 will contain rows with values for id column 10001-20000 and Partition 3 will contain rows with values for id column 20001-30000.
--Create a database for Demo at default files location
create database StatsDemo
--Create Partition Function
create partition function xpfstatsdemo (int)
as range right for values
--Create Partition Scheme
create partition scheme xpsstatsdemo
as partition xpfstatsdemo
ALL to ([Primary])
--Create table by using Partition Scheme on id column
create table xtstatsdemo
id int not null,
balance int not null,
) on xpsstatsdemo (id)
--Create a clustered index on id column [This will cover complete table]
create clustered index CI_xtstatsdemo
on xtstatsdemo (id);
--You can check here about how many partitions belongs to the table
select * from sys.partitions where object_id=OBJECT_ID('xtstatsdemo')
-- Now I am inserting 30000 rows in the table. That means each partition will contain 10000 rows here
-- Here id and balance both are having same values for demo purpose
declare @counter int =1
declare @balance int =1
insert into xtstatsdemo values(@counter,@balance,'data1')
insert into xtstatsdemo values(@counter,@balance,'data2')
set @counter = @counter + 1;
set @balance = @balance + 1;
--You can check number of rows inside the table
Select count(*) from xtstatsdemo
--Now I am creating filtered index
--[As per balance column values this index will cover whole Patition3]
create NonClustered index IX_xtstatsdemo
on xtstatsdemo (id,name)
Include Actual Execution Plan and ran below query. This will use filtered index here.
Select id,name from xtstatsdemo where balance>20000
As I have already mention that by default every index create stats with the same name as of index, so let’s check stats details created by filtered index. Stats created by filtered index are known as filtered statistics.
Now I am going to delete 5000 rows from the table. These rows will be deleted from third partition also.
–Keep in mind that this will only mark stats as stale
delete from xtstatsdemo where id>25000
Here it is clear that initially my table was having 30000 rows while filtered index was having 10000 rows which belongs to Partition3 so if we talk about filtered index, i had deleted 5000 rows from it i.e. 50% of filtered index data. But if we will compare it to table then it is only 5000 rows out of 30000 rows i.e. 16.66%. So as per stats update threshold values filtered stats should be updated. Let’s check this by running below query:
--This Select statement should update the statistics
Select id,name from xtstatsdemo where id>20000 and balance>20000
Shocked… Stats last update time is still same as we had seen earlier i.e. stats has not been updated. We already deleted 5000 rows; however numbers of rows are still 10000 as per stats information. This is the reason for taking extra care while you are using Filtered indexes. Now we can say Filtered stats not follow the standard threshold vales for update stats. Filtered Stats update will depend on complete table update stats threshold values.