1/23/2008
SQL Server Database Maintenance (excerpt)
SQL Server
Database Maintenance
Last updated Mar 16, 2007.Even people who don’t know exactly what you do as a DBA (like anyone with a "C" in their title), know two requirements for your job: to keep the database server working, and to make the database server work fast. To do that, you need to know a little about maintenance. SQL Server has always been (and continues to be) known as one of the easiest systems to operate and maintain. That's a well-deserved reputation. But it doesn't mean that you don't have to do any maintenance at all. Because Microsoft has done a good job of hiding the complexity in SQL Server, some technical professionals that work with other products think it isn't a "real" platform. But they are wrong. A SQL Server installation takes a fair amount of planning and preparation to implement, and some amount of work to maintain.
By far, the easiest process to follow to maintain your system is to use the built-in Maintenance Wizard, a topic I've covered in more depth in another tutorial. Simply activating this wizard will walk you through creating a fairly comprehensive maintenance plan, and for many small shops it’s all you need. While quite simple to set up, the Maintenance Wizard is so important that I’ve included some screen shots so that you can just follow along to set one up on your databases at your site.
Even though the wizard is really easy to use and covers most maintenance situations, the mark of a seasoned DBA is knowing what is happening during maintenance and understanding when to step outside the wizard to perform specific tuning. To gain that knowledge, you’ll work with command-line tools such as the DBCC commands and the backup tools.
I covered many DBCC commands in previous tutorials, and you’ll see some of those again in this one. What I’m going to show you this time, however, is an application of those commands. It’s not enough just to memorize syntax; it’s also important to know when and how to use the tools to perform meaningful work. Some of these commands will help you form the basis of your maintenance plan.
Let's examine the server maintenance you need to do, and then the database maintenance you require. At the end of this tutorial, I'll give you a handy checklist you can use as a guide for your own maintenance plan.
Server Maintenance
Regardless of the application you’re using, it stands to reason that the application software won’t perform to its peak if the hardware and operating system on the server aren’t up to full speed. This is especially true for SQL Server; since it makes heavy demands of the only four pieces of hardware that really affect a server’s performance: CPU, disk, memory and network interfaces.
A full primer on server maintenance is beyond the reach of this tutorial, but familiarity with the process of monitoring your server on a periodic basis will go far in devising your overall maintenance strategy. There are a lot of good articles here on Informit and several good books on Safari (see the references section at the end of this article) and I have a tutorial or two that cover the Performance Monitor tool, which can shed some light on how things are performing on the server.
Another useful tip is to install the monitoring utilities that came with your server hardware. There are times when this is not appropriate, but a majority of the time you should have them installed and configured.
As part of any maintenance plan (server or otherwise) you should have a solid backup plan which is tested on a periodic basis. Backups are an integral part of your maintenance, and they are essential for recovery.
Your shop may use an after-market tool for backups, but if you don’t then you can use the backup software built right into the Windows server operating system. I’ve seen many shops that survive quite well using only the native backup tools. Read the Windows help file to learn how to automate the built-in tool to schedule your system backups. Note that this product won’t backup your SQL databases, but later in this tutorial I’ll show you how to back up your databases to the hard drive. The file backup software can then pick up that backup file. Just make sure you schedule the SQL Server database backup (if you are sending it to disk) before your regular file system backup takes place.
Server Optimization
Microsoft SQL Server’s strength (some view it as a weakness) is that it only runs on Microsoft operating systems. What this means is that the developers can link into the operating system to enhance the performance of SQL Server.
This advantage has a flip side, however. If the server is not optimized, the software that runs on the server won’t be able to operate at its best performance. Again, the same four base components apply here.
Take a little time to learn about the Performance Monitor tool so that you’re familiar with what’s taking cycles on your CPU. Are programs and services running that aren’t necessary? Your server won’t complain when you ask it to do a lot, but your users will. I've got a complete set of tutorials starting here that will help you with a full performance tuning exercise.
If your server is paging (writing memory to disk), get more memory for it. Some paging is unavoidable, but excessive paging will devastate the performance of your system. Here’s an interesting thought experiment: Take the base tick of a memory cycle (which works in nanoseconds) and set it in your mind to equal one second. Perform a task in that second. If you could really do that, then a hard drive (which works in milliseconds) would take 42 days to do that same task. Buy the RAM.
Speaking of the hard drive, Windows is still notorious for disk fragmentation. I’ll talk a little later about database fragmentation, but outside the database the hard drive needs to be defragmented on a regular basis. The bad news is that you really should turn the SQL Services down before you defragment the hard drive, but even if you can’t do that you should run the defragmentation anyway.
Many people don’t know what the slowest component on a server is. No, it’s not the hard drive, CD ROM or even the floppy drive. The slowest part of your server is the Network card. Spend some time verifying the driver software and network configuration, and you’ll buy more time than almost any other component.
It all comes down to your familiarity with your system. I recommend that you become familiar with the Windows System Monitor to know what to tune on your server.
Database Maintenance
Now that you’ve taken some time to monitor and tune the server, you can move on to your databases. I’m going to cover only three or four commands, so while this tutorial should in no way be considered comprehensive, it will get you started.
The key to maintaining a database is to be familiar with how it’s running. The commands I’ll show you will help you spot the warning signs that the database is about to have a bad day — and, hopefully, help you prevent them.
The first command to run to help spot the problems in the database is DBCC CHECKDB. The DBCC CHECKDB command verifies that Index and data pages are linked properly, that Indexes are sorted, that the pointers in the database are accurate, that the data looks OK, and that there are proper page offsets. If you don’t know what all that means, it’s OK, you’re only looking for errors at this point. The syntax looks like this:
DBCC CHECKDB (’DatabaseName’)
The output goes on for what seems like forever, but shouldn’t have any error codes. You can suppress the output of the information messages (I do) by adding WITH NO_INFOMSGS to the end of the end of the command. Adding this parameter will still show you any errors.
There’s another command, DBCC CHECKTABLE, which lets you drill in a bit further on any table that has generated an error.
If you do get error messages from either command, you’ll use another DBCC command to fix them, but we’ll cover that in another tutorial. The key is to check the database structure often and make those corrections early.
Database Optimization
The main item of database optimization is the index. Each index on a database is actually a set of pointers to where the data is stored in the physical arrangement of the tables. When these indexes are not kept up to date or become fragmented, then the access to the data is slow. To defragment the indexes, use the command:
DBCC DBREINDEX (’TableName’, ’IndexName’, FillFactor)
The parts of the command are:
| Tablename | The name of the table the index belongs to. |
| IndexName | The name of the index to defragment. You can leave this blank, and it will do all the indexes. Leaving it blank can take some time if your indexes are large or you have a lot of them. |
| FillFactor | A fillfactor tells the process how much space to use for the index storage unit (a page). If you think that the indexes won’t be added to very often, then set the number high (like 90), and if you think that the index will be added to often, then set it a bit lower (like 70). It’s a guessing game, most of the time, and requires that you understand the way your indexes are used. I’ve covered indexes and page space in another tutorial. |
There’s another command that will defragment the indexes on your databases called DBCC INDEXDEFRAG, but it has some limitations over the full database defragmentation. You can read up on this command in Books Online, but if your database is small, I recommend the
DBCC DBREINDEX
command.
You can normally just apply the defragmentation commands blindly to all your tables – but if the indexes are quite large or the defragmentation takes a long time, you may want to know if you need to run them or not. To do that, we call on another DBCC command, called DBCC SHOWCONTIG (TableName). This is a pretty deep topic, but the thrust of it is that you’ll see an output like this:
DBCC SHOWCONTIG scanning ’bucks_info’ table...
Table: ’bucks_info’ (420196547); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 8009.0
- Avg. Page Density (full).....................: 1.05%
DBCC execution completed.
If DBCC printed error messages, contact your system administrator.
The important bits for this output are the Scan Density and the Avg. Page Density. The Scan Density displays a ratio of how contiguous the physical structure is. The closer to 100% this number is, the more contiguous the data is laid out, and the faster the access will be.
The Avg. Page Density relates to the data that is stored in the table. The higher this number, the more data is on a page (the physical storage unit). This is good for reads, but more problematic for writes. If this table is read-intensive, it’s best to have a high number, but if the table has a lot of new writes, it’s best to have a lower number.
Now that you’ve checked the databases and optimized them, you need to back them up. One of the simplest forms of the command is:
BACKUP DATABASE
’DatabaseName’
TO DISK = ’c:\temp\DatabaseName.bak’
WITH INIT
The format I’ve shown above creates a backup file of the entire database on the temp directory on your hard drive, assuming you have a directory called C:\TEMP. Of course, you would replace the word DatabaseName with the name of your database. It also overwrites any file with the same name (WITH INIT).
If you want to backup to tape, you would use the command:
BACKUP DATABASE
’DatabaseName’
TO TAPE = ’\\.\TAPE0’
Replace the TAPE0 with TAPE1, TAPE2, etc. for each tape device on your server.
There are also other options for backing up the database such as incremental backups, log backups and so forth. As I mentioned, I’ve covered those in another tutorial.
This has been a quick introduction to the type of maintenance you should consider for your server. I've developed a small checklist that you can use to look at this from a timing perspective.
It's important to note that this checklist should be used as a guide. There are far more things that you may be required to check and maintain, based on your organization's landscape, and what features you have installed. For instance, if you have replication enabled on any of your databases, you will have more to check. This guide should be used as a simple format to start off with. Always make sure you understand your server and use the knowledge you gain here and else where to form your own.
| Frequency | Task | Explanation |
| Daily | Check Backups | Check the backups using the logs. |
| Daily | Check Event Logs | Check the System, Application and Security areas in the Windows Event logs. |
| Daily | Check SQL Server Logs | Check the log files in SQL Server. |
| Daily | Check Drive Space | Check that the drives that store database logs, data files and operational files have sufficient space. |
| Daily | Check jobs history | Check the job history to ensure that it ran correctly. |
| Daily | Check Agent Logs | Check the agent logs on your system. |
| Daily, Weekly, Monthly, Yearly | Perform database backups | Create a plan that executes an automated backup for all of your databases, including the system databases. |
| Daily, Weekly, Monthly, On-Demand | Update Indexes and Statistics | Develop a good reindexing strategy. |
| Daily, Weekly, Monthly, Yearly | Archive Backups | Determine when the data is no longer needed locally and send tapes or other media off-site for storage. |
| Daily, Weekly, Monthly, Yearly | Data Archival | If you have data that loses its immediate value, create a "rollup" strategy to aggregate the data to save space. |
| On-Demand | Update Change Management Scripts | Script your database objects into a change control management system. |
| On System Implementation | Install and configure SQL Mail or Database Mail | Install and configure SQL or Database Mail |
| On System Implementation | Automate Maintenance and Logging | Use the SQL Server Agent to process all maintenance and logging tasks possible. |
| Monthly | Review Security Access Points | Check the server for network access. |
| Monthly | Test Restore Operations | Restore the database backups to a test server. |
| Monthly | Check indexes | Create a report on indexes to find which are useful. |
What I’ve shown you in this article are a few simple commands that will get you started on a basic maintenance plan. Use these commands with your regular Windows maintenance for a complete plan.
Informit Articles and Sample Chapters
If you got here and you don't have time for all this, check out this article. It gives you basic information that you can use for SQL Server.
Online Resources
Here's a nice flash guide for walking through the maintenance plans.