Tag: Report

SQL Server Performance Report

SQL Server Performance Report

A lot of times we as a DBA have trouble to find out the root cause of a performance issue. We had to go through many blogs out there to get appropriate queries for the issue at hand and run those queries to do proper analysis of the issue and this is a tedious task.

Hence, I have made an effort to make that tedious task very easy by consolidating all the required information related to SQL Server Performance in one single HTML report.

Some of the information below is deleted due to privacy reasons. Please be informed that

the script works only for SQL Server version 2005 and above and Powershell version PS 2 and above.

You just have to save the below code in PS1 format and then in powershell go to the location where the file is saved and execute it by giving the server name as parameter.

C:\temp\script1.ps1 server1

 

[CmdletBinding()]

Param (

[parameter(ValueFromPipeline=$True)]
 [ValidatePattern('^[a-zA-Z0-9]+$')]
 [string[]]$ComputerName

)

Begin
 {
 #Initialize
 Write-Verbose "Initializing"

}
 Process
 {
 $reportime = Get-Date

$headstyle2 = "

Server Info: $ComputerName

Generated: $reportime

"

$headstyle = "

Server Info: $ComputerName

Generated: $reportime

"

$style = "

Server Info: $ComputerName

Generated: $reportime

"

#---------------------------------------------------------------------
 # Process each ComputerName
 #---------------------------------------------------------------------

if (!($PSCmdlet.MyInvocation.BoundParameters[“Verbose”].IsPresent))
 {
 Write-Host "Processing $ComputerName"
 }

Write-Verbose "=====> Processing $ComputerName <====="

$htmlreport = @()
 $htmlbody = @()
 $htmlfile = "$($ComputerName).html"
 $spacer = "
 "

#---------------------------------------------------------------------
 # Do 10 pings and calculate the fastest response time
 # Not using the response time in the report yet so it might be
 # removed later.
 #---------------------------------------------------------------------

try
 {
 $bestping = (Test-Connection -ComputerName $ComputerName -Count 10 -ErrorAction STOP | Sort ResponseTime)[0].ResponseTime
 }
 catch
 {
 Write-Warning $_.Exception.Message
 $bestping = "Unable to connect"
 }

if ($bestping -eq "Unable to connect")
 {
 if (!($PSCmdlet.MyInvocation.BoundParameters[“Verbose”].IsPresent))
 {
 Write-Host "Unable to connect to $ComputerName"
 }

"Unable to connect to $ComputerName"
 }
 else
 {

#---------------------------------------------------------------------
 # Collect computer system information and convert to HTML fragment
 #---------------------------------------------------------------------

Write-Verbose "Collecting computer system information"

$subhead = "

Computer System Information

"
 $htmlbody += $subhead

try
 {
 $csinfo = Get-WmiObject Win32_ComputerSystem -ComputerName $ComputerName -ErrorAction STOP |
 Select-Object Name,Manufacturer,Model,
 @{Name='Physical Processors';Expression={$_.NumberOfProcessors}},
 @{Name='Logical Processors';Expression={$_.NumberOfLogicalProcessors}},
 @{Name='Total Physical Memory (Gb)';Expression={
 $tpm = $_.TotalPhysicalMemory/1GB;
 "{0:F0}" -f $tpm
 }},
 DnsHostName,Domain

$htmlbody += $csinfo | ConvertTo-Html -Fragment
 $htmlbody += $spacer

}
 catch
 {
 Write-Warning $_.Exception.Message
 $htmlbody += "

An error was encountered. $($_.Exception.Message)

"
 $htmlbody += $spacer
 }

#---------------------------------------------------------------------
 # Collect operating system information and convert to HTML fragment
 #---------------------------------------------------------------------

Write-Verbose "Collecting operating system information"

$subhead = "

Operating System Information

"
 $htmlbody += $subhead

try
 {
 $osinfo = Get-WmiObject Win32_OperatingSystem -ComputerName $ComputerName -ErrorAction STOP |
 Select-Object @{Name='Operating System';Expression={$_.Caption}},
 @{Name='Architecture';Expression={$_.OSArchitecture}},
 Version,Organization,
 @{Name='Install Date';Expression={
 $installdate = [datetime]::ParseExact($_.InstallDate.SubString(0,8),"yyyyMMdd",$null);
 $installdate.ToShortDateString()
 }},
 WindowsDirectory

$htmlbody += $osinfo | ConvertTo-Html -Fragment
 $htmlbody += $spacer
 }
 catch
 {
 Write-Warning $_.Exception.Message
 $htmlbody += "

An error was encountered. $($_.Exception.Message)

"
 $htmlbody += $spacer
 }

#---------------------------------------------------------------------
 # Collect pagefile information and convert to HTML fragment
 #---------------------------------------------------------------------

$subhead = "

PageFile Information

"
 $htmlbody += $subhead

Write-Verbose "Collecting pagefile information"

try
 {
 $pagefileinfo = Get-WmiObject Win32_PageFileUsage -ComputerName $ComputerName -ErrorAction STOP |
 Select-Object @{Name='Pagefile Name';Expression={$_.Name}},
 @{Name='Allocated Size (Mb)';Expression={$_.AllocatedBaseSize}}

$htmlbody += $pagefileinfo | ConvertTo-Html -Fragment
 $htmlbody += $spacer
 }
 catch
 {
 Write-Warning $_.Exception.Message
 $htmlbody += "

An error was encountered. $($_.Exception.Message)

"
 $htmlbody += $spacer
 }

#---------------------------------------------------------------------
 # Collect logical disk information and convert to HTML fragment
 #---------------------------------------------------------------------

$subhead = "

Logical Disk Information

"
 $htmlbody += $subhead

Write-Verbose "Collecting logical disk information"

try
 {
 $diskinfo = Get-WmiObject Win32_LogicalDisk -ComputerName $ComputerName -ErrorAction STOP |
 Select-Object DeviceID,FileSystem,VolumeName,
 @{Expression={$_.Size /1Gb -as [int]};Label="Total Size (GB)"},
 @{Expression={$_.Freespace / 1Gb -as [int]};Label="Free Space (GB)"}

$htmlbody += $diskinfo | ConvertTo-Html -Fragment
 $htmlbody += $spacer
 }
 catch
 {
 Write-Warning $_.Exception.Message
 $htmlbody += "

An error was encountered. $($_.Exception.Message)

"
 $htmlbody += $spacer
 }

#---------------------------------------------------------------------
 # Collect volume information and convert to HTML fragment
 #---------------------------------------------------------------------

$subhead = "

Volume Information

"
 $htmlbody += $subhead

Write-Verbose "Collecting volume information"

try
 {
 $volinfo = Get-WmiObject Win32_Volume -ComputerName $ComputerName -ErrorAction STOP |
 Select-Object Label,Name,DeviceID,SystemVolume,
 @{Expression={$_.Capacity /1Gb -as [int]};Label="Total Size (GB)"},
 @{Expression={$_.Freespace / 1Gb -as [int]};Label="Free Space (GB)"}

$htmlbody += $volinfo | ConvertTo-Html -Fragment
 $htmlbody += $spacer
 }
 catch
 {
 Write-Warning $_.Exception.Message
 $htmlbody += "

An error was encountered. $($_.Exception.Message)

"
 $htmlbody += $spacer
 }

#---------------------------------------------------------------------
 # Collect network interface information and convert to HTML fragment
 #---------------------------------------------------------------------

$subhead = "

Network Interface Information

"
 $htmlbody += $subhead

Write-Verbose "Collecting network interface information"

try
 {
 $nics = @()
 $nicinfo = @(Get-WmiObject Win32_NetworkAdapter -ComputerName $ComputerName -ErrorAction STOP | Where {$_.PhysicalAdapter} |
 Select-Object Name,AdapterType,MACAddress,
 @{Name='ConnectionName';Expression={$_.NetConnectionID}},
 @{Name='Enabled';Expression={$_.NetEnabled}},
 @{Name='Speed';Expression={$_.Speed/1000000}})

$nwinfo = Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $ComputerName -ErrorAction STOP |
 Select-Object Description, DHCPServer,
 @{Name='IpAddress';Expression={$_.IpAddress -join '; '}},
 @{Name='IpSubnet';Expression={$_.IpSubnet -join '; '}},
 @{Name='DefaultIPgateway';Expression={$_.DefaultIPgateway -join '; '}},
 @{Name='DNSServerSearchOrder';Expression={$_.DNSServerSearchOrder -join '; '}}

foreach ($nic in $nicinfo)
 {
 $nicObject = New-Object PSObject
 $nicObject | Add-Member NoteProperty -Name "Connection Name" -Value $nic.connectionname
 $nicObject | Add-Member NoteProperty -Name "Adapter Name" -Value $nic.Name
 $nicObject | Add-Member NoteProperty -Name "Type" -Value $nic.AdapterType
 $nicObject | Add-Member NoteProperty -Name "MAC" -Value $nic.MACAddress
 $nicObject | Add-Member NoteProperty -Name "Enabled" -Value $nic.Enabled
 $nicObject | Add-Member NoteProperty -Name "Speed (Mbps)" -Value $nic.Speed

$ipaddress = ($nwinfo | Where {$_.Description -eq $nic.Name}).IpAddress
 $nicObject | Add-Member NoteProperty -Name "IPAddress" -Value $ipaddress

$nics += $nicObject
 }

$htmlbody += $nics | ConvertTo-Html -Fragment
 $htmlbody += $spacer
 }
 catch
 {
 Write-Warning $_.Exception.Message
 $htmlbody += "

An error was encountered. $($_.Exception.Message)

"
 $htmlbody += $spacer
 }

##################################################### MEMORY #######################################################################

$subhead = "

Memory Information

"
 $htmlbody += $subhead

$mQuery = "if @@version not like '%Microsoft SQL Server 2005%'
 begin
 select total_physical_memory_kb/1024 as 'TotalPhysical_RAM_MB'
 , available_physical_memory_kb/1024 as 'AvailablePhysical_RAM_MB'
 , total_page_file_kb/1024 as 'TotalPagefile_Size_MB'
 , available_page_file_kb/1024 as 'Available_Pagefile_MB'
 , system_memory_state_desc
 from sys.dm_os_sys_memory WITH (nolock) option (RECOMPILE)
 end"

$mConnection = New-Object System.Data.SqlClient.SqlConnection
 $mConnection.ConnectionString = "Data Source=$($ComputerName);Initial Catalog='master';Integrated Security = True";
 $mAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($mQuery, $mconnection)
 $mtable = New-Object System.Data.Dataset

$mRecs = $mAdapter.Fill($mtable)

$htmlbody += $mtable.Tables[0] | select TotalPhysical_RAM_MB, AvailablePhysical_RAM_MB, TotalPagefile_Size_MB, Available_Pagefile_MB| ConvertTo-Html -Fragment
 $htmlbody +=$spacer

####################################################### SQL Server Details ########################################################

$subhead = "

SQL Server Instance Information

"
 $htmlbody += $subhead

$pQuery = "declare @SP table -- table only needed so output can be better formatted
 ( [ServerProperty] varchar(50)
 , [Value] varchar(80)
 )

insert @SP ([ServerProperty], Value)
 -- Get selected server properties (SQL Server 2008)
 select 'Machine Name', convert(varchar(80), serverproperty('MachineName'))
 union all
 select 'Server Name', convert(varchar(80), serverproperty('ServerName'))
 union all
 select 'Physical Net BIOS Name', convert(varchar(80), serverproperty('ComputerNamePhysicalNetBIOS'))
 union all
 select 'Instance Name', isnull(convert(varchar(80), serverproperty('InstanceName')), '')
 union all
 select 'Is Clustered', case convert(varchar(80), SERVERPROPERTY('IsClustered')) when '0' then 'Not Clustered'
 when '1' then 'Clustered'
 end
 union all
 select 'SQL Version', case SUBSTRING(convert(char(20), serverproperty('ProductVersion')), 1, 4)
 when '10.5' then 'SQL Server 2008 R2'
 when '10.0' then 'SQL Server 2008'
 when '11.0' then 'SQL Server 2012'
 when '9.00' then 'SQL Server 2005'
 else @@version
 end
 union all
 select 'SQL Edition', convert(varchar(80), serverproperty('Edition'))
 union all
 select 'SQL SP Level', convert(varchar(80), SERVERPROPERTY('ProductLevel'))
 union all
 select 'SQL Version Number', convert(varchar(80), serverproperty('ProductVersion'))
 union all
 select 'SQL Collation', convert(varchar(80), serverproperty('Collation'))
 union all
 select 'SQL Feature: Full Text Indexing', convert(varchar(80), case SERVERPROPERTY('IsFullTextInstalled') when 0 then 'No - Full Text Indexing NOT installed' else 'Yes - Full Text Indexing is installed' end )
 union all
 select 'SQL Authentication:', convert(varchar(80), case serverproperty('IsIntegratedSecurityonly') when 0 then 'Mixed Security' else 'Windows Authenticaion Only' end )
 union all
 select 'SQL Server Start Date', convert(char(17), create_date, 113) + ' (' + convert(varchar(15), datediff(dd, create_date, getdate())) + ' days)' from sys.databases where database_id = 2 -- Tempdb
 union all
 select 'SQL Server Install Date', convert(char(17), createdate, 113) from sys.syslogins where [sid] = 0x010100000000000512000000;

select [ServerProperty] as 'SQLServerProperty'
 , Value
 from @SP"

$pConnection = New-Object System.Data.SqlClient.SqlConnection
 $pConnection.ConnectionString = "Data Source=$($ComputerName);Initial Catalog='master';Integrated Security = True";
 $pAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($pQuery, $pconnection)
 $ptable = New-Object System.Data.Dataset

$pRecs = $pAdapter.Fill($ptable)

$htmlbody += $ptable.Tables[0] | Select SQLServerProperty, Value | ConvertTo-Html -Fragment
 $htmlbody +=$spacer

########################################################### SQL Server Database Information #############################################################
 $subhead = "

SQL Server Database Information

"
 $htmlbody += $subhead

$dQuery = "
 -- SELECT ''+name+'' as DatabaseName FROM sys.databases
 SELECT ''+name+'' as DatabaseName, database_id,create_date,collation_name,state_desc,recovery_model_desc FROM sys.databases "

$dConnection = New-Object System.Data.SqlClient.SqlConnection
 $dConnection.ConnectionString = "Data Source=$($ComputerName);Initial Catalog='master';Integrated Security = True";
 $dAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($dQuery, $dconnection)
 $dtable = New-Object System.Data.Dataset

$dRecs = $dAdapter.Fill($dtable)

$test = $dtable.Tables[0] | select DatabaseName, database_id,create_date,collation_name,state_desc,recovery_model_desc | ConvertTo-HTML -Fragment

Add-Type -AssemblyName System.web
 $htmlbody += [system.web.httputility]::htmldecode($test)
 $htmlbody +=$spacer

###########################################SQL Server Trace Information####################################

$subhead = "

SQL Server Trace flags Configured

"
 $htmlbody += $subhead

$pQuery = "declare @TraceStatus table
 ( TraceFlag int
 , [Status] bit
 , IsGlobal bit
 , IsSession bit
 )

insert @TraceStatus (TraceFlag, [Status], IsGlobal, IsSession)
 EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')

select TraceFlag
 , case IsGlobal when 1 then 'Global'
 else 'Non Global'
 end as 'IsGlobal'
 , IsSession
 , case TraceFlag when 2371 then 'Auto update stats more often'
 when 1204 then 'Writes information about deadlocks to the ERRORLOG in a text format'
 when 1222 then 'Writes information about deadlocks to the ERRORLOG in a XML format'
 when 3604 then 'Send output to console'
 when 3605 then 'Send output to Errorlog'
 when 610 then 'Minimally logged inserts into indexed tables'
 when 834 then 'Allows SQL to use Large-page allocations (KB920093)'
 when 835 then 'Lock Pages in Memory support for Standard Edition (KB970070)'
 when 1118 then 'Tempdb to allocate full extents to each tempdb object (KB328551)'
 when 1211 then 'Disables lock escalation based on memory pressure or number of locks'
 when 1224 then 'Disables lock escalation based on the number of locks'
 when 2528 then 'Disables parallel checking of objects during DBCC CHECKDB'
 when 3226 then 'Prevents successful backup operations from being logged'
 when 4199 then 'Enables all the fixes that were previously made for the query processor under many trace flags (KB941006)'
 else '?'
 end as 'Comment'
 from @TraceStatus"

$pConnection = New-Object System.Data.SqlClient.SqlConnection
 $pConnection.ConnectionString = "Data Source=$($ComputerName);Initial Catalog='master';Integrated Security = True";
 $pAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($pQuery, $pconnection)
 $ptable = New-Object System.Data.Dataset

$pRecs = $pAdapter.Fill($ptable)
 $collection = @()
 $na = "NA"
 $collection += new-object pscustomobject -property @{
 "TraceFlag"=$na;
 "IsGlobal"=$na;
 "IsSession"=$na;
 "Comment"=$na;
 }

if( $ptable.Tables[0].Rows.Count -eq 0)
 {
 $htmlbody += $collection | Select TraceFlag, IsGlobal, IsSession, Comment |ConvertTo-Html -Fragment
 $htmlbody +=$spacer
 Clear-item variable:collection
 }
 else
 {

$htmlbody += $ptable.Tables[0] | Select TraceFlag, IsGlobal, IsSession, Comment | ConvertTo-Html -Fragment
 $htmlbody +=$spacer
 }

########################################SQL Server Tempdb Usage information ###########################################

$subhead = "

SQL Server Tempdb Usage Information

"
 $htmlbody += $subhead

$tQuery = ";with cte
 as (
 select DB_NAME(database_id) as name,
 mf.name as db_filename,
 mf.physical_name,
 cast((mf.size / 128.0) as decimal(20, 2)) as initial_size_MB,
 cast((df.size / 128.0) as decimal(20, 2)) as actual_size_MB,
 CASE mf.is_percent_growth
 when 0 then STR(cast((mf.growth / 128.0) as decimal(10, 2))) + ' MB'
 when 1 then STR(mf.growth) + '%'
 END as auto_grow_setting
 from sys.master_files mf
 JOIN tempdb.sys.database_files df ON mf.name = df.name
 )
 select convert(varchar(40), db_filename) as 'Tempdb_Auto_growth_Since_Start_up'
 , actual_size_MB - initial_size_MB as 'Change_MB'
 , convert(char(15), initial_size_MB) as 'InitialSize_MB'
 , convert(char(15), actual_size_MB) as 'CurrentSize_MB'
 , auto_grow_setting as 'AutoGrow_Setting'
 , substring(physical_name, 1, 150) as 'Physical_Name'
 from cte
 where initial_size_MB <> actual_size_MB;

select sum(user_object_reserved_page_count*8/1024) as 'UserObjects_Reserved_MB'
 , sum(internal_object_reserved_page_count*8/1024) as 'InternalObject_Reserved_MB'
 , sum(version_store_reserved_page_count*8/1024) as 'Version_Store_MB'
 from tempdb.sys.dm_db_file_space_usage
 "

$tConnection = New-Object System.Data.SqlClient.SqlConnection
 $tConnection.ConnectionString = "Data Source=$($ComputerName);Initial Catalog='master';Integrated Security = True";
 $tAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($tQuery, $tconnection)
 $ttable = New-Object System.Data.Dataset

$tRecs = $tAdapter.Fill($ttable)

$htmlbody += $ttable.Tables[0] | select Tempdb_Auto_growth_Since_Start_up, Change_MB, InitialSize_MB, CurrentSize_MB, AutoGrow_Setting, Physical_Name | ConvertTo-Html -Fragment
 $htmlbody +=$spacer

$htmlbody += $ttable.Tables[1] | select UserObjects_Reserved_MB, InternalObject_Reserved_MB, Version_Store_MB | ConvertTo-Html -Fragment
 $htmlbody +=$spacer

######################################### SQL Server Backup Information ##############################################################

$subhead = "

SQL Server Backup Information

"
 $htmlbody += $subhead

$pQuery = "declare @backups table
 ( DBName varchar(80) not null primary key
 , LastFull datetime null
 , NumFullLast30days smallint null default 0
 , LastLogBackup datetime null
 , LastFullLocation varchar(150) null default '*** NO FULL BACKUP ***'
 , LastLogLocation varchar(150) null default '*** NO LOG BACKUPS ***'
 , NumLogLast24hours smallint null default 0
 , Comments varchar(200) null
 )

insert @backups (DBName)
 select substring(name, 1, 80)
 from master.sys.databases
 where database_id <> 2 -- ignore tempdb

update @backups
 set LastLogLocation = 'N/A - System DB'
 where DBName in ('master', 'model' )

update @backups
 set LastLogLocation = 'N/A - Simple'
 from master.sys.databases d join
 @backups b on b.DBName collate DATABASE_DEFAULT = d.name collate DATABASE_DEFAULT
 where d.recovery_model_desc = 'Simple'
 and b.DBName <> 'master'

update @backups
 set LastFullLocation = bf.physical_device_name
 , LastFull = bs.backup_finish_date
 from @backups b left join
 msdb.dbo.backupset bs ON bs.database_name collate DATABASE_DEFAULT = b.DBName collate DATABASE_DEFAULT left JOIN
 msdb.dbo.backupmediaset ms ON bs.media_set_id = ms.media_set_id left JOIN
 msdb.dbo.backupmediafamily bf ON ms.media_set_id = bf.media_set_id
 where bs.type in ('D')
 AND ISNULL(bs.backup_finish_date, bs.backup_start_date) = (select ISNULL(MAX(backup_finish_date), MAX(backup_start_date))
 from msdb.dbo.backupset
 where type = bs.type
 and database_name = bs.database_name
 )

update @backups
 set LastLogLocation = bf.physical_device_name
 , LastLogBackup = bs.backup_finish_date
 from @backups b left join
 msdb.dbo.backupset bs ON bs.database_name collate DATABASE_DEFAULT = b.DBName collate DATABASE_DEFAULT left JOIN
 msdb.dbo.backupmediaset ms ON bs.media_set_id = ms.media_set_id left JOIN
 msdb.dbo.backupmediafamily bf ON ms.media_set_id = bf.media_set_id
 where bs.type in ('L')
 AND ISNULL(bs.backup_finish_date, bs.backup_start_date) = (select ISNULL(MAX(backup_finish_date), MAX(backup_start_date))
 from msdb.dbo.backupset
 where type = bs.type
 and database_name = bs.database_name
 )
 -- Count Full backups in the last 31 days
 update @backups
 set NumFullLast30days = CountBackups
 from @backups b join
 ( select database_name
 , count(*) as 'CountBackups'
 from msdb.dbo.backupset bs
 where type = 'D'
 and bs.backup_start_date > DATEADD(dd,-31,getdate())
 group by database_name
 ) cb on cb.database_name collate DATABASE_DEFAULT = b.DBName collate DATABASE_DEFAULT

update @backups
 set Comments = 'Differential backups also runnig'
 from @backups b inner join
 msdb.dbo.backupset bs ON bs.database_name collate DATABASE_DEFAULT = b.DBName collate DATABASE_DEFAULT
 where bs.type = 'I' -- Differential

-- Count Log backups in the last 24 hours
 update @backups
 set NumLogLast24hours = CountBackups
 from @backups b join
 ( select database_name
 , count(*) as 'CountBackups'
 from msdb.dbo.backupset bs
 where type = 'L'
 and bs.backup_start_date > DATEADD(hh,-24,getdate())
 group by database_name
 ) cb on cb.database_name collate DATABASE_DEFAULT = b.DBName collate DATABASE_DEFAULT

------------------------------
 -- Report database backup info

select DBName as 'Backups'
 , isnull(convert(varchar(17), LastFull , 13) + ' (' + convert(varchar(20), datediff(hh, LastFull, getdate())) + ' hours)', '*** NO FULL BACKUPS ***') as 'LastFull_Backup'
 , NumFullLast30days
 , isnull(convert(varchar(17), LastLogBackup , 13) + ' (' + convert(varchar(20), datediff(mi, LastLogBackup, getdate())) + ' minutes)', 'N/A - Simple') as 'LastLog_Backup'
 , NumLogLast24hours
 , LastFullLocation
 , LastLogLocation
 , Comments
 from @backups
 go"

$pConnection = New-Object System.Data.SqlClient.SqlConnection
 $pConnection.ConnectionString = "Data Source=$($ComputerName);Initial Catalog='master';Integrated Security = True";
 $pAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($pQuery, $pconnection)
 $ptable = New-Object System.Data.Dataset

$pRecs = $pAdapter.Fill($ptable)

$htmlbody += $ptable.Tables[0] | select Backups, LastFull_Backup, NumFullLast30days, LastLog_Backup, NumLogLast24hours, LastFullLocation, LastLogLocation | ConvertTo-Html -Fragment
 $htmlbody +=$spacer

############################################## Blocking Sesssion ID information ###############################################################

$subhead = "

SQL Server Blocking Information

"
 $htmlbody += $subhead

$bQuery = "SELECT
 db.name as DBName,
 tl.request_session_id as Request_SessionID,
 wt.blocking_session_id as Blocking_SesssionID,
 OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
 tl.resource_type as ResourceType,
 h1.TEXT AS RequestingText,
 h2.TEXT AS BlockingTest,
 tl.request_mode as Request_Mode
 FROM sys.dm_tran_locks AS tl
 INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
 INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
 INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
 INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
 INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
 CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
 "

$bConnection = New-Object System.Data.SqlClient.SqlConnection
 $bConnection.ConnectionString = "Data Source=$($ComputerName);Initial Catalog='master';Integrated Security = True";
 $bAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($bQuery, $bconnection)
 $btable = New-Object System.Data.Dataset

$bRecs = $bAdapter.Fill($btable)

$bcollection = @()
 $na = "NA"
 $bcollection += new-object pscustomobject -property @{
 "DBName"=$na;
 "Request_SessionID"=$na;
 "Blocking_SesssionID"=$na;
 "BlockedObjectName"=$na;
 "ResourceType"=$na;
 "RequestingText"=$na;
 "BlockingTest"=$na;
 "Request_Mode"=$na;
 }

if( $btable.Tables[0].Rows.Count -eq 0)
 {
 $htmlbody += $bcollection | select DBName, Request_SessionID, Blocking_SesssionID, BlockedObjectName,ResourceType,RequestingText,BlockingTest,Request_Mode | ConvertTo-Html -Fragment
 $htmlbody +=$spacer

}
 else
 {

$htmlbody += $btable.Tables[0] | select DBName, Request_SessionID, Blocking_SesssionID, BlockedObjectName,ResourceType,RequestingText,BlockingTest,Request_Mode | ConvertTo-Html -Fragment
 $htmlbody +=$spacer
 }

#$htmlbody += $btable.Tables[0] | select DBName, Request_SessionID, Blocking_SesssionID, BlockedObjectName,ResourceType,RequestingText,BlockingTest,Request_Mode |ConvertTo-Html -Fragment
 # $htmlbody +=$spacer

####################################### CPU Usage by each Database ################################################################

$subhead = "

CPU utilization by each database

"
 $htmlbody += $subhead

$cQuery = "WITH DB_CPU_Stats as
 (select DatabaseID
 , DB_Name(DatabaseID) as 'DatabaseName'
 , SUM(total_worker_time)/1000000 as 'CPU_Time_Sec' -- micro
 from sys.dm_exec_query_stats qs cross apply
 ( select convert(int, value) as [DatabaseID]
 from sys.dm_exec_plan_attributes(qs.plan_handle)
 where attribute = N'dbid'
 ) F_DB
 group by DatabaseID
 )
 select top 15
 ROW_NUMBER() over(order by CPU_Time_Sec DESC) as 'row_num'
 , convert(varchar(100), DatabaseName) as 'DatabaseName'
 , CPU_Time_Sec as 'CPU_Seconds'
 , cast(CPU_Time_Sec * 1.0 / SUM(CPU_Time_Sec) over() * 100.0 as decimal(4, 1)) as 'CPUPercent'
 from DB_CPU_Stats
 where DatabaseID > 4 -- system databases
 and DatabaseID <> 32767 -- ResourceDB
 and CPU_Time_Sec > 0
 order by row_num option (RECOMPILE);
 "

$cConnection = New-Object System.Data.SqlClient.SqlConnection
 $cConnection.ConnectionString = "Data Source=$($ComputerName);Initial Catalog='master';Integrated Security = True";
 $cAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($cQuery, $cconnection)
 $ctable = New-Object System.Data.Dataset

$cRecs = $cAdapter.Fill($ctable)

$collection = @()
 $na = "NA"
 $collection += new-object pscustomobject -property @{
 "row_num"=$na;
 "DatabaseName"=$na;
 "CPU_Seconds"=$na;
 "CPUPercent"=$na;
 }

if( $ctable.Tables[0].Rows.Count -eq 0)
 {
 $htmlbody += $collection | select row_num, DatabaseName, CPU_Seconds, CPUPercent |ConvertTo-Html -Fragment
 $htmlbody +=$spacer
 Clear-item variable:collection
 }
 else
 {
 $htmlbody += $ctable.Tables[0] | select row_num, DatabaseName, CPU_Seconds, CPUPercent| ConvertTo-Html -Fragment
 $htmlbody +=$spacer
 }
 ####################################### Fragmentation details ###########################################

$subhead = "

Fragmentation details of all the indexes from all databases

"
 $htmlbody += $subhead

$fQuery = "
 IF OBJECT_ID('tempdb.dbo.#bbc', 'U') IS NOT NULL
 DROP TABLE #bbc
 --If exists (select * from tempdb.sys.all_objects where name like '#bbc%' )
 --drop table #bbc
 create table #bbc
 (DatabaseName varchar(100),ObjectName varchar(100),Index_id int, indexName varchar(100),avg_fragmentation_percent float,IndexType varchar(100),Action_Required varchar(100) default 'NA')

insert into #bbc (DatabaseName,ObjectName,Index_id, indexName,avg_fragmentation_percent,IndexType)

exec master.sys.sp_MSforeachdb ' USE [?]

SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as ObjectName,

a.index_id, b.name as IndexName,

avg_fragmentation_in_percent, index_type_desc

-- , record_count, avg_page_space_used_in_percent --(null in limited)

FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id AND a.index_id = b.index_id

WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0 and db_id() > 4'

update #bbc
 set Action_Required ='Rebuild'
 where avg_fragmentation_percent >30

update #bbc
 set Action_Required ='Rorganize'
 where avg_fragmentation_percent 5

select * from #bbc
 "

$fConnection = New-Object System.Data.SqlClient.SqlConnection
 $fConnection.ConnectionString = "Data Source=$($ComputerName);Initial Catalog='master';Integrated Security = True";
 $fAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($fQuery, $fconnection)
 $ftable = New-Object System.Data.Dataset

$fRecs = $fAdapter.Fill($ftable)
 $bcollection = @()
 $na = "NA"
 $fcollection += new-object pscustomobject -property @{
 "DatabaseName"=$na;
 "ObjectName"=$na;
 "indexName"=$na;
 "avg_fragmentation_percent"=$na;
 "IndexType"=$na;
 "Action_Required"=$na;
 }

if( $ftable.Tables[0].Rows.Count -eq 0)
 {
 $htmlbody += $fcollection | select DatabaseName, ObjectName, Index_id, indexName, avg_fragmentation_percent, IndexType, Action_Required | ConvertTo-Html -Fragment
 $htmlbody +=$spacer

}
 else
 {

$htmlbody += $ftable.Tables[0] | select DatabaseName, ObjectName, Index_id, indexName, avg_fragmentation_percent, IndexType, Action_Required | ConvertTo-Html -Fragment
 $htmlbody +=$spacer
 $htmlbody +=$spacer
 $htmlbody +=$spacer
 $htmlbody= $htmlbody + ''
 }

#$htmlbody += $ftable.Tables[0] | select DatabaseName, ObjectName, Index_id, indexName, avg_fragmentation_percent, IndexType, Action_Required| ConvertTo-Html -Fragment
 #$htmlbody +=$spacer

####################################### SQL SERVER MEMORY ###############################################

[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")

#frame

$MemoryUsageChart3 = New-object System.Windows.Forms.DataVisualization.Charting.Chart
 $legend = New-Object system.Windows.Forms.DataVisualization.Charting.Legend

$legend.name = "Legend1"

$MemoryUsageChart3.Legends.Add($legend)

$MemoryUsageChart3.Width = 400

$MemoryUsageChart3.Height = 400

$MemoryUsageChart3.BackColor = [System.Drawing.Color]::White

$pQuery = "SELECT Total_physical_memory_MB = cast((([total_physical_memory_kb] ))/1024 as varchar(50))FROM [master].[sys].[dm_os_sys_memory]"

$pConnection = New-Object System.Data.SqlClient.SqlConnection
 $pConnection.ConnectionString = "Data Source=$($ComputerName);Initial Catalog='master';Integrated Security = True";
 $pAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($pQuery, $pconnection)
 $ptable = New-Object System.Data.Dataset
 $pRecs = $pAdapter.Fill($ptable)

[void]$MemoryUsageChart3.Titles.Add("Physical Memory Usage by SQL Server [Total Physical Memory $($ptable.Tables[0].Total_physical_memory_MB) MB ] ")
 $MemoryUsageChart3.Titles[0].Font = "segoeuilight,20pt"

#$MemoryUsageChart3.Titles[0].Alignment = "TopLeft"
 $MemoryUsageChart3.Titles[0].Position.Auto = "True"

$chartarea1 = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea

$chartarea1.Name = "ChartArea1"

$MemoryUsageChart3.ChartAreas.Add($chartarea1)

[void]$MemoryUsageChart3.Series.Add("data1")

$MemoryUsageChart3.Series["data1"].ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Pie

$bQuery = "SELECT Available_PM = cast((([available_physical_memory_kb]))/1024 as varchar(50))FROM [master].[sys].[dm_os_sys_memory];
 SELECT BufferPool =cast(CEILING(cntr_value/1024) as varchar(50)) FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)';"

$bConnection = New-Object System.Data.SqlClient.SqlConnection
 $bConnection.ConnectionString = "Data Source=$($ComputerName);Initial Catalog='master';Integrated Security = True";
 $bAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($bQuery, $bconnection)
 $btable = New-Object System.Data.Dataset
 $bRecs = $bAdapter.Fill($btable)
 $i = 0
 $mem =@(Foreach ($tab in $btable.Tables)
 {
 $j = $btable.Tables[$i].columns.ColumnName
 $j+ "`n"+$btable.Tables.$j+"MB"
 $i++
 })

Clear-Item variable:i
 $h = 0
 $p = @()
 foreach($st in $btable.Tables){
 $p += $btable.Tables[$h].columns.ColumnName
 $h++
 }
 $k = 0
 $ph = @(foreach ($t in $p){
 $btable.Tables[$k].$t
 $k++})

$processes2 = Get-Process -ComputerName $ComputerName | Sort-Object -Property WS | Where-Object {$_.Name -eq 'sqlservr'} | Select-Object Name,PM,VM
 $ProcessList1 = @(foreach($Proc in $Processes2){ $Proc.Name + "`n"+[math]::Round((($Proc.PM/1MB)),0)+"MB"})
 $Placeholder1 = @(foreach($Proc in $Processes2){[Math]::ROUND($Proc.PM/1MB,0)})
 $mem = $mem+$ProcessList1
 $ph = $ph+$Placeholder1
 $MemoryUsageChart3.Series["data1"][“PieLabelStyle”] = “Outside”
 $MemoryUsageChart3.Series["data1"][“PieLineColor”] = “Black”
 $MemoryUsageChart3.Series["data1"][“PieDrawingStyle”] = “Concave”
 $MemoryUsageChart3.Series["data1"].Points.DataBindXY($mem,$ph)
 $MemoryUsageChart3.SaveImage("$(get-location)\SQL_Memory.png","png")
 $images1 = Get-ChildItem "$(get-location)\SQL_Memory.png"
 $ImageHTML += $images1 | % {
 $ImageBits = [Convert]::ToBase64String((Get-Content $_ -Encoding Byte))
 "My Image"
 }

##############################################Physical MEMORY#################################

[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")

#frame

$MemoryUsageChart1 = New-object System.Windows.Forms.DataVisualization.Charting.Chart

$MemoryUsageChart1.Width = 500

$MemoryUsageChart1.Height = 400

$MemoryUsageChart1.BackColor = [System.Drawing.Color]::White

#header

[void]$MemoryUsageChart1.Titles.Add("Physical Memory Usage: Top 5 Processes")

$MemoryUsageChart1.Titles[0].Font = "segoeuilight,20pt"

#$MemoryUsageChart1.Titles[0].Alignment = "topLeft"
 $MemoryUsageChart1.Titles[0].Position.Auto = "True"

$chartarea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea

$chartarea.Name = "ChartArea1"

$MemoryUsageChart1.ChartAreas.Add($chartarea)

$legend1 = New-Object system.Windows.Forms.DataVisualization.Charting.Legend

$legend1.name = "Legend1"

$MemoryUsageChart1.Legends.Add($legend1)

[void]$MemoryUsageChart1.Series.Add("data1")

$MemoryUsageChart1.Series["data1"].ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Pie

$Processes = Get-Process -ComputerName $ComputerName| Sort-Object -Property WS | Select-Object Name,PM,VM -Last 5
 $ProcessList2 = @(foreach($Proc in $Processes){$Proc.Name + "`n"+[math]::floor($Proc.PM/1MB)+"MB"})
 $Placeholder2 = @(foreach($Proc in $Processes){$Proc.PM})

$MemoryUsageChart1.Series["data1"][“PieLabelStyle”] = “Outside”

$MemoryUsageChart1.Series["data1"][“PieLineColor”] = “Black”
 $MemoryUsageChart1.Series["data1"][“PieDrawingStyle”] = “Concave”
 $MemoryUsageChart1.Series["data1"].Points.DataBindXY($ProcessList2, $Placeholder2)
 $MemoryUsageChart1.SaveImage("$(get-location)\Physical_Memory_Usage.png","png")

clear-item variable:images1

clear-item variable:ImageBits

$images1 = Get-ChildItem "$(get-location)\Physical_Memory_Usage.png"
 $ImageHTML += $images1 | % {
 $ImageBits = [Convert]::ToBase64String((Get-Content $_ -Encoding Byte))
 "My Image"
 }

#################### VIRTUAL MEMORY ################################
 [void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")

#frame
 $MemoryUsageChart2 = New-object System.Windows.Forms.DataVisualization.Charting.Chart
 $MemoryUsageChart2.Width = 500
 $MemoryUsageChart2.Height = 400
 $MemoryUsageChart2.BackColor = [System.Drawing.Color]::White

[void]$MemoryUsageChart2.Titles.Add("Virtual Memory Usage: Top 5 Processes")
 $MemoryUsageChart2.Titles[0].Font = "segoeuilight,20pt"
 $MemoryUsageChart2.Titles[0].Position.Auto = "True"
 $chartarea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea
 $legend2 = New-Object system.Windows.Forms.DataVisualization.Charting.Legend
 $legend2.name = "Legend1"
 $MemoryUsageChart2.Legends.Add($legend2)
 $chartarea.Name = "ChartArea1"
 $MemoryUsageChart2.ChartAreas.Add($chartarea)
 [void]$MemoryUsageChart2.Series.Add("data2")
 $MemoryUsageChart2.Series["data2"].ChartType =
 [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Pie
 $Processes = Get-Process -ComputerName $ComputerName | Sort-Object -Property WS | Select-Object Name,PM,VM -Last 5
 $ProcessList3 = @(foreach($Proc in $Processes){$Proc.Name + "`n"+[math]::floor($Proc.VM/1MB)+"MB"})
 $Placeholder3 = @(foreach($Proc in $Processes){$Proc.VM})
 $MemoryUsageChart2.Series["data2"][“PieLabelStyle”] = “Outside”
 $MemoryUsageChart2.Series["data2"][“PieLineColor”] = “Black”
 $MemoryUsageChart2.Series["data2"][“PieDrawingStyle”] = “Concave”

$MemoryUsageChart2.Series["data2"].Points.DataBindXY($ProcessList3,$Placeholder3)

$MemoryUsageChart2.SaveImage("$(get-location)\Virtual_Memory_Usage.png","png")

$ImageHTML= $ImageHTML + ''

$images1 = Get-ChildItem "$(get-location)\Virtual_Memory_Usage.png"
 $ImageHTML += $images1 | % {
 $ImageBits = [Convert]::ToBase64String((Get-Content $_ -Encoding Byte))
 "My Image"
 }
 ##################################################################### Database Wise Buffer Pool usage ##############################################################

[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")
 $MemoryUsageChart3 = New-object System.Windows.Forms.DataVisualization.Charting.Chart
 $legend = New-Object system.Windows.Forms.DataVisualization.Charting.Legend
 $legend.name = "Legend1"
 $MemoryUsageChart3.Legends.Add($legend)
 $MemoryUsageChart3.Width = 700
 $MemoryUsageChart3.Height = 400
 $MemoryUsageChart3.BackColor = [System.Drawing.Color]::White
 $dQuery = "DECLARE @total_buffer INT;
 SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters
 WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND counter_name = 'Total Pages';
 ;WITH src AS( SELECT database_id, db_buffer_pages = COUNT_BIG(*)
 FROM sys.dm_os_buffer_descriptors --WHERE database_id BETWEEN 5 AND 32766
 GROUP BY database_id)SELECT [db_name] = CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END, db_buffer_MB = db_buffer_pages / 128
 FROM src
 ORDER BY db_buffer_MB DESC;"
 $dConnection = New-Object System.Data.SqlClient.SqlConnection
 $dConnection.ConnectionString = "Data Source=$($ComputerName);Initial Catalog='master';Integrated Security = True";
 $dAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($dQuery, $dconnection)
 $dtable = New-Object System.Data.Dataset
 $dRecs = $dAdapter.Fill($dtable)
 [void]$MemoryUsageChart3.Titles.Add("SQL Server Memory Usage by each database from the buffer pool")
 $MemoryUsageChart3.Titles[0].Font = "segoeuilight,20pt"
 $MemoryUsageChart3.Titles[0].Position.Auto = "True"
 $chartarea1 = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea
 $chartarea1.Name = "ChartArea1"
 $MemoryUsageChart3.ChartAreas.Add($chartarea1)
 $MemoryUsageChart3.ChartAreas["ChartArea1"].Area3DStyle.Enable3D = "true"
 $MemoryUsageChart3.ChartAreas["ChartArea1"].Area3DStyle.Inclination = 45
 $MemoryUsageChart3.ChartAreas["ChartArea1"].Area3DStyle.Rotation = 45
 [void]$MemoryUsageChart3.Series.Add("data1")
 $MemoryUsageChart3.Series["data1"].ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Pie
 $f =0
 $mem = @()
 foreach ($s in $dtable.Tables[0].db_buffer_MB)
 {
 $mem += $dtable.Tables[0].db_name[$f]+"`n"+$dtable.Tables[0].db_buffer_MB[$f]+"MB"
 $f++
 }
 $ph = $dtable.Tables[0].db_buffer_MB
 $MemoryUsageChart3.Series["data1"][“PieLabelStyle”] = “Outside”
 $MemoryUsageChart3.Series["data1"].SmartLabelStyle.Enabled = "true"
 $MemoryUsageChart3.Series["data1"]["3DLabelLineSize"] = "30"
 #$MemoryUsageChart3.Series["data1"].LableStyle.Interval = "20"

$MemoryUsageChart3.Series["data1"][“PieLineColor”] = “Black”
 $MemoryUsageChart3.Series["data1"][“PieDrawingStyle”] = “Concave”
 $MemoryUsageChart3.Series["data1"].Points.DataBindXY($mem,$ph)
 $MemoryUsageChart3.SaveImage("$(get-location)\SQL_Databases.png","png")
 $images1 = Get-ChildItem "$(get-location)\SQL_Databases.png"
 $ImageHTML += $images1 | % {
 $ImageBits = [Convert]::ToBase64String((Get-Content $_ -Encoding Byte))
 "My Image"
 }

#---------------------------------------------------------------------
 # Generate the HTML report and output to file
 #---------------------------------------------------------------------

Write-Verbose "Producing HTML report"

$reportime = Get-Date

#Common HTML head and styles
 $htmlhead="




Server Info: $ComputerName

Generated: $reportime

"

$htmltail = "
 "

$headstyle1 = "

Server Info: $ComputerName

Generated: $reportime

"

####################################################################### Database information collection ################################################

$htmlreport = $style + $htmlbody + $htmltail

$htmlreport | Out-File $htmlfile -Encoding Utf8
 Add-content $htmlfile $ImageHTML
 }

}

 

 

Output:

Server Info: Server1

Generated: 03/30/2017 16:02:26

Computer System Information

Name Manufacturer Model Physical Processors Logical Processors Total Physical Memory (Gb) DnsHostName Domain Total No.of Cores
Server1 VMware, Inc. VMware Virtual

Platform

2 2 8 Server1 abcd.co 2

Operating System Information

Operating System Architecture Version Organization Install

Date

WindowsDirectory
Microsoft Windows Server 2008 R2 Enterprise 64-bit 6.1.7601 ABCD

Ltd

09/04/2013 C:\Windows

PageFile Information

Pagefile

Name

Allocated Size (Mb)
C:\Windows\Pagefile\pagefile.sys 12288

Logical Disk Information

DeviceID FileSystem VolumeName Total Size (GB) Free Space

(GB)

A: 0 0
C: NTFS System 70 30
D: NTFS DATA 50 29
E: CDFS scripts_17aug 0 0

Volume Information

Label Name DeviceID SystemVolume Total Size (GB) Free Space (GB)
System C:

\

\\?\Volume{b5aeaa89-a149-11e2-8222-806e6f6e6963}\ True 70 30
Pagefile C:\Windows\Pagefile

\

\\?\Volume{b5aeaa8a-a149-11e2-8222-806e6f6e6963}\ False 18 6
DATA D:\ \\?\Volume

{b5aeaa8b-a149-11e2-8222-806e6f6e6963}\

False 50 29
SQLDATA D:\Server1\Data\Data-01\ \\?\Volume

{182d34f1-a1c5-11e2-a9e0-005056a5018e}\

False 50 47
SQLTempDB D:\Server\TempDB\TempDB-01\ \\?

\Volume{182d34f5-a1c5-11e2-a9e0-005056a5018e}\

False 10 8
SQLTlog D:\Server1\TLog\TLog-01\ \\?

\Volume{182d34f9-a1c5-11e2-a9e0-005056a5018e}\

False 50 46
scripts_17aug E:\ \\?\Volume{71cb28ec-

45bc-11e5-8d19-806e6f6e6963}\

False 0 0

Network Interface Information

Connection Name Adapter

Name

Type MAC Enabled Speed (Mbps) IPAddress
Production vmxnet3 Ethernet

Adapter

Ethernet 802.3 00:##:##:##:##:## True 10000 10.1.2.3
Management vmxnet3 Ethernet

Adapter #2

Ethernet 802.3 00:##:##:##:##:## True 10000 10.4.5.6
Backup vmxnet3 Ethernet

Adapter #3

Ethernet 802.3 00:##:##:##:##:## True 10000 10.7.8.9

Memory Information

TotalPhysical_RAM_MB AvailablePhysical_RAM_MB TotalPagefile_Size_MB Available_Pagefile_MB
8191 5748 20477 18103

SQL Server Instance Information

SQLServerProperty Value
Machine Name Server1
Server

Name

Server1
Physical Net BIOS Name Server1
Instance Name
Is

Clustered

Not Clustered
SQL Version SQL Server 2008 R2
SQL Edition Enterprise Edition (64-bit)
SQL SP Level SP3
SQL Version Number 10.50.6000.34
SQL

Collation

SQL_Latin1_General_CP1_CI_AS
SQL Feature: Full Text Indexing Yes – Full Text Indexing is installed
SQL Authentication: Mixed Security
SQL Server Start Date 29 Mar 2017 19:10 (1 days)
SQL Server

Install Date

10 Apr 2013 11:06

SQL Server Database Information

DatabaseName database_id create_date collation_name state_desc recovery_model_desc
master 1 08/04/2003

09:13:36

SQL_Latin1_General_CP1_CI_AS ONLINE SIMPLE
tempdb 2 29/03/2017 19:10:05 SQL_Latin1_General_CP1_CI_AS ONLINE SIMPLE
model 3 08/04/2003

09:13:36

SQL_Latin1_General_CP1_CI_AS ONLINE SIMPLE
msdb 4 02/04/2010 17:35:08 SQL_Latin1_General_CP1_CI_AS ONLINE SIMPLE
DBA 7 22/05/2013

12:39:33

SQL_Latin1_General_CP1_CI_AS ONLINE SIMPLE

SQL Server Trace Information

TraceFlag IsGlobal IsSession Comment
NA NA NA NA

SQL Server Tempdb Usage Information

UserObjects_Reserved_MB InternalObject_Reserved_MB Version_Store_MB
0 0 0

SQL Server Backup Information

Backups LastFull_Backup NumFullLast30days LastLog_Backup NumLogLast24hours LastFullLocation LastLogLo

cation

DBA 30 Mar 2017 05:01 (11 hours) 31 N/A – Simple 0 Legato#35f483a6-f1cb-4247-87cd-

bd701b0f5783

N/A – Simple
master 30 Mar 2017 05:28 (11 hours) 31 N/A – Simple 0 Legato#1b653ceb-cd5f-4616-a08e-ae8c9c11361e N/A – System DB
model 30 Mar

2017 05:29 (11 hours)

31 N/A – Simple 0 Legato#3eac7182-cc22-43bc-a0a6-1ef6f99f99a4 N/A – Simple
msdb 30 Mar 2017 05:29 (11 hours) 31 N/A – Simple 0 Legato#42ab678e-f2d6-442d-a8c3-c5731f20021e N/A –

Simple

SQL Server Blocking Information

DBName Request_SessionID Blocking_SesssionID BlockedObjectName ResourceType RequestingText BlockingTe

st

Request_Mode
NA NA NA NA NA NA NA NA

CPU

utilization by each database

row_num DatabaseName CPU_Seconds CPUPercent
1
2
3 DBA 2 25.0

Fragmentation

details of all the indexes from all databases

DatabaseName ObjectName Index_id indexName avg_fragmentation_percent IndexType Action_Required Server1_MiServer1_Reporting Insured 1 PK_Insured_1 98.7426201796971 CLUSTERED

INDEX

Rebuild Server1_MiServer1_Reporting Event 1 PK_Event_1 97.3728339854667 CLUSTERED

INDEX

Rebuild QRiskReporting MSsnapshotdeliveryprogress 3 nci_MSsnapshotdeliveryprogress_session_token 94.1176470588235 NON

CLUSTERED INDEX

Rebuild msdb backupmediafamily 2 backupmediafamilyuuid 92.8571428571429 NONCLUSTERED INDEX Rebuild msdb backupmediaset 2 backupmediasetuuid 87.5 NONCLUSTERED INDEX Rebuild msdb backupset 3 backupsetDate 87.5 NONCLUSTERED INDEX Rebuild msdb sysjobactivity 1 clust 85.7142857142857 CLUSTERED INDEX Rebuild master spt_values 2 ix2_spt_values_nu_nc 80 NONCLUSTERED INDEX Rebuild msdb backupset 2 backupsetuuid 77.7777777777778 NONCLUSTERED INDEX Rebuild msdb backupfile 1 PK__backupfi__57D1800A17C286CF 75.7462686567164 CLUSTERED INDEX Rebuild ETL_Config ETL_Container_Syndicate_Audit 1 PK_ETL_Container_Syndicate_Audit 75 CLUSTERED

INDEX

Rebuild QRiskReporting MSsnapshotdeliveryprogress 2 ci_MSsnapshotdeliveryprogress_progress_token_hash 75 NONCLUSTERED

INDEX

Rebuild msdb MSdbms_datatype_mapping 1 pk_MSdbms_datatype_mapping 75 CLUSTERED

INDEX

Rebuild msdb backupmediafamily 1 PK__backupme__0C13C86F0880433F 69.3333333333333 CLUSTERED

INDEX

Rebuild msdb sysjobhistory 2 nc1 66.6666666666667 NONCLUSTERED

INDEX

Rebuild Server1_MiServer1_Reporting QueryVersionRowCount 1 PK_QueryVersionRowCount 66.6666666666667 CLUSTERED

INDEX

Rebuild Server1_MiServer1_Reporting NatureOfLoss 1 PK_NatureOfLoss 66.6666666666667 CLUSTERED

INDEX

Rebuild msdb backupmediaset 1 PK__backupme__DAC69E4D04AFB25B 63.1578947368421 CLUSTERED

INDEX

Rebuild Server1_MiServer1_Reporting ResponseCache 1 PK_ResponseCache 60 CLUSTERED

INDEX

Rebuild Server1_MiServer1_Reporting AnalysisCode 1 PK_AnalysisCode 60 CLUSTERED

INDEX

Rebuild msdb syspolicy_facet_events 1 IX_facet_events_target_type_alias 60 CLUSTERED

INDEX

Rebuild master spt_values 1 spt_valuesclust 52.6315789473684 CLUSTERED

INDEX

Rebuild msdb sysjobsteps 1 clust 50 CLUSTERED INDEX Rebuild msdb MSdbms_datatype 1 pk_MSdbms_datatype 50 CLUSTERED INDEX Rebuild msdb syspolicy_facet_events 2 UX_facet_events 50 NONCLUSTERED INDEX Rebuild msdb syscollector_collection_items_internal 1 PK_syscollector_collection_items_internal 50 CLUSTERED

INDEX

Rebuild Server1_MiServer1_Reporting InsuranceClass 1 PK_InsuranceClass 50 CLUSTERED

INDEX

Rebuild msdb syscollector_collector_types_internal 1 PK_syscollector_collector_types_internal 50 CLUSTERED

INDEX

Rebuild Server1_MiServer1_Reporting DistributionChannel 1 PK_DistributionChannel 50 CLUSTERED

INDEX

Rebuild DBA dba_indexDefragLog 1 PK_indexDefragLog_v40 50 CLUSTERED

INDEX

Rebuild ETL_Config ReconciliationResultSet 1 PK_ReconciliationResultSet 50 CLUSTERED

INDEX

Rebuild msdb sysjobhistory 1 clust 47.6190476190476 CLUSTERED

INDEX

Rebuild msdb backupfilegroup 1 PK__backupfi__760CD67A12FDD1B2 41.6666666666667 CLUSTERED

INDEX

Rebuild msdb MSdbms_map 1 pk_MSdbms_map 40 CLUSTERED INDEX Rebuild Server1_MiServer1_Reporting ReportingClassSubPortfolioBridge_Dwh 1 PK_ReportingClassSubPortfolioBridge 37.5 C

LUSTERED INDEX

Rebuild ETL_Config ETL_Error_Log 2 ETL_Error_Log_Index 37.5 NONCLUSTERED

INDEX

Rebuild msdb backupset 1 PK__backupse__21F79AAB0E391C95 35.8490566037736 CLUSTERED

INDEX

Rebuild QRiskReporting UserGroup 1 PK_UserGroup 20.4081632653061 CLUSTERED

INDEX

Rorganize QRiskReporting UserGroup 1 PK_UserGroup 20.4081632653061 CLUSTERED

INDEX

Rorganize QRiskReporting ClientDivision 1 ClientDivision_PK 20.3389830508475 CLUSTERED

INDEX

Rorganize QRiskReporting Factor 1 RMSSupplierAuditFactorScore_PK 20 CLUSTERED

INDEX

Rorganize QRiskReporting ClientDivision 1 ClientDivision_PK 20 CLUSTERED

INDEX

Rorganize QRiskReporting BrokerBranch 1 BrokerBranch_PK 19.5121951219512 CLUSTERED

INDEX

Rorganize QRiskReporting Contact 1 Contact_PK 18.1818181818182 CLUSTERED

INDEX

Rorganize Server1_MiServer1_Reporting Hierarchy 2 _dta_index_Hierarchy_35_1044198770__K2_7 15.625 NONCLUSTERED

INDEX

Rorganize Server1_MiServer1_Reporting ReportingClassSubPortfolioBridge_Lda 1 PK_ReportingClassSubPortfolioBridge_Lda 13.3333333

333333

CLUSTERED INDEX Rorganize QRiskReporting ClientProfileAccidentTrend 1 ClientProfileAccidentTrend_PK 13.2743362831858 CLUSTERED

INDEX

Rorganize Server1_MiServer1_Reporting AnalysisCode 1 PK_AnalysisCode 13.2122213047069 CLUSTERED

INDEX

Rorganize QRiskReporting ClientProfileAccidentTrend 1 ClientProfileAccidentTrend_PK 11.9266055045872 CLUSTERED

INDEX

Rorganize QRiskReporting Client 1 Client_PK 11.9266055045872 CLUSTERED

INDEX

Rorganize QRiskReporting Client 1 Client_PK 11.0169491525424 CLUSTERED

INDEX

Rorganize

Server Memory and CPU consumption in graphical

representation

My  Image

»This will show you the SQL Server instance Memory Usage. If you see more than one “sqlservr” process, then it means that the

server contains more than one instance.

»This will show you the Top memory consuming processes apart from sql server. Please be informed that by default SQL

Server will consume all the memory at its disposal as configured.If you find any other process consuming more memory, please contact Wintel

team.

»This will show you the Virtual memory consuming processes.Virtual memory is a memory management capability of an OS that

uses hardware and software to allow a computer to compensate for physical memoryshortages by temporarily transferring data from random access memory

(RAM) to disk storage.

»This will show you the bufferpool(Memory) being used by each database. Note: There are other things which will consume

memory, hence please investigate appropriately.

 

 

screenshot of the output.

Untitled

 

Advertisements