Dynamics Community Forum Thread Details

high CPU usage SQL DB

Hi All,

now I am facing high cpu usage on my sql DB server, after simple check using this link https://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/

there has one query that executed many times and make the cpu high..

here is the query :

WITH "account0Security" as (
select xxx

case when poaa0.ReadAccess is null then 0 else poaa0.ReadAccess end as 'poaa0_openrevenue', 
case when poaa1.ReadAccess is null then 0 else poaa1.ReadAccess end as 'poaa1_opendeals' 
from
account0Security as "account0"
left outer join fn_UserSharedAttributeAccess(@flsSecuredSystemUserId0, @securedAttributeId0, @securedAttributeObjectTypeCode0) as "poaa0" 
on (poaa0.ObjectId = "account0".AccountId)

left outer join fn_UserSharedAttributeAccess(@flsSecuredSystemUserId1, @securedAttributeId1, @securedAttributeObjectTypeCode1) as "poaa1" 
on (poaa1.ObjectId = "account0".AccountId)

where
(("account0".Name like @Name0)) order by
"account0".AccountId asc

please any suggestion what is that query purpose? and how to solve this issue?

thanks

Hi, I am not 100% sure what this table does, however one thing to check is where you are using lots of sharing of records in CRM. or perhaps during on load of particular form you are querying the security roles of users etc.

From SQL server prospective you can tweak few settings to make it more efficient however it all depends what kind of configurations you are having right now. Is it being shared with other applications as well?

0. Keep the Log file and data files in different drives.

1. Set isolation level to 1 for CRM Orgs DB

2. Set isolation level to 1 for CRM CONFIG DB

3. Microsoft Dynamics CRM is designed to work best over networks with latency under 150 ms (milliseconds) and bandwidth greater than 50 kbps (kilobytes per second).

4. CRM makes individual calls to database for each subgrid and its ribbon. The more the subgrids more calls which will adversely impact rendering of CRM forms.

5. You can check SQL server counters as well:

--Buffer Manager\Buffer cache hit ratio

--Buffer Manager\Page life expectancy

--General Statistics\User Connections

--General Statistics\Transactions

--Latches\Total Latch Wait Time (ms)

--Latches\Average Latch Wait Time (ms)

--Locks(_Total)\Average Wait Time (ms)

--Locks(_Total)\Lock Wait Time (ms)

--SQL Statistics\Batch Requests/sec

--SQL Statistics\SQL Compilations/sec

--SQL Statistics\SQL Re-Compilations/sec