Partitioned View Pruning Issue

I'm

using Sql Server 2000 Standard Edition,

service pack 3. I'm fairly new to

partitioned views and merge replication.

We have a 3-year set of monthly

tables with about 1 million rows per table from some code we inherited. To ease development, I'm trying to create a

local partitioned view. We wanted it to be UPDATE-able and be able to select

data by a date range or by id without incurring very much cost. The problem is

that my SELECT statements correctly prune the unneeded tables while my INSERT, UPDATE ,

and DELETE statements do not.

Here's our basic structure:

CREATE

TABLE [dbo].[Call_2004_01] (
[id] [varchar] (64) COLLATE

SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[callStartedOn] [datetime] NOT

NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT

[DF_Call_2004_01_rowguid] DEFAULT (newid()),
CONSTRAINT

[PK_Call_2004_01] PRIMARY KEY CLUSTERED
(
[id]
)

WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_Call_2004_01_id]

CHECK ([id] >= '200401' and [id] < '200402')
) ON

[PRIMARY]

CREATE TABLE [dbo].[Call_2004_02] (
[id] [varchar] (64)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[callStartedOn]

[datetime] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL

CONSTRAINT [DF_Call_2004_02_rowguid] DEFAULT (newid()),
CONSTRAINT

[PK_Call_2004_02] PRIMARY KEY CLUSTERED
(
[id]
)

WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_Call_2004_02_id]

CHECK ([id] >= '200402' and [id] < '200403')
) ON

[PRIMARY]

CREATE VIEW
Call
AS
SELECT * FROM

dbo.Call_2004_02
UNION ALL
SELECT * FROM

dbo.Call_2004_01
GO

The ids themselves contain the date information,

so we decided to use the id as the partition key. (I originally had the date and

the id as the primary key and it still didn't work.)

If you

run:

SET STATISTICS IO ON
SELECT * FROM Call WHERE [id] =

'20040104-fake-asdf'

You'll get the exact query plan and IO you would

expect. It only shows the Call_2004_01 table. However, if you run:

SET

STATISTICS IO ON
UPDATE Call SET [callStartedOn] = '2004-01-01 00:00:00.000'

WHERE [id] = '20040104-fake-asdf'

or

SET STATISTICS IO

ON
DELETE FROM Call WHERE [id] = '20040104-fake-asdf'

It scans all of

the tables in the view. If I add an index on top of the primary key, the

STATISTICS IO comes back as 0's for the unused table, but the query plan still

shows execution on them.

To add injury to insult, we have merge

replication set up for these tables. So, when the INSERT, UPDATE, or DELETE statement

occurs on the tables that it isn't supposed to run on, the triggers for those

tables fire. This becomes apparent when you look at the STATISTICS IO, query

plan, and/or the fact that a simple query which should take less than 1 second

is now taking at least 8.

The problem goes away, of course, when you

specify the table name instead of the view.

So, what am I missing here?

At this point, I'm just grasping at straws.

Also, I tried upgrading to service pack 4 just now and that didn't change anything.|||

It is a bug in SQL Server 2000 due to auto-parameterization. It has been fixed in SQL Server 2005 so your examples will work fine there. You can make it work in SQL Server 2000 by adding some bogus parameter to the WHERE clause of the UPDATE & DELETE statement.

declare @.p int

SET STATISTICS IO ON

UPDATE Call SET [callStartedOn] = '2004-01-01 00:00:00.000'

WHERE [id] = '20040104-fake-asdf' and @.p is null

go

declare @.p int

SET STATISTICS IO ON

DELETE FROM Call WHERE [id] = '20040104-fake-asdf' and @.p is null

go

To summarize, the partition pruning will not happen in cases where auto-parameterization of the statement occurs. This typically happens in cases where you use constants/literals in WHERE clause.

|||

Thanks. I'll give that a try when I get back to work tomorrow. However, I had originally been trying with a variable when I noticed the problem. Something like

DECLARE @.callId varchar(64)
SET @.callId = '20040104-fake-asdf'
DELETE FROM Call WHERE [id] = @.callId

Maybe the bogus data will help.

|||The bogus data helped some. Now it's behaving the same way it would if I manually created additional indexes on the PK. However, it's still not properly pruning the data out of the view. Here's the query I'm running and the output I'm getting.

DECLARE @.p bit
SET @.p = NULL

DECLARE @.callId varchar(64)
SET @.callId = '2004010100281315'

DELETE FROM Call WHERE [id] = @.callId AND @.p IS NULL

STATISTICS IO (I deleted quite a bit):
Table 'Call_2006_12'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
... [All other tables between are the same as the above] ...
Table 'Call_2004_02'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'Call_2004_01'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

(0 row(s) affected)

Table 'sysmergearticles'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'MSmerge_replinfo'. Scan count 22, logical reads 44, physical reads 0, read-ahead reads 0.
Table 'sysmergesubscriptions'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'MSmerge_tombstone'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'MSmerge_contents'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sysmergearticles'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

(15 row(s) affected)

[the above (starting with the first "Table 'sysmergearticles'") repeats 35 more times]

SHOWPLAN_TEXT Results (again, edited for size):
|--Sequence
|--Clustered Index Delete(OBJECT:([Callbright].[dbo].[Call_2004_01].[PK_Call_2004_01]))
| |--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
| |--Filter(WHERE:(STARTUP EXPR([@.callId]<'200402' AND [@.callId]>='200401')))
| |--Clustered Index Seek(OBJECT:([Callbright].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]=[@.callId]) ORDERED FORWARD)
... [All other tables between are the same as the above] ...
|--Clustered Index Delete(OBJECT:([Callbright].[dbo].[Call_2006_12].[PK_Call_2006_12]))
|--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
|--Filter(WHERE:(STARTUP EXPR([@.callId]<'200701' AND [@.callId]>='200612')))
|--Clustered Index Seek(OBJECT:([Callbright].[dbo].[Call_2006_12].[PK_Call_2006_12]), SEEK:([Call_2006_12].[id]=[@.callId]) ORDERED FORWARD)

