Problem description: We are running a very huge SELECT and import the result set inside another table. We found out that we got some duplicates ROWS in the destination table. However it shouldn't occur because each row is unique.
Cause: We noticed at this time that another query was updating the clustering key of the index. Updates to the clustering key can cause rows to move within the index.We were meeting a situation where the physical location of a row within a table changes due to an modification operation. The result is that the same row may be revisited multiple times at its original location. Reading the data using an index whose key is going to be update is an example of the Halloween effect.
Recommandation: In most cases it is best to start with a clustered index on every table.
A good candidate for the clustered index key must have the following characteristics: - Narrow in size (since repeated in all nonclustered) - Unique - Incremental - Static
As in a cluster index, the data rows are stored in order, based on the cluster index key, this issue occurs because the key is updated. You wouldn't normally expect the key to be frequently update.
Resolution: To ensure that a row is not updated, encountered again by the same scan, we fix the issue with the hint HOLDLOCK and a plan guide
Links:- The "Halloween Problem" for XML APIs- Halloween Protection
Happy Halloween! ;-)
Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |
SQL Server 2012 developer training kit is available. It will help you to take advantage of the improvements introduced in SQL Server 2012 in your application.
More detail: http://blogs.msdn.com/b/rdoherty/archive/2011/10/12/learn-sql-server-2012-development-now.aspx
Problem: Remote T-Sql debugger from SSMS SQL 2008 fails with one of the following issue: Could not attach to SQL Server process on 'MyInstance'.The RPC server is unavailableORUnable to start T-SQL debugging. Could not attach to SQL Server process on 'MyInstance'.So, you are not able to remotely connect in SQL Server Management Studio to your development server in SQL Server 2008
Troubleshooting to do: All tests below are not only for the RPC issue but also for others issues like authentication.Test 1: When you locally connect to the same instance the issue is not reproduced Test 2: Check if TCP 139, TCP 445, UDP 137 and UDP 138 are open from both side (server and client) (e.g.: Configuring the Transact-SQL Debugger )Test 3: Check if SQL management Studio is lauched with "Run an administrator..." Test 4: Check if SQL service account can connect to the client machine. When you are using T-SQL debugging, SQL service account will communicate with the machine host Management Studio.It's an additional reason to set up SQL service with domain account
Test 5: Check if SSMS and SQL server service are in the same domain. if not, set up the same account and password that SQL server is using.Test 6: Check if your domain account is sysadmin.Test 7: Check if Kerberos is enable correctly and SPN are ok for your instance.- The SQL Network Interface library was unable to register SPN- SQL Server 2008 connectivity issue : cannot generate SSPI context
Test 8: Check from server side that the service below are started- TCP/IP NetBIOS Helper- Remote Registry - RPC Service