Friday, June 01, 2012

MS CRM 2011: Sql Timeouts during access to views

Yesterday we have deployed solution on productive system and imported data – about 40 millions of records in different entities. During testing “Generic Sql Exception” messages begun appear for some types of records.
I have turned on tracing, reproduced an error and have got following log:
create table #ObjectsIds (ObjectId uniqueidentifier);
create NONCLUSTERED index ndx_ObjectsIds_objId on #ObjectsIds (ObjectId ASC);
insert into #ObjectsIds
select POA.ObjectId from PrincipalObjectAccess POA (NOLOCK) join SystemUserPrincipals sup (NOLOCK) on POA.PrincipalId = sup.PrincipalId where sup.SystemUserId = '235d0cc8-3699-e111-b821-00155d3a0c00' and POA.ObjectTypeCode = 10006 and ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1) = 1;
insert into #ObjectsIds
select SomeEntityNameId from [SomeEntityName]  (NOLOCK)
where OwnerId in
(select pem.PrincipalId from PrincipalEntityMap pem (NOLOCK) join SystemUserPrincipals sup (NOLOCK) on pem.PrincipalId = sup.PrincipalId where sup.SystemUserId = '235d0cc8-3699-e111-b821-00155d3a0c00' and pem.ObjectTypeCode = 10006)

insert into #ObjectsIds
select SomeEntityNameId from [SomeEntityName]  (NOLOCK)
where owningbusinessunit in
(select BusinessUnitId from SystemUserBusinessUnitEntityMap (NOLOCK) where SystemUserId = '235d0cc8-3699-e111-b821-00155d3a0c00' and ObjectTypeCode = 10006)

select
COUNT(*) as [#TotalRecordCount]
from
 (select
top 5001 "SomeEntityName0".SomeEntityNameId as "SomeEntityNameid"
from
 SomeEntityName as "SomeEntityName0" (NOLOCK) 
where
 ("SomeEntityName0".SomeEntityNameId in (select distinct ObjectId from #ObjectsIds) and ("SomeEntityName0".statecode = 0 and "SomeEntityName0".mcs_AccountId = '079a3330-4aa6-e111-9355-00155d160b0d'))) as #SubQuery;
drop table #ObjectsIds



After some googling I have found following thread - http://social.microsoft.com/Forums/en-US/crm/thread/16a1cabc-eb5c-4646-bb63-34ff47fcd1c1/ and KB - http://support.microsoft.com/kb/955138.

Once I have applied changes to registry and restarted IIS issues disappeared.