Filtered Index Limitation

Filtered Index feature has been introduced since SQL Server 2008. In my recent survey with some Data Analysts, Database Developers and DBAs, it seems that Filtered Index has been ignored or forgotten. So here’s some reminder on why it’s a good idea to rethink about using Filtered Index.

Filtered index characteristics:

  • an optimised nonclustered index
  • containing only the specified subset of data, specified by the filter predicate
  • referring to column(s) of the table which it belongs to

Having Filtered Index is particularly useful if only the same subset of data is being queried very frequently (or almost all the time). Filtered index will be smaller in size compared to an index with the same definition without filtered index.

Below is an illustration of the Production.WorkOrder set and its subset data where DueDate between 1 Jan 2011 and 30 Jun 2011.

Set Diagram representing Production.WorkOrder records

 

The Non-Filtered index size is much larger in comparison to the Filtered index size as shown below.  The smaller the index size, the quicker for SQL Server will fetch the data.

Non Filtered Index vs Filtered Index Size Comparison

 

A couple of basic usage examples are listed below:

1. Reports are generated only for the year to date data.

A good example will be creating an index filtering the date column from the beginning of the year. e.g.

CREATE NONCLUSTERED INDEX IX_Production_WorkOrder_DueDate ON Production.WorkOrder
(
     DueDate
)
WHERE DueDate >= '01 Jan 2011' 

Of course, at the beginning of the year next year, you may wish to change the DueDate clause to refer to “01 Jan 2012″ (instead of “01 Jan 2011″).  To avoid manually changing the filter every year, you could create a SQL Server job that dynamically alters the index so that the filtered index is referring to the new DueDate filter at the specified date of the year.

 

2. Data is processed only for Customers that have email address ID value.

If there is more data with Non Nullable EmailAddressID values than the NULL EmailAddressID, creating filtered index will reduce size and therefore improve on performance (compared to creating a non-filtered index).

CREATE NONCLUSTERED INDEX IX_Person_Person ON Person.Person
(
     EmailAddressID
)
WHERE EmailAddressID IS NOT NULL

 

More scenarios and best practice have been documented here on BOL: http://msdn.microsoft.com/en-us/library/cc280372(v=SQL.100).aspx

The filter predicate only supports simple comparison operator such as the following logical operators:

  1. =
  2. !=
  3. <
  4. <=
  5. >
  6. >=
  7. IS NULL
  8. IS NOT NULL

This means “LIKE” and “BETWEEN … AND …” comparisons cannot be used.  However, there is a workaround  to the “BETWEEN … AND …”  comparison, i.e. by replacing it with the “>=” and “<=” operators, such as shown in the snippet below.

CREATE NONCLUSTERED INDEX IX_Production_WorkOrder_DueDate ON Production.WorkOrder

(
     DueDate
)
WHERE DueDate >= '01 Jan 2011' AND DueDate <= '30 Jun 2011'
-- equivalent to DueDate BETWEEN '01 Jan 2011' AND '30 Jun 2011

 

When implementing Filtered Indexes, I would recommend running the result of turning on Statistics IO for before and after.  This will give you a better understanding of how much improvement you will gain with the Filtered Index.

An article from MSSQLTips worth reading to get to know more about Filtered Index can be found here: http://www.mssqltips.com/sqlservertip/1785/sql-server-filtered-indexes-what-they-are-how-to-use-and-performance-advantages/

Important Trace Flag that Every Expert DBA Should Know

I have recently co-presented with Victor Isakov (from SQL Server Solutions) at the Sydney SQL Server User Group on “Important Trace Flag that Every Expert DBA Should Know. The presentation went quite well and I learnt more things that I could imagine from co-presenting.

Some of the trace flags we discussed were:

1204

This trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in text format. In SQL Server 2008, this trace flag is only available at the Global Level (i.e. applies to the SQL Server instance). In my experience, it is worth turning this trace flag on, only for debugging purposes. A wealth of information on Deadlocks can be found here: http://msdn.microsoft.com/en-us/library/ms178104.aspx

 

1222

SImilar to trace flag 1204, this trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in XML format.

 

3226

In an environment where database backup is frequently performed, it is a good idea to turn trace flag 3226 on, to supress an entry to the SQL Server Error Log for each backup.  This is beneficial as the bigger the log, the harder it is to find other messages.  However, you will need to ensure that none of your scripts or systems rely on the backup entry detail on the SQL Server Error Log.

 

There are more trace flags that are covered in the presentation.  Above are the basic ones that every DBAs should know and would one way or another be very helpful in running production environment.

The presentation will be run again in a few weeks time (13 October 2011) at PASS Summit 2011 in Seattle!  I’m certainly looking forward to co-presenting it again with Victor Isakov (SQL Server Solutions).

See you in Seattle [PASS Summit 2011]

 

 

Edit (25 Feb 2012): As requested, below is a sample of a stored procedure that can be executed at the database server start up to enable certain trace flags. Please ensure you understand and test the trace flags before you turn them on, especially in Production environment.  Special thanks to Victor Isakov for providing the sample code.  This stored procedure was covered at the PASS Summit 2011 presentation and the slide deck is downloadable from here.

 

USE [master]
GO

CREATE PROC [dbo].[EnableTraceFlags]
-- Author  : Victor Isakov
-- Company : SQL Server Solutions (http://www.sqlserversolutions.com.au)
-- Purpose : Enable global trace flags upon SQL Server startup.
-- Notes   : Need to execute sp_procoption to enable this stored procedure to autoexecute
--           whenever SQL Server instance starts:
--           EXEC sp_procoption 'dbo.EnableTraceFlags', 'startup', 'true'
-- Bugs    : None
-- Version : 1.0
-- History : 
-- DATE       DESCRIPTION
-- ========== ==================================================
-- 11/04/2011 Version 1.0 released.
AS
DBCC TRACEON (4199, -1);
-- Enable Query Optimiser fixes (http://support.microsoft.com/kb/974006)
DBCC TRACEON (1222, -1);
-- Write deadlocks to errorlog (BOL)
DBCC TRACEON (3226, -1);
-- Supress successfull backup messages (BOL)
GO

EXEC sp_procoption N'[dbo].[EnableTraceFlags]', 'startup', '1'
GO