May 4, 2007 at 7:01 am
We are running SQL Server 2005 in a filtered merge publication scenario with .Net Pocket PC 2003 handhelds in the field. Our current partition scheme sets each handheld as its own partition. We hit a wall at around 90 handhelds due the amount of data required to keep all the partitions fed. Lots of blocking and deadlocks when performing a sync and running stored procs to insert data. 10 minute sync wait times were not uncommon. We recognize a design flaw with the partition scheme and are redesigning. My question is: Is there a rule-of-thumb for how many partitions can be supported? Even with a redesign I can see where we will have 300+ in the next few months.
Thanks - Duane
May 8, 2007 at 6:31 am
I looked at your post and thought - no idea offhand - then thought that possibly you might be taking the wrong approach with your use of partitions - normally partitioning is used to age out inactive data or just split static data ( e.g. a BI solution ) I use it , amongst others, for handling logging data which a day is assigned a partition, but the data only gets inserted ( normally ) into one partition, I'd say that the mechanics of the process under the hood which has to seperate the partition to insert into would actually make the process inefficient.
All I'd say is make sure your inserts are indexed correctly, I know most people don't look to this and technically your partitioning columns should handle this for you - but just a few thoughts - have you analysed the query plans?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 8, 2007 at 7:17 am
The hard limit of partition number is 999.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply