patternsqlMinor
Considerations for Adding Indexes to a SQL Server Database
Viewed 0 times
considerationssqlindexesaddingdatabaseforserver
Problem
I'm a .NET developer. So, I know enough about databases to be dangerous, but do not have the expertise of a proper DBA.
We have a slow running stored proc and one of the devs here ran a query optimization tool which suggested the addition of some indexes.
It suggested 9 Non-clustered indexes and 78 statistics.
I know this is a pretty huge area and I know enough to know that I don't know much. So, given that I don't have access to a proper DBA to review the indexes and statistics, would it be a reckless move to just proceed with those suggested changes?
Rewriting the Stored Proc is another option we are not too keen on, as it is returning the correct data right now and we are reticent to mess with that.
But if there is a big gotcha that I am unaware of, I'd really like to find out about it. So, advice would be greatly appreciated.
I was going to post the most expensive query plan of the batch to Pastetheplan.com . Alas, I was unable to get permission from my manager. I can't see how a bit of random SQL will have any security implications etc., but the business is more paranoid than me. I realise this would have helped a lot and am disappointed I cannot post to it. The 1 index I added has helped a lot and the advice here has been much appreciated. Choosing an answer is tough and I'm still working on that.
We have a slow running stored proc and one of the devs here ran a query optimization tool which suggested the addition of some indexes.
It suggested 9 Non-clustered indexes and 78 statistics.
I know this is a pretty huge area and I know enough to know that I don't know much. So, given that I don't have access to a proper DBA to review the indexes and statistics, would it be a reckless move to just proceed with those suggested changes?
Rewriting the Stored Proc is another option we are not too keen on, as it is returning the correct data right now and we are reticent to mess with that.
But if there is a big gotcha that I am unaware of, I'd really like to find out about it. So, advice would be greatly appreciated.
I was going to post the most expensive query plan of the batch to Pastetheplan.com . Alas, I was unable to get permission from my manager. I can't see how a bit of random SQL will have any security implications etc., but the business is more paranoid than me. I realise this would have helped a lot and am disappointed I cannot post to it. The 1 index I added has helped a lot and the advice here has been much appreciated. Choosing an answer is tough and I'm still working on that.
Solution
If you can review the suggested indexes and maybe try adding a few here and there. However, be cautious of over-indexing. Over-indexing can cause SQL Server to get confused on which index to use and that can lead to "DUI’s", that is, it can hamper the performance of Deletes, Updates or Inserts (DUI’s). Also look up the concept of a covering index. A covering index is a single index that "covers" the query and that index can also be reused by other queries, effectively reducing the number of indexes needed.
Watch this excellent free 30 minute video by Kendra Little that covers Indexing in SQL Server:
https://www.youtube.com/watch?v=wAhr91FBlx4
Watch this excellent free 30 minute video by Kendra Little that covers Indexing in SQL Server:
https://www.youtube.com/watch?v=wAhr91FBlx4
Context
StackExchange Database Administrators Q#150819, answer score: 2
Revisions (0)
No revisions yet.