2

We frequently use query store force plan feature.
We would like to audit when and who forced each plan.
Is extended events session is needed auditing this event,
or there are dmvs or catalog views with this specific information?

9

You could create some auditing on the execution of the sys.sp_query_store_force_plan procedure.

Create the server audit

USE master ;  
GO  
-- Create the server audit.   
CREATE SERVER AUDIT ServerAudit  
    TO FILE ( FILEPATH =   
'D:\AuditFiles\' ) ;   
GO  
-- Enable the server audit.   
ALTER SERVER AUDIT ServerAudit   
WITH (STATE = ON) ;  

Create the database audit specification

CREATE DATABASE AUDIT SPECIFICATION Audit_Plan_force
FOR SERVER AUDIT ServerAudit  
ADD (EXECUTE
     ON sys.sp_query_store_force_plan BY public)   
WITH (STATE = ON) ;   
GO  

Force a plan in a user database

enter image description here

Query the audit file

SELECT   event_time,Action_id,succeeded,server_principal_name,database_name,object_name,Statement
FROM fn_get_audit_file('D:\AuditFiles\*',default,default);

Result enter image description here

  • Probably will go for extended events session, any idea if in 2017 auto force plan if it will be the same – Michael Cherevko Sep 18 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.