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*@discussions.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.

I don't think running several SPs would give you correct index
recommendations because the difference between missing_index DMVs and DTA is
that DTA calculates other DML transactions other than SELECT and to perform
a correct calculation it needs more data to analyse. I mean, it's important
to see a possible index' s impact on the system from all angles, not only
one.

I've seen situations where DTA is used blindly (as Erland mentioned) and the
results were catastrophic. Of course it has good recommendations sometimes
but you should be careful about them. Even if you create indexes using DTA'
s recommendations, you should analyse those indexes using
"dm_db_index_usage_stats" DMV or you can simply use SSMS' s Reports (Index
Usage) which uses the same DMV for its report.

Show quoteHide quote

Maintainance Plans Disappeared from SSMS
sql profiler writing on an unused partition?
SQL Maintenance Plans and Atomic Backups of a Set of Databases
Need help debugging this...
SQL Management Studio and Small Business Server
SQL Server 2005 Database Tuning Advisor
Properties of DB in SQL 2008 SSMS - Arithmetic overflow
DTE recommends an index that already exists but with a different .
Help with SQL Profiler
automatic bak filename generation