RSS Feeds

Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

SQL Azure

Developer

General DBA

PowerShell

Windows Server

ASP.NET / ADO.NET

SQL Azure

USEFUL SITES :

ASP.NET Tutorials
Windows and SQL Azure Tutorials
Cloud Hosting Magazine
SharePoint Tutorials
Windows Server Help

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

SQL Server Performance Coding Standards

By : Amol Kulkarni
Jul 01, 2008

1. Avoid using “*” in SELECT queries
Always specify the required list of columns in the select list. This will ensure that only the columns required by the query are returned to avoid unnecessary I/O and processing.

2. Always use variables of the appropriate data type and size
Use appropiate data types to avoid implicit data type conversion being perfromed by SQL Server and appropiate sizes to avoid excess usage of memory.

For example, assigning an integer type to a varchar is allowed but SQL Server will implicitly convert the integer data type to varchar which causes additional overhead.

For a column/variable like Age a data type of type integer is required. For this int, smallint or tinyint types can be used. But using int or smallint would require 4 and 2 bytes respectively of memory which is very large to accommodate the Age variable. Hence using tinyint would be a better choice, which would occupy 1 byte and provide a range between 0 and 255.

3. Keep transactions as small as possible
Try and avoid using large transactions. Using large transactions across an entire batch can cause other processes to be locked/blocked and the resources involved in the transaction cannot use them until the transaction is completed (committed or rolled back) causing performance issues in the database or even causing deadlocks.

4. Avoid excessive usage of temp tables
Excessive usage of temp tables may cause tempdb contention which can degrade the overall performance of the system. You can use table variables instead of temp tables, as this can use the memory allocated to that process and avoid usage of tempdb. It is recommended to use temp tables when dealing with considerably large amounts of intermediate data.

When using temp tables avoid using the SELECT INTO clause to create and insert data into temp tables. This will lock the entire tempdb and will cause blocking issues with other processes trying to use the tempdb resources. Always use the CREATE TABLE statement to create the temp table and then use INSERT INTO statement to insert data into the temp table. This wwill avoid tempdb contention and allow other process to use the tempdb resources.

5. Environmental settings
Always use “SET NOCOUNT ON” in stored procedures to avoid unnecessary data traffic on the network.

6. Avoid usage of cursors
Cursors are CPU intensive and make round trips to the CPU for every execution, degrading the overall performance of the batch. Instead use while loops to loop through the data rows. In SQL Server 2005, a CTE can be used to loop through data rows.

7. Never use a function towards left side in the WHERE clause
Using a function towards the left side in the WHERE clause can prevent SQL Server using an index. If needed use the function call on the right side in the WHERE clauses which allows the index to be used for the query.

8. Avoid using the DISTINCT clause
If you need to return a distinct set of rows, use a GROUP BY clause instead of DISTINCT. As the GROUP BY clause is evaluated before the DISTINCT clause.

Ask A Question In the Forums

Next Page>>