Rebuilding Table Heaps in SQL Server

sqlserver2014

The basic idea, when talking about database performance, it a table scan can be the slowest way to get data when you query a table. There is also a way that a table scan on two “identical” tables can actual be shower for one table than in the other table. If one of the tables has extra forwarding pointers, you can get even worse query performance than you might expect.

This has to do with a technique that SQL Server uses when a row in a heap is increased in size so it no longer fits in the original page. This usually occurs when a variable length column is updated to take more space.  If SQL Server just moved the row to another page, any nonclustered indexes would have to be updated to indicate the new page address. This is done as a performance optimization so that all the nonclustered indexes on the heap do not have to be altered with the new location of the heap record. (Remember, if the underlying table is a heap, nonclustered indexes point to the data row using a actual address.) Since there can be up to 249 nonclustered indexes on a single table, that could potentially be a LOT of work. So instead, when a row in a heap has to move, SQL Server leaves behind a forwarding pointer in place of the row that has moved. The nonclustered indexes continue to point to the old location, and then SQL Server just needs one more page lookup to find the new location. For just a few lookups, this expense is minimal and more than made up for my the savings of not having to update all the nonclustered indexes every time a row moves.

If the table is a clustered index (remember that a table can be organized as a heap OR a clustered index, not both), the record locator is the set of cluster keys of the data record. Both of these record locators are guaranteed to be unique. For a heap record locator, the record lookup (commonly called a bookmark lookup) goes directly to the physical location of the record. For a clustered index record locator, the record lookup uses the cluster keys to navigate down through the clustered index to the leaf level.

The idea is that when SQL Server is running a query against a table with a lot of forwarding pointers, there will be a more complex process to find and read all the data as it will be scattered across more drive sectors, and additional drive I/O can decrease query performance.

So how do you get rid of forwarding pointers?

  • Let SQL Server do it – Not recommended because SQL Server won’t always remove the pointer and move the row page to the original page, even if the row has changed and the record can be moved back to the original page.
  • Shrinking the Data File – Forwarded records will be cleaned up when you shrink the data file, but this is obviously not recommended. SQL Server does so much moving of data and updating nonclustered index pointers when shrinking a data file that updating the forwarded records is not very much extra work at all. The total amount of work required to shrink the data file is a performance hit that most production servers are excluded from this process.
  • Build a Clustered Index – This will force all the forwarded records to go away. If you really don’t want the clustered index, you can then drop it.

You can also use the ALTER TABLE … REBUILD in SQL Server 2008 and up to help address this issue. You can read more about this command here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s