Possible SQL issues affecting SCOM performance

  • Article

 

Good reasons for a Risk Assessment

 

SQL RAS runs 800+ queries to check on target SQL servers

Check Best Practice Recommendations (BPR)

 

May be good opportunity to audit the SQL build for BPR!

 

 

 

Ran across some good examples where SQL settings brought SCOM to a standstill

One was Cardinality Estimation - basically, predicts how many rows a query will return

Part of SQL since 1998 with SQL Server v7.0

 

Let's figure out what SQL2016 runs OoB (out of box)

 

SQL 2016

SELECT ServerProperty('ProductVersion');
GO 

SELECT name, value FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
GO

 

 

The other is CLR Strict Security

SELECT * FROM sys.configurations

WHERE name = 'clr enabled'

 

 

Talking with Shawn Nakhostin - SQL PFE, we discussed opportunities and questions around SQL optimization and best practices.

Shawn gave me the following feedback on customer performance issues:

I've found some customers who have had performance issues with SQL based on organizational SQL settings:

  1. Trace flag 9481
  2. CLR Strict Security is by default enabled

 

Trace flag 9481

Enabling or disabling this TF is not a matter of best practice.

The customer should see what works for them.

Here is the explanation:

Customer started using a new cardinality estimator in SQL Server 2014.

The product team knew that the new CE improved some of the query plans, but not all of them. In other words, they knew that this would improve overall query performance in “some” environments but might have a different impact in other environments.

For this reason, they created TF 9481 so that environments that see query performance degradation after upgrading SQL Server from version 2012 and earlier, they can turn on this trace flag so that the query optimizer uses the old algorithm for CE.

Note:-Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version) of the cardinality estimator when creating the query plan. https://blogs.technet.microsoft.com/dataplatform/2017/03/22/sql-server-2016-new-features-to-deal-with-the-new-ce/ https://support.microsoft.com/en-in/help/2801413/enable-plan-affecting-sql-server-query-optimizer-behavior-that-can-be

 

CLR Strict Security is by default enabled

This causes all assemblies to be treated as unsafe.

As a result, assemblies will not load.

To get the assemblies to load they can do one of the following:

  • Sign the assembly. This may work if you have a few assemblies but becomes a huge task if there are many assemblies to sign.
  • Set the TRUSTWORTHY database property to on.
    • This is not recommended because in some form defeats the purpose of using CLR Strict Security.
  • Add the assembly to the trusted assemblies list.
    • This is called whitelisting, which may be a better option than the previous two.

/en-us/sql/database-engine/configure-windows/clr-strict-security?view=sql-server-2017