I have a serveral SQL Server 2005 stored procedures that I would like to
test with a tool to determine if there are indexes that could be added to the
stored procedures to enhance performance. Can I use the Database Tuning
Advisor on the stored procedures? If, yes please let me know the process.

Thanks,

Joe K. (Joe K. (AT) discussions (DOT) microsoft.com) writes:
You probably can, but I cannot really give you the way to do it, because I
have never used DTA myself. And I am not so sure that using DTA for a
few procedures is a good thing. I believe DTA works better if you give
a workload for a whole day. Then you might find interesting things.

The problem with running DTA for just a few procedures, is that indexing
is often about tradeoffs. For a certain query, an index on
(a, b, c, d) with (e, f, g) included is the optimal. But the next query
needs h included instead of g and so on. Since there is some overhead
with index, you cannot add them blindly.

The alternative is to add the indexes manually, which of course is not
that trivial is you are not experienced in performance tuning. To that
end DTA can probably be a help. But if you are able to get DTA running,
don't just slap on the index it suggests blindly, but try to understand
why they work as they do, and if you can for instance remove some column
without too much penalty.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx