Partitioned index on partitioned table

Question

Partitioned index on partitioned table

N_Muller

SSCarpal Tunnel

Points: 4037
More actions
December 19, 2015 at 2:02 pm

#310283

I have a partitioned table with a partitioned index:
CREATE TABLE MyTable
(
PartitionID int not null,
ID int not null,
UpdateDT datetime2(2) not null,
Name varchar(20),
"Other Columns"...,
CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED
(
PartitionID,
ID,
UpdateDT
) ON PS_MyPartition ( PartitionID )
) ON PS_MyPartition ( PartitionID )
GO
CREATE INDEX IX_MyTable_Name ON MyTable
( PartitionID, Name ) ON PS_MyPartition ( PartitionID )
GO
I have a query with an inner join with MyTable on columns PartitionID and Name:
SELECT *
FROM MyTable2 T2 inner join MyTable T on
T.PartitionID = @PartitionID and
T.Name = T2.Name
The query sometimes performs poorly and the execution plan suggest an index:
CREATE INDEX [Name] ON MyTable ( Name ) INCLUDE ( PartitionID )
Why would SQL Server execution plan suggest such index, and why SQL Server failed to detect the partitioned index? It doesn't make any sense to me that the execution plan would suggest an unaligned non-partitioned index where the partitioned column is part of the include option, when a partitioned index exists. If I give the hint on the query to use index IX_MyTable_Name on MyTable, the performance is significantly better and the execution plan no longer suggest the index. I don't have an option to create unaligned indexes.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MadAdmin SSChampion Points: 11261 More actions · Answer 1

Have a look at the index column order of your nonclustered index. It is redundant since you have a table already sorted on partitionid.

Does your query need to do the almighty select STAR?

What does the structure of the other table look like?

We cant really give good help without the index structure of both table1 and table2.

What would also help greatly is if you could attach an execution plan.

The duplicate/redundant is making things better because from this angle it is probably doing an index scan, so if you create a narrower index, it may reduce the amount of pages needed to scan.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

If you (almost) always look up by PartitionID and Name, then the table should be clustered first on those columns. If you really want/need to, add other columns to make the clustering key unique.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 3

ScottPletcher (12/21/2015)
If you (almost) always look up by PartitionID and Name, then the table should be clustered first on those columns. If you really want/need to, add other columns to make the clustering key unique.

"It Depends". If the table is really wide, it's sometimes better to use a non-clustered index and a Row Lookup. I just went through this this last week when the optimizer selected the wrong index to scan because it was more narrow than the clustered index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)