SQLDBA- TempDB Alert and Auto Kill on Threshold Breach

Case: one  of user  running query  long time. DBA,  will notify at that the session TEMP use has been more than 1 TB  and kill that manfully. Its cleared temp DB space. But  it is not  reclaimed the temp DB drive space.  to re- claim temp DB space we need  to raise change request  and approval from CAB meeting. 

In this case we need to  set a job that  will need kill automatically  user session  when its reached to threshold values. 

This SQL Agent job continuously monitors user sessions consuming TempDB space. When a session's TempDB usage reaches or exceeds the configured threshold value, the job automatically:

1. Identifies the session exceeding the threshold.
2. Sends an email notification to the associated user and DBA team.
3. Waits for the configured grace period.
4. Rechecks the session's TempDB usage.
5. Automatically terminates the session if the TempDB usage remains above the threshold.
6. Sends a confirmation or failure notification to the DBA team.

USE master 

GO

DECLARE @ThresholdGB DECIMAL(18,2) = 100.00;

DECLARE

      @SessionID      INT

    , @EmailAddress   VARCHAR(500)

    , @Body           NVARCHAR(MAX)

    , @KillCmd        NVARCHAR(100)

    , @CurrentGB      DECIMAL(18,2)

    , @Recipients     VARCHAR(500);

IF OBJECT_ID('tempdb..#TempDBSessions') IS NOT NULL

    DROP TABLE #TempDBSessions;

SELECT

      s.session_id

    , s.login_name

    , s.host_name

    , s.program_name

    , DB_NAME(r.database_id) AS database_name

    , CAST(

        (su.user_objects_alloc_page_count +

         su.internal_objects_alloc_page_count) * 8.0 / 1024 / 1024

         AS DECIMAL(18,2)

      ) AS TempDB_Used_GB

    , r.start_time

    , ee.email_address

INTO #TempDBSessions

FROM sys.dm_db_session_space_usage su

JOIN sys.dm_exec_sessions s

    ON su.session_id = s.session_id

LEFT JOIN sys.dm_exec_requests r

    ON s.session_id = r.session_id

