This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
Today, I handled a service request from our customer seeking additional information on performance monitoring for ReadScale-Out in Azure SQL Database. They needed details on how to find missing indexes, query statistics, and more.
I created this PowerShell script that works for both Elastic Database Pools and standalone databases, executing multiple queries for monitorin
The following PowerShell script connects to a read-only replica, retrieves performance data, and stores it for future analysis. This article will guide you through the steps required to implement this script in your own environment and could be possible to use for Single Azure SQL Database, Azure SQL Elastic Pool or Azure SQL Managed Instance.
Basically we need to configure the parameters:
- $server = "xxxxx.database.windows.net" // Azure SQL Server name
- $user = "xxxxxx" // User Name
- $passwordSecure = "xxxxxx" // Password
- $Db = "xxxxxx" // Database Name, if you type the value ALL, all databases will be checked.
- $Folder = $true // Folder where the log file will be generated with all the issues found.
- DropExisting =value 1 or 0, if the previous files located on Destinatio folder will be deleted every time that you execute the process.
- ElasticDBPoolName. PowerShell Script will check all the databases that are associated with this elastic database pool (only for Azure SQL Database).
- Create a subfolder with date and time used in every execution
- PerfChecker.Log = Contains all the issues found.
- Every check done will save two files
- Extension .Txt that contains the report of the operation done.
- Extension .task that contains a possible mitigation about the issue found.
Script
#----------------------------------------------------------------
# Application: ReadScale-Out Performance Checker
# Propose: Inform about performance recomendations for ReadScale-Out
# Checks:
# 1) Check if the statistics
# If number of rows in the statistics is different of rows_sampled.
# If we have more than 15 days that the statistics have been updated.
# 2) Check if we have any auto-tuning recomendations
# 3) Check if the statistics associated to any index is:
# If number of rows in the statistics is different of rows_sampled.
# If we have more than 15 days that the statistics have been updated.
# 4) Check if MAXDOP is 0
# 5) Check if we have missing indexes (SQL Server Instance)
# 6) Obtain resource usage per database.
# 7) Total amount of space and rows per table.
# 8) Query Stats summary
# Outcomes:
# In the folder specified in $Folder variable we are going to have a file called PerfChecker.Log that contains all the operations done
# and issues found. Also, we are going to have a file per database and check done with the results gathered.
# Every time that the process is executed it is created a new subfolder.
#----------------------------------------------------------------
#----------------------------------------------------------------
#Parameters
#----------------------------------------------------------------
param($server = "myserver.database.windows.net", #ServerName parameter to connect,for example, myserver.database.windows.net
$user = "username", #UserName parameter to connect
$passwordSecure = "pwd!", #Password Parameter to connect
$Db = "DBName", #DBName Parameter to connect. Type ALL to check all the databases running in the server
$Folder = "c:\SQLDAta", #Folder Parameter to save the log and solution files, for example, c:\PerfChecker
$DropExisting=1, #Drop (1) the previous file saved in the folder with extensions .csv, .txt, .task !=1 = leave the files
$ElasticDBPoolName = "dbPoolName") #Name of the elastic DB Pool if you want to filter only by elastic DB Pool.
#-------------------------------------------------------------------------------
# Check the statistics status
# 1.- Review if number of rows is different of rows_sampled
# 2.- Review if we have more than 15 days that the statistics have been updated.
#-------------------------------------------------------------------------------
function CheckStatistics($connection,$FileName, $FileNameLogSolution , $iTimeOut)
{
try
{
$Item=0
logMsg( "---- Checking Statistics health (Started) (REF: https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-ver15)---- " ) (1) $true $FileName
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand
$command.CommandTimeout = $iTimeOut
$command.Connection=$connection
$command.CommandText = "SELECT sp.stats_id, stat.name, o.name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter, DATEDIFF(DAY, last_updated , getdate()) AS Diff, schema_name(o.schema_id) as SchemaName
FROM sys.stats AS stat
Inner join sys.objects o on stat.object_id=o.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE o.type = 'U' AND stat.auto_created ='1' or stat.user_created='1' order by o.name, stat.name"
$Reader = $command.ExecuteReader();
while($Reader.Read())
{
if( $Reader.GetValue(5) -gt $Reader.GetValue(6)) #If number rows is different rows_sampled
{
$Item=$Item+1
logMsg("Possible outdated (Rows_Sampled is less than rows of the table):".PadRight(100," ") + " of " + ($Reader.GetValue(11).ToString() +"."+ ($Reader.GetValue(2).ToString() + " " + $Reader.GetValue(1).ToString())).PadRight(400," ")) (2) $true $FileName
logSolution("UPDATE STATISTICS [" + $Reader.GetValue(11).ToString() +"].["+ $Reader.GetValue(2).ToString() + "]([" + $Reader.GetValue(1).ToString() + "]) WITH FULLSCAN") $FileNameLogSolution
}
if( TestEmpty($Reader.GetValue(10))) {}
else
{
if($Reader.GetValue(10) -gt 15) #if we have more than 15 days since the lastest update.
{
$Item=$Item+1
logMsg("Possible outdated (15 days since the latest update):".PadRight(100," ") + " of " + ($Reader.GetValue(11).ToString() +"."+ ($Reader.GetValue(2).ToString() + " " + $Reader.GetValue(1).ToString())).PadRight(400," ")) (2) $true $FileName
logSolution("UPDATE STATISTICS [" + $Reader.GetValue(11).ToString() +"].["+ $Reader.GetValue(2).ToString() + "]([" + $Reader.GetValue(1).ToString() + "]) WITH FULLSCAN") $FileNameLogSolution
}
}
}
$Reader.Close();
logMsg( "---- Checking Statistics health (Finished) ---- " ) (1) $true -$FileName
return $Item
}
catch
{
logMsg("Not able to run statistics health checker..." + $Error[0].Exception) (2) $true $FileName
return 0
}
}
#-------------------------------------------------------------------------------
# Check missing indexes.
#-------------------------------------------------------------------------------
function CheckMissingIndexes($connection ,$FileName, $FileNameLogSolution , $iTimeOut)
{
try
{
$Item=0
logMsg( "---- Checking Missing Indexes (Started) Ref: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-groups-transact-sql?view=sql-server-ver15 ---- " ) (1) $true $FileName
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand
$command.CommandTimeout = $iTimeOut
$command.Connection=$connection
$command.CommandText = "SELECT CONVERT (varchar, getdate(), 126) AS runtime,
CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact *
(migs.user_seeks + migs.user_scans)) AS improvement_measure,
REPLACE(REPLACE('CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' +
CONVERT (varchar, mid.index_handle) + ' ON ' + LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(mid.statement,CHAR(10), ' '), CHAR(13), ' '),' ',''))) +
'(' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL
THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', ''), CHAR(10), ' '), CHAR(13), ' ') AS create_index_statement,
migs.avg_user_impact
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC"
$Reader = $command.ExecuteReader()
$bFound=$false
$bCol=$false
$ColName=""
$Content = [System.Collections.ArrayList]@()
while($Reader.Read())
{
#Obtain the columns only
if($bCol -eq $false)
{
for ($iColumn=0; $iColumn -lt $Reader.FieldCount; $iColumn++)
{
$bCol=$true
$ColName=$ColName + $Reader.GetName($iColumn).ToString().Replace("\t"," ").Replace("\n"," ").Replace("\r"," ").Replace("\r\n","").Trim() + " || "
}
}
#Obtain the values of every missing indexes
$bFound=$true
$TmpContent=""
for ($iColumn=0; $iColumn -lt $Reader.FieldCount; $iColumn++)
{
$TmpContent= $TmpContent + $Reader.GetValue($iColumn).ToString().Replace("\t"," ").Replace("\n"," ").Replace("\r"," ").Replace("\r\n","").Trim() + " || "
}
$Content.Add($TmpContent) | Out-null
}
if($bFound)
{
logMsg( "---- Missing Indexes found ---- " ) (1) $true $FileName
logMsg( $ColName.Replace("\t","").Replace("\n","").Replace("\r","") ) (1) $true $FileName $false
for ($iColumn=0; $iColumn -lt $Content.Count; $iColumn++)
{
logMsg( $Content[$iColumn].Replace("\t","").Replace("\n","").Replace("\r","").Replace("\r\n","").Trim() ) (1) $true $FileName $false
$Item=$Item+1
}
}
$Reader.Close();
logMsg( "---- Checking missing indexes (Finished) ---- " ) (1) $true $FileName
return $Item
}
catch
{
logMsg("Not able to run missing indexes..." + $Error[0].Exception) (2) $true $FileName
return 0
}
}
#-------------------------------------------------------------------------------
# Check if the statistics associated to any index is:
# 1.- Review if number of rows is different of rows_sampled
# 2.- Review if we have more than 15 days that the statistics have been updated.
#-------------------------------------------------------------------------------
function CheckIndexesAndStatistics($connection, $FileName, $FileNameLogSolution , $iTimeOut )
{
try
{
$Item=0
logMsg( "---- Checking Indexes and Statistics health (Started) - Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-ver15 -" ) (1) $true $FileName
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand
$command.CommandTimeout = $iTimeOut
$command.Connection=$connection
$command.CommandText = "SELECT ind.index_id, ind.name, o.name, stat.filter_definition, sp.last_updated, sp.rows, sp.rows_sampled, sp.steps, sp.unfiltered_rows, sp.modification_counter, DATEDIFF(DAY, last_updated , getdate()) AS Diff, schema_name(o.schema_id) as SchemaName,*
from sys.indexes ind
Inner join sys.objects o on ind.object_id=o.object_id
inner join sys.stats stat on stat.object_id=o.object_id and stat.stats_id = ind.index_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE o.type = 'U' order by o.name, stat.name"
$Reader = $command.ExecuteReader();
while($Reader.Read())
{
if( $Reader.GetValue(5) -gt $Reader.GetValue(6)) #If number rows is different rows_sampled
{
$Item=$Item+1
logMsg("Possible outdated - (Rows_Sampled is less than rows of the table):".PadRight(100," ") + " of " + ($Reader.GetValue(11).ToString() +"."+ $Reader.GetValue(2).ToString() + " " + $Reader.GetValue(1).ToString()).PadRight(400," ")) (2) $true $FileName
logSolution("ALTER INDEX [" + $Reader.GetValue(1).ToString() + "] ON [" + $Reader.GetValue(11).ToString() +"].["+ $Reader.GetValue(2).ToString() + "] REBUILD") $FileNameLogSolution
}
if( TestEmpty($Reader.GetValue(10))) {}
else
{
if($Reader.GetValue(10) -gt 15)
{
$Item=$Item+1
logMsg("Possible outdated - (15 days since the latest update):".PadRight(100," ") + " of " + ($Reader.GetValue(11).ToString() +"."+ $Reader.GetValue(2).ToString() + " " + $Reader.GetValue(1).ToString()).PadRight(400," ")) (2) $true $FileName
logSolution("ALTER INDEX [" + $Reader.GetValue(1).ToString() + "] ON [" + $Reader.GetValue(11).ToString() +"].["+ $Reader.GetValue(2).ToString() + "] REBUILD") $FileNameLogSolution
}
}
}
$Reader.Close();
logMsg( "---- Checking Indexes and Statistics health (Finished) ---- " ) (1) $true $FileName
return $Item
}
catch
{
logMsg("Not able to run Indexes and statistics health checker..." + $Error[0].Exception) (2) $true $FileName
return 0
}
}
#-------------------------------------------------------------------------------
# Check if MAXDOP is 0
#-------------------------------------------------------------------------------
function CheckScopeConfiguration($connection ,$FileName, $FileNameLogSolution , $iTimeOut)
{
try
{
$Item=0
logMsg( "---- Checking Scoped Configurations ---- Ref: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-scoped-configurations-transact-sql?view=sql-server-ver15" ) (1) $true $FileName
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand
$command.CommandTimeout = $iTimeOut
$command.Connection=$connection
$command.CommandText = "select * from sys.database_scoped_configurations"
$Reader = $command.ExecuteReader();
while($Reader.Read())
{
if( $Reader.GetValue(1) -eq "MAXDOP")
{
if( $Reader.GetValue(2) -eq 0)
{
logMsg("You have MAXDOP with value 0" ) (2) $true $FileName
$Item=$Item+1
}
}
}
$Reader.Close();
logMsg( "---- Checking Scoped Configurations (Finished) ---- " ) (1) $true $FileName
return $Item
}
catch
{
logMsg("Not able to run Scoped Configurations..." + $Error[0].Exception) (2) $true $FileName
return 0
}
}
#----------------------------------------------------------------
#Function to connect to the database using a retry-logic
#----------------------------------------------------------------
Function GiveMeConnectionSource($DBs)
{
for ($i=1; $i -lt 10; $i++)
{
try
{
logMsg( "Connecting to the database..." + $DBs + ". Attempt #" + $i) (1)
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = "Server="+$server+";Database="+$Dbs+";User ID="+$user+";Password="+$password+";Connection Timeout=60;Application Name=ReadScaleOut Collector;ApplicationIntent=ReadOnly"
$SQLConnection.Open()
logMsg("Connected to the database.." + $DBs) (1)
return $SQLConnection
break;
}
catch
{
logMsg("Not able to connect - " + $DBs + " - Retrying the connection..." + $Error[0].Exception) (2)
Start-Sleep -s 5
}
}
}
#--------------------------------------------------------------
#Create a folder
#--------------------------------------------------------------
Function CreateFolder
{
Param( [Parameter(Mandatory)]$Folder )
try
{
$FileExists = Test-Path $Folder
if($FileExists -eq $False)
{
$result = New-Item $Folder -type directory
if($result -eq $null)
{
logMsg("Imposible to create the folder " + $Folder) (2)
return $false
}
}
return $true
}
catch
{
return $false
}
}
#-------------------------------
#Create a folder
#-------------------------------
Function DeleteFile{
Param( [Parameter(Mandatory)]$FileName )
try
{
$FileExists = Test-Path $FileNAme
if($FileExists -eq $True)
{
Remove-Item -Path $FileName -Force
}
return $true
}
catch
{
return $false
}
}
#--------------------------------
#Log the operations
#--------------------------------
function logMsg
{
Param
(
[Parameter(Mandatory=$true, Position=0)]
[string] $msg,
[Parameter(Mandatory=$false, Position=1)]
[int] $Color,
[Parameter(Mandatory=$false, Position=2)]
[boolean] $Show=$true,
[Parameter(Mandatory=$false, Position=3)]
[string] $sFileName,
[Parameter(Mandatory=$false, Position=4)]
[boolean] $bShowDate=$true
)
try
{
if($bShowDate -eq $true)
{
$Fecha = Get-Date -format "yyyy-MM-dd HH:mm:ss"
$msg = $Fecha + " " + $msg
}
If( TestEmpty($SFileName) )
{
Write-Output $msg | Out-File -FilePath $LogFile -Append
}
else
{
Write-Output $msg | Out-File -FilePath $sFileName -Append
}
$Colores="White"
$BackGround =
If($Color -eq 1 )
{
$Colores ="Cyan"
}
If($Color -eq 3 )
{
$Colores ="Yellow"
}
if($Color -eq 2 -And $Show -eq $true)
{
Write-Host -ForegroundColor White -BackgroundColor Red $msg
}
else
{
if($Show -eq $true)
{
Write-Host -ForegroundColor $Colores $msg
}
}
}
catch
{
Write-Host $msg
}
}
#--------------------------------
#Log the solution
#--------------------------------
function logSolution
{
Param
(
[Parameter(Mandatory=$true, Position=0)]
[string] $msg,
[Parameter(Mandatory=$false, Position=3)]
[string] $sFileName
)
try
{
Write-Output $msg | Out-File -FilePath $sFileName -Append
}
catch
{
Write-Host $msg
}
}
#--------------------------------
#The Folder Include "\" or not???
#--------------------------------
function GiveMeFolderName([Parameter(Mandatory)]$FolderSalida)
{
try
{
$Pos = $FolderSalida.Substring($FolderSalida.Length-1,1)
If( $Pos -ne "\" )
{return $FolderSalida + "\"}
else
{return $FolderSalida}
}
catch
{
return $FolderSalida
}
}
#--------------------------------
#Validate Param
#--------------------------------
function TestEmpty($s)
{
if ([string]::IsNullOrWhitespace($s))
{
return $true;
}
else
{
return $false;
}
}
#--------------------------------
#Separator
#--------------------------------
function GiveMeSeparator
{
Param([Parameter(Mandatory=$true)]
[System.String]$Text,
[Parameter(Mandatory=$true)]
[System.String]$Separator)
try
{
[hashtable]$return=@{}
$Pos = $Text.IndexOf($Separator)
$return.Text= $Text.substring(0, $Pos)
$return.Remaining = $Text.substring( $Pos+1 )
return $Return
}
catch
{
$return.Text= $Text
$return.Remaining = ""
return $Return
}
}
Function Remove-InvalidFileNameChars {
param([Parameter(Mandatory=$true,
Position=0,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true)]
[String]$Name
)
return [RegEx]::Replace($Name, "[{0}]" -f ([RegEx]::Escape([String][System.IO.Path]::GetInvalidFileNameChars())), '')}
function Replace-SpacesInRange {
param (
[string]$InputString)
# Replace matched spaces with a single space
$OutputString = ($InputString -replace "\s+"," ")
return $OutputString
}
#-------------------------------------------------------------------------------
# Check the rows, space used, allocated and numbers of tables.
#-------------------------------------------------------------------------------
function CheckStatusPerTable($connection ,$FileName, $iTimeOut)
{
try
{
logMsg( "---- Checking Status per Table ---- " ) (1) $true $FileName
$Item=0
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand
$command.CommandTimeout = $iTimeOut
$command.Connection=$connection
$command.CommandText = "SELECT s.Name + '.' + t.name,
SUM(p.rows) AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
s.Name + '.' + t.name"
$Reader = $command.ExecuteReader();
$StringReport = "Table "
$StringReport = $StringReport + "Rows "
$StringReport = $StringReport + "Space "
$StringReport = $StringReport + "Used "
logMsg($StringReport) (1) $false $FileName -bShowDate $false
while($Reader.Read())
{
$Item=$Item+1
$lTotalRows = $Reader.GetValue(1)
$lTotalSpace = $Reader.GetValue(2)
$lTotalUsed = $Reader.GetValue(3)
$lTotalUnUsed = $Reader.GetValue(4)
$StringReport = $Reader.GetValue(0).ToString().PadRight(100).Substring(0,99) + " "
$StringReport = $StringReport + $lTotalRows.ToString('N0').PadLeft(20) + " "
$StringReport = $StringReport + $lTotalSpace.ToString('N0').PadLeft(20) + " "
$StringReport = $StringReport + $lTotalUsed.ToString('N0').PadLeft(20)
logMsg($StringReport) (1) $false $FileName -bShowDate $false
}
$Reader.Close();
return $Item
}
catch
{
$Reader.Close();
logMsg("Not able to run Checking Status per Table..." + $Error[0].Exception) (2)
return 0
}
}
#----------------------------------------------------------------------------------------
# Check query stats and obtain execution count, cpu_time, logical_reads, etc.. per query
#---------------------------------------------------------------------------------------
function CheckQueryStats($connection ,$FileName, $iTimeOut)
{
try
{
logMsg( "---- Checking Query Stats ---- " ) (1) $true $FileName
$Item=0
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand
$command.CommandTimeout = $iTimeOut
$command.Connection=$connection
$command.CommandText = "SELECT
qs.execution_count,
qs.total_worker_time AS cpu_time,
qs.total_logical_reads AS avg_logical_reads,
qs.total_logical_writes AS avg_logical_writes,
qs.total_physical_reads AS avg_physical_reads,
qs.total_elapsed_time AS avg_elapsed_time,
st.text AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st"
$Reader = $command.ExecuteReader();
$StringReport = "Execution Count "
$StringReport = $StringReport + "CPU Time "
$StringReport = $StringReport + "Logical Reads "
$StringReport = $StringReport + "Logical Writes "
$StringReport = $StringReport + "Physical Writes "
$StringReport = $StringReport + "Elapsed Time "
$StringReport = $StringReport + "TSQL "
logMsg($StringReport) (1) $false $FileName -bShowDate $false
while($Reader.Read())
{
$Item=$Item+1
$TSQL = $Reader.GetValue(6).ToString().Trim()
$cleanedString = Replace-SpacesInRange -InputString $TSQL
$StringReport = $Reader.GetValue(0).ToString('N0').ToString().PadLeft(20) + " "
$StringReport = $StringReport + $Reader.GetValue(1).ToString('N0').PadLeft(20) + " "
$StringReport = $StringReport + $Reader.GetValue(2).ToString('N0').PadLeft(20) + " "
$StringReport = $StringReport + $Reader.GetValue(3).ToString('N0').PadLeft(20) + " "
$StringReport = $StringReport + $Reader.GetValue(4).ToString('N0').PadLeft(20) + " "
$StringReport = $StringReport + $Reader.GetValue(5).ToString('N0').PadLeft(20) + " "
$StringReport = $StringReport + $cleanedString
logMsg($StringReport) (1) $false $FileName -bShowDate $false
}
$Reader.Close();
return $Item
}
catch
{
$Reader.Close();
logMsg("Not able to run Checking Query Stats.." + $Error[0].Exception) (2)
return 0
}
}
#-------------------------------------------------------------------------------
# Show the performance counters of the database
#-------------------------------------------------------------------------------
function CheckStatusPerResource($connection,$FileName, $iTimeOut)
{
try
{
logMsg( "---- Checking Status per Resources ---- " ) (1) $true $FileName
$Item=0
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand
$command.CommandTimeout = $iTimeOut
$command.Connection=$connection
$command.CommandText = "select end_time, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, avg_memory_usage_percent, max_worker_percent from sys.dm_db_resource_stats order by end_time desc"
$Reader = $command.ExecuteReader();
$StringReport = "Time "
$StringReport = $StringReport + "Avg_Cpu "
$StringReport = $StringReport + "Avg_DataIO "
$StringReport = $StringReport + "Avg_Log "
$StringReport = $StringReport + "Avg_Memory "
$StringReport = $StringReport + "Max_Workers"
logMsg($StringReport) (1) $false $FileName -bShowDate $false
while($Reader.Read())
{
$Item=$Item+1
$lTotalCPU = $Reader.GetValue(1)
$lTotalDataIO = $Reader.GetValue(2)
$lTotalLog = $Reader.GetValue(3)
$lTotalMemory = $Reader.GetValue(4)
$lTotalWorkers = $Reader.GetValue(5)
$StringReport = $Reader.GetValue(0).ToString().PadLeft(20) + " "
$StringReport = $StringReport + $lTotalCPU.ToString('N2').PadLeft(10) + " "
$StringReport = $StringReport + $lTotalDataIO.ToString('N2').PadLeft(10)
$StringReport = $StringReport + $lTotalLog.ToString('N2').PadLeft(10)
$StringReport = $StringReport + $lTotalMemory.ToString('N2').PadLeft(10)
$StringReport = $StringReport + $lTotalWorkers.ToString('N2').PadLeft(10)
logMsg($StringReport) (1) $false $FileName -bShowDate $false
}
$Reader.Close();
return $Item
}
catch
{
logMsg("Not able to run Checking Status per Resources..." + $Error[0].Exception) (2) $true $FileName
return 0
}
}
function sGiveMeFileName{
Param([Parameter(Mandatory=$true)]
[System.String]$DBAccess,
[Parameter(Mandatory=$true)]
[System.String]$File)
try
{
return $FolderV + $DBAccess + $File
}
catch {
return "_UnKnow.csv"
}
}
try
{
Clear
#--------------------------------
#Check the parameters.
#--------------------------------
if (TestEmpty($server)) { $server = read-host -Prompt "Please enter a Server Name" }
if (TestEmpty($user)) { $user = read-host -Prompt "Please enter a User Name" }
if (TestEmpty($passwordSecure))
{
$passwordSecure = read-host -Prompt "Please enter a password" -assecurestring
$password = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($passwordSecure))
}
else
{$password = $passwordSecure}
if (TestEmpty($Db)) { $Db = read-host -Prompt "Please enter a Database Name, type ALL to check all databases" }
if (TestEmpty($Folder)) { $Folder = read-host -Prompt "Please enter a Destination Folder (Don't include the last \) - Example c:\PerfChecker" }
$DbsArray = [System.Collections.ArrayList]::new()
#--------------------------------
#Variables
#--------------------------------
$CheckStatistics=0
$CheckIndexesAndStatistics=0
$CheckMissingIndexes=0
$CheckScopeConfiguration=0
$CheckStatusPerResource=0
$CheckStatusPerTable=0
$CheckQueryStats=0
$TotalCheckStatistics=0
$TotalCheckIndexesAndStatistics=0
$TotalCheckMissingIndexes=0
$TotalCheckScopeConfiguration=0
$TotalCheckStatusPerResource=0
$TotalCheckStatusPerTable=0
$TotalCheckQueryStats=0
#--------------------------------
#Run the process
#--------------------------------
$timestamp = Get-Date -Format "yyyyMMddHHmmss"
$Folder = $Folder +"\" + $timestamp + "\"
logMsg("Creating the folder " + $Folder) (1)
$result = CreateFolder($Folder) #Creating the folder that we are going to have the results, log and zip.
If( $result -eq $false)
{
logMsg("Was not possible to create the folder") (2)
exit;
}
logMsg("Created the folder " + $Folder) (1)
$sFolderV = GiveMeFolderName($Folder) #Creating a correct folder adding at the end \.
$LogFile = $sFolderV + "PerfChecker.Log" #Logging the operations.
logMsg("Deleting Operation Log file") (1)
$result = DeleteFile($LogFile) #Delete Log file
logMsg("Deleted Operation Log file") (1)
logMsg("-------------------- Header Filter details --------------") (1)
logMsg(" ServerName: " + $server) (1)
logMsg(" DB Filter : " + $DB) (1)
logMsg(" Folder : " + $Folder) (1)
logMsg(" Delete Files: " + $DropExisting) (1)
logMsg(" Elastic DB Pool Name: " + $ElasticDBPoolName) (1)
logMsg("-------------------- Footer Filter details --------------") (1)
if( $DropExisting -eq 1)
{
foreach ($f in ((Get-ChildItem -Path $sFolderV)))
{
if($f.Extension -in (".txt") -or $f.Extension -in (".task") )
{
logMsg("Deleting Operation file: " + $f.FullName) (1)
$result = DeleteFile($f.FullName)
logMsg("Deleted Operation file: " + $f.FullName) (1)
}
}
}
if($Db -eq "ALL")
{
$SQLConnectionSource = GiveMeConnectionSource "master" #Connecting to the database.
if($SQLConnectionSource -eq $null)
{
logMsg("It is not possible to connect to the database") (2)
exit;
}
$commandDB = New-Object -TypeName System.Data.SqlClient.SqlCommand
$commandDB.CommandTimeout = 6000
$commandDB.Connection=$SQLConnectionSource
if(TestEmpty($ElasticDBPoolName))
{
$commandDB.CommandText = "SELECT name from sys.databases where database_id >=5 order by name"
}
else
{
$commandDB.CommandText = "SELECT d.name as DatabaseName FROM sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id WHERE dso.elastic_pool_name = '" + $ElasticDBPoolName + "' ORDER BY d.name"
}
$ReaderDB = $commandDB.ExecuteReader();
while($ReaderDB.Read())
{
[void]$DbsArray.Add($ReaderDB.GetValue(0).ToString())
logMsg("Database Name selected:" + $ReaderDB.GetValue(0).ToString()) (1)
}
$ReaderDB.Close();
$SQLConnectionSource.Close()
}
else
{
$DbsArray.Add($DB)
}
for($iDBs=0;$iDBs -lt $DbsArray.Count; $iDBs=$iDBs+1)
{
logMsg("Connecting to database.." + $DbsArray[$iDBs]) (1)
$SQLConnectionSource = GiveMeConnectionSource($DbsArray[$iDBs]) #Connecting to the database.
if($SQLConnectionSource -eq $null)
{
logMsg("It is not possible to connect to the database " + $DbsArray[$iDBs] ) (2)
exit;
}
logMsg("Connected to database.." + $DbsArray[$iDBs]) (1)
$CheckStatistics=0
$CheckIndexesAndStatistics=0
$CheckMissingIndexes=0
$CheckScopeConfiguration=0
$CheckStatusPerResource=0
$CheckStatusPerTable=0
$CheckQueryStats=0
$FileName=Remove-InvalidFileNameChars($DbsArray[$iDBs])
$CheckStatistics = CheckStatistics $SQLConnectionSource ($sFolderV + $FileName + "_CheckStatistics.Txt") ($sFolderV + $FileName + "_CheckStatistics.Task") (3600)
$CheckIndexesAndStatistics = CheckIndexesAndStatistics $SQLConnectionSource ($sFolderV + $FileName + "_CheckIndexesStatistics.Txt") ($sFolderV + $FileName + "_CheckIndexesStatistics.Task") (3600)
$CheckMissingIndexes = CheckMissingIndexes $SQLConnectionSource ($sFolderV + $FileName + "_CheckMissingIndexes.Txt") ($sFolderV + $FileName + "_CheckMissingIndexes.Task") (3600)
$CheckScopeConfiguration = CheckScopeConfiguration $SQLConnectionSource ($sFolderV + $FileName + "_CheckScopeConfiguration.Txt") ($sFolderV + $FileName + "_CheckScopeConfiguration.Task") (3600)
$CheckStatusPerResource = CheckStatusPerResource $SQLConnectionSource ($sFolderV + $FileName + "_ResourceUsage.Txt") (3600)
$CheckStatusPerTable = CheckStatusPerTable $SQLConnectionSource ($sFolderV + $FileName + "_TableSize.Txt") (3600)
$CheckQueryStats=CheckQueryStats $SQLConnectionSource ($sFolderV + $FileName + "_QueryStats.Txt") (3600)
$TotalCheckStatistics=$TotalCheckStatistics+$CheckStatistics
$TotalCheckIndexesAndStatistics=$TotalCheckIndexesAndStatistics+$CheckIndexesAndStatistics
$TotalCheckMissingIndexes=$TotalCheckMissingIndexes+$CheckMissingIndexes
$TotalCheckScopeConfiguration=$TotalCheckScopeConfiguration+$CheckScopeConfiguration
$TotalCheckStatusPerResource = $TotalCheckStatusPerResource + $CheckStatusPerResource
$TotalCheckStatusPerTable = $TotalCheckStatusPerTable + $CheckStatusPerTable
$TotalCheckQueryStats=$TotalCheckQueryStats+$CheckQueryStats
logMsg("Closing the connection and summary for..... : " + $DbsArray[$iDBs]) (3)
logMsg("Number of Issues with statistics : " + $CheckStatistics ) (1)
logMsg("Number of Issues with statistics/indexes : " + $CheckIndexesAndStatistics ) (1)
logMsg("Number of Issues with Scoped Configuration : " + $CheckScopeConfiguration ) (1)
logMsg("Number of Issues with Missing Indexes : " + $CheckMissingIndexes ) (1)
logMsg("Number of Resource Usage : " + $CheckStatusPerResource ) (1)
logMsg("Number of Tables Usage : " + $CheckStatusPerTable ) (1)
logMsg("Number of Query stats : " + $CheckQueryStats ) (1)
$SQLConnectionSource.Close()
}
Remove-Variable password
logMsg("ReadScale-Out Performance Collector Script was executed correctly") (3)
logMsg("Total Number of Issues with statistics : " + $TotalCheckStatistics ) (1)
logMsg("Total Number of Issues with statistics/indexes : " + $TotalCheckIndexesAndStatistics ) (1)
logMsg("Total Number of Issues with Scoped Configuration : " + $TotalCheckScopeConfiguration ) (1)
logMsg("Total Number of Issues with Missing Indexes : " + $TotalCheckMissingIndexes ) (1)
logMsg("Total Number of Resource Usage : " + $TotalCheckStatusPerResource ) (1)
logMsg("Total Number of Tables Usage : " + $TotalCheckStatusPerTable ) (1)
logMsg("Total Number of Query Stats : " + $TotalCheckQueryStats ) (1)
}
catch
{
logMsg("ReadScale-Out Performance Collector Script was executed incorrectly ..: " + $Error[0].Exception) (2)
}
finally
{
logMsg("ReadScale-Out Performance Collector Script finished - Check the previous status line to know if it was success or not") (2)
}
Disclaimer: This script is provided as an example and should be used at the user's own risk. It must be thoroughly tested before being used in a production environment.
