We’ve currently run into a problem with our production environment.
Our Biztalk Clustered Databse is constantly running at 80%+ cpu.
When doing analysis we have found that aproximately 90% of the cpu is used by the following stored procedure:
MsgBoxPerfCounters_GetHostInstanceNumber_<NameOfOrchestrationHost>
This is the query that is taking all the time inside the above stored procedure…
SELECT count(*)
FROM Instances WITH (NOLOCK)
WHERE uidAppOwnerID = ( SELECT uidAppID
FROM Applications WITH (NOLOCK)
WHERE nvcApplicationName = ‘WorkLoadOrchestrationHost’)
Can someone help me. Is this procedure necessary? can it be turned off?… is there any way to speed it up?
We have aprox 56k instances in the Instance table. (We have a lot of long running transactions that go to dehydration).
Thanks in advance.
Rich