August 26, 2016 at 3:24 am
Sever DBs have just been migrated from SQL 2005 to 2008, pretty much the same spec/hardware/settings. There are two main DBs, the first one is operational, and it immediately needed stats updating to perform efficiently. The second DB is used for ODS and has a nightly movement of data from the operational DB.
The first time the data move took place instead of taking its normal 3.5 hours it ran for 17 hrs :crying:. The migration is a series of select/insert statements, but it appeared to be the inserts that were taking the time to be performed. The destination tables have all indexes and constraints dropped at the start of this load and re-added at the end.
Should the fact that there was no stats update performed on the ODS DB cause inserts to be slow when the tables had no indexes??? My hunch is that it shouldn't - am I wrong?
August 26, 2016 at 6:40 am
No, it's unlikely that the statistics on tables that are emptied and reloaded should have been updated before they were emptied and reloaded. They would naturally get updated through that process, so there's nothing that you could have done. The issue lies somewhere else. Hard to even suggest for sure without more detail.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2016 at 7:17 am
If it makes a difference, the destination tables are not emptied, they just have additional data written to them. Before/after this runs any table constraints are removed/added. Strangely though, the time needed to add the constraints at the end has halved, which suggests performance of the new box is generally good.
An additional factor seems to be that for the first 17 hour run parallelism was sitting at 1 (its normal daily value), rather than the value of 4 which is usually switched on temporarily for the duration of the load process. When that parallelism of 4 setting was originally introduced several years ago, it gave a slight improvement in running time, but things didn't used to run as slowly a17 hours.
August 26, 2016 at 7:24 am
rarara (8/26/2016)
If it makes a difference, the destination tables are not emptied, they just have additional data written to them. Before/after this runs any table constraints are removed/added. Strangely though, the time needed to add the constraints at the end has halved, which suggests performance of the new box is generally good.An additional factor seems to be that for the first 17 hour run parallelism was sitting at 1 (its normal daily value), rather than the value of 4 which is usually switched on temporarily for the duration of the load process. When that parallelism of 4 setting was originally introduced several years ago, it gave a slight improvement in running time, but things didn't used to run as slowly a17 hours.
Dropping the indexes means that the statistics are also dropped for those statistics. Table statistics are not dropped and will be maintained & updated through the automatic statistics processes (assuming those are still enabled). It's possible these statistics are needed depending on the data loads (again, no detail, so just guesses).
For parallelism, take a look at the cost threshold for parallelism setting on the server. That determines what processes can go parallel, not the MAXDOP.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply