Transcript Got_stress_
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 • HammerDB Who am I? • Platform Database Consultant – BlueChip Consulting Group • President CBusPASS • 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: @pshore73 • E-mail: [email protected] 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 • HammerDB • 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/ff878183(v=sql.110).aspx • Installation • https://blogs.msdn.microsoft.com/mspfe/2012/11/08/using-distributed-replay-toload-test-your-sql-serverpart-1/ • https://www.sqlskills.com/blogs/jonathan/installing-and-configuring-sql-server-2012distributed-replay/ • http://www.slideshare.net/stevedxu/sql-server-distributed-replay • Usage • https://blogs.msdn.microsoft.com/mspfe/2012/11/14/using-distributed-replay-toload-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"StackOverflow" -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:\AmitStresTest\stress_01.sql –n200 –r2000 –oc:\ AmitStresTest \output (sample from SQLServergeeks.com) • All the script files in a folder • Ostress.exe –ic:\temp\*.sql – n100 – r1000 –oc:\temp\output (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-multimachine-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 • GitHub • https://github.com/ErikEJ/SqlQueryStress • Too many references to link on Adam Machanic’s site • http://sqlblog.com/blogs/adam_machanic HammerDB • 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 HammerDB • 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 HammerDB • Reference • http://www.hammerdb.com/index.html • All documentation can be found on the site Questions?