Also, I should mention that the Clustered Index Delete takes 2% of the execution time and performs 1 execution while the Clustered Index Seek takes 1% of the execution time and performs 0 executions on the tables that should be pruned.

Any ideas?|||

There are two ways in which the partitions can be eliminated and it depends on the type of query.

1. Partition elimination at compile-time - This will happen usually if you specify the partition key value as a constant in the WHERE clause. You can observe this behavior in the showplan output (estimated or actual execution plan)

2. Partition elimination at run-time - This will happen in cases where the partition key value is not known at compile-time or the plan needs to handle cases where the key could change between executions and so on. In this case, you will not see the partition(s) being eliminated in the showplan output (estimated or actual). You will however see STARTUP EXPR filters in the various branches. You can think of this as short-circuit mechanisms that gets evaluated at run-time and prevents that branch from executing.

So given these different plan strategies, the best way to determine which partition(s) are being eliminated is to look at the SET STATISTICS IO output and look for read counters with zero values to identify the partition(s) that are being eliminated. Another technique is to look at SET STATISTICS PROFILE ON (or SET STATISTICS XML PROFILE ON in SQL Server 2005) output. In this case, you will get the actual execution plan along with the execution statistics. You would specifically look at rows in the SET STATISTICS PROFILE output (In SQL Server 2000) for rows which have EXECUTES = 0 and/or ROWS = 0. This will tell you the partition(s) that were eliminated.

To summarize, from your showplan output the partition elimination is happening fine and it uses mechanism #2 described above. As for the replication tables, my guess is that it is a result of the replication trigger on the table that is propogating the deletes. Hope this helps.

|||I would have thought that if the partition was actually doing any pruning that the merge subscription triggers on the pruned tables wouldn't be firing. Is that wrong?|||Here's my query and SET STATISTICS PROFILE ON output. As you can see, it's still performing 3 executions per "pruned" table (and one of those is a delete). Shouldn't this be all zeros except for the Call_2004_01 table?

SET STATISTICS PROFILE ON
DECLARE @.bogusVariable bit
DELETE FROM Call WHERE [id] = '2004010100000000001' AND @.bogusVariable IS NULL

