--- PDF PAGE 653 ---
645
gotcha here is that an external wait does not always mean that the thread is actually 
waiting. It could be performing an operation external to SQL Server, such as an extended 
stored procedure running external code.
Any task that has been issued is in one of three states: running, runnable, or 
suspended. If a task is in the running state, then it is actually being executed on a 
processor. When a task is in the runnable state, it sits on the processor queue, awaiting its 
turn to run. This is known as a signal wait. When a task is suspended, it means that the 
task is waiting for any reason other than a signal wait. In other words, it is experiencing a 
resource wait, a queue wait, or an external wait. Each query is likely to alternate between 
the three states as it progresses.
The sys.dm_os_wait_stats returns details of the cumulative waits for each wait 
type, since the instance started or since the statistics exposed by the DMV were reset. 
You can reset the statistics by running the command in Listing 17-16. This is important, 
as it gives a holistic view, as to the source of bottlenecks.
Listing 17-16.  Resetting Wait Stats
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) ;
The columns returned by sys.dm_os_wait_stats are detailed in Table 17-9.
To find the wait types that are responsible for the highest cumulative wait time, run 
the query in Listing 17-17. This query adds a calculated column to the result set, which 
deducts the signal wait time from the overall wait time to avoid CPU pressure from 
skewing the results.
Table 17-9.  sys.dm_os_wait_stats Columns
Column
Description
wait_type
The name of the wait type that has occurred.
waiting_tasks_count
The number of tasks that have occurred on this wait type.
wait_time_ms
The cumulative time of all waits against this wait type, displayed in 
milliseconds. This includes signal wait times.
max_wait_time_ms
The maximum duration of a single wait against this wait type.
signal_wait_time_ms
The cumulative time for all signal waits against this wait type.
Chapter 17  SQL Server Metadata

--- PDF PAGE 655 ---
647
wait_time_ms BIGINT
) ;
DECLARE @Waits2 TABLE
(
wait_type NVARCHAR(128),
wait_time_ms BIGINT
) ;
INSERT INTO @waits1
SELECT wait_type
        ,wait_time_ms
FROM sys.dm_os_wait_stats ;
WAITFOR DELAY '00:10:00' ;
INSERT INTO @Waits2
SELECT wait_type
        ,wait_time_ms
FROM sys.dm_os_wait_stats ;
SELECT TOP 5
        w2.wait_type
        ,w2.wait_time_ms - w1.wait_time_ms
FROM @Waits1 w1
INNER JOIN @Waits2 w2
        ON w1.wait_type = w2.wait_type
ORDER BY w2.wait_time_ms - w1.wait_time_ms DESC ;
Database Metadata
In previous versions of SQL Server, if a DBA needed to discover information about 
specific pages within a database, he had no choice but to use the well-known, but 
undocumented, DBCC command, DBCC PAGE. SQL Server addresses this issue, by adding 
a new dynamic management view, called sys.dm_db_page_info. It is fully documented 
and supported by Microsoft, and provides the ability to return a page header, in a table-­
valued format. The function accepts the parameters detailed in Table 17-10.
Chapter 17  SQL Server Metadata

--- PDF PAGE 656 ---
648
In order to populate these parameters, an additional system function has been 
added, called sys.fn_PageResCracker. This function can be cross applied to a table, 
passing %%physloc%% as a parameter. Alternatively, if cross applied to the sys.dm_exec_
requests DMV, or sys.sysprocesses, a deprecated system view, an additional column 
has been added, called page_resource, which can be passed as a parameter to the 
function. This is helpful, if you are diagnosing an issue with page waits. When passed a 
page resource/physical location object, the function will return the database_id,  
file_id, and page_id of each row in a result set.
Caution  When used with %%physloc%% as opposed to a page_resource 
object, the sys.fn_PageResCracker function returns a slot_id, as opposed to 
a database_id. Therefore, when used with %%physloc%%, the DB_ID() function 
should be used to obtain the database_id, and the database_id column returned 
by the function should be discarded.
Table 17-11 details the columns that are returned by the sys.dm_db_page_info DMF.
Table 17-10.  Parameters Accepted by sys.dm_db_page_info
Parameter
Description
Database_id
The database_id of the database that you wish to return details for.
File_id
The file_id of the file that you wish to return details for.
Page_id
The page_id of the page that you are interested in.
Mode
Mode can be set to either LIMITED or DETAILED. The only difference between the 
modes is that when LIMITED is used, the description columns are not populated. 
This can improve performance against large tables.
Chapter 17  SQL Server Metadata

