2

We have a partitioned tables partitioned on STATUS Column using below partitioning function. So a table will have total two partitions and both are going to different filegroups as per partition scheme.

CREATE PARTITION FUNCTION PART_FN_STATUS(INT) AS RANGE RIGHT FOR VALUES (1000)

NOTE: As per buisness logic, status cannot be greater than 1000. 1000 is Max.

We have a Non Clustered Index on Status Column as well which is an Aligned Partitioned Index, i.e partitioned on Status Column.

When I run below query and observe actual execution plan, I have below questions:

SELECT COUNT(*) FROM ORDERS WHERE STATUS <> 1000
  1. On NCI Seek, when I hover my mouse on it, I can see "Actual Partition Count" as 2. So why it is not doing partition elimination?
  2. On same tool tip, when i checked estimated / actual number of rows read, it is actually the count of 1 partition. So it looks like partition elimination happened?
  3. <> is a Non-Sargable Operator, so how can a SEEK happen on a NCI?
  4. Can a Non-Sargable Operator do a Partition Elimination?

Any help would be appreciated.

1

Yes, but...

Your query is asking a bad question. As-is, the optimizer has no idea if the value 1001 exists in the table. The predicate <> 1000 does not mean "everything under 1000", unless you give it more information.

Setup

CREATE PARTITION FUNCTION 
    a_func(int) 
    AS RANGE RIGHT FOR VALUES (1000);
GO
CREATE PARTITION 
    SCHEME a_scheme 
    AS PARTITION a_func ALL TO ([PRIMARY]);
GO

CREATE TABLE dbo.a_table
(
    id int PRIMARY KEY
) ON a_scheme(id);
GO 

INSERT dbo.a_table(id)
    VALUES(999), (1000);
GO 

Fix One: A Constraint

A check constraint can help, but...

ALTER TABLE dbo.a_table 
    ADD CONSTRAINT ck_a_ck CHECK(id <= 1000);

You'll need to beware of getting a trivial plan/simple parameterization. If your literal value is substituted with a parameter via simple parameterization, you'll need to add something to your query to avoid that happening, like this:

SELECT 
    a.*,
    $PARTITION.a_func(a.id) AS pid
FROM a_table AS a
WHERE a.id <> 1000
AND   1 = (SELECT 1);

NUTS

Fix Two: Hacking The Function

If you feel like directing the optimizer manually to a partition, you can query the table like this to avoid hitting unnecessary partitions:

SELECT 
    a.*
FROM a_table AS a
CROSS APPLY
(   
    VALUES($PARTITION.a_func(a.id))
) AS c (pid)
WHERE c.pid = 1;

NUTS

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.