Unfortunately databases don’t have an indicator light because that could be a help to you. Fortunately databases don’t have an indicator light because that could be an annoyance to you. I was not trying to rip off Charles Dickens’ opening line in A Tale of Two Cities. I was trying to start you thinking about the word *maintenance* in a broader sense. Maybe the indicator light should be assessed in terms of brightness rather than simply on or off? This post will list five forms of database maintenance and with each form draw an analogy to automotive maintenance.
Cars and databases have standard reoccurring maintenance that is recommended. Changing fluids, replacing filters and adjusting tire pressure are a few examples for automobiles. Updating statistics, defragmenting indexes and performing backups are a few examples for databases. How often you perform these tasks vary based on mileage and time for cars and based on data churn and acceptable data loss for databases.
In line with regularly scheduled maintenance tasks you should get under the hood and perform regularly scheduled inspection tasks. For cars this includes checking items such as brakes, belts and hoses. For databases this includes checking items such as table integrity, error logs and performance counters. How often? The frequency for a car depends on factors such as driving habits, vehicle age and climate. The frequency for a database depends on factors such as hardware dependability, code quality and performance needs.
Not all maintenance is scheduled sometimes you breakout the specialized equipment to dig a little deeper. You might do this when she’s just not running like she used to. You might do this for the satisfaction of knowing she’s in tip top shape. With automobiles you might test timing, compression and the air conditioning (A/C) system. With databases you might test by using the Best Practice Analyzer (BPA), running a SQL Trace and hooking up the service codenamed Atlanta. How often? This is a return on investment (ROI) call. A decision is made weighing the value of dependability against resource costs.
If you want to raise the bar of your peak performance then you will need to do some tuning. On your car you can swap copper plugs for iridium plugs, upgrade standard tires to high performance tires and replace a two inch exhaust system with two and half inch exhaust system. On your database you can use smaller data types, reduce virtual log files (VLFs) and add more data files. How often? This is another ROI call. A decision is made weighing capacity against resource costs.
The practice of maintaining, inspecting, diagnosing and tuning will not completely prevent repairs but should help with reducing and planning repairs. Plugging a tire, replacing a hose and refilling freon are potential car repairs. Adding a covering index, increasing a data file size and restoring from a backup are potential database repairs. How often? The simple answer is every time you find a problem.
Yes, fully maintaining a database is a lot of work but just as cars you owned received different treatment, databases you own will receive different treatment. Hopefully you enjoyed this broader definition to database maintenance because I plan to build it into a series. In the meantime, why not find a database to love, take a benchmark, get your hands dirty and then measure the improvement.
Database Mechanic Yellow Pages
Maintaining – Name: Paul Randal Contact: @PaulRandal Location: http://www.sqlskills.com/BLOGS/PAUL/
Inspecting – Name: Bill Ramos Contact @BillRamo Location: http://sqlblog.com/blogs/bill_ramos/ New Location: http://blogs.msdn.com/b/billramo/
Diagnosing – Name: Brad McGehee Contact: @BradMcGehee Location: http://www.bradmcgehee.com/
Tuning – Name: Kimberly L Tripp Contact: @KimberlyLTripp Location: http://www.sqlskills.com/blogs/Kimberly/
Repairing – Name: PASS Professional Association for SQL Server Contact: #sqlhelp Location: http://search.twitter.com/search?q=%23sqlhelp
Until next time, Let It Ride