SQLCat blog on Partition Level Locking in SQL Server 2008

Interesting blog by the SQL Customer Advisory Team

This week’s MSDN Blogs category found me on the SQLCat site reading about enabling partition-level locking in SQL Server 2008.  I’ve never heard of this “hidden” feature, so it sounded cool. Essentially what they did was demonstrate a method to override the locking of a table when performing DML on a majority of the records in a partition; experimenting with this feature could allow additional concurrency when loading a large table because affecting many rows in a partition will escalate exclusive locks only to that partition.  Pretty slick.  The they demonstrated an instance where SERIALIZABLE on a heap causes the serializable lock to override (or supress) the partition locking feature.

Check this out at http://blogs.msdn.com/sqlcat/archive/2010/03/04/enabling-partition-level-locking-in-sql-server-2008.aspx and experiment with it.  I know that partitioning is popular – seems that every time that I turn around someone wants to partition this or that.  A good rule of thumb is go test thoroughly this type of database architecture before simply reading a magazine article and throwing it on all of your data warehouse tables.  I need to do some tests on this…just haven’t gotten around to it.

Lee

----------------------

“Hold up! Hey, who's been putting out their Kools on my floor? Who has been putting out their Kools on my floor?”

References: Enabling Partition Level Locking in SQL Server 2008. Retrieved 3/4/10 at http://blogs.msdn.com/sqlcat/archive/2010/03/04/enabling-partition-level-locking-in-sql-server-2008.aspx.