April 16, 2010 at 10:26 am
We have prob over 30 indexes in total on our db where DTA has recommended using clustered index on covering index or as include - i am concerned that this is incorrect
To give a bit of context all of our database application tables has a clustered index of type guid - this is not changeable. For example - sample from our donation tbl we have 3 indexes:
1) clustered index on column id (guid)
2) nc index on col id with include on cols value, date_received
3) nc index on date_received with include on col id
The above have been produced as result of capturing queries through profiler and then run through DTA, for example when a user say opens the form for a contact record, it displays the related donation values, date received etc.
Can anyone shed any light on why DTA has suggested this?
Thanks in advance
April 16, 2010 at 2:51 pm
DTA will recommend bogus indexes. It also recommends good indexes from time to time. Depending on your queries, performance and execution plans - you may actually need to include the ID column in some of your other indexes to create covering indexes. This is cheaper in many cases than performing a Lookup back to the clustered index.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 16, 2010 at 2:59 pm
DTA is a good tool, just take its recommendations with 2-ton grain of salt. You really need to test the recommendations that it makes to be sure that they really help. I have used it to help identify possible index changes that may be of benefit, but I don't always take its recommendations. I have seen it recommend included columns that nearly duplicates the data from the table, not necessarily a good thing.
Just remember this mantra: Test, test, and test again!
April 16, 2010 at 3:17 pm
Lynn Pettis (4/16/2010)
Test, test, and test again!
Absolutely. Do it on a non-prod system first if possible. Document the changes made and then check for improvement or degradation of performance.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 17, 2010 at 3:12 am
also make sure the workload you pass in is a comprehensive representation of the normal database activity and not just a 2 minute profile session!
you also have the ability to evaulate any recommendations as a hypothetical configuration to determine if any improvements can be seen
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 19, 2010 at 6:57 am
I agree with almost all of the advice here with one exception... DTA is a pile.
For anything beyond the simplest mom&pop sized database, it's recommendations are so horrendously dangerous and wrong, that the time invested in running the tool would be much better spent doing more traditional performance tuning.
"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
April 19, 2010 at 8:54 am
Grant Fritchey (4/19/2010)
I agree with almost all of the advice here with one exception... DTA is a pile.For anything beyond the simplest mom&pop sized database, it's recommendations are so horrendously dangerous and wrong, that the time invested in running the tool would be much better spent doing more traditional performance tuning.
dont hold back now Grant you say what you think 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 19, 2010 at 10:32 am
Perry Whittle (4/19/2010)
Grant Fritchey (4/19/2010)
I agree with almost all of the advice here with one exception... DTA is a pile.For anything beyond the simplest mom&pop sized database, it's recommendations are so horrendously dangerous and wrong, that the time invested in running the tool would be much better spent doing more traditional performance tuning.
dont hold back now Grant you say what you think 😀
With or without the flowery speech I picked up working for Uncle Sam's Navy?
Seriously though, DTA is deeply flawed and I think it probably causes almost as many problems as it solves. Although, I suppose, it's that "almost" that makes it workable.
"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
April 19, 2010 at 10:35 am
Grant Fritchey (4/19/2010)
Perry Whittle (4/19/2010)
Grant Fritchey (4/19/2010)
I agree with almost all of the advice here with one exception... DTA is a pile.For anything beyond the simplest mom&pop sized database, it's recommendations are so horrendously dangerous and wrong, that the time invested in running the tool would be much better spent doing more traditional performance tuning.
dont hold back now Grant you say what you think 😀
With or without the flowery speech I picked up working for Uncle Sam's Navy?
Seriously though, DTA is deeply flawed and I think it probably causes almost as many problems as it solves. Although, I suppose, it's that "almost" that makes it workable.
It's that almost that makes it workable but also makes it a tool to help people practice and review without implementing any of the suggestions that DTA spouts out.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply