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.