Transcript Document
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.