how to properly index?

Question

how to properly index?

water490

SSC Eights!

Points: 954
More actions
February 25, 2024 at 5:54 am

#4384394
I am not sure how to decide on how I should index my tables so the query is able to run as fast as possible. I am fairly new to SS. My queries are all SELECT. Some use GROUP BY but others do not. There are no DELETE/UPDATE. How do I decide which type of index to use?
My query took close to 1 min to run. I removed all the indices and now the query take about 25 sec. I guess it took more time to build the index that it was better to not have them in the first place but this is not obvious to me at first. I just did trial and error to see what the performance would look like if there was no index. However, I was under the impression that index are always useful but looks like my experiment proved me wrong.
Any advise you can give is much appreciated.
- This topic was modified 9 months ago by water490.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Your question is easy to ask and can take years to answer.

It sounds like you could stand a lesson on what an "Execution Plan" is. Please see the following link to what I consider to very likely be the best book there will ever be on the subject of Execution Plans. It's by one other than Grant Fritchey and it's a completely free download (PDF) offered by this very site/Red-Gate.

https://www.red-gate.com/products/sql-monitor/entrypage/execution-plans

Of course, you should actually start with the MS documentation on what a Heap, Clustered Index, and Non-Clustered (all are in the "Rowstore" category... "Columnstore" is a seriously different animal). When looking at such documentation, also make sure you check out the other links in the "Next Steps" section of such articles.

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described

That just scratches the surface... building good indexes is a bit of an "art" with a fair number of pitfalls. If you're serious about learning such things, search for articles and 'tubes by Brent Ozar, Erik Darling, Kendra Little, Paul Randal, and Kimberly Tripp. They also have some awesome courses. They're not free but they're worth it.

To save you from some "hard learning", do a search for the terms "SARGable" and "SARGability" and how it affects queries.

All of those authors (Including Grant Fritchey and some others) do have free info and 'tubes out there. So do a lot of others (just like anything else on the internet, though... caveat emptor there. There are some people with a bazillion articles and followers that I don't actually recommend for reasons I won't discuss here).

Also understand that indexes should be considered to be mostly permanent once create. You don't make a new one for every query and the time to make one should usually not be included in the time the query runs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

water490 SSC Eights! Points: 954 More actions · Answer 2

does it make sense that an unindexed query is faster than an indexed one?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

water490 wrote:

does it make sense that an unindexed query is faster than an indexed one?

Oh yeah... and big time! There are many reasons why such a thing may happen. For example, you may have told it to index in one particular manner but the index now has to lookup each item and do a clustered index lookup for each item instead of just doing a full table scan to get all of the items. It's also possible that your index is more compressed that what the original data is and so SQL decided it no longer needed to go parallel and your query runs much slower.

Another reason is that an index can, indeed, need to be run more than once to get it to stay in memory. That means that the first time it runs, it could be slower than what's been running without an index (heap) because the heap is already in memory and the former is not and even the execution plan will usually need to recompile the first 2 times the newly indexed query runs.

Just because you add an index doesn't mean that you added the right one nor does it mean that one was actually needed. There are a ton of different ways to screw up on indexes.

The optimizer DOES try to protect people from themselves but even the optimizer can't cover every abuse or contingency.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

water490 SSC Eights! Points: 954 More actions · Answer 4

this is super interesting. i didn't know that.

this begs the question....how do you decide on an appropriate index? is there a good resource you can suggest that goes over the thought process behind how one should select an appropriate index?

This reply was modified 9 months ago by water490.

DesNorton SSC-Insane Points: 24386 More actions · Answer 5

This is a great book on indexing

Expert Performance Indexing in SQL Server, Second Edition

How to post data/code on a forum to get the best help.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

In addition to all the stuff already mentioned, I'll add my book on query tuning (link in the signature, yeah, that one does cost money) gets into a lot of discussion around index selection. It's not really easy or straightforward. There are a few guidelines though.

First, every table (with exceptions, but please, exceptions should be just that, exceptional) should have a clustered index. The optimizer in SQL Server is built around this construct and takes good advantage of it, so should you. Where possible, the clustered index should be unique, but it absolutely doesn't have to be. Try, as much as you can, to keep the keys on your indexes as narrow as possible (meaning, if you have a datatype that's 24 bytes and one that's 36 bytes, either would work as an index, go with the 24 bytes one). Put as few indexes on a table as you can, but put on as many as you need (yeah, horrific advice, but you can't simply add indexes willy nilly, it hurts performance because they then have to be maintained, but you also don't want to put an artificial limit on indexes, I've seen people say one, and one only, but also, no more than three, but what if that fourth index increases performance dramatically on a query that is currently putting the most load on your systems, just add it for crying out loud). Take advantage of INCLUDE to make indexes covering (long discussion, but it means the index satisfies the query without going back to the table/clustered index). Generally, most restrictive column first in compound keys. Statistics maintenance is as important as index choice (more so sometimes). Finally, more important than indexes, your code is your worst problem.

There's more, but that's why I wrote a book. Ha!

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

water490 wrote:

this is super interesting. i didn't know that.
this begs the question....how do you decide on an appropriate index? is there a good resource you can suggest that goes over the thought process behind how one should select an appropriate index?

Like I said previously, "Your question is easy to ask and can take years to answer".

The links I provided are helpful. Any book on execution plans and/or indexes by Grant Fritchey is at the top of my list of best ways to learn.

But, I have to tell you... even experts may have to try more than once and it frequently means running the code and measuring reads while comparing to execution plans, etc, etc. While there's a good amount of "Science" to it, there's also a fair amount of "Use the Force, Luke" artful knowledge/experience feel that can go along with it.

An example of what I mean there is based on one of the previous examples of why an index may cause things to slow down simply due to a reduction in the number of pages involved in the query. Index Maintenance has the same and other frights to be aware of.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

Jeff Moden wrote:

But, I have to tell you... even experts may have to try more than once and it frequently means running the code and measuring reads while comparing to execution plans, etc, etc. While there's a good amount of "Science" to it, there's also a fair amount of "Use the Force, Luke" artful knowledge/experience feel that can go along with it.

Oh good gosh is this the truth.

And I'm not sure what happened to my signature. Seems to be missing. I'll work on fixing that.

Here's the book: https://www.amazon.com/Server-2022-Query-Performance-Tuning/dp/1484288904/ref=sr_1_1?crid=XBI6W56VDPBP&dib=eyJ2IjoiMSJ9.X-djJkNg2uBG1G2LQQKUbW781ByP4MsyZGcu4K7zl80uULSLQayvfabVyBpoTls6c5J4afwFLo8PXqD5_yrSKAovEHeoIcV3dNyvbkpSLzLfAreCZAl-mge969EW9K7G1qovMYN1R8qzLs_S6x-lHMTt17ENB7odrn_cHidoi9Qx24-STP_obSmBcVpdaidZFIg19fL2CsKKtkdnQUPGf5jM674oLkx898p_vSaziWc.OCjjresB6pXpwXYauh9iOpS0Pfu_vDL1mgb8LsgVKbw&dib_tag=se&keywords=fritchey&qid=1709047787&sprefix=fritch%2Caps%2C203&sr=8-1

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning