When in Performance Analysis for SQL Server you may notice on occassion the SC_MSSQL program always showing up as a blocking program.
The Middleware (MW) process tries to get the Object Details from the SQLServer DB based on the DBIDs. It sees the data sent by our Collector during the Dictionary refresh and this is being blocked by the Change Management tracker issued SQL statement. Both processes access the same objects in the DB. The reason as to why they should block each other is these SQL executions are run at READ UNCOMMITTED transaction isolation levels. The likely cause of blocking is an object is created or updated with in a transaction and the statement had to wait for that transaction to be committed before the system catalog is freed.
Almost all processes of Performance Analysis (PA) that interact with the Database are designed to re-initiate a task if it fails the first time and they keep making repeat attempts until the task succeeds. Because we have so many processes interacting with the DB, it is not unusual that there is sometimes contention between different processes for the same resource - e.g. lock on a Repository table or MW process accessing the data dictionary on the Monitored Host.
Once the DB dictionary is updated by the database engine, the MW process will get all the details of the DB objects and will release any resources held by it. Then Change Tracking will continue. So you need not be concerned about this behaviour.
Working as designed
© 2021 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy