As DBAs know, Database Tuning Advisor can help you in some cases.

• The analyze of the traces will be efficient in OLTP where the most of queries are repeating and could be generalized
• The analyze of traces will be less efficient in OLAP/reporting where the queries are parameterized by users. The recommendations to create index with included columns let your database grows quickly. Be aware to apply recommendations like those.
• Finally, the analyze will be useless in case of OLAP/Decision. Every "ad hoc" query produces the recommendation to add some indexes with included columns. Taking into account the size of data warehouses/datamarts and that the kind of queries is hard to generalize, this kind of "optimization" wont be a good solution.

In such case the precise optimization is required. I propose you the following procedure that could be adapted accordingly your needs.

Gather required information

I developed a small utility that gathers static and statistical data about database server. You can download it from sourceforge.net. The sources are open so you can compile it yourself too.

Instructions to start:

1. Unpack ZIP file and place the utility in some directory (directory on database server is preferred)
2. Change connection's parameters in configuration file appcfg.xml
3. Start ssexpertcmd.exe directly or from command line (recommended)

The utility creates the subdirectory corresponding to start time and then stores into all gathering informations as CSV files.

At the same time the log file "ssexpertcmd.exe.log" will be created to store information about any errors. I.e. some database could be off-line.

It is not necessary to place the utility on database server and you can start it from workstation. In this case all storage information will be about your local disks.

Selection of necessary informations

In the directory created by ssexpertcmd.exe you have many CSV files that you can open with Excel or Open/LibreOffice:

See also MSDN article about sys.dm_exec_query_stats view.

Analyzing queries

You could start from 090_QStatTop\* data files and analyze several queries at top of the list.

It is recommended to install some plug-in to format SQL code in Management Studio. Then you copy the query from file to SSMS and format it.

Add the following instructions before SQL code to show execution time and readings:

SET STATISTICS IO ON
SET STATISTICS TIME ON

If needs, add the instructions of cleaning the cache to simulate "cold mode" (i.e after server restart or query was deleted from cache). Don't use this on production server!.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

However, on production server you can force query recompilation:

SELECT
...
OPTION (RECOMPILE);

Activate the option "Include actual execution plan" in the toolbar of SSMS or in the menu "Query - Include actual execution plan". Or use SET instruction:

SET STATISTICS XML ON

Start the query and see the results about CPU time/elapsed time, readings and real execution plan.

There are many sources explaining how to improve Transact SQL query. I.e. the good start point is the article "Checklist for Analyzing Slow-Running Queries".

Have a good hunting!