How can I make sure the SQL Server query optimizer uses the exact tables in the query

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?

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:

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);

1. 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:

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;

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:

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.

StackExchange Database Administrators Q#34698, answer score: 8