When Indexed Views Don’t Work
There are two kinds of virtual objects which you can cause to be persisted resulting in faster query performance; views and computed columns.
By default views and computed columns are simply pre-defined SQL queries that are not realized until they are executed. However, if you create an index on a view or a computed column, the index is persisted, and may be used in place of real time realization of the view or computed column.
There are restrictions for persisting virtual data. The primary restriction is that the data, once computed, will not change from one query to the next. For example, a computed column returning the number of days a record was created to the current time could not be indexed because the result would be different as time progresses. This makes sense.
There are some other rules you can look up if you have interest at http://msdn.microsoft.com/en-us/library/ms191432.aspx.
I was recently reviewing the performance on a stored procedure based on an indexed view and found that it was not using the index in the actual query plan. Because the query was being performed on SQL Server 2008 Standard the first thing I looked for was a query hint WITH (NOEXPAND) in the statement. This tells SQL Server to not use the underlying tables in the view definition and consider using the index created for the view instead. You can read about this query hint at http://msdn.microsoft.com/en-us/library/ms181714.aspx.
Ok, the hint was in place; but, the query plan still did not use the index. I even tried forcing the index with a query hint (index=myindexontheview) with no change. The query optimize still refused to consider my wonderful index on the view which was perfectly formed for the task at hand.
Today I came across the solution. Some of the rules required to create the index on the view in the first place are also required in order to utilize the index while performing a query. The ANSI settings must be set to specific values in order for the index to be considered. They are:
- ANSI_NULLS ON
- ANSI_PADDING ON
- ANSI_WARNINGS ON
- ARITHABORT ON
- CONCAT_NULL_YIELDS_NULL ON
- NUMBERIC_ROUNDABORT OFF
- QUOTED_IDENTIFIER ON
Changing those settings resulted in a query plan utilizing the index, and performance immediately jumped. So, today I tip my hat to Kimberly Tripp for putting this tip in her book SQL Server Internals.
That’s my tip for the day.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Introduction to Windows Azure SQL Database (Part 1)
This article presents an overview of architecture and steps to configure Microsoft Windows Azure SQL Database.
Featured White Paper(s)
Top 10 Tips for Optimizing SQL Server Performance
read more)
Featured Script
dba3_modulus_Article
— Modeling Date Logic II: Queries, Functions, Procedures for Business Date & Time Calculations — http://bitonthewire.wpengine.com/see… (read more)