February 12, 2009 at 8:50 am
I made changes to a sql query which was taking long time to run, now after the changes
it's doing much better. Now as the last step I am trying to see what indexes I need
to add on the tables which the query is using. What's the best way of determining what
indexes we need on the tables? Do I need to run database tuning advisor on that sql query
so that it can make recommendations required? Please advice.
February 12, 2009 at 9:17 am
Personally I prefer looking at the query plan. Whenever I find a scan, I try to change it to a seek with the appropriate indexes. This depends on the arguments you are scanning for.
Always keep in mind that more indexes means more I/O during update/insert statements, so it could improve read performance and worsen write performance.
Generally speaking, there's no way to determine the indexes without taking into account all the ways you access the table.
I know it sounds a bit sad, but in my opinion, no tool or wizard can do the job instead of you: it can only help. If you want it done at best, do it by hand.
Regards
Gianluca
-- Gianluca Sartori
February 12, 2009 at 9:42 am
I prefer using the query plan as well, but you can also use the index related DMV's to help as well. Check out this in BOL: http://msdn.microsoft.com/en-us/library/ms187974.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 12, 2009 at 11:44 am
Another vote for execution plans.
Check this article, [/url]and this one [/url]if you're new to them.
"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