There are times when you may see degraded experience on loading your console and with the values refresh. The first few pointers that come to mind are:

  1. Configuration
  2. Network
  3. SQL

If the configuration seems to be fine and so does the network, I usually recommend to check your database if it has been indexed reguarly. If that too has been performed, its very likely that you may have open connections that are consuming the I/O threads. I recently ran across a client that had external integrations that read data from the SCCM DB and had not closed out the connections, hence putting load on the I/O. The following SQL statement will allow you to see the user, application and host from which the thread originates and the performance consumed per thread.

Note: Change the database name ‘CM_CMD’ to your database name before executing

SELECT        TOP (100) PERCENT DB_NAME(dbid) AS DBName, COUNT(dbid) AS NumberOfConnections, loginame AS LoginName, hostname, program_name, login_time, cpu, memusage, physical_io, open_tran
FROM            sys.sysprocesses
WHERE        (dbid > 0)
GROUP BY dbid, loginame, hostname, program_name, login_time, physical_io, memusage, open_tran, cpu
HAVING        (DB_NAME(dbid) = 'CM_CMD')
ORDER BY DBName, NumberOfConnections DESC, LoginName, physical_io

 

RELATED ARTICLESMORE FROM AUTHOR