After watching Paul Randalls latest Pluralsight course : SQL Server: Index Fragmentation Internals, Analysis, and Solutions. He talks about Page splits and tracking via code he wrote in an article which you can read here in it he shows how to read a transaction backup and read through the page splits. I thought it might be interesting to loop through all the tran backups for a database using PowerShell to find them and load the results into a table. I found some interesting results in my environments, maybe you will to.


<#

Query - Page Splits written to Table from Transaction Log

#>
IF (!(Get-Module -Name sqlps))
    {
        Write-Host 'Loading SQLPS Module' -ForegroundColor DarkYellow
        Push-Location
        Import-Module sqlps -DisableNameChecking
        Pop-Location 
    }

## Load invoke-sqlcmd2 
. \\Server\PowerShell\Invoke-Sqlcmd2.ps1

## Server to Query 
$Server = "."
##DB
$db = 'SSISDB'
## Backup location
$BackupLocation = 'C:\SQL_Backup\Server2\SSISDB\LOG'
## Table of Results saved in:
$ResultsDB = "DBA"
## Build Table Name 
$tbl = $db + "PageSplits"
$fullpathDB = "$ResultsDB..$tbl"
## Create results table 
Invoke-Sqlcmd2 -ServerInstance $Server -Database $ResultsDB -Query "CREATE TABLE $tbl ([FileName] VARCHAR(1000), [CreationTime] datetime, [Index] VARCHAR(1000), [SplitType] VARCHAR(1000), [SplitCount] int);"

## get transaction log backups
$trn = Get-ChildItem -Path $BackupLocation

## loop through each backup writing pagesplit into to table
Foreach ($t in $trn) 
    { $t.CreationTime 

        Write-Host "Reading File : $t " -ForegroundColor Green
        $l = $t.FullName
        $time = $shortdt = "{0:yyyy-MM-dd HH:mm:ss}" -f ($t.CreationTime)

        Invoke-Sqlcmd2 -ServerInstance $Server -Query "INSERT INTO $fullpathDB  ([FileName], [CreationTime], [Index], SplitType, SplitCount )
                SELECT '$t', '$time',  CAST ([s].[name] AS VARCHAR) + '.' + CAST ([o].[name] AS VARCHAR)
                    + '.' + CAST ([i].[name] AS VARCHAR) AS [Index] ,
                    [f].[SplitType] ,
                    [f].[SplitCount]
            FROM    ( SELECT    [AllocUnitId] ,
                                ( CASE [Context]
                                    WHEN N'LCX_INDEX_LEAF' THEN N'Nonclustered'
                                    WHEN N'LCX_CLUSTERED' THEN N'Clustered'
                                    ELSE N'Non-Leaf'
                                  END ) AS [SplitType] ,
                                COUNT(1) AS [SplitCount]
                      FROM      fn_dump_dblog(NULL, NULL, N'DISK', 1,'$l' ,
                                              DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                                              DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                                              DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                                              DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                                              DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                                              DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                                              DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                                              DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                                              DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                                              DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                                              DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                                              DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                                              DEFAULT, DEFAULT, DEFAULT)
                      WHERE     [Operation] = N'LOP_DELETE_SPLIT'
                      GROUP BY  [AllocUnitId] ,
                                [Context]
                    ) f
                    JOIN sys.system_internals_allocation_units [a] ON [a].[allocation_unit_id] = [f].[AllocUnitId]
                    JOIN sys.partitions [p] ON [p].[partition_id] = [a].[container_id]
                    JOIN sys.indexes [i] ON [i].[index_id] = [p].[index_id]
                                            AND [i].[object_id] = [p].[object_id]
                    JOIN sys.objects [o] ON [o].[object_id] = [p].[object_id]
                    JOIN sys.schemas [s] ON [s].[schema_id] = [o].[schema_id];"
    }

Advertisements