Professional SQL Server™ 2005 Programming

23.5. Routine Maintenance

I hate it when good systems go bad. It happens on a regular basis though. It usually happens when people buy or build systems, put them into operation, and then forget about them.

Maintenance is as much about performance as it is about system integrity. Query plans get out of date, index pages get full (so you have a lot of page splits), fragmentation happens, the best indexes have to have changes as usage and the amount of data in various tables changes.

Watch the newsgroups. Talk to a few people who have older systems running. You'll hear the same story over and over again. "My system used to run great, but it just keeps getting slower and slower — I haven't changed anything, so what happened?" Well, systems will naturally become slower as the amount of data they have to search over increases; however, the change doesn't have to be all that remarkable and usually it shouldn't be. Instead, the cause is usually that the performance enhancements you put in place when you first installed the system don't really apply anymore, as the way your users use the system and the amount of data has changed, so has the mix of things that will give you the best performance.

We'll be looking at maintenance quite a bit in the next chapter; however, we've discussed it here for two reasons. First, it will help if you are checking out this chapter because you have a specific performance problem. Second, and perhaps more importantly, because there is a tendency to just think ...