Tuesday, March 27, 2012

Database Snapshots

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.Can you please explain the term about 'low', is it the data size or data?|||

Satya SKJ wrote:

Can you please explain the term about 'low', is it the data size or data?

It was "Slow" and not "low". Sorry.
I mean the time to execute a query.|||

It is by design@.

Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

|||

Satya SKJ wrote:

It is by design@.

Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

Ok. I read that note.
But what does it mean "Performance is reduced"?
Query on the normal table executed in about 40 seconds; on the snapshot it's over 8 minutes...

No comments:

Post a Comment