go back

home page

SQL Server Database Structures



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);
GO


Demo 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;
Share it:
Tags:
help from us