Top 10 SQL server Performance tuning tips

Introduction

I planned to post some performance tuning steps ,mostly DB design related.

Use SQL Profiler

Use SQL Profiler check the Duration, Reads and Writes for each Stored Procedure and for each statement within the stored procedure.Typically Reads should not exceed four times the number of rows returned. If reads are abnormally high then indexes may require a re-look or the query has to be split depending on the complexity.Set the duration filter before running the trace to identify the queries that exceed a specific duration.

Use of UNION/UNION ALL

UNION move the results to the tempdb, sort it, identify unique entries and retrieve the results.

UNION ALL will not do the sort, and will not identify unique entries. The results will not be unique. This can be used when the results from two queries are distinctly different

UNION will have adverse impact on the number of Reads and Writes and hence the performance.

Use of OPTIMIZE FOR

OPTIMIZE FOR a little known but powerful SQL Server feature.

DECLARE @ProdCode NVARCHAR(20)SET @ProdCode = N’100’
SELECT ProdId,MfnDate
FROM Product
WHERE ProdCode = @ProdCodeOPTION (OPTIMIZE FOR (@ ProdCode = N’100’))

The OPTIMIZE FOR hint will force an index seek when the specific value is provided. More about OPTIMIZE FOR/OPTIMIZE FOR UNKNOWN here

Use of Indexes

• NON CLUSTERED index should be created on a highly selective field.
• Order of the fields in the WHERE clause should be same as the index order.
• Every table in the DB should have a CLUSTERED index.
• Primary Key may not be suitable for clustered index if the table is extensively used for SELECT.

Avoid cursors

Avoid using cursors.It may result in Deadlocks.Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. During this kind of situation, SQL Server identifies the trouble and ends the deadlock by automatically choosing one process and aborting the other process, allowing the other process to continue.

Set Based Approach

Even though procedural approach is easier for most of the programmers,it (Use of Cursor, or, UDF to process rows in a result set) should be avoided when working with large result set.Most of the times cursors can be replaced by CTE(Common Table Expression). Avoiding Cursors with SQL Server 2005

Remove unwanted columns from the SELECT statement

In queries, don't return column data you don't need. For example, you should not use SELECT * to return all the columns from a table if you don't need all the data from each column. In addition, using SELECT * may prevent the use of covered indexes, further potentially affect query performance.

Reduce the NULLs

Reduce the use of NULLs in the database. The use of NULLs in a database can greatly reduce database performance, especially in WHERE clauses.Highly normalized database can achieve this.

Use of NOLOCK hint

NOLOCK hint is useful to improve the query performance when selecting the rowset from a table which is being accessed by multiple transaction.But this leads to dirty read.A dirty read is where a query reads data from the database without lock protection. Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running.

Use of performance monitoring tools

SQL server ships with many tools like Profiler,Database Engine Tuning Advisor,Index Tuning Wizard,PerfMon,SysMon.Make use of these tools to moniter the performance of your query and take the nessesary actions,if required.

That's all,I hope its helpful to you to you