Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query running in SQL Server 2008:

select count(*)
from table1 join
table2 on table1.col1 = table2.col2

In the data warehouse where this script runs, a table called thin_table1 is exclusively populated by table1 (we use this thin table for indexed view creation. Read this answer for a little more detail).

The problem is that the optimizer is choosing to use thin_table1 rather than table1 during execution. This does not happen in SQL Server 2005. This new execution plan won't work for our current operation.

How can I turn off this "pass-through" feature, either at the database or session-level (or in SSIS)? I have many SSIS packages and stored procedures running during the data load, so I don't want to touch all the objects individually.

At this point, even knowing what that feature is called would be helpful in searching for the answer.

EDIT:
Went back to look at the same plan in 2005. Looks like it did happen there, but it to much less dramatic effect. I thought it was a problem just in 2008 but the same functionality appeared in 2005.

EDIT2:
A DBA here noticed that the plan is referencing an indexed view. We normally drop our indexed views at runtime, but in this test scenario, they were still built. Looks like when the indexed view is active, it will use that and any tables associated with it when the query executes.

Is there a way to bypass this automatic reference to indexed views?

share|improve this question

migrated from stackoverflow.com Feb 13 '13 at 22:14

This question came from our site for professional and enthusiast programmers.

    
I don't think so. I understand that I can specify a table to use, but I don't want to do that, considering the number of programs I'd need to change. I had a similar problem in Oracle which was a DB setting that we turned off so that it wouldn't use the other tables. I'm hoping this is available in Sql Server too. –  jabs Feb 13 '13 at 18:47
    
Not quite sure I understand. The problem is that the optimizer is choosing to use thin_table1 rather than table1 during execution. If thin_table1 is an indexed view (...it has a horrible name), are you expecting UPDATE table1 to only affect table1 and not the indexed view? Or the opposite? Are you sure you've correctly identified the actual performance problem here? Could it possibly involve wide update plans? –  Aaron Bertrand Feb 13 '13 at 18:55
    
thin_table1 is not an indexed view. It is used during the iView creation, since iViews lock the tables. It is a subset of rows and columns of table1. We don't want the dimensions (like table1) locked for iView creation, so we used this approach instead. –  jabs Feb 13 '13 at 19:15
    
Is that a new question for that group, or is this transferable? –  jabs Feb 13 '13 at 19:33
5  
The optimizer in Enterprise Edition can perform automatic indexed view matching, even where the query does not reference the view directly. You could try OPTION (EXPAND VIEWS) on the query to avoid this. –  Paul White Feb 13 '13 at 21:28

1 Answer 1

up vote 8 down vote accepted

There are only two possible ways that the execution plan for your query might not read from table1 directly. I will use the following indexed view created in the AdventureWorks sample database to illustrate:

CREATE VIEW dbo.IV
WITH SCHEMABINDING
AS
SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
GROUP BY
    p.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.IV (ProductID);

1. Table1 is an indexed view

In this case, the optimizer will make an estimated-cost-based decision whether to read from the indexed view or to expand the view and read from the base tables it references. Example:

SELECT
    ProductID,
    cnt
FROM dbo.IV;

The optimizer chooses to read from the indexed view directly:

Indexed view direct

If the optimizer chose instead to expand the view, we could use the NOEXPAND table hint to prevent that. This hint is required in non-Enterprise SKUs to access indexed views directly.

The EXPAND VIEWS query hint forces expansion of the view, leading to a plan that reads from the base tables:

SELECT
    ProductID,
    cnt
FROM dbo.IV
OPTION (EXPAND VIEWS);

EXPAND VIEWS

2. An indexed view exists which matches the query

SQL Server Enterprise Edition contains a feature which can match queries to indexed views, where the indexed view is not referenced in the query:

SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE
    p.ProductID BETWEEN 1 AND 100
GROUP BY
    p.ProductID;

Despite not mentioning our indexed view, the execution plan does:

Indexed view matching

About indexed view matching

This matching is only possible where the query processor has guarantees that the rewrite will always produce correct results. These guarantees include the fact that any changes to the tables referenced by the indexed view will also be reflected in the indexed view.

Any INSERT, UPDATE, DELETE or MERGE query that affects the Product or TransactionHistory tables will have extra operations added to the execution plan to make the appropriate changes to our indexed view.

The query optimizer does not keep track of any tables that might be created by the user from the base tables - they will not be maintained to reflect base table changes, and any indexed views created on these new tables will reflect only changes to those tables, not the originals. There is no magic here - the relationship between an indexed view and its base tables is very explicit.

The thin table example

To take an example that appears to match your question, say we create a 'thin' extract from the Product base table, containing only the ProductID column:

CREATE TABLE dbo.ThinProduct
(
    ProductID integer NOT NULL

    CONSTRAINT PK_ThinProduct
    PRIMARY KEY (ProductID)
);

INSERT dbo.ThinProduct
    (ProductID)
SELECT
    p.ProductID
FROM Production.Product AS p;

Now we drop the original indexed view and create a new one that references the thin table instead:

DROP VIEW dbo.IV;
GO
CREATE VIEW dbo.IVthin
WITH SCHEMABINDING
AS
SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM dbo.ThinProduct AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
GROUP BY
    p.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.IVthin (ProductID);

A new query that references the thin Product table directly, can use the new indexed view:

SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM dbo.ThinProduct AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE
    p.ProductID BETWEEN 1 AND 100
GROUP BY
    p.ProductID;

Thin indexed view

A query that references the original Product table cannot use this new indexed view, because there are no guarantees that IVthin will stay in step with any changes to the Product table (it will reflect changes to the ThinProduct table):

SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE
    p.ProductID BETWEEN 1 AND 100
GROUP BY
    p.ProductID;

The execution plan shows base table access - it cannot use the indexed view:

Base table access plan

Summary

Indexed view matching can only be performed where the appropriate guarantees are enforced by the engine. The scenario outlined in the question cannot occur as outlined there. The SSIS package must be referencing indexed views that can be expanded or issuing queries that can be matched to an indexed view for a query reference to table1 to resolve to anything other than that named object.

I appreciate this answer may not help you except in a general sense, but the discussion around the question has gone on for quite some time without clear specifics emerging. The question could be made easier for answerers to address specifically by including the actual SSIS query, table and indexed view definitions, and actual execution plans.

share|improve this answer
    
Thanks for this thorough answer Paul. It's cleared up some lingering questions for me. If I continue to have an issue, I'll post those additional details you mentioned. –  jabs Feb 15 '13 at 19:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.