Grant Fritchey (8/20/2013)
GilaMonster (8/20/2013)
Grant Fritchey (8/20/2013)
I thought the internal/hidden snapshot was a different critter than the snapshots we create. But, hey, happy to be wrong.No, the same thing, same behaviour, same mechanics.
The only difference with the CheckDB snapshot is that it's created on all editions (only on Enterprise can you create an explicit snapshot) and it puts the snapshot files into NTFS alternate streams.
But if you run DBCC against a snapshot, do you get another snapshot?
No.
And since it's going back to the original pages anyway for all the reads, what's the point of separating it out?
Transactional consistency for logical checks. Otherwise you'd have to do it the SQL 2000 way, of running crash recovery in-memory to get a stable view of the tables. Massively complex. Snapshots ensure transactional consistency and a stable view of the tables which means that the CheckDB process doesn't have to worry about concurrent changes while working through the database.
That's why the alternative to using a snapshot is WITH TABLOCK - take table level locks to ensure the table can't change while being checked.
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