· 6 years ago · Jul 21, 2019, 04:50 PM
1$server = "xxxx"
2$inventoryDB = "xxxx"
3
4#Section 1 START
5#Create the Monitoring schema if it doesn't exist
6$schemaCreationQuery = "
7IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'Monitoring')
8EXEC('CREATE SCHEMA Monitoring AUTHORIZATION dbo')
9"
10Invoke-Sqlcmd -Query $schemaCreationQuery -Database $inventoryDB -ServerInstance $server
11
12#Create the central table where you will store the information gathered from all the instances
13$indexStatusTableCreationQuery = "
14IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'IndexStatus' AND xtype = 'U')
15CREATE TABLE [Monitoring].[IndexStatus](
16 [instance] [varchar](255) NULL,
17 [database] [varchar](255) NULL,
18 [schema] [varchar](255) NULL,
19 [table] [varchar](255) NULL,
20 [index] [varchar](255) NULL,
21 [type] [varchar](255) NULL,
22 [allocation_unit_type] [varchar](255) NULL,
23 [fragmentation] [decimal](10, 2) NULL,
24 [pages] [int] NULL,
25 [writes] [int] NULL,
26 [reads] [int] NULL,
27 [disabled] [tinyint] NULL,
28 [stats_timestamp] [datetime] NULL
29) ON [PRIMARY]
30"
31Invoke-Sqlcmd -Query $indexStatusTableCreationQuery -Database $inventoryDB -ServerInstance $server
32
33#Clean the Monitoring.IndexStatus table
34Invoke-Sqlcmd -Query "TRUNCATE TABLE Monitoring.IndexStatus" -Database $inventoryDB -ServerInstance $server
35#Section 1 END
36
37#Section 2 START
38$spCreationQuery = "
39USE ["
40
41$spCreationQuery += $inventoryDB
42
43$spCreationQuery += "]
44GO
45
46IF EXISTS (
47 SELECT type_desc, type
48 FROM sys.procedures WITH(NOLOCK)
49 WHERE NAME = 'indexes_status'
50 AND SCHEMA_NAME(schema_id) = 'Monitoring'
51 AND type = 'P'
52 )
53DROP PROC Monitoring.indexes_status
54GO
55
56-- =============================================
57-- Author: Alejandro Cobar
58-- Create date: 2/20/2019
59-- Description: Grabs index information status for all databases in an instance
60-- =============================================
61CREATE PROCEDURE [Monitoring].[indexes_status]
62AS
63BEGIN
64 SET NOCOUNT ON;
65
66 DECLARE @query VARCHAR(MAX);
67
68 SET @query = '
69 DECLARE @command VARCHAR(MAX)
70
71 DECLARE @SDBA_IndexFragmentation TABLE(
72 database_name VARCHAR(255),
73 Eschema VARCHAR(255),
74 xTable VARCHAR(255),
75 xIndex VARCHAR(255),
76 xType VARCHAR(255),
77 xAllocUnitType VARCHAR(255),
78 avg_fragmentation_percent DECIMAL(10,2),
79 page_count INT,
80 writes INT,
81 reads INT,
82 disabled TINYINT,
83 xStatsTimestamp DATETIME
84 )
85
86 SELECT @command = '+CHAR(39)+'Use [?]
87 DECLARE @DB_ID INT;
88 SET @DB_ID = DB_ID();
89
90 SELECT
91 db_name(@DB_ID) db_Name,
92 s.name AS ESchema,
93 t.name AS xTable,
94 i.name AS xIndex,
95 i.type_desc AS xType,
96 ips.alloc_unit_type_desc AS xAllocUnitType,
97 CONVERT(DECIMAL(10,2),ips.avg_fragmentation_in_percent) AS fragmentation,
98 ips.page_count,
99 ISNULL(ius.user_updates,0) AS writes,
100 ISNULL(ius.user_seeks + ius.user_scans + ius.user_lookups,0) AS reads,
101 i.is_disabled AS disabled,
102 STATS_DATE(st.object_id, st.stats_id)
103 FROM sys.indexes i
104 JOIN sys.tables t ON i.object_id = t.object_id
105 JOIN sys.schemas s ON s.schema_id = t.schema_id
106 JOIN sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, NULL) ips ON ips.database_id = @DB_ID AND ips.object_id = t.object_id AND ips.index_id = i.index_id
107 LEFT JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = @DB_ID AND ius.object_id = t.object_id AND ius.index_id = i.index_id
108 JOIN sys.stats st ON st.object_id = t.object_id AND st.name = i.name
109 WHERE i.index_id > 0'+CHAR(39)+'
110
111 INSERT INTO @SDBA_IndexFragmentation
112 EXEC sp_MSForEachDB @command
113
114 SELECT SERVERPROPERTY(''SERVERNAME'') AS [instance], database_name AS [database], Eschema AS [schema], xTable AS [table], xIndex AS [index], xType AS [type], xAllocUnitType AS [allocation unit type],avg_fragmentation_percent AS [fragmentation], page_count AS [pages], writes, reads , disabled, xStatsTimestamp AS [stats timestamp]
115 FROM @SDBA_IndexFragmentation
116 WHERE database_name NOT IN (''msdb'',''master'',''model'',''tempdb'');
117 '
118
119 SELECT @query AS tsql;
120END
121"
122Invoke-Sqlcmd -Query $spCreationQuery -Database $inventoryDB -ServerInstance $server
123#Section 2 END
124
125
126#Section 3 START
127#Fetch all the instances from the list you specify
128<#
129 This is an example of the result set that your query must return
130 ###################################################
131 # name # instance #
132 ###################################################
133 # server1.domain.net,45000 # server1 #
134 # server1.domain.net,45001 # server1\MSSQLSERVER1#
135 # server2.domain.net,45000 # server2 #
136 # server3.domain.net,45000 # server3 #
137 # server4.domain.net # server4\MSSQLSERVER2#
138 ###################################################
139#>
140
141#Put in your query that returns the list of instances as described in the example result set above
142$instanceLookupQuery = "SELECT name, instance FROM instances"
143$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $instanceLookupQuery
144#Section 3 END
145
146#Section 4 START
147$indexStatusQuery = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query "EXEC Monitoring.indexes_status" -MaxCharLength 8000
148#Section 4 END
149
150#Section 5 START
151#For each instance, grab the index information for all the databases
152foreach ($instance in $instances){
153 Write-Host "Fetching indexes information for instance" $instance.instance
154
155 #Go grab the indexes information for all the databases in the instance
156 $results = Invoke-Sqlcmd -Query $indexStatusQuery.tsql -ServerInstance $instance.name -ErrorAction Stop
157
158 #Perform the INSERT in the IndexStatus table only if it returned at least 1 row
159 if($results.Length -ne 0){
160 $counter = 0
161
162 #Build the insert statement
163 $insert = "INSERT INTO Monitoring.IndexStatus VALUES"
164 foreach($result in $results){
165 $counter++
166
167 if($result['instance'].ToString().trim() -eq [String]::Empty){$instance = "''"} else{$instance = $result['instance'] }
168 if($result['database'].ToString().trim() -eq [String]::Empty){$database = "''"} else{$database = $result['database'] }
169 if($result['schema'].ToString().trim() -eq [String]::Empty){$schema = "''"} else{$schema = $result['schema']}
170 if($result['table'].ToString().trim() -eq [String]::Empty){$table = "''"} else{$table = $result['table']}
171 if($result['index'].ToString().trim() -eq [String]::Empty){$index = "''"} else{$index = $result['index'] }
172 if($result['type'].ToString().trim() -eq [String]::Empty){$type = "''"} else{$type = $result['type']}
173 if($result['allocation unit type'].ToString().trim() -eq [String]::Empty){$allocationUnitType = "''"} else{$allocationUnitType = $result['allocation unit type']}
174 if($result['fragmentation'].ToString().trim() -eq [String]::Empty){$fragmentation = "''"} else{$fragmentation = $result['fragmentation']}
175 if($result['pages'].ToString().trim() -eq [String]::Empty){$pages = "''"} else{$pages = $result['pages'] }
176 if($result['writes'].ToString().trim() -eq [String]::Empty){$writes = "''"} else{$writes = $result['writes'] }
177 if($result['reads'].ToString().trim() -eq [String]::Empty){$reads = "''"} else{$reads = $result['reads'] }
178 if($result['disabled'].ToString().trim() -eq [String]::Empty){$disabled = "''"} else{$disabled = $result['disabled'] }
179 if($result['stats timestamp'].ToString().trim() -eq [String]::Empty){$statsTimestamp = "''"} else{$statsTimestamp = $result['stats timestamp']}
180
181 $insert += "
182 (
183 '"+$instance+"',
184 '"+$database+"',
185 '"+$schema+"',
186 '"+$table+"',
187 '"+$index+"',
188 '"+$type+"',
189 '"+$allocationUnitType+"',
190 "+$fragmentation+",
191 "+$pages+",
192 "+$writes+",
193 "+$reads+",
194 "+$disabled+",
195 '"+$statsTimestamp+"'
196 ),
197 "
198 $insert = $insert -replace "''",'NULL'
199 $insert = $insert -replace "NULLNULL",'NULL'
200
201 if($counter -eq 1000){
202 Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
203 $counter = 0
204 $insert = "INSERT INTO Monitoring.IndexStatus VALUES"
205 }
206
207 }
208
209 #Store the results in the local Monitoring.IndexStatus table in our Lab Server instance
210 Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
211 }
212}
213#Section 5 END
214
215Write-Host "Done!"