sql server – What is the best way to have actual execution plan and IO stats as values to insert?

We have idea to create procedure which will test performance of query and insert specific information associated with executed query to table with test performance history ((dbo).(TestHistory). We would like to collect:

  1. (ActualExecutionPlan) (XML) NOT NULL
  2. (LogicalReads) (BIGINT) NOT NULL
  4. (ElapsedTime) (BIGINT) NULL,
  5. (StartProcedureTime) (DATETIME2)(7) NOT NULL
  6. (EndProcedureTime) (DATETIME2)(7) NOT NULL
  7. (IndexNamesInDynamicSqlTableOrView) (VARCHAR)(8000)
  8. (RowCount) (INT) NOT NULL
  9. (ViewDefinition) (VARCHAR)(MAX) NULL

And insert these values after every execution of procedure with parameter.

So idea is to create procedure (dbo).(TestQuery) with parameter @sqlQuery varchar(max). @sqlQuery will be query to execute as dynamic sql with header -> SET STATISTICS XML,IO,TIME ON.


Do you have idea how to get:

  1. (ActualExecutionPlan) as xml
  2. (LogicalReads)
  3. (CPUTime)
  4. (ElapsedTime)

As values to insert to (dbo).(TestHistory) table ? Is it any possibility to get it from “Messages” tab and result from SET STATISTICS XML ON ?