September 18, 2020 at 6:58 pm
Hi,
I have an existing IDX on a table on col1 and col2. Now, when I am checking the execution plan the SSMS hints me about missing IDX.
Now, the missing IDX is for the same table but col order is revere in the suggested IDX, like col2 and col1 instead of existing IDX. So we need to be careful with the colum order however is it good idea to just alter the existing one or create a new one. The table already has 5 existing IDX. Please share your thoughts.
Thanks!
September 18, 2020 at 7:17 pm
It depends. The order for the index is the order it is "ordered" in the table. So if you order it by col1 and col2, it is REALLY by col1 THEN col2. What I mean is if COL1 is an integer column that keeps track of the HOUR an operation happened and col2 is an integer column that keeps track of the Minute it happened, ordering by col1 then col2 makes sense and is likely how you want it. That would be very weird table design, but it is just an example.
Now if col1 is the gender of a person and col2 is their email address, that one may make a bit more sense for ordering by col1 then col2 OR possibly by col2 then col1. It depends on the query. If your query is to filter by gender, then ordering by col1 then col2 would be what you want. if your filter is by email address, then col2 should come first in the ordering.
Now, why I say it depends is if you have 99% of your SELECT queries filtering on col1 then col2 and 1% filtering on col2 then col1, adding that index for the 1% of the time is a bad idea. If it is 50/50, might not be a bad idea, or it could be better to remove both indexes. If your table is 99% INSERT/UPDATE/DELETE and 1% SELECT, you want minimal indexes. If it is 99% SELECT and 1% IUD, then having more indexes may be what you want.
The best practice depends on the use case for the table. As for adding indexes, I try to avoid it on existing tables unless I know it will improve performance drastically on frequently run queries. And going back to the 99% IUD point, UPDATES and DELETES can benefit from indexes, so it may be beneficial to add more. But each index you add will hurt INSERT performance as you now have another order for the data stored on disk.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 18, 2020 at 7:22 pm
Thank you for your advice, it is helpful!!
Is there anyway we can figure out how the table is used like inserts or updates. I think there is a standard report for this...not able to recollect the name.
September 18, 2020 at 7:51 pm
My approach is to watch for long running queries. If I have a long running query, then I probably need an index (or other tuning) of some sort. I would encourage you to do other tuning prior to indexes but sometimes adding indexes is the solution.
If you don't know how the table is used, do some simple analytics on it. Put a short-term audit table on it to capture IUD data and see how quickly that audit table grows. As for SELECTs, watch for long running queries. If you have no long running queries, or you have acceptable long running queries, then don't worry about indexes.
But something to keep in mind is that indexes are not really designed to be a performance tool - they are used to order your data. This can help performance but it can also hurt performance and adding an index will ALWAYS use more disk space. My approach to indexes is to first run the query without adding any and see the performance. If the performance is acceptable for the data size AND the data growth isn't going to cause problems (can be estimated by looking at the number of rows/number of years the table has existed which will give you the rows per year average) for the short term, I don't bother adding an index at that time. I do watch for long running queries and check my reports on long running queries to see if I should be looking at adding indexes though.
But if your query is completing in under 1 second (for example) AND SQL is telling you adding the index will give it a performance boost, is it worth it? My opinion no. now if your query is running in 2 hours without adding the index but putting the index in turns that down to 2 minutes (a scenario I had recently. 2 hour query went down to 2 minutes by adding 3 indexes), then I'd be looking at adding them.
In the end though - always always always test indexes on your test environment before putting them on live. Last thing you want to do is shave off 1 second on your 5 second query and add 10 seconds to all of the others on the system.
Another good practice is to remove unused indexes. Those just waste disk space and can actually hurt performance of IUD AND SELECT statements.
My overall advice though - don't worry too much about indexes UNLESS you have a VERY slow query. And even then, you may be able to tune the query without needing to worry about non-clustered indexes That is assuming your clustered index is good and that you have a clustered index on the table. In my production environment, once a system is set up and running, I rarely go back in to add indexes, even if SQL is telling me I should. Getting a 1 second query down to 1/2 second just isn't worth the time or effort in my mind.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 19, 2020 at 3:58 am
But something to keep in mind is that indexes are not really designed to be a performance tool
Uh, no, they are designed exclusively to help performance. That is, increased performance is the only reason for indexes to exist.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 19, 2020 at 4:02 am
You didn't provide nearly enough info to analyze your index needs.
However, keep in mind that SQL strongly favors equal (=) comparisons over other types. So, if your conditions are "WHERE col2 = <something> AND col1 >= somevalue" then col2 will generally work better first in the index.
By far the most important performance factor is having the best clustered index for the table. Hint: For the (vast) majority of tables, an identity column is NOT the best clustered index.
If you'd like, I can provide a query that will give you the minimum info needed to properly analyze indexes. Be aware, though, that index tuning still requires lots of skill which takes time and practice to acquire.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply