Many tricks can be employed in order to optimize the performance of your SQL Server.
SQL 2005 comes with the Database Engine Tuning Advisor that er... advises about the steps that one has to take in order to optimise the performance of his kit.
As you can imagine, advice requires input. Input may mean scenario or factual input. Scenario based input is wise to be taken into consideration when the analysis of factual input has come to a dead end.
The best factual input, as far as the SQL Server is concerned, comes from the analysis of the trace logs offered by the profiler tool.
The picture above displays the wizard that one has to run in order to set up his trace.
It is wise to use a table in a database not used by any other application to store the traces.
The amount of traces that will suffice depend on your application and its usage. In my case I found that 100000 to 500000 traces do suffice to reveal most of the problems in our application. Off course you have to bear in mind that traces have significance for that particular section of your application if that particular section was used/called during the trace generation.
Having reached that critical amount of traces you may run the DB Tuning Wizard as shown in the picture below:
Here you only need to select the databases that you would like to have analysed by the tool and the source of your trace log.
Then you run the tool and the analysis comes with two valuable reports.
1. All the errors that were caught in the trace that refer to databases you selected and
2. With tips about optimizing the performance of your database depending on its usage.
In this example above the tuning tool came to suggest that something less than 50% of the overall processing of the SQL server analyzed is consumed dealing with errors (or perceived errors)! Because for example particular sprocs may do actually exist in the database but for your application, given the request, did not exist due to permissions, wrong path of execution etc.
But the advisor does show its teeth when it comes to recommend better indexes and statistics for your databases as shown in the figure below.
Here you see that following the analysis of the input patterns it does come to suggest additional indexes and statistics in the databases analyzed. The tool indeed delivers the actual statements for the creation of the new indexes, statistics. All you have to do is to go to the last cell of each column, click and copy paste the command to a query analyzer.
This tool is not a panacea but it is certainly the first step to actualy getting to know your own applications usage, enabling you to optimise it.