tailieunhanh - Brad’s Sure Guide to SQL Server Maintenance Plans- P6

Brad’s Sure Guide to SQL Server Maintenance Plans- P6: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 1 Why is Database Maintenance Important The longer the damage remains undiscovered the more out of date will be the most recent undamaged backup. If you delete older backups on a regular schedule you may not even have an undamaged copy In either case you may end up losing a lot of data so it is important for DBAs to regularly check the physical integrity of their databases using the DBCC CHECKDB command. Maintain a Database s Indexes Over time as indexes are subjected to data modifications INSERTs UPDATEs and DELETEs index fragmentation can occur in the form of gaps in data pages that create wasted empty space and in a logical ordering of the data that no longer matches the physical ordering of the data. Both forms of fragmentation are normal byproducts of data modifications but unfortunately both can hurt SQL Server s performance. Wasted space reduces the number of rows that can be stored in SQL Server s data cache which can lead to increased disk I O. The index page ordering problem also causes extra disk activity as it often takes more work to find the data on disk and move it to the data cache than it would if the pages were in physical order. SQL Server doesn t automatically correct index fragmentation problems. The only way to remove wasted space and restore the correct page ordering is to rebuild or reorganize the indexes on a regular basis. This requires the DBA to create a maintenance job to perform these tasks. Maintain Index and Column Statistics The Query Optimizer uses index and column statistics as part of its evaluation process as it tries to determine an optimal query execution plan. If the statistics are old or incomplete then the Query Optimizer might create an inefficient execution plan which substantially slows down a query s performance. In theory index and column statistics are selfmaintaining but this self-maintaining process is not perfect in practice. In order to ensure that the optimizer has the most complete and current statistics