Why parallelism is not desired in OLTP database enviornment.

 a) Many time out of date statistics or missing indexes can lead to parallelism in OLTP environment. Contrarily to OALP in most OLTP environment a transaction touches few numbers of rows so large table sca n is not desired which generally uses parallel query executions.

 b) Say you have highly scalable OLTP database with more than 1000 transactions per second. If a query or stored procedure uses parallelism it can spawn multiple threads per request this in turn increase CPU and amount of threads that can be handled per seconds (1000 threads x 3 = 3000 threads).  

 In certain situation you can limit max degree of parallelism server configuration to 1 which in turn disables parallelism.

 Keep in mind if you set Max Degree of Parallelism to 1, if you use query hint OPTION MAXDOP it would override server level parallelism settings.

 

 

Advertisements
Posted in SQL Tricks and Tips | Leave a comment

SQL Server Backup Compression Also Available in Standard Edition

OK I was under the impression that SQL Server backup compression is only available in enterprise edition only, however starting with SQL Server 2008 R2 backup compression is also available in standard edition.

 Here is MSDN BOL that confirms that

“Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions.”

Posted in General | Leave a comment

How to upgrade from evaluation or trial edition to full or retail edition of SQL Server

When the evaluation period for your SQL Server 2008 Evaluation edition expires, you will notice the following symptoms:

SQL Server service fails to start.

The following message is logged in the Windows Application log:
SQL Server evaluation period has expired

To upgrade the Evaluation Edition to a retail edition you can use the procedure documented in the following topic in Books Online:

How to: Upgrade to a Different Edition of SQL Server 2008 (Setup)

 If the procedure documented in the above topic fails on Engine_SqlEngineHealthCheck rule, then you can use the command line installation option to skip this specific rule to allow the upgrade process to complete successfully.

To skip checking this rule, open a command prompt, change to the hard disk drive and to the folder that contains SQL Server Setup (Setup.exe).

Then, type the following command:

setup.exe /q /ACTION=editionupgrade /InstanceName=MSSQLSERVER /PID=<appropriatePid> /SkipRules= Engine_SqlEngineHealthCheck

Note: The above command line runs SQL Server setup in silent mode. For a complete list of options for running the setup from the command line, please refer to the following topic in SQL Server Books Online

For more information on the upgrade scenarios supported in SQL Server please refer to the following topic in SQL Server 2008 Books Online:

Reference: http://support.microsoft.com/kb/2020443

Posted in SQL Tricks and Tips | Leave a comment

SQL Server Error Message: Cannot Generate SSPI Context

Cannot generate SSPI context means SQL server failed to authenticate (when using Windows Authentication) with your active directory or your  Active Directory Server is not responsive.

Please talk to your ISP or network administrator trying to get your Active Directory server in a better state as the real fix, but in the interim, you can try to mitigate the problem by reducing the number of logins/logouts to SQL Server. Does your application use Connection Pooling? If not, enabling it to use Connection Pooling might be helpful, since reusing a pooled connection means it does not need to authenticate again

Also, 

How to troubleshoot the “Cannot generate SSPI context” error message

http://support.microsoft.com/default.aspx?scid=kb;en-us;811889

The following blog posts from SQL Protocols team see it helps

http://blogs.msdn.com/b/sql_protocols/archive/2005/10/15/481297.aspx

http://blogs.msdn.com/b/sql_protocols/archive/2007/01/02/cannot-generate-sspi-context-error-message-poisoned-dns.aspx

Download the setspn tool from link above.  Insure your SQL Server service is started.  Next try to delete all the SPNs you see for this instance of SQL using the setspn tool (read KB article 811889 to determine how to do this).  If you cannot delete the SPNs, then the SPNs are assigned to some domain user account and you need a domain administrator to delete it.  Delete all SPNs for the SQL Server instance, then stop and restart SQL.  Next, use the setspn tool to see if the SPN is re-created.  If the SPN is recreated, then everything should work fine at this point.  If the SPN is not created at this point, you will need to contact the domain admin and have him or her create the SPN under the account that the SQL Server service is running under.

Another alternative is to use SQL server authentication, but again talk to your network guys and find out why SQL have problems talking to AD

Posted in SQL Server Data Access | Leave a comment