Snapshot Isolation in SQL Server Wiki Article on SnapShot Isolation : http://en.wikipedia.org/wiki/Snapshot_isolation Issues with SnapShot Isolation: http://sqlperformance.com/2014/06/sql-performance/the-snapshot-isolation-level Kimberly Tripp Video on Isolation Levels : http://download.microsoft.com/download/6/7/9/679B8E59-A014-4D88-9449-701493F2F9FD/HDI-ITPro-TechNet-mp4video-MCM_11_SnapshotIsolationLecture(4).m4v Kendra Little on SnapShot Isolatioin : http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/ Microsoft Link: https://msdn.microsoft.com/en-us/library/ms188277(v=sql.105).aspx https://msdn.microsoft.com/en-us/library/bb522682.aspx SQL Team Link : http://www.sqlteam.com/article/transaction-isolation-and-the-new-snapshot-isolation-level Idera Short article on TempDB : http://sqlmag.com/site-files/sqlmag.com/files/uploads/2014/01/IderaWP_Demystifyingtempdb.pdf Jim Gray …
SQL Server Locking
Microsoft Link : https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx Locks held by Current Processes: SQL Server 2008 R2 https://technet.microsoft.com/en-us/library/ms189497(v=sql.105).aspx SQL Team Primer on Locks : http://www.sqlteam.com/article/introduction-to-locking-in-sql-server Kalen Delany Crisp explanation on Update Locks: http://sqlblog.com/blogs/kalen_delaney/archive/2009/11/13/update-locks.aspx UPDATE locks are not really a separate kind of lock, but rather are a hybrid of …
Random GUID or Sequential GUID
Random GUID or Sequential GUID SQL Server has two in-built functions for creating uniqueidentifier. NEWID() NEWSEQUENTIALID() New NEWSEQUENTIALID would help to reduce page splits and fragmentation because of sequential nature of the values returned from this function. NEWID() is truly (almost) random and it would …
More CPU’s in SQL Server : Does it Help or Hurt? (MAXDOP)
More CPU’s in SQL Server : Does it Help or Hurt? (MAXDOP): Common sense tells us , having more CPU’s mean more processing power , that means things would be done faster. Hold on for a sec..Its SQL Server. Common Sense isn’t applicable here. PUN …
SQL Handle and Plan Handle to Text – Quick Reference
SQL Handle to Text – Quick Reference DECLARE @pStartOffSet INT DECLARE @pEndOffSet INT DECLARE @pSQLHandle VARBINARY(64) SELECT @pSQLHandle = 0x030008001374fc0232620901c7a300000100000000000000 ,@pStartOffSet = 1788,@pEndOffSet = 3042 SELECT SUBSTRING(st.text, (@pStartOffSet/2)+1, ((CASE @pEndOffSet WHEN -1 THEN DATALENGTH(st.text) ELSE @pEndOffSet END – @pStartOffSet)/2) + 1) AS statement_text FROM sys.dm_exec_sql_text(@pSQLHandle) …
MERGE Statement and Deadlocks
MERGE Statement and Deadlocks: We have MERGE SQL Statement to satisfy our functional needs. Instead of writing separate Insert / Update (UPSERT) Statement we decided to use MERGE Statement because of its simplicity in syntax. MERGE test.Target AS t USING(SELECT ItemID, Score, TargetText FROM @SrcTable …