February 1, 2013 at 12:06 am
Hi all,
I have 2 sessions:
Session1:
UPDATE a
SET Column1 = fnc_calculatecost(b.ID)
FROM Table_A a
JOIN Table_B b ON a.ID_B_FK = b.ID
INSERT INTO Table_C
Session2:
SELECT
FROM Table_C
WHERE ID =
1/In function fnc_calculatecost, I also have code
SELECT
FROM Table_A a
JOIN Table_B b ON a.ID_B_FK = b.ID
Table_A ~ 700.000 rows
Table_B ~ 90.000 rows
Table_C ~ 2milion rows
2/Table_A has a foreign key ID_B_FK to Table_B(ID)
3/Session 1 and 2 are running at the same time.
So,I'm facing a problem PAGEIOLATCH_SH lock on Table_B table and LCK_M_IS lock on Table_C in my query.
Any suggestions?
Thanks,
February 1, 2013 at 1:44 am
PageIOLatch isn't a lock, it's a latch. Usually used when SQL is moving pages around from memory to disk or disk to memory. Probably you don't have enough cache memory for this and all other queries and the reads from disk are taking time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2013 at 3:15 am
GilaMonster (2/1/2013)
PageIOLatch isn't a lock, it's a latch. Usually used when SQL is moving pages around from memory to disk or disk to memory. Probably you don't have enough cache memory for this and all other queries and the reads from disk are taking time.
Thank for your info. As your explanation, we don't have any solution to resolve this issue. Maybe, there were many processes at the same time and there was not enough cache.
February 1, 2013 at 3:36 am
Read less data (optimise queries), add memory, optimise the IO subsystem
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply