I was recently involved in a query tuning work where we used synthetic, rather than production data, to validate the results of our query and index tuning work. We faced some issues with the generated data that had quite a severe impact on our testing, and that prompted me into writing this blog post. Lets start by first defining what is synthetic data. In my view synthetic data is data that resembles actual production data, but is artificial/generated. I have seen similar (and also more detailed) definitions elsewhere and I think it is a good one.
I also like to point out that there are plenty of good reasons for using synthetic data in testing, as production data is often strictly regulated and not easily available for testing purposes. However, you need to be certain that the synthetic data you are using is similar to what you have in production.
As we are almost done with the year 2017 it is a good time to look back for a moment, and to also consider what the next year will bring. This year has seen a fair share of hype around the topics of AI and Blockchain, even I touched that latter topic with this blog post. Data security has been in headlines as well, both in bad and good with the data breaches and with fast approaching General Data Protection Regulation in European Union countries.
Just a friendly reminder to everyone that just like all good things come to and end so does the extended support for these two Microsoft products. First will be the Windows 2003 R2 with the end of lifecycle date set to July 14 2015 and soon after that SQL Server 2005 with it’s end of lifecycle date set to April 12 2016.
You can still run these products after these dates of course but it’s definitely not recommended and the reason is simple. End of the extended support means that neither of these products will be receiving any patches or security updates, ever. So if you’re not already working on upgrading them, now would be a good time to start.
SQL Server offers out-of-the-box solution to create a workflow of tasks that can be used to optimize, backup and run consistency checks on your databases. These workflows, commonly known as Maintenance Plans, are actually Integration Services packages that are run either by scheduling them as SQL Server Agent jobs or manually. While I wholeheartedly recommend that you run regular backups and other maintenance routines to your databases, I wouldn’t necessarily recommend using Maintenance Plans for this. In fact, I’d probably never recommend using Maintenance Plans, unless it’s the only thing you have.
When I’m dealing with a problem on a Failover Cluster (not very often, but sometimes) one of the first steps I do is to run the Validation Test. It’s a great tool that’ll usually show what might be the problem, but apparently not always…
For the last couple days I’ve been busy wrecking havoc on a cluster with a Microsoft Cluster PFE on a Cluster Disaster Recovery workshop. Among the scenarios we’ve gone through causing, fixing and then documenting there was one that had a small surprise in store for both of us.
A few days ago there was a question in Twitter about options to KILL (SPID) with a long running transaction that was causing a lot of blocking in a mission-critical system. The person asking the question got some helpful tips on how to fix the problem, such as looking at the tables and the indexes and some tools were pointed out to him, like Adam Machanics Sp_WhoIsActive.