I have a process which is getting blocked by a System proces. The only information I have about the system process (from the Activity Monitor) is the Command field which is "RESOURCE MONITOR". I've looked up "resource monitor" in books on-line and it says something about clustered servers. I don't have a server cluster.
This server is severely underutilized -- the sql server is currently using less than 1 GB of 10 GB available to SQL server. While the blocked process is running, the total CPU utilization is at ~20%.
Any insight would be appreciated.
Thanks
System Process issues "RESOURCE MONITOR" command and blocks a process
J Russo
I tried to post the output from sysprocesses but apparently it was too much for this window -- I got an error. If there's some other way I can get the information to you, let me know.
Thanks
Baibhab Das
Resource monitor thread runs when the server needs to clean up resources internally.
In order to dig into this further a few general questions
When you get into a blocking state, could you paste output of sysprocesses I’m interested in overall load, waittypes and waitresources.
Thanks,
Boris.
JamesVSTS
62 Total processes running at the time. I just noticed one difference between the sysprocesses output and what the Activity monitor shows. The Activity monitor shows that process 55 is being blocked by process 1. But the sysprocesses output seems to show that process 55 is blocking itself
DBCC MEMORYSTATUS output:
Memory Manager KB
------------------------------ --------------------
VM Reserved 12684848
VM Committed 7879792
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
------------------------------ --------------------
VM Reserved 12679216
VM Committed 7874248
AWE Allocated 0
MultiPage Allocator 19080
SinglePage Allocator 474936
(5 row(s) affected)
CACHESTORE_OBJCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16112
MultiPage Allocator 32
(7 row(s) affected)
CACHESTORE_SQLCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 422128
MultiPage Allocator 24
(7 row(s) affected)
CACHESTORE_PHDR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6256
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 592
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 104
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2224
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2072
MultiPage Allocator 112
(7 row(s) affected)
USERSTORE_DBMETADATA (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4584
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 376
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 208
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 256
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 648
MultiPage Allocator 48
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 288
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 32768
VM Committed 32768
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8432
MultiPage Allocator 0
(7 row(s) affected)
Buffer Distribution Buffers
------------------------------ -----------
Stolen 1335
Free 168103
Cached 58032
Database (clean) 685721
Database (dirty) 45945
I/O 0
Latched 0
(7 row(s) affected)
Buffer Counts Buffers
------------------------------ --------------------
Committed 959136
Target 1310720
Hashed 731666
Stolen Potential 1185817
External Reservation 0
Min Free 256
Visible 1310720
Available Paging File 708145
(8 row(s) affected)
Procedure Cache Value
------------------------------ -----------
TotalProcs 1580
TotalPages 55573
InUsePages 145
(3 row(s) affected)
Global Memory Objects Buffers
------------------------------ --------------------
Resource 285
Locks 1057
XDES 40
SETLS 8
SE Dataset Allocators 16
SubpDesc Allocators 8
SE SchemaManager 272
SQLCache 573
Replication 2
ServerGlobal 48
XP Global 2
SortTables 2
(12 row(s) affected)
Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 947200
Maximum (Buffers) 947200
Limit 947200
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 960000
(11 row(s) affected)
Small Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 12800
Maximum (Buffers) 12800
Limit 12800
(5 row(s) affected)
Optimization Queue Value
------------------------------ --------------------
Overall Memory 8388608000
Target Memory 8322138112
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway Value
------------------------------ --------------------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
(6 row(s) affected)
Medium Gateway Value
------------------------------ --------------------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 12
(5 row(s) affected)
Big Gateway Value
------------------------------ --------------------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 row(s) affected)
MEMORYBROKER_FOR_CACHE Value
-------------------------------- --------------------
Allocations 58034
Rate 0
Target Allocations 1024000
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_STEAL Value
-------------------------------- --------------------
Allocations 1329
Rate 0
Target Allocations 1015886
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_RESERVE Value
-------------------------------- --------------------
Allocations 0
Rate 0
Target Allocations 1024000
Future Allocations 236800
Last Notification 1
(5 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Daniel Pratt
Out of the all output of sys.sysprocesses, could you please post line for RESOURCE MONITOR and for blocked SPID(s) along with total number of rows in it (just to get an idea of how many things are running on the system)
DBCC MEMORYSTATUS is another thing I would like to look at but it again may not fit in the window. If you could put all of the output BUT memory clerks info (Aggregate memory as called in this KB: http://support.microsoft.com/ id=907877), it may fit.
What does the process that is being blocked do (some reporting query against DW, insert, update, stored procedure; how is it submitted: interactively, from an application, is it dynamically generated SQL, etc.)
Thank you,
Boris.