March 12, 2010 at 12:14 pm
Hi,
We have 3 SQL Server instances for BizTalk and 1 instance for other application on same same SQL Server.
We have 16 GB of RAM and each instance's Max memory is set to 3 GB and left 4 GB for OS. All the storage is from SAN & all drives are RAID 10
On instance 1, we have BizTalkMsgBox database (data file on D & log file on E & tenmDb on F drive(both mdf & ldf of tempdb are on F drive))
On instance 2, we have BizTalkDTADb database (data file on G & log file on H & tenmDb on I drive(both mdf & ldf of tempdb are on I drive))
On instance 3, we have BizTalkMgmtDb, SSODB, BizTalkRuleEngine databases (data file on J & log file on K & tenmDb on L drive(both mdf & ldf of tempdb are on Ldrive))
On instance 4, we have 4 small databases serving other application (data file on M & log file on N & tenmDb on O drive(both mdf & ldf of tempdb are on Odrive)
When ever, the BizTalk admin runs queries from HAT( Health Activity Tracking) tool in BizTalk, on SQL server, we are getting below messages from Spot light monitoring tool :
1) Disk Queue Length: Disk H: has an average queue length of 200.1
2)Page Life Expectancy: The buffer cache page life expectancy is 4 seconds --> for instance 2
3)Memory - Buffer Cache Hit Rate: Buffer Cache hit rate is 70.23%
4)Disk Queue Length: Disk I: has an average queue length of 8.8 --tempdb
So from above info, is that normal to get High Disk IO, low Page Life Expectancy on SQL Server, when we run queries from HAT ( Health Activity Tracking) tool in BizTalk or we really have Memory pressure & that's why we getting these issues?
The query ran is: Query to view no.of messages tracked in the Tracking databasse for over past week from HAT ( Health Activity Tracking) tool in BizTalk
Please advice
March 12, 2010 at 4:58 pm
Finding memory pressure for a particular instance:
I ran the below query to investigate the memory pressure on the server.
select r.ring_buffer_address,
r.ring_buffer_type,
dateadd (ms, r.[timestamp] - sys.ms_ticks, getdate()) as record_time,
cast(r.record as xml) record
from sys.dm_os_ring_buffers r
cross join sys.dm_os_sys_info sys
where
ring_buffer_type='RING_BUFFER_RESOURCE_MONITOR'
order by 3 desc
Results:
ring_buffer_address ring_buffer_type record_time
0x0000000000F0FE60RING_BUFFER_RESOURCE_MONITOR2010-03-09 12:33:23.193
0x0000000000F0FE60RING_BUFFER_RESOURCE_MONITOR2010-03-09 12:33:23.193
So from above results, the record time is the one when we got the memory pressure on the server?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply