Thursday, October 23, 2008

NOLOCK in SQL Server to improve performance

Sometimes it is good to use nolock in SQL Server queries, specially when your query is just reading the records. It does improve the performance of the system. The disadvantage is that one may not be sure that they are getting the data which is currently being updated in the Table i.e. Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running.

The sytax for using NOLOCK is:

SELECT * from TblName
WITH (NOLOCK)
WHERE col1=col2

More reference on this:
http://blogs.neudesic.com/blogs/phil_scott/archive/2005/12/05/11.aspx

Force SQL Server to use index

If your table has too many indexes, then I figured out that it is better to force your SQL Server query to use the index which is made for that particular query.

To force the use of index,

SELECT *
FROM tablename WITH (INDEX = IDX_1234)
WHERE Column1 = 'value'

In this example IDX_1234 is the name of the Index.