February 27, 2015 at 11:49 am
I have a large (1.2TB) database that receives constant inserts but no updates. The inserts can be 100+ per second. Reporting queries against the database can impact the application that does the inserts. I want to try a Database Snapshot for the reporting queries to run against. But don't queries against the snapshot actually hit the standard database, unless that page in the standard database has changed? It seems to me if a query disrupts the standard DB then querying the snapshot will also disrupt the DB. Have I got that right?
thanks
The DB is SQL 2008r2 Enterprise.
February 28, 2015 at 8:45 pm
Ken Davis (2/27/2015)
I have a large (1.2TB) database that receives constant inserts but no updates. The inserts can be 100+ per second. Reporting queries against the database can impact the application that does the inserts. I want to try a Database Snapshot for the reporting queries to run against. But don't queries against the snapshot actually hit the standard database, unless that page in the standard database has changed? It seems to me if a query disrupts the standard DB then querying the snapshot will also disrupt the DB. Have I got that right?thanks
The DB is SQL 2008r2 Enterprise.
Using database snapshots for offloading reporting queries is one of the benefits of the technology. A query against the snapshot will query the snapshot and not the source database. The original condition of the data is maintained by a sparse file. When a change to the original data happens in the source database then the original data is maintained in the snapshot via the sparse file. As data continues to change the sparse file will grow.
March 2, 2015 at 1:51 pm
Imarkum,
Thanks for the reply but if understand the concept, the copy-on-write means a page of data only goes into the snapshot sparse file when that page gets updated in the source database. So with a DB that never gets updated, it seems the sparse file will remain empty and queries against the snapshot are actually hitting the source database, just like if there was no snapshot. That is why I am trying to understand if Snapshots bring any benefit in this case.
March 2, 2015 at 1:55 pm
Yes, you will query back to the original db for the unchanged pages. I confirmed this using BOL as well; from BOL:
"If the page has not yet been updated on the source database, a read operation on the snapshot reads the original page from the source database."
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 2, 2015 at 10:39 pm
Ken Davis (3/2/2015)
Imarkum,Thanks for the reply but if understand the concept, the copy-on-write means a page of data only goes into the snapshot sparse file when that page gets updated in the source database. So with a DB that never gets updated, it seems the sparse file will remain empty and queries against the snapshot are actually hitting the source database, just like if there was no snapshot. That is why I am trying to understand if Snapshots bring any benefit in this case.
I read BOL a few times and re-read parts of SQL Server 2008R2 Unleashed on this topic. I suppose perhaps I have misunderstood database snapshots from the very beginning. It seems that you and Steve are saying that the only parts of a query against a snapshot that actually run against the snapshot are source data pages that were copied to the sparse file prior to being changed in the source database.
Some of the verbiage in BOL seems to suggest that the snapshot is an entity of its own, but I understand what you and Steve are saying. My apologies for an incorrect answer, but part of why I participate on the forums is to learn.
I created a database snapshot of Adventureworks, ran Profiler to capture queries, and the database name that is returned is the name of my snapshot. This would suggest to me that the snapshot is being queried, but perhaps what I am really doing is querying a logical abstraction of the source database, like querying a view instead of the underlying tables. Given all of this, I am really not sure why database snapshots would be mentioned as a way to offload reporting from a source database since it seems that the only time a query would truly access the snapshot is when the sparse file is accessed for pages that changed in the source database.
March 3, 2015 at 9:48 am
The only time a snapshot offloads the work is when it's used in conjunction with database mirroring. You can create a snapshot of the mirror database and report off that, meaning it's a separate server from the read-write primary.
A snapshot directly off the source DB is just a point-in-time view of that database. It alleviates locking (being read-only), but that's about all. Queries against the snapshot read pages from the sparse file if the data on that page in the source DB had changed since the snapshot's creation, otherwise the query reads the page from the source DB.
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
March 3, 2015 at 10:06 am
Thanks for the clarifications.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply