Page Life Expectancy and Data Throughput
Page Life Expectancy, or PLE as it’s often referred to, is a measure of how frequently the average page of memory is evicted from the buffer pool. PLE is measured in seconds, and is the…
Transaction Isolation Levels and sp_executesql
SQL Server has several transaction isolation levels; probably the one most people are familiar with is the default of “read committed”. However, you may not realize it, but running dynamic code via sys.sp_executesql doesn’t necessarily…
Archive Data by Date
Tables with a large amount of data can become unwieldy. As a result, it’s not uncommon to archive data by date into an archive table that contains older rows we care about, but don’t need…
Memory Consumption by Object
SQL Server caches object data in memory in the buffer pool. Understanding memory consumption by object can be crucial for performance. For instance, you may have a large logging table consuming 90% of the buffer…
dm_db_database_page_allocations
sys.dm_db_database_page_allocations is an undocumented SQL Server T-SQL Dynamic Management Function. This DMF provides details about allocated pages, allocation units, and allocation extents. The function definition in SQL Server 2016 is:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
create function sys.dm_db_database_page_allocations ( @DatabaseId SMALLINT, @TableId INT = NULL, @IndexId INT = NULL, @PartitionId BIGINT = NULL, @Mode NVARCHAR(64) = 'LIMITED' ) returns table as return select DA.[database_id], DA.[object_id], DA.[index_id], DA.[partition_id], DA.[rowset_id], DA.[allocation_unit_id], DA.[allocation_unit_type], IP.[name] as 'allocation_unit_type_desc', DA.[data_clone_id], DA.[data_clone_state] as 'clone_state', case (DA.[data_clone_state]) when 0 then N'PRIMARY' else N'SECONDARY' end AS clone_state_desc, DA.[extent_file_id], DA.[extent_page_id], DA.[allocated_page_iam_file_id], DA.allocated_page_iam_page_id, DA.[allocated_page_file_id], DA.allocated_page_page_id, DA.is_allocated, DA.is_iam_page, DA.is_mixed_page_allocation, DA.page_free_space_percent, DA.page_type, DA.page_type_desc, DA.page_level, DA.next_page_file_id, DA.next_page_page_id, DA.previous_page_file_id, DA.previous_page_page_id, DA.is_page_compressed, DA.has_ghost_records from openrowset (table DM_DB_DATABASE_ALLOCATIONS, @DatabaseId, @TableId, @IndexId, @PartitionId, @Mode) DA JOIN sys.syspalvalues IP ON IP.[class] = 'AUTY' AND IP.[value] = DA.[allocation_unit_type] |
The function definition tells…
Using Dynamic SQL inside a Stored Procedure
Preventing direct access to database tables for users is a widely considered “best practice” for many DBAs. The pattern typically looks like User -> Stored Procedure -> Tables. Since the stored procedure owner has access…
