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.
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.
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.
Powered by IDERA