Friday, December 25, 2009

SQL Server Error 666: The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID...

Recently while running a huge insert query I got the following error:

Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 422214343852032. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

The query was joining 2 huge tables (each having more than 3 billion rows) and then inserting the result in a third table.

The structure of the query was as follows:

insert into table_archive with(TABLOCK) (id, rid, mid, uid, mDate, result, oDate, bid, jid)
select mtrid as id, rid, mid, uid, mDate, result, oDate, bid, jid
from (
select  rid, mid, uid, mDate, result, oDate, bid, jiud, ROW_NUMBER() over(order by jid, mid, rid) as archiveRow
from dbo.table1 with(nolock)
) archiveTable join
(
select mtrid, ROW_NUMBER() over(order by mtrid) as Idrow
from archive_temp.dbo.billionNumbers
) idtable
on idtable.Idrow = archiveTable.archiveRow

After searching the net I found that this issue occurs when the query execution plan has a Table Spool operator. The Table Spool operator creates a unique clustered index on a column when the execution plan is generated for the query. When the number of the input rows exceeds 2,147,483,648, the unique identifier for the clustered index runs out. Therefore, you receive the error message that states that duplicate rows cannot be inserted.

There are couple of things you can try:

1. Install the HotFix that Microsoft has released a HotFix for this issue in SQL Server 2005. The HotFix is available from at this Microsoft Knowledge Base article.
2. Try to use some query hints or index optimizer hints to rewrite the query. You may find a way to avoid the Table Spool operator. In my case I tried using the query hint Option (Hash Join) and this worked for me. When using this query hint in the execution plan I did not get a Table Spool - so I went ahead and tried it and it worked. My final query was:

insert into table_archive with(TABLOCK) (id, rid, mid, uid, mDate, result, oDate, bid, jid)
select mtrid as id, rid, mid, uid, mDate, result, oDate, bid, jid
from (
select  rid, mid, uid, mDate, result, oDate, bid, jiud, ROW_NUMBER() over(order by jid, mid, rid) as archiveRow
from dbo.table1 with(nolock)
) archiveTable join
(
select mtrid, ROW_NUMBER() over(order by mtrid) as Idrow
from archive_temp.dbo.billionNumbers
) idtable
on idtable.Idrow = archiveTable.archiveRow
Option (Hash Join)

In a different scenario, you may have to use a different query hint. If you have used a diff. query hint that worked in your case, do let me know.

Thursday, May 28, 2009

SQL Server function to strip non numeric characters

This is useful if you have a phone/fax number field and want to remove any non numerical characters. for eg: (301) 123-4567 to 3011234567


calling this function like: select [dbo].[FilterNonDigit]('123-456-7890') will return 1234567890

CREATE FUNCTION [dbo].[FilterNonDigit]( @Input varchar(256))
RETURNS varchar(256)
AS
BEGIN
If PATINDEX('%[^0-9]%', @Input) > 0
WHILE PATINDEX('%[^0-9]%', @Input) > 0
SET @Input = Stuff(@Input, PATINDEX('%[^0-9]%', @Input), 1, '')
RETURN @Input
END


http://www.DaksaTech.com
Web technology solutions for small and medium enterprises.

SQL Server Function to get date without time

CREATE FUNCTION [dbo].[getDateWithoutTime](
@date datetime
)
RETURNS smalldatetime
AS
BEGIN
DECLARE @returnDate smalldatetime
SET @returnDate = cast(cast(month(@date) as varchar(2)) + '-' + cast(day(@date) as varchar(2)) + '-' + cast(year(@date) as varchar(4)) as smalldatetime)
RETURN @returnDate
END


http://www.daksatech.com/
Web technology solutions for small and medium enterprises.

Monday, February 02, 2009

Troubleshooting hung ColdFusion Server

We recently upgraded from CFMX 6.1 to ColdFusion 8 Enterprise Edition. CF8 has some really cool tools - one such is the Server Monitor - this tool allows you to see whats going on below the surface in CF. You can see which requests are running, which queries are running, how much memory is being used by different threads & sessions + a whole lot of other things.

Immediately after upgrading to CF8, we started seeing problems with the server - using the Server Monitor we found that there were some database connections that were still open on CF.



But pretty soon, we started having hung CF servers again. This would happen 4-5 times a day. There were no issues on the DB and we were not even able to get the CF Server Monitors up (as the server monitors are themselves CF app). Also, the Windows performance monitor showed only a couple of CF threads running. So it seemed that the 3-4 CF threads that were running were causing the server to go down.

So I decided to take a look at what these 3-4 CF threads were. For this I had to take thread dumps of when we were seeing these problems.

To do so, I followed the following steps:
  • Enabled "Allow service to interact with desktop" for the CF service in Windows Services panel.












  • Our CF server is hosted at a remote location. So I use Remote Desktop to log on to the server and see the desktop. But when I directly logged on to the server, I did not see the console (as CF is started using the system account).
  • So to see the console, I Opened command prompt on my local machine and entered:
    c:\mstsc -v:xxx.xxx.xx.xxx /F -console (This did not work from Vista; but did work from Windows XP)
  • This will open a Remote Desktop window to the CF server
  • Enter the Admin password for the server and login.
  • Once logged in, you will see a blank console window on the server. This console window will have the title "c:\cfusion8\runtime\bin\jrun.exe" (or something similar)
  • With this window selected (highlighted) , hit Ctrl+(Pause/Break key) - this key is on top right side of keyboard
  • This will generate a dump in the file c:\cfusion8\runtime\logs\coldfusion-out.log
  • Repeat above step a couple of times in 20-30 second intervals. This will give you diff. dumps at 20-30 sec intervals and will help you better understand whats going on.
In my case, we found that the problem was a Java deadlock caused by 3 threads. Of these 3 threads, 2 were threads related to the ColdFusion Server Monitor.
The specific entries were:
Found one Java-level deadlock:=============================
"jrpp-68":
waiting to lock monitor 0x60968554 (object 0x0db33628, a
java.util.Hashtable),
which is held by "scheduler-0"

"scheduler-0":
waiting to lock monitor 0x609291cc
(object 0x0d4dcd10, a
coldfusion.monitor.memory.SessionMemoryMonitor$TopMemoryUsedSessions),
which is held by "jrpp-47"

"jrpp-47":
waiting to lock
monitor 0x60968554 (object 0x0db33628, a java.util.Hashtable),
which is held by "scheduler-0"
This was followed by the details of the 3 stacks. The details clearly showed us which CF pages were involved.
One lesson we learned was that it is not prudnet to run the Server Monitor on a production machine very often. If you have to run it, run is very sparingly and dont forget to stop the monitors when done.
A very good article on trouble shooting CF can be found at: http://kb.adobe.com/selfservice/viewContent.do?externalId=tn_18339