none
Database Experimentation Assistant 2.6 results are not as expected RRS feed

  • Question

  • Hello Guys,

    I'm trying out DEA 2.6 for SQL Server upgrade and had a test run in two identical machines. The results are surprisingly different with analysis report showing up graphs with 45-56% degraded performance. Below are the specs and options tested.

    Specs: Server A, B

    SQL Server 2016 SP1 CU8

    RAM: 48 GB

    CPU: 16 core

    Tested Options:

    1. Trace collected on server A and replayed on server B. Report shows 56% degradation.

    2. Upgraded server B to SQL Server 2016 SP2 CU2 and restored initial backup of DB (server A) and replayed trace of A. Analysis report shows 49% degradation.

    Any help/suggestions in this area are highly appreciated.

    Thanks,

    Vamshi

    Sunday, September 9, 2018 12:56 PM

Answers

  • Hi Vamshi,

    You mention in (1) that report shows 56% degradation, but that you only replayed to B?

    As far as the setup for DEA goes, you need to first capture on your source server (A).  Using that captured trace (Trace A), you replay it to target server 1 (A' - same version as source server).  You also replay Trace A to target server 2 (B - new version of SQL).  The results of these replays (A' trace files and B trace files) you then compare in analysis. 

    If you do the above, you'll be comparing A' vs B and that will result in only one degradation metric (shown in report by execution count and by distinct query count).

    If you need more guidance on the setup, check out the DEA blog: https://blogs.msdn.microsoft.com/datamigration/2018/08/06/release-database-experimentation-assistant-dea-v2-6/

    Hope that helps!

    • Marked as answer by V2KRSNA2 Wednesday, September 19, 2018 11:39 AM
    Thursday, September 13, 2018 12:50 AM
  • Thanks Jain for your inputs. I was trying out multiple things and this is one point actually how DEA works. Basic assumption was to replay trace from server A to B, but replay has 15x load that's incomparable to initial trace.However, it worked and able to see the results.

    • Marked as answer by V2KRSNA2 Wednesday, September 19, 2018 11:39 AM
    Wednesday, September 19, 2018 11:39 AM

All replies

  • Did someone explore this feature. Please share if you have positive results.
    Wednesday, September 12, 2018 7:54 AM
  • Hi Vamshi,

    You mention in (1) that report shows 56% degradation, but that you only replayed to B?

    As far as the setup for DEA goes, you need to first capture on your source server (A).  Using that captured trace (Trace A), you replay it to target server 1 (A' - same version as source server).  You also replay Trace A to target server 2 (B - new version of SQL).  The results of these replays (A' trace files and B trace files) you then compare in analysis. 

    If you do the above, you'll be comparing A' vs B and that will result in only one degradation metric (shown in report by execution count and by distinct query count).

    If you need more guidance on the setup, check out the DEA blog: https://blogs.msdn.microsoft.com/datamigration/2018/08/06/release-database-experimentation-assistant-dea-v2-6/

    Hope that helps!

    • Marked as answer by V2KRSNA2 Wednesday, September 19, 2018 11:39 AM
    Thursday, September 13, 2018 12:50 AM
  • Thanks Jain for your inputs. I was trying out multiple things and this is one point actually how DEA works. Basic assumption was to replay trace from server A to B, but replay has 15x load that's incomparable to initial trace.However, it worked and able to see the results.

    • Marked as answer by V2KRSNA2 Wednesday, September 19, 2018 11:39 AM
    Wednesday, September 19, 2018 11:39 AM