SQL Server - database maintenance gotchas

What kind of database maintenance operations you should be doing when you are DBA?

You should have better understanding on the terms in managing the Database platform at your end, without proper information it is very hard to say you are managing your platform effectively. Think about DBCC checks, backups and database maintenance tasks and list of tasks as follows:

• Hardware - think about maintenance
• Integriy checks for data and environment
• Database files - place them for better performance
• Optimizations - database and table data including bulk insert data
• Backups - very important for every DBA to test the database backups
• Scheduling- jobs, dts packages and others
• What else - you think about it?

When you think about managing the database platform, usual trend is go by Maintenance plan - database, whether it is a home-grown or built-in within SQL Server you choose the best after a testing. Determine what kind of maintenance window you have, how many databases are important to the business and how often you are testing your database backups. When you designing the MP then think about flexibility and limiting manual processes. Home-grown, that means you have designed the backup, DBCC checks and integrity checks with TSQL - I should say you will have more control on the lits of tasks that are important for every DBA, if you are a good programmer then you will definetly add upon more reporting features to this schedule. Having the advantages of Reporting Services with SQL Server you can have a swiss-knife technicality at your end to manage the show with minimum down time and keepup the performance.

Hardware configuration - maintenace required, do not ignore. Memory, CPU, physical disk placement. RAID - get to know the configuration for better placement of data & log files for better performance, think OLTP or OLAP. If one says you should have availability of more physical memory, that doesn't mean having 32GB on SQL Server will get you performance without any optimization tasks or normalization of the database.

Take into the account of EDITION of SQL SErver to take advantage of higher memory configuration, for instance SQL Server Standard edition can only go upto 2GB until 2000 version, within 2005 this is a different scenario. CPU - 2 is best and 4 is better, Hyperthreading - do not ignore and make sure to discuss with Hardware vendor in this case and disable it when you have highest OLTP based transactions with 24/7 kind of applications. RAID, well you should place the data and log files on different drives and this includes the TEMPDB when you have to take into 24/7 OLTP based application database. You may be aware about RAID 1 (mirrored) better for Operating system, database log file & tempdb files. RAID5 (striped) a standard followed for data & tempdb(too), but when the transactions are high then think about RAID 10 (0+1). RAID 0 best suited for backups (instant or temporary) based upon high availability, but be aware about no fault tolerance. In my experience placing the data & tempdb on RAID10 fetches better performance in terms of 140% gain for workload and indexing that includes maintaince tasks such as BULK LOAD operations. I'm not saying RAID 5 is bad, but when you had complaints on the procedures of normal workload and bulk insert operations think about RAID 10 usage within your platform. Backup wouldn't gain much on RAID10, but Restore operations will be better than performing on a RAID 5 disks.

What about SAN?  You may think I have ignored this bit of storage, well I'm not better suited SAN configuration can be discussed with your Vendor when you have high-level design of database is ready for testing. For performance factors better to test all the configuration of STRIPE + PARITY of RAID5 on your SAN, so best to go with vendor. Few blog references on SAN/RAID - RAID - performance key factor, RAID Server Failures And Data Loss Issues!, Determining the required capacity of an I/O subsystem before deploying the database and How to tell if the IO subsystem is causing corruptions? links.

Performance Dashboard reports are good to go when you have performance issues reported on the platform - SQL Server 2005 Performance Dashboard Reports - making most out of it! and you will get to see Server Performance dashboard results, I would like cover my experience on Spurious errors when using SQL Server 2005 Performance Dashboard Reports blogs too.

Still more to come on this series, I will keep them updated on this blog section.