Knowledge Base

Copying on the fly (SQL trigger)

A trigger is a database object that is attached to a table. It is similar to a stored procedure. A trigger is only fired when an INSERT, UPDATE or DELETE occurs.

SQL Server uses a lot of processing time when calling a trigger and you must try to keep this to the minimum. The SQL script that inserts a trigger should be as short as possible and must never lock the write process.

Only use an SQL trigger if you are confident that you have the required skills to do it properly.

Copying on demand (SQL script)

If your application does not need to create a table copy on the fly it is preferable to request a snapshot of the table instead.

Snapshot FAQ

What is a snapshot – A database snapshot is a read-only, static view of a SQL Server database. The snapshot content is a copy of your database at a given time. To reiterate, if the source database content changes after the snapshot , the snapshot contents doesn’t change.

Why use a snapshot – The main interest for PcVue is that the snapshot isolation does not cause lock escalation. A snapshot does not request a lock when reading data. You can select a large quantity of data or lock snapshot without disturbing the HDS table.

Things to be aware of –

• Each snapshot reduces IO write performance, so you should try to delete snapshots that are no longer required.
• A snapshot offers better performance than a trigger.
• You need to create a snapshot each time you need fresh data.
• The action to delete a snapshot can take a long time if there has been a lot of modifications on the source database.

Example of a T-SQL script to create a snapshot

/* CREATE AND UPDATE A SNAPSHOT */
/* Define the source database name and snapshot path */

declare @DBName sysname
declare @SSName sysname
declare @DBDataLogicalName sysname
declare @SSPath nvarchar(MAX)
declare @REQ_CreateSnap nvarchar(MAX)

set @DBName = N’PRJ_DATABASE1′
set @SSPath = N’E:\11.1\Databases\PRJ_DATABASE1.ss
set @SSName = @DBName + N’_ss’

SELECT @DBDataLogicalName=name FROM sys.master_files WHERE database_id = DB_ID(@DBName) AND type_desc = ‘ROWS’

IF (EXISTS(SELECT name FROM master.dbo.sysdatabases WHERE (‘[‘ + name + ‘]’ = @SSName OR name = @SSName)))

BEGIN
declare @REQ_DropDb nvarchar(MAX)
set @REQ_DropDb = ‘DROP DATABASE ‘ + @SSName
EXEC(@REQ_DropDb)
END

SELECT @REQ_CreateSnap = ‘CREATE DATABASE ‘ + @SSName +
‘ ON (NAME=’ + @DBDataLogicalName +‘, FILENAME=’ + quotename(@SSPath) + ‘)’ +
‘ AS SNAPSHOT OF ‘ + @DBName

EXEC(@REQ_CreateSnap)

Other useful scripts

/* USE YOUR SNAPSHOT INSTEAD YOUR DATABASE */
/*SELECT * FROM [PRJ_DATABASE1].dbo.TRENDTABLE1*/

SELECT * FROM [PRJ_DATABASE1_ss].[dbo].TRENDTABLE1

/* LOCK YOUR TABLE */

BEGIN TRANSACTION
UPDATE [PRJ_DATABASE1].[dbo].TRENDTABLE1
SET Chrono = Chrono
WAITFOR DELAY ’00:02:00′ — 2 minutes hh:mm:ss
ROLLBACK TRANSACTION

/* SHOW LOCKS */

SELECT
DB_NAME(resource_database_id) AS database_name,
resource_type,
request_type,
request_mode,
resource_description

FROM sys.dm_tran_locks;

Further reading

Lock escalation: http://technet.microsoft.com/en-US/en-en/library/ms184286(v=sql.105).aspx
Snapshot isolation level in request: http://msdn.microsoft.com/fr-fr/library/ms173763.aspx (In French)
Snapshot isolation level in request: http://msdn.microsoft.com/en-en/library/ms173763.aspx (In English)
Database snapshots (SQL Server): http://msdn.microsoft.com/en-us/library/ms175158.aspx