SQL Server Resource Governor

Microsoft has introduced an interesting and exciting new feature in SQL Server 2008; it is named the Resource Governor. It allows the user to limit the CPU and memory usage by a specific application or user. It would help in preventing any particular user / application to eat up all the resources of the SQL…

Performance Tuning, Resource Governor, SQL Server, SQL Server Administration Vamshi B October 3, 2011 @ 4:24 pm

SQL Server Memory

SQL Server divides the total memory dedicated to it into two parts referred to as Buffer Pool and Memory to Leave. Buffer Pool is the larger of the two and is used for most memory operations like stored procedure query plan generation, caching data and creating indexes. Memory to Leave is dedicated to executing linked…

Performance Tuning, SQL Server, SQL Server Administration Vamshi B @ 12:01 pm

SQL Server Optimistic Concurrency

SQL Server Concurrency Terminology Optimistic concurrency control inside the SQL Server 2005/2008 database engine technically means that row versioning is used instead of a pessimistic locking approach. I believe this is a source of much confusion to both application developers and DBAs because row versioning isn’t required for applications to use optimistic concurrency control by…

Concurrency Control, Dealocks, Performance Tuning, SQL Server, SQL Server Administration, Temporary Database Vamshi B October 1, 2011 @ 6:06 pm

SQL Server Disk I/O Specific issues

Top Waits Stats rollup query

The query which can be used to identify Top Wait Stats is given below. It is used to Isolate top waits for server instance since last restart. This query has been really helpfull in identifying various issues as I/O issues, CPU Bottlenecks etc. Top Waits Stats rollup query — Isolate top waits for server instance…

SQL Server, SQL Server Administration, Wait Stats Vamshi B @ 3:30 pm

Transaction log file VLF management

One of the important things I would recommend is not having transaction log VLFs be too large, with 500MB being recommended maximum, so that it is not to interrupt the clearing/truncating of the log.The Reason for this is that transaction log could only clear a VLF when TLog Backup is taking place only when it’s…

SQL Server, SQL Server Administration, Transaction Log Vamshi B @ 1:54 pm