Select Page

Investigating Missing SQL Indexes to Increase Performance

Eric Russo | | December 12, 2013

indexblogIf your organization uses a relational database, chances are it relies on an SQL server.

Although there are companies with products bearing this name, here we use the term generically for those database management systems able to respond to queries from client machines that are formatted in SQL.

If you’re trying to troubleshoot or take on some performance tuning for your SQL server database, one of the issues may result from missing indexes.

An index stores data in the database logically. Indexes can be used for optimizing operations. They can, for example, be created across partitions. These different choices contribute to the database’s performance. A database administrator with a good understanding of how indexes work enables that administrator to improve database response time and performance as well as the end-user’s experience.

So why not insure your database is fully indexed from the outset? Microsoft’s Tom Mills explains:

“Over-indexing a database table can lead to bigger performance problems such as having to update more indexes during write operations. The Missing Indexes report is useful for identifying candidates for new indexes. Index tuning is still a practice that requires as much art as automation.”

You should be able to easily determine those missing indexes by using dynamic management views (DMV). From this point, proceed with care to prevent compounding the problem by adding indexes. SQL Server Pro advises:

“Don’t just take the information from the DMVs and create all the specified indexes. The reported indexes should be compared against the indexes that already exist in the database to ensure that duplicate indexes aren’t being created. Duplicate indexes can lead to additional writes, updates, and deletes, causing performance problems.”

Running a query is a better strategy for evaluating missing indexes. This approach pinpoints the issue on the specific database on which the query is being run. Mills adds:

“The Missing Indexes report can be useful to sift through the data in the missing indexes DMVs to find the top 10 or 20 necessary missing indexes. Be aware that the missing index process can potentially generate a lot of data. Many production databases will rack up several hundred missing indexes within a day or so of operation.”

As Kimberly L. Tripp, writing on SQLskills.com, observes, “[I]ndexing for performance is really about finding the right balance between too many and too few indexes, as well as trying to get more from the indexes that you do keep.”

Want to optimize your organization’s SQL server’s indexes and need experienced assistance? We can help you evaluate and fine-tune your database performance. Datavail experts can answer questions about your challenges by phone, but we are also available 24x7x365 through our online chat system.

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
code

Recover a Table from an RMAN Backup in an Oracle 12c

This blog post will is to show a table restore for one table in a container database

Megan Elphingstone | February 2, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS