Category: TempDB

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 …

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 …

Temporary Tables Vs Table Variables Table Variables : No Transaction Log Overhead No Lock Overhead No Recompilations No Rollback Maintenance You can only have one Primary Key or UNIQUE Constraint. You can have only one INDEX Usually it doesn’t trigger Statement or Stored Procedure re-compilations …

I had a need to remove TempDB DataFiles in one of our environments. When I ran the simple DBCC SHRINKFILE Command, I encountered following error. DBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page. DBCC SHRINKFILE (N’tempdev9′ , EMPTYFILE) …