Changing options for multiple databases can be time-consuming when done one database at the time. With just few databases you might be fine using the Management Studio and doing changes through the GUI but when you have dozens, or hundreds, of databases and only some of them are having wrong options (like that stupid AUTO_CLOSE one) you need to replace, you’re going to want to use scripts for it.
I’m finally getting back to the “Tools of the Trade” series with a third post on some of my favorite (and free) SQL Server tools. This time on the spotlight is the Diagnostic Scripts by Glenn Berry. The scripts can be found from his blog, here. The whole package is 6 different T-SQL scripts, one for each version of SQL Server starting from 2005 and going to 2016. There’s also a blank Excel to go with each one.
What is a Performance Baseline?
One of the important things every DBA should have, is a performance baseline for their business critical servers. A good baseline is something that tells you how your server is performing under various workloads. This is also the reason why you should have multiple sets of performance data collected instead of just one. In a perfect world you should have one for the minimum load, one when the usage is “normal” and finally one for the situation where your server and databases are under a lot of stress.
In this post, I’ll be giving you some advice on when and why you should have a performance baseline and how you can create one.
In my previous post we took a quick look into one of the SQL Server features that I personally consider to be a great boon to any DBA, the Dynamic Management Views and Functions. Im going to show you through some basic examples on how you can use these views to get meaningful information out of your SQL Server instances.
I’ve been working with SQL Server ever since 1999, back then it was the 6.5 version which was soon replaced by the SQL Server 2000 (we skipped the 7.0 completely). Back in those days getting information out of SQL Server was much, much more difficult and you had way less tools for it. Then came the SQL Server 2005 and suddenly there was a plenty of information available to make troubleshooting and working on performance issues so much easier.
In fact there’s so much information available these days, that finding out where simply to start can be a daunting task. When I first started working with SQL Server 2005 my first task was to familiarize myself with the new feature called Dynamic Management Views and Functions, or DMV’s as they’re sometimes called. In SQL Server 2005 you have about 90 DMV’s, in SQL Server 2012 the number of DMV’s is almost 180. They’re definately an important tool in any DBA’s toolkit. And that is what this article is about, to get yourself started with the DMV’s.