Disk fragmentation
The disk the database files and log are placed is fragmented, especially when the disk is not dedicated to the database files, and when files of more than one database is placed on the disk and databases have their default settings of initial size and file growths.
How to deal with it
1. Stop SQL Server service
2. Copy database files from the disk you want to defragment (file level backup)
3. Run the defragment tool, e.g. Windows built-in Disk Defragmenter
4. Start SQL Server
5. Run DBCC CHECKDB to check the affected databases
Recommendations
Don’t leave default settings for initial size and growth of your database files and transaction log. Do capacity planning to estimate initial size including tables and indexes. Define the growth based on the regular usage, plus the maintenance cost, e.g. index rebuild require large amount of database space (approx 1.5 times your table size). Leave your database with ~30% free space. Schedule the database files growth with ALTER DATABASE statement during maintenance windows, in order to eliminate the expensive disk allocations during the transactions. Don't wait until SQL Server performs auto-growth for you, auto-growth is just left there to act as a safety net. Use fixed size for growth settings instead of the default 10 percent. Enable Instant File Initialization for SQL Server 2005 enterprise or higher. Turn OFF Auto-Shrink and don’t run SHRINKDATABASE nor SHRINKFILE, they're absolutely unnecessary, increase fragmentation and reduce performance. Create only ONE transaction log file for each database, even though you can create multiple transaction log files, SQL Server just use the transaction log file sequentially. If your transaction log is fragmented already (check it by running DBCC LOGINFO('DB_Name') command, see if there are 50 more VLFs), follow the instructions how to deal with fragmented Tlog files on this blog, look at step 8.
Index fragmentation
Regular database activities – inserts, deletes and updates, lead to indexes fragmented over time. This kind of internal database fragmentation isn't due to something wrong on your database settings, but it's normal that your indexes will become fragmented at some time.
How to deal with it
Periodically monitor the index fragmentation level, using a tools you like such as Idera SQL Fragmentation Analyzer, could be once a week, depending on your database usage.
The general rule is if the fragmentation is more than 30% you should deal with it either with INDEX REORGANIZE or ALTER INDEX REBUILD. To create your index maintenance job, the Ola Hallengren's SQL Server Maintenance Solution is a great tool to start, it's very reliable and absolutely free.
No comments:
Post a Comment