SQL Server, TempDB

TempDB Contention

TempDB contention: TempDB plays very crucial in well performing SQL Server engine. These are few simple design decisions , which helps to avoid the performance bottlenecks in the long run. As a general rule, if the number of logical processors is less than or equal to …

SQL Server, TempDB

Snapshot Isolation in SQL Server

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 …

Interview Questions, SQL Server

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 …

SQL Server, Technology

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 …

SQL Server

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) …

AWS RDS, SQL Server

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 …