CRM 2011 performance (for roll ups less than 10) : why search on some default views with non admin role is too slow
I have been checking around a performance related issue in CRM 2011 on some default views with non admin roles. For example I will take the "Active Companies" view which is the default one that comes with CRM itself on the entity Account/ Company. Say there are 150.000 accounts present in the system and my account does not have admin access. Provided that CRM server is not up with roll up 10, say it has roll up 8 or less.
Now, when I try to seach for a company with a particular company id(account number) with value say 1001, what I need to do is I put that value 1001 in the searchable textbox present at the top right corner of the account entity form and hit the search button.
Now we have to wait at least 15-30 seconds to get the whole list of records that contain the 1001 value in the account/ company number, say 10011, 1001 etc. Even though we changed the cpu or ram, it was little impact in it. Well, something going wrong, right. Since if we do the same thing using a user with Administrator role, it is jsut a matter of second to retrieve all the records.
It was a good idea from us to check what is going behind for the queries in SQL database level, since ultimately there will be sql queries to be executed on the CRM sql server database to get the data where the information are stored.
We ran the SQL profiler on the CRM SQL database with following options checked :
•RPC:Completed
•SP:StmtCompleted
•SQL:BatchStarting
•SQL:BatchCompleted
Then we stored the information on a sql staging table and tried to find out what is consuming the time. We checked the query that is taking most of the time, with having "Duration" is max. We checked the "TextData" which is the sql query being ran with the maximum Duration of execution.
"select TextData, Duration from dbo.TraceTable order by Duration desc"
Now the interesting things come up. We cam to know, that this is the query which is fetching all the data for the particular search.
Below is the query that is begin ran by CRM on seach in a default view for a company number.
exec sp_executesql N' create table #ObjectsIds (ObjectId uniqueidentifier); create NONCLUSTERED index ndx_ObjectsIds_objId on #ObjectsIds (ObjectId ASC); insert into #ObjectsIds select POA.ObjectId from PrincipalObjectAccess POA join SystemUserPrincipals sup on POA.PrincipalId = sup.PrincipalId where sup.SystemUserId = @SystemUserId and POA.ObjectTypeCode = @ObjectTypeCode and ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1) = 1; insert into #ObjectsIds select AccountId from [Account] where OwnerId in (select pem.PrincipalId from PrincipalEntityMap pem join SystemUserPrincipals sup on pem.PrincipalId = sup.PrincipalId where sup.SystemUserId = @UserIdOwnerCommand0 and pem.ObjectTypeCode = @OtcOwnerCommand0) insert into #ObjectsIds select AccountId from [Account] where owningbusinessunit in (select BusinessUnitId from SystemUserBusinessUnitEntityMap where SystemUserId = @SystemUserId and ObjectTypeCode = @ObjectTypeCode) select top 51 "account0".AccountId as "accountid" , convert(bigint, "account0".VersionNumber) as "versionnumber" , "account0".Address2_City as "address2_city" , "account0".Telephone1 as "telephone1" , "account0".AccountNumber as "accountnumber" , "account0".Name as "name" , "account0".ModifiedOn as "modifiedon" , "userentityinstancedata".FlagStatus as "userentityinstancedata.flagstatus" , "userentityinstancedata".PersonalCategories as "userentityinstancedata.personalcategories" , "userentityinstancedata".ReminderSet as "userentityinstancedata.reminderset" , "userentityinstancedata".ReminderTime as "userentityinstancedata.remindertime" , "userentityinstancedata".StartTime as "userentityinstancedata.starttime" , "userentityinstancedata".DueDate as "userentityinstancedata.duedate" , "userentityinstancedata".ToDoItemFlags as "userentityinstancedata.todoitemflags" , convert(bigint, "userentityinstancedata".VersionNumber) as "userentityinstancedata.versionnumber" from Account as "account0" left outer join UserEntityInstanceData as "userentityinstancedata" on ("account0".AccountId = "userentityinstancedata".ObjectId and (("userentityinstancedata".OwnerId = @OwnerId0))) where ("account0".AccountId in (select distinct ObjectId from #ObjectsIds) and ((("account0".StateCode = @StateCode0)) and (("account0".Scrm_VATNumber like @our_customsedfieldincrm0 or "account0".Address2_PostalCode like @Address2_PostalCode0 or "account0".Address2_City like @Address2_City0 or "account0".Name like @Name0 or "account0".AccountNumber like @AccountNumber0)))) order by "account0".Name asc , "account0".AccountId asc; drop table #ObjectsIds ' ,N'@SystemUserId uniqueidentifier,@ObjectTypeCode int,@UserIdOwnerCommand0 uniqueidentifier,@OtcOwnerCommand0 int,@StateCode0 int,@our_customsedfieldincrm0 nvarchar(17),@Address2_PostalCode0 nvarchar(17),@Address2_City0 nvarchar(17),@Name0 nvarchar(17),@AccountNumber0 nvarchar(17),@OwnerId0 uniqueidentifier', @SystemUserId='DB73F4D8-3FF3-E111-A440-005056B33055',@ObjectTypeCode=1,@UserIdOwnerCommand0='DB73F4D8-3FF3-E111-A440-005056B33055',@OtcOwnerCommand0=1,@StateCode0=0,@our_customsedfieldincrm0=N'ABC%',@Address2_PostalCode0=N'ABC%',@Address2_City0=N'ABC%',@Name0=N'ABC%',@AccountNumber0=N'ABC%',@OwnerId0='DB73F4D8-3FF3-E111-A440-005056B33055'
I will explain what is happening basically. On click of search, CRM system creates a temp table and stores all the account records accessible by the user and stores there. Then there are some checks and finally with the main account table, this temp table is joined to get the desired records from CRM. Simple, right?
Then we ran the same query in SQL server itself, same time it is taking to run in it SSMS. There must be something wrong going on here.
We made changes to the query few times, say instead of temp table create a physical table and drop it later(which is never a good idea to create table in CRM database) or modifying the amount of records to be retrieved, creating indexes on the related tables, removing indexes etc etc.
Then we made one change, instead of temp table we used table level variable and re run the query. Now all of sudden the execution duration came to 4 seconds from 28 seconds. So, it should be the case. We found something that maybe were not tested properly during the product release (no offense to nobody, still we guessed it was not a good query that is being used). So we know that this query is not good, and needs to be updated. BUT we cannot do anything to this, since it is being created by CRM system itself to get data from database.
So we looked for other options, then we came to know that Microsoft has released a new Roll up for CRM 2011 that contains a bug fix for performance related issues with large dataset. BANG. Now we know, what we can expect with this Roll up. And we are also expecting that the query structure will be changed at least for the default view search for non admin roles(not exactly the same way we discovered at least).
So we updated the Roll up 10 to our CRM server(with great expectation), run sql profiler and retrieved the query being created for Active Companies search for non admin user. Then we analysed. There is a difference now :
- use of CTE
- Use of table level Functions(in other words Microsoft has replaced Temp table with table level variable). And the name of the function is : fn_POARetrieveMultiple.
Still, there were issues with Outlook client. So we tested with CRM server roll up 10 and client side, Outlook roll up 8. Performance was not that bad, but still bad in comparison to the web client. Again same thing, modification in queries.
So lessions learned : we cannot blame our servers or machines everytime for bad performance, we need to check the system queries in depth to find out the root cause and maybe inform the solution provider for our findings in case it is really an issue. For this case, many people complained Microsoft for the bad performance with CRM 2011 browser client as well as outlook and they came up with a re release of roll up 10 which solved the bugs.
regards
joon