SQL Server Filtered Statistics Update Issue

Hi Friends,

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
go
Use StatsDemo
go
--Create Partition Function
create partition function xpfstatsdemo (int)
as range right for values
(10000,20000)
go
--Create Partition Scheme
create partition scheme xpsstatsdemo
as partition xpfstatsdemo
ALL to ([Primary])
go
--Create table by using Partition Scheme on id column
create table xtstatsdemo
(
id int not null,
balance int not null,
name varchar(25)
) on xpsstatsdemo (id)
go
--Create a clustered index on id column [This will cover complete table]
create clustered index CI_xtstatsdemo
on xtstatsdemo (id);
go
--You can check here about how many partitions belongs to the table
select * from sys.partitions where object_id=OBJECT_ID('xtstatsdemo')
go
-- 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
while (@counter<=30000)
begin
    if (@counter%2=0)
        begin
        insert into xtstatsdemo values(@counter,@balance,'data1')
        end
    else
        begin
        insert into xtstatsdemo values(@counter,@balance,'data2')
        end
    set @counter = @counter + 1;
    set @balance = @balance + 1;
end
go
--You can check number of rows inside the table
Select count(*) from xtstatsdemo
go
--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)
where
balance>20000

Include Actual Execution Plan and ran below query. This will use filtered index here.

Select id,name from xtstatsdemo where balance>20000
go

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.

DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo')
go

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

go

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
go
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo')
go

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.

HAPPY LEARNING!

Regards

Prince Rastogi

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook