SQL Server Performance

Indexed view is not used

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by ysfinks, Jan 29, 2007.

  1. ysfinks New Member

    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.



  2. satya Moderator

  3. daustinash New Member

    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
    dnash@verndale.com
  4. ysfinks New Member

    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).
  5. hanson5b New Member

    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

Share This Page