3

We are running SQL Server 2012 on Windows 2008 R2 with 256GB RAM and 90% memory allocated to SQL Server. No other Apps run on the server.

I have a scenario where I’ve to copy a Source (staging) table to the Target (Fact) table. The Staging table is un-compressed, has 215 columns – many are nullable - and has 8 million rows.

The Target table is page-compressed and occupies about 3GB of space after data-copy. I made sure nothing else was running when doing the tests. It takes 25+ mins. to for the data-copy to complete. To mitigate the situation, I ran it on 4 sessions concurrently with ~2 million rows per session. While 2 sessions finished in about 7 mins., another 2 sessions took nearly 14 mins. to complete. I tested it multiple times with similar results.

To understand what is happening,

Using guidance from your ‘where it hurts’ blogs for PAGEIOLATCH and capturing wait stats during a time period blogs, I found out the culprit is PAGEIOLATCH_EX waits.

We have Local HDDs in RAID 5 as storage sub-system. The Source table is all cached from previous runs and nothing else is running on the server.

Digging a bit deeper, using sys.dm_os_waiting_tasks while the job was running I captured the resource_description of the waits which provided – DBID:FILEID:PAGEID.

Then, using (sys.dm_os_buffer_descriptors, sys.allocation_units, sys.partitions), to my surprise all the waits are from the Target table!

Paul Randall describes PAGEIOLATCH_EX wait @ https://www.sqlskills.com/help/waits/pageiolatch_ex/ as (emphasis mine):

This wait type is when a thread is waiting for the "read of a data file page" from disk to complete, and the thread is going to modify the page structure once it is in memory (EX = EXclusive mode). The Latches Whitepaper in the sidebar on the right has a description of all latch modes and their compatibility with other latch modes.

I can understand if the IO latch waits are for source table but here they ALL are on Target table’s - DATA_PAGE & IN_ROW_DATA as per (sys.dm_os_buffer_descriptors, sys.allocation_units, sys.partitions)

Why are we seeing PAGEIOLATCH_EX waits on the Target table!? What is SQL Server reading from disk here for the Target table, while it is being loaded for the first time?

  1. Aren't SQL Server Writes data only in Memory - while:
  2. Write-Ahead Logging, Log Buffers & Log Writer takes care of writing to Transaction Log
  3. Checkpoint process taking care of flushing dirty data pages

Can you please help me understand what is happening here? Why am I seeing a "Read from disk" wait for the table that is being written to. There is PLENTY of RAM space and nothing else running... Thanks.

P.S.

Running the same test on a Server with SAN (better IO subsystem) all sessions/application threads finish around same time each taking ~3mins(8 sessions). However I do see same PAGEIOLATCH waits on Target table. Still couldn't understand what is SQL Server reading from disk for the Target table, while it is being loaded for the first time!

Improve this question
5
  • It is a financial database with generic schema covering all product types. Only index on target table is clustered PK on a single integer column. Application generates ever increasing row number but that happens in the Staging phase. While inserting rows, Target table is empty clustered PK on a single int column. No other non clustered index...
    – user93975
    Oct 15, 2018 at 22:43
  • The Target table is indeed empty when data copy starts (insert .. select)
    – user93975
    Oct 15, 2018 at 22:53
  • 1
    Does the insert use TABLOCK or TABLOCKX on the target table? See dba.stackexchange.com/q/165659/1186 and drillchina.wordpress.com/2012/12/18/…
    – Aaron Bertrand
    Oct 15, 2018 at 23:34
  • Actually to avoid lock escalation, in the concurrent sessions I'm inserting 1000 rows at a time in a while loop.
    – user93975
    Oct 16, 2018 at 0:15
  • 1
    I sent an email to Paul Randall and this is what he said: "I think the pages are being immediately marked for disuse in the buffer pool and then are having to be read back in because the data in the concurrent inserts isn't sorted and so it's having to re-read earlier pages - something like that. For an empty table with a single-threaded insert-select, there's no way you'd ever see any PAGEIOLATCH_EX waits"
    – user93975
    Oct 16, 2018 at 11:09

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.