Does SQL Server check disk space before allocating a new page?

Question

I have a bet with my old boss. I bet her that SQL Server, when allocating a new extent, always allocates from the buffer pool and never checks to see whether there is some place on disk where the allocation could be stored. Essentially, she contests that SQL Server should check for available space on the LUN before allocating a page. This seems wrong, since I could place my storage on the moon, which would cause some serious latency. I feel that she really wants SQL Server always to bring in a page from disk firstly and then perform the DML task(s).

Here is my "proof" that she is wrong. If you disagree with my "proof", then please most definitely respond with a better one!

Let us create a trivial database and table. The database's recovery model will be set to SIMPLE and AUTO_CREATE_STATISTICS will be shut off, in order to minimize log record bloat.

Before we begin, let me divulge the version of SQL Server that I am using.

SELECT @@VERSION;
-------------------------------------------------------------------------------------
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

Now, the code...

USE master;
GO

IF DATABASEPROPERTYEX(N'PageAllocDemo' , N'Version') > 0 
      BEGIN
            ALTER DATABASE PageAllocDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            DROP DATABASE PageAllocDemo;
      END;
GO

CREATE DATABASE PageAllocDemo
GO

USE PageAllocDemo;
GO
SET NOCOUNT ON;
GO

-- Set the database to SIMPLE and turn off log generating crapola
ALTER DATABASE PageAllocDemo SET RECOVERY SIMPLE;
GO
ALTER DATABASE PageAllocDemo SET AUTO_CREATE_STATISTICS OFF;
GO

CREATE TABLE dbo.X
(
      c1 INT IDENTITY (1,1)
) ON [PRIMARY];
GO

Now, let us check how many pages were allocated? I suspect zero, since we have only created a "logical table", in our case an empty heap.

-- How many pages are allocated to our table?
DBCC IND (PageAllocDemo,X,-1);
GO

Now, clear the log.

-- Clear the log
CHECKPOINT;
GO

What is currently in the log?

-- What is in the log right now?
SELECT * FROM fn_dblog(NULL,NULL);
GO

/*

---------------------------------------
-- Operation -------------- Context ---
---------------------------------------
LOP_BEGIN_CKPT      LCX_NULL
LOP_XACT_CKPT       LCX_BOOT_PAGE_CKPT
LOP_END_CKPT        LCX_NULL

*/

This is expected, since we are in the SIMPLE recovery model. We will now create an explicit transaction that will insert one and only one record into our table;but, before we do that, let us open Process Monitor and filter for our MDF and LDF file as well as the PID for the SQL Server process.

Begin the transaction:

BEGIN TRAN

    INSERT INTO dbo.X DEFAULT VALUES;
    GO

Process Monitor shows two writes to the transaction log file.

Let us check the log records.

-- What is in the log right now?
    SELECT * FROM fn_dblog(NULL,NULL);

    /*
    I omitted all the log records for PFS, GAM, SGAM, etc.
    ---------------------------------------------------------------
    -- Operation -------------- Context ------- Transaction ID  ---
    ---------------------------------------------------------------
    LOP_BEGIN_XACT      LCX_NULL                0000:0000030e
    LOP_BEGIN_XACT      LCX_NULL                0000:0000030f
    LOP_FORMAT_PAGE     LCX_HEAP                0000:0000030f
    LOP_COMMIT_XACT     LCX_NULL                0000:0000030f
    LOP_INSERT_ROWS     LCX_HEAP                0000:0000030e
    LOP_COMMIT_XACT     LCX_NULL                0000:0000030e

*/

I omit the bit map and PFS allocations and we can see that a page is allocated and one row is inserted as one would anticipate.

How many pages are allocated to our heap?

-- How many pages are allocated to our table?
    DBCC IND (PageAllocDemo,X,-1);
    GO

    /*

    One IAM page and one data page and nothing else
    ---------------------------------
    PageFID PagePID     IAMFID IAMPID      
    ------- ----------- ------ ------ 
    1       264         NULL   NULL        
    1       231         1      264         

    */

This is as anticipated. We have one IAM page and one data page. Now, our penultimate action is committing the transaction. I expect a 512B log block flush to occur at this point.

COMMIT TRAN;

Let us finish the "proof" with a checkpoint operation. So far, nothing has been committed to the data file only to the log file.

CHECKPOINT;
GO

Cool, the data pages flushed to disk as expected.

My conclusion, from the evidence from Process Monitor is that SQL Server allocates in-memory, adds the record in memory, and commits the page to disk all without checking anything at the storage level.

Does anyone object to this hypothesis? If yes, why?

Answer 1

There's no need to check for disk space when allocating a new extent to an object. SQL Server already owns that space on the disk. It knows which pages within it's data file are allocated and which aren't, so there's no need to verify that we own the pages within the extent as we know for a fact that we already do. It will simply allocate space in the buffer pool and write the data into memory, then overwrite whatever is in that space on the disk when checkpoint occurs.

The only time that SQL Server will bother to see how much free space is on the disk is when it's actually expanding the data file.