Providing smoothly running systems that quickly respond to user queries keeps both customers and management happy. All but the most inexperienced database admins know that one of the most important parts of their job is to avoid the type of notoriety that accompanies badly performing databases. You need to find a better way to get your name known around the company.
SQL Server implementations are complex and many different areas can contribute to an ill-performing database. There can be external factors such as a slow network or improperly provisioned hardware that are beyond a DBA’s control, though they can identify the issues and raise concerns to the appropriate personnel. Hopefully, their warnings are heeded and the infrastructure is upgraded to bolster performance.
There are also many internal aspects of an SQL Server that impact its performance. Indexes are among the many things a DBA needs to be concerned about that influence the efficiency of their databases. Properly maintained and defined indexes are necessary for good performance. But problem indexes can be the cause of degraded query response time.
The Benefits of Indexes
Indexes are used to reduce the time and computational power required to return query results. They are designed to minimize the amount of data that needs to be examined to respond to a user query. In that regard, indexes are essential to the basic functionality of databases which is to supply information in response to requests.
SQL Server provides two basic types of indexes that can be used in tables or views.
- Clustered indexes sort and store the rows of data in a table or view based on their key values. There can only be one clustered index per table because the rows are stored in a particular order. Clustered indexes allow a table to contain sorted data. Such a table is called a clustered table as opposed to a heap which is an unordered structure without a clustered index.
- Nonclustered indexes have a separate structure separate from the data rows. The nonclustered index key values contain pointers to the data row that contains that key value. This pointer is called a row locator and its structure depends on whether the table in question is a clustered table or a heap. The row locator is a pointer to a row in a heap. In clustered tables, the row locator is the clustered index key.
Both types of indexes present some advantages and disadvantages and database teams need to take care when choosing how to implement indexes in their systems. A common issue that impacts indexes is fragmentation.
The Problem of Index Fragmentation
Index fragmentation is the result of changing data in a database. The process of inserting and deleting data causes the indexes to become fragmented which impacts the efficiency of data retrieval. This drags down performance and can result in inefficient use of disk space. Fragmentation comes in a variety of forms.
- File fragmentation at the operating system level can be addressed by system tools. This fragmentation occurs on the file allocation level and results in a mismatch between the physical and logical order of data pages. This issue should be addressed before proceeding to the other types of fragmentation that occur within the database.
- Logical order fragmentation, also called external fragmentation, is similar to file fragmentation and leads to pages that are not in logical order and take longer to traverse when satisfying a query.
- Page density fragmentation is also known as internal fragmentation and occurs as the result of page splits that are necessary when information is added to a page. Excessive free space is left on pages which can cause more pages to be read and slow down performance.
Any type of fragmentation can impact database performance. Logical order and page density fragmentation need to be addressed by database-specific tools as their resolution is beyond the scope of system utilities.
Defragmenting SQL Server Indexes
The two options for dealing with fragmented SQL Server indexes are to rebuild or reorganize them. During a rebuild, new contiguous pages are created. The process may be able to be done online, but there may be a performance hit due to the excessive use of system resources. The DBCC DBREINDEX command is used to rebuild table indexes.
Reorganizing an index makes use of the DBCC INDEXDEFRAG command. The index’s leaf pages are reordered in place and may need to be rebuilt to avoid interleaved indexes which can affect data access efficiency. You can get more details concerning index fragmentation and its effects on your SQL Servers in an IDERA solution brief that covers the topic in-depth.
SQL Defrag Manager offers database teams an automated method of controlling SQL Server index fragmentation. It allows you to centralize your view of SQL Server fragmentation in on-premises and virtual cloud instances, making it a great solution for hybrid environments. The tool automatically identifies index fragmentation hot spots and will perform on-demand or scheduled defragmentation.
SQL Defrag Manager is an agentless solution that keeps overhead low and installs quickly and easily. Built-in reporting functionality keeps the team informed and email notifications can be set up to alert the responsible parties when metric thresholds are exceeded. The addition of this SQL Defrag Manager to a SQL Server environment removes a major area of concern regarding database performance and frees up your DBAs to concentrate on other activities.