Skip to main content
Matrix42 Self-Service Help Center

Database Performance Issues Caused by Long SQL Compile Times (Poor Performance in LCM Processing)

Overview

If you encounter poor performance during LCM processing (less than 10 objects/min for a long time), one possible reason is a very high compile time of queries on your SQL server.
This is very often found on SQL server versions before 2012. We describe ways how to find out if your server is behaving this way and how you can solve it with some configuration settings.

Cause

In version 8.1.4 we changed the behaviour of the query processing for more complex SQL statements.

We encountered several cases of so-called "parameter sniffing" issues, especially in LCM processing. If not suppressed by special settings, SQL server will cache your SQL statements and how the statements are executed in the plan cache (preparing a statement for execution is the "parse and compile" phase). This is, in general, a very good thing as preparing a statement for execution always needs time.
But for some edge cases, the performance of these cached statements can be extremely bad. A possible business example is first calculating the licensing of a very special software that is only present on 5 computers and then using the same statements for an e-mail program that is installed on 50,000 machines. Optimization for one case might be very bad for the other case. This is only a very rough description, while on the physical layer it is more complicated.

For more complex statements, the danger of bad optimization is increasing. Therefore, we added an option to the generated SQL to suppress plan caching in case the statements get more complicated. SQL server will now parse and compile the complex statements on every call to always produce the best plan for the statement.

However, on some SQL environments, we see a dramatically bad performance for the compile phase. 90% of the execution time is spent on generating the plan.

Resolution

In version 9.0.0 and higher, you can change the complexity metric for suppressing the plan caching. The default value is 7. If you change this to 100, you will probably never see any statements that are not cached. This abstract number will be used by our logic after evaluating the complexity of a statement as a threshold.

The value can be changed in this configuration file located in the bin folder of your installation:

update4u.SPS.DataLayer.dll.config

It is an XML file and you have to change (or add) the attribute RecompileThreshold in the section QueryOptimization.

  <QueryOptimization 
        GuidListAutoParametrization="50" 
        RecompileThreshold="7" />

More Information

To find out if this issue is present on your system, you can run one of the statements of the LCM processing engine manually and check the execution statistics.
This is a possible test statement. It uses very general parameters so you should see the effect. It will not change any data, so there is no risk executing it. If it takes too long on your system, you can always cancel it. Expected runtime is not longer than 10 seconds.

set statistics time on

exec sp_executesql N'
WITH __actualSet AS 
( 
    SELECT [T-0].[ID] AS [ID]  FROM  [dbo].[LCMLicenseDemandClassLicenseAssignment] AS [T-0] 
/*W-1*/ LEFT JOIN [dbo].[SPSAssetClassLicense] AS [T-0-1] ON [T-0].[AssignedLicense] = [T-0-1].[ID] 
/*W-3*/ LEFT JOIN [dbo].[SPSSoftwareClassBase] AS [T-0-1-0] ON [T-0-1].[Software] = [T-0-1-0].[ID] 
/*W-4*/ LEFT JOIN [dbo].[SPSCommonClassBase] AS [T-0-1-0-0] ON ([T-0-1-0].[Expression-ObjectID] = [T-0-1-0-0].[Expression-ObjectID])
/*W-7*/ LEFT JOIN [dbo].[LCMLicenseDemandClassBase] AS [T-0-2] ON ([T-0].[Expression-ObjectID] = [T-0-2].[Expression-ObjectID])
/*W-8*/ LEFT JOIN [dbo].[SPSSoftwareClassBase] AS [T-0-2-0] ON [T-0-2].[Software] = [T-0-2-0].[ID] 
/*W-9*/ LEFT JOIN [dbo].[SPSCommonClassBase] AS [T-0-2-0-0] ON ([T-0-2-0].[Expression-ObjectID] = [T-0-2-0-0].[Expression-ObjectID])
/*W-19*/ LEFT JOIN [dbo].[SPSCommonClassBase] AS [T-0-3] ON ([T-0].[Expression-ObjectID] = [T-0-3].[Expression-ObjectID])
 WHERE (((((((([T-0-1].[LicenseModel] = @SelectParameter0) AND ([T-0-1-0-0].[OU] = @SelectParameter1)) AND ([T-0-1].[Software] <> @SelectParameter2)) 
 AND ([T-0-1].[Type] <> 20)) AND ([T-0].[Exclusive] = 0)) AND ([T-0-2-0-0].[OU] = [T-0-1-0-0].[OU])) AND
  ([T-0-2].[Software] <> [T-0-1-0].[ID]))
   AND (( 
   (SELECT TOP 1 [A-ou].[Depth] FROM [dbo].[Schema-SPSOrgUnitType-SPSCommonClassBase-OU] [A-ou]
     WHERE ([A-ou].[IDBase] = [T-0-3].[OU]) )  + 
   (SELECT TOP 1 [A-loc].[Depth] FROM [dbo].[Schema-SPSLocationType-SPSCommonClassBase-Location] [A-loc]
     WHERE ([A-loc].[IDBase] = [T-0-3].[Location]) ) ) > 4))
)
SELECT 
/*ID*/ [T-0].[ID] AS [ID] 
,/*Count*/ [T-0].[Count] AS [Count] 
,/*ObjID*/ [T-0].[Expression-ObjectID] AS [ObjID]

FROM
[dbo].[LCMLicenseDemandClassLicenseAssignment] AS [T-0]
WHERE [T-0].[ID] IN (
SELECT ID FROM __actualSet )

OPTION (recompile) ;
;
',N'@SelectParameter0 uniqueidentifier,@SelectParameter1 uniqueidentifier,@SelectParameter2 uniqueidentifier',
@SelectParameter0='3E2EC97F-814E-41A5-8950-E394E519E3F4',
@SelectParameter1='4DF735F2-B4BC-4CE8-92EE-60F40FCF5653',
@SelectParameter2='EA53C72A-6F34-E211-F6BF-E61F1359F33B'

Run this query at least 2 times to reduce the effect of any caching. The result of the query is not important, but the messages are interesting.
You should see something like the following (times and rows will of course differ):

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
(10680 row(s) affected)
 SQL Server Execution Times:
   CPU time = 282 ms,  elapsed time = 311 ms.
 SQL Server Execution Times:
   CPU time = 2876 ms,  elapsed time = 2906 ms.

Copy this result to notepad or some other editor so that you can compare it with the next execution.
Now remove the line OPTION (recompile) from the query and execute it again 2 times.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
(10680 row(s) affected)
SQL Server Execution Times:
   CPU time = 343 ms,  elapsed time = 281 ms.
SQL Server Execution Times:
   CPU time = 343 ms,  elapsed time = 281 ms.

 The examples shown here are from a server that has very long compile times. You can see that the total execution time of the query in the last line with the recompile option is nearly 10 times longer than without it.
Acceptable would be 50% or even 100% longer, but 10 times is a critical performance issue.

  • Was this article helpful?