Once the query has executed successfully, click EXECUTION PLAN tab to compare the performance of the queries before and after the creation of Filtered Index on HUMANRESOURCES.EMPLOYEE table. You could see that in the first scenario when the query ran before the creation of Filtered Index, the index scan is 100% done on Clustered Index taking 25% of total cost of query execution. In the second scenario when the query ran after the creation of Filtered Index on HUMANRESOURCES.EMPLOYEE table, the index scan was 50% on Non Clustered Index and another 50% on the Clustered Index and the cost of query was just 19%. This is a significant improvement even thought the table is just having 290 rows. From this example it’s clear that if Filtered Index is defined correctly, then it will result in significant improvement when created on larger tables to retrieve a very small percentage of data.
The below TSQL once executed will let you know the number of rows which will be resulted by the Filtered Index. You could see that in the above mentioned TSQL code we had created a new Filtered Index namely FI_DEPARTMENT.
USE ADVENTUREWORKS
GO
— FIND NUMBER OF ROWS IN INDEXES
SELECT NAME, I.INDEX_ID, [ROWS]
FROM SYS.PARTITIONS P
INNER JOIN SYS.INDEXES I
ON P.[OBJECT_ID] = I.[OBJECT_ID]
AND P.INDEX_ID = I.INDEX_ID
WHERE P.[OBJECT_ID] = OBJECT_ID(‘HUMANRESOURCES.EMPLOYEE’)
GO
You could see that the newly created FI_DEPARTMENT Filtered Index has only one row. This means that when you are executing the query to search for records where TITLE = ‘FINANCE MANAGER’, the databases engine uses the newly created Filtered Index their by improving performance considerably even for a small table like HUMANRESOURCES.EMPLOYEE in AdventureWorks database.
Conclusion
Before creating Filtered Indexes you need to first analyse the queries which are used by your application and how they identify the subset of data. If the subset of data returned is very small then it is ideal to create Filtered Indexes. Before creating Filtered Indexes in production a thorough investigation is a must.
]]>