0 1 DELETE FROM Call WHERE [id] = '2004010100000000001' AND @.bogusVariable IS NULL 436 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 0.4912329 NULL NULL DELETE 0 NULL
0 1 |--Sequence 436 2 1 Sequence Sequence NULL NULL 1.0 0.0 3.6000001E-5 56 0.4912329 NULL NULL PLAN_ROW 0 1.0
0 1 |--Clustered Index Delete(OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01])) 436 3 2 Clustered Index Delete Delete OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01]) NULL 1.0 1.0161194E-2 0.000001 56 1.3445652E-2 NULL NULL PLAN_ROW 0 1.0
0 1 | |--Filter(WHERE:(STARTUP EXPR([@.bogusVariable]=NULL))) 436 5 3 Filter Filter WHERE:(STARTUP EXPR([@.bogusVariable]=NULL)) NULL 1.0 0.0 2.7999999E-7 311 3.2833579E-3 [Bmk1006] NULL PLAN_ROW 0 1.0
0 1 | |--Clustered Index Seek(OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]='2004010100000000001') ORDERED FORWARD) 436 6 5 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]='2004010100000000001') ORDERED FORWARD [Bmk1006] 1.0 3.2034749E-3 7.9603E-5 311 3.2830781E-3 [Bmk1006] NULL PLAN_ROW 0 1.0
0 1 |--Clustered Index Delete(OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02])) 436 9 2 Clustered Index Delete Delete OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02]) NULL 1.0 1.0161523E-2 0.000001 56 1.3445981E-2 NULL NULL PLAN_ROW 0 1.0
0 1 | |--Filter(WHERE:(STARTUP EXPR([@.bogusVariable]=NULL))) 436 11 9 Filter Filter WHERE:(STARTUP EXPR([@.bogusVariable]=NULL)) NULL 1.0 0.0 2.7999999E-7 307 3.2833579E-3 [Bmk1008] NULL PLAN_ROW 0 1.0
0 1 | |--Filter(WHERE:(STARTUP EXPR(0))) 436 12 11 Filter Filter WHERE:(STARTUP EXPR(0)) NULL 1.0 3.2034749E-3 7.9603E-5 307 3.2830781E-3 [Bmk1008] NULL PLAN_ROW 0 1.0
0 0 | |--Clustered Index Seek(OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02]), SEEK:([Call_2004_02].[id]='2004010100000000001') ORDERED FORWARD) 436 13 12 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02]), SEEK:([Call_2004_02].[id]='2004010100000000001') ORDERED FORWARD [Bmk1008] 1.0 3.2034749E-3 7.9603E-5 307 3.2830781E-3 [Bmk1008] NULL PLAN_ROW 0 1.0
... [The rest of the tables are here and mirror the above statement] ...
0 1 |--Clustered Index Delete(OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12])) 436 281 2 Clustered Index Delete Delete OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12]) NULL 1.0 1.6756756E-2 0.000001 56 2.0041214E-2 NULL NULL PLAN_ROW 0 1.0
0 1 |--Filter(WHERE:(STARTUP EXPR([@.bogusVariable]=NULL))) 436 283 281 Filter Filter WHERE:(STARTUP EXPR([@.bogusVariable]=NULL)) NULL 1.0 0.0 2.7999999E-7 324 3.2833579E-3 [Bmk1552] NULL PLAN_ROW 0 1.0
0 1 |--Filter(WHERE:(STARTUP EXPR(0))) 436 284 283 Filter Filter WHERE:(STARTUP EXPR(0)) NULL 1.0 3.2034749E-3 7.9603E-5 324 3.2830781E-3 [Bmk1552] NULL PLAN_ROW 0 1.0
0 0 |--Clustered Index Seek(OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12]), SEEK:([Call_2006_12].[id]='2004010100000000001') ORDERED FORWARD) 436 285 284 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12]), SEEK:([Call_2006_12].[id]='2004010100000000001') ORDERED FORWARD [Bmk1552] 1.0 3.2034749E-3 7.9603E-5 324 3.2830781E-3 [Bmk1552] NULL PLAN_ROW 0 1.0

After this, there's a bunch of stuff for merge replication which really means the triggers on the "pruned" tables fired. To me, this indicates some kind of a pruning error (the table definitions are wrong, sql server 2000 is wrong, etc.). Am I wrong in thinking that? Does Sql Server 2000 execute insert/update/delete statements this way by design?|||

Don't look at the DELETE operator executes only. Start with the clustered index seek for each partition and you can see how many rows it produces & the number of executes. This way you can figure out if a parent operator (delete in this case) did any work. I tried your example with some sample data and following DELETE statement:

-- I added one row in each partition:

DECLARE @.p bit
DELETE FROM Call WHERE [id] = '20040104-fake-asdf' and @.p is null

-- set statistics io output

/*

Table 'Call_2004_01'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'Call_2004_02'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
*/

-- set statistics profile partial output

/*

Rows Executes StmtText

- -- -
1 1 DELETE FROM Call WHERE [id] = '20040104-fake-asdf' COLLATE SQL_Latin1_General_CP1_CI_AS and @.p is null
1 1 |--Sequence
0 1 |--Clustered Index Delete(OBJECT:([MyTestDB].[dbo].[Call_2004_02].[PK_Call_2004_02]))
0 1 | |--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
0 1 | |--Filter(WHERE:(STARTUP EXPR(0)))
0 0 | |--Clustered Index Seek(OBJECT:([MyTestDB].[dbo].[Call_2004_02].[PK_Call_2004_02]), SEEK:([Call_2004_02].[id]='20040104-fake-asdf') ORDERED FORWARD)
1 1 |--Clustered Index Delete(OBJECT:([MyTestDB].[dbo].[Call_2004_01].[PK_Call_2004_01]))
1 1 |--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
1 1 |--Clustered Index Seek(OBJECT:([MyTestDB].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]='20040104-fake-asdf') ORDERED FORWARD)

*/

Now, zero rows went through the delete operators but we will still fire the triggers on the partition tables. The triggers should be intelligent enough to perform no work in this case. This is also no different than doing DELETE t WHERE 1=0 which will still fire the trigger. This may seem kind of strange but this is how triggers work. There is one exception in case of distributed partitioned views where the triggers may not fire on the remote tables. Hope this helps explain the behavior you are seeing.

And you should start a different thread in the replication newsgroup about their trigger behavior in this case.

|||

I guess I figured that the view would prevent any execution on a table that didn't meet the constraints, but I can see how your example is related to this situation. The subscription triggers are smart enough not to perform any inserts, but they do perform enough work to cause problems. I'd considered modifying them to make them do even less work (where it would check the Inserted and Deleted tables for any rows before executing), but maintenance on those triggers seemed painful since they're auto-generated, so instead, I added INSTEAD OF triggers to the view that prune the tables the way I expected them to be pruned.

Thanks.