When are partitioned indexes slower than regular indexes?

Question

When are partitioned indexes slower than regular indexes?

winston Smith

SSCoach

Points: 19500
More actions
November 23, 2011 at 2:07 pm

#251007

I had someone tell me earlier on that on a partitioned table, for certain queries a non partitioned index will be more performant than a partitioned index.
I keep all my indexes partitioned so that i can easily swap in/out partitions.
I have tried to repro what this person said but i cannot. Is there any truth in this and if so, how could i replicate it?

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

winston Smith (11/23/2011)
...for certain queries ...

Ask the person that told you to produce the proof. All claims of performance or lack thereof should be accompanied by a code example. Otherwise, it's nothing more than an unsubstantiated opion on their part. 😉

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Ask the person who told you that for a query that shows the problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

winston Smith SSCoach Points: 19500 More actions · Answer 3

Managed to repro this issue, based on some research. not sure if it is the same issue as my collegue experienced but i get the symptoms discussed.

Your query needs to be doing a min, max or top N. that way the partitioned index has to scan all partitions, whereas a nonpartitioned index does not, it just goes through its btree as per usual.

There's a connect item for this here. just adding to close thread off:

ttp://connect.microsoft.com/SQLServer/feedback/details/240968/partition-table-using-min-max-functions-and-top-n-index-selection-and-performance