forked from blakedrumm/SCOM-Scripts-and-SQL
-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQL-DW-Performance.ps1
124 lines (111 loc) · 4.5 KB
/
SQL-DW-Performance.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
#This script gathers performance data from SQL DW and outputs as Objects.
$SQLServer = "SQL1"
$db1 = "OperationsManagerDW"
#Disk space in MB
$query1 = @'
select
vManagedEntity.Path
,Perf.vPerfdaily.DateTime
,Perf.vPerfdaily.SampleCount
,vPerformanceRule.ObjectName
,vPerformanceRule.CounterName
,vManagedEntity.Name
,Perf.vPerfdaily.Averagevalue
,Perf.vPerfdaily.MinValue
,Perf.vPerfdaily.MaxValue
,vRule.RuleDefaultName
from Perf.vPerfDaily
join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vPerfDaily.PerformanceRuleInstanceRowid
join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vPerfDaily.ManagedEntityRowId
join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
where Perf.vPerfDaily.Datetime between '2020-01-01' and '2020-07-30'
and vPerformanceRule.ObjectName='LogicalDisk'
and vPerformanceRule.CounterName='Free Megabytes'
and (vRule.RuleDefaultName='Logical Disk Free Megabytes 2000'
or vRule.RuleDefaultName='Logical Disk Free Megabytes 2003'
or vRule.RuleDefaultName='Logical Disk Free Megabytes 2008')
Order by Path, Name
'@
#CPU Utilization
$query2 = @'
select
vManagedEntity.Path
,Perf.vPerfdaily.DateTime
,Perf.vPerfdaily.SampleCount
,vPerformanceRule.ObjectName
,vPerformanceRule.CounterName
,vManagedEntity.Name
,Perf.vPerfdaily.Averagevalue
,Perf.vPerfdaily.MinValue
,Perf.vPerfdaily.MaxValue
,vRule.RuleDefaultName
from Perf.vPerfDaily
join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vPerfDaily.PerformanceRuleInstanceRowid
join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vPerfDaily.ManagedEntityRowId
join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
where Perf.vPerfDaily.Datetime between '2020-01-01' and '2020-07-30'
and vPerformanceRule.ObjectName='Processor'
and vPerformanceRule.CounterName='% Processor Time'
and (vRule.RuleDefaultName='Processor % Processor Time Total 2003'
or vRule.RuleDefaultName='% Processor % Processor TIme Total 2008')
Order by Path,Name
'@
#Free disk space in Percentage
$query3 = @'
select
vManagedEntity.Path
,Perf.vPerfdaily.DateTime
,Perf.vPerfdaily.SampleCount
,vPerformanceRule.ObjectName
,vPerformanceRule.CounterName
,vManagedEntity.Name
,Perf.vPerfdaily.Averagevalue
,Perf.vPerfdaily.MinValue
,Perf.vPerfdaily.MaxValue
,vRule.RuleDefaultName
from Perf.vPerfDaily
join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vPerfDaily.PerformanceRuleInstanceRowid
join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vPerfDaily.ManagedEntityRowId
join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
where Perf.vPerfDaily.Datetime between '2020-01-01' and '2020-07-30'
and vPerformanceRule.ObjectName='LogicalDisk'
and vPerformanceRule.CounterName='% Free Space'
and (vRule.RuleDefaultName='% Logical Disk Free space 2000'
or vRule.RuleDefaultName='% Logical Disk Free Space 2003'
or vRule.RuleDefaultName='% Logical Disk Free Space 2008')
Order by Path, Name
'@
# Memory Percentage Used
$query4 = @'
select
vManagedEntity.Path
,Perf.vPerfdaily.DateTime
,Perf.vPerfdaily.SampleCount
,vPerformanceRule.ObjectName
,vPerformanceRule.CounterName
,vManagedEntity.Name
,Perf.vPerfdaily.Averagevalue
,Perf.vPerfdaily.MinValue
,Perf.vPerfdaily.MaxValue
,vRule.RuleDefaultName
from Perf.vPerfDaily
join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vPerfDaily.PerformanceRuleInstanceRowid
join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vPerfDaily.ManagedEntityRowId
join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
where Perf.vPerfDaily.Datetime between '2020-01-01' and '2020-07-30'
and vPerformanceRule.ObjectName='Memory'
and vPerformanceRule.CounterName='PercentMemoryUsed'
and (vRule.RuleDefaultName='Percent Memory Used')
Order by Path,Name
'@
<#
$query5 = @'
'@
#>
$i = 0
$query = ($query1, $query2, $query3, $query4)
$query | % { $i++; Write-Host "Query : $i" -ForegroundColor Cyan; Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $_ -OutputSqlErrors $true | ft * }