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
Leave a Reply