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 … Read the rest

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)) … Read the rest

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

Adding Article to Transactional Replication Publisher without Snapshot Re-initialisation

Add article to Transactional Replication without Snapshot Re-initialisation

To add a new article to a publisher efficiently without reinitializing whole replication but running the below scripts/procedure can be done without whole snapshot to be run again. This method only snapshots the new article added to the publisher.

The Steps needs to be followed in same order.

Step No Steps
1 EXEC sp_changepublication @publication = ‘ContosoPub’
@property = N’allow_anonymous’
@value = ‘false’
GO
Change the property of replication.
2 EXEC sp_changepublication @publication = ‘ContosoPub’
@property = N’immediate_sync’
@value = ‘false’
GO
Change the property of replication.
3 add article to publication either by script below or GUI
EXEC sp_addarticle @publication = ‘ContosoPub’
4 add article to subcriber either by script below
Read the rest

SQL Server Transparent Data Encryption

SQL Server 2008 Transparent Data Encryption (TDE)
A secure Environment can be built by designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. … Read the rest

Administration of Transactional Replication

Administration of Transactional Replication

There are 3 types of Replication Types in SQL Server:

1. Snapshot Replication
2. Transactional Replication
3. Merge Replication

Replication of data can be configured between same server or can be different servers connected by Network. SQL Server replication uses a publisher, distributor and subscriber for replication to work
.
Publisher is the database that sends its data to another database which can be located in same or another server.

Subscriber is the database that receives data from another database which can be located in same or another server.

Distributor is the server that manages the flow of data through the replication system. This server contains the distribution database.

There are 3 steps involved for Configuring

Read the rest

Transactional Replication

Transactional Replication in SQL Server 2008:Transactional replication will start with a snapshot of the publication database objects and data which is called as initial snapshot. Subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed.
By default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that allow updates at the Subscriber.
Transactional replication is implemented by the SQL … Read the rest

Log Shipping in SQL Server 2008

Log Shipping in SQL Server 2008
Log shipping allows us to automatically send transaction log backups from a primary database server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. There is option to include another server instance known as the monitor server which will record the history and status of backup and restore operations and be configured to raise alerts if these operations fail to occur as scheduled.
Log shipping consists of three operations:
1. Back up the transaction log at the primary server instance.
2. Copy the transaction log file to the secondary server instance.
3. Restore the log backup on the … Read the rest

Database Mirroring in SQL Server 2008

Database Mirroring in SQL Server 2008:
In database mirroring we can opt to either synchronous or asynchronous DB mirroring.
In asynchronous DB Mirroring the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance.
In synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.
There are two mirroring operating modes as below;
1. In High-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.
2. In … Read the rest