April 3, 2020 at 9:16 pm
Hello,
SQL Server Gurus, Can any one please suggest steps regarding performance testing all workloads ( queries both OLTP and OLAP ) before they are moved from a lower version( sql server 2014 ) to the latest and greatest version ( SQL Server 2019 ). What Tools to use, to see if legacy code will work in SQL Server 2019, also how to capture time it takes to run queries in the old/ new version and confirm that all works well before we say OK to the Production workloads.
Thanks
April 3, 2020 at 11:31 pm
I used Microsoft's Data Experimentation Assistant (DEA) on my last upgrade project. The databases upgraded included 100TB+ databases pushing billions of transactions per day: https://docs.microsoft.com/en-us/sql/dea/database-experimentation-assistant-overview?view=sql-server-ver15
DEA is a system for comparing workload measurements from two different systems. It starts by capturing a T-SQL Replay trace from a live system (your 2014 production system). You then restore that database (to the moment in time before you began your trace) on two different systems: one that matches your current production setup (SQL2014), and one that will match your future setup (SQL2019). Be sure to match CPU, storage, and memory on both. DEA then uses SQL Server Distributed Replay (I think the latest version tosses DReplay for OStress, which is a million times easier to set up and maintain) to replay the trace on both systems while a performance trace is running. DEA then consumes and analyzes the performance trace from each tested system, and produces an interactive report showing what ran faster, what ran slower, and by how much, including the execution plans for comparison. It also produces lists of commands that failed only on the new system (things that will fail after an upgrade), those that failed only on the old system (things that are fixed by the upgrade), and those that failed on both.
Once you have the initial trace and the backups, you can run the replay and workload comparison any number of times, so you can also test and compare SQL2019 with different compatibility levels, different storage layouts, and so on.
It has a UI, but can be automated from the command line.
It is neither foolproof nor trivial, though. Plan to spend some time in the documentation and fiddling with the setup to make it work.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply