functions/Clear-DbaWaitStatistics.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
function Clear-DbaWaitStatistics {
    <#
 .SYNOPSIS
  Clears wait statistics
 
 .DESCRIPTION
  Reset the aggregated statistics - basically just executes DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR)
 
 .PARAMETER SqlInstance
  Allows you to specify a comma separated list of servers to query.
 
 .PARAMETER SqlCredential
  Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. To use:
  $cred = Get-Credential, this pass this $cred to the param.
 
  Windows Authentication will be used if DestinationSqlCredential is not specified. To connect as a different Windows user, run PowerShell as that user.    
 
 .PARAMETER WhatIf
  If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.
 
 .PARAMETER Confirm
  If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.
 
 .PARAMETER Silent
  Use this switch to disable any kind of verbose messages
 
 .NOTES
  Tags: WaitStatistic
  Website: https://dbatools.io
  Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
  License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
 
 .LINK
  https://dbatools.io/Clear-DbaWaitStatistics
 
 .EXAMPLE
  Clear-DbaWaitStatistics -SqlInstance sql2008, sqlserver2012
  After confirmation, clears wait stats on servers sql2008 and sqlserver2012
  
 .EXAMPLE
  Clear-DbaWaitStatistics -SqlInstance sql2008, sqlserver2012 -Confirm:$false
  Clears wait stats on servers sql2008 and sqlserver2012, without prompting
 #>

    [CmdletBinding(ConfirmImpact = 'High', SupportsShouldProcess)]
    param (
        [parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $True)]
        [Alias("ServerInstance", "SqlServer", "SqlServers")]
        [DbaInstance[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [switch]$Silent
    )
    process {
        foreach ($instance in $SqlInstance) {
            Write-Message -Level Verbose -Message "Attempting to connect to $instance"
            
            try {
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $SqlCredential -MinimumVersion 9
            }
            catch {
                Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $instance -Continue
            }
            
            if ($Pscmdlet.ShouldProcess($instance, "Performing CLEAR of sys.dm_os_wait_stats")) {
                try {
                    $server.Query("DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR);")
                    $status = "Success"
                }
                catch {
                    $status = $_.Exception
                }
                
                [PSCustomObject]@{
                    ComputerName     = $server.NetName
                    InstanceName     = $server.ServiceName
                    SqlInstance         = $server.DomainInstanceName
                    Status             = $status
                }
            }
        }
    }
}