We currenlty have a few customers where their querys would take ages to get results out of a partitioned view. On closer inspection we found the execution plan to scan every table, instead of just the ones with the relevant data.
To isolate this behaviour, I took the partitioned view example from https://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx and recreated our problem:
In this example, we have 12 tables (for each month of the year 1998):
CREATE TABLE Jan1998sales
(
OrderID INT,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL CHECK(DATEPART(yy, OrderDate) = 1998),
OrderMonth INT CHECK (OrderMonth = 1),
DeliveryDate DATETIME NULL CHECK(DATEPART(MM, DeliveryDate) = 1),
CONSTRAINT Jan1998sales_OrderIDMonth PRIMARY KEY (OrderID, OrderMonth)
)
We are looking at a view that looks as follows:
CREATE VIEW Year1998Sales
AS
(
SELECT * FROM Jan1998Sales
UNION ALL
SELECT * FROM Feb1998Sales
UNION ALL
SELECT * FROM Mar1998Sales
UNION ALL
[...]
UNION ALL
SELECT * FROM Dec1998Sales
)
Now we have one query that works perfectly fine (only scans the necessary tables):
SELECT * FROM Year1998Sales
WHERE (OrderMonth = 5 OR OrderMonth = 6) AND CustomerID = 64892
But if we filter with parameters, it suddently scans all tables:
DECLARE @MonthA int = 5
DECLARE @MonthB int = 6
SELECT * FROM Year1998Sales
WHERE (OrderMonth = @MonthA OR OrderMonth = @MonthB) AND CustomerID = 64892
My first guess to explain this behaviour would be that the Microsoft SQL Server builds a execution plan once, which scans all tables, and reuses this for every execution of this query, thus is scanning all tables at all times.
Does anyone know how we could get the SQL Server to only scan the necessary tables and still use a parameterized filter? Or can anyone confirm that this is a bug in the SQL Server execution plan builder?
For the full code have a look at this SQL Fiddle: http://sqlfiddle.com/#!6/e1f33/1
