February 8, 2016 at 11:13 am
Hi Team,
I have a SQL Query which takes around 5 hours as the DB size is huge (600 GB) and is in a bad maintenance stage.
We take a DB snapshot of this Database every day.
My Query:
> If I run the SQL query on DB snapshot , it should not have any impact on the main Database, right ? .Are there any implications at all ?.
I am not worried about latest data, as we need last month's data only and DB snapshot is refreshed every day.
February 8, 2016 at 11:28 am
DB Snapshots use the database as the main source of data and only store pages that get modified. That means that querying against the snapshot is probably going to impact the database.
This article explains it: https://technet.microsoft.com/en-us/library/ms187054(v=sql.105).aspx
February 8, 2016 at 12:40 pm
Snapshot won't help you, in fact it will very likely make performance worse.
If you only need to refresh the reporting view once a day, back up the original db and restore it to a new name for reporting. If you can, make the new db read only, for max performance.
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".
February 9, 2016 at 6:50 am
Luis Cazares (2/8/2016)
DB Snapshots use the database as the main source of data and only store pages that get modified. That means that querying against the snapshot is probably going to impact the database.This article explains it: https://technet.microsoft.com/en-us/library/ms187054(v=sql.105).aspx
The only time it's liable to affect the source database is when a query performs reads from disk to populate the buffer pool, disk I\O can be hit in this case.
Once in memory the page access is the same as any other database.
the way snapshots work is by using sparse files. After the point of the snapshot any database pages which change are copied to the snapshot databases sparse files to preserve the data from the snapshot point.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 9, 2016 at 9:35 am
Perry Whittle (2/9/2016)
Luis Cazares (2/8/2016)
DB Snapshots use the database as the main source of data and only store pages that get modified. That means that querying against the snapshot is probably going to impact the database.This article explains it: https://technet.microsoft.com/en-us/library/ms187054(v=sql.105).aspx
The only time it's liable to affect the source database is when a query performs reads from disk to populate the buffer pool, disk I\O can be hit in this case.
Once in memory the page access is the same as any other database.
the way snapshots work is by using sparse files. After the point of the snapshot any database pages which change are copied to the snapshot databases sparse files to preserve the data from the snapshot point.
Every modification to the table data also has to be done twice, which naturally can also slow down main table processing.
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".
February 9, 2016 at 9:37 am
ScottPletcher (2/9/2016)
Perry Whittle (2/9/2016)
Luis Cazares (2/8/2016)
DB Snapshots use the database as the main source of data and only store pages that get modified. That means that querying against the snapshot is probably going to impact the database.This article explains it: https://technet.microsoft.com/en-us/library/ms187054(v=sql.105).aspx
The only time it's liable to affect the source database is when a query performs reads from disk to populate the buffer pool, disk I\O can be hit in this case.
Once in memory the page access is the same as any other database.
the way snapshots work is by using sparse files. After the point of the snapshot any database pages which change are copied to the snapshot databases sparse files to preserve the data from the snapshot point.
Every modification to the table data also has to be done twice, which naturally can also slow down main table processing.
done twice for what??
You modify a table and subsequently a page in memory and that's where it stays. If the page is flushed to disk and replacing the page on disk before the snapshot was taken then this original will be copied to the sparse file for the snapshot database. It's not going to be every page every time and actions aren't exactly done twice
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 9, 2016 at 9:53 am
Perry Whittle (2/9/2016)
ScottPletcher (2/9/2016)
Perry Whittle (2/9/2016)
Luis Cazares (2/8/2016)
DB Snapshots use the database as the main source of data and only store pages that get modified. That means that querying against the snapshot is probably going to impact the database.This article explains it: https://technet.microsoft.com/en-us/library/ms187054(v=sql.105).aspx
The only time it's liable to affect the source database is when a query performs reads from disk to populate the buffer pool, disk I\O can be hit in this case.
Once in memory the page access is the same as any other database.
the way snapshots work is by using sparse files. After the point of the snapshot any database pages which change are copied to the snapshot databases sparse files to preserve the data from the snapshot point.
Every modification to the table data also has to be done twice, which naturally can also slow down main table processing.
done twice for what??
You modify a table and subsequently a page in memory and that's where it stays. If the page is flushed to disk and replacing the page on disk before the snapshot was taken then this original will be copied to the sparse file for the snapshot database. It's not going to be every page every time and actions aren't exactly done twice
True, SQL delays the actual copying of the original page until it has to flush the buffers, but in the normal course of activity that should happen reasonably soon. And true, subsequent mods to the same page won't require the copy, but those are likely buffered anyway. Still, the big performance hit is that any actual mod to any page requires first copying that page, which in effect roughly doubles the I/O overhead for mods.
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".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply