Database Snapshots Performance

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.

There is a section in Books Online titled "How Database Snapshots Work" which shows the extra level of redirection for snapshots. For a newly created snapshot, the data will not be in cache so it might take a little while to pull the data into the buffer pool. Once it is "warmed up" though, the performance should not be that different.

You can look at the sys.dm_db_index_operational_stats and sys.dm_io_virtual_file_stats DMVs to try to determine where the issues are.

|||Yes, but I always have a "newly created snapshot". Infact, to update the snapshot, i need to drop and re-create. And i do this every day at least.|||

So, do the performance problems persist, or are they temporary until the cache is populated?

This will help narrow down where the problem may be.

Database Snapshots Performance

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.

There is a section in Books Online titled "How Database Snapshots Work" which shows the extra level of redirection for snapshots. For a newly created snapshot, the data will not be in cache so it might take a little while to pull the data into the buffer pool. Once it is "warmed up" though, the performance should not be that different.

You can look at the sys.dm_db_index_operational_stats and sys.dm_io_virtual_file_stats DMVs to try to determine where the issues are.

|||Yes, but I always have a "newly created snapshot". Infact, to update the snapshot, i need to drop and re-create. And i do this every day at least.|||

So, do the performance problems persist, or are they temporary until the cache is populated?

This will help narrow down where the problem may be.

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...

Database Snapshot Performance

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
"Slow".
Can anyone tell me why?
Tanks.
F.
A snapshot is slow because it makes a copy of of modified data in tempdb.
Thus reads are scattered all over. The real question is why would you NEED
a snapshot of a fact table? This is non-standard DW practice AFAIK.
TheSQLGuru
President
Indicium Resources, Inc.
"Johnny" <xxx.johnny@.gmail.com> wrote in message
news:1171290125.043815.129030@.v33g2000cwv.googlegr oups.com...
> 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
> "Slow".
> Can anyone tell me why?
> Tanks.
> F.
>

Database Snapshot Performance

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
"Slow".
Can anyone tell me why?
Tanks.
F.A snapshot is slow because it makes a copy of of modified data in tempdb.
Thus reads are scattered all over. The real question is why would you NEED
a snapshot of a fact table? This is non-standard DW practice AFAIK.
TheSQLGuru
President
Indicium Resources, Inc.
"Johnny" <xxx.johnny@.gmail.com> wrote in message
news:1171290125.043815.129030@.v33g2000cwv.googlegroups.com...
> 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
> "Slow".
> Can anyone tell me why?
> Tanks.
> F.
>