SQL Server 2008 filtered indexes in 5 minutes

Posted by Jason on Saturday, February 23, 2008 to SQL Server 2008, SQL performance tuning, tsql, Indexes, CTP6, In 5 minutes
3477 Views | 1 Comments |

My jaw literally dropped when I saw it. If this works as advertised, it has the potential of changing everything. The days of over indexing will be over. Dynamic indexing off of the missing and unused index DMV's could be possible especially with added support. I also think this will better accomplish what people tried to do with partitioning for performance reasons in SQL Server 2005.

It will be really interesting to see how it gets applied in production and where this leads in the next versions. Maybe an autoindex checkbox will replace the DBA :)

This is a small and quick example. We will have to see how it scales to larger environments.

 

use adventureworks

--Let's nullify a random 400

update  Production.WorkOrder

set EndDate = null

where WorkOrderID in (select top 400 WorkOrderID from Production.WorkOrder where enddate is not null)

--Let's give a random 200 a recent date to mimic prod data

update top (200) Production.WorkOrder

set duedate = getdate()-10

where enddate is null

--Base query
--This is query type that should be simple yet common
--Let's get open WO's that will be due soon

set statistics io on

select p.Name, wo.OrderQty, wo.DueDate

from Production.WorkOrder wo JOIN Production.Product p on wo.ProductID=p.ProductID

where wo.EndDate is null and wo.DueDate >= '2007-11-24'

 --CI Scan with loop join

--Table 'WorkOrder'. Scan count 1, logical reads 530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Now let's create a covering index

create index ix01 on Production.WorkOrder(EndDate,DueDate, ProductID) include (OrderQty)

--Run the base query

set statistics io on
select p.Name, wo.OrderQty, wo.DueDate
from Production.WorkOrder wo JOIN Production.Product p on wo.ProductID=p.ProductID
where wo.EndDate is null and wo.DueDate >= '2007-11-24'

--Does NCI seek

--Table 'WorkOrder'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

--Clean up

drop index [Production].[WorkOrder].[ix01]

 

--Create Filter index

create index ix01 on Production.WorkOrder(EndDate,DueDate, ProductID) include (OrderQty) where EndDate is null and DueDate >= '2007-11-24'

--Run base query

set statistics io on
select p.Name, wo.OrderQty, wo.DueDate

from Production.WorkOrder wo JOIN Production.Product p on wo.ProductID=p.ProductID

where wo.EndDate is null and wo.DueDate >= '2007-11-24'

 

--It uses it. We have a real small sample but it performs better

--Table 'WorkOrder'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Now let's look at size
--Create unfiltered index for comparison

create index ix02 on Production.WorkOrder(EndDate,DueDate, ProductID) include (OrderQty)

--note the new syntax

declare @dbid int = db_id()

declare @objid int = object_id('[Production].[WorkOrder]')

select * from sys.dm_db_index_physical_stats(@dbid, @objid, null, null, 'detailed') ps join sys.indexes i

on ps.object_id=i.object_id and ps.index_id=i.index_id

and i.name in ('ix01', 'ix02') and i.type_desc='NONCLUSTERED'

--We are looking at 2 page vs. 301 pages

That is a huge difference in size. The major point is not the fact that we retrieve the same while taking up so much less space on disk but so much less space in memory as well. We are going to get into this much more!

 

 Technorati Tags: SQL Server 2008,Filtered indexes,CTP6