Simon's SQL

GO

/****** Object:  StoredProcedure [core].[sp_purge_data]    Script Date: 04/06/2011 04:25:41 ******/

SET ansi_nulls ON

GO

SET quoted_identifier ON

GO

ALTER PROCEDURE [core].[Sp_purge_data] @retention_days SMALLINT = NULL,

@instance_name SYSNAME = NULL,

@collection_set_uid UNIQUEIDENTIFIER =

NULL,

@duration SMALLINT = NULL

AS

BEGIN

— Security check (role membership)

IF ( NOT ( Isnull(Is_member(N’mdw_admin’), 0) = 1 )

AND NOT ( Isnull(Is_srvrolemember(N’sysadmin’), 0) = 1 ) )

BEGIN

RAISERROR(14677,

16,

–1,

‘mdw_admin’)

RETURN( 1 ) — Failure

END

— Validate parameters

IF ( ( @retention_days IS NOT NULL )

AND ( @retention_days < 0 ) )

BEGIN

RAISERROR(14200,

–1,

–1,

‘@retention_days’)

RETURN( 1 ) — Failure

END

IF ( ( @duration IS NOT NULL )

AND ( @duration < 0 ) )

BEGIN

RAISERROR(14200,

–1,

–1,

‘@duration’)

RETURN( 1 ) — Failure

END

— This table will contain a record if somebody requests purge to stop

— If user requested us to purge data – we reset the content of it – and proceed with purge

— If somebody in a different session wants purge operations to stop he adds a record

— that we will discover while purge in progress

—

— We dont clear this flag when we exit since multiple purge operations with differnet

— filters may proceed, and we want all of them to stop.

DELETE FROM [core].[purge_info_internal]

SET @instance_name = Nullif(Ltrim(Rtrim(@instance_name)), N”)

— Calculate the time when the operation should stop (NULL otherwise)

DECLARE @end_time DATETIME

IF ( @duration IS NOT NULL )

BEGIN

SET @end_time = Dateadd(MINUTE, @duration, Getutcdate())

END

— Declare table that will be used to find what are the valid

— candidate snapshots that could be selected for purge

DECLARE @purge_candidates TABLE (

snapshot_id INT NOT NULL,

snapshot_time DATETIME NOT NULL,

instance_name SYSNAME NOT NULL,

collection_set_uid UNIQUEIDENTIFIER NOT NULL )

— Find candidates that match the retention_days criteria (if specified)

IF ( @retention_days IS NULL )

BEGIN

— User did not specified a value for @retention_days, therfore we

— will use the default expiration day as marked in the source info

INSERT INTO @purge_candidates

SELECT s.snapshot_id,

s.snapshot_time,

s.instance_name,

s.collection_set_uid

FROM core.snapshots s

WHERE ( Getutcdate() >= s.valid_through )

END

ELSE

BEGIN

— User specified a value for @retention_days, we will use this overriden value

— when deciding what means old enough to qualify for purge this overrides

— the days_until_expiration value specified in the source_info_internal table

INSERT INTO @purge_candidates

SELECT s.snapshot_id,

s.snapshot_time,

s.instance_name,

s.collection_set_uid

FROM core.snapshots s

WHERE Getutcdate() >= Dateadd(DAY, @retention_days,

s.snapshot_time)

END

— Determine which is the oldest snapshot, from the list of candidates

DECLARE oldest_snapshot_cursor CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT p.snapshot_id,

p.instance_name,

p.collection_set_uid

FROM @purge_candidates p

WHERE ( ( @instance_name IS NULL )

OR ( p.instance_name = @instance_name ) )

AND ( ( @collection_set_uid IS NULL )

OR ( p.collection_set_uid = @collection_set_uid ) )

ORDER BY p.snapshot_time ASC

OPEN oldest_snapshot_cursor

DECLARE @stop_purge INT

DECLARE @oldest_snapshot_id INT

DECLARE @oldest_instance_name SYSNAME

DECLARE @oldest_collection_set_uid UNIQUEIDENTIFIER

FETCH NEXT FROM oldest_snapshot_cursor INTO @oldest_snapshot_id,

@oldest_instance_name, @oldest_collection_set_uid

— As long as there are snapshots that matched the time criteria

WHILE @@FETCH_STATUS = 0

BEGIN

— Filter out records that do not match the other filter crieria

IF ( ( @instance_name IS NULL )

OR ( @oldest_instance_name = @instance_name ) )

BEGIN

— There was no filter specified for instance_name or the instance matches the filter

IF ( ( @collection_set_uid IS NULL )

OR ( @oldest_collection_set_uid = @collection_set_uid )

)

BEGIN

— There was no filter specified for the collection_set_uid or the collection_set_uid matches the filter

BEGIN TRANSACTION tran_sp_purge_data

— Purge data associated with this snapshot. Note: deleting this snapshot

— triggers cascade delete in all warehouse tables based on the foreign key

— relationship to snapshots table

— Cascade cleanup of all data related referencing oldest snapshot

DELETE core.snapshots_internal

FROM core.snapshots_internal s

WHERE s.snapshot_id = @oldest_snapshot_id

COMMIT TRANSACTION tran_sp_purge_data

PRINT ‘Snapshot #’ + CONVERT(NVARCHAR(MAX),

@oldest_snapshot_id)

+

‘ purged.’;

END

END

— Check if the execution of the stored proc exceeded the @duration specified

IF ( @duration IS NOT NULL )

BEGIN

IF ( Getutcdate() >= @end_time )

BEGIN

PRINT ‘Stopping purge. More than ‘ +

CONVERT(NVARCHAR(MAX), @duration)

+

‘ minutes passed since the start of operation.’;

BREAK

END

END

— Check if somebody wanted to stop the purge operation

SELECT @stop_purge = COUNT(stop_purge)

FROM [core].[purge_info_internal]

IF ( @stop_purge > 0 )

BEGIN

PRINT ‘Stopping purge. Detected a user request to stop purge.’

;

BREAK

END

— Move to next oldest snapshot

FETCH NEXT FROM oldest_snapshot_cursor INTO @oldest_snapshot_id,

@oldest_instance_name, @oldest_collection_set_uid

END

CLOSE oldest_snapshot_cursor

DEALLOCATE oldest_snapshot_cursor

— Delete orphaned rows from snapshots.notable_query_plan.  Query plans are not deleted by the generic purge

— process that deletes other data (above) because query plan rows are not tied to a particular snapshot ID.

— Purging query plans table and the smaller query text table as a special case, by looking for plans that

— are no longer referenced by any of the rows in the snapshots.query_stats table.  We need to delete these

— rows in small chunks, since deleting many GB in a single delete statement would cause lock escalation and

— an explosion in the size of the transaction log (individual query plans can be 10-50MB).

DECLARE @delete_batch_size BIGINT;

DECLARE @rows_affected INT;

SET @delete_batch_size = 500;

SET @rows_affected = 500;

WHILE ( @rows_affected = @delete_batch_size )

BEGIN

DELETE TOP (@delete_batch_size) snapshots.notable_query_plan

FROM snapshots.notable_query_plan AS qp

WHERE NOT EXISTS (SELECT snapshot_id

FROM snapshots.query_stats AS qs

WHERE qs.[sql_handle] = qp.[sql_handle]

AND qs.plan_handle = qp.plan_handle

AND qs.plan_generation_num =

qp.plan_generation_num

AND qs.statement_start_offset =

qp.statement_start_offset

AND qs.statement_end_offset =

qp.statement_end_offset

AND qs.creation_time = qp.creation_time);

SET @rows_affected = @@ROWCOUNT;

IF( @rows_affected > 0 )

BEGIN

RAISERROR (

‘Deleted %d orphaned rows from snapshots.notable_query_plan’,

0,

1,

@rows_affected) WITH NOWAIT;

END

— Check if the execution of the stored proc exceeded the @duration specified

IF ( @duration IS NOT NULL )

BEGIN

IF ( Getutcdate() >= @end_time )

BEGIN

PRINT ‘Stopping purge. More than ‘ +

CONVERT(NVARCHAR(MAX), @duration)

+

‘ minutes passed since the start of operation.’;

BREAK

END

END

— Check if somebody wanted to stop the purge operation

SELECT @stop_purge = COUNT(stop_purge)

FROM [core].[purge_info_internal]

IF ( @stop_purge > 0 )

BEGIN

PRINT ‘Stopping purge. Detected a user request to stop purge.’

;

BREAK

END

END;

— Do the same purge process for query text rows in the snapshots.notable_query_text table.

SET @rows_affected = 500;

WHILE ( @rows_affected = @delete_batch_size )

BEGIN

DELETE TOP (@delete_batch_size) snapshots.notable_query_text

FROM snapshots.notable_query_text AS qt

WHERE NOT EXISTS (SELECT snapshot_id

FROM snapshots.query_stats AS qs

WHERE qs.[sql_handle] = qt.[sql_handle]);

SET @rows_affected = @@ROWCOUNT;

IF( @rows_affected > 0 )

BEGIN

RAISERROR (

‘Deleted %d orphaned rows from snapshots.notable_query_text’,

0,

1,

@rows_affected) WITH NOWAIT;

END

— Check if the execution of the stored proc exceeded the @duration specified

IF ( @duration IS NOT NULL )

BEGIN

IF ( Getutcdate() >= @end_time )

BEGIN

PRINT ‘Stopping purge. More than ‘ +

CONVERT(NVARCHAR(MAX), @duration)

+

‘ minutes passed since the start of operation.’;

BREAK

END

END

— Check if somebody wanted to stop the purge operation

SELECT @stop_purge = COUNT(stop_purge)

FROM [core].[purge_info_internal]

IF ( @stop_purge > 0 )

BEGIN

PRINT ‘Stopping purge. Detected a user request to stop purge.’

;

BREAK

END

END;

END