none
Database Experimentation Assistant - Question about analysis report RRS feed

  • Question

  • I used Database Experimentation Assistant and captured an 8 hour trace of my production on-premise SQL Server. It includes lots of stored procedure executions and select queries, but always with different parameters passed in. 

    I used Database Experimentation Assistant to replay the trace file against an Azure SQL Managed Instance, and then analyzed the report also using DEA. I just wanted to see how it looked, so I loaded the same replay result trace files for target 1 and target 2. However, the analysis report seem pretty useless. It only captures a few system queries which are irrelevant, and most of the results say "Cannot Evaluate". Here's how it looks below.

    In the DEA docs, I read the following about the Cannot Evaluate status: 

    • Cannot Evaluate: The sample size for the query is too small for statistical analysis. For A/B testing analysis, DEA requires the same queries to have at least 30 executions on each target.

    So is it possible that because each stored procedure and lookup is executed with different parameters, DEA simply ignores them since they are not run 30+ times each?


    • Edited by noamo48 Friday, August 2, 2019 4:26 AM
    Friday, August 2, 2019 4:26 AM

All replies

  • Hi moamo48,

    Query caching in SQL Server can affect evaluation results. We recommend that you restart the SQL Server service (MSSQLSERVER) in the services application to improve consistency in evaluation results.

    >> So is it possible that because each stored procedure and lookup is executed with different parameters, DEA simply ignores them since they are not run 30+ times each?

    On my understanding, it is possible.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 5, 2019 8:10 AM
  • Thanks for the feedback. I am testing against a Azure SQL Managed Instance so restart is not possible. 

    Regarding the queries not being evaluated unless they are executed 30+ times each, is there any way to alter this behavior so that DEA analyzes all queries?

    Monday, August 5, 2019 3:14 PM
  •   
    Hi,
    > Regarding the queries not being evaluated unless they are executed 30+ times each, is there any way to alter this behavior so that DEA analyzes all queries?
    Waiting for others giving some suggestion for this issue.
    Friday, August 23, 2019 9:24 AM