Monthly Archives: January 2014

Understanding CXPACKET wait stats in SQL Server

Understanding CXPACKET

From the SQL BOL (Books on Line) CXPACKET is defined as Class Exchange Packet and “occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.”
To get a deeper understanding, it can be explained this way. Let’s say you need to run a single query and you will need a group of threads to process this query. SQL Server will assign each thread a task from the query. Some of the threads will finish faster and others will lag behind. The threads that finish or execute faster will then tell the lagging threads that they’re done with their assigned task hence, they are waiting. The CXPACKET wait stat is flagged by the threads who finished earlier and not by the threads that are lagging behind.
To be clear, CXPACKET wait stats does not exactly pose a problem. But it can be an alert or warning that something needs to be tuned up in the running query. Or you can check if you need to add the OPTION (MAXDOP 1) hint when running your query.

Parallel execution can be a highly effective way of dealing with large sets of data, say in data warehousing or reporting server scenario. But in OLTP systems, this is not as effective and can have a negative impact on performance.

One query that I use often when I my server is having too many CXPACKET wait stats is pasted below. You can fine tune this to suit your requirements and also add hint for OPTION (MAXDOP 1)

SELECT TOP 10 st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC

Important points for CXPACKET wait type and parallelism

• OLAP/reporting related queries benefits from parallelism but not OLTP systems.
• Look at your plan cache, which of your queries use parallelism?
• Check query plans – do you have missing indexes?
• When was the last time you ran sp_updatestats?
• What other waits are occurring that might be causing CXPACKET?
• Consider using MAXDOP to fine tune specific queries. Again test this first in a staging environment.
• Set max degree of parallelism and cost threshold for parallelism to best suit your server’s function.
• Do not adjust max degree of parallelism without proper analysis of your production servers workload and queries. Make this your last resort!


Understanding the Copy_Only option in Backups for SQL Server

The Copy_Only option is very useful in full backup scenario when you don’t want to break up the sequence of log sequence number in conventional SQL Server backups.

For example, when you have a schedule of daily full backups and differential backup of every six hours daily. Then you need to run a weekly full backup using the copy_only option so as not to break the LSN between your daily full backups and differential backups.

To use this copy_only option you may run below statement

BACKUP DATABASE <databasename> TO <disk> …  WITH COPY_ONLY …..

When using doing this via the SSMS, in the backup database dialog box, select the Copy Only Backup option. This option however is only available from SQL Server 2005 and later.



Reviewer #17 Database Replication in SQL Server 2012

Wow, my first article for the year 2014! Here’s to a blessed and fruitful year ahead for all of us ❤

Database Replication 

This topic is part 3 and the last topic of lesson in High Availability which covers 12% of the certification exam.

Replication enables the SQL Server 2012 to distribute and synchronise data and database objects from one database to another.

Replication Architecture

SQL Server 2012 replication uses specific terminology to describe how components interact and work together in the replication topology. Very much similar to a news print or magazine publication i might say.

Publisher – An instance that makes data available in through publication

Article – the published object. This are the tables, schemas, stored procedure, views, indexed view or a UDF that is published in the replication topology.

Publication – the collection of articles published.

Distributor – An instance that manages the transmission from publisher to subscriber. A distributor on the same LAN as the publisher is termed as a local distributor while a distributor on a remote  location from the publisher is a remote distributor.

Subscriber – the instance that receives the published articles.

Agent – the services that enables the publisher, subscriber and distributor to perform the replication tasks.

Replication Types

Snapshot Replication – Enables the complete refreshes of data rather than updating the database on an incremental basis. This replication type is most suitable for infrequent data changes or only a small volume of data needs to be replicated.

Transactional Replication – Suited for the instance-to-instance situations that require subscriber databases to stay up to date with changes from the publisher.

Peer-to-Peer Replication – Enables peer nodes to read and write changes and still have those changes replicated to other nodes in the replication topology.

Merge Replication – Suited for mobile and distributed server applications when data conflict is possible and any node may publish changes that is synchronised across all nodes.

Configuring Snapshot Replication

When configuring for a snapshot replication, you need to determine the schedule for the snapshot agent to complete a snapshot. The snapshot agent uses the BCP utility to generate a snapshot. You can set the snapshots to be compressed. However, compressing snapshots takes additional time and resources and due to the limitations of CAB file format, you cannot compress snapshot files over 2GB. The following are steps to configure snapshot replication.

1. On the node that hosts the database that will act as your publisher, expand the Replication, right click Local Publications, select New Publication to launch the New Publication Wizard.

2. When you come to the Distributor page, choose the local instance as the distributor.

3. On the SQL Server Agent start page, configure the SQL Server agent service to start automatically.

4. On the snapshot folder page, specify the network share for storing the snapshots. You must ensure that the replication a gents have the appropriate permissions to this shared folder. The publisher must be able to write to this shared folder and also the subscriber must be able to read from this shared folder.

5. On the publication database page, select the database you wish to publish.

6. On the publication type page, select snapshot replication.

7. On the articles page, select the objects you wish to be included in the publication.

8. Use the filtered rows page if you have the need to filter the content of the tables in the publication.

9. On the snapshot agent page, choose to create a snapshot immediately and then configure a snapshot creation schedule.

10. On the agent security page, click security settings to choose the snapshot agent security account. You can choose to run under a windows domain account or use the SQL Server Agent service account. You can also configure the account that connects to the publisher instance.

11. On the Wizard actions page, you can choose to create the publication and generate a script file with steps to create the publication. You can save this script for reference later.

Configuring a Subscription

When configuring a subscription to a snapshot replication, connect to the subscriber instance and do the following steps;

1. Right click on Replication\Local Subscriptions on your subscriber instance and click on New Subscription

2. On the publication page, use the drop down list to select your publisher. Click Connect. Select the database and the publication.

3. On the Distribution Agent Location page, choose to run each agent at its subscriber to configure pulls publication.

4. On the Subscriber page, select <New Database>, provide the name of subscriber database and click OK.

5. On the Distributor Agent Security page, click (…) and enter the credentials the Distribution Agent process will use.

6. On the Synchronization Schedule page, choose Run Continously for the Agent schedule.

7. On the Intialize Subscription page, choose Immediately under Initialize When.

8. On the Wizard Actions page, choose Create The Subscription, then proceed to Finish.

To simulate the steps above, I created 2 instances on my laptop and it was fairly easy to set it up.

Transactional Replication

This is the most suitable type of replication when you want your changes from the publisher synchronised with the subscriber as they happen. The replication direction is only one-way, from the publisher to the subscriber.

To configure the transaction replication to publish your articles and to use the local instance distributor, you may do the following steps;

1.  Right click on Replication\Local Publication and choose New Publication to launch the wizard.

2. On the Publication Database page, select the database that will act as the publisher.

3. On the Publication Type page, choose Transactional Replication.

4. On the Articles page, select the objects you wish to include your publication.

5. Use the Filter Table Rows page if you have needs to filter the table row data. But for testing i usually just bypass this page.

6. On the Snapshot Agent page, choose Create a Snapshot Immediately and Keep The Snapshot Available To Initialize Subscriptions. In a way you can also configure your snapshot agent to run according to a schedule so new subscriptions are created from an updated snapshot.

7. On the Agent Security page, click Security Settings to specify credentials of the Snapshot Agent and the Log Reader Agent.

8. On the Wizard Actions page, choose Create the Publication. You may also click on Create the Script and save the script for reference.

9. On Complete the Wizard page, provide the Publication name and proceed to Finish.

After the snapshot is created, you can subscribe to the transactional publication through the Local Subscriptions node on the Subscriber instance.

After the successful configuration, you should be able to see the replication when you expand Local Publications.


Peer-to-Peer Transactional Replication

Peer-to-Peer transactional replication enables transactions performed by subscribers to be synchronised with other nodes in the replication topology. When you implement peer-to-peer transactional replication, you must prevent conflicts by partitioning the data so that the same row will not be updated at separate locations. If the data is not partitioned properly and a conflict occurs, the replication will fail.

Peer-to-peer  transactional replication has the following conditions;

  • All the instances that participate in the replication topology must be running SQL Server 2012 Enterprise Edition.
  • You cannot use row and column filtering.
  • Publication names must be identical on all instances participating in the replication.
  • Each participating instance should use its own distribution database to ensure that there is no single point of failure.
  • You cannot include tables in multiple peer-to-peer publications in the same publication database.
  • You cannot reinitialise peer-to-peer subscriptions. You must restore a backup at a node if you must force an update of data.
  • If you add nodes to a peer-to-peer topology and have to perform a restoration, you need to restore only from backups created with the new nodes participating in the topology.

To configure a peer-to-peer publication that uses the local instances as the distributor, see steps below;

  1. Go to Replication, right click on Local Publications and choose New Publication to launch the New Publication Wizard.
  2. On the Distributor page, choose the local instance as the distributor.
  3. On the SQL Server Agent Start page, configure the SQL Server Agent service to start automatically.
  4. On the Snapshot Folder page, provide the location of a network share folder that the other agents participating in the replication can access.
  5. On the Publication Database page, choose the database that hosts the data or objects you will replicate.
  6. On the Publication Type page, select Peer-to-Peer Publication.
  7. On the Articles page, select the objects you want to include in the publication.
  8. On the Agent Security page, click Security Settings and then specify the credentials of the Log Reader agent.
  9. On the Wizard Actions page, select Create the Publication.
  10. Provide the Publication name and proceed to Finish.

Merge Replication

Merge Replication enables databases hosted on instances at separate locations to be updated and those changes replicated to all other database participating in the replication topology.

When configuring merge replication, the publisher and subscribers track changes made by using triggers. When a subscriber synchronises with the publisher, they exchange all rows that have changed between the publisher and subscriber since the last synchronisation.

Merge replication is most suitable for the following requirements;

  • You have the database updated at multiple locations. The publication is multi direction across all the nodes.
  • You have enabled subscribers to make offline changes to data and then to synchronise those changes back when they connect.
  • You are able to detect and resolve update conflicts.
  • Your configuration supports non-SQL Server nodes participating in the replication.
  • Applications do not require tables to be transactionally consistent.

Merge replication makes use of the SQL Server Snapshot Agent and the Merge Agent. If the Merge Agent detects a conflict, such as the same row being updated with different values at different locations, the conflict resolvers determines which data is accepted.

When you start to implement a subscription in merge replication, you need to specify a subscription type and conflict resolution priority. A server subscription enables you to set a conflict resolution priority, which is a figure between 0 and 99.99, with servers assigned a higher priority overriding servers assigned with lower priority. When configuring the Client subscription type, the priority for conflict resolution is “whoever is first to publisher wins”. The default type is Server, and default  priority is 75. To view the conflicts, right click on Merge Publication and select View Conflicts. Conflicts are viewable only if conflicts have occurred. When a conflict occurs between a publisher and subscriber, the publisher change is kept and the subscriber change is dropped.

To configure a merge replication, you may follow the steps for configuring a Transactional Replication discussed above, but selecting Merge Publication in the Publication Type page. On the Subscriber Types page, choose SQL Server 2008 or later versions.  Only subscribers with SQL Server 2008 or later are supported. Proceed to Create and Name the Publication.

Replication Monitor

Replication Monitor is a tool built inside the SQL Server Management Studio (SSMS) that enables you to monitor replication. Only members of the sysadmin fixed server role can perform monitoring on the Distributor instance or have been assigned membership of the replmonitor database role in the distribution database.

You may add a publisher from the Replication Monitor tool. Right click on the Replication Monitor node and choose Add Publisher to bring up the Add Publisher dialog box. You can also add a SQL Server Publisher or a Oracle Publisher or add a Distributor and add its publishers. This final option enables you to view all publications associated with a specific distributor.

From the Replication Monitor Tool, you can configure alerts based on performance benchmarks. When a monitored value exceeds the benchmark, Replication Monitor displays an alert in the Status column for the subscription and the publication with which it synchronises. You can also configure an alert to send email or run SQL Server Agent jobs.

Replication Monitor also provides performance-quality information for the transactional replication and merge replication. To set a threshold, select the publication you wish to monitor, click the Warnings tab, choose the warning and set the threshold value. In the same manner, to configure an alert for the selected publication, on the Warnings tab, click Configure Alerts. Select the alert you will configure and click Configure.

Replication Monitor

The following maximum sizes and numbers apply to SQL Server 2012 replication;

  • Merge publication articles 256
  • Snapshot or transactional publication articles 32767
  • Merge publications columns in a table 246
  • Snapshot or transactional publications columns in a table 1000
  • Merge replication bytes for column used in a filter 1024
  • Snapshot or transactional publication bytes for a column used in a row filter 8000

For more information on replicating data  between SQL Server, Oracle and IBM DB2 database, visit


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali -

Meels on Wheels

Meels for breakfast, lunch and dinner.

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment