Database Optimization Techniques to Increase SQL SERVER Performance – Part I – Increase SQL Server stored procedure performance

Benny May 19, 2009 0 comments

1) Increase SQL Server stored procedure performance with following three tips:

Stored procedures play a vital role in enhancing the performance of the database. The following three tips will help us to maximize database performance when stored procedures are used.

Use NOCOUNT:
If NOCOUNT option is turned off, on each operation: stored procedures returns information concerning the number of rows affected and it increases network overhead. By default, the Option NOCOUNT will be turned off.  If we turn on NOCOUNT option, stored procedures will block row count information so that it will reduce the network overhead occupied with information communication to the user.

Use return values:
           Queries are used to validate information or to return a single value. The return value of stored procedure can be used to retrieve a single value from a query and mainly useful when inserting a new record. The information necessary for retrieval is the primary key value. Place the statement “RETURN <value>” at the end of the query to utilize the return value in stored procedure.

Optimize table access with NOLOCK:
          Transaction safety is not mandatory for most of the database access. This is apparent in MySQL database product that it does not contribute any record locking capability. Any access to a database table or a stored procedure in SQL will make tremendous performance gains when a table hint is used, which lets the SQL engine ignore and not perform locks for a given function.