January 10, 2018 at 5:20 am
Hi,
Does anyone have experience of cloning a DW that uses columnstore indexes. Thinking of the clone moving to different server.
I'm interested in what happens to the columnstore indexes - I suppose the compressed row groups come across - do we have to warm the cache so to speak?
Best
Lee
January 10, 2018 at 6:10 am
The storage of the index on the image is going to be basically identical to what's stored inside a database because it's just a copy of the pages, so there's nothing special about the columnstore that's going to be hurt by being in a clone. Now, the cache, yeah, that will have to get warmed up after creating a clone, but it's the same as if you restored the database. Everything is on disk, not in-memory. There will be a process of getting the hotter stuff loaded into memory. The clone process does mean that the step of loading stuff into memory is a little slower since it's not accessing local storage but is going through the hop out to the image, but since we're talking dev/test here (not production, never production), then it's pretty painless.
I hope that helps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2018 at 6:41 am
Thanks Grant - that makes sense. One use case I have in my mind is to run a daily clone: Prod to Test/Sandbox (for ad-hoc user querys). Reluctant to have ad-hoc user queries on fact table run in conjunction with production workloads.
January 10, 2018 at 7:19 am
leehbi - Wednesday, January 10, 2018 6:41 AMThanks Grant - that makes sense. One use case I have in my mind is to run a daily clone: Prod to Test/Sandbox (for ad-hoc user querys). Reluctant to have ad-hoc user queries on fact table run in conjunction with production workloads.
Yeah, that can work. Offloading reporting like this is a valid use case too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply