What do we want? GUIs ! Mostly because I’m a lazy fellow who likes clicking the mouse instead of learning shell commands and automating all things. That habit needs to change, but that’s a kick in the rear for another day. What I can tell you is that with SQL 2016 Microsoft really wants you to upgrade. They want to get you on the latest and greatest. With SQL 2016 they’re making that even easier with the release of a new tool titled “Database Experimentation Assistant.” Experiments? That sounds exciting to me. I hope it is to you.
So what does this shiny new application do? It allows you to GUI -ify the the process of running Distributed Replay. Yes, you will need to have installed it and set it up first. Not only that but it will do some analysis for you on your work loads and tell you if your newer SQL server will be slower than your old current production box. Let’s be honest, that Monday morning after a weekend upgrade coming into work and you phone rings with the application isn’t performing. That is not what anyone wants. Starting in SQL 2012 you could use distributed replay to capture your current workload and play it back on your new instance. If you did lots of work you could get pre and post stats to see if your queries would fly or run slower. With this tool it will do all the calls for you and make your life easier. The tool is new as of PASS 2016 so I recommend you give it a try at work. We did and found a bunch of merge statements were going to run slower. That’s real world scenarios we took to our development team to update before we went live. As an aside I told them to dump merge, but they didn’t want to listen ( =) ). That most likely stopped someones phone calls that Monday morning after the upgrade. That’s results you can take to the bank.
This year my SQL Sat session is on setting up Distributed Replay and running this new Database Experimentation Assistant. If you go to Nashville SQLSat site from 2016 you should find my slide deck with lots of setup pictures and steps. I’d also be happy to work with anyone if they need help getting this working. Just find me on twitter or email me (contact info is in my bio.)
I’ll be trying to praise the gospel of this setup at a couple different SQL Saturdays this year and hopefully (but probably not) PASS 17.
Above are a couple links to get you started straight from Microsoft. I hope this primer has peaked in an interest and you’ll go give this a try at work. Remember, there’s always a performance overhead for doing this in production so please start with test or development first to get comfortable with the setup and it’s results.