Sql Server Faq

We have observed a few cases now where sql server has started performing slow after changing the Cost Threshold Of Parallelism server option of the sql server.

Ironically in such cases we observe that sql server is performing slow even when there is no high CPU utilization and no high I/O operations on the server. Increasing the Cost Threshold Of Parallelism decreasing your CPU utilization as it reduces the Parallelism in the execution plan of some of the queries which are fired on the database and in turn slows their response time and hence the performance of sql server deprecates.

As defined in the BOL,

Cost threshold for parallelism option is used to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. Only set cost threshold for parallelism on symmetric multiprocessors.
The Default Value of the Cost Threshold Of Parallelism is 5 which means when Optimizer creates and estimates the cost of the query if the cost exceeds 5, optimizer considers this query as a candidate for the parallel execution plan and hence it selects the Parallel plan for this query. However the Degree of Parallelism depends on the Number of Processors in the System and the MAX DOP setting of the sql server. Based on these values optimizer selects a parallel execution plan which consumes higher CPU but with much better response time of the queries and hence the performance of sql server increases.

Cost Threshold of parallelism can also be used to reduce the parallelism in the sql server if the parallel queries consume excessive CPU so we can increase the Cost Degree of Parallelism and change the behavior of sql server to use serial execution plans.

Usually it is always a good option to go with default value for the Cost Threshold Of Parallelism and avoid manipulating the behavior of optimizer unless it is tested on your environment with the same workload as production.

You are able to submit the data in the table using the application like InfoPath 2007 but when you go ahead and add the table in the Merge Replication Publication, the insert into the table using the InfoPath form starts to fail. This issue occurs when we have the application designed to insert data into multiple tables and the tables have master-child relationship with primary key in the tables set for identity value.

The error message you will see is similar to the below,

Error Message:-
InfoPath cannot submit the form.
An error occurred while the form was being submitted.
Cannot insert the value NULL into column ‘<Column_Name>’, table ‘<Table_Name>’; column does not allow nulls. INSERT fails.

When you remove the article from Replication, you will find the insert statement working again.

If you take an SQL Profiler during the failure on SQL Server, You can find that the application runs queries similar to the below,
SELECT @@identity

Then it inserts into the child table using the value returned by the above “SELECT @@identity” statement

The query “SELECT @@identity” returns incorrect results from the application perspective and the insert fails. This result is different because the Replication creates Triggers on the table which goes ahead and inserts data into the replication metadata tables during the operation. This increases the identity value and hence the application gets the updated identity value when it tries to insert into the child table. This causes the failure of the insert.

This is a known issue with ADO 2.8. The KB 951937 article provides a hotfix to correct this issue,

On a computer that is running Windows Vista, Windows Server 2008, or Windows XP, an incorrect value is returned when an application queries the identity column value of a newly inserted row in various versions of SQL Server 2005 and of SQL Server 2000

http://support.microsoft.com/?id=951937

We get the below error message when distribution agent tries to sync,

Error: 14151, Severity: 18, State: 1.
The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.

CAUSE:
======
When we check the Publication properties, we find that the options for subscription expiration are marked as “Subscriptions never expire”.

When we check the sql server error log, we see that the following error is reported in the error log,

Message in Errorlog :-
Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 scheduled for retry. Query timeout expired
Error: 14151, Severity: 18, State: 1.
Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.
Error: 14151, Severity: 18, State: 1.
Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.
Error: 14151, Severity: 18, State: 1.

Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.

From the above error log it is clear that the Subscriber was deactivated due to some reason.

So in order to find the cause of deactivation we need to check the Transaction Retention period in the Distribution properties. If it is set to a lower number like 3 hours, then this indicates that if the subscriber does not synchronies in 3 hours, the distribution cleanup agent will truncate the commands tables in distribution and hence the subscriber will be marked as deactivated.

Since the commands are deleted from distributor there is no other option but to reinitialize the subscriber.

Also to find the cause of the Distributor not being able to sync we can check the Distribution history in the below tables on distribution database,

msdistribution_agents
msdistribution_history