Free T-SQL Database Maintenance Scripts

I often get questions in e-mails, or at user group meetings, for advice on writing T-SQL database maintenance scripts. There seem to be a lot of DBAs who need more flexibility than what the Database Maintenance Wizard offers to perform database maintenance, which means they must user either T-SQL or PowerShell scripts in order to get this flexibility. Unfortunately, many of the DBAs who ask me this question don’t have a lot of time, or T-SQL experience, so they don’t know where to start.

The advice I generally give to them is to not reinvent the wheel, but to see what others have done, and to emulate them, especially to those DBAs with lesser experience. For example, the following websites offer T-SQL database maintenance scripts that are freely available for others to use with little or no modification, or to use as a learning tool in order to see how other DBAs have tackled this issue.

Continue reading

Help Design a New SQL Server Monitoring Tool

image

In an experiment in SQL Server community involvement, the members of Red Gate Software’s software development usability team have created a new website called www.thefutureofmonitoring.com. This team, among other teams at Red Gate Software, are working Version 2 of its popular SQL Response software.

The goal of the new website is to get your input on what you would like to see in a SQL Server monitoring tool. For example, what do you dislike about software installers, or what memory counters do you think are the most important? In fact, you can even help the usability team design a new dashboard.

Whatever you think about SQL Server monitoring tools, feel free to share those thoughts with the usability team at their new website. In fact, I have already given the team my input on the next version of SQL Response, and they would really like to hear from you too.

Policy-Based Management

If SQL Servers are treated as ‘islands’, each with their own subtly different configurations, managing multiple instances of them can be a frustrating experience for the DBA. SQL Server 2008’s policy-based management aims to help us enforce policies for SQL Server instances throughout an organization. In this article, I look at what policy-based management is, how to implement it, and how to check it is running. Read more…

The Maintenance Plan Wizard Can Lead You Astray

In theory, the SQL Server Maintenance Plan Wizard is supposed to make it easier for non-DBAs or novice DBAs to create database maintenance plans that help to optimize the performance of their SQL Server databases. The problem is that unless you really know what you are doing, you can hurt the performance of your SQL Server if you make poor choices. Unfortunately, the Wizard does not tell you if you make poor choices.

For example, check out the following screen, which is part of the SQL Server 2008 Maintenance Plan Wizard.

image

If you are an experienced DBA, your first thought should be, “Why are all the maintenance tasks checked?” This is because experienced DBAs know that you only need to perform some of these tasks, not all of them, and in fact, if you choose all of them, you can cause your server to waste a lot of valuable resources. For example, performing all three of the following tasks: reorganizing, rebuilding, and updating statistics is redundant, as rebuilding your indexes essentially performs the same tasks as reorganizing and updating statistics. In other words, if you rebuild your indexes, reorganizing and updating statistics is a duplication of effort. There are also some additional problems that can occur when selecting all of the options, but I don’t have time to write about them all now, but I think you get the point.

Now, let’s look at the above screen from the perspective of the non-DBA, or novice DBA who isn’t familiar with what maintenance plans do, or how they work. From their point of view, don’t you think that the more options you select, the better off your SQL Server will be? Isn’t more always better than less? What has prompted me to write about this topic is because I just reviewed a SQL Server instance that was installed and set up by a non-DBA, and as you have already probably guessed, they selected every maintenance task on the menu, thinking that more is better than less. Because the non-DBA made this choice (which is not all than uncommon), the maintenance plan took more than twice the time it really needed to run, wasting a lot of the server’s resources. There were some additional problems as well, but that is another story.

The point I want to make is that the Maintenance Plan Wizard can sometimes be useful in the hands of an experienced DBA, but it can be very dangerous in the hands of a non-DBA or novice-DBA. In fact, most experienced DBAs I know write their own maintenance plans because using the Wizard just has too many limitations and problems. If you are an experienced DBA, you know what I mean. If you are a non-DBA or novice DBA reading this, please take heed of my message, more is not better. If you decide to use the Wizard instead of writing your own custom maintenance plans, please do your research before you begin, learning what should, and what should not be done, within a maintenance plan.

PS: I have decided to write a book on how to use the Maintenance Plan Wizard properly. Look for it during the Fall of 2009.