Tag: storage

• Where are my Rows? – Using the %%physloc%% Virtual Column

  

  2014-06-25 - DMVs & CVs, General, Series, SQL Server Internals, Storage Wednesday

  Sometimes it is helpful to know on which page exactly SQL Server has stored a particular row. You can use the undocumented %%physloc%% virtual column, together with its supporting functions sys.fn_PhysLocFormatter and sys.fn_PhysLocCracker, to find out.

  [more…]

• Bulk Changed Map (BCM): The Minimally Logged Advantage

  

  2014-05-14 - General, Series, SQL Server Internals, Storage Wednesday

  The Bulk-Logged recovery mode can provide a performance gain for your database. Check out how SQL Server uses the Bulk Changed Map to make this possible.

  [more…]

• PFS Pages: How SQL Server tracks “Page Free Space”

  

  2014-04-30 - General, Series, SQL Server Internals, Storage Wednesday

  PFS pages contain information about the free space available on all pages in a database file. They contain one byte per page that also holds four additional status bits. Read on to discover how to interpret the PFS data.

  [more…]

• The Shared Global Allocation Map (SGAM)

  

  2014-04-23 - General, Series, SQL Server Internals, Storage Wednesday

  Discover how SQL Server tracks mixed extents containing free pages using a Shared Global Allocation Map made up of one SGAM page per GAM Interval.

  [more…]

• The Global Allocation Map (GAM)

  

  2014-04-16 - General, Series, SQL Server Internals, Storage Wednesday

  SQL Server uses the Global Allocation Map (GAM) to catalog if an extent is allocated or not. In a 7988-byte bitmap each extent is associated with a single bit to convey that information. Read on to get all the details.

  [more…]