functions/Get-DbaWaitResource.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
function Get-DbaWaitResource {
    <#
    .SYNOPSIS
        Returns the resource being waited upon
 
    .DESCRIPTION
        Given a wait resource in the form of 'PAGE: 10:1:9180084' returns the database, data file and the system object which is being waited up.
 
        Given a wait resource in the form of 'KEY: 7:35457594073541168 (de21f92a1572)', returns the database, object and index that is being waited on, With the -row switch the row data will also be returned.
 
    .PARAMETER SqlInstance
        The target SQL Server instance or instances.
 
    .PARAMETER SqlCredential
        Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted.
 
    .PARAMETER WaitResource
        The wait resource value as supplied in sys.dm_exec_requests
 
    .PARAMETER Row
        If this switch provided also returns the value of the row being waited on with KEY wait resources
 
    .PARAMETER EnableException
        Replaces user friendly yellow warnings with bloody red exceptions of doom!
        Use this if you want the function to throw terminating errors you want to catch.
 
    .NOTES
        Tags: Pages, DBCC
        Author: Stuart Moore (@napalmgram), stuart-moore.com
 
        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT
 
    .LINK
        https://dbatools.io/Get-DbaWaitResource
 
    .EXAMPLE
        PS C:\> Get-DbaWaitResource -SqlInstance server1 -WaitResource 'PAGE: 10:1:9180084'
 
        Will return an object containing; database name, data file name, schema name and the object which owns the resource
 
    .EXAMPLE
        PS C:\> Get-DbaWaitResource -SqlInstance server2 -WaitResource 'KEY: 7:35457594073541168 (de21f92a1572)'
 
        Will return an object containing; database name, schema name and index name which is being waited on.
 
    .EXAMPLE
        PS C:\> Get-DbaWaitResource -SqlInstance server2 -WaitResource 'KEY: 7:35457594073541168 (de21f92a1572)' -row
 
        Will return an object containing; database name, schema name and index name which is being waited on, and in addition the contents of the locked row at the time the command is run.
 
    #>

    [CmdletBinding()]
    param (
        [parameter(Mandatory)]
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstance]$SqlInstance,
        [PsCredential]$SqlCredential,
        [parameter(Mandatory, ValueFromPipeline)]
        [string]$WaitResource,
        [switch]$Row,
        [switch]$EnableException
    )

    process {
        if ($WaitResource -notmatch '^PAGE: [0-9]*:[0-9]*:[0-9]*$' -and $WaitResource -notmatch '^KEY: [0-9]*:[0-9]* \([a-f0-9]*\)$') {
            Stop-Function -Message "Row input - $WaitResource - Improperly formatted"
            return
        }

        try {
            $server = Connect-SqlInstance -SqlInstance $sqlinstance -SqlCredential $SqlCredential
        } catch {
            Write-Message -Level Warning -Message "Cannot connect to $SqlInstance"
        }

        $null = $WaitResource -match '^(?<Type>[A-Z]*): (?<dbid>[0-9]*):*'
        $ResourceType = $matches.Type
        $DbId = $matches.DbId
        $DbName = ($server.Databases | Where-Object ID -eq $dbid).Name
        if ($null -eq $DbName) {
            stop-function -Message "Database with id $dbid does not exist on $server"
            return
        }
        if ($ResourceType -eq 'PAGE') {
            $null = $WaitResource -match '^(?<Type>[A-Z]*): (?<dbid>[0-9]*):(?<FileID>[0-9]*):(?<PageID>[0-9]*)$'
            $DataFileSql = "select name, physical_name from sys.master_files where database_id=$DbID and file_ID=$($matches.FileID);"
            $DataFile = $server.query($DataFileSql)
            if ($null -eq $DataFile) {
                Write-Message -Level Warning -Message "Datafile with id $($matches.FileID) for $dbname not found"
                return
            }
            $ObjectIdSQL = "dbcc traceon (3604); dbcc page ($dbid,$($matches.fileID),$($matches.PageID),2) with tableresults;"
            try {
                $ObjectID = ($server.databases[$dbname].Query($ObjectIdSQL) | Where-Object Field -eq 'Metadata: ObjectId').Value
            } catch {
                Stop-Function -Message "You've requested a page beyond the end of the database, exiting"
                return
            }
            if ($null -eq $ObjectID) {
                Write-Message -Level Warning -Message "Object not found, could have been delete, or a transcription error when copying the Wait_resource to PowerShell"
                return
            }
            $ObjectSql = "select SCHEMA_NAME(schema_id) as SchemaName, name, type_desc from sys.all_objects where object_id=$objectID;"
            $Object = $server.databases[$dbname].query($ObjectSql)
            if ($null -eq $Object) {
                Write-Message -Warning "Object could not be found. Could have been removed, or could be a transcription error copying the Wait_resource to sowerShell"
            }
            [PsCustomObject]@{
                DatabaseID   = $DbId
                DatabaseName = $DbName
                DataFileName = $Datafile.name
                DataFilePath = $DataFile.physical_name
                ObjectID     = $ObjectID
                ObjectName   = $Object.Name
                ObjectSchema = $Object.SchemaName
                ObjectType   = $Object.type_desc
            }
        }
        if ($ResourceType -eq 'KEY') {
            $null = $WaitResource -match '^(?<Type>[A-Z]*): (?<dbid>[0-9]*):(?<frodo>[0-9]*) (?<physloc>\(.*\))$'
            $IndexSql = "select
                            sp.object_id as ObjectID,
                            OBJECT_SCHEMA_NAME(sp.object_id) as SchemaName,
                            sao.name as ObjectName,
                            si.name as IndexName
                        from
                            sys.partitions sp inner join sys.indexes si on sp.index_id=si.index_id and sp.object_id=si.object_id
                                inner join sys.all_objects sao on sp.object_id=sao.object_id
                        where
                            hobt_id = $($matches.frodo);
                "

            $Index = $server.databases[$dbname].Query($IndexSql)
            if ($null -eq $Index) {
                Write-Message -Level Warning -Message "Heap or B-Tree with ID $($matches.frodo) can not be found in $dbname on $server"
                return
            }
            $output = [PsCustomObject]@{
                DatabaseID   = $DbId
                DatabaseName = $DbName
                SchemaName   = $Index.SchemaName
                IndexName    = $Index.IndexName
                ObjectID     = $index.ObjectID
                Objectname   = $index.ObjectName
                HobtID       = $matches.frodo
            }
            if ($row -eq $True) {
                $DataSql = "select * from $($Index.SchemaName).$($Index.ObjectName) with (NOLOCK) where %%lockres%% ='$($matches.physloc)'"
                $Data = $server.databases[$dbname].query($DataSql)
                if ($null -eq $data) {
                    Write-Message -Level warning -Message "Could not retrieve the data. It may have been deleted or moved since the wait resource value was generated"
                } else {
                    $output | Add-Member -Type NoteProperty -Name ObjectData -Value $Data
                    $output | Select-Object * -ExpandProperty ObjectData
                }
            } else {
                $output
            }
        }
    }
}