6

I am trying to learn Table Partitioning. However, I struggling to understand why SQL Server behaving stage for a simple query.

Test dataset

   /* --------------------------------------------------
-- Create helper function GetNums by Itzik Ben-Gan
-- http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
-- GetNums is used to insert test data
-------------------------------------------------- */

-- Drop helper function if it already exists
IF OBJECT_ID('GetNums') IS NOT NULL
    DROP FUNCTION GetNums;
GO

-- Create helper function
CREATE FUNCTION GetNums(@n AS BIGINT) RETURNS TABLE AS RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
  SELECT TOP (@n) n FROM Nums ORDER BY n;
GO

/* ------------------------------------------------------------
-- Create example Partitioned Table (Heap)
-- The Partition Column is a DATE column
-- The Partition Function is RANGE RIGHT
-- The Partition Scheme maps all partitions to [PRIMARY]
------------------------------------------------------------ */

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'Sales')
    DROP TABLE Sales;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
    DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
    DROP PARTITION FUNCTION pfSales;

-- Create the Partition Function 
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE right FOR VALUES 
('2013-01-01', '2014-01-01', '2015-01-01');

-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales 
ALL TO ([Primary]);

-- Create the Partitioned Table (Heap) on the Partition Scheme
CREATE TABLE Sales 
(
    SalesDate DATE constraint ck_date check(SalesDate<'2016-01-01'),
    Quantity INT
) ON psSales(SalesDate);

-- Insert test data
INSERT INTO Sales(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

Qery

select count(1) from sales where SalesDate>'20160101'

Please see the execution plan below.

Execution Plan

Questions

  1. Why SQL doing CONVERT_IMPLICIT
  2. Why SQL is not doing the contradiction detection to avoid the table scan.

Many thanks

4

Why SQL doing CONVERT_IMPLICIT

Because you have a literal in the query that needs to be parsed and converted to a date. This is not a performance concern.

SQL is not doing the contradiction detection to avoid the table scan.

With no indexes, a table scan is inevitable. However, as you can see from the table scan operator properties, a seek is used so only the partition containing the specified SalesDate value is scanned. Run the query with STATISTICS IO ON with and without the WHERE clause to see the impact of partition elimination:

SELECT COUNT(1) FROM sales WHERE SalesDate>'20160101';
Table 'Sales'. Scan count 1, logical reads 722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT COUNT(1) FROM sales;
Table 'Sales'. Scan count 4, logical reads 2890, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The partition aware seek operation was introduced in SQL Server 2008. Previously (SQL 2005), partition elimination was accomplished with separate execution plan operators.

EDIT I just realized the main question was mostly about the check constraint than partition boundaries. I think this answer still provides some value so I won't delete it.

  • That's still 722 reads. Compared with 0 when AND 1=1 is added. – Martin Smith Aug 30 '17 at 14:37
  • 1
    @MartinSmith, yes, I didn't consider the check constraint when I first posted my answer. 722 is still better than 2890 :-) – Dan Guzman Aug 30 '17 at 14:45
9

You're getting a trivial and simple parameterized plan for your simple query.

Add AND 1 = (SELECT 1); to the end of your query, and you'll get the constant scan/contradiction detection.

enter image description here

Using just AND 1 = 1 doesn't always work if the aim is to also avoid trivial plans. See Query Plans: Trivial Optimization vs Simple Parameterization by Erik Darling.

You should also get into the habit of being careful about data types. A string is not a date, and there is no special T-SQL syntax to specify a date literal. You can use CONVERT to be explicit, e.g. SalesDate > CONVERT(date, '20160101', 112).

Related reading: Why Doesn’t Partition Elimination Work? by Paul White.

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.