Presentation Slides
Demo Allocation withing data files
-- create database
USE master
GO
DROP DATABASE IF EXISTS FileAllocDemo;
GO
CREATE DATABASE FileAllocDemo
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'FileAllocDemo_P1'
, FILENAME = N'D:\DATA\FileAllocDemo_P1.mdf'
, SIZE = 8192KB , FILEGROWTH = 65536KB ),
FILEGROUP [SECONDARY]
( NAME = N'FileAllocDemo_S1'
, FILENAME = N'D:\DATA\FileAllocDemo_S1.ndf'
, SIZE = 8192KB , FILEGROWTH = 65536KB ),
( NAME = N'FileAllocDemo_S2'
, FILENAME = N'D:\DATA\FileAllocDemo_S2.ndf'
, SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'FileAllocDemo_log'
, FILENAME = N'D:\DATA\FileAllocDemo_log.ldf'
, SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
USE [FileAllocDemo]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'SECONDARY')
ALTER DATABASE [FileAllocDemo] MODIFY FILEGROUP [SECONDARY] DEFAULT
GO
-- create table
USE FileAllocDemo
GO
CREATE TABLE T
(
col1 int identity PRIMARY KEY,
col2 char(8000) default (replicate('a',8000))
)
GO
-- CHECK FILEGROUP
SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name
, t.name AS table_name
, i.index_id
, fg.name AS filegroup_name
, FORMAT(p.rows, '#,###') AS rows
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
LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds ON ps.data_space_id=dds.partition_scheme_id AND p.partition_number=dds.destination_id
INNER JOIN sys.filegroups fg ON COALESCE(dds.data_space_id, i.data_space_id)=fg.data_space_id;
GO
-- DEMO PART 1
SELECT file_id
, name
, FILEGROUP_NAME(data_space_id)
, (size *1.0 )/128 as size_mb
FROM sys.database_files;
GO
INSERT INTO T DEFAULT VALUES;
GO
DBCC EXTENTINFO (0,T,-1);
GO
-- DEMO PART 2
SELECT file_id
, name
, FILEGROUP_NAME(data_space_id)
, (size * 8) / 1024 as size_mb
FROM sys.database_files;
GO
SET NOCOUNT ON;
INSERT INTO T DEFAULT VALUES;
GO 1000
SELECT file_id
, name
, FILEGROUP_NAME(data_space_id)
, (size * 8) / 1024 as size_mb
FROM sys.database_files;
GO
Demo Analyzing DB Structures
--Create table and insert test data
use AdventureWorks;
GO
IF object_id('viewPage') is not null drop table viewPage;
GO
CREATE TABLE viewPage
(
ID int identity(1,1) not null,
rowData varchar(8000)
)
GO
DECLARE @i int = 1;
WHILE @i <=3
BEGIN
INSERT INTO viewPage (rowData) VALUES (REPLICATE(cast(@i as char(1)), 2000))
SET @i = @i + 1
END
GO
-- Find page information
-- DBCC IND replacement
/*
The new DMF provides replacement functionality for DBCC IND. Based on the parameters provided, it will return a list of all pages that are associated with one or more tables and indexes. The available parameters for sys.dm_db_database_page_allocations are:
@DatabaseId: Database from which to return the page listing for tables and indexes. The parameter is required and accepts the use of the DB_ID() function.
@TableId: Object_id for the table from which to return the page listing. The parameter is required and accepts the use of the OBJECT_ID() function. NULL can also be used to return all tables.
@IndexId: Index_id from the table which the page list is from. The parameter is required and accepts the use of NULL to return information for all indexes.
@PartionId: ID of the partition which the page list is returning. The parameter is required and accepts the use of NULL to return information for all indexes.
@Mode: Defines the mode for returning data, the options are DETAILED or LIMITED. With LIMITED, the information is limited to page metadata, such as page allocation and relationships information. Under the DETAILED mode, additional information is provided, such as page type and inter-page relationship chains.
--------------
DMF Column DBCC Column Description
object_id ObjectID Object ID for the table or view
index_id IndexID ID for the index
partition_id PartitionNumber Partition number for the index
rowset_id PartitionID Partition ID for the index
allocation_unit_type_desc iam_chain_type Description of the allocation unit
allocated_page_iam_file_id IAMFID File ID for the index allocation map page associated to the page
allocated_page_iam_page_id IAMPID Page ID for the index allocation map page associated to the page
allocated_page_file_id PageFID File ID of the allocated page
allocated_page_page_id PagePID Page ID for the allocated page
page_type PageType Page type ID for the allocated page
page_level IndexLevel Level of the page in B-Tree index
next_page_file_id NextPageFID File ID for the next page
next_page_page_id NextPagePID Page ID for the next page
previous_page_file_id PrevPageFID File ID for the previous page
previous_page_page_id PrevPagePID Page ID for the previous page
--------------
New sys.dm_db_database_page_allocations Columns
DMF Column Description
database_id ID of the database
allocation_unit_id ID of the allocation unit
allocation_unit_type Type of allocation unit
data_clone_id Unknown
clone_state Unknown
clone_state_desc Unknown
extent_file_id File ID of the extent
extent_page_id Page ID for the extent
is_allocated Indicates whether a page is allocated
is_iam_page Indicates whether a page is the index allocation page
is_mixed_page_allocation Indicates whether a page is allocated
page_free_space_percent Percentage of space free on the page
page_type_desc Description of the page type
is_page_compressed Indicates if the page is compressed
has_ghost_records Indicates if the page has ghost records
*/
select db_name(database_id) as DatabaseName
, OBJECT_NAME(object_id) as TableName
, allocation_unit_type
, allocation_unit_type_desc
, allocated_page_file_id
, allocated_page_page_id
from sys.dm_db_database_page_allocations(db_id('AdventureWorks')
,object_id('viewPage')
,NULL
,NULL
,'DETAILED')
where page_type = 1;
GO
-- Enable trace flag
DBCC TRACEON(3604);
GO
-- View page allocation
dbcc page('AdventureWorks',1,37320,3);
GO
-- Update data
update viewPage set [rowData] = replicate('1',5000) where id = 1;
GO
-- Find page information
select db_name(database_id) as DatabaseName
, OBJECT_NAME(object_id) as TableName
, allocation_unit_type
, allocation_unit_type_desc
, allocated_page_file_id
, allocated_page_page_id
from sys.dm_db_database_page_allocations(db_id('AdventureWorks')
,object_id('viewPage')
,NULL
,NULL
,'DETAILED')
where page_type = 1;
GO
-- View updated page allocation
dbcc page('AdventureWorks',1,37321,3);
GODemo Monitor tempDB
-- Amount of space in each tempdb file (Free and Used)
use tempdb
SELECT name, physical_name, SUM(size)*1.0/128 AS [size in MB]
FROM sys.database_files
group by name, physical_name
-- Amount of free space in each tempdb file
SELECT b.name, b.physical_name, SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage a join sys.database_files b
on a.file_id = b.file_id
GROUP BY b.name, b.physical_name
-- Amount of space used by the version Store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;
-- Number of pages and the amount of space in MB used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;
-- Amount of space used by user objects in tempdb
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;