--- PDF PAGE 656 ---
648
In order to populate these parameters, an additional system function has been 
added, called sys.fn_PageResCracker. This function can be cross applied to a table, 
passing %%physloc%% as a parameter. Alternatively, if cross applied to the sys.dm_exec_
requests DMV, or sys.sysprocesses, a deprecated system view, an additional column 
has been added, called page_resource, which can be passed as a parameter to the 
function. This is helpful, if you are diagnosing an issue with page waits. When passed a 
page resource/physical location object, the function will return the database_id,  
file_id, and page_id of each row in a result set.
Caution  When used with %%physloc%% as opposed to a page_resource 
object, the sys.fn_PageResCracker function returns a slot_id, as opposed to 
a database_id. Therefore, when used with %%physloc%%, the DB_ID() function 
should be used to obtain the database_id, and the database_id column returned 
by the function should be discarded.
Table 17-11 details the columns that are returned by the sys.dm_db_page_info DMF.
Table 17-10.  Parameters Accepted by sys.dm_db_page_info
Parameter
Description
Database_id
The database_id of the database that you wish to return details for.
File_id
The file_id of the file that you wish to return details for.
Page_id
The page_id of the page that you are interested in.
Mode
Mode can be set to either LIMITED or DETAILED. The only difference between the 
modes is that when LIMITED is used, the description columns are not populated. 
This can improve performance against large tables.
Chapter 17  SQL Server Metadata

--- PDF PAGE 657 ---
649
Table 17-11.  Columns Returned by sys.dm_db_page_info
Column
Description
Database_id
The ID of the database
File_id
The ID of the file
Page_id
The ID of the page
page_type
The internal ID associated with the page type description
Page_type_desc
page_flag_bits
page_flag_bits_desc
The type of page. For example, data page, index page, IAM page, PFS 
page, etc.
page_type_flag_bits
Hexadecimal value representing the page flags
page_type_flag_bits_desc A description of the page flags
object_id
The ID of the object that the page is a part of
index_id
The ID of the index that the page is part of
partition_id
The partition ID of the partition that the page is part of
alloc_unit_id
The ID of the allocation unit where the page is stored
page_level
The level of the page within a B-tree structure
slot_count
The number of slots within the page
ghost_rec_count
The number of records of the page that have been marked for deletion, 
but have not yet been physically removed
torn_bits
Used to detect data corruption, by storing 1 bit for every torn write detected
is_iam_pg
Indicates if the page is an IAM page
is_mixed_ext
Indicates if the page is part of a mixed extent (an extent allocated to 
multiple objects)
pfs_file_id
The file ID of the file where the page’s associated PFS (Page Free 
Space) page is stored
pfs_page_id
The page ID of the PFS page that is associated with the page
pfs_alloc_percent
The amount of free space on the page
pfs_status
The value of the page’s PFS byte
pfs_status_desc
A description of the page’s PFS byte
(continued)
Chapter 17  SQL Server Metadata

--- PDF PAGE 658 ---
650
Table 17-11.  (continued)
Column
Description
gam_file_id
The file ID of the file where the page’s associated GAM (global allocation 
map) page is stored
gam_page_id
The page ID of the GAM page, which is associated with the page
gam_status
Indicates if the page is allocated in GAM
gam_status_desc
Describes the GAM status marker
sgam_file_id
The file ID of the file where the page’s associated SGAM (shared global 
allocation map) page is stored
sgam_page_id
The page ID of the SGAM page, which is associated with the page
sgam_status
Indicates if the page is allocated in SGAM
sgam_status_desc
Describes the SGAM status marker
diff_map_file_id
The file ID of the file containing the page’s associated differential 
bitmap page
diff_map_page_id
The page ID of the differential bitmap page associated with the page
diff_status
Indicates if the page has changed since the last differential backup
diff_status_desc
Describes the differential status marker
ml_file_id
The file ID of the file that stores the page’s associated minimally logged 
bitmap page
ml_page_id
The page ID of the minimally logged bitmap page, associated with  
the page
ml_status
Indicates if the page is minimally logged
ml_status_desc
Describes the minimally logged status marker
free_bytes
The amount of free space on the page (in bytes)
free_data_offset
The page offset, to the start of the free space on the page
reserved_bytes
If the page is a leaf-level index page, indicates the amount of rows 
awaiting ghost cleanup. If the page is on a heap, then indicates the 
number of free bytes reserved by all transactions
reserved_xdes_id
Used for MSFT support for debugging
(continued)
Chapter 17  SQL Server Metadata

--- PDF PAGE 659 ---
651
The potential occasions where this data may prove invaluable are almost limitless. 
The script in Listing 17-20 demonstrates how this data could be used to determine the 
maximum log sequence number in a critical table, in preparation for a restore activity. 
The DBA can then use the maximum LSN, to ensure that a point-in-time restore captures 
the latest modifications to the critical data.
Listing 17-20.  Find the Most Recent LSN to Modify a Table
CREATE DATABASE Chapter17
GO
ALTER DATABASE Chapter17
SET RECOVERY FULL
GO
USE Chapter17
GO
CREATE TABLE dbo.CriticalData (
    ID    INT    IDENTITY    PRIMARY KEY    NOT NULL,
    ImportantData    NVARCHAR(128) NOT NULL
)
Column
Description
xdes_id
Used for MSFT support for debugging
prev_page_file_id
The file ID of the previous page in the IAM chain
prev_page_page_id
The page ID of the previous page in the IAM chain
next_page_file_id
The file ID of the next page in the IAM chain
next_page_page_id
The page ID of the next page in the IAM chain
min_len
The length of fixed width rows
Page_lsn
The last LSN (log sequence number) to modify the page
header_version
The version of the page header
Table 17-11.  (continued)
Chapter 17  SQL Server Metadata