HOT SPOTS

Hot spots occur when you have excessive contention for a page on a SQL

Server table. This happens most often when you have some sort of data

modification taking place at the end of the data page chain in the

table.

Assume the following scenario.

CREATE TABLE TableX (

IDKey INT IDENTITY NOT NULL,

ssn CHAR(9) NOT NULL,

lname VARCHAR(50) NOT NULL)

CREATE CLUSTERED INDEX cidx_1 ON TableX (IDKey)

This seems normal enough, but take a closer look at the data that will

reside within this table. As your Online Transaction Processing (OLTP)

application inserts new data into TableX, the data has a tendency to

move sequentially down the page chain due to the clustered indexing of

the IDKey column. SQL Server will attempt to use the remaining free

space on the data page before allocating to another page. If many

users/processes are attempting to insert new data simultaneously,

contention for the same data page will begin to develop. This is a hot

spot. When a hot spot develops, your transaction processing will

significantly degrade.

A way to avoid this problem is to cluster your index on a

nonincrementing value. A nonincrementing value is a data value that is

more randomly distributed. The ssn column would be a good candidate for

the clustered index, as opposed to the IDKey column, as the ssn data

would tend to be more evenly distributed and most likely would not

increase sequentially. By distributing the data more evenly throughout

the clustered index, you lessen the likelihood of hot spots within your

tables.