January 1, 2011 at 12:04 am
Need help please.. We are experiencing PageIOLatch_* and spid being blocked by itself. the query goes like this. Not sure if this is index problem, but this query used to work. Suddenly, the server experienced slow down and now, we can no longer connect. With just one client connection, the query is very slow. Do you think this is performance issue because of poorly written query? I'm just confused because this script used to work. We also tried to restore a copy of the database to a different server just to isolate the hardware problem, still same. Any suggestion please! thanks!
by the way, the number of rows in this table is about 20M, but i think this is not much. There is an index (asc) containing strip_id, Row_Pos, Col_pos. Not very good in SQL though but when i execute this, with about 2000 rows as output, this query takes about 3-4mins. Any suggestion please! Thanks!
Select Count(0) From SM_Rework_History_V A,
( Select Strip_ID, Row_Pos, Col_Pos, Max(TimeStamp)
As TimeStamp From SM_Rework_History_V Where Lot_ID = 'ABC12345.1' And
TimeStamp <= '2010/12/29 20:04:09' And (Unit_Status = 'B' Or Unit_Status = 'G')
Group By Strip_ID, Row_Pos, Col_Pos) B,
( Select Distinct Strip_ID From SM_Rework_History_V Where Lot_ID = 'ABC12435.1'
And TimeStamp <= '2010/12/29 20:04:09' And TGrade Is Not Null) C
Where A.Strip_ID = B.Strip_ID
And A.Strip_ID = C.Strip_ID And A.Row_Pos = B.Row_Pos And A.Col_Pos = B.Col_Pos
And A.TimeStamp = B.TimeStamp And A.Unit_Status = 'B'
January 2, 2011 at 5:33 pm
mrcmiranda_sg (1/1/2011)
Need help please.. We are experiencing PageIOLatch_* and spid being blocked by itself.
This basically means the query is waiting for itself, while the Disk I/O subsystem returns data.
Not sure if this is index problem, but this query used to work. Suddenly, the server experienced slow down and now, we can no longer connect.
This usually points to a hardware failure, IE: one disk in a RAID 5 going kablewie, however, since you've already done some troubleshooting on this...
We also tried to restore a copy of the database to a different server just to isolate the hardware problem, still same. Any suggestion please! thanks!
by the way, the number of rows in this table is about 20M, but i think this is not much. There is an index (asc) containing strip_id, Row_Pos, Col_pos.
The query itself isn't helpful enough alone, we'd need to see the sqlplan of an actual execution if possible, or at least the estimated if it can't complete. That said, however, it's possible you've gone from seeks to scans because the selectivity has changed. That difference would make the system try to load the table to memory, and then probably page the data to the swapfile.
Most likely, you've experienced a swap of this nature. Possibly on multiple tables.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 3, 2011 at 12:10 pm
mrcmiranda_sg,
To further clarify what Craig said in his post, you may have hit a 'tipping point' in your query. Usually you would see this before 20M rows in the table, but it could just be that you hit a point in the query where the the results of the outer part of the query won't fit into memory any more and has to be flushed out to disk and then read again for the sub-queries. This causes a lot of IO.
If this is the case then you may want to 'divide and conquer' as Jeff Moden has coined. This would be something like getting the results of just the outer query into a temp table and then using that against the sub-queries, or even updating the temp table against 1 sub-query at a time.
Without an actual query plan there's no way to tell.
Todd Fifield
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply