SQL Performance Tips 2

Hi All,

Here are some more tips on SQL Performance.

SET NOCOUNT
So what is it useful for?
First of all what is it doing: Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

Which means that when you do not need the number of rows affected to be returned from the SPROC then write that at the beginning of the SPROC. Why? because it will reduce the traffic and the execution time of your SPROC.



Even Microsoft states: "For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced".

WITH (NOLOCK)
What is it doing?
It allows MS SQL Server to ignore the normal locks that are placed and held for a transaction and allows the query to complete without having to wait for the first transaction to finish and therefore release the locks.Using NOLOCK gives significant improvement on large tables, where insert / update commands cantake 3-15 seconds. However you need to be very carefully with using NOLOCK. Remember you can get some records that were partially updated or inserted. It is safe to use NOLOCK on rows that you know are not changing right now.For example, records that belong to some user and he is running reports, but not updates, however some users can do updates / inserts at the same time.

New Tips to follow.