Sql-server – How to set up replication on SQL Server 2008

In the SQLOS a scheduler is created for each logical processor that SQL Server sees. With hyperthreading enabled this equates to double the schedulers. One of the purposes for the SQLOS is to minimize and prevent context switching from occuring which is why only one scheduler is created for each logical processor. Once the SQLOS creates the schedulers, the total number of workers is divided amongst the schedulers. The SQLOS implements a form of cooperative scheduling in that the workers yield the scheduler as it requires unavailable resources, or reaches its execution quantum allowing other workers to execute on the scheduler. This keeps context switching to a minimum since the scheduler is doing the work and they are bound one for one.

Understanding that background, hyperthreading somewhat works opposite of how SQLOS is specifically designed to function. Specifically, parallelism can be problematic with hyperthreading and can result in high CXPACKET waits since SQLOS may try to run a query at DOP 8 on what is reality a DOP 4 system. If your CPU utilization is low, you might not notice, but the higher your CPU utilization goes the more problematic it could become. I recently had a discussion on twitter regarding this, and the consensus was "It Depends" as to whether or not it would help or hurt.

If you have a lot of signal waits on your server, but you have low CPU utilization, you may see benefit enabling hyperthreading which will double your internal schedulers and spread the workers out more which means they won't wait to execute in the runnable queue as long. However if your workload utilized parallelism heavily you get heavy CXPACKET waits in sys.dm_os_wait_stats, you might look at disabling hyperthreading to see if it reduces your waits.

Check the Publication Properties-->Snapshot-->Location of snapshot Files-- Make sure put files in the default folder is checked, in my case it was't check marked instead put files in the following folder was checked with a location address(C:\REPL) but i had my distributor in a different server other than publisher and the location address was misleading to distributor agent .