Table Partition performance Tips part 8

Index aligned vs non aligned

To have an Aligned index it should have same FG as partition resides and index structure should be match or the index column and partition key column of source table should match to switch in and out.

In our switch example, I have created non aligned index and switch was failed with the following error.

Msg 7733, Level 16, State 4, Line 147

‘ALTER TABLE SWITCH’ statement failed. The table ‘Test_Tbl_Partition.dbo.tbl_partition_test’ is partitioned while index ‘Ix_tbl_partition_test’ is not partitioned.

 

Partition Elimination

Data partition elimination refers to the query optimizer is ability to determine, based on query predicates, that only a subset of the data partitions in a table need to be accessed to get the data.

I am trying to get data from 6^th partition from date and for that date the ID is 1. But the first query is only scanning the partition 6. The second query is all partitions. The table has only 6 rows, this will be problem when our table is larger and having many partitions. That is the reason table partition will not improve the select query performance, it may even degrade the permanence. This is we can create simple non clustered index (Non aligned) to the ID column will make query faster.

Notes:

Even though if my query is getting partition elimination if I have lot of partition ex:more than 5000 partition. Still, non aligned non clustered index will be small to seek or scan than scanning all 5000+ partitions.  Example: If our query has MIN, MAX & TOP with order by it mostly has to can all partitions.

use Test_Tbl_Partition
go


------------=============------------------
--Performance Tips

-- Run this query with actual plan and see the partition access count

--This will scan only 1  partition i.e partition no 6
select * from [tbl_partition_test] where Date_Col ='2024-01-06 00:00:00.0000000'
select top 1 (Date_Col) from [tbl_partition_test]


--This will scan all  partition
select * from [tbl_partition_test] where ID = 11


--min max top
--This will scan all  partition
select max(Date_Col) from [tbl_partition_test]
select min(Date_Col) from [tbl_partition_test]
select top 1 (Date_Col) from [tbl_partition_test] order by Date_Col

-- try using both : This will scan only 1  partition i.e partition no 6
select * from [tbl_partition_test] where ID = 11 and Date_Col ='2024-01-06 00:00:00.0000000'


-- Try to create non aligned index and run
 drop index [Ix_tbl_partition_test] ON [tbl_partition_test]
create index [Ix_tbl_partition_test] ON [tbl_partition_test] (ID) ON [Primary]

-- This is it won't go to scan all partition, it will scan the NCI which us very small
-- But the problem is we cannot switch usng non aligned index
select * from [tbl_partition_test] where ID = 11

-- try using both : This will scan only 1  partition i.e partition no 6
select * from [tbl_partition_test] where ID = 11 and Date_Col ='2024-01-06 00:00:00.0000000'



--Query using $Partition function

--https://simonlearningsqlserver.wordpress.com/2017/02/14/using-partition-to-find-the-last-time-an-event-occured/
SELECT  *FROM [tbl_partition_test] T (NOLOCK)


SELECT MAX(T.Date_Col)
FROM [tbl_partition_test] T (NOLOCK)
WHERE T.ID = 14

DECLARE @Counter INT = 6 -- this should be the maximum partition ID on our table
DECLARE @MaxDate DATETIME2(7);

WHILE @Counter > 0
BEGIN

  RAISERROR('%d', 0,1,@Counter) WITH NOWAIT;

  SELECT @MaxDate = MAX(T.Date_Col)
  FROM [tbl_partition_test] T
  WHERE T.ID =14
  AND $partition.PF_myDateRange(Date_Col) = @Counter

  IF @MaxDate IS NOT NULL
  BEGIN
    SELECT @MaxDate, @Counter
    BREAK;
  END

  SET @Counter -= 1;

END;


/*
--Remove table partition

-- Drop PK or CI

DROP INDEX Cx_tbl_partition_test on [tbl_partition_test] /* drop the clustered if already exists if not create nw CI on primary FG*/
ALTER TABLE [dbo].[tbl_partition_test] DROP CONSTRAINT [PK_C1810429_tbl_partition_test] WITH ( ONLINE = OFF )

/* Create another clustered index on the table to free it from the partitioning scheme; Here, the "ON [primary]"  or Dummy part is key to removing the partition scheme from the table ! */

CREATE CLUSTERED INDEX Cx_tbl_partition_test ON [tbl_partition_test] (ID,Date_Col) ON FG_Dummy 

DROP PARTITION SCHEME PS_myPartitionScheme

DROP PARTITION FUNCTION PF_myDateRange

/* drop the dummy clustered index you created, as it was only created to free the table from the partitioning scheme */

DROP INDEX Cx_tbl_partition_test ON [tbl_partition_test]

--Create the original PK
ALTER TABLE [dbo].[tbl_partition_test] ADD  CONSTRAINT [PK_C1810429_tbl_partition_test] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [Date_Col] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PS_myPartitionScheme]([Date_Col])

*/