Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Performance issue with Data collection

Posted by Simon Cho on 04/06/2011

The new feature of Data collection has performance issue.

There is one job name as “mdw_purge_data_[db_datacollector]”

Basically, this procedure has 2 missing indexes.

And this job is for deleted orphaned records, so, it working with table scan for base table.

So, you should keep the small base table data.

That meaning is that setup the retention days as small as you can.!!

reference  : http://www.toddbaker.org/blog/2010/12/17/sql-2008-mdw-fixing-long-running-purges/

sp: exec core.sp_purge_data


USE [db_datacollector] 

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: