March 16, 2009 at 11:52 am
I have a query which sometimes takes one second and other times 2 -5 minutes.
I ran a blocking trace and there was not any blocking. I saw on the aaaaa table there is a trigger for updates and inserts and that there where a lot of concurrent users waiting to compile this trigger, would this effect the select statements? I also see a lot of I/O waits (we had quest for a short time and it seemed 50-80% of all server time was down to I/O waits). Disk queues are up and down but SAN engineers say there isn’t a problem.
Anyway has anyone got a suggestion for a series of steps which will prove what the problem is.
Or has anyone get any steps to eliminate potential problem.
Like I said I think it’s down to I/O wait and many users but I have no idea how to prove it.
Many thanks
his is the statement "SELECT dgpro.proca_refno proca_refno,
dgpro.odpcd_refno odpcd_refno,
prvsp.admit_dttm prvsp_admit_dttm,
prvsp.disch_dttm prvsp_disch_dttm
FROM aaaaa dgpro,
bbbbb prvsp
, cccc prcae
WHERE dgpro.odpcd_refno = 43195
AND dgpro.proca_refno = 202261
AND prvsp.proca_refno = 202261
AND prvsp.prvsp_refno = prcae.prvsp_refno
AND prcae.prcae_refno = dgpro.sorce_refno
AND dgpro.sorce_code = 'PRCAE'
AND dgpro.dptyp_code = 'PROCE'
AND prvsp.admit_dttm IS NOT NULL
AND prvsp.disch_dttm IS NOT NULL
AND ISNULL(dgpro.archv_flag,'N') = 'N'
AND ISNULL(prvsp.archv_flag,'N') = 'N'
AND ISNULL(prcae.archv_flag,'N') = 'N'"
March 16, 2009 at 11:55 am
If you can, please post the execution plan. I'd also be interested to see the trigger. It's definitely a point to look at, especially if it's got to recompile every time it runs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2009 at 11:50 pm
Just disable the trigger and run more than one time. Look at the execution time.
As GSquared said,post the trigger and execution plan. You will get more help.
karthik
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply