March 12, 2010 at 6:28 pm
Hi,
We have SQL Server 2005 EE x64 with SP3.
RAM = 16 GB
No.of instances on the server = 4
Max memory for each instance = 3 GB
Min memory for each instance = default value '0'
When I ran the below queres, I'm getting High Disk IO & low page life expectancy alerts from monitoring tool
Query1:
DECLARE @Timestamp as DATETIME
SET @Timestamp= GETUTCDATE() - 7 -- local time converted to UTC
DECLARE @MessageFailures as INT
DECLARE @MessagesSent as INT
DECLARE @MessagesReceived as INT
-- Count Failures--
SELECT @MessageFailures=COUNT(*)
FROM [dbo].[dtav_MessageFacts] mf WITH (READPAST)
JOIN [dbo].[dta_MessageStatus] st WITH (READPAST) ON st.strStatus = mf.[Event/Direction]
WHERE
st.nMessageStatusId = 5
AND [Event/Timestamp] > @Timestamp
-- Count Sends --
SELECT @MessagesSent=COUNT(*)
FROM [dbo].[dtav_MessageFacts] mf WITH (READPAST)
JOIN [dbo].[dta_MessageStatus] st WITH (READPAST) ON st.strStatus = mf.[Event/Direction]
WHERE
st.nMessageStatusId = 1
AND [Event/Timestamp] > @Timestamp
-- Count Receives--
SELECT @MessagesReceived=COUNT(*)
FROM [dbo].[dtav_MessageFacts] mf WITH (READPAST)
JOIN [dbo].[dta_MessageStatus] st WITH (READPAST) ON st.strStatus = mf.[Event/Direction]
WHERE
st.nMessageStatusId = 0
AND [Event/Timestamp] > @Timestamp
-- Print Results --
SELECT
@MessagesReceived as "Messages Received",
@MessagesSent as "Messages Sent",
@MessageFailures as "Message Transmission Failures"
---------------------------------------------------------------
-------------------------------------------------------------
Query2: got time out , High disk IO & low page life expectancy
DECLARE @LocalMidNightInUtc as datetime
DECLARE @LocalMidNightInLocal as datetime
set @LocalMidNightInLocal = cast (cast (GETUTCDATE() + 0.5 + ((1.0 * @UtcOffsetMin)/60.0)/24.0 as bigint) as datetime)
set @LocalMidNightInUtc = @LocalMidNightInLocal - ((1.0 * @UtcOffsetMin)/60.0)/24.0
DECLARE @nMsgFail1 as int, @nMsgFail2 as int, @nMsgFail7 as int, @nMsgFail14 as int, @nMsgFailAll as int
DECLARE @nMsgSent1 as int, @nMsgSent2 as int, @nMsgSent7 as int, @nMsgSent14 as int, @nMsgSentAll as int
DECLARE @nMsgReceived1 as int, @nMsgReceived2 as int, @nMsgReceived7 as int, @nMsgReceived14 as int, @nMsgReceivedAll as int
-- Count Failures--
SELECT
@nMsgFail1 = sum(CASE cast(@LocalMidNightInUtc - mioe.dtTimestamp - 0.5 as int) when 0 then 1 else 0 END),
@nMsgFail2 = sum(CASE cast(@LocalMidNightInUtc - mioe.dtTimestamp - 0.5 as int)/2 when 0 then 1 else 0 END),
@nMsgFail7 = sum(CASE cast(@LocalMidNightInUtc - mioe.dtTimestamp - 0.5 as int)/7 when 0 then 1 else 0 END),
@nMsgFail14 = sum(CASE cast(@LocalMidNightInUtc - mioe.dtTimestamp - 0.5 as int)/14 when 0 then 1 else 0 END),
@nMsgFailAll = count(*)
FROM dbo.dta_MessageInOutEvents mioe WITH (READPAST)
WHERE mioe.nStatus = 5 -- Transmission failure
-- Count messages sent to the outside world
SELECT
@nMsgSent1 = sum(CASE cast(@LocalMidNightInUtc - mioe.dtTimestamp - 0.5 as int) when 0 then 1 else 0 END),
@nMsgSent2 = sum(CASE cast(@LocalMidNightInUtc - mioe.dtTimestamp - 0.5 as int)/2 when 0 then 1 else 0 END),
@nMsgSent7 = sum(CASE cast(@LocalMidNightInUtc - mioe.dtTimestamp - 0.5 as int)/7 when 0 then 1 else 0 END),
@nMsgSent14 = sum(CASE cast(@LocalMidNightInUtc - mioe.dtTimestamp - 0.5 as int)/14 when 0 then 1 else 0 END),
@nMsgSentAll = count(*)
FROM dbo.dta_MessageInOutEvents mioe WITH (READPAST)
WHERE
mioe.nStatus = 1 -- Send
-- include only messages that were sent and participated in 1 activity only
AND mioe.uidMessageInstanceId IN
(
SELECT mioe2.uidMessageInstanceId
FROM dbo.dta_MessageInOutEvents mioe2 WITH (READPAST)
GROUP BY mioe2.uidMessageInstanceId
HAVING count (distinct cast (mioe2.uidActivityId as varchar(36))) = 1 -- message participated in single activity only
AND min((mioe2.nStatus-1)*(mioe2.nStatus-1)) = 0 -- at least one send (nStatus=1)
)
-- Count messages received from the outside world
SELECT
@nMsgReceived1 = sum(CASE cast(@LocalMidNightInUtc - mioe.dtTimestamp - 0.5 as int) when 0 then 1 else 0 END),
@nMsgReceived2 = sum(CASE cast(@LocalMidNightInUtc - mioe.dtTimestamp - 0.5 as int)/2 when 0 then 1 else 0 END),
@nMsgReceived7 = sum(CASE cast(@LocalMidNightInUtc - mioe.dtTimestamp - 0.5 as int)/7 when 0 then 1 else 0 END),
@nMsgReceived14 = sum(CASE cast(@LocalMidNightInUtc - mioe.dtTimestamp - 0.5 as int)/14 when 0 then 1 else 0 END),
@nMsgReceivedAll = count(*)
FROM dbo.dta_MessageInOutEvents mioe WITH (READPAST)
WHERE
mioe.nStatus = 0 -- Receive
-- include only messages that were received and participated in 1 activity only
AND mioe.uidMessageInstanceId IN
(
SELECT mioe2.uidMessageInstanceId
FROM dbo.dta_MessageInOutEvents mioe2 WITH (READPAST)
GROUP BY mioe2.uidMessageInstanceId
HAVING count (distinct cast (mioe2.uidActivityId as varchar(36))) = 1 -- message participated in single activity only
AND min(mioe2.nStatus) = 0 -- at least one send
)
SELECT N'Today' as N'Period', 1 as 'Days', @nMsgFail1 as 'Message Transmission Failures', @nMsgSent1 as N'Messages Sent', @nMsgReceived1 as N'Messages Received'
UNION SELECT N'Last 2 days', 2, @nMsgFail2, @nMsgSent2, @nMsgReceived2
UNION SELECT N'Last 7 days', 7, @nMsgFail7, @nMsgSent7, @nMsgReceived7
UNION SELECT N'Last 14 days', 14,@nMsgFail14, @nMsgSent14, @nMsgReceived14
UNION SELECT N'Total', 99,@nMsgFailAll, @nMsgSentAll, @nMsgReceivedAll
ORDER BY Days
Could you please advice me on how to avoid this disk IO, Low page life expectancy issues?
Is something wrong with queries?
March 12, 2010 at 9:30 pm
Please see the article at the second link in my signature line below. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2010 at 11:09 pm
Thanks Jeff, It's really helpful
I went through the link http://support.microsoft.com/default.aspx/kb/899000?p=1
But here is the scenario: We have 3 instances for BizTalk on Same SQL Server as below:
On instance 1, we have BizTalkMsgBox database (data file on D & log file on E & tenpDb on F drive(both mdf & ldf of tempdb are on F drive))
--> FOR THIS INSTANCE1 MAXDOP IS SET 1 AUTOMATICALLY WHEN Biztalk has installed
On instance 2, we have BizTalkDTADb database (data file on G & log file on H & tenpDb on I drive(both mdf & ldf of tempdb are on I drive))
--> FOR THIS INSTANCE2 MAXDOP IS SET 0 by default when BizTalk has installed
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 L drive))
--FOR THIS INSTANCE3 MAXDOP IS SET 0 by default when BizTalk has
Question:
Do we need to set the MAX DOP value to 1 for instance2 & instance3 also??
Or we only need set MAX DOP value to 1 for the instance which hosts the BizTalkMsgBoxDB
database??
please advice....
March 13, 2010 at 5:42 pm
That's not the original question and it won't help with the original question.
You've clearly identified it as performance problem in the code...
When I ran the below queres, I'm getting High Disk IO & low page life expectancy alerts from monitoring tool
... and no simple MAXDOP setting will give it the kind of help it needs.
Like I said, follow the instructions in the 2nd link in my signature.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply