Hi all,
We are trying to load a batch in a huge table ( 150 GB) , but sometimes we are getting timeout errors .
When i see in activity monitor, i can see some entries like PAGEIOLATCH_EX, PAGEIOLATCH_SH.
Can somebody suggets me the workaround for this problem.
Thanks for all your help.
Configuration:
Sql Server 2005 SP1, 4GB Memory, Windows 2003 standard Edition SP1
Could you post an example of the error you are getting?Can you check what level of growth is specified for you data/log files please? eg 10% or 100MB....
|||
Hi Rich,
Growth for the data file = 2000 MB , Unrestricted Growth. and log file = 10% Unrestricted growth.
And about error, when i see in the activity monitor , i can see PAGEIOLATCH_EX, PAGEIOLATCH_SH, in the wait type column. ( its taking a long time, for rolling back the batch)
Is there anything about indexes , which affects this kind of error.
Thanks.
|||Are you getting errors in the SQL Error Log? If so, could you copy and paste one of these errors into the thread as it may help diagnose the problem?
|||In general, Significant wait times on page latches is often related to disk issues. You'd generally want to watch your disk counters in Perf Mon.
But what about your log during this process - is it large enough to handle the load? What is 10% growth - meaning, what size is it trying to grow to if this is the problem. I generally don't have grow logs by a percent - you have no control over what size increments it's growing. If it's a large file you are trying to load, you would also want to try batching the inserts.
-Sue
