Issues with partitioning...

Question

Post reply

Issues with partitioning...

T.Ashish

SSCarpal Tunnel

Points: 4248
More actions
August 18, 2013 at 9:47 pm

#281187

Hi All,
I was trying to use partitioned index for a slow query but I was stopped with following response from my manager :
"There are few cases where partitioning seems logical and looks promising but then we found that with right set of indexes and tweaks, performance was equal or even better as any partition scheme would otherwise offer.
I think we had external SQL consultants who were of same opinion we well."
If you please share your experience with partitioning, it would be a great help.
Regards

Viewing 10 posts - 1 through 9 (of 9 total)

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

Nick Doyle SSC Eights! Points: 926 More actions · Answer 1

This depends on the query and your data. Can you provide more details?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

T.Ashish (8/18/2013)
Hi All,
I was trying to use partitioned index for a slow query but I was stopped with following response from my manager :
"There are few cases where partitioning seems logical and looks promising but then we found that with right set of indexes and tweaks, performance was equal or even better as any partition scheme would otherwise offer.
I think we had external SQL consultants who were of same opinion we well."
If you please share your experience with partitioning, it would be a great help.
Regards

Your boss is absolutely correct. You should NOT be looking to partitioning to primarily speed up a slow query. That is NOT what it was built for. It CAN provide incredible performance gains - but you can also REALLY mess things up and cause pain for yourself, especially if you are not experienced with partitioning in the first place and don't get professional help with it.

TUNE FIRST (and there are a TON of things to be evaluated/refactored/done when tuning), and only THEN should you look to alternatives to get additional performance gains if needed.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

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

https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

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

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 4

T.Ashish

SSCarpal Tunnel

Points: 4248

August 19, 2013 at 8:51 am

#1642734

Kevin, Gail

Thanks a lot.

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 5

One more Issue:

Is it going to change in next version?

chvenkataraman Valued Member Points: 72 More actions · Answer 6

It really depends on the access pattern of your data through queries. For generic queries, it is always better to check performance improvement with indexes.

IMO Go for partition if

1. The query access pattern always needs a where condition on partition columns. Remember post partition, to get better performance the query always should use partition column else it might result in table scan

2. The data you are handling is very high. The partition helps in purging / archival of data from certain partitions using ALTER SWITCH PARTITION (DBA perspective)

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

T.Ashish (8/19/2013)
One more Issue:
Is it going to change in next version?

Partitioning is not primarily for performance. It's for management, data loads, easy archiving. That it doesn't magically improve performance is not a bug to be fixed.

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

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 8

Gail,

I understood that. And I have read the article you mentioned, its really nice.

Actually I meant this feature of SQL Server 2014

a. Online Indexing at partition level

b. Statistics at the partition level

I found it on some blog.

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 9

T.Ashish (8/20/2013)
Gail,
I understood that. And I have read the article you mentioned, its really nice.
Actually I meant this feature of SQL Server 2014
a. Online Indexing at partition level
b. Statistics at the partition level
I found it on some blog.

I've not personally checked but Paul White (whom I have quite a bit of trust in), says that online indexing is in 2014 CTP1. Unfortunately, I've not heard of a retrofit to any other versions.

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