SQLServer Query

SQL Server database snapshot

What is the use of SQL Server database snapshot and how to create SQL Server database snapshot?

Database snapshots are available only in SQL Server 2005 Enterprise Edition and later versions. All recovery models support database snapshots.

A database snapshot is a read-only, static view of the source database.Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation.A snapshot persists until it is explicitly dropped by the database owner.

Snapshots can be used for reporting purposes.In the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot’s creation.

Creating a series of snapshots over time captures sequential snapshots of the source database. Each snapshot exist until it is dropped.Each snapshot will continue to grow as  pages in original database are updated, you may want to conserve disk space by deleting an older snapshot after creating a new snapshot.

Steps to create database snapshot in SQL Server:

1.Create a new database or use existing database to create the database snapshots.

Syntax:

USE master
GO
— Create database
CREATE DATABASE test
GO
USE test
GO

2.Create table  and Insert values into table.

Syntax:

–create a Table
CREATE TABLE test1 (a INT, b INT,c INT)
—Insert values
declare @a nchar(10)
set @a=1
while (@a<5)
begin
insert into test1 values (@a,’1′,’1′)
set @a=@a+1
end

3.Create a snapshot  for a source database.

Syntax:

— Create Snapshot Database
CREATE DATABASE Snapshottest ON
(Name =’test’,
FileName=’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\testsnap.ss’)
AS SNAPSHOT OF test;
GO

image

Snapshottest database is created successfully.

4.Now let us view both the source database and snapshot database.

Syntax:

SELECT * FROM test.dbo.test1;
Go
SELECT * FROM Snapshottest.dbo.test1;
Go

Data’s in tables of source and snapshot database are same.

 image

Size on disk  of the testsnap .ss could be viewed by opening its properties window.Let us note the  size on disk  of the source database.

image

5.Let us update existing rows in the table.

Syntax:

update test1 set a=0,b=0,c=0

6.After updating rows ,let us view the source and snapshot database.The values are updated only in source database,not in snapshot database.Because snapshot database is consistent with the source database as of the moment of the snapshot’s creation.

Syntax:

— Select from test and Snapshottest Database
SELECT * FROM test.dbo.test1;
SELECT * FROM Snapshottest.dbo.test1;
GO

image 

But size on disk is increased,It clearly shows that when we update data in Source database, it copies the old/existing data pages to Snapshot database. This is the reason why the size of the snapshot database is increasing while updating the source database.

image

7.We could revert the source database.Reverting overwrites updates made to the source database since the snapshot was created by copying the pages from the sparse files back into the source database.

Syntax:

— Restore old data from Snapshot Database
USE master
GO
RESTORE DATABASE test
FROM DATABASE_SNAPSHOT = ‘Snapshottest’;

8.View the data in the tables from the source and the snapshot database after the restore from snapshot.

Syntax:

SELECT * FROM test.dbo.test1;
Go
SELECT * FROM Snapshottest.dbo.test1;
Go

image

9.We drop the snapshot database like any other database using drop database command.

Syntax:

— drop snapshottest
DROP DATABASE [snapshottest];

10. We can also create database snapshot on mirror database for load balancing.

Advertisement
by

Capture context switches from dm_os_ring_buffers

You can use the below query to extract the context switches information from ring buffers and time each thread spend owning the scheduler.

SELECT  
dateadd (ms, (a.[timestamp] - tme.ms_ticks), GETDATE()) as Time_Stamp,
a.*
FROM
(SELECT 
	  y as timestamp,	
      x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
      x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
      x.value('(//Record/@time)[1]', 'bigint') AS [Time],
      x.value('(//Record/Scheduler/@address)[1]', 'varchar(30)') AS [Scheduler Address],
      x.value('(//Record/Scheduler/Action)[1]', 'varchar(30)') AS [Scheduler Action],
      x.value('(//Record/Scheduler/CPUTicks)[1]', 'bigint') AS [Scheduler CPUTicks],
      x.value('(//Record/Scheduler/TickCount)[1]', 'bigint') AS [Scheduler TickCount],
      x.value('(//Record/Scheduler/SourceWorker)[1]', 'varchar(30)') AS [Scheduler SourceWorker],
      x.value('(//Record/Scheduler/TargetWorker)[1]', 'varchar(30)') AS [Scheduler TargetWorker],
      x.value('(//Record/Scheduler/WorkerSignalTime)[1]', 'bigint') AS [Scheduler WorkerSignalTime],
      x.value('(//Record/Scheduler/DiskIOCompleted)[1]', 'bigint') AS [Scheduler DiskIOCompleted],
      x.value('(//Record/Scheduler/TimersExpired)[1]', 'bigint') AS [Scheduler TimersExpired]
FROM
      (SELECT CAST (record as xml),timestamp  
      FROM sys.dm_os_ring_buffers 
      WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER' ) AS R(x,y)) a
	  cross join sys.dm_os_sys_info tme 
WHERE a.[Scheduler Action] = 'SCHEDULER_SWITCH_CONTEXT'
ORDER BY       a.[Scheduler Address] , [Time_stamp]

by

Deferred update instead of direct

Although we dont update the column of unique constraint we might verywell end up with defererred update if you dont pass all the coulumns of PK contraint in where clause of the update query. We have to review all update statements to check if all columns of unique constraint is passed in where clause.

 Assume you fire below query on table with below constraint

 CONSTRAINT [PK_] PRIMARY KEY CLUSTERED
(
 c1  ASC,
 c2 ASC
 )

update table xx set xx=1 where pk1=1 and xx2=2

It is possible that multiple rows will be returned by the where clause in the update,hence breaks the requirements for a direct update (it is considered to be a multirow update because of the where clause)
Althought you did not update PK columns ,your search argument did not specify value for all the columns used to create index ,so you might end with deferred update