May 30, 2018 at 4:19 am
Morning Guys,
I have a very simple statement that does a select into a new table with no predicates. I want the whole of TableA to be replicated (data only) into TableB:
select *
into TableA
from TableB
It's currently taking 23 hours to complete (its a big table, a long time was expected)
As it is on a test system that has no other use currently I reset the wait stats and the following are the wait stats form that server (after removing the typically suggested stats to ignore by Paul Randall/sqlskills.com)
PAGEIOLATCH_SH 91%
PAGEIOLATCH_EX 9%
Given those two stats, what can I do to improve this. It's a VM so more memory and probably CPU can be granted. Even more space can be added but not necessarily the quality of the disks. We are using StorSimples, which in some benchmarking done by the infrastructure gang gave very simlar results in an IO test.
My main reason for writing in though is to learn something rather than just make this work.
is PAGEIOLATCH_SH an indicator of memory pressure?
Cheers
Alex
June 4, 2018 at 3:13 pm
alex.sqldba - Wednesday, May 30, 2018 4:19 AMMorning Guys,I have a very simple statement that does a select into a new table with no predicates. I want the whole of TableA to be replicated (data only) into TableB:
select *
into TableA
from TableBIt's currently taking 23 hours to complete (its a big table, a long time was expected)
As it is on a test system that has no other use currently I reset the wait stats and the following are the wait stats form that server (after removing the typically suggested stats to ignore by Paul Randall/sqlskills.com)
PAGEIOLATCH_SH 91%
PAGEIOLATCH_EX 9%Given those two stats, what can I do to improve this. It's a VM so more memory and probably CPU can be granted. Even more space can be added but not necessarily the quality of the disks. We are using StorSimples, which in some benchmarking done by the infrastructure gang gave very simlar results in an IO test.
My main reason for writing in though is to learn something rather than just make this work.
is PAGEIOLATCH_SH an indicator of memory pressure?
Cheers
Alex
It could be memory. Or it could be something in the I/O subsystem. Or it could be disks. Or it could be network. That's pretty much the case with any of the waits - they give you an idea of where to look but the waits alone generally won't give you a complete picture. Not sure what you read when trying to learn more about this but here are a couple of decent articles on that wait:
Knee-Jerk Wait Statistics : PAGEIOLATCH_SH
Handling excessive SQL Server PAGEIOLATCH_SH wait types
I'd also check the recovery model of the database and check if files were growing during the select into, pre-allocate a decent amount of space for the database files if needed.
Sue
June 4, 2018 at 5:47 pm
select *
into TableA
from TableBIt's currently taking 23 hours to complete (its a big table, a long time was expected)
[...]
is PAGEIOLATCH_SH an indicator of memory pressure?
PAGEIOLATCH_* waits are experienced during data-page disk reads. The length of each wait is, to SQL Server, the time it takes for a page to be fetched from disk.
You are reading a large table in order to rewrite it. That should cause a lot of disk reads.
Memory can only help if the source table were busy enough that other queries had already loaded much of it into memory before you try to read the entire table, and it wasn't flushed out by data from other queries.
Because you are copying a large table, and reading lots of data from disk, disk speed is the metric you must change to reduce the length of the PAGEIOLATCH_* waits.
Eddie Wuerch
MCM: SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply