Improvements to SCOM console and SqUp performance

Dashboard Server
, ,
1

This isn’t a question as such, more a tip! You may, or may not, be aware of changing the Max Degree of Parallelism in SQL SSMS to improve the SCOM console performance:

http://thoughtsonopsmgr.blogspot.co.uk/2014/12/scom-2012x-console-on-steroids-try-mdop.html

When I tried that it didn’t really help that much, but what did make a difference in conjunction with the above was to change the 'cost threshold for parallelism’ setting as well, in my case from 5 to 50. (Our SQL server is running on VMWare, YMMV etc.)

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option#Recommendations

This appears to have improved my Operations console performance and the speed in which Squared Up displays dashboard data (on top of performance improvements in 3.2.1).

1 Like
2

One of my colleagues has come up with the following to set the cost threshold for parallelism to 50 and set the max degree of parallelism based on your core count. You’ll need to change where the logs output to.

<#New Cost & MAXDOP Settings#>
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$core = Get-WMIObject -class Win32_processor | Measure-Object -Property numberofcores -Sum
$coretotal = $core.Sum
If ($coretotal -gt 8) {$coretotal= "8"}
$conn = New-Object System.Data.SqlClient.SqlConnection "Server=localhost;Database=master;Integrated Security=True";
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $event.Message | Out-File c:\outfile.txt -Append}; 
$conn.add_InfoMessage($handler); 
$conn.FireInfoMessageEventOnUserErrors = $true;
$conn.Open();
$query = "sp_configure 'show advanced option', '1';RECONFIGURE WITH OVERRIDE;EXEC sp_configure 'cost threshold for parallelism', '50';RECONFIGURE WITH OVERRIDE;EXEC sp_configure 'max degree of parallelism', '$coretotal';RECONFIGURE WITH OVERRIDE;"
$cmd = $conn.CreateCommand()
$cmd.CommandText = $query
$cmd.ExecuteNonQuery()
$conn.Close()
3

How does your vmwaremachine look? How many CPU:s in the virtual machine and on the host itself?

4

“This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.” - Sounds like I need to find a DBA to assist me :frowning:

5

@jannep 4 CPUs on the virtual machine, not sure about the host

6

I didn’t even know this was a thing. Thanks for bringing it to my attention.

I just did a huge amount of reading on this, made some changes and now things are running much better

7

That’s awesome! Thanks for sharing!