Performance - too many indexes
-
Need help!!! Using SQL Server 2005 and having performance problems which I believe is due to excessive number of indexes but don't know best way to resolve it. Here is an example for just one table but the problem exists for other tables. Have a table with 36 columns. I used DTA to analyze every query in every SP and used the DTA recommendations for index creation. By the time I got done, I had 12 composite indexes (each with an avg of 7 or 8 columns), each containing one or more redundant indexes. Issue is that there are so many queries which access this table in so many different ways (i.e. different select/where clauses) that I cannot create one index to cover all the queries or it would essentially be an index with every column in it. Thought about just creating single column indexes to avoid redundancy but don't know if that is a good idea either. Would like to hear other ideas/suggestions.
-
Hi Walter,
I attended a seminar on SQL performance tuning by Kimberly Tripp last week and she had some interesting comments on DTA. It would seem that DTA attempts to tune SQL to the n-th degree and what she recommended is looking at what the DTA suggests and apply the index recomendations gradually starting with the ones that would seem to address you most intensive queries.
Use the server side traces to track down the problematic statements.
Remeber that DTA will only take the period of the workload captured into consideration for tuning and this might not yield the ideal results for other times of the day.
Regards
Shaun
PS: Kimberly Tripp's webcasts are available from microsoft on this topic.
-
I for one would like to have a link to that page
. -
Download details: .NET Rocks! - Kimberly Tripp on SQL Server
This would be valid for SQL 2005 except I don't think there will be any mention of the DM Views in this article.
She has a series of 11 webcasts available on SQL 2005 from Microsoft. All worth listening to! This is a link to the 5th webcast in the series. You should be able to get to the rest from here.
http://msevents.microsoft.com/cui/eventdetail.aspx?eventID=1032290558&Culture=en-US
Regards
Shaun
-
If you want to understand indexes get along to an event by either Kimberley or Kalen ( delaney ) I can guarantee you'd find it money well spent!!
As for DTA .. it's ok if you know what you're doing with indexes
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Couldn't agree more. On Colin's recommendation a while ago, I went to one of Kimberley's seminars in Reading. Brilliant, absolutely brilliant! I've always prided myself on understanding indexing very well, but that seminar changed my perception on several key issues (no pun intended!
)A couple more of Kim's excellent excellent webcast links:
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032254503&Culture=en-US
http://msevents.microsoft.com/cui/eventdetail.aspx?EventID=1032256511&culture=en-us
-
Want to thank everyone for their insightful replies and suggestions. However, attending a seminar is not a viable solution to my immediate needs. I have performance issues that need to be resolved in a short amount of time. Does anyone have any ideas/suggestions to my problem as original stated. Should I create single column indexes to avoid redundancy or ?
-
Can you undo the work the DTA made, and upgrade the indexes using only the slowest running queries?
-
Yes, I can undo the work the DTA made, but exactly what you do you mean by upgrade?
-
Undo the work the DTA did.. should give you a break.
Then in query analyser, replay the trace for all the query and show the execution plans.
From there tune all the queries one by one until you have satisfactory performance.
-
I did not use a trace in DTA. I submitted each query, one by one, from every stored proc we have. That is how I wound up with so many composite indexes with redundancies. DTA did not consider existing indexes when it gave its recommendations. The issue is that there are so many queries which access this table in so many different ways (i.e. different select/where clauses) that I would need to create one composite index to cover all the queries (a covering index) but it would essentially be an index with every column in it. The only other thing I can think of is to create single column indexes so there is no redundancy but then I am concerned about the many joins the optimizer will have to do as well as the performance decrease caused by index maintenance.
-
There's no magic wand to wave for index tuning - I make most of my living performance tuning applications and servers. Ultimately each problem query needs attention, it may take a day or more to cope with one complex procedure which might need more than just the odd index.
A test environment is pretty crucial too - if your prod has multiple procs then your test must too, likewise with 3gb switch, o/s versions, sql versions, service packs
You need to make sure each table has a clustered index and all foreign keys are indexed, then you might want to make sure major sargs have indexes - then it's down to profiler or which ever tool you choose to use to help you.
Quite often it's the code not the indexes or a combination of the two. Mostly it's down to query plans - you can generate these within profiler and then scan the results for obvious issues. I usually start with high i/o or whatever is giving the most pain.
Make sure your hardware is good though - pointless doing lots of work if the hardware is not up to scratch.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Walter setup a server side trace and schedule it to run during the period that you expierence your performance problem.
1) Use SQL Profiler to setup a trance based on the performance tuning template and write the results to file. Do not execute the trace from Profiler but simply use it to generate the SQL code.
2) Execute the code in Query Analyser to setup the trace. Get the trace ID.
3) Schedule a job in the Job scheduler to turn on the trace at the desired time and another to turn the trace off.
4) Once you have the trace you must open it with SQL Profiler and identify the statements that execute the most often and have the longest duration, IO and CPU. Take that most offensive statement and put it into Query Analyzer turning on the execution plan and server IO statistics.
5) Test your new index/s required to improve this statement and then repeat the trace capture and analyse the results again.
6) Finaly remove the trace.
The solution is divide and conquer them one at a time. That way you will create indexes that have real value on the most frequent queries. If a query is very intensive but only occurs once a month and the query is important then consider creating the indexes to support this query say on the 25th of the month and dropping them again after month end.
Shaun
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply