June 13, 2016 at 3:52 am
HELLO
I Have a serious problem
I am trying to run an sql query (uodate)
UPDATE DBO. GENERIC
SET TITLE= B.TITLE
FROM DBO. GENERIC AS A with (nolock)
INNER JOIN DBO. DISTINCT AS B with (nolock) ON
A.EXTERNAL_ID = B.EXTERNAL_ID
AND A.SOURCE_ID = B.SOURCE_ID
WHERE B.SOURCE_ID = 4
AND BATCH_ID IN (‘9000’,’9022’)
I am expecting to update about 50,000,000 rows . the problem is the query running for 28 hours and in activity monitor I get (suspended PAGEIOLATCH_EX )
Wait type 0x0044
And waitresource 13:1:78484916
How can I overcome this problem asap
thanks
June 13, 2016 at 3:57 am
Page IO Latch is a wait for the page to be read in to memory. Is it spending all the time waiting for that page, or are the waits across different pages?
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
June 13, 2016 at 4:34 am
i hope the attachment is helpfull
any idea what i have to do ?
June 13, 2016 at 4:48 am
the problem remain . any help ????
June 13, 2016 at 7:13 am
gxdatareal (6/13/2016)
HELLOI Have a serious problem
I am trying to run an sql query (uodate)
UPDATE DBO. GENERIC
SET TITLE= B.TITLE
FROM DBO. GENERIC AS A with (nolock)
INNER JOIN DBO. DISTINCT AS B with (nolock) ON
A.EXTERNAL_ID = B.EXTERNAL_ID
AND A.SOURCE_ID = B.SOURCE_ID
WHERE B.SOURCE_ID = 4
AND BATCH_ID IN (‘9000’,’9022’)
I am expecting to update about 50,000,000 rows . the problem is the query running for 28 hours and in activity monitor I get (suspended PAGEIOLATCH_EX )
Wait type 0x0044
And waitresource 13:1:78484916
How can I overcome this problem asap
thanks
1) PageIOLTHIS IS CLEARLY SPAMans the query is waiting to get data from disk into RAM where it can be operated on. This can happen for MANY reasons. The most likely is too slow IO and/or too little RAM. A bad query plan can cause this too, missing index (where such is appropriate to be used), etc.
2) You say you expect to update 50M rows. But how many have to be TOUCHED to get to those 50M? 100M? 1B?? What are the row counts and create table statements of both tables (including indexes)?
3) use sp_whoisactive to get more details on this query, including differential work done for a period of seconds (I would do 60). Please give us the query plan you can get from that free, awesome sproc too.
4) What is the configuration of the server?
5) Any triggers or foreign keys on the table being updated?
6) It is kind of silly to put a NOLOCK hint on a table you are updating. :hehe: That makes me think you and your company NOLOCK every query, which is a not-good-thing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 13, 2016 at 8:17 am
gxdatareal (6/13/2016)
the problem remain . any help ????
Maybe if you answered my question.
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
June 13, 2016 at 10:56 am
The execution plan (even an estimated plan at this point) would also help.
June 13, 2016 at 12:43 pm
I looked at the image you posted.
Wait time = 0ms. It's a disk read. That's what they look like. PAGEIOLATCH_* waits occur during disk reads. With that as both your last and current wait type (type 0x44 = PAGEIOLATCH_EX in SQL2014), it just means your query is reading lots of data. If those latches takes more than 30ms to acquire, then you get concerned. But heavy read/write queries such as this are, in fact, heavy on the reading and writing. The wait resource listed is just a page of data (<dbid>:<fileid>:<pageid>) Page 13:1:78484916 means it is a page at around 600GB deep in the database 13's primary data file. Do you have more than one data file? What is the read/write performance on this file?
Threads actually spend way more time Suspended than Running, especially in cases like this where you're clearly rifling through lots and lots of data off disk. Disk reads and writes are kernel-mode stuff - Windows performs the disk work outside of SQL Server. When your thread wants a page of data that's not in cache, then it cannot continue until Windows separately does some work. So, the thread is suspended (with a PAGEIOLATCH_* wait type) to free up the CPU thread for processes that aren't waiting on things until Windows finishes the work and notifies SQL Server that the data has been loaded into memory. Once the data's there, the query may continue, until it needs data from another page that isn't in memory, at which time it will once again be suspended with a PAGEIOLATCH_* wait, but the wait_resource will change to the page the query is now trying to read or write (must read into memory before write can occur). Repeating Gail's question: does the wait_resource change when you re-run the query that produced the output you pasted?
The only way to avoid that wait is to not read from disk. Tuning a query is, more than anything else, the art of making it not read from disk. You can't blame a computer for doing what you told it to do.
Look at the CPU and physical IO columns in your output. Over 30 million physical IO (touching over 200GB of physical data), and 2000 seconds of CPU time. Note: 30MM physical IO over 28 hours = ~300 continuous IOPS for one query. What about the disk(s) hosting the data and log? What does tempdb look like? Are you seeing SLEEP_TASK waits? Or SLEEP_BPOOL_FLUSH, WRITELOG, LOGBUFFER, LCK_M_*, CHECKPOINT, RESOURCE_SEMAPHORE..?
The very limited info you've posted so far only support one conclusion: server operation is normal, huge query is running. The issue is the query and the data it touches. Without some real data from your end about the tables and indexes involved, there is only one conclusion: you must tune your query or simply wait for it to complete.
If that source query is not written and indexed to support such an operation, then it's going to take forever.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply