I would like to use the SQL Server 2005 database tuning advisor for my one
of database on this database server for tuning indexes.

Should I create SQL Server profiler trace and use this in the database
tuning advisor for index analysis?  Which template or options should be
selected for the profiler trace?

Please help me with the items that need to be completed for the database
tuning advisor for analyzing indexes?

Thanks,

Yes, you must create a workload file using SQL Profiler to be used for index
tuning operatin in DTA.

I suggest you to create the workload file when your database is being the
most. However I don't know your server's hardware bottlenecks and SQL
Profiler will have an additional work-load on your server so you may want to
run SQL Profiler remotely against your SQL Server server.

In the Tuning Options tab of DTA choose the following to tune your indexes:
PDS to use in database: Indexes

Partitioning strategy to employ: It depends on your environment.

PDS to keep in database: If you want your current indexes to remain then
choose "Keep all existing PDS". If you want your current indexes to be
considered for removal then choose "Do not keep any existing PDS".

Also, after creating new indexes using DTA or using DMVs (such as
sys.dm_db_missing_index_details) I strongly suggest you to observe your
indexes usage using built-in Index Usage report or
"sys.dm_db_index_usage_stats" and drop any indexes which are not used or if
their usage (seek and scan values) are much lower than the updates that are
applied against to the fields of these indexes.

Show quoteHide quote

I won't say that DTA does everything wrong, that would clearly be an
overstatement. But, the problem is really as stated to get a proper workload.
If your server have been running for quite some time since last reboot, you
may want to have a look at sys.dm_exec_query stats, and se what queries have
caused the most load since the last restart of SQL Server. I preffer this
method.

Show quoteHide quote

Free tools for keeping sql server instances of db in synch?
SQL Maintenance Plans and Atomic Backups of a Set of Databases
No trace of maintenance plan checkdb in sql server error log?!?
Backup stratagy Overview
DTE recommends an index that already exists but with a different .
Help with SQL Profiler
automatic bak filename generation
Calling sqlcmd in a loop?
Management Studio: Omit "Set" when modifying a proc
SQL Server Migration Assistant Wizard for ACCESS - can't connect to SQL Server