August 10, 2009 at 2:16 pm
Environment:
Microsoft Dynamics AX
Microsoft SQL Server 2008 SP 1
Clustered SQL Server on High End Servers using Fiber Attached SAN
64 GB Memory
Issue:
There is a table in Microsoft Dynamics named SYSPERSONALIZATION which is a system table that contains 5 columns (NO FULL TEXT INDEXES, NO RELATIONSHIP TO CUSTTABLEINDEX):
BUFFER (Image, null)
ELEMENTNAME (PK, nvarchar(40), not null)
ELEMENTTYPE (PK, int, not null)
USERID (PK, nvarchar(5), not null
RECVERSION (int, not null)
RECID (bigint, not null)
INDEXES: CLUSTERED PRIMARY KEY
The following ROOT BLOCKER was running a query:
INSERT INTO SYSPERSONALIZATION (BUFFER,ELEMENTNAME,ELEMENTTYPE,USERID,RECVERSION,RECID)
VALUES (@P1,@P2,@P3,@P4,@P5,@P6)
MS DYNAMICS AX:
The SYSPERSONALIZATION INSERT was the root blocker, which blocked SPID 23 FT INDEX PROCESS, which blocked another FULL TEXT INDEX PROCESS, which blocked many USER SPIDS trying to perform transamctions against our custoer table. There is FULL TEXT index on one of our Customer Tables that is involved with managing customer data.
WHAT I KNOW:
ROOT BLOCKER = User Spid running query INSERT INTO SYSPERSONALIZATION
NEXT BLOCKED = System Spid (23) running FT Catalog Update
NEXT BLOCKED = System Spid (33) running FT Process
NEXT BLOCKED = Many USER spids were blocked performing transactions against the customer table.
I cant find a relationship between FULL TEXT and the SYSPERSONALIZATION table. However, the LOB_DATA Ghost Record Count for the SYSPERSONALIZATION is over 90K. Not sure if this makes a different with this specific issue.
Any ideas which would cause blocking with Full Text and a table that has nothing to do with Full Text?
Thanks
Greg G.
August 12, 2009 at 4:36 pm
I believe the root problem was a combination of how Microsoft Dynamics AX was calling the object. Also, there is a trace flag 7646; DBCC TRACEON(7646, -1) that can be used to help prevent blocking with the full text index process. We opened a case with Microsoft, and they asked us to use the trace flag to help prevent blocking. Since SQL Server 2008 Full Text is now integrated in the database and processing is accomplished with the sqlserver service, we now have the possibility of transactional issues.
This is all I know. They have an internal KB article for this trace flag, but they aren't providing any info about it yet. This is a known issue with contention on the Full Text index for SELECTS/UPDATES.
This is all I know. If anyone has any specific information regarding the trace flag 7646, that would be great to know. I am trying to find internal documentation and why this flag is supposed to help.
Thanks
Greg G.
December 27, 2009 at 7:14 pm
You may like to take a look at this for trace flag 7646.
http://sqldev.wordpress.com/2008/09/16/sql-server-2008-full-text-slowness/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply