The implications of lack on indexes on supporting a large production database



  • The situation is as follows: I started at a new company several months ago in a support capacity. Part of my role will involve running queries in production to help diagnose application issues. I was given production db access (read only) on my first day. I was a bit worried about this but got over it. I then set about investigating an issue. One of the biggest tables is the ubiquitous orders table. I have to filter on 'CustomerField' (string data) and join on foreign keys. Now I looked at the schema and 'CustomerField' isnt even indexed. In fact there are barely any indexes throughout the whole database. What are the performance implications of running this query and others like it? It feels wrong, how worried should I be?



  • Typically it's a good idea to index on the fields in the predicates (JOIN, WHERE, and HAVING clauses) of the queries commonly executed on your server or the queries that require executing with the utmost performance. Most Tables should at a minimum have a clustered index, which then stores the data in the order of the fields defined in that index. This avoids the usage of Heaps, which can degrade in performance over time (especially as they're continually modified), and stores the data in a non-ordered manner.

    There are a lot of small caveats to each statement in the previous paragraph - too many to get into the details on in one answer. And indexes have their own overhead for INSERTs on a table, so too many of them on one table can hurt performance too. But it is generally good advice to be aware of and follow.

    As SMor mentioned in the comments, sometimes it's good to follow an if it ain't broke don't fix it approach if no one's complaining, but that doesn't mean it isn't worth drilling in and understanding the performance implications of your potentially worst offenders, both in the context of the call stack of themselves and more globally on the server, as they consume resources.

    One of the best ways to do that is to leverage some of the following tools out there that some of the wiser SQL-folk have made freely available:

    1. https://www.brentozar.com/responder/ - Made by Brent Ozar and former team, there's a lot of powerful procedures in there that will help shed light on your worst offenders. Particularly sp_BlitzCache which will tell you your worst offending query plans in your server's cache. It can be called with @SortOrder = 'cpu' to get the results sorted by CPU consumption, or @SortOrder = 'xpm' to get the results sorted by the queries executed most frequently per minute. Additionally sp_BlitzIndex will provide you the worst offending indexing cases (either lacking indexes, too many indexes, or useless indexes that are written to a lot but not read from much). Those among the other procedures in the kit provide a multitude of useful information, but requires a lot of practice to get used to using.

    2. https://www.erikdarlingdata.com/sp_pressuredetector/ is another great tool that gives a wholistic view of things that are causing resource contention on your server. It was written by Erik Darling, a former contributor to the First Responder Kit as well.

    3. And finally, Adam Machanic's http://whoisactive.com/ is another great tool to help see what's currently actively running on your server, what those queries are waiting on and / or being blocked by, and how long they've been running for (among other helpful pieces of information - such as the executing user and machine). This is a great tool to run in a pinch when performance issues are popping up. (The aforementioned Erik Darling is also a contributor in recent versions to this tool too).

    All three of the previously mentioned tools are great to get practice with and become familiar on when to use them. But for particularly looking into specific index issues, I'd recommend starting with sp_BlitzIndex and see if it has any recommendations relating to your orders table.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2