August 20, 2009 at 12:25 pm
DB tuning advisor not recommending indices in Test SQL Server DB, whereeas it doees recommend indices in Development. When these indices are implemented in Test and Dev database, there is improvement in query performance. Does anyone has any input, why this could be happening?
August 20, 2009 at 1:25 pm
Are both systems structurally identical? Do both systems have exactly the same data in them? Do both systems have the indexes defragged and the statistics updated in exactly the same way? Are you testing the exact same code against each system?
If you've answered no to any of these questions, there's your answer. If none of these are it, there are a few more options.
"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
August 24, 2009 at 9:47 am
Thanks for your response.
Both the systems have statistics updates, there may be little variation in the data. I am not sure of they are structured the same way & index defragged- what do you mean by that ?
I think I did not do a good job of framing my question ritght.
Test DB Tuning advisor has not been recommending any index for the past few weeks for any query. It was recommending indices a month back, at some point, it stopped making recommendations. Do you think, we need to uninstall the tuning advisor in the Server ? or any reason that you could think of.
August 24, 2009 at 9:59 am
It really depends on too many factors for me to comment. What service pack do you have installed? I had a lot of problems with the Tuning Advisor until I installed SP1. Have you changed settings on the tuning advisor? Also, I'm not sure you can reinstall Tuning Advisor, although I suppose it's possible. It's much more likely that you've made a setting change that has changed the behavior.
BTW, the Tuning Advisor recommendations should be very carefully tested and not simply accepted as right.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply