{"id":4555,"date":"2018-08-01T15:14:35","date_gmt":"2018-08-01T21:14:35","guid":{"rendered":"https:\/\/kevinjustin.com\/blog\/?p=4555"},"modified":"2019-08-20T11:37:21","modified_gmt":"2019-08-20T15:37:21","slug":"possible-sql-issues-affecting-scom-performance","status":"publish","type":"post","link":"https:\/\/kevinjustin.com\/blog\/2018\/08\/01\/possible-sql-issues-affecting-scom-performance\/","title":{"rendered":"Possible SQL issues affecting SCOM performance"},"content":{"rendered":"

\"\"<\/p>\n

 <\/p>\n

Good reasons for a Risk Assessment<\/p>\n

 <\/p>\n

SQL RAS runs 800+ queries to check on target SQL servers<\/p>\n

Check Best Practice Recommendations (BPR)<\/p>\n

 <\/p>\n

May be good opportunity to audit the SQL build for BPR!<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

Ran across some good examples where SQL settings brought SCOM to a standstill<\/p>\n

One was Cardinality Estimation <\/a>– basically, predicts how many rows a query will return<\/p>\n

Part of SQL since\u00a01998 with SQL Server v7.0<\/p>\n

 <\/p>\n

Let’s figure out what SQL2016 runs OoB (out of box)<\/p>\n

 <\/p>\n

SQL 2016<\/strong><\/p>\n

SELECT<\/span> ServerProperty(‘ProductVersion’<\/span>);
\nGO\u00a0 <\/span><\/p>\n


\nSELECT<\/span> <\/span>name<\/span>, <\/span>value<\/span>
\n<\/span>FROM<\/span> sys.database_scoped_configurations
\n<\/span>WHERE<\/span> <\/span>name<\/span> = <\/span>‘LEGACY_CARDINALITY_ESTIMATION’<\/span>;
\nGO<\/span>
\n<\/span><\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

The other is CLR Strict Security<\/p>\n

SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> <\/span>sys<\/span>.<\/span>configurations<\/span><\/p>\n

WHERE<\/span> <\/span>name<\/span> <\/span>=<\/span> <\/span>‘clr enabled’<\/span><\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

Talking with Shawn Nakhostin – SQL PFE, we discussed opportunities and questions around SQL optimization and best practices.<\/p>\n

Shawn gave me the following feedback\u00a0on customer performance issues:<\/strong><\/p>\n

I’ve found some customers who have had performance issues with SQL based on organizational SQL settings:<\/p>\n

    \n
  1. Trace flag 9481<\/li>\n
  2. CLR Strict Security is by default enabled<\/li>\n<\/ol>\n

     <\/p>\n

    Trace flag 9481<\/strong><\/p>\n

    Enabling or disabling this TF is not a matter of best practice.<\/p>\n

    The customer should see what works for them.<\/p>\n

    Here is the explanation:<\/p>\n

    Customer\u00a0started using a new cardinality estimator in SQL Server 2014.<\/p>\n

    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 \u201csome\u201d environments but might have a different impact in other environments.<\/p>\n

    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.<\/p>\n

    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.<\/a><\/span><\/p>\n

    https:\/\/blogs.technet.microsoft.com\/dataplatform\/2017\/03\/22\/sql-server-2016-new-features-to-deal-with-the-new-ce\/<\/a><\/p>\n

    https:\/\/support.microsoft.com\/en-in\/help\/2801413\/enable-plan-affecting-sql-server-query-optimizer-behavior-that-can-be<\/a><\/p>\n

     <\/p>\n

    CLR Strict Security is by default enabled<\/strong><\/p>\n

    This\u00a0causes all assemblies to be treated as unsafe.<\/p>\n

    As a result, assemblies will not load.<\/p>\n

    To get the assemblies to load they can do one of the following:<\/p>\n