Problem Statement: Sometimes SQL server Optimizer is not able to use correct statistics to generate the best query plan for execution of the Query. This situation can occur even if statistics are updated, in that scenario filtered statistics are quite useful. This feature of filtered statistics was introduced in SQL server 2008 and available in all version post 2008 version.
Solution: To understand filtered statistics, we will create two tables and put some data in those tables using below script.
Create table Employee(Eid int,Name Varchar(100))
Create table SalesInfo(Eid int,SalesAmount int)
Insert into Employee values(101,’Neeraj’)
Insert into Employee values(102,’Raj’)
Insert into Employee values(103,’Shiva’)
Insert into Employee values(104,’Ramesh’)
Insert into Employee values(105,’Prashant’)
insert into SalesInfo values(101,2000)
insert into SalesInfo values(103,2500)
insert into SalesInfo values(104,200)
insert into SalesInfo values(105,1200)
In salesInfo table, we will insert 5000 records against Eid=102 using below SQL script.
Declare @counter as int
insert into salesinfo values(102,@counter)
Now we will create indexes on both tables using below SQL script.
Create clustered index Clustered_Index_emp on employee(Eid)
Create index Index_Emp_name on employee(name)
Create clustered index Clustered_index_salesInfo on SalesInfo(Eid)
Lets create statistics using below script.
CREATE STATISTICS index_Employee_EID_name ON Employee(EID, name)
Now we will update the statistics of both tables so that we have updated statistics to be used.
UPDATE STATISTICS Employee WITH fullscan
UPDATE STATISTICS SalesInfo WITH fullscan
Now we will check the actual execution plan of the query(below Query) by pressing (Ctrl + M) key before executing the query.
select Employee.Eid,name,SalesAmount from Employee Inner join SalesInfo on Employee.Eid=SalesInfo.Eid
we can see in the above screenshot estimated number of rows for above query is 1001 while actual number of rows is 1 that is incorrect. lets created a filtered statistics and see the difference.
Now we will created filtered statistics on Employee table using below SQL script.
CREATE STATISTICS Name_employee ON Employee (Eid)
WHERE name = ‘Neeraj’
Lets check the actual execution plan of the same query again and see the difference.
The above screenshot of execution plan is clearly showing the difference, now estimated and actual number of rows are same and which is correct also.
Conclusion: This example shows how filtered statistics can help query optimizer to choose best execution plan to execute a query with great efficiency.
882 total views, 3 views today