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

 

Have you ever wondered how to change the keys for the encryption for an already encrypted database. For this you can use “Key Rotation”. Key rotation is the process of decrypting data with the old encryption key and encrypting the data with the new encryption key.

But how to use the Key Rotation with SQL Server 2005??

Below are specified a few details as to how you can go ahead and use the Key Rotation to change the key with which the data has been encrypted.

1. You can add an encryption by a temporary new certificate.
2. Drop the old encryption and the old certificate.
3. Create a new certificate with the old name, add an encryption by it.
4. Finally drop the encryption made with the temporary certificate and drop the temporary certificate as well.

And with this you have the data encrypted with a new key and the old key has been dropped.

You can refer to this blog by Laurentiu Cristofor of the Microsoft SQL Server Security team for more details and the alternatives as well.

 

Kerberos is a network authentication protocol which can be used only with TCP/IP protocol. So if the client connects to the sql server with the Named Pipe Alias Kerberos is not used. Every service that will use Kerberos authentication needs to have an SPN set for it so that clients can identify the service on the network. If an SPN is not set for a service, then clients will have no way of locating that service. Without properly setting the SPNs, Kerberos authentication is not possible.

For more information on SPNs, please refer to the below article,

http://msdn.microsoft.com/en-us/library/ms677949.aspx

 

To use Kerberos authentication, you must make sure that all the following conditions are true:

•	Both the server and the client computers must be members of the same Windows domain or members of trusted domains.
•	The server’s service principal name (SPN) must be registered in the Active Directory directory service.
•	The instance of SQL Server 2005 must enable the TCP/IP protocol.
•	The client must connect to the instance of SQL Server 2005 by using the TCP/IP protocol. For example, you can put the TCP/IP protocol at the top of the client’s protocol order. Or you can add the prefix “tcp:” in the connection string to specify that the connection will use the TCP/IP protocol.

What is SPN?

Service Principal Name (SPN) is the identity of a service which is started with the particular Domain Account running in that domain. SPNs are registered at the Domain Controller (Active Directory).

 To register the SPN at the AD we need to provide the following information

1)       The service

2)       The Fully Qualified Domain Name (FQDN) of the system on which the service is installed

3)       The TCP/IP port no. on which the service is listening. (Since we can have multiple services of same type running on the same system but on the different host for e.g  sql server)

4)       The startup account with which the service is started

SPNs are used by the clients who are connecting to that sql server service using windows integrated authentication mechanism

 When SQL Server is running under the local system account or under a domain administrator account, the instance will automatically register the SPN in the following format when the instance starts:

MSSQLSvc/<FQDN>:<tcpport>

 

C:\Program Files\Resource Kit>setspn -L pariks-2003

Registered ServicePrincipalNames for CN= pariks -2003,OU=Workstations,OU=Machines,DC=testdc,DC=corporate,DC=sampledomain,DC=com:

MSSQLSvc/ pariks -2003.testdc.corporate.sampledomain.com:1393

SMTPSVC/ pariks -2003

SMTPSVC/ pariks -2003.testdc.corporate.sampledomain.com

HOST/ pariks -2003

HOST/ pariks -2003.testdc.corporate.sampledomain.com

 

You must manually register the SPN for the instance under a domain administrator account to use Kerberos authentication. To register the SPN, you can use the SetSPN.exe tool that is included with the Microsoft Windows 2000 Server Resource Kit. This tool is also included with the Microsoft Windows Server 2003 Support Tools. The Windows Server 2003 Support Tools are included in Microsoft Windows Server 2003 Service Pack 1 (SP1).

You can use a command that is similar to the following to register an SPN for an instance:

SetSPN –A MSSQLSvc/<ComputerName>.<DomainName>:1433 <AccountName>

If an SPN already exists, you must delete the SPN before you can reregister it. You may have to do this if the account mapping has changed. To deleted an existing SPN, you can use the SetSPN.exe tool together with the -D switch.

After you connected to an instance of SQL Server 2005, run the following Transact-SQL statement in SQL Server Management Studio:

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

If SQL Server is using Kerberos authentication, a character string that is listed as “KERBEROS” appears in the auth_scheme column in the result window

In order to verify that Kerberos is setup and working fine we can use KerbTray utility. Kerbtray utility can be downloaded from

http://www.microsoft.com/downloads/details.aspx?FamilyID=4e3a58be-29f6-49f6-85be-e866af8e7a88&DisplayLang=en

How to use KerbTray utility

1)       Install the KerbTray utility from the above link and install it in C:\Program Files\Resource Kit

2)       On Installing it a GREEN icon appears on the Task Bar on the right hand bottom of the screen

3)       Right Click on the icon and click purge tickets to purge all the tickets received by that machine

4)       Wait until the green icon turns yellow. When the icon turns yellow fire the following command from the cmd prompt

C:\Program Files\Resource Kit>net session /d

The command completed successfully.

 

5)       Now we can request the ticket from the AD using the following command from the cmd prompt

                 C:\Program Files\Resource Kit>set L   (Press Enter)

                 lib=C:\Program Files\SQLXML 4.0\bin\

                 LOGONSERVER=\\comp-dc-01

                C:\Program Files\Resource Kit>net use \\ comp-dc-01\IPC$

                 Local name

                 Remote name       \\ comp-dc-01\IPC$

                 Resource type     IPC

                 Status            OK

                 # Opens           0

                 # Connections     1

                 The command completed successfully.

 

**comp-dc-01 is the DC for the domain.

 

After firing the above command wait for some time and you will see that yellow icon of the KerbTray turning green which indicates that it has started received Tickets which can also be confirmed by right clicking on the icon and click on LIST TICKETS

909801 (http://support.microsoft.com/kb/909801/) How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005

**To enable Kerberos logging in the Windows Xp, 2003, Vista as well as Windows 2000 (Reboot required after setting the following registry key), we need to set the following registry key

1. Start Registry Editor.
2. Add the following registry value:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\ParametersRegistry Value: LogLevel
Value Type: REG_DWORD
Value Data: 0×1

If the Parameters subkey does not exist, create it.

3. Quit Registry Editor. The setting will become effective immediately on Windows Vista, on Windows Server 2003, and on Windows XP. For Windows 2000, you must restart the computer.

 

 

** IMPORTANT : This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

Also please contact the Product Support Services for more help regarding the specific issue you have.