0

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?

2
  • Who is complaining about performance? No one? That's your answer - there are no concerns.
    – SMor
    May 10 at 23:30
  • An interesting take on the situation. I am not going to out myself with further details but suffice to say it turns out that isn't entirely accurate to say no one is complaining about performance. May 12 at 6:57

2 Answers 2

2

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. First Responder Kit - 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. 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 sp_WhoIsActive 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.

1

As J.D. has mentioned indexes (or lack of) can have a significant impact on query level performance and it certainly warrants further investigation. In a nutshell the more that SQL can efficiently use indexes the better your queries are and the less work SQL Server has to do, and that means less resource consumption.

That's an incredibly high-level view, it is after all a very deep subject - Microsoft recently updated their index architecture and design guide for SQL Server and Azure SQL which is well worth a read first:

https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15

Now of course you can't just go throwing indexes into a production database but if you have a suitable dev/test environment then you can assess the impact of adding indexes by looking at the execution plan of specific queries (before and after) looking at scan/seek operators, number of rows read etc - but remember that table indexes could be causing overhead on updates coming from other queries, it is a balancing act of sorts!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.