In a past couple months I’ve seen this happen in a couple of different customers, so I thought I’d write a short post about the topic. While it’s easy to think that running SQL Server workloads on cloud VM’s is the same as running them on VM’s in an on-premises environment, this is typically not the case. There is one huge difference to cloud based VMs.
And as you are well aware, what databases commonly are used for, it’s storing data. But that data doesn’t exist in a limbo (though sometimes the cloud makes me wonder…) but in an actual storage device somewhere. In the on-premises world, if you’re running any type of serious workloads, that storage is going to be an expensive SAN device. In the cloud your storage is also very like coming from a super-expensive, state-of-the-art storage device, but that’s not the experience you’re going to get. Instead of having bandwidth measured in GB’s, you’re getting something that is measured in MB’s.
About two months back I ended up moving to another job, which has unfortunately kept me to be bit too occupied to find the time to blog, until now. Due to this previously mentioned career change, I have been working quite a bit with monitoring, and it gave me a spark to write this post about my favorite PerfMon counters.
Like most DBAs I rely quite a lot on monitoring, and like most DBAs I too have my own set of PerfMon counters that I rely on to provide me an accurate view of what’s happening in the environment I am administering. In this post, I’ll describe what are my favorite PerfMon counters.
One thing that we always set up on a new server is a Performance Monitor “black box”. The black box is basically a PerfMon collector that starts when the server does and runs continuously on the background, gathering performance data from number of vital counters. It has minimal performance overhead and is great at giving you a good idea on what has been going on in your servers during the last several days.
Distributed Replay is a feature that was first introduced with SQL Server 2012. It allows you to play a set of recorded transactions against a SQL Server database. This can be extremely useful if you’re doing hardware or SQL Server version upgrades and want to test the performance impacts of these changes, or if they’re going to break your application.
Not a long ago I was doing a database cluster delivery to one of our customers and as a part of that process, I scheduled our regular set of test runs for the storage. The results of the tests we ran were bad and none of the changes we did at the SAN weren’t helping. After a while we figured out that the issue was not in the test software or at the SAN but in the scheduled tasks we used to run the different test!
This is definitely one of the most used tools in my toolkit. Besides being incredibly useful when you need to figure out issues like blocking and locking and what the transactions are doing, you should take a few long moments to look at the code. The code extensively commented making it easy to follow and to understand what different parameters do. Also Adam has some serious T-SQL skills so going through the code is a great way to improve your own skills as well!
Most DBAs have their own favorite set of tools and utilities they like to use and so do I. In this blog series, I’ll be introducing you to my favorite tools of the trade. Most of these utilities are made by the members of SQL Server community and all of these have one thing in common, they are free. While people often turn to commercial solutions (if they have the budget to do so), I would consider the following free tools essential for anyone working with databases or database servers.