October 26, 2014 at 9:07 pm
I have a busy 50GB database which takes ~24 hours to DBCC CHECKDB because it has many persisted/computed columns, and this triggers slowness which Paul Randal has blogged about in the past. It's busy in that it seems lots of simple INSERT statements but nothing in the way of explicit transactions.
Last week I restored a full backup from production in a testing environment and it took only a few minutes. Today I attempted to restore another full backup (but one that occurred near the end of the 24-hour DBCC CHECKDB window) in another testing environment and it has been going for > 40 minutes.
I restored with REPLACE, STATS and was watching the wait stats, so I could see:
- An initial pause with ASYNC_IO wait stat as it zero-initialized the files.
- The progress print statements 10-100% and "Processed XXXX pages" for the data and log file.
- And then 35+ minutes of PAGEIOLATCH_SH waits and random pages in this database as the resource.
- I can also observe the disk stats for the server are going crazy with reads and writes on that mount point.
My only guess is that it is rolling forward transactions that were committed during the DBCC CHECKDB, because I know that DBCC CHECKDB takes an internal snapshot while it is running. But I never thought it would have an impact like this - and these extremely slow DBCC CHECKDBs and any impact they have aren't documented very well 🙂
Am I on the right track?
October 27, 2014 at 2:40 am
Shouldn't be related to CheckDB, it makes no changes to the DB and you're not backing up the snapshot, you're backing up the source DB
Did you have long-running uncommitted transactions at the time the backup ran? It sounds like the time was the recovery process, which is rolling back uncommitted transactions and rolling forward any changes made during the backup time.
Semi-related suggestion. Given how long CheckDB takes, have you considered running it against a restored copy of the database instead of the actual database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2014 at 6:56 am
CHECKDB is orthogonal to restore speed. Likely you happened to end the data-reading portion of the backup at a point where there are long-running transactions to roll back, or lots of smaller transactions to roll back, so the restore has to do that. If you have a varying transaction load, it's a crap shoot how long a restore will take and performance of one can't be used to predict performance of the next one.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 27, 2014 at 7:05 am
The reason I thought not is because I was not aware of any long-running transactions on that database, I thought it's a data dump for single-row inserts with a fairly predictable load (e.g. it's a simple logging database).
But if we're saying the DBCC is definitely not connected then...
- I'll set up an agent job to capture long-running transactions/queries during the weekend to see if I can capture something I didn't know about.
- And copy the log backups this time so I can try some point in time restores to find the exact moment restores go wonky.
So far I only tried the full backups from the day before, during, and after the CHECKDB, and they were okay, not okay, and okay. Also there was no index maintenance scheduled to run at that time. Hence, the confusion.
Anyway thanks for the input. If I do end up tracking it down I'll post back.
October 27, 2014 at 7:27 am
did you double check the "slow" instances service account is authorized to use Instant File Initialization ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply