SQLskills SQL101: Why does my heap have a bunch of empty pages?

SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

Here’s a question I’m often asked (paraphrasing):

I’ve got a large heap table where the space isn’t being given up when I delete a large number of records, but then when I shrink the database the heap is reduced in size. Can you explain?

This behavior is how SQL Server works, but it’s pretty non-intuitive. When a page in an index becomes empty, it’s always deallocated, as an empty page isn’t allowed in a SQL Server index structure from SQL Server 2005 onward. However, the structure of a heap is different and as a result, the behavior is too.

Whenever a row is deleted in a heap, it’s usually the case that the page containing the row does not become empty. However, if the heap page that the row is stored on becomes empty as a result of the delete, the page cannot be deallocated from the table unless an exclusive (X) table lock is held (to remove the page from the internal free-space “tracking” mechanism that is in place for heaps). It’s not common that a table level X lock is held unless lock escalation has occurred (because you’re deleting enough rows to trigger escalation from individual row X locks to a single table X lock), or if you specifically use the TABLOCK hint on the delete statement, for instance. But, because both of these circumstances are unlikely, the empty heap page usually cannot be deallocated.

There is a Knowledge Base article that describes this phenomenon: KB 913399. However, the KB article only references up to and including SQL Server 2005 but this behavior exists in all version and is very easy to reproduce if you want to prove it to yourself. Also, if you’re using one of the flavors of snapshot isolation then even using TABLOCK won’t allow the pages to be deallocated.

The script below will create a database, create a table with one row per heap page, show you how many pages and rows, and how full each page is. Next it’ll delete all the rows, and show you the pages are still there, with no rows, and empty. Give it a try!

CREATE DATABASE [EmptyHeapTest];
GO
USE [EmptyHeapTest];
GO

CREATE TABLE [test] ([c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'a');
GO

SET NOCOUNT ON;
GO
-- Insert 1000 rows, which will allocate 1000 pages
INSERT INTO [test] DEFAULT VALUES;
GO 1000

-- How many pages and how full?
SELECT [page_count], [record_count], [avg_page_space_used_in_percent]
FROM sys.dm_db_index_physical_stats (DB_ID (N'EmptyHeapTest'), OBJECT_ID (N'test'), 0, 	DEFAULT, 'DETAILED');
GO
page_count           record_count         avg_page_space_used_in_percent
-------------------- -------------------- ------------------------------
1000                 1000                 98.974549048678
-- Delete all the rows
DELETE FROM [test];
GO

-- Did all the pages get deleted?
SELECT [page_count], [record_count], [avg_page_space_used_in_percent]
FROM sys.dm_db_index_physical_stats (DB_ID (N'EmptyHeapTest'), OBJECT_ID (N'test'), 0, 	DEFAULT, 'DETAILED');
GO 
page_count           record_count         avg_page_space_used_in_percent
-------------------- -------------------- ------------------------------
1000                 0                    0

The empty pages will be reused by subsequent inserts (into the same table, of course), but if the space isn’t going to be reused following a large delete in a heap, you might consider using the TABLOCK hint to allow the empty pages to be deallocated and the space made available for other objects in the database to use.

Another alternative is to just use a clustered index instead, or if a heap is necessary, you could rebuild the heap using ALTER TABLE … REBUILD (that was added in SQL Server 2008 to support enabling compression on a heap), with the caveat that this will cause all the table’s nonclustered indexes to be rebuilt.

On the extreme end (in my opinion), you could reclaim the empty heap space using a shrink operation. Shrink won’t free up space inside pages as it moves them (with the exception of compacting LOB pages as it goes – somewhat unsuccessfully depending on which version and build you’re on – see KB 2967240), but it will remove empty pages rather than moving them. This will effectively shrink the heap after a large delete, but with the usual caveats about shrink causing index fragmentation and generally being an expensive, slow operation to perform.

So now you know why you may have a bunch of empty pages in your heaps!

SQLskills SQL101: How can corruptions disappear?

As Kimberly blogged about earlier this year, SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

Every so often I get an email question from a confused DBA: why is it that sometimes corruptions seem to disappear?

The situation is commonly as follows:

  • There is a regular SQL Agent job that runs DBCC CHECKDB
  • One morning the DBA finds that the job failed, reporting corruptions in one of the databases
  • The DBA runs DBCC CHECKDB on that database again, but this time there are no reported corruptions

This can lead the DBA to mistrust DBCC CHECKDB. Remember the SQL Server 2000 days where sometimes DBCC CHECKDB occasionally reported corruptions when there weren’t any? Those days are long gone now: if DBCC CHECKDB reports corruption, then at that time that it ran there was definitely corruption.

Think about what DBCC CHECKDB is doing: it reads and processes all the allocated pages in the database – all the pages that are part of tables and indexes at the time that DBCC CHECKDB runs. It doesn’t check all the pages in the data files; only those that are currently being used. The pages that are not currently allocated to an object cannot be checked as there’s no “page history” maintained. There’s really no way for DBCC CHECKDB to tell if they have ever been used before or not and since they’re not currently allocated there’s no valid page structure on them and no past to verify.

And if your database is still being accessed then the set of allocated pages can change after DBCC CHECKDB runs. A simple example of this occurring is:

  • Nonclustered index X of table Y has some corrupt pages in, which the DBCC CHECKDB (being run by a SQL Agent job) reports
  • Another SQL Agent job runs and performs index maintenance where it rebuilds index X (the rebuild operation always builds a new index and then drops the old index)
  • The DBA runs DBCC CHECKDB manually and there are no corruptions reported in the new index structure

Nonclustered index corruption is the best kind of corruption to have. The rebuild operation rewrote the index to a new set of pages and deallocated the pages that had corruption. When DBCC CHECKDB is run manually, those new pages are not corrupt and the old pages are not checked, as they are no longer in use.

These kind of ‘disappearing’ corruptions are a problem because it’s almost impossible to investigate them further. However, they could indicate a problem with your I/O subsystem. If you find that they’re occurring repeatedly, consider briefly preventing the process that causes the corrupt pages to be deallocated so you can investigate the corruption.

Another cause of disappearing corruptions can be transient I/O subsystem problems, where page reads sometimes fail outright and then succeed after that. Take a look at these blog posts on read-retry and Agent alerts for more information.

And yet one more cause could be that the database is mirrored or is part of an availability group and the page was fixed by automatic page repair before the second DBCC CHECKDB occurred. You can look in the msdb.dbo.suspect_pages table (more details here) for an entry for the broken page(s) with event_type of 4.

Bottom line: From SQL Server 2005 onward, if DBCC CHECKDB reports corruption, then at the time that it ran there definitely was corruption. Make sure you don’t just ignore the problem as next time the corruption occurs, you may not be so ‘lucky’ that it just seemed to disappear.

SQLskills SQL101: Should you kill that long-running transaction?

As Kimberly blogged about earlier this year, SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

One of the problems you may have encountered is a transaction log file growing because of a long-running query.

What do you do?

The temptation is to kill the query, but is that the correct approach? Well, as always, the answer starts with ‘it depends’.

A long-running query, no matter how much work it’s done (and hence how many log records it has generated) will prevent the log from clearing, as the log all the way back to the LOP_BEGIN_XACT log record of the long-running transaction is required, in case that transaction rolls back. And the log will not be able to clear until (at least) that long-running transaction has committed or finished rolling-back.

You can tell how many log records a transaction has generated using my script here, along with the total space taken up in the log by the transaction.

If the long-running query has generated hardly any log records, then killing it will mean that it rolls-back quickly and then hopefully the next log backup (in the full and bulk-logged recovery models) or checkpoint (in the simple recovery model) will allow the log to clear and stop its growth.

However, if the long-running query has generated a *lot* of log records, then it’s going to take a long time to roll back (as rolling back each log record means generating the ‘anti-operation’ for that log record, making the change, and generating *another* log record describing the change). That rollback itself won’t cause the log to grow any more, as a transaction always reserves free space in the log to allow it to roll back without requiring log growth (my script above also tells you that amount of space). However, as it will take a long time to roll back, other activity in the log from other transactions will likely cause the log to grow more until it’s finally able to clear.

So it may actually be better to allow a long-running transaction that’s generated a lot of log records to continue running until it completes. If the time to completion is going to be a lot less than the time to roll back, this could mean less overall extra log growth until you’re able to finally clear the log, and then potentially resize it back to normal, and continue running.

The trick is knowing what the query is doing and/or being able to figure out how close to completion it is. You could look at is the logical_reads column in the DMV sys.dm_exec_requests and correlate that with the number of pages in the index or table being scanned, or look at the number of log records generated in the script output and correlate that to the number of records you’d expect an UPDATE statement to perform.

Bottom line: don’t always knee-jerk and decide to cancel a problematic, long-running query without thinking about what the effect of that will be.