Identical Servers and data but performance isn’t identical – Why?
A lot of times, you have nearly identical database servers for an application running in Production, Test and Development but you may notice performance differences between them for the same data/queries that you could not attribute to any reason since CPU, Memory, Disk etc., may all be identical.
The difference is likely in the SQL Server configuration itself. For example the “Max Degree of Parallelism” may be the default value in one server but tuned in the other server.
Please understand that I am not ruling out other differences like Indexes, Table/Index Statistics, User/Job Workload, Blocking/Locking, TempDB size/configuration/file layout, Database file layout/distribution on the disks and their speed etc., but just saying that the instance configuration is a main source of difference in performance for identical servers with identical databases and data.
sys.configurations – SQL Server configuration
This view has a row of data for each setting corresponding to the each server-wide configuration option/value. By comparing and contrasting the data in the view between two SQL Server instances, we can quickly identify the configuration differences.
How to compare the configuration two servers?
I have written about this already in a post a while back but every time try to use it, it is a bit tedious as I have to setup Linked servers. The dbatools based PowerShell method I outline below is quick and elegant
Show me the code!
Just substitute the server names and file path at the beginning of the script and run to view the results and export to csv!
#Setup the source and target instance names to compare configuration data
#-------------------------------------------------------------------------
$sourceInstance = 'MyProductionServer'
$targetInstance = 'MyTestServer'
$exportPath = "C:\Temp"
$fileName = "$($sourceInstance)_vs_$($targetInstance).$($(Get-Date).ToString('yyyyMMdd_hh24mmss'))"
$exportCSVFileName = "$fileName.csv"
$exportCSVDiffFileName = "$fileName.Diff.csv"
$exportCSVFileFullPathAndName = Join-Path -Path $exportPath -ChildPath $exportCSVFileName
$exportCSVDiffFileFullPathAndName = Join-Path -Path $exportPath -ChildPath $exportCSVDiffFileName
#Get and save the configuration data from soruce and target and save to TempDB of target
#-------------------------------------------------------------------------
$tableName = "DBASysConfigurations"
$tableNameDiff = "DBASysConfigurationsDiff"
$query = "select @@servername as ServerName, *
from sys.configurations"
$dataTable = Invoke-DbaQuery -SqlInstance $sourceInstance -Database master -Query $query -As DataTable
$dataTable | Write-DbaDbTableData -SqlInstance $targetInstance -Database tempdb -Table $tableName -AutoCreateTable
$dataTable = Invoke-DbaQuery -SqlInstance $targetInstance -Database master -Query $query -As DataTable
$dataTable | Write-DbaDbTableData -SqlInstance $targetInstance -Database tempdb -Table $tableName -AutoCreateTable
#Save the actual configuration values of both servers now in our table to csv
#-------------------------------------------------------------------------
$query = "select * from $tableName"
Invoke-DbaQuery -SqlInstance $targetInstance -Database tempdb -Query $query -As DataTable |
Export-CSV $exportCSVFileFullPathAndName -NoTypeInformation -Delimiter ','
#Get and save the configuration data differences into a table on TempDb of target
#-------------------------------------------------------------------------
$query = "select
src.ServerName AS SourceServer,
dest.ServerName AS TargetServer,
src.configuration_id,
src.name,
src.minimum,
src.maximum,
src.description,
MAX(src.value_in_use) AS Source_Value,
MAX(dest.value_in_use) AS Target_Value
From dbasysconfigurations src
INNER JOIN dbasysconfigurations dest
ON src.configuration_id = dest.configuration_id
WHERE
src.ServerName = '$sourceInstance'
AND dest.ServerName = '$targetInstance'
AND src.value_in_use <> dest.value_in_use
GROUP BY
src.ServerName, --AS SourceServer,
dest.ServerName, --AS TargetServer,
src.configuration_id,
src.name,
src.minimum,
src.maximum,
src.description
ORDER BY
src.configuration_id"
$dataTable = Invoke-DbaQuery -SqlInstance $targetInstance -Database tempdb -Query $query -As DataTable
$dataTable | Write-DbaDbTableData -SqlInstance $targetInstance -Database tempdb -Table $tableNameDiff -AutoCreateTable
#View the differences in a GridView
$dataTable | ogv
#Save the differences from the difference table to csv
#-------------------------------------------------------------------------
$query = "select * from $tableNameDiff"
Invoke-DbaQuery -SqlInstance $targetInstance -Database tempdb -Query $query -As DataTable |
Export-CSV $exportCSVDiffFileFullPathAndName -NoTypeInformation -Delimiter ','
#Cleanup
#-------------------------------------------------------------------------
$query = "DROP TABLE $tableName;
DROP TABLE $tableNameDiff;"
Invoke-DbaQuery -SqlInstance $targetInstance -Database tempdb -Query $query -As DataTable
The output
In my case, the output looked something like this and I was happy to quickly identify and reconcile the differences as I saw fit.
The script also exported the actual values and differences to C:\Temp as CSV for review!
SQL Jana 
One thought on “PowerShell & SQL Server – Compare Configuration of Two SQL Server Instances”