Dynamics Community Forum Thread Details

High CPU usage on SQL 2016 SP1 after CRM 2016 on-prem (SP1)

Just recently completed an upgrade (backup, restore) from Dynamics 2013 -> 2015 -> 2016 update 1.

As part of the upgrade we also went from SQL 2012 to SQL 2016 Enterprise.

I've applied the settings as per https://technet.microsoft.com/en-us/library/hh699783.aspx#sql2016_perf (trace flag 1224, TARGET_RECOVERY_TIME = 60). Also, MAXDOP = 1, dedicated 16GB VM with 4 cores, 12GB max SQL server memory, Server 2012 R2 Datacenter, fast SSD.

Performance is generally acceptable, but for large portions of the day where there is a lot of traffic on the site (~100 users), the CPU usage on the SQL server tends to be quite high (70 - 90%). Then sometimes it will drop down and just sit at 25%, other times it will sit at the (expected and what I've observed in the past) 2-15%.

At the higher CPU utilization, the site does become noticeably sluggish.

The big problem, that may be related, is that some custom SSRS reports published to CRM will timeout for some users depending on their security roles, which is odd. The only error is in the application log: Report render failure. Error: Thread was being aborted.

Now, the SQL expensive queries often shows this as the worst offender (very high Logical Reads/sec):

insert into @t (ObjectId) 
	
				select distinct POA.ObjectId from PrincipalObjectAccess POA WITH (NOLOCK)
			
				join SystemUserPrincipals sup WITH (NOLOCK) 
				on POA.PrincipalId = sup.Princi

Out PrincipalObjectAccess table has about ~700k rows, so a lot of record sharing and ownership teams, but never did we have any SQL performance issues on 2013.

Any idea or where to start looking?

Was the VM/server with SQL 2012 of exactly the same spec in terms of CPU and RAM? If any of them was higher that could make a difference.

Also for 100 users 4 cores is probably a bit short.

As you mention VM has the VM machine/host been set according to the best practices ? this will depend on the VM type (e.g. vmware or Hyper-V).
Specially care should be given on the host to not overallocate CPU to the VM's.

Regarding the information on that link - altough they do not state the reason for having the database on compatibility level 120 I suspect that until they address their code and solve the most common issues with the new Query Cardinality engine that they are recommending it to be set at that level so the old one is used instead.
Because of this, and if you have the option of trying it out I would suggest that you change the compatibility level to 130 so you can take full advantage of the performance of 2016.
If the queries start to behave even worst then set "ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON" on that particular database so that the old query engine is used.

Being 2016 you most likely already have more than 1 datafile for TEMPDB - if not I would advise you to set it with 4 files.

Another thing to check is that the Windows power setting are set at high performance and not on balanced mode.

With regards to that particular SQL high logical reads normally mean missing indexes and/or bad statistics on the tables involved.
Digging onto the querystore will help you on this - see www.simple-talk.com/.../the-sql-server-2016-query-store-built-in-reporting

Quick Links