Category: TempDB

SQL Server Optimizations for High Concurrency

SQL Server Optimizations for High Concurrency Our business needs very robust, low latency, highly available and durable online transactional system which supports high concurrency for about four weeks in a year. It’s almost like Thanksgiving sale where you mark down very popular item

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

Snapshot Isolation in SQL Server

Snapshot Isolation in SQL Server Wiki Article on SnapShot Isolation : Issues with SnapShot Isolation: Kimberly Tripp Video on Isolation Levels : Kendra Little on SnapShot Isolatioin : Microsoft Link: SQL Team Link : Idera Short article

Temporary Tables Vs Table Variables

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

Removing Additional Data Files from TempDB

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.

Checklist for SQL Server Installation

Feature Selection : We don’t want to install all the features. Identify the features needed and install only those features. Model Database Properties : Figure out all the Model database Properties and configure them Correctly. File Layout : Make sure