Providing information is the most fundamental purpose of a database. This is accomplished through queries that are submitted by the database’s users. Based on the parameters submitted in a query, the database is searched for appropriate results. Hopefully, an answer to the query is delivered promptly.
Even the most complete collection of data about a particular subject is useless if it cannot be accessed in a reasonable amount of time. Depending on the type of information contained in a database, the acceptable lag time between query submission and completion can vary widely. Critical health-related databases need to return query results much more quickly than one intended for academic research on 18th Century literature.
There are many different aspects of an SQL Server database that can be addressed to increase its performance. The code can be tuned to streamline query execution and there is always the possibility of hardware upgrades. One of the most common methods of improving the response time of database queries is to use indexes. But simply creating indexes will not maintain enhanced performance. There is maintenance required to make sure the indexes are doing their job.
Why Indexes are Important
Achieving optimal database performance is the goal of most DBAs. There are certainly other activities such as creating backups and enforcing security that are equally important, but these essential tasks are not always evident to database users. The speed at which queries are responded to is their main concern.
Indexes are a construct used in databases to optimize the speed in which query results are returned to users. They contain keys that are associated with specific columns in a table or view. The keys are then stored in a B-tree structure that allows the database to find rows requested in a query more efficiently.
In the world of SQL Server, there are two basic types of indexes that can be implemented to reduce the time required to respond to user queries.
- Clustered indexes are used to sort and store rows of data based on their key values. Each table can only have a single clustered index and the inclusion of one makes it a clustered table. Rows are stored in sorted order as opposed to the unordered table structure which is known as a heap.
- Nonclustered indexes have a structure that is not bound to the data rows. The Index contains index key values and a pointer to the data row containing that value. The pointer is known as the row locator and its structure differs depending on if the data in question is stored in a heap or clustered table. The locator points to the row in a heap whereas it is the clustered key index for clustered tables.
The actual construction and definition of indexes are beyond the scope of this post, but it is safe to say that the judicious use of these structures can result in a significant reduction in query response time. The wise use of indexes is a proven method of improving database performance.
How Defragmentation Improves Performance
Over time, the indexes created for an SQL Server database can become fragmented. Two types of fragmentation can afflict SQL Server indexes and consequently the performance of your databases.
- Internal fragmentation occurs when the pages that SQL Server uses to store data contain too much free space. Deletions and insertions are responsible for this issue which eventually results in more pages being required to be read to satisfy a query. The additional read operations slow down response time.
- External fragmentation is the result of pages that are out of order. The disarray is caused by updates that attempt to use page space efficiently. As pages get out of order, it takes longer for a query to execute.
Defragmentation is the process of rebuilding or reorganizing indexes to optimize performance. Both disk space allocation and query performance benefit from index defragmentation.
Defragmenting SQL Server Databases
IDERA’s SQL Defrag Manager can help your database team find those elusive performance gains that provide end-users with a better overall experience when accessing their SQL Servers. It’s an excellent tool for conducting index maintenance throughout the SQL Server environment. The application supports both cloud and on-premises database instances.
With SQL Defrag Manager your team can monitor index fragmentation across the complete environment from a single dashboard. This enables more efficient management of database fragmentation and saves valuable DBA cycles that can be better spent elsewhere. It will also result in fewer occurrences of irate users complaining about excessive response time.
There are multiple advantages to using SQL Defrag Manager to maintain database performance. The defragmentation process can be automated across the environment and policies can be defined to conform to enterprise standards. Servers can be automatically added and deleted from defragmentation schedules to handle evolving SQL Server environments. Reports can be generated to document performance improvements due to defragmentation and the cost-saving realized by the organization. SQL Defrag Manager is an excellent choice for keeping SQL Servers running smoothly and end-users happy.