Performance Tuning...

One of the issues as a Sql server architect/developer faces constantly is performance issues of queries/applications on the SQL Server database. There are lot of ways to do performance tuning, here in this post I would like to highlight some of the steps in approaching performance issues. They performance can be seperated into two broad aspects, one would be the system/hardware resources aspect, the other would be the programming/query aspect. In other words the methodology would comprise of (1) checking/configuring system resources, and (2) tuning individual queries. Let us focus on the query/programming aspect. When optimizing a query, the following tools can be used: The options can be set before the actual query block.


1. SET STATISTICS IO ON
2. SET SHOWPLAN_XML ON
3. SET SHOWPLAN_TEXT ON
In the Manangement Studio, the Display Estimated Execution Plan and Include Actual Execution Plan can be used. While looking at the execution plans look out of table scans followed by clustered index scans and index scans. On interpreting the Statistics IO, look at the Logical reads, Physical reads parameters, also look at if there are any read-aheads.

The following Option can be used to check the index fragmentation on a table:
DBCC SHOWCONTIG('TestTable', 'TempIndx')

The following option can be used to Display the current distribution statistics for the specified target on the specified table.
DBCC SHOW_STATISTICS('TestTable','TempIndx')
UPDATE STATISTICS can be used to keep the statistics of the table up to date.

Some of the basic parameters that can be monitored on the System/Hardware side:
CPU Utilization. ( How much is CPU pressured, On an average when the CPU utilization starts getting over 60-70% range and starts following this pattern,one needs to pay attention to such instances)
Memory Utilization (How much of meroy is reserved for SQL Server).
Disk I/O and Network I/O rates.

In this post I have listed some basic steps towards Performance tuning, in future articles, I would attempt to post more detailed information on the performance tuning steps.