Sql-server – SQL PROFILER Replay statistics event interpretation

profilersql serversql-server-2012sql-server-2016

I tried capturing a replayable trace on a SQL Server 2012 production environment and then replay it against a SQL 2016 test environment.

After doing so , I got the following results :

enter image description here

Can anyone help me identify what each of the below highlighted term represents :

  • Total events = 89
  • Replayed events = 39
  • Provider errors = 13
  • Internal errors = 26
  • Total errors = 39
  • Provider hit ratio = 66.67 %
  • Internal hit ratio = 33.33 %
  • Overall hit ratio = 0.00 %
  • Replay time = 0 hr 2 min 12 sec 38 ms

Will be of great help as I am not able to understand how the upgrade to 2016 might perform based on the profiler results.

Best Answer

I am afraid this replay will not help you at all. It will not give you any indication about performance in your new test server as all the replay events failed.

I recommend you read this when you take your trace at source, especially following sections:

Event Classes Required for Replay

Data Columns Required for Replay

I will use these terms so I do not repeat the same thing:

SQL Server 2012 production environment = Source

SQL 2016 test environment = Destination

  • Total events = Number of events that was captured from source
  • Replayed events = Number of events replayed at destination
  • Provider errors = Look at the textdata column for details on each provider warnings or error
  • Internal errors = Look at Errors and Warnings column for details
  • Total errors = Sum(Provider errors + Internal errors)
  • Provider hit ratio = Ration between Replayed events and Providers errors
  • Internal hit ratio = Ration between Replayed events and Internal errors
  • Overall hit ratio = Meaning out of 39 Replayed events all of those timed out either and Provide errors or Internal error. In your case it 13+26=39.
  • Replay time = Time took to replay trace at destination.

You can load your test result into a table and then you should be easily able find details about errors. As an example for Provider Errors you can use following query which I copied from this blog post.

SELECT RowNumber,
       EventClass,
       TextData,
       ConnectionID,
       SPID,
       EventSubclass,
       RequestProperties,
       RequestParameters,
       BinaryData
  FROM dbo.Trace_Replay_Results
 WHERE EventClass = 63485
Related Question