Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

enter image description here

Begin the transaction:

BEGIN TRAN

    INSERT INTO dbo.X DEFAULT VALUES;
    GO

Process Monitor shows two writes to the transaction log file. enter image description here

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;

enter image description here

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

enter image description here

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?

share|improve this question
2  
Allocating a page and committing a page's contents to a data file are two separate things. Which do you care about? –  Jon Seigel Jun 14 '13 at 22:24
5  
Why would it need to check disc space? It can't allocate a page except in the file and the disc space used by the file is already reserved. If the allocation caused the file to auto grow it would need to check. –  Martin Smith Jun 14 '13 at 22:54
    
Agree with @MartinSmith, and also I think that if auto-grow is disabled for the file it won't even check on that case –  user16484 Jun 14 '13 at 23:06
1  
Folks, the point is this: can you prove without a doubt that SQL Server does not check for available disk space when it allocates an extent. So, one must look to determine whether any IO is actually used to communicate to the logical or physical disk. That is the question here. –  ooutwire Jun 15 '13 at 1:09
    
@JonSeigel I agree with you concerning a commit. When the commit occurs, the log record is flushed in the log block which has a 512B minimum size. At the checkpoint, the pages and some log records are flushed. Does this satisfy an interlocutor posing the question of whether SQL Server queries the disk subsystem prior to extent allocation...well, that is the question I am trying to resolve conclusively. –  ooutwire Jun 15 '13 at 1:11

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
I do not disagree with this argument. My follow up question would be this: can I then not disconnect my NICs and, assuming no log blocks flush or dirty pages are written to disk, assume that SQL Server should work just fine? Secondly, it seems to me that SQL Server can never guarantee that it "owns the space on disk" since I could just blow all my disks apart and SQL Server's writes will fail. I would rather say that SQL Server successfully allocates space on disk, does all its biz in the buffer pool irrespective of the disks existence, and then flushes IO to disk and hopes all goes well. –  ooutwire Jun 15 '13 at 2:58
1  
A prima facie argument in favor would be to show the file expansion request in Process Monitor and further demonstrate that no such query to the disk occurs otherwise. –  ooutwire Jun 15 '13 at 3:03
1  
Yes, if you were to query a table and load it into the buffer pool then disconnect the server from it's disks everything will continue to run without issue so long as you are only running select statements. If the log disk is connected insert, update and delete statements will run fine, until checkpoint happens. Then the database will go suspect. The SQL Server assumes that the data files exist until checkpoint happens as it doesn't actually need the data files until then. –  mrdenny Jun 15 '13 at 6:13
    
What mrdenny said, except replacing "checkpoint" with "commit". The first transaction that tried to commit would fail if the disks weren't available. –  Greenstone Walker Jun 18 '13 at 4:27
    
@GreenstoneWalker This is obvious. Log blocks commit when the log records associated with transactions residing in them are committed/aborted, the 60K log block size is reached, or when a checkpoint (or even from lazy writer) occurs. The whole point of the question is to determine firmly that SQL Server as an RDBMS does not constantly check to disk for allocated space. This may seem trivial but it is not necessarily so, and I bet most people here have never thought about it for more than a passing second. –  ooutwire Jun 20 '13 at 3:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.