Dynamics Community Forum Thread Details

Retrieving Audit History for a record generates a somewhat expensive query that depending on how many rows audit table contains, it can lead to timeouts.

Introduced on v9.0 onprem version, such single record audit retrieves started using a query hint of type: OPTION (OPTIMIZE FOR UNKNOWN).

This was a decision based on CRM online metrics (absorved into onprem branch) as the most performing way to get this data with sucess.

v9.0 sample query:

exec sp_executesql N'select 
top 51 "audit0".AuditId as "auditid"
, "audit0".AttributeMask as "attributemask"
, "audit0".ChangeData as "changedata"
, "audit0".CreatedOn as "createdon"
, "audit0".Action as "action"
, "audit0".Operation as "operation"
, "audit0".CallingUserId as "callinguserid"
, "audit0".UserId as "userid"
, "audit0".ObjectId as "objectid"
, "audit0".ObjectTypeCode as "objecttypecode"
, "audit0".CallingUserIdName as "callinguseridname"
, "audit0".UserIdName as "useridname"
, "audit0".ObjectIdName as "objectidname" 
from
 Audit as "audit0" WITH (NOLOCK)  
where
 ((("audit0".ObjectTypeCode = @ObjectTypeCode0 and "audit0".ObjectId = @ObjectId0) or ("audit0".Action = @Action0 and "audit0".ObjectTypeCode = @ObjectTypeCode1 and "audit0".ObjectId = @ObjectId1) or ("audit0".Action = @Action1 and "audit0".ObjectTypeCode = @ObjectTypeCode2 and "audit0".ObjectId = @ObjectId2) or ("audit0".Action = @Action2 and "audit0".ObjectTypeCode = @ObjectTypeCode3 and "audit0".ObjectId = @ObjectId3 and "audit0".AttributeMask like @AttributeMask_Leading_WC_Suffix0)) and ("audit0".CreatedOn >= @CreatedOn0)) 
order by
 "audit0".CreatedOn desc
, "audit0".AuditId desc 
OPTION (OPTIMIZE FOR UNKNOWN)',N'@ObjectTypeCode0 int,@ObjectId0 uniqueidentifier,@Action0 int,@ObjectTypeCode1 int,@ObjectId1 uniqueidentifier,@Action1 int,@ObjectTypeCode2 int,@ObjectId2 uniqueidentifier,@Action2 int,@ObjectTypeCode3 int,@ObjectId3 uniqueidentifier,@AttributeMask_Leading_WC_Suffix0 nvarchar(1000),@CreatedOn0 datetime',@ObjectTypeCode0=1,@ObjectId0='A5900D9B-CA46-EC11-97A9-00155D015503',@Action0=102,@ObjectTypeCode1=1,@ObjectId1='00000000-0000-0000-0000-000000000000',@Action1=103,@ObjectTypeCode2=1,@ObjectId2='00000000-0000-0000-0000-000000000000',@Action2=104,@ObjectTypeCode3=1019,@ObjectId3='00000000-0000-0000-0000-000000000000',@AttributeMask_Leading_WC_Suffix0=N'%,156,%',@CreatedOn0='2021-11-16 10:47:35'

Depending on a customer Audit data distribution, this change may lead to timeouts.

In order to confirm if this option hint is causing problems, we can enable a SQL profiler, trigger a timeout from CRM UI, copy the query from profiler and then remove the query hint.

If we get a confirmation that this improves the query performance, we should then work to remove the hint.

If we are on v9.1 ONPREM version, that is somewhat easy. We introduced there a Feature Control Bit ( FCB) that can stop this behavior.

The Feature name is: FCB.DisableAuditOptimizeForUnknown. If true, it won't trigger the option hint. If false on it doesn't exist, it does not.

In case you are on V9.0, this isn't as easy as above. There is no CRM application way to remove the hint.
This leave us just one option: SQL side.

We can use the method described here:

Summarizing:

1. Get the plan handle and create the initial plan guide:

Execute on CRM DB (change your to your DBname)

SELECT SUBSTRING(st.text, (qs.statement_start_offset/2) 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)   1) AS statement_text,
qs.sql_handle, qs.statement_start_offset, qs.plan_handle,
db_name(CONVERT(INT, pa.value)) as db,
qs.creation_time, qs.last_execution_time
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE st.text like '%PTION (OPTIMIZE FOR UNKNOWN)%'
AND pa.attribute='dbid'
AND db_name(CONVERT(INT, pa.value)) = 'crmv91_MSCRM'

Replace the plan_handle values and statement_start_offset into:

USE crmv91_MSCRM
exec sp_create_plan_guide_from_handle @name = N'demo plan guide',
@plan_handle = 0x06000D00ACDC320C80B3D8139E01000001000000000000000000000000000000000000000000000000000000,
@statement_start_offset = 612

2. Edit the plan guide, remove the hint and save it with a new name.

Once we create the plan guide, it will appear here:

We can then script output the plan guide in Create mode and remove all content from this value:

There is a major important note at this step.

Script Outputing a plan guide around these type of queries does not generate the correct SQL statement in order to be picked up automatically.

There is some character difference here which i cannot explain, which can only be solved by grabbing again the original query from SQL profiler and paste it again on the parameters @stmt and @module_or_batch

Copy from:

Paste into the plan guide:

Then create the plan guide. If you have an error, make sure there is no plan guide from previous attempts.

If you then execute the same query from SQL profiler and enable executionPlan, you will then see the plan being used:

Final notes:

- Since these audit retrieves TOP statement is based on user personal options pagination, you would have to this for all the 5 types of pagination (26-51-76-101-251)