Indexed view is not used | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Indexed view is not used

I am trying to evaluate performance of indexed views. So far results are very negative. Here is one of the examples:
I have 3 tables: Tenant(Id, Name, etc), Service(Id, Name, etc.) and intermediate ServiceTenant(Id, Tenant_Id, Sercice_Id) for m:m between Service and Tenant. 10000 records in Tenant, 200 in Service, 20000 in ServiceTenant. Id column is PK for ServiceTenant. I deliberately don’t have index on other columns in ServiceTenant.
Here is my view and clustered index:
create view [dbo].[vwTenantService] with schemabinding as
select t.id tenant_id, s.id service_id, t.name tenant_name, s.name service_name
from dbo.[Service] s, dbo.serviceTenant st, dbo.tenant t
where
t.id = st.tenant_id
and st.service_id = s.id CREATE UNIQUE CLUSTERED INDEX ixuvwTenantService ON vwTenantService(tenant_id, service_id) I run 3 queries:
select * from vwTenantService where tenant_id = 72057594037934470 — indexed view is not used — CPU is 15
select * from vwTenantService with (NOEXPAND) where tenant_id = 72057594037934470 – indexed view is used, index seek – CPU is 0
select * from vwTenantService where service_id = 72057594037928102 –- indexed view is used – index scan First query doesn’t use indexed view. It uses index scan on ServiceTenant, than inner join nested loops with IndexSeek on Service. Index scan cost is 0.17 + other operations.
Second query is forced to use index view, it’s index seek. Cost is 0.003
Third query is using index scan on indexed view.
Why indexed view is not used for first query? It’s much cheaper. I run enterprise edition (you can see that third query uses indexed view w/o NOEXPAND.
See thishttp://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx#ENH is any help. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
ysfinks, The above article posted by satya is awesom. I read it last night and cut a data scrub from 2 hours down to 5 minutes using an indexed view. From what I can gather, and I’m no indexed view expert yet, just because you create a view doesn’t mean the query will use it. If you create a view that joins several tables, then use the view in your statement, it may very well treat the view name as a programming convience and "expand" the view into the underlying tables during runtime, and use them and their indexes. Using with noexpand forces the query to treat the view as a static table with a clustered index. Going from .17 to .003 is a 98% performance improvement. Read that article. It’s long, but it will answer your questions for sure. dn daustinash
[email protected]
I am very much familiar with this article but thank you anyway.
daustinash, I am using the view directly in my statements (look at my statements).
Unfortunately behavior is inconsistent. I got situations when index is used but these are the cases when it’s practically irrelevant to pick up the index. In the case I described above when number of view records is 10000 index view is not used, when 1000000 – it’s used (which corresponds with article point of small number of records). But in another view, more complicated than original , and 1330000 records, count(*) from myview runs 50 sec CPU with not picking indexed view and runs 0.2 sec with (NOEXPAND).
If you name the view in your query and you want the system to process it against the materialized version of the view, then use the NOEXPAND hint in your query. SQL Server expands all view definitions into your query if you don’t use the NOEXPAND hint when you reference them. It then attempts to do indexed view matching during query optimization. If it can match the structure of your view to a substructure in the query, and it thinks using the indexed view will cost less than not using it, it will use it in the query plan it chooses. The cost estimates of the optimizer are not perfect, so sometimes it gets it wrong. Also, matching also has limitations, and is sometimes not attempted by the optimizer for very simple queries (like a single table SELECT our SELECT COUNT(*) query). Indexed view matching is mainly for the scenario where you can’t or don’t want to reference the view by name in your query. See my white paper for more details: http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
Eric
Lead Program Manager
Query Processing
SQL Server
]]>