matt stockham (9/24/2008)
Re. inserts ... wouldn't it have to write an entire page of null bytes, so still the same data size?Logically it shouldn't need to do anything at all with a new page - the snapshot won't care because the page didn't exist at the time it was created. Following that train of thought makes me wonder why there is any overhead at all unless there is some requirement on the index pages or any of the system-type pages - maybe contention on whichever set of pages determines if the snapshot file holds the page to look at? Am I missing something obvious here?
Possibly the overhead is caused by necessary changes to the allocation pages. I'm just guessing here, as I don't know the exact details of what changes are made to what and when when it comes to snapshots.
I do know someone to ask though. 😎
I only asked because the article covered all the other obvious scenarios - there are probably a number of good questions that could be answered on the read side - does the snapshot have its own procedure cache entries etc.
I'll put said article on the to-do list.
From what I recall, the snapshots do have their own cache entries, both procedure and data. This applies even to data pages read from the source 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