Capturing Execution Plans for Overnight Processes

Scenario: I want to capture execution plans for a particular overnight process. Maybe I want to check what indexes a query is using at 3AM and compare its index usage at 7AM

Add the following code to your procedure:

DECLARE @xyz VARBINARY(MAX) = CAST(‘ANY TEXT HERE’ AS VARBINARY(MAX))
SET CONTEXT_INFO @xyz

Select @xyz and obtain the variable value

For example: 0x646174616D6172742E7573705F646D5F637573746F6D6572726567697374726174696F6E

Remove the initial 0x and make a note of the rest of the value

Create an Extended Event

add event “Query Post Execution Showplan”

add filter sqlserver.context_info equal binary data 646174616D6172742E7573705F646D5F637573746F6D6572726567697374726174696F6E

add an event file to store the output and start the Extended Event. Dont use ring buffer, I cant get the process to work with ring buffer although admitedly I havent really persevered.

Let the overnight process complete and the next day right click the event file and View Target Data, click on a record and change to Query Plan tab in the bottom half of your screen to view the query plan.

Detecting Database access via AD group membership

SET NOCOUNT on

SELECT id=IDENTITY (int, 1,1),’exec xp_logininfo ”’ +name +”’,”members”’ code

INTO #t

FROM sys.server_principals WHERE type = ‘G’

–SELECT * FROM #t

CREATE TABLE #r (account_name VARCHAR(100),type_desc VARCHAR(100),privilege VARCHAR(100),mapped_login_name VARCHAR(100),permission_path VARCHAR(100))

DECLARE @maxid INT = (SELECT MAX(id) FROM #t)

DECLARE @currentid INT = 1

WHILE @currentid<=@maxid

BEGIN

DECLARE @code VARCHAR(MAX) = (SELECT code FROM #t WHERE id = @currentid)

–PRINT @code

INSERT INTO #r

EXEC (@code)

SET @currentid = @currentid +1

END

IF OBJECT_id('database_name.dbo.permissions_table') IS NULL

SELECT *,GETDATE() date_captured

INTO ('database_name.dbo.permissions_table

FROM #r

IF OBJECT_id('('database_name.dbo.permissions_table_audit') IS NULL

SELECT TOP 0 *,GETDATE() date_captured, 'A' record_type

INTO ('database_name.dbo.permissions_table_audit

FROM #r

–detect additons

SELECT *,GETDATE() date_captured

INTO #additions

FROM

(

SELECT * FROM #r

EXCEPT

SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path

FROM ('database_name.dbo.permissions_table

) a

INSERT INTO ('database_name.dbo.permissions_table_audit

SELECT *,'A' FROM #additions

INSERT INTO ('database_name.dbo.permissions_table

SELECT * FROM #additions

–detect deletions

SELECT *,GETDATE() date_captured

INTO #deletions

FROM

(

SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path

FROM ('database_name.dbo.permissions_table

EXCEPT

SELECT * FROM #r

) a

INSERT INTO ('database_name.dbo.permissions_table_audit

SELECT *,'D' FROM #deletions

DELETE FROM ('database_name.dbo.permissions_table

FROM ('database_name.dbo.permissions_table

INNER JOIN (

SELECT * FROM

(SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path FROM #deletions

INTERSECT

SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path FROM ('database_name.dbo.permissions_table) a ) a

ON a.account_name = permissions_table.account_name AND a.permission_path = permissions_table.permission_path