Dynamic Management Views in SQL Server

Dynamic Management Views in SQL Server exists in SYS schema and their names start with DM_. To query a DMV, we should prefix the view name with SYS. Ex: To know total physical memory of the SQL Server we can use: SELECT (Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb FROM sys.dm_os_sys_info The Mostly used DMV’s to check the current…

Performance Tuning, SQL Server, SQL Server Administration Vamshi B October 8, 2011 @ 4:40 pm

Creating a Central Management Datawarehouse in SQL Server 2008

What is the Management Data Warehouse? The Management Data Warehouse(MDW) is a single database/Data Warehouse created inside of a SQL Server 2008 instance, which holds various performance related statistics. The performance statistics in the MDW are gathered via special data gathering routines, known as Data Collections. The MDW can contain Data Collection information from a…

MDW, Performance Tuning, SQL Server, SQL Server Administration Vamshi B October 7, 2011 @ 10:05 am

SQL 2008 Transactional Replication and initializing from a backup file

In some environments,running Fresh snapshot will take ages to synchronise the subscribers with publishers. Even if it is done in low activity hours, it will run into business hours because of time takes to take snapshot, copy, restoring snapshot, synchronising transactions again. There is way in which the replication can be setup by initialising using…

Replication, SQL Server, SQL Server Administration Vamshi B October 4, 2011 @ 8:49 am

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