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.

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

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.

2_SQL_Server_Filtered_Statistics_Update_Issue

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:

3_SQL_Server_Filtered_Statistics_Update_Issue

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 TwitterFollow me on FaceBook