Home » SQL Server » Page 668

SQL Server

sql server DBA 999

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 a backup in SQL Server 2008. This new feature is very useful in environments where it takes a lot of time to run a fresh Snapshot/Re-initialise the replication.
I have followed a following set of steps in setting this up and testing:

Step No Steps Followed
1 Set up the
… Read the rest
sql server DBA 999

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 Server.
Terminologies:
Resource Pool
It is the collection of physical resources of the database management system. It is viewed as a virtual SQL Server instance within the SQL Server as visible by the application. It is defined with minimum and maximum values of resource (memory and CPU) utilization. SQL Server 2008 allows a maximum of 18 user defined pools and two built-in pools. There are … Read the rest

sql server DBA 999

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 server queries, extended stored procedure calls and OLE automation procedure calls. By default, Memory to Leave is 384 MB and the balance of total memory available to SQL Server will determine the size of Buffer Pool.
An important counter of the performance of buffer cache is the Buffer Cache Hit Ratio performance counter. It indicates the percentage of data pages found in the buffer cache … Read the rest

sql server DBA 999

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 using hints like (nolock).
Applications have used optimistic concurrency long before row versioning was introduced in SQL Server 2005/2008. In fact, the rowversion data type (a.k.a. timestamp) exists specifically to facilitate optimistic concurrency control.

Choosing the SQL Server concurrency control features (transaction isolation levels, transactions, locking hints) that are most appropriate for the task at hand without getting tripped up on terminology. For example, SQL … Read the rest

sql server DBA 999

SQL Server Disk I/O Specific issues

If you suspect that your SQL Server instance is having Disk I/O issues, these queries can help furnish proof that you are under I/O pressure, and they can help pinpoint what is causing the problem.
Before you run determine that you have I/O issues, please verify that by running Top Wait Stats rollup query.


-- Always look at Avg Disk Sec/Read and Avg Disk Sec/Write in PerfMon for each Physical Disk

-- Check for IO Bottlenecks (run multiple times, look for values above zero)
SELECT cpu_id, pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE';

-- Look at average for all schedulers (run multiple times, look for values above zero)
SELECT AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE';… Read the rest

sql server DBA 999

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 since last restart
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP'
,'CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS … Read the rest

sql server DBA 999

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 completely inactive. So that means larger the VLF then bigger VLF to be cleared and for it to inactive.

Other important thing I would recommend is not having too many transaction log VLFs. Having an excessive number of VLFs can negatively impact all transaction log related activities and you may even see degradation in performance when transaction log backups occur. Often, when transaction logs are … Read the rest