What Are The Major Disadvantages of Indexing in SQL Server?

Getting the best possible performance from a SQL server can become more complicated as you add more operations to your database.

For example, online transaction processing requires the ability to add and delete data, modify selected data, and retrieve it quickly. Indexing this data may seem like an obvious solution. With multiple indexes to categorize and organize the information on hand, analyzing and retrieving the data should theoretically be much easier.

However, the negative impact of indexing this data can be seen in multiple ways.

Improving Overall Query performance

The constant need to keep the data accurate and indexed causes competition within the data table. Functions are constantly running to keep the numbers up to the minute, while indexing analyzes this data in order to sort it. Across thousands of rows of data, these processes can consume too much storage and impact overall performance. SQL Server scalability figures show that each cluster uses 50 instances of SMB storage or 25 instances of SAN storage.

Additionally, when data is indexed, the indexing process can take up additional storage space. All the data that is not part of a clustered data index now needs its own storage space. This constant need for added space can quickly balloon into a serious storage issue for enterprises that index a lot of data.

Query optimization techniques in SQL Server

An easy way to improve query performance is to identify those queries that consume an outsize amount of resources. Once these problem queries are identified, you can analyze the query’s execution to determine whether it performs poorly all the time or only in certain situations.

With this analysis done, a cause should become clear. Whether the query is designed poorly, constrained by the hardware in place, blocked by another query, using skewed data to process, or structured improperly, there are solutions available to resolve the problem. These solutions can optimize every problem query and help the whole SQL server run optimally.

SQL Tricks and Performance Tips

A SQL Server has built-in performance counters that make it easier to analyze how much memory is used.

Experts in database management agree that available memory is the most valuable resource for a SQL server that needs to perform optimally. Therefore, tracking memory use through a performance counter helps you assess whether lack of memory is impacting overall performance. This data is viewable through the Performance Monitor or the Dynamic Management View (DMV), both of which come built into the server. There are also multiple built-in performance counters to choose from, including:

• Page writes per second: Monitors the number of pages written to disk
• Lazy writes: Tracks use of the Lazy Writer process, which moves dirty pages from buffer to disk
• Memory grants pending: Gives the number of processes waiting for a workspace memory grant each second

Improving SQL Indexing Performance

Using dynamic management techniques to evaluate the effectiveness of any changes made to the database can help you identify issues before they become larger problems. Within the DMV, you can monitor what data threads are waiting with the server. Thanks to the wait tracking system built into the server, you can easily monitor each running thread and view the log of what resources the thread is waiting on. Wait types that are most likely to cause larger issues can include:

• Resource semaphore waits: These are typically caused by a lack of memory, causing queries to wait an excessive amount of time or missing data and indexes within the query string.
• Cx packet waits: Indicate a problem with configuration, likely in the cost threshold for parallelism (CTfP) setting.

Although there are disadvantages to indexing in an SQL Server, indexing can be set up properly within the database. This means you can get optimal performance while using SQL Server OLTP systems.

More information

To learn more about SQL Server, view our Microsoft SQL Server training courses.