June 30, 2020 at 4:02 pm
Hi
Recently we moved to replication of a DB we receive.
I noticed it was slow, long story short, the non-clustered indexes were missing, added them back in and queries ran quickly again.
My question is , how do I know when and what fields to add a non-clustered index?
Any info/links would be appreciated
Thanks
June 30, 2020 at 5:05 pm
Adding non-clustered indexes depends on the query being executed. Generally, you want to look at the WHERE clause or the ORDER BY clause and anything those are filtering on will generally benefit from the index.
Now on the other hand, an index is essentially a copy of the table data you are including in the index. So if you have a 10 column table and have an index on 2 of the columns, those 2 columns (plus the clustered index key(s)) are going to exist in the table (either a heap or a clustered index) AND in the index. And if you have any INCLUDES on the index, those are replicated in the index as well. So you can imagine what this does to space over time. Plus, it will affect performance of INSERT, UPDATE and DELETE statements as you need to insert, update, or delete from the table AND the indexes.
So adding indexes blindly is a good way to hit performance problems,but also removing indexes blindly is a good way to hit performance issues. There are also cases that can cause SELECT performance to be impacted in a negative way by adding indexes, but that is usually related to duplicate and/or unused indexes.
But to answer your questions directly:
WHEN - add indexes when you notice SELECT performance is slow and you are out of other options to improve performance
WHAT FIELDS - add fields that are used to filter or order the data
The reason I say "out of other options to improve performance" is because if you have some crazy complex query or you have a poorly written query (uses a cursor for example... which isn't always a poorly written query, but will always give you performance problems), it is often better to fix the bad query than to fix it at the table level. It is EASY to add an index that makes your query faster, but it may be difficult to test all queries that touch that table and your new index MAY make everything else slower.
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.
June 30, 2020 at 5:58 pm
Thank You...
This is great information to have.....
June 30, 2020 at 6:24 pm
If you'd like more information on indexes, I highly recommend Kendra Little's blog:
https://sqlworkbooks.com/blog/
Kendra is my "go-to" person for indexes!
Brent Ozar has a blog post on it with Kendra Little too:
https://www.brentozar.com/archive/2011/09/kendra-little-explains-how-design-smarter-indexes/
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.
July 1, 2020 at 12:06 pm
And to see what indexes are being used and how they're being used, you want to look at the Execution Plans. The book in my link is available in digital format for free, or you can pay for the paper copy.
"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
July 1, 2020 at 7:40 pm
Thank you....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply