Best practices to improve the performance of SQL Server

In this article we will see some practices to improve the performance of SQL Server.


1. Using SET NOCOUNT ON property to improve the performance of SQL Server Stored procedures.


In Microsoft SQL Server, when executing a T-SQL statement, it returns "Number of rows affected" by default.

For an example,


USE Northwind

Update ORDERS

SET ShipVia = 2

WHERE OrderID = 10248



After the execution of this query, the message pane displays,

(1 row(s) affected)


Here additional processing is required to determine the number of rows affected by this Query.

By turning off this feature, we can reduce the overall time needed to process a stored procedure.


How to turn off?

This can be accomplished by using the SET NOCOUNT ON command in the top of the above example.


USE Northwind

SET NOCOUNT ON

Update ORDERS

SET ShipVia = 2

WHERE OrderID = 10248



To display the rows affected, we can use SET NOCOUNT OFF command


2. Avoid Using Cursors


The T-SQL cursors are generally used in stored procedures, triggers and Scripts to return the result set of certain query.

Cursors generally use a lot of resources and reduce the performance and scalability of applications.

Cursors are mainly used to perform row by row operations which is in server side code. In order perform row by row operations, we can try out other alternative methods.


Some of the alternative methods to cursor are:

1. Using TempDB

2. Using Derived Tables

3. Using Correlated sub query

4. Perform the operation at client side code


In case of no choice other than server side cursor, try to use either one of the following cursors:

FORWARD ONLY
FAST FORWARD
READ ONLY

These types of cursors can reduce the overhead of sql server atleast.


3. Avoid Using Text and Ntext Data Types

The Disadvantages of Text and Ntext DataTypes, including:

* You cannot directly write or update text data using the INSERT or UPDATE statements. Instead, you have to use special statements like READTEXT, WRITETEXT and UPDATETEXT.

* There are several known issues associated with replicating tables that contain text columns.

* Because of the way SQL Server stores text columns, it can take more time to retrieve text/ntext values.

* Text data types have extra overhead that can negatively impact performance.

Suggestion:

If you don't have to store more than 8KB of text, consider using char(8000) or varchar(8000) data types instead of text and ntext.


Benefits:

1. It helps to improve the performance of application.

2. performance improvement helps to maintain the applications.