Query slower after partitioning

Question

Query slower after partitioning

Green Armchair

Hall of Fame

Points: 3561
More actions
January 9, 2007 at 6:44 am

#115649

Hi
I have partitioned a very large table by creating the partition function and scheme. I then rebuilt the clustered index for that table into the partition scheme by adding a primary key constraint (having dropped the original PK).
However, the query I am using to test the performance is now taking 4 times longer (6 minutes to 24 minutes).
Anybody have any ideas why this should be so? Have I done something wrong when creating the partitioned table?
Any help appreciated
Regards
Andy

Viewing 13 posts - 1 through 12 (of 12 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

We can't answer this type of question without having the tables ddl (with indexes and keys) along with the query and execution plan. ONce you provide that we'll most likely have enough info to get you on your way.

Green Armchair Hall of Fame Points: 3561 More actions · Answer 2

Green Armchair

Hall of Fame

Points: 3561

January 9, 2007 at 7:15 am

#682009

what's the best way to provide this info?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You got a little bit of reading ahead but it's really worth it, for everyone involved.

http://www.aspfaq.com/etiquette.asp?id=5006

http://www.sqlservercentral.com/columnists/siyer/2645.asp

Green Armchair Hall of Fame Points: 3561 More actions · Answer 4

I'm happy to provide this info, but my first query was really about partitioning, and if there are any known issues i.e. "some queries can run slower after partitioning unless you do xyz"

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Sorry but I don't know the answer to that one... I guess we'll know at the same time.

But if I were you I'd keep reading those articles... they'll come in handy some day for sure.

Green Armchair Hall of Fame Points: 3561 More actions · Answer 6

This is a stored procedure with 2 parameters. If I run the SQL with the parameters hard coded (i.e. not as a stored procedure call), I get a different (better) execution plan and I'm back to 6 minutes.

Next step is to work out why the different execution plan.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Dev server??

DBCC FREEPROCCACHE

Now you can search these forums for parameter sniffing to get more info on that topic.

noeld SSC Guru Points: 96590 More actions · Answer 8

Database Partitioning is *not* a primary performance feature. It is mostly a maintainablility one.

I have "several" instances in which that was the case. On SP2, M$ is saying that they have improved the optimizer to make better use of the partitioning feature. It is key that you check that only the partitions needed by your most heavy queries are actually accessed; the table on-disk configuration is as parallel as possible, etc.

This feature is very usefull for datawarehouse loading scenarios but It hasn't prove me yet what it could represent a performance booster.

-Regards

* Noel

Green Armchair Hall of Fame Points: 3561 More actions · Answer 9

Thanks Noel

I'm looking at partitioning to help maintain some very big tables, (i.e. rebuild indexes at partition level rather than table level). As long as queries perform the same with partitioned tables then that would be acceptable. Not sure that they are at the moment!

Ninja, DBCC FREEPROCCACHE helped. Thanks for that. The execution plan changed and the query came down to 9 minutes. Still not as good as without partitioning though. I will continue to investigate.

Regards

Andy