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)

COUNT(*) as [#TotalRecordCount]
top 5001 "SomeEntityName0".SomeEntityNameId as "SomeEntityNameid"
 SomeEntityName as "SomeEntityName0" (NOLOCK) 
 ("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 - and KB -

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