Stopped Query

JanM

Hi

If we lookup on article for inventory we had sometimes a very long response time.

Activity Monitor: 1 open Transaction, State: stopped, worktype: PAGEIOCATCH_SH, no blocking process,
Profiler: duration 58s, reads 103115, query: select * from [company$Item Ledger Entry] where [Item No_] = 'xy'

At the blocking time we can just send the query by SQL Mgmt. Studio and it responds without delay - several times.

The NAV DB has 4.0SP3 technicaly. On the server are no other perfomance consuming applications, enough memory and cpu. The sift keys on the table are set. Table hast 1.7Mio entries.

So can anyone assume why there are so many reads and what could be the reason of that blocking situation?

Thanks for help.
Jan

kine

1) Are you using Update 6 or which exact build of the client you are using?
2) Is the Index Hinting enabled or disabled (search the forum for more info about the Index hinting)
3) PAGEIOLATCH is waiting for reading data from HDDs, because there is very high reading count, it means that there is some scan - check that you have some simple index just beginning with the Item No. field

kriki

[Topic moved from Navision forum to SQL Performance forum]