February 24, 2012 at 11:36 pm
Hi All,
I want to partition a table having more than 30lac data. But my concern is how it will behave in case of concurrent insertion from different source. will the performance slow down for that. will any possibility of dead lock over that table?
please help.......
thanks and regards,
Debanjan
February 25, 2012 at 7:41 am
Depends whether the inserts are all into the same partition or different ones, depends on how many rows inserted at a time and a bunch more things. It's not that different from inserting into a non-partitioned table.
Deadlocks, no more likely than for inserts into a non-partitioned table, SQL 2005 does not have locking at the partition level.
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
February 26, 2012 at 1:39 am
If we did the partition then that will insert in different partitions.
My question is if we partition that table then both insertion can be simultaneous. Then table level lock will occur because of insertion. Will it affect the insertion process in other partition?
February 26, 2012 at 3:51 am
Just as with a non-partitioned table, there can be concurrent inserts depending on the locks taken. If the inserts take row locks, then other inserts can proceed without any problem. If for whatever reason the inserts take table locks, then that table lock will block any other inserts (a table lock is a full lock of the table). SQL 2005 does not have partition-level locks, SQL 2008 does.
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
February 27, 2012 at 6:45 am
We have to load data from different region wise sources. We have to load all the data in a common DW,which is common for all the regions. Since we have common tables for regions in DW, we cant load more than one regions data at a time because of table level lock and slow performance. Now we are looking for parallel loading of different regions data. Will Partitioning in DW table help in our scenario.
I am thinking of region wise partition.
I hope I am able to clarify my needs...
February 27, 2012 at 6:48 am
It might, but again it depends on whether SQL escalates the locks to table locks or not. SQL 2005 does not have partition-level locks, only row, page and table. If the locks escalate to table, that's a full table lock.
Test it out in a dev environment and see.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply