4

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.

  • 1
    You certainly can do harm with too many indexes. Also, if your DBA ran the Database Engine Tuning Advisor, it's not really well-regarded for the advice it gives. You're better off posting the execution plan and seeing if someone can help you make sense of it. – dpw Sep 28 '16 at 22:08
  • So far, I have only added 1 index and it reduced the query time from 10s to 3s. I'll continue to see how it goes. If it consistently retrieves the data in that time, mission accomplished. – onefootswill Sep 29 '16 at 13:12
  • You can upload plans to sqlperformance.com and they remove authentication information. There may still be some server information there, but this is explained in this post - sqlperformance.com/2013/02/t-sql-queries/…. – user3777284 Oct 4 '16 at 18:04
  • Are Auto_update and auto_create statistics turned on? – Sir Swears-a-lot Oct 5 '16 at 7:59
  • 1
    One of the DBAs ran a query optimization but you don't have access to a proper DBA? If you have DBAs this is a task for the DBA. – paparazzo Oct 6 '16 at 20:51
2

I dont think you can ignore the stored proc. This it ultimately query tuning. Queries use indexes. Indexes support queries. They go together. There is a limit to how much indexes can help a bad query.

You cant do too much harm adding a few indexes judiciously. However if you add too many indexes you could make performance even worse.

I would start by ensuring your current indexes and statistics are maintained and in good shape. If not you should start there. You may get a noticeable improvement just from rebuilding your indexes.

Next I would look at the queries in your stored proc and assess if they would benefit by adding or modifying indexes. As mentioned by others I would focus on covering indexes that support joins and filtering.

Dont go crazy, just start with a couple, test and review. Look at the execution plan and focus on the tables and functions that have the highest "cost".

If there are several queries within the same sp I believe you should consider them together. You may find one covering index may benefit several queries.

Keep in mind that different query types may benefit from different types of indexes or indexes on different columns depending upon what they do. For example a query designed to return a single row vs a query attempting to aggregate data.

Edit: Your comment about the reccomendation to create 78 statistics makes me suspicious that something isnt right there. This article about auto_create_statistics & auto_update_statistics might be of use to you.

3

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

2

Typically a best practice touted by Kim Tripp who is an excellent authority on SQL Server Indexes is to make a covering index that covers your queries, instead of several different indexes.

You would want to put the joins and filters (WHERE) in the main index, then anything that is only a SELECT in the INCLUDE syntax in CREATE INDEX command. This way your index is covered and it doesn't have to go to the base table.

Of course this depends on the queries being ran and might not be best for you. I would try both and do load testing then see which one is better.

If you'd like you can post some sample queries and table structure and we can give you a more in depth answer.

Kim Tripp's MCM video on Indexing is very useful and can be found here.

1

Before start creating so many indexes, make sure the Optimizer will take advantage of them to speed up the execution of the query.
Take a peek at Sargable queries.
Maybe you don't need to create any new index to speed up the performance.

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.