LEFT JOIN reference.dbo.emplye_master em

    ON em.usrnam =

       CASE

            WHEN CHARINDEX('\',s.login_name) > 0

                THEN RIGHT(s.login_name,

                           LEN(s.login_name) - CHARINDEX('\',s.login_name))

            ELSE s.login_name

       END

OUTER APPLY

(

    SELECT TOP (1) email_address

    FROM reference.dbo.emplye_email ee

    WHERE ee.emplye_id = em.emplye_id

    ORDER BY ee.mdfctn_dt DESC

) ee

WHERE s.is_user_process = 1

AND s.session_id <> @@SPID

AND s.program_name = 'Microsoft SQL Server Management Studio - Query'

AND CAST(

        (su.user_objects_alloc_page_count +

         su.internal_objects_alloc_page_count) * 8.0 / 1024 / 1024

        AS DECIMAL(18,2)

    ) >= @ThresholdGB;

DECLARE AlertCursor CURSOR LOCAL FAST_FORWARD

FOR

SELECT session_id, email_address

FROM #TempDBSessions;

OPEN AlertCursor;

FETCH NEXT FROM AlertCursor

INTO @SessionID, @EmailAddress;

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @Recipients = COALESCE(NULLIF(@EmailAddress,''),'kovvuru.subbareddy@cleanharbors.com');

    ----------------------------------------------------------------

    -- Build HTML Email

    ----------------------------------------------------------------

    SELECT @Body =

    '<html><body>

    <h3>Critical TempDB Usage Alert</h3>

    <table border=1 cellpadding=5 cellspacing=0>

      <tr><td><b>Session ID</b></td><td>' + CAST(session_id AS VARCHAR(20)) + '</td></tr>

      <tr><td><b>Login Name</b></td><td>' + ISNULL(login_name,'') + '</td></tr>

      <tr><td><b>Host Name</b></td><td>' + ISNULL(host_name,'') + '</td></tr>

      <tr><td><b>Program Name</b></td><td>' + ISNULL(program_name,'') + '</td></tr>

      <tr><td><b>Database</b></td><td>' + ISNULL(database_name,'') + '</td></tr>

      <tr><td><b>TempDB Usage</b></td><td>' +

            CAST(TempDB_Used_GB AS VARCHAR(20)) + ' GB</td></tr>

    </table>

    <br><br>

    This session exceeded the configured threshold of '

    + CAST(@ThresholdGB AS VARCHAR(20)) + ' GB.

    <br>Please terminate and  optimize the query immediately. Else the '+'Session ID ' + CAST(@SessionID AS VARCHAR(20)) + ' will be automatically terminated after 5 Minutes. 

    </body></html>'

    FROM #TempDBSessions

    WHERE session_id = @SessionID;

    ----------------------------------------------------------------

    -- Send Warning Email

    ----------------------------------------------------------------

    EXEC msdb.dbo.sp_send_dbmail

        --@profile_name = 'DBA_Mail_Profile',

        @recipients = @Recipients,

        @copy_recipients = 'kovvuru.subbareddy@cleanharbors.com',

        @subject = 'CRITICAL ALERT - TempDB Usage Exceeded',

        @body = @Body,

        @body_format = 'HTML';

    --------------------------------------------------------------

    -- Grace Period

    ----------------------------------------------------------------

    WAITFOR DELAY '00:05:00';

    ----------------------------------------------------------------

    -- Recheck Current TempDB Usage

    ----------------------------------------------------------------

    SELECT

        @CurrentGB =

        CAST(

            (su.user_objects_alloc_page_count +

             su.internal_objects_alloc_page_count)

             * 8.0 / 1024 / 1024

             AS DECIMAL(18,2)

        )

    FROM sys.dm_db_session_space_usage su

    WHERE su.session_id = @SessionID;

    ----------------------------------------------------------------

    -- Kill only if still above threshold

    ----------------------------------------------------------------

    IF ISNULL(@CurrentGB,0) >= @ThresholdGB

    BEGIN

        BEGIN TRY

        DECLARE @body1   NVARCHAR(MAX)  =   'Session ID ' + CAST(@SessionID AS VARCHAR(20)) + ' was automatically terminated because TempDB usage remained at '+ CAST(@CurrentGB AS VARCHAR(20))+ ' GB after the 5-minute grace period.'

        DECLARE @body2   NVARCHAR(MAX)  =    'Failed to terminate Session ID '+ CAST(@SessionID AS VARCHAR(20))+ CHAR(13)+CHAR(10) + ERROR_MESSAGE()

            SET @KillCmd = 'KILL ' + CAST(@SessionID AS VARCHAR(20));

           EXEC (@KillCmd);

            EXEC msdb.dbo.sp_send_dbmail

                --@profile_name = 'DBA_Mail_Profile',

                @recipients = @Recipients,

                @copy_recipients = 'kovvuru.subbareddy@cleanharbors.com',

                @subject = 'Session Killed - TempDB Threshold Exceeded',

                @body =@body1;

        END TRY

        BEGIN CATCH

            EXEC msdb.dbo.sp_send_dbmail

                --@profile_name = 'DBA_Mail_Profile',

                @recipients = 'kovvuru.subbareddy@cleanharbors.com',

                @subject = 'Failed to Kill Session',

                @body =@body2;

        END CATCH

    END

    ELSE

    BEGIN

        ----------------------------------------------------------------

        -- Optional: Notify DBA that usage dropped below threshold

        ----------------------------------------------------------------

         

       DECLARE   @body3   NVARCHAR(MAX)  = 'Session ID ' + CAST(@SessionID AS VARCHAR(20)) + ' reduced TempDB usage to ' + CAST(ISNULL(@CurrentGB,0) AS VARCHAR(20)) + ' GB. Session was not terminated.'; 

        EXEC msdb.dbo.sp_send_dbmail

            --@profile_name = 'DBA_Mail_Profile',

            @recipients = 'kovvuru.subbareddy@cleanharbors.com',

            @subject = 'TempDB Usage Reduced - No Kill Required',

            @body =@body3 ;

    END

    FETCH NEXT FROM AlertCursor

    INTO @SessionID, @EmailAddress;

END

CLOSE AlertCursor;

DEALLOCATE AlertCursor;

GO

Below is  the  verification quesy:

SELECT

    s.session_id,

    s.login_name,

    s.host_name,

    s.program_name,

    DB_NAME(r.database_id) AS database_name,

    CAST(

        (su.user_objects_alloc_page_count +

         su.internal_objects_alloc_page_count) * 8.0 / 1024 / 1024

         AS DECIMAL(18,2)

    ) AS TempDB_Used_GB,

    r.status,

    r.command,

    r.start_time,

    em.emplye_id,

    UserName =

        CASE

            WHEN CHARINDEX('\', s.login_name) > 0

                THEN SUBSTRING(

                        s.login_name,

                        CHARINDEX('\', s.login_name) + 1,

                        LEN(s.login_name)

                     )

            ELSE s.login_name

        END,

    ee.email_address

FROM sys.dm_db_session_space_usage su

INNER JOIN sys.dm_exec_sessions s

    ON su.session_id = s.session_id

LEFT JOIN sys.dm_exec_requests r

    ON s.session_id = r.session_id

LEFT JOIN reference.dbo.emplye_master em

    ON em.usrnam =

       CASE

            WHEN CHARINDEX('\', s.login_name) > 0

                THEN SUBSTRING(

                        s.login_name,

                        CHARINDEX('\', s.login_name) + 1,

                        LEN(s.login_name)

                     )

            ELSE s.login_name

       END

OUTER APPLY

(

    SELECT TOP (1)

           email_address

    FROM reference.dbo.emplye_email e

    WHERE e.emplye_id = em.emplye_id

    ORDER BY e.mdfctn_dt DESC

) ee

WHERE

    (

        (su.user_objects_alloc_page_count +

         su.internal_objects_alloc_page_count) * 8.0 / 1024 / 1024

    ) >= 1

    AND s.is_user_process = 1

ORDER BY TempDB_Used_GB DESC;

--EXEC msdb.dbo.sp_send_dbmail

--     @recipients = 'kumar.kiran2@cleanharbors.com;qi.guang@cleanharbors.com;jampanna.sudhakar@cleanharbors.com;kovvuru.subbareddy@cleanharbors.com',  --;jampanna.sudhakar@cleanharbors.com

--     @body = @body,

-- @body_format ='HTML',

--     @subject = @sub

Below one not working need to  work:

DECLARE @ThresholdGB DECIMAL(18,2) = 1;

DECLARE

      @SessionID INT

    , @LoginName SYSNAME

    , @HostName VARCHAR(256)

    , @ProgramName VARCHAR(500)

    , @DatabaseName SYSNAME

    , @TempDBGB DECIMAL(18,2)

    , @StartTime DATETIME

    , @EmailAddress VARCHAR(500)

    , @Body NVARCHAR(MAX)

    , @KillCmd NVARCHAR(100);

DECLARE TempDB_Cursor CURSOR FAST_FORWARD

FOR

SELECT

      s.session_id

    , s.login_name

    , s.host_name

    , s.program_name

    , DB_NAME(r.database_id) AS database_name

    , CAST(

        (su.user_objects_alloc_page_count +

         su.internal_objects_alloc_page_count) * 8.0 / 1024 / 1024

         AS DECIMAL(18,2)

      ) AS TempDB_Used_GB

    , r.start_time

    , ee.email_address

FROM sys.dm_db_session_space_usage su

JOIN sys.dm_exec_sessions s

    ON su.session_id = s.session_id

LEFT JOIN sys.dm_exec_requests r

    ON s.session_id = r.session_id

LEFT JOIN reference.dbo.emplye_master em

    ON em.usrnam =

       CASE

           WHEN CHARINDEX('\',s.login_name) > 0

               THEN RIGHT(s.login_name,

                         LEN(s.login_name) - CHARINDEX('\',s.login_name))

           ELSE s.login_name

       END

OUTER APPLY

(

    SELECT TOP(1)

           email_address

    FROM reference.dbo.emplye_email ee

    WHERE ee.emplye_id = em.emplye_id

    ORDER BY ee.mdfctn_dt DESC

) ee

WHERE

      s.is_user_process = 1

  AND s.session_id <> @@SPID

  AND CAST(

        (su.user_objects_alloc_page_count +

         su.internal_objects_alloc_page_count) * 8.0 / 1024 / 1024

         AS DECIMAL(18,2)

      ) >= @ThresholdGB

  AND s.login_name IN

      (

          'Microsoft SQL Server Management Studio - Query'

      );

OPEN TempDB_Cursor;

FETCH NEXT FROM TempDB_Cursor

INTO

      @SessionID

    , @LoginName

    , @HostName

    , @ProgramName

    , @DatabaseName

    , @TempDBGB

    , @StartTime

    , @EmailAddress;

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @recipients1   VARCHAR(500)=  COALESCE(NULLIF(@EmailAddress,''),'kovvuru.subbareddy@cleanharbors.com') 

    SET @Body =

    '<html><body>' +

    '<h3>TempDB Usage Alert</h3>' +

    '<p>The following session has exceeded the configured TempDB threshold of '

    + CAST(@ThresholdGB AS VARCHAR(20)) + ' GB.</p>' +

    '<table border=1 cellpadding=5 cellspacing=0>' +

    '<tr><td><b>Session ID</b></td><td>' + CAST(@SessionID AS VARCHAR(20)) + '</td></tr>' +

    '<tr><td><b>Login Name</b></td><td>' + ISNULL(@LoginName,'') + '</td></tr>' +

    '<tr><td><b>Host Name</b></td><td>' + ISNULL(@HostName,'') + '</td></tr>' +

    '<tr><td><b>Program Name</b></td><td>' + ISNULL(@ProgramName,'') + '</td></tr>' +

    '<tr><td><b>Database</b></td><td>' + ISNULL(@DatabaseName,'') + '</td></tr>' +

    '<tr><td><b>TempDB Usage (GB)</b></td><td>' + CAST(@TempDBGB AS VARCHAR(50)) + '</td></tr>' +

    '<tr><td><b>Start Time</b></td><td>' + CONVERT(VARCHAR(30),@StartTime,120) + '</td></tr>' +

    '</table>' +

    '<br><br>This session has exceeded the permitted TempDB usage threshold.' +

    '<br>Please review the execution immediately.' +

    '<br><br>Regards,' +

    '<br>SQL DBA Team' +

    '</body></html>';

    --EXEC msdb.dbo.sp_send_dbmail

    --    -- @recipients =  COALESCE(NULLIF(@EmailAddress,''),'kovvuru.subbareddy@cleanharbors.com')

    --     @recipients =  'kovvuru.subbareddy@cleanharbors.com',

    --    @copy_recipients = 'kovvuru.subbareddy@cleanharbors.com',

    --    @subject = 'CRITICAL ALERT - TempDB Usage Exceeded Threshold',

    --    @body = @Body,

    --    @body_format = 'HTML';

         EXEC msdb.dbo.sp_send_dbmail

        --@profile_name = 'DBA_Mail_Profile',

        @recipients = @recipients1,

        @copy_recipients = 'kovvuru.subbareddy@cleanharbors.com',

        @subject = 'ALERT: TempDB Usage Exceeded 100 GB',

        @body = @Body,

        @body_format = 'HTML'; 

    ------------------------------------------------------------------

    -- Wait 1 minute before killing (optional)

    ------------------------------------------------------------------

 --WAITFOR DELAY '00:01:00';

    ------------------------------------------------------------------

    -- Kill Session

    ------------------------------------------------------------------

    --SET @KillCmd = 'KILL ' + CAST(@SessionID AS VARCHAR(20));

    --BEGIN TRY

    --    --EXEC (@KillCmd);

    --    EXEC msdb.dbo.sp_send_dbmail

    --        --@profile_name = 'DBA_Mail_Profile',

    --        @recipients = @recipients1,

    --        @copy_recipients = 'kovvuru.subbareddy@cleanharbors.com',

    --        @subject = 'Session Terminated - TempDB Threshold Exceeded',

    --        @body =

    --        'Session ID '

    --        + CAST(@SessionID AS VARCHAR(20))

    --        + ' was automatically terminated because TempDB usage reached '

    --        + CAST(@TempDBGB AS VARCHAR(50))

    --        + ' GB.';

    --END TRY

    --BEGIN CATCH

    --    EXEC msdb.dbo.sp_send_dbmail

    --        --@profile_name = 'DBA_Mail_Profile',

    --        @recipients = @recipients1,

    --        @subject = 'Failed to Kill Session',

    --        @body =

    --        'Failed to terminate Session ID '

    --        + CAST(@SessionID AS VARCHAR(20))

    --        + CHAR(13) + CHAR(10)

    --        + ERROR_MESSAGE();

    --END CATCH;

    FETCH NEXT FROM TempDB_Cursor

    INTO

          @SessionID

        , @LoginName

        , @HostName

        , @ProgramName

        , @DatabaseName

        , @TempDBGB

        , @StartTime

        , @EmailAddress;

END

CLOSE TempDB_Cursor;

DEALLOCATE TempDB_Cursor;

GO

Table size and rows of tables in sql server

Below query  will help us, to know tables size and now  of rows in a table. 

SELECT

    SCHEMA_NAME(t.schema_id) AS SchemaName,

    t.name AS TableName,

    SUM(p.rows) AS RowCont,

    CAST(

        SUM(a.total_pages) * 8.0 / 1024 / 1024

        AS DECIMAL(18,2)

    ) AS TotalSizeGB,

    CAST(

        SUM(a.used_pages) * 8.0 / 1024 / 1024

        AS DECIMAL(18,2)

    ) AS UsedSizeGB,

    CAST(

        (SUM(a.total_pages) - SUM(a.used_pages)) * 8.0 / 1024 / 1024

        AS DECIMAL(18,2)

    ) AS UnusedSizeGB

FROM sys.tables t

INNER JOIN sys.indexes i

    ON t.object_id = i.object_id

INNER JOIN sys.partitions p

    ON i.object_id = p.object_id

    AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a

    ON p.partition_id = a.container_id

WHERE t.is_ms_shipped = 0

GROUP BY

    t.schema_id,

    t.name

ORDER BY

    TotalSizeGB DESC;

SQL Server Locks, Blocking, and Deadlocks Made Easy for Beginners

 

Understand how SQL Server protects your data and why sessions sometimes wait for each other

A practical beginner-friendly guide to understanding concurrency, troubleshooting waits, and reducing production risk.

=================================================================== 

Introduction

Have you ever executed a query and noticed that it keeps running without completing? Or perhaps an application suddenly becomes slow even though server CPU usage looks normal.

In many cases, the cause is related to three important SQL Server concepts:

·       Locks

·       Blocking

·       Deadlocks

Key idea  These mechanisms are not automatically problems. SQL Server uses locking to protect data and maintain consistency when multiple sessions access the same resources.

 

1. What Is a Lock?

A lock is like a "Do Not Disturb" sign placed on data while it is being used. If one user updates a bank account balance, SQL Server can temporarily lock that data so another user cannot modify it at the same time.

Example

Session 1 starts a transaction and updates one employee:

BEGIN TRAN UPDATE Employees SET Salary = Salary + 1000 WHERE EmployeeID = 1;

 

The row remains locked until the transaction is committed or rolled back. If Session 2 tries to update the same row, it must wait:

UPDATE Employees SET Salary = Salary + 500 WHERE EmployeeID = 1;

 

2. Common Lock Types

Shared Lock (S)

Used while reading data. Multiple sessions can normally hold compatible shared locks at the same time.

SELECT * FROM Employees;

 

Exclusive Lock (X)

Used while modifying data. An exclusive lock prevents incompatible access to the locked resource.

UPDATE Employees SET Salary = 50000 WHERE EmployeeID = 1;

 

Update Lock (U)

Used when SQL Server reads a resource with the intention of possibly changing it. Update locks help reduce certain conversion deadlocks.

3. What Is Blocking?

Blocking occurs when one session waits for another session to release an incompatible lock. Think of one person using an ATM while the next person waits for the transaction to finish.

Blocking Example

Session 1:

BEGIN TRAN UPDATE Customers SET City = 'London' WHERE CustomerID = 10; -- Leave the transaction open for demonstration

 

Session 2:

SELECT * FROM Customers WHERE CustomerID = 10;

 

Result  Depending on the isolation level and database configuration, Session 2 may wait for Session 1. That wait is blocking.

 

How to Detect Blocking

EXEC sp_who2;

 

In the output, the BlkBy column can identify the blocking session. For a more focused view:

SELECT     blocking_session_id,     session_id,     wait_type,     wait_time FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

 

4. What Is a Deadlock?

A deadlock occurs when two or more sessions form a cycle in which each session is waiting for a resource held by another. SQL Server detects the cycle, selects one transaction as the deadlock victim, rolls it back, and allows the other transaction to continue.

Simplified Deadlock Scenario

Session A                         Session B ---------                         --------- Locks Customer row                Locks Order row Needs Order row                   Needs Customer row         \                         /          +------ deadlock -------+

 

A typical application receives error 1205, stating that the transaction was deadlocked and chosen as the deadlock victim. The application should handle the error appropriately, often by retrying the transaction after a short delay.

5. How to Capture Deadlocks

SQL Server Extended Events can capture a deadlock graph that shows the sessions, resources, statements, and victim involved. The built-in system_health session is commonly used as a starting point.

SELECT * FROM sys.dm_xe_sessions WHERE name = 'system_health';

 

Important  The query above only confirms whether the session exists. To investigate a specific deadlock, review the captured xml_deadlock_report event and its graphical deadlock report.

 

6. Best Practices to Reduce Blocking

·       Keep transactions short and commit or roll back promptly.

·       Avoid unnecessary explicit transactions.

·       Create appropriate indexes so queries touch fewer rows and pages.

·       Avoid waiting for user input while a transaction remains open.

·       Schedule large maintenance or data-change operations carefully.

·       Evaluate row-versioning options such as Read Committed Snapshot Isolation only after application testing and capacity review.

7. Best Practices to Reduce Deadlocks

·       Access tables and resources in a consistent order across code paths.

·       Keep each transaction focused on the smallest practical unit of work.

·       Use selective predicates and effective indexes.

·       Avoid holding locks while performing unrelated processing.

·       Capture and analyze the deadlock graph instead of guessing.

·       Implement controlled retry logic for error 1205 where appropriate.

Consistent Access Order

Preferred pattern:

Customers  ->  Orders  ->  Payments

 

Riskier pattern:

Session A: Customers -> Orders Session B: Orders -> Customers

 

Quick Summary

Concept	Meaning
Lock	Protects a resource while SQL Server processes an operation.
Blocking	One session waits for another session to release an incompatible lock.
Deadlock	Sessions form a circular dependency and cannot proceed.
Deadlock victim	The transaction SQL Server terminates and rolls back to break the cycle.
Primary response	Use short transactions, good indexing, consistent access order, monitoring, and tested retry logic.

Conclusion

Locks are essential for data consistency. Blocking is a normal consequence of incompatible locking, although prolonged blocking can affect performance. Deadlocks are circular waiting conditions that SQL Server resolves by terminating one transaction.

Understanding these concepts helps DBAs, developers, data engineers, and architects diagnose application slowdowns and design safer transaction patterns.

SQL Server Storage Architecture Explained: How SQL Server Stores Data Internally

 

Introduction

Have you ever wondered what happens when you insert a row into a SQL Server table?

Where does the data actually go?

How does SQL Server find free space?

How are indexes stored?

Why do DBAs talk about Pages, Extents, GAM, SGAM, PFS, and IAM?

Understanding SQL Server Storage Architecture is one of the most important skills for any DBA, Database Architect, or Performance Engineer because almost every performance issue eventually leads back to how data is stored internally.

In this article, we will simplify SQL Server's internal storage mechanism using practical examples.

Why Storage Architecture Matters

Understanding storage internals helps you:

✅ Troubleshoot performance issues

✅ Understand index fragmentation

✅ Optimize large tables

✅ Reduce disk I/O

✅ Improve backup and restore strategies

✅ Analyze space usage

✅ Perform advanced DBA troubleshooting

The Foundation: Data Pages

SQL Server stores all data in Pages.

A Page is the smallest unit of storage.

Page Size - 1 Page = 8 KB

Every database is built using thousands or millions of pages.

Imagine a book.

• Book = Database
• Chapters = Tables
• Pages = SQL Server Data Pages

Data Page Structure

What the Header Stores

• Page ID
• Object ID
• Free Space Information
• Next Page Pointer

SQL Server uses these details to locate data quickly.

Extents: A Group of Pages

Managing individual pages would be inefficient.

Instead SQL Server groups pages together.

1 Extent = 8 Pages

Since each page is 8 KB: 8 × 8 KB = 64 KB

Hence 1 Extent = 64 KB

Visual representation of an extent:

Extent

├─ Page 1

├─ Page 2

├─ Page 3

├─ Page 4

├─ Page 5

├─ Page 6

├─ Page 7

└─ Page 8

Types of Extents

Uniform Extent

All 8 pages belong to the same object.

Table A

├─ Page1

├─ Page2

├─ Page3

...

 └─ Page8

Page1 → TableA

How SQL Server Finds Free Space

Now comes the interesting part.

How does SQL Server know where free pages exist?

The answer is:

Allocation Maps

These are special pages maintained by SQL Server.

PFS Page (Page Free Space)

PFS keeps track of:

• Free space on pages
• Empty pages
• Allocated pages

Think of it as: "Space Availability Register"

When new data arrives: INSERT INTO Customers

SQL Server first checks PFS.

GAM (Global Allocation Map)

GAM tracks extents that are completely free.

Free Extent ?

YES → Available

NO → Occupied

Think of GAM as: "Free Land Registry"

SGAM (Shared Global Allocation Map)

Tracks mixed extents that still have free pages.

Mixed Extent + Free Pages Available

Used mostly for small objects.

IAM (Index Allocation Map)

IAM connects tables and indexes to physical pages.

Without IAM SQL Server cannot locate table data.

Visualization:

Customer Table

│

▼

IAM Page

│

▼

Page 100

Page 101

Page 102

 Page 103

Think of IAM as: Google Maps for SQL Server

How an Insert Really Works

Suppose:

INSERT INTO Employee VALUES (1001,'Ajit')

SQL Server performs:

Step 1

Check PFS - Any page with free space?

Step 2

If none available:

Check GAM - Any free extent available?

Step 3

Allocate new extent

Step 4

Update IAM

Step 5

Store Row

Step 6

Commit Transaction

All this happens in milliseconds.

Real DBA Scenario

Imagine a table containing: 500 Million Rows

If pages become fragmented:

• More disk reads
• More I/O
• Slower queries

This is why DBAs run: ALTER INDEX REBUILD or ALTER INDEX REORGANIZE

Regular maintenance improves page organization and performance.

Key Interview Questions

Q1. What is a Page in SQL Server?

Smallest storage unit of size 8 KB.

Q2. What is an Extent?

Group of 8 Pages. (Size 64 KB)

Q3. What does GAM track?

Free extents.

Q4. What does SGAM track?

Mixed extents with available pages.

Q5. What does IAM do?

Maps database objects to physical pages.

Q6. What does PFS track?

Page allocation and free space.

Summary

SQL Server stores data in a highly organized structure:

Database

▼

File

▼

Extent (64 KB)

▼

Page (8 KB)

▼

Rows

SQL Server then uses:

• PFS
• GAM
• SGAM
• IAM

to manage storage efficiently.

Understanding these concepts will make performance tuning, capacity planning, and troubleshooting significantly easier.