2

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.

0

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 
  • Hi ! Thanks for the answer! Will try the above. – Sayantani Nath May 7 '18 at 7:11
  • If this answers your question please mark it as accepted so other users can be benefited. – SqlWorldWide May 8 '18 at 12:55

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.