SQL Server Reference Guide

Even people who don't know exactly what you do as a Database Administrator (like anyone with a "C" in their title), know at least two requirements for your job: you're supposed to keep the database server working, and to make the database server work fast. Of course, they don't look at it that way - they are more interested in the applications that actually use the database, and often only find out that you exist when things go awry.

To be remembered as the person who makes things run well, you need to know about maintaining your system. 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. But — it isn't perfect.

Even though the wizard is really easy to use and covers most maintenance situations, you need to understand the steps it shows you to accurately use it. 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. In fact, it's far more effective to use Transact-SQL statements to create your maintenance per database, and then schedule that maintenance using the SQL Server Agent Tool. 

I've covered a few 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 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.

Begin by checking the SQL Server Error Logs. This feature is actually not the best named, since the “Error Log” contains not only errors but a wealth of information. I cover a better process for checking logs in this article, but you can run a simple command to view them this way:

EXEC sys.xp_readerrorlog 0

(By the way, the checklist below has way more information on the various steps for maintenance. But this is worth calling out here)

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. In fact, you should ALWAYS check the backups on a system before you do anything else. Here's a quick script from the MSDN Codeplex site that will show you the status:

SELECT 
T1.Name AS DatabaseName, 
COALESCE(CONVERT(VARCHAR(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') AS LastBackUpTaken 
FROM master.sys.databases T1 LEFT OUTER JOIN msdb.dbo.backupset T2 
ON T2.database_name = T1.name 
GROUP BY T1.Name 
ORDER BY T1.Name

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), find out why, of course, but then 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 can still have 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. This tip is for local disks — SAN storage and other storage types (Like SSD) may not need defragmentation - it can actually

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. I show you how here.

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. Each database is unique, including the system databases, so apply this information one at a time to each of them.

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. It's actually superior to run the ALTER INDEX statement in SQL Server 2005 and higher. You have two options - a REORGANIZATION, which uses one level of defragmentation, and a REINDEX, which is more thorough but has more impact on the system.

You could 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). Make sure you have room to back up the database first!

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. There are links embedded within the checklist you can use to find more information.

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 elsewhere to form your own.

After you review this checklist run — do not walk — over to this website: http://ola.hallengren.com/ Download, read, understand an implement these scripts. My friend Ola has done a better job on these scripts than I've seen anywhere else, hands down.

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.