2

I have the following partitioned table

create table T (Month char(6), ID int, .... primary key (ID, Month)) on psMonth(Month)

The partition function is

CREATE PARTITION FUNCTION [pfMonth](char(6)) AS 
RANGE RIGHT FOR VALUES (N'200001', .... N'200705', N'200706', N'200707', ....)

However, the actual partition count of following query shows is 260 instead of 1? 

select count(*)
from T (nolock)
where Month = '200706'

The execution plan is 

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[globalagg1006],0)))
       |--Stream Aggregate(DEFINE:([globalagg1006]=SUM([partialagg1005])))
            |--Parallelism(Gather Streams)
                 |--Stream Aggregate(DEFINE:([partialagg1005]=Count(*)))
                      |--Clustered Index Scan(OBJECT:([DB].[dbo].[T].[PK_T]), WHERE:([DB].[dbo].[T].[Month]=[@1]))

Update:
The query run fast and the Actual Partition Count is 1 if I run the following query.

select count(*), $partition.pfMonth(Month)
from T with (nolock)
where Month = '200706'
group by $partition.pfMonth(Month)

Update 2:
The following query also only scan one partition.

select count(*), min($partition.pfByMonth(Month))
from T 
where Month = '200707'

Update 3:
The original SQL scans only one partition now. No clue what happened. The only difference is that more data has been inserted into the table.
Answer: I used a CTE which wrapped the table. And it worked around the issue caused by type precedence. (However, the CTE didn't work around the issue at first though, the whole thing is strange and unpredictable.)

Improve this question
0
9

Your column is of type CHAR(6). Your filter is of type VARCHAR (perhaps counter intuitive, but that what the constant literal '200706' is). The rules of Data Type Precedence dictate that the comparison occurs using the type with higher precedence, ie. the VARCHAR type. This is a different type than the partitioning function, therefore you do not get partition elimination.

Try this instead:

select count(*)
from T (nolock)
where Month = CAST('200706' as CHAR(6));

Your plan should get a partitioning elimination filter.

Improve this answer
5
  • It always scans only one partition now. I don't know what happened. Even if I change the where clause to where Month = cast('200706' as varchar(6)) – u23432534 Feb 14 '12 at 22:43
  • You didn't change the definition of the table/PF, did you? Ie. it still has the same column type and clustered index key as in your original post. – Remus Rusanu Feb 14 '12 at 22:49
  • The definition of the table/PF is not changed. One of my ssis package is still loading the data into this table. It's really strange that suddenly the query only use one partition now. – u23432534 Feb 14 '12 at 22:56
  • It scans all the partitions if I change where clause to where Month = 200706. Is it any setting force the constant string literal char instead of varchar? – u23432534 Feb 14 '12 at 23:10
  • In fact I found that it can be worked around by using CTE to wrap the partitioned table. Very strange. – u23432534 Feb 16 '12 at 4:49
2

This is directly related to your other question.

You don't have an index on your partition key alone, it's the second key on your PK. Because of this the engine needs to check every row. If you had an index on that field then it could check just the index.

Improve this answer
10
  • Why the second query (in the update section of my question) only scan one partition without a index? – u23432534 Feb 14 '12 at 20:16
  • 1
    Because you are directly invoking the partition you need. The optimizer can't infer the partition you need without an index to point the way. – JNK Feb 14 '12 at 20:18
  • There is no condition filtering the result by partition ID though. In fact, I tried select count(*), min($partition.pfMonth(Month)) from T where Month = '200708' and it still only scan one partition. – u23432534 Feb 14 '12 at 20:24
  • 2
    By invoking $partition you are specifying the partition to search, albeit indirectly. Without this guidance the engine needs to check all the partitions because, again, you have no index on the field you are checking – JNK Feb 14 '12 at 20:26
  • @NickW, JNK is right. The $partition tells the optimizer where to look. If you want elimination to happen automatically without specifying the partition function, SQL Server needs some other clue (e.g. a non-clustered index on the Month column). – Aaron Bertrand Feb 14 '12 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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