SQL Query: partition count varies based on variable or hard coded parameter

Question

I have a table which have partition. On that table we are creating a view which is using partition by. When we query view by passing date context from variables, it does not use partition context for query. Please help me sort this out.

Following is query for table creating, data populate and creating required indexes:

IF EXISTS(SELECT 1 FROM sys.indexes WHERE name='IX_TRAN_DATE' AND object_id = OBJECT_ID('TEST_TRANSACTION'))
BEGIN
PRINT 'Dropping Index IX_TRAN_DATE on TEST_TRANSACTION'

DROP INDEX IX_TRAN_DATE
    ON TEST_TRANSACTION;

END   
GO

IF EXISTS (SELECT 1 FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'TEST_TRANSACTION') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
AND NOT EXISTS(SELECT * FROM sys.indexes WHERE name='IX_TRAN_DATE' AND object_id = OBJECT_ID('TEST_TRANSACTION'))
BEGIN
PRINT 'Creating Index IX_TRAN_DATE on TEST_TRANSACTION with primary'

CREATE CLUSTERED INDEX [IX_TRAN_DATE] ON [dbo].[TEST_TRANSACTION]
(
    [TRAN_DATE]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

END   
GO

IF EXISTS(SELECT * FROM sys.indexes WHERE name='IX_TRAN_DATE' AND object_id = OBJECT_ID('TEST_TRANSACTION'))
BEGIN
PRINT 'Dropping Index IX_TRAN_DATE on TEST_TRANSACTION with primary'

DROP INDEX IX_TRAN_DATE
    ON TEST_TRANSACTION;

END   
GO

IF EXISTS (SELECT * FROM sys.partition_schemes WHERE type = 'PS' AND name = 'DATETIME_PS')
    BEGIN
        PRINT 'Dropping Partition Scheme DATETIME_PS'
        DROP PARTITION SCHEME DATETIME_PS
    END
GO

IF EXISTS (SELECT * FROM sys.partition_functions WHERE type = 'R' AND name = 'DATETIME_PF')
    BEGIN        
        PRINT 'Dropping Partition Function DATETIME_PF'
        DROP PARTITION FUNCTION DATETIME_PF
    END

GO

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'TEST_TRANSACTION') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
BEGIN
    DROP TABLE TEST_TRANSACTION
END
GO

PRINT 'Creating Partition Function DATETIME_PF'
GO

CREATE PARTITION FUNCTION DATETIME_PF (datetime)
AS RANGE RIGHT FOR VALUES 
(
    '01/01/2000',
    '01/01/2001',
    '01/01/2002',
    '01/01/2003',
    '01/01/2004',
    '01/01/2005',
    '01/01/2006',
    '01/01/2007',
    '01/01/2008',
    '01/01/2009',
    '01/01/2010',
    '01/01/2011',
    '01/01/2012',
    '01/01/2013',
    '01/01/2014',
    '01/01/2015',
    '01/01/2016',
    '01/01/2017',
    '01/01/2018',
    '01/01/2019',
    '01/01/2020',
    '01/01/2021',
    '01/01/2022',
    '01/01/2023',
    '01/01/2024',
    '01/01/2025'
);

PRINT 'Creating Partition Scheme DATETIME_PS'
GO

CREATE PARTITION SCHEME DATETIME_PS
AS PARTITION DATETIME_PF
ALL TO ([PRIMARY]);

GO

CREATE TABLE [dbo].[TEST_TRANSACTION](
    [TEST_TRAN_ID] [bigint] IDENTITY(100000, 1) NOT NULL,
    [TRAN_DATE] [datetime] NOT NULL,
    [CREATED_DATE] [datetime] NOT NULL,
    [CREATED_BY] nvarchar(64) NULL
)
GO

DECLARE @dateVar date 
SET @dateVar = '01/01/2013'

While (YEAR(@dateVar) < 2017)
BEGIN

    INSERT INTO [TEST_TRANSACTION] ([TRAN_DATE], [CREATED_DATE], [CREATED_BY]) 
    VALUES (@dateVar, GETDATE(), 'admin')

    SET @dateVar = DATEADD(DAY, 15, @dateVar)

