SQL Server Health Check Script with Powershell
param($servernames
)Add-PSSnapin SqlServerCmdletSnapin110
Add-PSSnapin SqlServerProviderSnapin110
cls
write-host $servernames
$starttime = Get-Date
write-host $starttime
foreach($servername in $servernames)
{write-host Starting Server $servername
$dataSource = $servername
$database = "master"
$TableHeader = "SQL Server Health Check Report"
$path = "S:\Health_Check\Reports\"
$name = $dataSource -replace "\\","_"
$OutputFile_new = $path + $name + '.html' ##The file location
$a = "<style>"
$a = $a + "BODY{background-color:white;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 1px;border-style: solid;border-color: black;;background-color:thistle}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
$a = $a + "</style>"
$colorTagTable = @{
Stopped = ' bgcolor="RED">Stopped<';
Running = ' bgcolor="Green">Running<';
OFFLINE = ' bgcolor="RED">OFFLINE<';
ONLINE = ' bgcolor="Green">ONLINE<'
"ALL DATABASES ARE" = ' bgcolor="Green">ALL DATABASES ARE<'
"ALL Databases has been" = '
bgcolor="Green">ALL Databases has been<';
"backup" = ' bgcolor="Green">backup<';
"in Last 24 Hours" = ' bgcolor="Green">in Last 24 Hours<';
"No Job Failed in Last 24 Hours" = '
bgcolor="Green">No Job Failed in Last 24 Hours<';
"Error Log" = ' bgcolor="Green">Error Log<';
"check did not find out anything major" = '
bgcolor="Green">check did not find out anything major<';
"but will still advise to please verify manually" = '
bgcolor="Green">but will still advise to please verify manually<';
"Server Might Have Memory Issue" = '
bgcolor="Red">Server Might Have Memory Issue<';
}
##Create a string variable with all our connection details
$connectionDetails = "Provider=sqloledb; " + "Data Source=$dataSource;
" + "Initial Catalog=$database; " + "Integrated Security=SSPI;"
##**************************************
##Calculating SQL Server Information
##**************************************
$sql_server_info = "select @@servername as ,
CAST( SERVERPROPERTY('MachineName') AS NVARCHAR(128)) AS ,
CAST( SERVERPROPERTY('ServerName')AS NVARCHAR(128)) AS ,
CAST( SERVERPROPERTY('IsClustered') AS NVARCHAR(128)) AS ,
CAST( SERVERPROPERTY('ComputerNamePhysicalNetBIOS')AS NVARCHAR(128)) AS ,
serverproperty('edition') as ,
serverproperty('productlevel') as ,
CAST( SERVERPROPERTY('InstanceName') AS NVARCHAR(128)) AS ,
SERVERPROPERTY('Productversion') AS ,@@version as "
##Connect to the data source using the connection details and T-SQL command we provided above,
##and open the connection
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command1 = New-Object System.Data.OleDb.OleDbCommand $sql_server_info,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command1
$dataSet1 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet1)
$connection.Close()
##Return all of the rows and pipe it into the ConvertTo-HTML cmdlet,
##and then pipe that into our output file
$frag1 = $dataSet1.Tables | Select-Object -Expand Rows |select -Property SQLNetworkName,
MachineName,SQLServerName,IsClustered,SQLService_Current_Node,Edition,Servicepack,InstanceName,
ProductVersion,Serverversion | ConvertTo-HTML -AS Table -Fragment
-PreContent '<h2>SQL Server Info</h2>'|Out-String
write-host $frag1
##**************************************
##SQL Server AGent Information Collection
##**************************************
$sqlserverAgent = "
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#sql_agent_state'))
BEGIN
drop table
END
declare @sql_agent_service varchar(128),@state_sql_agent varchar(20)
create table
insert into
--select service_name as ServiceName, state as Status from
select service_name as ServiceName, replace(state,'.','') as Status from
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command2 = New-Object System.Data.OleDb.OleDbCommand $sqlserverAgent,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command2
$dataSet2 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet2)
$connection.Close()
$frag2 = $dataSet2.Tables | Select-Object -Expand Rows| Select -Property ServiceName,
Status|ConvertTo-HTML -AS Table -Fragment
-PreContent '<h2>SQL Server Agent Status</h2>'|Out-String
$colorTagTable.Keys | foreach { $frag2 = $frag2 -replace ">$_<",($colorTagTable.$_) }
write-host $frag2
##**************************************
##Database states
##**************************************
$SQLServerDatabaseState = "
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tmp_database'))
BEGIN
drop table
END
declare @count int
declare @name varchar(128)
declare @state_desc varchar(128)
select @count = COUNT(*) from sys.databases where state_desc not in ('ONLINE','RESTORING')
create table
if @count > 0
begin
Declare Cur1 cursor for select name,state_desc from sys.databases
where state_desc not in ('ONLINE','RESTORING')
open Cur1
FETCH NEXT FROM Cur1 INTO @name,@state_desc
WHILE @@FETCH_STATUS = 0
BEGIN
insert into
FETCH NEXT FROM Cur1 INTO @name,@state_desc
END
CLOSE Cur1
DEALLOCATE Cur1
end
else
begin
insert into
end
select name as DBName ,state_desc as DBStatus from
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command3 = New-Object System.Data.OleDb.OleDbCommand $SQLServerDatabaseState,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command3
$dataSet3 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet3)
$connection.Close()
$frag3 = $dataSet3.Tables | Select-Object -Expand Rows |Select -Property DBName,DBStatus |
ConvertTo-HTML -AS Table -Fragment -PreContent '<h2>SQLServer Databases State</h2>'|Out-String
$colorTagTable.Keys | foreach { $frag3 = $frag3 -replace ">$_<",($colorTagTable.$_) }
write-host $frag3
##**************************************
##SQL Job Status
##**************************************
$SQLJob = "
declare @count int
select @count = count(1) from msdb.dbo.sysjobs as sj
join msdb.dbo.sysjobhistory as sjh on sj.job_id = sjh.job_id
where sj.enabled != 0
and sjh.sql_message_id > 0
and sjh.run_date > CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)
and sjh.Step_id <= 1
if (@count >= 1)
begin
select distinct sj.name as SQLJobName
from msdb.dbo.sysjobs as sj
join msdb.dbo.sysjobhistory as sjh on sj.job_id = sjh.job_id
where sj.enabled != 0
and sjh.sql_message_id > 0
and sjh.run_date > CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)
and sjh.Step_id <= 1
order by name
end
else
begin
Select 'No Job Failed in Last 24 Hours' as SQLJobName
end
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command4 = New-Object System.Data.OleDb.OleDbCommand $SQLJob,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command4
$dataSet4 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet4)
$connection.Close()
$frag4 = $dataSet4.Tables | Select-Object -Expand Rows |select -Property SQLJobName |
ConvertTo-HTML -AS Table -Fragment -PreContent
'<h2>SQLServer SQL Job failed in last 24 Hours</h2>'|Out-String
$colorTagTable.Keys | foreach { $frag4 = $frag4 -replace ">$_<",($colorTagTable.$_) }
write-host $frag4
##**************************************
##Database Backup in Last 24 Hours
##**************************************
$SQLServerDatabaseBackup = "
declare @backupcount int
select @backupcount = count(1)
from sys.databases
where state != 6
and name not like 'Tempdb%'
and name not in
(select database_name
from msdb.dbo.backupset as bkupset
join msdb.dbo.backupmediafamily as bkupmedf on bkupset.media_set_id = bkupmedf.media_set_id
where type in ('D','I')
and backup_start_date > (CONVERT(datetime,getdate()) - 1)
)if (@backupcount >= 1)
begin
select name as DBName, State_Desc as DBStatus,'Backup Not happened' as DBComments from sys.databases
where state != 6
and name not like 'Tempdb%'
and name not in
(select database_name
from msdb.dbo.backupset as bkupset
join msdb.dbo.backupmediafamily as bkupmedf on bkupset.media_set_id = bkupmedf.media_set_id
where type in ('D','I')
and backup_start_date > (CONVERT(datetime,getdate()) - 1)
)order by 1
end
else
begin
Select 'ALL Databases has been' as DBName,
'backup' as DBStatus ,'in Last 24 Hours' as DBComments
end
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command5 = New-Object System.Data.OleDb.OleDbCommand $SQLServerDatabaseBackup,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command5
$dataSet5 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet5)
$connection.Close()
$frag5 = $dataSet5.Tables | Select-Object -Expand Rows |select -property DBName,
DBStatus,DBComments | ConvertTo-HTML -AS Table -Fragment
-PreContent '<h2>SQLServer Database Backup status in Last 24 Hours</h2>'|Out-String
$colorTagTable.Keys | foreach { $frag5 = $frag5 -replace ">$_<",($colorTagTable.$_) }
write-host $frag5
##**************************************
##SQL Server ErrorLog
##**************************************
$SQLServerErrorlog = "
declare @errorlogcount int
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#errorlog'))
BEGIN
DROP TABLE
END
create table
insert into
select @errorlogcount = count(*) from
where date_time > (CONVERT(datetime,getdate()) - 0.5)
and Comments like '%fail%'
and Comments like '%error%'
and processinfo not in ('Server','Logon')
if(@errorlogcount >= 1)
begin
select date_time as Date,processinfo as ProcessInfo, Comments from
where date_time > (CONVERT(datetime,getdate()) - 0.5)
and Comments like '%fail%'
and Comments like '%error%'
and processinfo not in ('Server','Logon')
end
else
begin
select 'Error Log' as Date, 'check did not find out anything major'
as ProcessInfo, 'but will still advise to please verify manually' as Comments
end
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command6 = New-Object System.Data.OleDb.OleDbCommand $SQLServerErrorlog,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command6
$dataSet6 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet6)
$connection.Close()
$frag6 = $dataSet6.Tables | Select-Object -Expand Rows|select
-Property Date,processinfo,Comments | ConvertTo-HTML -AS Table -Fragment
-PreContent '<h2>SQLServer ErroLog Information</h2>'|Out-String
$colorTagTable.Keys | foreach { $frag6 = $frag6 -replace ">$_<",($colorTagTable.$_) }
write-host $frag6
##**************************************
##CPU information
##**************************************
$SQLServerCPUInformation = "declare @query2008r2_cpu nvarchar(max)
declare @query2012_cpu nvarchar(max)
set @query2008r2_cpu = 'SELECT cpu_count AS Logical_CPU_Count,
hyperthread_ratio AS Hyperthread_Ratio,
cpu_count/hyperthread_ratio AS Physical_CPU_Count,
physical_memory_in_bytes/1024/1024 AS Physical_Memory_in_MB
FROM sys.dm_os_sys_info'
set @query2012_cpu = 'SELECT cpu_count AS Logical_CPU_Count,
hyperthread_ratio AS Hyperthread_Ratio,
cpu_count/hyperthread_ratio AS Physical_CPU_Count,
physical_memory_kb/1024/1024 AS Physical_Memory_in_MB
FROM sys.dm_os_sys_info'
/*SQL Object Memory Allocation*/
declare @version nvarchar(128)
select @version = cast(SERVERPROPERTY('Productversion') as nvarchar(128))
if (@version like '11%')
begin
EXECUTE sp_executesql @query2012_cpu
end
else
begin
EXECUTE sp_executesql @query2008r2_cpu
end
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command7 = New-Object System.Data.OleDb.OleDbCommand $SQLServerCPUInformation,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command7
$dataSet7 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet7)
$connection.Close()
$frag7 = $dataSet7.Tables | Select-Object -Expand Rows|select
-Property Logical_CPU_Count,Hyperthread_Ratio,Physical_CPU_Count,
Physical_Memory_in_MB | ConvertTo-HTML -AS Table -Fragment
-PreContent '<h2>CPU Information</h2>'|Out-String
write-host $frag7
##**************************************
##SQL Server Memory Infomration
##**************************************
## 1. Memory Allocated to SQL Server
$SQLServerMemoryAllocated = "SELECT --object_name,
counter_name as Counter, cntr_value/1024 as MemoryLimitSet_inMB
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');"
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command8 = New-Object System.Data.OleDb.OleDbCommand $SQLServerMemoryAllocated,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command8
$dataSet8 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet8)
$connection.Close()
$frag8 = $dataSet8.Tables | Select-Object -Expand Rows|select
-Property Counter,MemoryLimitSet_inMB | ConvertTo-HTML -AS Table
-Fragment -PreContent '<h2>Memory Allocated to SQL Server</h2>'|Out-String
write-host $frag8
#2. Top 10 Memory Consuing Objects
$SqlServerMemortConsumingobjects = "declare @query2008r2_and_less nvarchar(max)
declare @query2012_and_more nvarchar(max)
set @query2008r2_and_less = 'select top 10 type as Object,
SUM(single_pages_kb+multi_pages_kb+virtual_memory_committed_kb+awe_allocated_kb)/1024 as Space_used_inMB
from sys.dm_os_memory_clerks
group by type
order by 2 desc'
set @query2012_and_more = 'select top 10 type as Object,
SUM(pages_kb+virtual_memory_committed_kb+awe_allocated_kb)/1024 as Space_used_inMB
from sys.dm_os_memory_clerks
group by type
order by 2 desc'
/*SQL Object Memory Allocation*/
declare @version nvarchar(128)
select @version = cast(SERVERPROPERTY('Productversion') as nvarchar(128))
if (@version like '11%')
begin
EXECUTE sp_executesql @query2012_and_more
end
else
begin
EXECUTE sp_executesql @query2008r2_and_less
end
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command9 = New-Object System.Data.OleDb.OleDbCommand $SqlServerMemortConsumingobjects,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command9
$dataSet9 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet9)
$connection.Close()
$frag9 = $dataSet9.Tables | Select-Object -Expand Rows | select
-Property Object,Space_used_inMB | ConvertTo-HTML -AS Table
-Fragment -PreContent '<h2>Top 10 Memory Consuming SQL Objects</h2>'|Out-String
write-host $frag9
#3.
$sqlservermemorypressuredetection = "declare @totalmemoryused bigint
declare @bufferpool_allocated bigint
declare @query2008r2_total nvarchar(max)
declare @query2012_total nvarchar(max)
declare @version nvarchar(128)
Set @query2008r2_total = 'select SUM
(single_pages_kb+multi_pages_kb+virtual_memory_committed_kb+awe_allocated_kb)/1024
from sys.dm_os_memory_clerks'
set @query2012_total = 'select SUM(pages_kb+virtual_memory_committed_kb+awe_allocated_kb)/1024
from sys.dm_os_memory_clerks'
select @version = cast(SERVERPROPERTY('Productversion') as nvarchar(128))
--select @version
if (@version like '11%')
begin
create table
insert into
select @totalmemoryused = value from
drop table
end
else
begin
create table
insert into
select @totalmemoryused = value from
drop table
end
--select @totalmemoryused
select @bufferpool_allocated = cntr_value/1024
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Target Server Memory (KB)')
if (@bufferpool_allocated > @totalmemoryused)
begin
Select 'Server has no Memory Issue' as Comments
end
else
begin
select 'Server Might Have Memory Issue' as Comments
end
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command10 = New-Object System.Data.OleDb.OleDbCommand $sqlservermemorypressuredetection,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command10
$dataSet10 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet10)
$connection.Close()
$frag10 = $dataSet10.Tables | Select-Object -Expand Rows |select
-Property Comments | ConvertTo-HTML -AS Table -Fragment
-PreContent '<h2>Server Have Memory Pressure or Not</h2>'|Out-String
$colorTagTable.Keys | foreach { $frag10 = $frag10 -replace ">$_<",($colorTagTable.$_) }
write-host $frag10
##**************************************
##Top 10 Long Running Queries
##**************************************
$LongRunningQueries = "
SELECT TOP 10 DB_NAME(qt.dbid) AS DBName,
o.name AS ObjectName,
qs.total_worker_time / 1000000 / qs.execution_count As Avg_MultiCore_CPU_time_sec,
qs.total_worker_time / 1000000 as Total_MultiCore_CPU_time_sec,
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS Average_Seconds,
qs.total_elapsed_time / 1000000.0 AS Total_Seconds,
qs.execution_count as Count,
qs.last_execution_time as Time,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query
--,qt.text
--,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where last_execution_time > getdate()-1
ORDER BY average_seconds DESC
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command11 = New-Object System.Data.OleDb.OleDbCommand $LongRunningQueries,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command11
$dataSet11 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet11)
$connection.Close()
$frag11 = $dataSet11.Tables | Select-Object -Expand Rows|select
-Property DBName,ObjectName,Avg_MultiCore_CPU_time_sec,
Total_MultiCore_CPU_time_sec,Average_Seconds,Total_Seconds,Count,
Time,Query| ConvertTo-HTML -AS Table -Fragment
-PreContent '<h2>Top 10 Long Running Query</h2>'|Out-String
write-host $frag11
##**************************************
##Top 10 CPU Consuming Query
##**************************************
$CPUConsumingQuery = "
SELECT TOP 10 DB_NAME(qt.dbid) as DBName,
o.name AS ObjectName,
qs.total_worker_time / 1000000 / qs.execution_count AS Avg_MultiCore_CPU_time_sec,
qs.total_worker_time / 1000000 As Total_MultiCore_CPU_time_sec,
qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds,
qs.total_elapsed_time / 1000000 As Total_Seconds,
(total_logical_reads + total_logical_writes) / qs.execution_count as Average_IO,
total_logical_reads + total_logical_writes as Total_IO,
qs.execution_count as Count,
qs.last_execution_time as Time,
SUBSTRING(qt., (qs.statement_start_offset / 2) + 1,
(
(
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.)
ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2
) + 1
) as Query
--,qt.text
--,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qs.execution_count > 5 --more than 5 occurrences
ORDER BY Total_MultiCore_CPU_time_sec DESC
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command12 = New-Object System.Data.OleDb.OleDbCommand $CPUConsumingQuery,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command12
$dataSet12 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet12)
$connection.Close()
$frag12 = $dataSet12.Tables | Select-Object -Expand Rows |select
-Property DBName,ObjectName,Avg_MultiCore_CPU_time_sec,
Total_MultiCore_CPU_time_sec,Average_Seconds,Total_Seconds,Average_IO,
Total_IO,Count,Time,Query | ConvertTo-HTML -AS Table -Fragment
-PreContent '<h2>Top 10 CPU Consuming Query</h2>'|Out-String
write-host $frag12
##**************************************
##Top 10 IO Consuming Query
##**************************************
$IOConsumingQuery = "
SELECT TOP 10 DB_NAME(qt.dbid) AS DBName,
o.name AS ObjectName,
qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds,
qs.total_elapsed_time / 1000000 As Total_Seconds,
(total_logical_reads + total_logical_writes ) / qs.execution_count AS Average_IO,
(total_logical_reads + total_logical_writes ) AS Total_IO,
qs.execution_count AS Count,
last_execution_time As Time,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query
--,qt.text
--,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where last_execution_time > getdate()-1
ORDER BY average_IO DESC
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command13 = New-Object System.Data.OleDb.OleDbCommand $IOConsumingQuery,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command13
$dataSet13 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet13)
$connection.Close()
$frag13 = $dataSet13.Tables | Select-Object -Expand Rows |Select
-Property DBName,ObjectName,Average_Seconds,
Total_Seconds, Average_IO, Total_IO, Count, Time, Query | ConvertTo-HTML
-AS Table -Fragment -PreContent '
<h2>Top 10 IO Consuming Query</h2>'|Out-String
write-host $frag13
##**************************************
##CPU Pressure
##**************************************
$CPUPressure = "
SELECT CAST(100.0 * SUM(signal_wait_time_ms) /
SUM (wait_time_ms) AS NUMERIC(20,2)) AS Pct_Signal_CPU_Waits,
'if Perc_signal_cpu_waits is > 15%, it means we have CPU pressure' as Comment,
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) /
SUM (wait_time_ms) AS NUMERIC(20,2)) AS Pct_Resource_Waits
FROM sys.dm_os_wait_stats
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command14 = New-Object System.Data.OleDb.OleDbCommand $CPUPressure,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command14
$dataSet14 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet14)
$connection.Close()
$frag14 = $dataSet14.Tables | Select-Object -Expand Rows | select -Property Pct_Signal_CPU_Waits,Comment,
Pct_Resource_Waits | ConvertTo-HTML -AS Table -Fragment
-PreContent '<h2>CPU Pressure Detection</h2>'|Out-String
write-host $frag14
##**************************************
##Wait Type % Calc
##**************************************
$WaitTimePercentage = "declare @totalwait_time_ms float
select @totalwait_time_ms = sum(wait_time_ms)
FROM sys.dm_os_wait_stats
where wait_time_ms > 0
select top 10 wait_type as WaitEvent,
wait_time_ms/1000 as Time_inSec,
round(100*(cast(wait_time_ms as float)/@totalwait_time_ms),2) as PctUsed
FROM sys.dm_os_wait_stats
where wait_time_ms > 0
order by wait_time_ms desc
"$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
$command15 = New-Object System.Data.OleDb.OleDbCommand $WaitTimePercentage,$connection
$connection.Open()
##Get the results of our command into a DataSet object, and close the connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command15
$dataSet15 = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet15)
$connection.Close()
$frag15 = $dataSet15.Tables | Select-Object -Expand Rows |select
-Property WaitEvent,Time_inSec,PctUsed | ConvertTo-HTML -AS Table
-Fragment -PreContent '<h2>Wait Type % Allocation on Server</h2>'|Out-String
write-host $frag15
##**************************************
##Final Code to Combine all fragments
##**************************************
ConvertTo-HTML -head $a -PostContent $frag1,$frag2,$frag3,$frag4,$frag5,
$frag6,$frag7,$frag8,$frag9,$frag10,$frag14,$frag15,$frag11,$frag12,$frag13
-PreContent "<h1>SQL Server Heatlh Check Report</h1>" | Out-File $OutputFile_new
$smtpServer = "mx.XYZ.com"
$anonUsername = "anonymous"
$anonPassword = ConvertTo-SecureString -String "anonymous" -AsPlainText -Force
$anonCredentials = New-Object System.Management.Automation.PSCredential($anonUsername,$anonPassword)
$attachment = "$OutputFile_new"
$body= Get-Content $OutputFile_new
#write-host $body
$subject = "Health Check Report for Server: " + $servername
write-host "Sending email"
Send-MailMessage -to "abc@xyz.com" -from "DoNotReply@xyz.com"
-SmtpServer "mx.xyz.com" -subject $subject -credential $anonCredentials
-BodyAsHtml "$body" #-Attachments $attachment #$OutputFile_new
$Stoptime = Get-Date
Write-host $Stoptime
}remove-variable starttime
remove-variable servernames
remove-variable servername
remove-variable dataSource
remove-variable database
remove-variable TableHeader
remove-variable path
remove-variable name
remove-variable OutputFile_new
remove-variable a
remove-variable colorTagTable
remove-variable connectionDetails
remove-variable connection
remove-variable dataAdapter
remove-variable sql_server_info
remove-variable sqlserverAgent
remove-variable SQLServerDatabaseState
remove-variable SQLJob
remove-variable SQLServerDatabaseBackup
remove-variable SQLServerErrorlog
remove-variable SQLServerCPUInformation
remove-variable SQLServerMemoryAllocated
remove-variable SqlServerMemortConsumingobjects
remove-variable sqlservermemorypressuredetection
remove-variable LongRunningQueries
remove-variable CPUConsumingQuery
remove-variable IOConsumingQuery
remove-variable CPUPressure
remove-variable WaitTimePercentage
remove-variable smtpServer
remove-variable anonUsername
remove-variable anonPassword
remove-variable anonCredentials
remove-variable attachment
remove-variable body
remove-variable subject
remove-variable Stoptime
remove-variable dataSet1
remove-variable dataSet2
remove-variable dataSet3
remove-variable dataSet4
remove-variable dataSet5
remove-variable dataSet6
remove-variable dataSet7
remove-variable dataSet8
remove-variable dataSet9
remove-variable dataSet10
remove-variable dataSet11
remove-variable dataSet12
remove-variable dataSet13
remove-variable dataSet14
remove-variable dataSet15
remove-variable command1
remove-variable command2
remove-variable command3
remove-variable command4
remove-variable command5
remove-variable command6
remove-variable command7
remove-variable command8
remove-variable command9
remove-variable command10
remove-variable command11
remove-variable command12
remove-variable command13
remove-variable command14
remove-variable command15
remove-variable frag1
remove-variable frag2
remove-variable frag3
remove-variable frag4
remove-variable frag5
remove-variable frag6
remove-variable frag7
remove-variable frag8
remove-variable frag9
remove-variable frag10
remove-variable frag11
remove-variable frag12
remove-variable frag13
remove-variable frag14
remove-variable frag15
##Powershell Code Ends##
页:
[1]