In the world of SQL Server administration, many issues can negatively impact database performance. They range from inappropriately provisioned storage capacity to poorly written SQL queries and inadequate network bandwidth. There are a lot of things that a DBA needs to be aware of to keep their systems performing well and head off that flood of phone calls from irate users.
Some of the problems that affect database performance are beyond the control of a DBA. They can lobby management for more network or storage resources but are at the mercy of decisions made by others. The database team is usually not responsible for purchasing the systems on which the SQL Server instances are run, but are expected to optimize their performance as much as possible.
Fragmented indexes are one of the causes of slow performing SQL Server databases that can be directly addressed by the administrative team.
Fragmentation and its Causes
Many computer users are familiar with the concept of file fragmentation, where parts of a file are stored in multiple physical locations on a storage device. Optimum data access performance is achieved by storing files contiguously, or all in one piece, on a disk. New systems with plenty of available disk space can meet this demand. Over time as files are deleted or expanded, this goal can no longer be obtained and files need to be stored in fragments, using the space that is available to the operating system. This slows down performance by requiring multiple operations when reading or storing data.
Index fragmentation is something altogether different. It is an internal issue with how SQL Server is handling its data. Physical disk fragmentation is not as big a problem with SQL Server implementations due to the fault-tolerance and multi-drive storage arrays used for its storage requirements. But there is still fragmentation going on that puts additional strain on the computing resources needed to satisfy data access requests by SQL Server databases.
SQL Server uses pages of 8192 bytes to store all of its data and internal structures. These pages are logical entities that do not pertain to how the information is stored on disk, which can be in multiple physical files. Internal fragmentation refers to gaps or voids in these pages that are essentially dead weight but are included in all SQL Server operations such as backups, query plans, logs, and cached data. Performance gradually degrades due to this extra empty baggage being passed around until it reaches a level that impacts user functionality. It can be compared to a slow leak in a car tire. Gas mileage is impacted almost imperceptibly and then suddenly the tire is flat and you’re stuck.
The gaps in SQL Server pages are caused by normal operations conducted on the database’s store of information. Over time, gaps are formed by insertions, deletions, and modifications made to the data on a specific page. It is therefore hard to predict when fragmentation will become a problem. In some cases, a rise in fragmentation may be related to the increased usage of databases due to business considerations.
Even when the fragmentation experienced by a SQL Server database has not degraded performance drastically, it can be costly to an organization in other ways. The time and storage resources required to back up the database are increased by fragmentation with associated direct financial implications. Response time may be affected in minor ways that accumulate over days, weeks or months to cause a substantial impact on user productivity. As a DBA, you want to eliminate index fragmentation wherever possible.
Manage SQL Indexes Efficiently
IDERA’s SQL Defrag Manager is an application that is designed to help SQL Server DBAs manage and optimize their database indexes. It performs index defragmentation that will improve user response time, which is always a good thing. The tool provides a unified dashboard that displays fragmentation details concerning all monitored SQL Server instances. It enables your DBAs to efficiently deal with fragmentation with a single tool rather than a collection of individual scripts.
SQL Defrag Manager enables defragmentation to be automated while minimizing the impact on system performance. It also provides the facility to create reports that demonstrate the performance gains that have been achieved by defragmenting and point to the SQL Server objects most impacted by fragmentation. Email notifications can be set up to keep personnel informed on the status of their streamlining efforts.
An IDERA whitepaper is available that takes a much more detailed look at index fragmentation and how SQL Defrag Manager can help your database team cope with this problem. If furnishes a deeper understanding of the issue of fragmentation and demonstrates why the tool is the best way to address the issue. SQL Server DBAs should give it a read and work on management to get funds appropriated for SQL Defrag Manager throughout the environment. It’s a no-brainer that automating performance-enhancing procedures is welcomed by everyone involved with administering and using your SQL Server databases.