AGmbDU65

1param(
2[string]$servernames
3)
4Add-PSSnapin SqlServerCmdletSnapin110
5Add-PSSnapin SqlServerProviderSnapin110
6cls
7write-host $servernames
8$starttime = Get-Date
9write-host $starttime
10#$servernames=Read-Host "Please enter a SQL Server Name"
11#$servernames = get-content C:dba_tempServer.txt
12foreach($servername in $servernames)
13{
14write-host Starting Server $servername
15$dataSource  =  $servername
16##setup data source
17
18$database = "master"                                 ##Database name
19$TableHeader = "SQL Server Health Check Report"      ##The title of the HTML page
20$path = "S:Health_CheckReports"
21$name = $dataSource -replace "\","_"
22$OutputFile_new = $path + $name + '.html'             ##The file location
23
24$a = "<style>"
25$a = $a + "BODY{background-color:white;}"
26$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
27$a = $a + "TH{border-width: 1px;padding: 1px;border-style: solid;border-color: black;;background-color:thistle}"
28$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
29$a = $a + "</style>"
30
31$colorTagTable = @{
32                    Stopped = ' bgcolor="RED">Stopped<';
33                    Running = ' bgcolor="Green">Running<';
34                    OFFLINE = ' bgcolor="RED">OFFLINE<';
35                    ONLINE  = ' bgcolor="Green">ONLINE<'
36                    "ALL DATABASES ARE" = ' bgcolor="Green">ALL DATABASES ARE<'
37                    "ALL Databases has been" = ' 
38                    bgcolor="Green">ALL Databases has been<';
39                    "backup" = ' bgcolor="Green">backup<';
40                    "in Last 24 Hours" = ' bgcolor="Green">in Last 24 Hours<';
41                    "No Job Failed in Last 24 Hours" = ' 
42                    bgcolor="Green">No Job Failed in Last 24 Hours<';
43                    "Error Log" = ' bgcolor="Green">Error Log<';
44                    "check did not find out anything major" = ' 
45                    bgcolor="Green">check did not find out anything major<';
46                    "but will still advise to please verify manually" = ' 
47                    bgcolor="Green">but will still advise to please verify manually<';
48                    "Server Might Have Memory Issue"  = ' 
49                    bgcolor="Red">Server Might Have Memory Issue<';
50                   } 
51
52##Create a string variable with all our connection details 
53$connectionDetails = "Provider=sqloledb; " + "Data Source=$dataSource; 
54" + "Initial Catalog=$database; " + "Integrated Security=SSPI;"
55
56
57##**************************************
58##Calculating SQL Server Information
59##**************************************
60$sql_server_info = "select @@servername as [SQLNetworkName], 
61CAST( SERVERPROPERTY('MachineName') AS NVARCHAR(128)) AS [MachineName],
62CAST( SERVERPROPERTY('ServerName')AS NVARCHAR(128)) AS [SQLServerName],
63CAST( SERVERPROPERTY('IsClustered') AS NVARCHAR(128)) AS [IsClustered],
64CAST( SERVERPROPERTY('ComputerNamePhysicalNetBIOS')AS NVARCHAR(128)) AS [SQLService_Current_Node],
65serverproperty('edition') as [Edition],
66serverproperty('productlevel') as [Servicepack],
67CAST( SERVERPROPERTY('InstanceName') AS NVARCHAR(128)) AS [InstanceName],
68SERVERPROPERTY('Productversion') AS [ProductVersion],@@version as [Serverversion]"
69
70##Connect to the data source using the connection details and T-SQL command we provided above, 
71##and open the connection
72$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
73$command1 = New-Object System.Data.OleDb.OleDbCommand $sql_server_info,$connection
74$connection.Open()
75
76##Get the results of our command into a DataSet object, and close the connection
77$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command1
78$dataSet1 = New-Object System.Data.DataSet
79$dataAdapter.Fill($dataSet1)
80$connection.Close()
81
82
83##Return all of the rows and pipe it into the ConvertTo-HTML cmdlet, 
84##and then pipe that into our output file
85$frag1 = $dataSet1.Tables | Select-Object -Expand Rows |select -Property SQLNetworkName,
86MachineName,SQLServerName,IsClustered,SQLService_Current_Node,Edition,Servicepack,InstanceName,
87ProductVersion,Serverversion  | ConvertTo-HTML -AS Table -Fragment 
88-PreContent '<h2>SQL Server Info</h2>'|Out-String
89
90
91write-host $frag1
92
93##**************************************
94##SQL Server AGent Information Collection
95##**************************************
96$sqlserverAgent = "
97IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#sql_agent_state'))
98BEGIN
99drop table #sql_agent_state
100    END
101    declare @sql_agent_service varchar(128),@state_sql_agent varchar(20)
102    create table #sql_agent_state(service_name varchar(128) default 'SQLAgent ' ,state varchar(20))
103    insert into #sql_agent_state(state) exec xp_servicecontrol N'querystate',N'SQLServerAGENT'  
104    --select service_name as ServiceName, state as Status from #sql_agent_state
105    select service_name as ServiceName, replace(state,'.','') as Status from #sql_agent_state
106"
107
108$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
109$command2 = New-Object System.Data.OleDb.OleDbCommand $sqlserverAgent,$connection
110$connection.Open()
111
112##Get the results of our command into a DataSet object, and close the connection
113$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command2
114$dataSet2 = New-Object System.Data.DataSet
115$dataAdapter.Fill($dataSet2)
116$connection.Close()
117
118$frag2 = $dataSet2.Tables | Select-Object -Expand Rows| Select -Property ServiceName,
119Status|ConvertTo-HTML -AS Table -Fragment 
120-PreContent '<h2>SQL Server Agent Status</h2>'|Out-String
121
122$colorTagTable.Keys | foreach { $frag2 = $frag2 -replace ">$_<",($colorTagTable.$_) }
123
124write-host $frag2
125
126##**************************************
127##Database states
128##**************************************
129$SQLServerDatabaseState = "
130IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tmp_database'))
131BEGIN
132drop table #tmp_database
133END
134
135declare @count int
136declare @name varchar(128)
137declare @state_desc varchar(128)
138
139select @count = COUNT(*) from sys.databases where state_desc not in ('ONLINE','RESTORING')
140create table #tmp_database (name nvarchar(128),state_desc nvarchar(128))
141if @count > 0
142        begin
143            Declare Cur1 cursor for select name,state_desc from sys.databases 
144            where state_desc not in ('ONLINE','RESTORING')
145        open Cur1
146            FETCH NEXT FROM Cur1 INTO @name,@state_desc
147            WHILE @@FETCH_STATUS = 0
148                BEGIN
149                    insert into #tmp_database values(@name,@state_desc)
150                FETCH NEXT FROM Cur1 INTO @name,@state_desc
151                END
152            CLOSE Cur1
153            DEALLOCATE Cur1
154        end
155else 
156    begin
157        insert into #tmp_database values('ALL DATABASES ARE','ONLINE')
158    end
159
160select name as DBName ,state_desc as DBStatus from #tmp_database
161"
162
163$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
164$command3 = New-Object System.Data.OleDb.OleDbCommand $SQLServerDatabaseState,$connection
165$connection.Open()
166
167##Get the results of our command into a DataSet object, and close the connection
168$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command3
169$dataSet3 = New-Object System.Data.DataSet
170$dataAdapter.Fill($dataSet3)
171$connection.Close()
172
173$frag3 = $dataSet3.Tables | Select-Object -Expand Rows |Select -Property DBName,DBStatus | 
174ConvertTo-HTML -AS Table -Fragment -PreContent '<h2>SQLServer Databases State</h2>'|Out-String
175
176$colorTagTable.Keys | foreach { $frag3 = $frag3 -replace ">$_<",($colorTagTable.$_) }
177
178write-host $frag3 
179
180
181##**************************************
182##SQL Job Status
183##**************************************
184$SQLJob = "
185declare @count int
186select @count = count(1) from msdb.dbo.sysjobs as sj 
187join msdb.dbo.sysjobhistory as sjh on sj.job_id = sjh.job_id 
188where sj.enabled != 0 
189and sjh.sql_message_id > 0 
190and sjh.run_date > CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)
191and sjh.Step_id <= 1
192
193if (@count >= 1)
194begin
195    select distinct sj.name as SQLJobName
196    from msdb.dbo.sysjobs as sj 
197    join msdb.dbo.sysjobhistory as sjh on sj.job_id = sjh.job_id 
198    where sj.enabled != 0 
199    and sjh.sql_message_id > 0 
200    and sjh.run_date > CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)
201    and sjh.Step_id <= 1
202    order by name
203end
204else 
205begin
206    Select 'No Job Failed in Last 24 Hours' as SQLJobName
207end
208
209"
210
211$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
212$command4 = New-Object System.Data.OleDb.OleDbCommand $SQLJob,$connection
213$connection.Open()
214
215##Get the results of our command into a DataSet object, and close the connection
216$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command4
217$dataSet4 = New-Object System.Data.DataSet
218$dataAdapter.Fill($dataSet4)
219$connection.Close()
220
221$frag4 = $dataSet4.Tables | Select-Object -Expand Rows |select -Property SQLJobName | 
222ConvertTo-HTML -AS Table -Fragment -PreContent 
223'<h2>SQLServer SQL Job failed in last 24 Hours</h2>'|Out-String
224
225$colorTagTable.Keys | foreach { $frag4 = $frag4 -replace ">$_<",($colorTagTable.$_) }
226
227write-host $frag4
228
229
230##**************************************
231##Database Backup in Last 24 Hours
232##**************************************
233$SQLServerDatabaseBackup = "
234declare @backupcount int
235select @backupcount = count(1)
236 from sys.databases 
237where state != 6 
238and name not like 'Tempdb%' 
239and name not in 
240(
241select database_name
242from msdb.dbo.backupset as bkupset 
243join msdb.dbo.backupmediafamily as bkupmedf on bkupset.media_set_id = bkupmedf.media_set_id 
244where type in ('D','I')
245and backup_start_date > (CONVERT(datetime,getdate()) - 1)
246)
247
248if (@backupcount >= 1)
249begin
250select name as DBName, State_Desc as DBStatus,'Backup Not happened' as DBComments from sys.databases 
251where state != 6 
252and name not like 'Tempdb%' 
253and name not in 
254(
255select database_name
256from msdb.dbo.backupset as bkupset 
257join msdb.dbo.backupmediafamily as bkupmedf on bkupset.media_set_id = bkupmedf.media_set_id 
258where type in ('D','I')
259and backup_start_date > (CONVERT(datetime,getdate()) - 1)
260)
261order by 1
262end
263else 
264begin
265Select 'ALL Databases has been' as DBName, 
266'backup' as DBStatus ,'in Last 24 Hours' as DBComments
267end
268"
269$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
270$command5 = New-Object System.Data.OleDb.OleDbCommand $SQLServerDatabaseBackup,$connection
271$connection.Open()
272
273##Get the results of our command into a DataSet object, and close the connection
274$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command5
275$dataSet5 = New-Object System.Data.DataSet
276$dataAdapter.Fill($dataSet5)
277$connection.Close()
278
279$frag5 = $dataSet5.Tables | Select-Object -Expand Rows |select -property DBName,
280DBStatus,DBComments | ConvertTo-HTML -AS Table -Fragment 
281-PreContent '<h2>SQLServer Database Backup status in Last 24 Hours</h2>'|Out-String
282
283$colorTagTable.Keys | foreach { $frag5 = $frag5 -replace ">$_<",($colorTagTable.$_) }
284
285write-host $frag5
286
287
288##**************************************
289##SQL Server ErrorLog
290##**************************************
291$SQLServerErrorlog = "
292declare @errorlogcount int
293IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#errorlog'))
294BEGIN
295DROP TABLE #errorlog
296END
297create table #errorlog(date_time datetime,processinfo varchar(123),Comments varchar(max))
298insert into #errorlog exec sp_readerrorlog
299
300select @errorlogcount = count(*) from #errorlog 
301where date_time > (CONVERT(datetime,getdate()) - 0.5)
302and Comments like '%fail%' 
303and Comments like '%error%'
304and processinfo not in ('Server','Logon')
305
306if(@errorlogcount >= 1)
307begin
308select date_time as Date,processinfo as ProcessInfo, Comments from #errorlog 
309where date_time > (CONVERT(datetime,getdate()) - 0.5)
310and Comments like '%fail%' 
311and Comments like '%error%'
312and processinfo not in ('Server','Logon')
313end
314else
315begin
316select 'Error Log' as Date, 'check did not find out anything major' 
317as ProcessInfo, 'but will still advise to please verify manually' as Comments
318end
319"
320
321
322$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
323$command6 = New-Object System.Data.OleDb.OleDbCommand $SQLServerErrorlog,$connection
324$connection.Open()
325
326##Get the results of our command into a DataSet object, and close the connection
327$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command6
328$dataSet6 = New-Object System.Data.DataSet
329$dataAdapter.Fill($dataSet6)
330$connection.Close()
331
332$frag6 = $dataSet6.Tables | Select-Object -Expand Rows|select 
333-Property Date,processinfo,Comments | ConvertTo-HTML -AS Table -Fragment 
334-PreContent '<h2>SQLServer ErroLog Information</h2>'|Out-String
335
336$colorTagTable.Keys | foreach { $frag6 = $frag6 -replace ">$_<",($colorTagTable.$_) }
337
338write-host $frag6
339
340##**************************************
341##CPU information
342##**************************************
343$SQLServerCPUInformation = "declare @query2008r2_cpu nvarchar(max)
344declare @query2012_cpu nvarchar(max)
345set @query2008r2_cpu = 'SELECT cpu_count AS Logical_CPU_Count, 
346hyperthread_ratio AS Hyperthread_Ratio,
347            cpu_count/hyperthread_ratio AS Physical_CPU_Count,
348            physical_memory_in_bytes/1024/1024 AS Physical_Memory_in_MB
349            FROM sys.dm_os_sys_info'
350
351set @query2012_cpu = 'SELECT cpu_count AS Logical_CPU_Count, 
352hyperthread_ratio AS Hyperthread_Ratio,
353            cpu_count/hyperthread_ratio AS Physical_CPU_Count,
354            physical_memory_kb/1024/1024 AS Physical_Memory_in_MB
355            FROM sys.dm_os_sys_info'
356
357
358/*SQL Object Memory Allocation*/
359declare @version nvarchar(128)
360select @version = cast(SERVERPROPERTY('Productversion') as nvarchar(128))
361if (@version like '11%')
362    begin
363        EXECUTE sp_executesql @query2012_cpu    
364    end
365    else
366    begin
367        EXECUTE sp_executesql @query2008r2_cpu
368    end
369"
370
371$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
372$command7 = New-Object System.Data.OleDb.OleDbCommand $SQLServerCPUInformation,$connection
373$connection.Open()
374
375##Get the results of our command into a DataSet object, and close the connection
376$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command7
377$dataSet7 = New-Object System.Data.DataSet
378$dataAdapter.Fill($dataSet7)
379$connection.Close()
380
381$frag7 = $dataSet7.Tables | Select-Object -Expand Rows|select 
382-Property Logical_CPU_Count,Hyperthread_Ratio,Physical_CPU_Count,
383Physical_Memory_in_MB  | ConvertTo-HTML -AS Table -Fragment 
384-PreContent '<h2>CPU Information</h2>'|Out-String
385write-host $frag7
386
387##**************************************
388##SQL Server Memory Infomration
389##**************************************
390
391## 1. Memory Allocated to SQL Server
392
393$SQLServerMemoryAllocated = "SELECT --object_name,
394counter_name as Counter, cntr_value/1024 as MemoryLimitSet_inMB 
395FROM sys.dm_os_performance_counters
396WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');"
397
398$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
399$command8 = New-Object System.Data.OleDb.OleDbCommand $SQLServerMemoryAllocated,$connection
400$connection.Open()
401
402##Get the results of our command into a DataSet object, and close the connection
403$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command8
404$dataSet8 = New-Object System.Data.DataSet
405$dataAdapter.Fill($dataSet8)
406$connection.Close()
407
408$frag8 = $dataSet8.Tables | Select-Object -Expand Rows|select 
409-Property Counter,MemoryLimitSet_inMB | ConvertTo-HTML -AS Table 
410-Fragment -PreContent '<h2>Memory Allocated to SQL Server</h2>'|Out-String
411write-host $frag8
412
413
414#2. Top 10 Memory Consuing Objects
415$SqlServerMemortConsumingobjects = "declare @query2008r2_and_less nvarchar(max)
416declare @query2012_and_more nvarchar(max)
417
418set @query2008r2_and_less = 'select top 10 type as Object,
419SUM(single_pages_kb+multi_pages_kb+virtual_memory_committed_kb+awe_allocated_kb)/1024 as Space_used_inMB
420        from sys.dm_os_memory_clerks 
421        group by type
422        order by 2 desc'
423
424set @query2012_and_more = 'select top 10 type as Object,
425SUM(pages_kb+virtual_memory_committed_kb+awe_allocated_kb)/1024 as Space_used_inMB
426        from sys.dm_os_memory_clerks 
427        group by type
428        order by 2 desc'
429
430/*SQL Object Memory Allocation*/
431declare @version nvarchar(128)
432select @version = cast(SERVERPROPERTY('Productversion') as nvarchar(128))
433
434if (@version like '11%')
435    begin
436        EXECUTE sp_executesql @query2012_and_more    
437    end
438    else
439    begin
440        EXECUTE sp_executesql @query2008r2_and_less
441    end
442"
443$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
444$command9 = New-Object System.Data.OleDb.OleDbCommand $SqlServerMemortConsumingobjects,$connection
445$connection.Open()
446
447##Get the results of our command into a DataSet object, and close the connection
448$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command9
449$dataSet9 = New-Object System.Data.DataSet
450$dataAdapter.Fill($dataSet9)
451$connection.Close()
452
453$frag9 = $dataSet9.Tables | Select-Object -Expand Rows | select 
454-Property Object,Space_used_inMB  | ConvertTo-HTML -AS Table 
455-Fragment -PreContent '<h2>Top 10 Memory Consuming SQL Objects</h2>'|Out-String
456write-host $frag9
457
458
459#3.     
460$sqlservermemorypressuredetection = "declare @totalmemoryused bigint
461declare @bufferpool_allocated bigint
462declare @query2008r2_total nvarchar(max)
463declare @query2012_total nvarchar(max)
464declare @version nvarchar(128)
465
466Set @query2008r2_total  = 'select SUM
467(single_pages_kb+multi_pages_kb+virtual_memory_committed_kb+awe_allocated_kb)/1024
468from sys.dm_os_memory_clerks' 
469
470set @query2012_total = 'select SUM(pages_kb+virtual_memory_committed_kb+awe_allocated_kb)/1024
471from sys.dm_os_memory_clerks'
472
473select @version = cast(SERVERPROPERTY('Productversion') as nvarchar(128))
474--select @version
475if (@version like '11%')
476    begin
477        create table #tmp (value bigint)
478        insert into #tmp Execute (@query2012_total)    
479        select @totalmemoryused = value from #tmp
480        drop table #tmp
481    end
482else
483    begin
484        create table #tmp_1 (value bigint)
485        insert into #tmp_1 Execute (@query2008r2_total)    
486        select @totalmemoryused = value from #tmp_1
487        drop table #tmp_1
488    end
489
490--select @totalmemoryused
491
492select @bufferpool_allocated = cntr_value/1024
493FROM sys.dm_os_performance_counters
494WHERE counter_name IN ('Target Server Memory (KB)')
495
496if (@bufferpool_allocated > @totalmemoryused)
497    begin
498        Select 'Server has no Memory Issue' as Comments
499    end 
500else 
501    begin 
502        select 'Server Might Have Memory Issue' as Comments
503    end
504"
505$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
506$command10 = New-Object System.Data.OleDb.OleDbCommand $sqlservermemorypressuredetection,$connection
507$connection.Open()
508
509##Get the results of our command into a DataSet object, and close the connection
510$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command10
511$dataSet10 = New-Object System.Data.DataSet
512$dataAdapter.Fill($dataSet10)
513$connection.Close()
514
515$frag10 = $dataSet10.Tables | Select-Object -Expand Rows |select 
516-Property Comments | ConvertTo-HTML -AS Table -Fragment 
517-PreContent '<h2>Server Have Memory Pressure or Not</h2>'|Out-String
518
519$colorTagTable.Keys | foreach { $frag10 = $frag10 -replace ">$_<",($colorTagTable.$_) }
520
521write-host $frag10
522
523
524##**************************************
525##Top 10 Long Running Queries
526##**************************************
527$LongRunningQueries = "
528SELECT TOP 10 DB_NAME(qt.dbid) AS DBName,
529o.name AS ObjectName,
530qs.total_worker_time / 1000000 / qs.execution_count As Avg_MultiCore_CPU_time_sec,
531qs.total_worker_time / 1000000 as Total_MultiCore_CPU_time_sec,
532qs.total_elapsed_time / qs.execution_count / 1000000.0 AS Average_Seconds,
533qs.total_elapsed_time / 1000000.0 AS Total_Seconds,
534qs.execution_count as Count,
535qs.last_execution_time as Time,
536SUBSTRING (qt.text,qs.statement_start_offset/2,
537(CASE WHEN qs.statement_end_offset = -1
538THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
539ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query
540--,qt.text
541--,qp.query_plan
542FROM sys.dm_exec_query_stats qs
543CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
544CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
545LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
546where last_execution_time > getdate()-1
547ORDER BY average_seconds DESC
548"
549
550$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
551$command11 = New-Object System.Data.OleDb.OleDbCommand $LongRunningQueries,$connection
552$connection.Open()
553
554##Get the results of our command into a DataSet object, and close the connection
555$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command11
556$dataSet11 = New-Object System.Data.DataSet
557$dataAdapter.Fill($dataSet11)
558$connection.Close()
559
560$frag11 = $dataSet11.Tables | Select-Object -Expand Rows|select 
561-Property DBName,ObjectName,Avg_MultiCore_CPU_time_sec,
562Total_MultiCore_CPU_time_sec,Average_Seconds,Total_Seconds,Count,
563Time,Query| ConvertTo-HTML -AS Table -Fragment 
564-PreContent '<h2>Top 10 Long Running Query</h2>'|Out-String
565write-host $frag11
566
567
568##**************************************
569##Top 10 CPU Consuming Query
570##**************************************
571$CPUConsumingQuery = "
572SELECT TOP 10 DB_NAME(qt.dbid) as DBName,
573    o.name AS ObjectName,
574    qs.total_worker_time / 1000000 / qs.execution_count AS Avg_MultiCore_CPU_time_sec,
575    qs.total_worker_time / 1000000 As Total_MultiCore_CPU_time_sec,
576    qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds,
577    qs.total_elapsed_time / 1000000 As Total_Seconds,
578    (total_logical_reads + total_logical_writes) / qs.execution_count as Average_IO,
579    total_logical_reads + total_logical_writes as Total_IO,    
580    qs.execution_count as Count,
581    qs.last_execution_time as Time,
582    SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
583        (
584            (
585                CASE qs.statement_end_offset
586                    WHEN -1 THEN DATALENGTH(qt.[text])
587                    ELSE qs.statement_end_offset
588                END - qs.statement_start_offset
589            ) / 2
590        ) + 1
591    ) as Query
592    --,qt.text
593    --,qp.query_plan
594FROM sys.dm_exec_query_stats AS qs
595CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
596CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
597LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
598where qs.execution_count > 5    --more than 5 occurrences
599ORDER BY Total_MultiCore_CPU_time_sec DESC
600"
601
602$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
603$command12 = New-Object System.Data.OleDb.OleDbCommand $CPUConsumingQuery,$connection
604$connection.Open()
605
606##Get the results of our command into a DataSet object, and close the connection
607$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command12
608$dataSet12 = New-Object System.Data.DataSet
609$dataAdapter.Fill($dataSet12)
610$connection.Close()
611
612$frag12 = $dataSet12.Tables | Select-Object -Expand Rows |select 
613-Property DBName,ObjectName,Avg_MultiCore_CPU_time_sec,
614Total_MultiCore_CPU_time_sec,Average_Seconds,Total_Seconds,Average_IO,
615Total_IO,Count,Time,Query | ConvertTo-HTML -AS Table -Fragment 
616-PreContent '<h2>Top 10 CPU Consuming Query</h2>'|Out-String
617write-host $frag12
618
619
620##**************************************
621##Top 10 IO Consuming Query
622##**************************************
623$IOConsumingQuery = "
624SELECT TOP 10 DB_NAME(qt.dbid) AS DBName,
625o.name AS ObjectName,
626qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds,
627qs.total_elapsed_time / 1000000 As Total_Seconds,
628(total_logical_reads + total_logical_writes ) / qs.execution_count AS Average_IO,
629(total_logical_reads + total_logical_writes ) AS Total_IO,
630qs.execution_count AS Count,
631last_execution_time As Time,
632SUBSTRING (qt.text,qs.statement_start_offset/2,
633(CASE WHEN qs.statement_end_offset = -1
634THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
635ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query
636--,qt.text
637--,qp.query_plan
638FROM sys.dm_exec_query_stats qs
639CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
640CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
641LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
642where last_execution_time > getdate()-1
643ORDER BY average_IO DESC
644"
645
646$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
647$command13 = New-Object System.Data.OleDb.OleDbCommand $IOConsumingQuery,$connection
648$connection.Open()
649
650##Get the results of our command into a DataSet object, and close the connection
651$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command13
652$dataSet13 = New-Object System.Data.DataSet
653$dataAdapter.Fill($dataSet13)
654$connection.Close()
655
656$frag13 = $dataSet13.Tables | Select-Object -Expand Rows |Select 
657-Property DBName,ObjectName,Average_Seconds,
658Total_Seconds, Average_IO, Total_IO, Count, Time, Query | ConvertTo-HTML 
659-AS Table -Fragment -PreContent '
660<h2>Top 10 IO Consuming Query</h2>'|Out-String
661write-host $frag13
662
663
664##**************************************
665##CPU Pressure
666##**************************************
667$CPUPressure = "
668SELECT CAST(100.0 * SUM(signal_wait_time_ms) / 
669SUM (wait_time_ms) AS NUMERIC(20,2)) AS Pct_Signal_CPU_Waits,
670'if Perc_signal_cpu_waits is > 15%, it means we have CPU pressure' as Comment,
671CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / 
672SUM (wait_time_ms) AS NUMERIC(20,2)) AS Pct_Resource_Waits
673FROM sys.dm_os_wait_stats
674"
675
676$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
677$command14 = New-Object System.Data.OleDb.OleDbCommand $CPUPressure,$connection
678$connection.Open()
679
680##Get the results of our command into a DataSet object, and close the connection
681$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command14
682$dataSet14 = New-Object System.Data.DataSet
683$dataAdapter.Fill($dataSet14)
684$connection.Close()
685
686$frag14 = $dataSet14.Tables | Select-Object -Expand Rows | select -Property Pct_Signal_CPU_Waits,Comment,
687Pct_Resource_Waits | ConvertTo-HTML -AS Table -Fragment 
688-PreContent '<h2>CPU Pressure Detection</h2>'|Out-String
689write-host $frag14
690
691##**************************************
692##Wait Type % Calc
693##**************************************
694
695$WaitTimePercentage = "declare @totalwait_time_ms float
696select @totalwait_time_ms  = sum(wait_time_ms) 
697FROM sys.dm_os_wait_stats
698where wait_time_ms > 0
699
700select top 10 wait_type as WaitEvent,
701    wait_time_ms/1000 as Time_inSec,
702    round(100*(cast(wait_time_ms as float)/@totalwait_time_ms),2) as PctUsed
703FROM sys.dm_os_wait_stats 
704where wait_time_ms > 0
705order by wait_time_ms desc
706"
707$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
708$command15 = New-Object System.Data.OleDb.OleDbCommand $WaitTimePercentage,$connection
709$connection.Open()
710
711##Get the results of our command into a DataSet object, and close the connection
712$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command15
713$dataSet15 = New-Object System.Data.DataSet
714$dataAdapter.Fill($dataSet15)
715$connection.Close()
716
717$frag15 = $dataSet15.Tables | Select-Object -Expand Rows |select 
718-Property WaitEvent,Time_inSec,PctUsed | ConvertTo-HTML -AS Table 
719-Fragment -PreContent '<h2>Wait Type % Allocation on Server</h2>'|Out-String
720write-host $frag15
721
722
723##**************************************
724##Final Code to Combine all fragments
725##**************************************
726
727ConvertTo-HTML -head $a -PostContent $frag1,$frag2,$frag3,$frag4,$frag5,
728$frag6,$frag7,$frag8,$frag9,$frag10,$frag14,$frag15,$frag11,$frag12,$frag13 
729-PreContent "<h1>SQL Server Heatlh Check Report</h1>" | Out-File $OutputFile_new
730
731$smtpServer = "mx.XYZ.com"
732$anonUsername = "anonymous"
733$anonPassword = ConvertTo-SecureString -String "anonymous" -AsPlainText -Force
734$anonCredentials = New-Object System.Management.Automation.PSCredential($anonUsername,$anonPassword)
735
736$attachment = "$OutputFile_new"
737$body= Get-Content $OutputFile_new
738#write-host $body
739
740$subject = "Health Check Report for Server: " + $servername
741
742write-host "Sending email"
743
744Send-MailMessage -to "abc@xyz.com" -from "DoNotReply@xyz.com" 
745-SmtpServer "mx.xyz.com" -subject $subject -credential $anonCredentials 
746-BodyAsHtml "$body" #-Attachments $attachment #$OutputFile_new
747
748
749$Stoptime = Get-Date
750Write-host $Stoptime
751
752}