Friday, January 25, 2013

NOLOCK vs READPAST IN SQL SERVER

NOLOCK and READPAST table hints in SQL Server


When data in a database is read or modified, the database engine uses special types of controls, called locks, to maintain integrity in the database. Locks basically work by making sure database records involved in a transaction cannot be modified by other transactions until the first transaction has committed, ensuring database consistency.
When designing database applications, you should keep in mind the different types of locks that will be issued, and the different levels of isolation your transactions will occur. Typically, the SQL Server defaults work fine for what you are trying to accomplish. However, there will be times when it is advantageous to manually make hints to how locks are issued on your tables in your SQL statements.


NOLOCK
This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.
The benefit of the statement is that it allows you to keep the database engine from issuing locks against the tables in your queries; this increases concurrency and performance because the database engine does not have to maintain the shared locks involved. The downside is that, because the statement does not issue any locks against the tables being read, some "dirty," uncommitted data could potentially be read. A "dirty" read is one in which the data being read is involved in a transaction from another connection. If that transaction rolls back its work, the data read from the connection using NOLOCK will have read uncommitted data. This type of read makes processing inconsistent and can lead to problems. The trick is being able to know when you should use NOLOCK.
As a side note, NOLOCK queries also run the risk of reading "phantom" data, or data rows that are available in one database transaction read but can be rolled back in another.
The following example shows how NOLOCK works and how dirty reads can occur. In the script below, I begin a transaction and insert a record in the ItemMaster table.
BEGIN TRANSACTION
INSERT INTO ItemMaster
(Product, SaleDate, SalePrice)
VALUES
('PoolTable', GETDATE(), 500)
The transaction is still open, which means that the record that was inserted into the table still has locks issued against it. In a new query window, run the following script, which uses the NOLOCK table hint in returning the number of records in the ItemMaster table.
SELECT COUNT(1) FROM ItemMaster WITH(NOLOCK)
The number of records returned is 11. Since the transaction that entered the record into the ItemMaster table has not been committed, I can undo it. I'll roll back the transaction by issuing the following statement:
ROLLBACK TRANSACTION
This statement removes the record from the ItemMaster table that I previously inserted. Now I run the same SELECT statement that I ran earlier:
SELECT COUNT(1) FROM ItemMaster WITH(NOLOCK)
This time the record count returned is 10. My first query read a record that was not yet committed -- this is a dirty read.so out of 11 records 1 Item PoolTable was a dirty record.


READPAST
This is a much less commonly used table hint than NOLOCK. This hint specifies that the database engine not consider any locked rows or data pages when returning results.
The advantage of this table hint is that, like NOLOCK, blocking does not occur when issuing queries. In addition, dirty reads are not present in READPAST because the hint will not return locked records. The downside of the statement is that, because records are not returned that are locked, it is very difficult to determine if your result set, or modification statement, includes all of the necessary rows. You may need to include some logic in your application to ensure that all of the necessary rows are eventually included.
The READPAST table hint example is very similar to the NOLOCK table hint example. I'll begin a transaction and update one record in the ItemMaster table.

BEGIN TRANSACTION
UPDATE TOP(1) ItemMaster
SET SalePrice = SalePrice + 1

Because I do not commit or roll back the transaction, the locks that were placed on the record that I updated are still in effect. In a new query editor window, run the following script, which uses READPAST on the ItemMaster table to count the number of records in the table.
SELECT COUNT(1)
FROM ItemMaster WITH(READPAST)

My ItemMaster table originally had 10 records in it. The UPDATE statement is currently locking one record in the table. The script above that uses READPAST returns 9 records, which means that because the record I am updating is locked, it is ignored by the READPAST hint.
In ReadPast Row Level Locking are skipped
Read operations that use READPAST do not block. Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes.
To specify the READPAST hint in these cases, remove the READCOMMITTED table hint if present, and include the READCOMMITTEDLOCK table hint in the query
READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.
The READPAST table hint cannot be specified when the READ_COMMITTED_SNAPSHOT database option is set to ON and either of the following conditions is true.

• The transaction isolation level of the session is READ COMMITTED.
• The READCOMMITTED table hint is also specified in the query


Friday, January 11, 2013

Difference Between Exec and sp_executesql

/* Difference Between Exec and sp_executesql*/ sp_executesql (also known as "Forced Statement Caching") • Performance wise good, Recommended • Allows for statements to be parameterized • Only allows parameters where SQL Server would normally allow parameters; however, this string can be built using forms of dynamic constructs. • Has strongly typed variables/parameters – and this can reduce injection and offer some performance benefits! • Creates a plan on first execution (similar to stored procedures) and subsequent executions reuse this plan • Need NVARCHAR Data type otherwise will show error Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. EXEC (also known as "Dynamic String Execution" or DSE) • Performance wise not good, Not recommended • Do not Allows for statements to be parameterized • Allows *any* construct to be built • Treats the statement similarly to an adhoc statement. This means that the statement goes through the same process that adHoc statements do – they are parsed, probably parameterized and possibly deemed "safe" for subsequent executions to re-use • Does not have strongly typed parameters in the adhoc statement and therefore can cause problems when the statements are executed • Does not force a plan to be cached. o This can be a pro in that SQL Server can create a plan for each execution o This can be a con in the SQL Server needs to recompile/optimize for each execution o Works with VARCHAR and NVARCHAR -- Use the following query to test, CREATE TABLE [dbo].[TestExectuionPlan] ( ID INT ) GO INSERT INTO [dbo].[TestExectuionPlan](ID) VALUES (1) INSERT INTO [dbo].[TestExectuionPlan](ID) VALUES (2) GO --- Clean the existing plans DBCC FREEPROCCACHE DECLARE @ItemID INT DECLARE @Query NVARCHAR(200) SET @Query = 'SELECT ''Query Using EXEC'' AS ''EXEC'', * FROM [dbo].[TestExectuionPlan] WHERE ID = ' SET @ItemID = 1 EXEC( @Query + @ItemID) SET @ItemID = 2 EXEC( @Query + @ItemID) SET @Query = 'SELECT ''Query Using sp_executesql'' AS ''SP_EXECUTESQL'', * FROM [dbo].[TestExectuionPlan] WHERE ID = @ID' SET @ItemID = 1 EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID SET @ItemID = 2 EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID -- To view the execution plan, use the following query. SELECT usecounts, sql FROM sys.syscacheobjects where dbid = DB_ID()--- ( to retrieve the execution plan of current database) ---- Check the list of queries in output create for sp_executesql and exec