SQL Tuning

SQL tuning is most certainly not an exact science. Optimizing one parameter can negatively affect the way another performs. Unfortunately, the whole SQL tuning process can literally be an exercise in trial and error.

When it comes to SQL tuning, the most widely prescribed tactic involves optimizing the use of indexes. However, this solution is not a cure-all for every sluggish database. Depending on the structure of the particular database, placing indexes on all the tables and queries could actually be detrimental to the performance of the database.

Things to Try When Tuning Your Database

Another thing to attempt when tuning a SQL database is maximizing the use of stored procedures. A stored procedure is a defined procedure that is stored in the database in a compiled format. The statements in a stored procedure only have to be written to the database once, henceforth the SQL Server can process requests for data in the stored procedure quicker.

Things to Avoid

Whenever possible, the DBA should try to avoid server-side cursors. This method of returning data consumes an enormous amount of the available system resources. Plus they make it rather difficult to increase the size of the database or to include any additional functionality in it.

The excessive use of "select*from" statements should also be avoided. This expression returns a huge amount of data, which can significantly slow down the performance of a database. Whenever possible the query should be refined to return as little data as possible.