THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

SQLCAT Technical Note: Resolving PageLatch Contention on Highly Concurrent Insert Workloads Part 1

Its funny to see this new technical note posted by the SQLCAT team today because we just recently had a discussion about this on Twitter.  My good friend Sankar Reddy (Blog/Twitter) sent me an IM with the link to this page based on the twitter discussions. 

Essentially, the problem deals with inserts into a table with a identity column as the primary key and clustered index key.  Under a busy enough workload all inserts occur on the same page at the end of the B-Tree and the processes wait on the Page Latch to be released on the page.  In this technical note, the SQLCAT team covers how to deal with this scenario using Partitioning and hashing.

http://sqlcat.com/technicalnotes/archive/2009/09/22/resolving-pagelatch-contention-on-highly-concurrent-insert-workloads-part-1.aspx

Published Tuesday, September 22, 2009 10:29 PM by Jonathan Kehayias

Comments

 

Greg Linwood said:

The article states that they couldn't add a leading column to an index because it would break the application. This is strange because indexes have nothing to do with the logical model & can't possibly break applications unless they change constraints. Even stranger, the solution published goes on to actually change the logical design by adding an additional column! I found this confused the message they were trying to get accross..

Perhaps they meant to say "constraint" instead of "index" but even this would be irrelevant as you'd simply move the constraint to an identical (unique) index & design the cluster key as described with the non sequential leading key.

The real problem with changing the leading key of the clustered index is that it vastly increases the incidence of page splits vs the hash key solution published, as insertion into each quarter of an index (with a single partition) will obviously experience many more splits than at the end of each partition's allocation.

Last, the solution published could have been improved further by hiding the partitioned table under a view that doesn't include the new column to reduce the likelihood of breaking the application.

September 23, 2009 5:07 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement