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?
- Aren't SQL Server Writes data only in Memory - while:
- Write-Ahead Logging, Log Buffers & Log Writer takes care of writing to Transaction Log
- 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!
