Mission critical application testing with Distributed Replay

Joe Yong Senior Program Manager This document has been prepared for limited distribution within Microsoft. This document contains materials and information that Microsoft considers confidential, proprietary, and significant for the protection of its business. The distribution of this document is limited to those solely involved with the program described within.

Confidential and Proprietary © 2011 Microsoft Last Updated: Sunday, April 26, 2020

DISTRIBUTED REPLAY

• • What is Distributed Replay?

• • • Multi-client scalability to drive higher throughput Higher workload replay fidelity Useful for stress testing production workload or performing application compatibility testing before upgrades Why an enhanced replay tool?

• • • • Use multiple low-cost clients (workstations) to handle large workload traces within reasonable time Address limitations of current SQL Trace based tools (Profiler, Upgrade Assistant) Provide enhanced features Groundwork for future workload testing capabilities

DISTRIBUTED REPLAY BASIC CONCEPT

Capture

System change

Replay (before) Replay (after) Comparison Reports

DISTRIBUTED REPLAY DATA & WORKFLOW

User 1 Preprocess Controller 2 Replay Batch Request Batch Request Batch Request … Batch Request SQL Server Multiple Replay Clients

DISTRIBUTED REPLAY CONCEPTS

•

Connect time:

time delta between trace start and login is established

Connect Time

Connection 51 Login •

Think time:

time delta between two events on the same connection

Think Time 2

Connection 52 Trace Start

Connect Time

Login

1 3 4 Think Time 5

Logout Logout

PREPROCESS & REPLAY STEPS

• • What Happens During Preprocess: • Sort the events in ascending order of EventSequence • Trim away events and columns not used for replay • • Generate an intermediate file Goal: preprocess the trace once, and intermediate file can be replayed with different options What Happens During Replay: • • • • User specified the replay options, including which clients to use Shred the intermediate file into chunks in a load balanced fashion Dispatch the file chunks to the clients Two replay modes, stress and synchronization o o Under stress mode, each client sends request independently Under sync mode, client needs to coordinate with controller to know when a specific event can be released (this is optimized so it is not done for every single event)

PREPROCESS OPTIONS

• • IncSystemSession: Whether system session events are filtered out MaxIdleTime: cap max idle time between events

REPLAY OPTIONS FOR SEQUENCE CONTROL

Submit order 1 Synchronization Mode

Event submit ordering across SPIDs

2 Stress Mode

StressScaleGranularity = SPID Event submit ordering within SPIDS

3

StressScaleGranularity = Connection Event submit ordering within connections

Submit timing

Submit time synchronization

When to use

High-fidelity replay mode, such as

functional runs

ConnectTimeScale (0%–100%) ThinkTimeScale (0%–100%) UseConnectionPooling (Yes | No) Use 100% connect time scale and 100% think time scale for performance runs.

Use SPID granularity and reduce connect time scale and think time scale for moderate-stress runs.

Use Connection granularity and reduce connect time scale and think time scale for high-stress runs.

SYNCHRONIZATION MODE

• • Events submitted in order across SPIDS Emulate original event submit time:

STRESS MODE (SPID GRANULARITY)

• • •

STRESS MODE (CONNECTION GRANULARITY)

• • •

OTHER REPLAY FEATURES (1)

• • Added type support for: sql_variant, money, smallmoney Healthmon for deadlock detection:

Wait for event=104 conn=51 commit tran; event=103 conn=52 select from T1

replay environment

event=101 conn=51 begin tran; update T1 event=102 conn=52 select from T1

SQL Server

Wait for

OTHER REPLAY FEATURES (2)

• • • Automatically manages database context • Avoid DB mismatch between captured trace and current DB context – DR will issue a “USE DATABASE...” command if discrepancy is detected between current DB context and DB Name column in intermediate file during replay Configurable query timeout Result trace collection • • • • Execution success/failure Execution elapsed time (microsec) (On/Off) # of rows in result sets (On/Off) result set contents Supported SQL Server versions:

Input Trace File Version

SQL Server 2005 SQL Server 2008 SQL Server 2008 R2 SQL Server 2012

Target SQL Server

SQL Server 2008 / 2008 R2 / 2012 SQL Server 2008 / 2008 R2 / 2012 SQL Server 2008 R2 / 2012 SQL server 2012

PERFORMANCE & SCALABILITY

APPLICATION COMPATIBILITY TESTING

• • More thorough functional and performance testing Detect potential upgrade issues not covered by Upgrade Advisor: • • • • • • Test ad-hoc T-SQL embedded in application Test actual T-SQL execution, not just syntactical checks Test security configuration o May include OS level security settings Detect changes in query plan, duration and results Detects use of undocumented features/objects Very rare but possible: identify changes that break or alter query behavior but is not documented (yet) • Not required for all applications but highly recommended for • Missions/business critical applications • • Complex applications Application where queries are mostly client-side, middle-tier or dynamic

APPLICATION COMPATIBILITY TESTING

Capture

Database Upgrade

Replay (before) Replay (after) Comparison Reports

SQL SERVER UPGRADE ASSISTANT V2

• • • • Leverages Profiler for SQL Trace capture Leverages Distributed Replay to replay captured workload in trace file Enhanced reporting capabilities on top of SSUA V1 Enhanced user interface

APPLICATION COMPATIBILITY TESTING

Upgrade Assistant for SQL Server V2.0

powered by SQL Server 2012 Distributed Replay

SUMMARY

• • • • Key to a successful upgrade is planning & testing • Tools are intended to simplify and expedite execution, not to replace proper planning Level of pre-upgrade validation/testing is typically proportionate to business criticality There is a wealth of resources available Feedback through Microsoft Connect will drive improvements in product and upgrade process/tools • We are working on plans for more automation for pre-upgrade validation

RESOURCES

• Distributed Replay • http://msdn.microsoft.com/en-us/library/ff878183(v=SQL.110).aspx

• Upgrading to SQL Server 2012 • http://msdn.microsoft.com/en-us/library/bb677622(v=SQL.110).aspx

• Upgrade Advisor • http://msdn.microsoft.com/en-us/library/ms144256(v=SQL.110).aspx

• Application compatibility testing with Upgrade Assistant • http://social.technet.microsoft.com/wiki/contents/articles/sql-server-upgrade assistant-tool-for-denali.aspx

• Product feedback • http://connect.microsoft.com/sqlserver

FURTHER. FORWARD. FASTER.

•

Learn more

Visit the Microsoft Virtual Academy

Free technical SQL Server training courses •

Get more

Download SQL Server 2012 Trial Software

180-day evaluation of SQL Server 2012 •

Do more

Participate in virtual launch activities and collect points for prizes

The more points you earn, the bigger your prize could be

© 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.