END
GO

CREATE CLUSTERED INDEX [IX_TRAN_DATE] ON [dbo].[TEST_TRANSACTION]
(
    [TRAN_DATE]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [DATETIME_PS]([TRAN_DATE])

I create following view on TEST_TRANSACTION table:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'TICKET_SHIPPER_DISTRIBUTION')
    BEGIN
        PRINT 'Dropping View TICKET_SHIPPER_DISTRIBUTION'
        DROP View TICKET_SHIPPER_DISTRIBUTION
    END
GO

PRINT 'Creating View TICKET_SHIPPER_DISTRIBUTION'
GO

CREATE VIEW dbo.TICKET_SHIPPER_DISTRIBUTION
AS

SELECT
    bitr.[TRAN_DATE]
    ,ROW_NUMBER() OVER (
    PARTITION BY 
       bitr.[TRAN_DATE]
    ORDER BY
        bitr.TRAN_DATE,
        bitr.CREATED_DATE ASC
    ) AS ROW_NUM
FROM
    TEST_TRANSACTION bitr

Now, if we run following queries

-- Query 1 - will have partition count = 27

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

DECLARE @startDate datetime
DECLARE @endDate datetime
SET @startDate='2015-07-10 00:00:00'
SET @endDate = '2015-08-01 00:00:00'
Select * from TICKET_SHIPPER_DISTRIBUTION where TRAN_DATE >= @startDate and TRAN_DATE <= @endDate

execution plan snapshot for query 1

-- QUERY 2 will have partition count = 1

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Select * from TICKET_SHIPPER_DISTRIBUTION WHERE TRAN_DATE > '2015-07-10 00:00:00' AND TRAN_DATE < '2015-08-01 00:00:00'

execution plan snapshot for query 2

Please help me understand why there is difference in execution of query 1 and 2, and How can i make query 1 to follow partition and scan 1 partition only.

score 0 · Answer 1 · answered Oct 14 '15 at 17:04

0

I ran this through on a clean DB and it returns 1 row count for each query. I'm on SQL 2014 Developer edition.

Your < > are different in the queries above but it should make no difference to the result.

answered Oct 14 '15 at 17:04

Toby

83
1
5

Thanks for your response. Row count is 1 in both case but if we observe execution plan, then we see that partition count is 27 in case 1 and 1 in case 2 – Puneet Jain Oct 15 '15 at 05:36

score 0 · Answer 2 · answered Oct 15 '15 at 09:23

I have figured this out. Actually it has nothing to do with partition of table or partition by. It was actually about, how we are passing date parameters.

Referring to 3 options suggested at http://www.sqlservercentral.com/Forums/Topic547887-149-1.aspx, option 2 of dynamic query helped me out.

I am still working to make it work in a single stored procedure without dynamic query. Any suggestion will be really appreciated.

score 0 · Answer 3 · answered Oct 15 '15 at 15:44

Ok, I see the issue.

There is a clustered index on the table but when you do a select * from the view it's turning the query to a Clustered Index Scan which is why it hits every partition.

If you issue the query as follows you will get a Clustered Index Seek and only a single partition hit because you are specifying the only field in the Clustered Index.

SELECT  TRAN_DATE
FROM    TICKET_SHIPPER_DISTRIBUTION
WHERE   TRAN_DATE >= @startDate AND
        TRAN_DATE <= @endDate;

or without the view

SELECT  bitr.TRAN_DATE
       ,ROW_NUMBER() OVER ( PARTITION BY bitr.TRAN_DATE ORDER BY bitr.TRAN_DATE, bitr.CREATED_DATE ASC ) AS ROW_NUM
FROM    TEST_TRANSACTION bitr
WHERE   TRAN_DATE > @startDate AND
        TRAN_DATE < @endDate;

You just need to watch out for how you design and use the clustered / covering indexes and you should be OK and avoid the need for dynamic SQL.

Here are some really good articles on index design and use http://www.sqlskills.com/blogs/kimberly/category/indexes/

SQL Query: partition count varies based on variable or hard coded parameter

3 Answers3