Got stress Sometimes the SQL Server needs some

Got stress? Sometimes the SQL Server needs some too Peter Shore SQL Saturday Cleveland 2017

Agenda • Introduction • Testing • Why we want stress • Distributed Replay • OStress • SQL Query Stress • Hammer. DB

Who am I? • Platform Database Consultant – Blue. Chip Consulting Group • President CBus. PASS • Intentionally Accidental DBA • Member Multiple Virtual Chapters • Over 20 years IT experience • • Server Engineer Desktop Engineer Network Infrastructure Desk side support • Co-Organizer SQL Saturday Columbus • How to find me • Twitter: @pshore 73 • E-mail: pshore 73@outlook. com

Stress • The confusion caused when ones mind overrides the body’s natural desire to choke the living **** out of some ***hole that desperately needs it. • The non-specific response of the body to any demand for change • Fulfillment of an infinite number of requests via finite set of resources

Why stress SQL Server? • To remove stress from SQL Server • Test queries • “Break in” new servers • Benchmark

Causes of SQL Stress • Query • Poorly written query/queries • Death by 1, 000 cuts • Database • Many users/queries interacting with the database • Instance • Other databases that are highly active • Noisy Neighbors • Other SQL instances on the server • Other applications on the server • Other servers on the virtualization host

Stress Testing SQL Server • Write our own test • Mission specific • High degree of control • Time consuming • Single Query tools • Ostress • SQL Query Stress • Database tools • Hammer. DB • Instance Tools • Distributed Replay

Distributed Replay • Microsoft supplied and supported • Debuted SQL Server 2012 • Uses Profiler Trace to capture a workload • Must capture from SQL 2005 or later • Similar to replaying trace • Playback against a different server • Can be played through multiple clients simultaneously • Any number from 1 through 16

Distributed Replay

Distributed Replay • Use Cases • • • Test SQL Version upgrade Test Windows/SQL service packs/cumulative updates/patches Test hardware upgrades Test virtualization Provide load for performance testing If multiple reply clients are needed • Installation & Configuration • Installs from SQL Server installation media • Use local or domain service accounts • Firewall exceptions may be needed

Distributed Replay • Usage • • Create new trace using the TSQL – Replay template Capture workload & save trace Preprocess the trace files to prepare for distributed clients Replay against target server using 1 or more clients

Distributed Replay • Reference Links • Overview • https: //technet. microsoft. com/en-us/library/ff 878183(v=sql. 110). aspx • Installation • https: //blogs. msdn. microsoft. com/mspfe/2012/11/08/using-distributed-replay-to-load -test-your-sql-serverpart-1/ • https: //www. sqlskills. com/blogs/jonathan/installing-and-configuring-sql-server-2012 distributed-replay/ • http: //www. slideshare. net/stevedxu/sql-server-distributed-replay • Usage • https: //blogs. msdn. microsoft. com/mspfe/2012/11/14/using-distributed-replay-to-load -test-your-sql-serverpart-2/ • https: //www. sqlskills. com/blogs/jonathan/performing-a-distributed-replay-withmultiple-clients-using-sql-server-2012 -distributed-replay/ • Forum • https: //social. technet. microsoft. com/Forums/sqlserver/en-US/home? forum=sqldru

OSTRESS • Part of RML Utilities • Command prompt based • Can be used as part of a script • Multi-threaded • ODBC based • Simulates multiple connections • Is able to replay trace files or run scripts

OSTRESS • In-line query • ostress -E -d"Stack. Overflow" -Q"select U. Reputation, C. Text from dbo. Users as U inner join dbo. Comments as C on U. Id = C. Id" -n"10" o"C: temp“ • Query from file • Ostress. exe –ic: Amit. Stres. Teststress_01. sql –n 200 –r 2000 –oc: Amit. Stres. Test output (sample from SQLServergeeks. com) • All the script files in a folder • Ostress. exe –ic: temp*. sql – n 100 – r 1000 –oc: tempoutput (sample from SQLConsulting. com)

OSTRESS • OStress Replay Control Agent (ORCA) • Conceptually similar to Distributed Replay • Requires same hardware architecture and version of RML Utilities • Use the OStress control. ini to point to ORCA server

OSTRESS • Background and link to installer • https: //support. microsoft. com/en-us/help/944837/description-of-thereplay-markup-language-rml-utilities-for-sql-server • ORCA and OStress • https: //blogs. msdn. microsoft. com/psssql/2009/01/23/inf-multi-machine -replay-using-orca-and-ostress/

SQL Query Stress • Originally written by Adam Machanic • Maintained by Erik Ejlskov Jensen • Single exe • GUI based tool to run one query multiple times • Able to run multiple times on the same machine

SQL Query Stress • Reference links • Git. Hub • https: //github. com/Erik. EJ/Sql. Query. Stress • Too many references to link on Adam Machanic’s site • http: //sqlblog. com/blogs/adam_machanic

Hammer. DB • Open source • Load test/benchmarking tool • Not just for SQL Server • GUI based graphical database • Limits command line functionality • Customizable workloads, if you know TCL

Hammer. DB • Usage • Change BENCHMARK to MS SQL Server • Schema Build • • Set name of server Can change name of database, but the target must be empty Adjust number of warehouses & users to build warehouses Double click build • Driver Script • Set database connection • Set number of transactions per user or the length of the test in time • Double click load • Click green arrow to run test

Hammer. DB • Reference • http: //www. hammerdb. com/index. html • All documentation can be found on the site

Questions?