<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Script.StandardDatasetSupport" Accessibility="Public"> <Install>
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDataset' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.StandardDataset
END
GO
CREATE TABLE dbo.StandardDataset
(
DatasetId uniqueidentifier NOT NULL
,SchemaName sysname NOT NULL DEFAULT('dbo')
,DebugLevel tinyint NOT NULL DEFAULT(0)
,DefaultAggregationIntervalCount tinyint NOT NULL
,RawInsertTableCount tinyint NOT NULL DEFAULT(1)
,StagingProcessorStoredProcedureName sysname NULL
,BlockingMaintenanceDailyStartTime char(5) NOT NULL
,BlockingMaintenanceDurationMinutes int NOT NULL DEFAULT (60)
,LastOptimizationActionDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
,LastOptimizationActionSuccessfulCompletionDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
,CONSTRAINT PK_StandardDataset PRIMARY KEY CLUSTERED (DatasetId)
)
GO
EXEC DomainTableRegisterIndexOptimization 'StandardDataset'
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetAggregation' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.StandardDatasetAggregation
END
GO
CREATE TABLE dbo.StandardDatasetAggregation
(
DatasetId uniqueidentifier NOT NULL
,AggregationTypeId tinyint NOT NULL
,AggregationIntervalDurationMinutes int NULL
,AggregationStartDelayMinutes int NULL
,BuildAggregationStoredProcedureName sysname NULL
,DeleteAggregationStoredProcedureName sysname NULL
,GroomStoredProcedureName sysname NOT NULL
,IndexOptimizationIntervalMinutes int NOT NULL
,MaxDataAgeDays int NOT NULL
,GroomingIntervalMinutes int NOT NULL
,MaxRowsToGroom int NOT NULL
,LastGroomingDateTime smalldatetime NULL
,DataFileGroupName sysname NULL
,IndexFileGroupName sysname NULL
,StatisticsMaxAgeHours int NOT NULL DEFAULT(18)
,StatisticsUpdateSamplePercentage int NULL
,CONSTRAINT PK_StandardDatasetAggregation PRIMARY KEY CLUSTERED (DatasetId, AggregationTypeId)
)
GO
EXEC DomainTableRegisterIndexOptimization 'StandardDatasetAggregation'
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetAggregationHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.StandardDatasetAggregationHistory
END
GO
CREATE TABLE dbo.StandardDatasetAggregationHistory
(
StandardDatasetAggregationHistoryRowId bigint NOT NULL IDENTITY(1, 1)
,DatasetId uniqueidentifier NOT NULL
,AggregationTypeId tinyint NOT NULL
,AggregationDateTime smalldatetime NOT NULL
,DirtyInd bit NOT NULL DEFAULT (1)
,DataLastReceivedDateTime datetime NULL
,AggregationCount int NOT NULL DEFAULT (0)
,FirstAggregationStartDateTime datetime NULL
,FirstAggregationDurationSeconds int NULL
,LastAggregationStartDateTime datetime NULL
,LastAggregationDurationSeconds int NULL
EXEC DomainTableRegisterIndexOptimization 'StandardDatasetAggregationHistory'
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetAggregationStorage' AND TABLE_SCHEMA = 'dbo')
BEGIN
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_StandardDatasetAggregationStorageIndex_StandardDatasetAggregationStorage')
ALTER TABLE StandardDatasetAggregationStorageIndex DROP CONSTRAINT FK_StandardDatasetAggregationStorageIndex_StandardDatasetAggregationStorage
DROP TABLE dbo.StandardDatasetAggregationStorage
END
GO
CREATE TABLE dbo.StandardDatasetAggregationStorage
(
StandardDatasetAggregationStorageRowId int NOT NULL IDENTITY(1, 1)
,DatasetId uniqueidentifier NOT NULL
,AggregationTypeId tinyint NOT NULL
,BaseTableName nvarchar(90) NOT NULL
,TableTag nvarchar(50) NULL
,DependentTableInd tinyint NOT NULL DEFAULT(0)
,TableTemplate nvarchar(max) NOT NULL
,CoverViewSelectClause nvarchar(max) NOT NULL
,MaxTableRowCount int NULL
,MaxTableSizeKb int NULL
EXEC DomainTableRegisterIndexOptimization 'StandardDatasetAggregationStorage'
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetAggregationStorageIndex' AND TABLE_SCHEMA = 'dbo')
BEGIN
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_StandardDatasetOptimizationHistory_StandardDatasetAggregationStorageIndex')
ALTER TABLE StandardDatasetOptimizationHistory DROP CONSTRAINT FK_StandardDatasetOptimizationHistory_StandardDatasetAggregationStorageIndex
DROP TABLE dbo.StandardDatasetAggregationStorageIndex
END
GO
CREATE TABLE dbo.StandardDatasetAggregationStorageIndex
(
StandardDatasetAggregationStorageIndexRowId int NOT NULL IDENTITY(1, 1)
,StandardDatasetAggregationStorageRowId int NOT NULL
,PrimaryKeyInd bit NOT NULL DEFAULT (0)
,UniqueInd bit NOT NULL
,IndexGuid uniqueidentifier NULL
,IndexDefinition nvarchar(1000) NULL
,OnlineRebuildPossibleInd bit NULL
,OnlineRebuildLastPerformedDateTime datetime NULL
,CONSTRAINT PK_StandardDatasetAggregationStorageIndex PRIMARY KEY CLUSTERED (StandardDatasetAggregationStorageIndexRowId)
,CONSTRAINT UN_StandardDatasetAggregationStorageIndex_IndexGuid UNIQUE (StandardDatasetAggregationStorageRowId, IndexGuid)
,CONSTRAINT CHK_StandardDatasetAggregationStorageIndex_Index CHECK ((PrimaryKeyInd = 1 AND UniqueInd = 1 AND IndexGuid IS NULL AND IndexDefinition IS NULL) OR (PrimaryKeyInd = 0 AND IndexGuid IS NOT NULL AND IndexDefinition IS NOT NULL))
)
GO
EXEC DomainTableRegisterIndexOptimization 'StandardDatasetAggregationStorageIndex'
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetOptimizationHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.StandardDatasetOptimizationHistory
END
GO
CREATE TABLE dbo.StandardDatasetOptimizationHistory
(
StandardDatasetOptimizationHistoryRowId bigint NOT NULL IDENTITY(1, 1)
,StandardDatasetTableMapRowId bigint NOT NULL
,StandardDatasetAggregationStorageIndexRowId int NOT NULL
,OptimizationStartDateTime datetime NULL
,OptimizationDurationSeconds int NULL
,BeforeAvgFragmentationInPercent float NULL
,AfterAvgFragmentationInPercent float NULL
,OptimizationMethod varchar(30) NULL
,CreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,CONSTRAINT PK_StandardDatasetOptimizationHistory PRIMARY KEY CLUSTERED (StandardDatasetOptimizationHistoryRowId)
)
GO
CREATE INDEX IX_StandardDatasetOptimizationHistory_TableMapRowIdIndexRowIdDateTime ON StandardDatasetOptimizationHistory(StandardDatasetTableMapRowId, StandardDatasetAggregationStorageIndexRowId, CreatedDateTime)
GO
EXEC DomainTableRegisterIndexOptimization 'StandardDatasetOptimizationHistory'
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetStagingArea' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.StandardDatasetStagingArea
END
GO
CREATE TABLE dbo.StandardDatasetStagingArea
(
DatasetId uniqueidentifier NOT NULL
,StagingTableColumnDefinition nvarchar(max) NULL
,MaxRowsToProcessPerTransactionCount int NULL
,BatchedProcessingSupportedInd bit NOT NULL
,CONSTRAINT PK_StandardDatasetStagingArea PRIMARY KEY CLUSTERED (DatasetId)
)
GO
EXEC DomainTableRegisterIndexOptimization 'StandardDatasetStagingArea'
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetTableMap' AND TABLE_SCHEMA = 'dbo')
BEGIN
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_StandardDatasetOptimizationHistory_StandardDatasetTableMap')
ALTER TABLE StandardDatasetOptimizationHistory DROP CONSTRAINT FK_StandardDatasetOptimizationHistory_StandardDatasetTableMap
DROP TABLE dbo.StandardDatasetTableMap
END
GO
CREATE TABLE dbo.StandardDatasetTableMap
(
StandardDatasetTableMapRowId bigint NOT NULL IDENTITY (1, 1)
,DatasetId uniqueidentifier NOT NULL
,AggregationTypeId tinyint NOT NULL
,TableGuid uniqueidentifier NOT NULL
,TableNameSuffix AS (REPLACE(CAST(TableGuid AS varchar(50)), '-', ''))
,InsertInd bit NOT NULL DEFAULT (1)
,OptimizedInd bit NOT NULL DEFAULT (0)
,StartDateTime datetime NULL
,EndDateTime datetime NULL
GRANT SELECT, UPDATE ON StandardDatasetTableMap TO OpsMgrWriter
GO
EXEC DomainTableRegisterIndexOptimization 'StandardDatasetTableMap'
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_StandardDatasetAggregationStorageIndex_StandardDatasetAggregationStorage')
BEGIN
ALTER TABLE StandardDatasetAggregationStorageIndex
ADD CONSTRAINT FK_StandardDatasetAggregationStorageIndex_StandardDatasetAggregationStorage FOREIGN KEY (StandardDatasetAggregationStorageRowId)
REFERENCES StandardDatasetAggregationStorage(StandardDatasetAggregationStorageRowId)
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetAggregate
@DatasetId uniqueidentifier
,@AggregationTargetStartDateTime datetime = NULL
,@IntervalsToAggregate int = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@LockResourceName sysname
,@ExecResult int
,@LockSetInd bit
,@CurrentAggregationIntervalDurationMinutes int
,@AggregationIntervalCount int
,@DebugLevel tinyint
,@LastMidnightDateTime datetime
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
,@StandardDatasetAggregationHistoryRowId int
,@i int
,@j int
,@CurrentAggregationTypeId tinyint
,@AggregationStartDateTime datetime
,@TableGuid uniqueidentifier
,@InsertTableGuid uniqueidentifier
,@TableNameSuffix varchar(50)
,@AggregationExistedInd bit
,@Statement nvarchar(max)
,@RowsDeleted int
,@UtcOffsetMinutes int
SET @UtcOffsetMinutes = DATEDIFF(minute, GETDATE(), GETUTCDATE())
BEGIN TRY
-- set lock to make sure only one process
-- performs aggregations on this data set
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_Aggregate'
IF (@ExecResult < -1)
BEGIN
RAISERROR(777971001, 16, 1, 'StandardDatasetAggregation', @ExecResult)
END
-- lock timeout - some other process performing aggregations
IF (@ExecResult = -1)
BEGIN
RETURN
END
SET @LockSetInd = 1
SELECT
@AggregationIntervalCount = ISNULL(@IntervalsToAggregate, DefaultAggregationIntervalCount)
,@DebugLevel = DebugLevel
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
-- find lowest type aggregations we need to perform
SELECT @CurrentAggregationTypeId = MIN(AggregationTypeId)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > 0)
IF (@CurrentAggregationTypeId IS NULL)
BEGIN
-- dataset does not need aggregations
RETURN
END
SELECT @CurrentAggregationIntervalDurationMinutes = AggregationIntervalDurationMinutes
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
-- adjust parameters if needed
IF (@AggregationIntervalCount < 1)
SET @AggregationIntervalCount = 1
IF (@AggregationTargetStartDateTime IS NULL)
SET @AggregationTargetStartDateTime = GETUTCDATE()
-- find the last aggregation interval which ended before
-- the aggregation start date time
IF (@CurrentAggregationTypeId < 30)
BEGIN
SET @LastMidnightDateTime = CAST(CONVERT(char(8), @AggregationTargetStartDateTime, 112) AS datetime)
SET @IntervalStartDateTime = DATEADD(
minute
,ABS(DATEDIFF(minute, @LastMidnightDateTime, @AggregationTargetStartDateTime)) / @CurrentAggregationIntervalDurationMinutes * @CurrentAggregationIntervalDurationMinutes - @CurrentAggregationIntervalDurationMinutes
,CAST(CONVERT(char(8), @AggregationTargetStartDateTime, 112) AS datetime))
END
ELSE IF (@CurrentAggregationTypeId = 30)
BEGIN
SET @IntervalStartDateTime = DATEADD(minute, @UtcOffsetMinutes, CAST(CONVERT(char(8), GETDATE(), 112) AS datetime))
END
SET @i = @AggregationIntervalCount
DECLARE @Aggregation TABLE
(
AggregationTypeId int NOT NULL
,AggregationDateTime datetime NOT NULL
)
WHILE (@i > 0)
BEGIN
INSERT @Aggregation(AggregationTypeId, AggregationDateTime)
VALUES (@CurrentAggregationTypeId, @IntervalStartDateTime)
SET @i = @i - 1
SET @IntervalStartDateTime = DATEADD(minute, -@CurrentAggregationIntervalDurationMinutes, @IntervalStartDateTime)
END
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > @CurrentAggregationTypeId)
)
BEGIN
SELECT @CurrentAggregationTypeId = MIN(AggregationTypeId)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > @CurrentAggregationTypeId)
IF (@CurrentAggregationTypeId = 20) -- hourly
BEGIN
INSERT @Aggregation (
AggregationTypeId
,AggregationDateTime
)
SELECT DISTINCT
20
,DATEADD(minute, -DATEPART(minute, AggregationDateTime), AggregationDateTime)
FROM @Aggregation
END
IF (@CurrentAggregationTypeId = 30) -- daily
BEGIN
INSERT @Aggregation (
AggregationTypeId
,AggregationDateTime
)
SELECT DISTINCT
30
,AggregationDateTime
FROM @Aggregation
WHERE (ABS(DATEDIFF(minute, DATEADD(minute, -@UtcOffsetMinutes, AggregationDateTime), CONVERT(char(8), DATEADD(minute, -@UtcOffsetMinutes, AggregationDateTime), 112))) < 60)
END
END
-- groom aggregation history
DELETE StandardDatasetAggregationHistory
WHERE (AggregationDateTime < DATEADD(month, -1, GETDATE()))
INSERT StandardDatasetAggregationHistory (
DatasetId
,AggregationTypeId
,AggregationDateTime
)
SELECT
@DatasetId
,a.AggregationTypeId
,a.AggregationDateTime
FROM @Aggregation a
WHERE NOT EXISTS (SELECT * FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = a.AggregationTypeId)
AND (AggregationDateTime = a.AggregationDateTime)
)
AND (a.AggregationTypeId < 30)
-- account for daylight switching in the daily aggregations
INSERT StandardDatasetAggregationHistory (
DatasetId
,AggregationTypeId
,AggregationDateTime
)
SELECT
@DatasetId
,a.AggregationTypeId
,a.AggregationDateTime
FROM @Aggregation a
WHERE NOT EXISTS (SELECT * FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = a.AggregationTypeId)
AND (AggregationDateTime BETWEEN DATEADD(hour, -1, a.AggregationDateTime) AND DATEADD(hour, 1, a.AggregationDateTime))
)
AND (a.AggregationTypeId = 30)
-- select aggregation period to work on
-- first select oldest never aggregated interval
-- if none exist select oldest dirty interval
SELECT TOP 1
@StandardDatasetAggregationHistoryRowId = StandardDatasetAggregationHistoryRowId
,@CurrentAggregationTypeId = ah.AggregationTypeId
,@IntervalStartDateTime = ah.AggregationDateTime
,@IntervalEndDateTime = DATEADD(minute, a.AggregationIntervalDurationMinutes, ah.AggregationDateTime)
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON ((a.DatasetId = ah.DatasetId) AND (a.AggregationTypeId = ah.AggregationTypeId))
WHERE (ah.DatasetId = @DatasetId)
AND (DATEADD(minute, a.AggregationIntervalDurationMinutes + a.AggregationStartDelayMinutes, ah.AggregationDateTime) < GETUTCDATE()) -- aggregation interval + slight delay is in the past
--AND (DATEADD(minute, a.AggregationStartDelayMinutes, ISNULL(ah.DataLastReceivedDateTime, '20050101')) < GETUTCDATE()) -- data last received time + slight delay is in the past
AND (DirtyInd = 1)
AND (ah.AggregationCount = 0) -- interval was never aggregated
ORDER BY ah.AggregationTypeId ASC, ah.AggregationDateTime ASC
IF (@StandardDatasetAggregationHistoryRowId IS NULL)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationHistoryRowId = StandardDatasetAggregationHistoryRowId
,@CurrentAggregationTypeId = ah.AggregationTypeId
,@IntervalStartDateTime = ah.AggregationDateTime
,@IntervalEndDateTime = DATEADD(minute, a.AggregationIntervalDurationMinutes, ah.AggregationDateTime)
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON ((a.DatasetId = ah.DatasetId) AND (a.AggregationTypeId = ah.AggregationTypeId))
WHERE (ah.DatasetId = @DatasetId)
AND (DATEADD(minute, a.AggregationIntervalDurationMinutes + a.AggregationStartDelayMinutes, ah.AggregationDateTime) < GETUTCDATE()) -- aggregation interval + slight delay is in the past
AND (DATEADD(minute, a.AggregationStartDelayMinutes, ISNULL(ah.DataLastReceivedDateTime, DATEADD(minute, a.AggregationIntervalDurationMinutes, ah.AggregationDateTime))) < GETUTCDATE()) -- data last received time + slight delay is in the past
AND (DirtyInd = 1)
AND (NOT EXISTS (SELECT *
FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = ah.AggregationTypeId)
AND (DirtyInd = 1)
AND (AggregationDateTime < ah.AggregationDateTime)
)
) -- this is the oldest aggregation which is dirty
ORDER BY ah.AggregationTypeId ASC, ah.AggregationDateTime ASC
END
BEGIN TRAN
IF (@StandardDatasetAggregationHistoryRowId IS NOT NULL)
BEGIN
-- find and delete old aggregates if they exist
-- the aggregates may only reside in a single table
SET @j = -1
SET @InsertTableGuid = NULL
SET @AggregationExistedInd = 0
WHILE (EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId > @j)
AND (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (@IntervalStartDateTime BETWEEN ISNULL(StartDateTime, '19000101') AND ISNULL(EndDateTime, '99991231'))
)
)
BEGIN
SELECT TOP 1
@j = StandardDatasetTableMapRowId
,@TableNameSuffix = TableNameSuffix
,@TableGuid = TableGuid
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId > @j)
AND (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (@IntervalStartDateTime BETWEEN ISNULL(StartDateTime, '19000101') AND ISNULL(EndDateTime, '99991231'))
ORDER BY StandardDatasetTableMapRowId
SELECT @Statement = DeleteAggregationStoredProcedureName
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
IF (@RowsDeleted > 0)
BEGIN
SET @AggregationExistedInd = 1
SET @InsertTableGuid = @TableGuid
BREAK
END
END
IF (@InsertTableGuid IS NULL)
EXEC StandardDatasetGetInsertTableGuid @DatasetId, @CurrentAggregationTypeId, @InsertTableGuid OUTPUT
-- build new aggregates
SELECT @Statement = BuildAggregationStoredProcedureName
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
IF (@AggregationExistedInd = 1)
BEGIN
-- set table for optimization if required
UPDATE StandardDatasetTableMap
SET OptimizedInd = 0
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (TableGuid = @InsertTableGuid)
END
ELSE
BEGIN
-- reallocate storage if required
EXEC StandardDatasetAllocateStorage @DatasetId, @CurrentAggregationTypeId
END
-- update aggregation history
UPDATE StandardDatasetAggregationHistory
SET AggregationCount = AggregationCount + 1
,FirstAggregationStartDateTime = ISNULL(FirstAggregationStartDateTime, @AggregationStartDateTime)
,FirstAggregationDurationSeconds = ISNULL(FirstAggregationDurationSeconds, ABS(DATEDIFF(second, GETUTCDATE(), @AggregationStartDateTime)))
,LastAggregationStartDateTime = @AggregationStartDateTime
,LastAggregationDurationSeconds = ABS(DATEDIFF(second, GETUTCDATE(), @AggregationStartDateTime))
,DirtyInd =
CASE
WHEN EXISTS (SELECT *
FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (AggregationDateTime < @IntervalStartDateTime)
AND (DirtyInd = 1)
) -- still dirty if there is a prior dirty aggregation of the same level
THEN 1
WHEN EXISTS (SELECT *
FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId < @CurrentAggregationTypeId)
AND (AggregationDateTime >= @IntervalStartDateTime)
AND (AggregationDateTime < @IntervalEndDateTime)
AND (DirtyInd = 1)
) -- still dirty if there is dirty lower-level aggregation inside the interval
THEN 1
ELSE 0
END
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (AggregationDateTime = @IntervalStartDateTime)
END
COMMIT
IF (@LockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @LockResourceName
,@LockOwner = 'Session'
SET @LockSetInd = 0
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@LockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @LockResourceName
,@LockOwner = 'Session'
END
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to aggregate data for standard data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @DebugMessageText
END
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetAggregationSizeList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetAggregationSizeList AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetAggregationSizeList
AS
BEGIN
SET NOCOUNT ON
DECLARE @TableSize TABLE (
TableName sysname NOT NULL
,[RowCount] bigint NOT NULL
,Reserved varchar(30) NOT NULL
,Data varchar(30) NOT NULL
,IndexSize varchar(30) NOT NULL
,Unused varchar(30) NOT NULL
)
DECLARE @Result TABLE (
DatasetId uniqueidentifier NOT NULL
,ServerName sysname NOT NULL
,DatabaseName sysname NOT NULL
,DatasetName nvarchar(256) NOT NULL
,AggregationTypeId int NOT NULL
,AggregationTypeName nvarchar(50) NOT NULL
,MaxDataAgeDays int NOT NULL
,[RowCount] bigint NULL
,MinStartDateTime datetime NULL
,SizeKb bigint NOT NULL
,DailySizeKb float NULL
,DailyRowCount bigint NULL
,TotalSizeKb float NULL
,TotalRowCount bigint NULL
,DataFileGroupName sysname NOT NULL
,IndexFileGroupName sysname NOT NULL
)
SET @DatasetId = '00000000-0000-0000-0000-000000000000'
-- take all standard datasets one-by one
WHILE EXISTS (SELECT *
FROM vDataset d
JOIN StandardDataset sd ON (d.DatasetId = sd.DatasetId)
JOIN vMemberDatabase mdb ON (d.MemberDatabaseRowId = mdb.MemberDatabaseRowId)
WHERE (d.DatasetId > @DatasetId)
AND (d.InstallCompletedInd = 1)
)
BEGIN
SELECT TOP 1
@DatasetId = d.DatasetId
,@SchemaName = sd.SchemaName
,@DatasetName = d.DatasetDefaultName
,@DatasetDescription = d.DatasetDefaultDescription
,@ServerName = mdb.ServerName
,@DatabaseName = mdb.DatabaseName
FROM vDataset d
JOIN StandardDataset sd ON (d.DatasetId = sd.DatasetId)
JOIN vMemberDatabase mdb ON (d.MemberDatabaseRowId = mdb.MemberDatabaseRowId)
WHERE (d.DatasetId > @DatasetId)
AND (d.InstallCompletedInd = 1)
ORDER BY d.DatasetId
-- take each dataset aggregation one-by one
SET @AggregationTypeId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > @AggregationTypeId)
)
BEGIN
SELECT TOP 1
@AggregationTypeId = a.AggregationTypeId
,@AggregationTypeName = at.AggregationTypeDefaultName
,@MaxDataAgeDays = a.MaxDataAgeDays
,@DataFileGroupName = a.DataFileGroupName
,@IndexFileGroupName = a.IndexFileGroupName
FROM StandardDatasetAggregation a
JOIN vAggregationType at ON (a.AggregationTypeId = at.AggregationTypeId)
WHERE (a.DatasetId = @DatasetId)
AND (a.AggregationTypeId > @AggregationTypeId)
ORDER BY a.AggregationTypeId
SET @RowCount = 0
SET @SizeKb = 0
SET @TotalHours = 0
SET @MinStartDateTime = NULL
SET @RowCountForDailyAvg = 0
SET @SizeKbForDailyAvg = 0
-- calculate number of rows and size in Mb for the aggregation
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetTableMapRowId = StandardDatasetTableMapRowId
,@TableGuid = TableGuid
,@TableNameSuffix = TableNameSuffix
,@StartDateTime = StartDateTime
,@EndDateTime = EndDateTime
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
ORDER BY StandardDatasetTableMapRowId
-- walk through all storage tables for this "guid" and calculate there size
SET @StandardDatasetAggregationStorageRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationStorageRowId = StandardDatasetAggregationStorageRowId
,@DependentTableInd = DependentTableInd
,@BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
ORDER BY StandardDatasetAggregationStorageRowId
-- get table creation date in case data was wrong
-- about the date it was generated
SELECT @TableCreatedDateTime = create_date
FROM sys.objects o
JOIN sys.schemas s ON (o.schema_id = s.schema_id)
WHERE (s.name = @SchemaName)
AND (o.name = @BaseTableName + '_' + @TableNameSuffix)
IF (@StartDateTime < @TableCreatedDateTime)
SET @StartDateTime = @TableCreatedDateTime
IF (@EndDateTime > GETUTCDATE())
SET @EndDateTime = GETUTCDATE()
SELECT
@RowCount = @RowCount + CASE WHEN @DependentTableInd = 0 THEN [RowCount] ELSE 0 END
,@SizeKb = @SizeKb + CAST(REPLACE(REPLACE(Reserved, 'KB', ''), ' ', '') as bigint)
FROM @TableSize
IF (@StartDateTime IS NOT NULL) AND (@EndDateTime IS NOT NULL)
BEGIN
SET @TotalHours = @TotalHours + ABS(DATEDIFF(hour, @StartDateTime, @EndDateTime))
SELECT
@RowCountForDailyAvg = @RowCountForDailyAvg + CASE WHEN @DependentTableInd = 0 THEN [RowCount] ELSE 0 END
,@SizeKbForDailyAvg = @SizeKbForDailyAvg + CAST(REPLACE(REPLACE(Reserved, 'KB', ''), ' ', '') as bigint)
FROM @TableSize
SET @MinStartDateTime =
CASE
WHEN @MinStartDateTime IS NULL THEN @StartDateTime
WHEN @StartDateTime < @MinStartDateTime THEN @StartDateTime
ELSE @MinStartDateTime
END
SET @MaxEndDateTime =
CASE
WHEN @MaxEndDateTime IS NULL THEN @EndDateTime
WHEN @EndDateTime > @MaxEndDateTime THEN @EndDateTime
ELSE @MaxEndDateTime
END
END
END -- std ds aggregation storage loop
END -- std ds tbl map loop
SET @TotalHours = ABS(DATEDIFF(hour, @MinStartDateTime, @MaxEndDateTime))
INSERT @Result (
DatasetId
,ServerName
,DatabaseName
,DatasetName
,AggregationTypeId
,AggregationTypeName
,MaxDataAgeDays
,[RowCount]
,MinStartDateTime
,SizeKb
,DailyRowCount
,DailySizeKb
,DataFileGroupName
,IndexFileGroupName
)
SELECT
@DatasetId
,@ServerName
,@DatabaseName
,@DatasetName
,@AggregationTypeId
,@AggregationTypeName
,@MaxDataAgeDays
,@RowCount
,@MinStartDateTime
,@SizeKb
,ROUND(CASE WHEN @TotalHours > 0 THEN @RowCountForDailyAvg / CAST(@TotalHours AS float) * 24.0 ELSE NULL END, 0)
,CASE WHEN @TotalHours > 0 THEN @SizeKbForDailyAvg / CAST(@TotalHours AS float) * 24.0 ELSE NULL END
,ISNULL(@DataFileGroupName, 'default')
,ISNULL(@IndexFileGroupName, 'default')
END -- std ds aggregation loop
END -- std ds loop
-- add config / instance space data
-- loop through tables registered for optimization
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'MaintenanceSetting')
BEGIN
DELETE @TableSize
SET @DomainTableRowId = 0
WHILE EXISTS (SELECT *
FROM DomainTable
WHERE (DomainTableRowId > @DomainTableRowId)
)
BEGIN
SELECT TOP 1
@DomainTableRowId = DomainTableRowId
,@TableName = QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
FROM DomainTable
WHERE (DomainTableRowId > @DomainTableRowId)
ORDER BY DomainTableRowId
SELECT
ServerName
,DatabaseName
,DatasetId
,AggregationTypeId
,DatasetName
,AggregationTypeName
,MaxDataAgeDays
,[RowCount]
,MinStartDateTime
,SizeKb
,SizePercentOfTotalSize = CAST(SizeKb AS float) / (SELECT SUM(SizeKb) FROM @Result) * 100
,DailySizeKb as EstimatedAvgDailySizeKb
,DailyRowCount as EstimatedAvgDailyRowCount
,TotalSizeKb as EstimatedTotalSizeKb
,TotalRowCount as EstimatedTotalRowCount
,TotalSizePercent = CAST(TotalSizeKb AS float) / (SELECT SUM(TotalSizeKb) FROM @Result) * 100
,DataFileGroupName
,IndexFileGroupName
FROM @Result
ORDER BY DatasetName, AggregationTypeId
END
GO
GRANT EXECUTE ON dbo.StandardDatasetAggregationSizeList TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetAllocateStorage')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetAllocateStorage AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetAllocateStorage
@DatasetId uniqueidentifier
,@AggregationTypeId int
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
,@DebugLevel tinyint
SET @ErrorInd = 0
BEGIN TRY
BEGIN TRAN
DECLARE
@LockResourceName sysname
,@ExecResult int
,@DatasetIdString varchar(50)
-- lock table map to ensure only this transaction is granted rights to (re-)create insertion tables
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_TableMap'
IF (@ExecResult < -1)
BEGIN
RAISERROR(777971001, 16, 1, 'Update:StandardDatasetTableMap', @ExecResult)
END
IF (@ExecResult <> -1)
BEGIN
DECLARE
@ExpectedInsertTableCount int
,@ActualInsertTableCount int
,@MapRowId int
,@StorageRowId int
,@TableRowId int
,@BaseTableName sysname
,@TableNameSuffix varchar(50)
,@TableName sysname
,@TableGuid uniqueidentifier
,@MessageText nvarchar(max)
,@TableStatement nvarchar(max)
,@NewTableGuid uniqueidentifier
,@NewTableSuffix varchar(50)
,@MaxTableRowCount int
,@MaxTableSizeKb int
,@TableTemplate nvarchar(max)
,@IndexRowId bigint
,@IndexName sysname
,@UniqueInd bit
,@IndexDefinition nvarchar(1000)
,@IndexStatement nvarchar(2000)
,@IndexFileGroupName sysname
,@SchemaName sysname
DECLARE @TableSize TABLE (
TableRowId int NOT NULL IDENTITY(1, 1)
,TableName sysname NOT NULL
,[RowCount] bigint NOT NULL
,Reserved varchar(30) NOT NULL
,Data varchar(30) NOT NULL
,IndexSize varchar(30) NOT NULL
,Unused varchar(30) NOT NULL
,TableGuid uniqueidentifier NULL
,MaxTableRowCount int NULL
,MaxTableSizeKb int NULL
)
SELECT
@ExpectedInsertTableCount = CASE WHEN @AggregationTypeId > 0 THEN 1 ELSE RawInsertTableCount END
,@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SELECT @ActualInsertTableCount = COUNT(*)
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (InsertInd = 1)
SET @MapRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (InsertInd = 1)
AND (StandardDatasetTableMapRowId > @MapRowId)
)
BEGIN
SELECT TOP 1
@MapRowId = StandardDatasetTableMapRowId
,@TableNameSuffix = TableNameSuffix
,@TableGuid = TableGuid
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (InsertInd = 1)
AND (StandardDatasetTableMapRowId > @MapRowId)
ORDER BY StandardDatasetTableMapRowId
-- build list of tables to check size for
SET @StorageRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
)
BEGIN
SELECT TOP 1
@StorageRowId = StandardDatasetAggregationStorageRowId
,@BaseTableName = BaseTableName
,@MaxTableRowCount = MaxTableRowCount
,@MaxTableSizeKb = MaxTableSizeKb
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
UPDATE @TableSize
SET TableGuid = @TableGuid
,MaxTableRowCount = @MaxTableRowCount
,MaxTableSizeKb = @MaxTableSizeKb
WHERE TableRowId = @TableRowId
END
END
-- check if any of the tables is over the limit(s)
SET @TableGuid = NULL
SELECT TOP 1 @TableGuid = TableGuid
FROM @TableSize
WHERE (([RowCount] > MaxTableRowCount) OR (CAST(REPLACE(Data, 'KB', '') AS int) > MaxTableSizeKb))
DELETE @TableSize
WHERE TableGuid = @TableGuid
IF ((@TableGuid IS NOT NULL) OR (@ExpectedInsertTableCount > @ActualInsertTableCount))
BEGIN
-- re-allocate or create storage
IF ((@DebugLevel > 2) AND (@TableGuid IS NOT NULL))
BEGIN
SET @MessageText = 'Table set ' + CAST(@TableGuid AS varchar(50)) + ' aggregation type ' + CAST(@AggregationTypeId AS varchar(10)) + ' is full. Adding new table to the data set'
IF (@ExecResult < 0)
BEGIN
RAISERROR(777971001, 16, 1, 'Exclusive:StandardDatasetTableMap', @ExecResult)
END
WHILE ((@TableGuid IS NOT NULL) OR (@ExpectedInsertTableCount > @ActualInsertTableCount))
BEGIN
IF (@TableGuid IS NOT NULL)
BEGIN
-- no more inserts into this table
UPDATE StandardDatasetTableMap
SET InsertInd = 0
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (TableGuid = @TableGuid)
END
-- create new table(s)
SET @NewTableGuid = NEWID()
SET @NewTableSuffix = REPLACE(CAST(@NewTableGuid AS varchar(50)), '-', '')
SET @StorageRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
)
BEGIN
SELECT TOP 1
@StorageRowId = StandardDatasetAggregationStorageRowId
,@BaseTableName = BaseTableName
,@TableTemplate = TableTemplate
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
ORDER BY DependentTableInd, StandardDatasetAggregationStorageRowId
-- set correct file groups
SELECT @TableTemplate = REPLACE(@TableTemplate, '$DataFileGroupName$', QUOTENAME(ISNULL(a.DataFileGroupName, 'default')))
FROM StandardDatasetAggregation a
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
SELECT @TableTemplate = REPLACE(@TableTemplate, '$IndexFileGroupName$', QUOTENAME(ISNULL(a.IndexFileGroupName, 'default')))
FROM StandardDatasetAggregation a
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
-- set table name guid
SET @TableStatement = REPLACE(@TableTemplate, '$Guid$', @NewTableSuffix)
EXECUTE(@TableStatement)
-- create indexes
SELECT @IndexFileGroupName = ISNULL(IndexFileGroupName, 'default')
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
-- ensure PK is registered for optimization
IF NOT EXISTS (SELECT *
FROM StandardDatasetAggregationStorageIndex
WHERE (PrimaryKeyInd = 1)
AND (StandardDatasetAggregationStorageRowId = @StorageRowId)
)
AND (@TableStatement LIKE '%primary key%')
BEGIN
INSERT StandardDatasetAggregationStorageIndex (
StandardDatasetAggregationStorageRowId
,PrimaryKeyInd
,UniqueInd
)
VALUES (
@StorageRowId
,1
,1
)
END
SET @IndexRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorageIndex
WHERE (StandardDatasetAggregationStorageRowId = @StorageRowId)
AND (StandardDatasetAggregationStorageIndexRowId > @IndexRowId)
AND (PrimaryKeyInd = 0)
)
BEGIN
SELECT TOP 1
@IndexRowId = StandardDatasetAggregationStorageIndexRowId
,@IndexName = 'IX_CUSTOM_' + REPLACE(CAST(IndexGuid AS varchar(100)), '-', '')
,@UniqueInd = UniqueInd
,@IndexDefinition = IndexDefinition
FROM StandardDatasetAggregationStorageIndex
WHERE (StandardDatasetAggregationStorageRowId = @StorageRowId)
AND (StandardDatasetAggregationStorageIndexRowId > @IndexRowId)
AND (PrimaryKeyInd = 0)
SET @IndexStatement = 'CREATE ' + CASE @UniqueInd WHEN 1 THEN ' UNIQUE ' ELSE '' END + ' INDEX'
+ ' ' + QUOTENAME(@IndexName)
+ ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + @NewTableSuffix)
+ ' ' + REPLACE(@IndexDefinition, '''', '''''')
+ ' ON ' + QUOTENAME(@IndexFileGroupName)
EXECUTE (@IndexStatement)
-- indicate no optimization currently needed for this index
INSERT StandardDatasetOptimizationHistory (
StandardDatasetTableMapRowId
,StandardDatasetAggregationStorageIndexRowId
,OptimizationStartDateTime
,OptimizationDurationSeconds
)
VALUES (
@MapRowId
,@IndexRowId
,GETUTCDATE()
,0
)
END
END
SET @ActualInsertTableCount = @ActualInsertTableCount + 1
SET @TableGuid = NULL
SELECT TOP 1 @TableGuid = TableGuid
FROM @TableSize
WHERE (([RowCount] > MaxTableRowCount) OR (CAST(REPLACE(Data, 'KB', '') AS int) > MaxTableSizeKb))
END
EXEC StandardDatasetBuildCoverView @DatasetId, @AggregationTypeId
END
END
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to allocate storage for standard data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @DebugMessageText
END
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetBuildCoverView')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetBuildCoverView AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetBuildCoverView
@DatasetId uniqueidentifier
,@AggregationTypeId int
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
,@DebugLevel tinyint
SET @ErrorInd = 0
BEGIN TRY
BEGIN TRAN
DECLARE
@StorageRowId int
,@MapRowId int
,@BaseTableName sysname
,@ViewStatement nvarchar(max)
,@NeedUnionAll bit
,@CoverViewSelectClause nvarchar(max)
,@SchemaName sysname
SELECT @SchemaName = SchemaName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
SET @StorageRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
)
BEGIN
SELECT TOP 1
@StorageRowId = StandardDatasetAggregationStorageRowId
,@BaseTableName = BaseTableName
,@CoverViewSelectClause = CoverViewSelectClause
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
ORDER BY DependentTableInd, StandardDatasetAggregationStorageRowId
SET @MapRowId = -1
SET @NeedUnionAll = 0
SET @ViewStatement = 'IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = ''V'' AND uid = SCHEMA_ID(''' + REPLACE(@SchemaName, '''', '''''') + ''') AND name = ''v' + REPLACE(@BaseTableName, '''', '''''') + ''')'
+ ' BEGIN'
+ ' EXECUTE(''CREATE VIEW ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('v' + @BaseTableName) + ' AS SELECT A = 1'')'
+ ' EXECUTE(''GRANT SELECT ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('v' + @BaseTableName) + ' TO OpsMgrReader'')'
+ ' END'
EXECUTE (@ViewStatement)
SET @ViewStatement = 'ALTER VIEW ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('v' + @BaseTableName) + ' AS '
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetTableMapRowId > @MapRowId)
)
BEGIN
SELECT TOP 1
@MapRowId = StandardDatasetTableMapRowId
,@ViewStatement = @ViewStatement + CASE @NeedUnionAll WHEN 1 THEN ' UNION ALL ' ELSE '' END
+ @CoverViewSelectClause
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + REPLACE(CAST(TableGuid AS varchar(50)), '-', '')) + ' (NOLOCK) '
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetTableMapRowId > @MapRowId)
ORDER BY StandardDatasetTableMapRowId
SET @NeedUnionAll = 1
END
EXECUTE (@ViewStatement)
END
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to build cover view for standard data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @DebugMessageText
END
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetDelete
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
BEGIN TRAN
-- delete dataset member tables
DECLARE
@StandardDatasetTableMapRowId int
,@StandardDatasetAggregationStorageRowId int
,@SchemaName sysname
,@BaseTableName sysname
,@TableNameSuffix sysname
,@AggregationTypeId int
,@Statement nvarchar(1000)
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap m
JOIN StandardDataset d ON (m.DatasetId = d.DatasetId)
WHERE (m.DatasetId = @DatasetId)
AND (m.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId
,@AggregationTypeId = m.AggregationTypeId
,@TableNameSuffix = m.TableNameSuffix
,@SchemaName = d.SchemaName
FROM StandardDatasetTableMap m
JOIN StandardDataset d ON (m.DatasetId = d.DatasetId)
WHERE (m.DatasetId = @DatasetId)
AND (m.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
ORDER BY m.StandardDatasetTableMapRowId
-- drop all dependent tables for this aggregation type
-- then drop all "main" tables
DECLARE @i int
SET @i = 1
WHILE (@i >= 0)
BEGIN
SET @StandardDatasetAggregationStorageRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = @i)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationStorageRowId = StandardDatasetAggregationStorageRowId
,@BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = @i)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
ORDER BY StandardDatasetAggregationStorageRowId
SET @Statement =
'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''' + @BaseTableName + '_' + @TableNameSuffix + ''' AND TABLE_SCHEMA = ''' + @SchemaName + ''')'
+ ' DROP TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + @TableNameSuffix)
EXECUTE(@Statement)
-- drop cover view
SET @Statement =
'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ''' + 'v' + @BaseTableName + ''' AND TABLE_SCHEMA = ''' + @SchemaName + ''')'
+ ' DROP VIEW ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('v' + @BaseTableName)
EXECUTE(@Statement)
END
SET @i = @i - 1
END
END
DELETE StandardDatasetOptimizationHistory
WHERE StandardDatasetAggregationStorageIndexRowId IN (
SELECT StandardDatasetAggregationStorageIndexRowId
FROM StandardDatasetAggregationStorageIndex
WHERE StandardDatasetAggregationStorageRowId IN (SELECT StandardDatasetAggregationStorageRowId
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
)
)
DELETE StandardDatasetAggregationStorageIndex
WHERE StandardDatasetAggregationStorageRowId IN (SELECT StandardDatasetAggregationStorageRowId
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
)
DELETE StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
DELETE StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
DELETE StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
DELETE StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
DELETE StandardDatasetStagingArea
WHERE (DatasetId = @DatasetId)
DELETE StandardDataset
WHERE (DatasetId = @DatasetId)
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetGetInsertTableGuid')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetGetInsertTableGuid AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetGetInsertTableGuid
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@InsertTableGuid varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON
SELECT TOP(@TargetTableIndex) @InsertTableGuid = TableGuid
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (InsertInd = 1)
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetGroom
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@DebugLevel tinyint
,@DebugMessage nvarchar(max)
,@OperationDurationMs bigint
,@TableBaseName sysname
,@TableNameSuffix varchar(50)
,@AggregationTypeSuffix varchar(50)
,@BaseTableName sysname
,@TableName sysname
,@Statement nvarchar(max)
,@CurrentAggregationTypeId int
,@LockResourceName sysname
,@ExecResult int
,@LastGroomingDateTime smalldatetime
,@MaxRowsToGroom int
,@RawInsertTableCount int
,@InsertTableCount int
,@CutoffDateTime datetime
,@StorageRowId int
,@MapRowId int
,@TableGuid uniqueidentifier
,@GroomingStartedDateTime datetime
,@RowsDeleted int
,@SchemaName sysname
BEGIN TRY
BEGIN TRAN
-- set lock to make sure only one process
-- performs grooming on this data set
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_Groom'
IF (@ExecResult < -1)
BEGIN
RAISERROR(777971001, 16, 1, 'StandardDatasetGroom', @ExecResult)
END
IF (@ExecResult = -1)
BEGIN
COMMIT
RETURN
END
SELECT
@DebugLevel = DebugLevel
,@RawInsertTableCount = RawInsertTableCount
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SET @CurrentAggregationTypeId = -1
SELECT TOP 1
@CurrentAggregationTypeId = AggregationTypeId
,@MaxRowsToGroom = MaxRowsToGroom
,@CutoffDateTime = DATEADD(minute, ISNULL(AggregationIntervalDurationMinutes, 0),
DATEADD(day, -MaxDataAgeDays, GETUTCDATE()))
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > @CurrentAggregationTypeId)
AND (DATEADD(minute, GroomingIntervalMinutes, ISNULL(LastGroomingDateTime, '19000101')) < GETUTCDATE())
ORDER BY AggregationTypeId
IF (@CurrentAggregationTypeId <> -1)
BEGIN
-- no-raw aggregations may only have 1 insert table
SELECT @InsertTableCount =
CASE
WHEN @CurrentAggregationTypeId > 0 THEN 1
ELSE @RawInsertTableCount
END
SET @LastGroomingDateTime = GETUTCDATE()
IF (@InsertTableCount = (SELECT COUNT(*)
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId))
)
BEGIN
-- this aggregation only has insert tables - groom each of them
SET @MapRowId = -1
SELECT @Statement = GroomStoredProcedureName
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
WHILE (EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (InsertInd = 1)
AND (StandardDatasetTableMapRowId > @MapRowId))
)
BEGIN
SELECT TOP 1
@MapRowId = StandardDatasetTableMapRowId
,@TableGuid = TableGuid
,@TableNameSuffix = TableNameSuffix
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (InsertInd = 1)
AND (StandardDatasetTableMapRowId > @MapRowId)
ORDER BY StandardDatasetTableMapRowId
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting grooming of tables with suffix ' + @TableNameSuffix + ' aggregation type ' + CAST(@CurrentAggregationTypeId AS varchar(10)) + '. Method: delete rows'
SET @GroomingStartedDateTime = GETUTCDATE()
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished grooming of tables with suffix ' + @TableNameSuffix + ' aggregation type ' + CAST(@CurrentAggregationTypeId AS varchar(10))
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @GroomingStartedDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@OperationDurationMs = @OperationDurationMs
END
END
END
ELSE
BEGIN
-- run through optimized non-insert tables only
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (OptimizedInd = 1)
AND (InsertInd = 0)
AND (EndDateTime < @CutoffDateTime))
BEGIN
SELECT TOP 1 @TableNameSuffix = TableNameSuffix
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (OptimizedInd = 1)
AND (InsertInd = 0)
AND (EndDateTime < @CutoffDateTime)
ORDER BY StandardDatasetTableMapRowId
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting grooming of tables with suffix ' + @TableNameSuffix + '. Method: drop table'
SET @GroomingStartedDateTime = GETUTCDATE()
-- drop all dependent tables if any first
SET @StorageRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (DependentTableInd = 1)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
)
BEGIN
SELECT TOP 1
@BaseTableName = BaseTableName
,@StorageRowId = StandardDatasetAggregationStorageRowId
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
AND (DependentTableInd = 1)
ORDER BY StandardDatasetAggregationStorageRowId
SET @Statement = 'DROP TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + @TableNameSuffix)
EXECUTE (@Statement)
END
-- drop main table
SELECT @BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (DependentTableInd = 0)
-- delete table map record
DELETE StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (TableNameSuffix = @TableNameSuffix)
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished grooming of tables with suffix ' + @TableNameSuffix
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @GroomingStartedDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@OperationDurationMs = @OperationDurationMs
END
END
END
UPDATE StandardDatasetAggregation
SET LastGroomingDateTime = @LastGroomingDateTime
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
EXEC StandardDatasetBuildCoverView
@DatasetId = @DatasetId
,@AggregationTypeId = @CurrentAggregationTypeId
END
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to groom data for standard data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @DebugMessageText
END
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetIndexDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetIndexDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetIndexDelete
@DatasetId uniqueidentifier
,@IndexGuid uniqueidentifier
,@DeleteIndexOnExistingTablesInd bit
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
BEGIN TRAN
DECLARE
@BaseTableName sysname
,@StandardDatasetAggregationStorageRowId int
,@AggregationTypeId int
,@StandardDatasetTableMapRowId int
,@TableNameSuffix varchar(50)
,@Statement nvarchar(1000)
,@IndexId int
,@IndexName sysname
,@TableName sysname
,@SchemaName sysname
SELECT @SchemaName = SchemaName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
SELECT
@BaseTableName = BaseTableName
,@StandardDatasetAggregationStorageRowId = s.StandardDatasetAggregationStorageRowId
,@AggregationTypeId = s.AggregationTypeId
FROM StandardDatasetAggregationStorageIndex i
JOIN StandardDatasetAggregationStorage s ON (s.StandardDatasetAggregationStorageRowId = i.StandardDatasetAggregationStorageRowId)
WHERE (i.IndexGuid = @IndexGuid)
AND (s.DatasetId = @DatasetId)
IF (@BaseTableName IS NOT NULL)
BEGIN
DELETE StandardDatasetAggregationStorageIndex
WHERE (IndexGuid = @IndexGuid)
AND (StandardDatasetAggregationStorageRowId = @StandardDatasetAggregationStorageRowId)
END
IF (@DeleteIndexOnExistingTablesInd = 1)
BEGIN
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
)
BEGIN
SELECT TOP 1
@StandardDatasetTableMapRowId = StandardDatasetTableMapRowId
,@TableNameSuffix = TableNameSuffix
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
ORDER BY StandardDatasetTableMapRowId
SET @IndexId = NULL
SET @IndexName = 'IX_CUSTOM_' + REPLACE(CAST(@IndexGuid AS varchar(100)), '-', '')
SET @TableName = @BaseTableName + '_' + @TableNameSuffix
SELECT @IndexId = indid
FROM sysindexes
WHERE ([name] = @IndexName)
AND (id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)))
IF (@IndexId IS NOT NULL)
BEGIN
SET @Statement = 'DROP INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
EXECUTE (@Statement)
END
END
END
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetIndexInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetIndexInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetIndexInsert
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@DependentTableInd tinyint
,@TableTag nvarchar(50)
,@UniqueInd bit
,@IndexGuid uniqueidentifier
,@IndexDefinition nvarchar(1000)
,@CreateIndexOnExistingTablesInd bit
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
BEGIN TRAN
DECLARE
@BaseTableName sysname
,@StandardDatasetAggregationStorageRowId int
,@Statement nvarchar(2000)
,@StandardDatasetTableMapRowId int
,@TableNameSuffix varchar(50)
,@IndexFileGroupName sysname
,@StandardDatasetAggregationStorageIndexRowId int
SELECT @SchemaName = SchemaName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
SELECT
@StandardDatasetAggregationStorageRowId = StandardDatasetAggregationStorageRowId
,@BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = @DependentTableInd)
AND ((TableTag IS NULL) AND (@TableTag IS NULL))
OR
(TableTag = @TableTag)
-- check if table was located
IF (@StandardDatasetAggregationStorageRowId IS NULL)
BEGIN
SELECT
@DatasetIdString = CAST(@DatasetId AS varchar(100))
,@TableTagString = CASE WHEN @TableTag IS NULL THEN '<NULL>' ELSE @TableTag END
RAISERROR (777971050, 16, 1
,@DatasetIdString
,@AggregationTypeId
,@DependentTableInd
,@TableTagString)
END
-- check if index already exists
IF EXISTS (SELECT *
FROM StandardDatasetAggregationStorageIndex
WHERE (StandardDatasetAggregationStorageRowId = @StandardDatasetAggregationStorageRowId)
AND (IndexGuid = @IndexGuid)
)
BEGIN
SELECT
@DatasetIdString = CAST(@DatasetId AS varchar(100))
,@TableTagString = CASE WHEN @TableTag IS NULL THEN '<NULL>' ELSE @TableTag END
,@IndexGuidString = CAST(@IndexGuid AS varchar(100))
RAISERROR (777971051, 16, 1
,@IndexGuidString
,@DatasetIdString
,@AggregationTypeId
,@DependentTableInd
,@TableTagString)
END
SELECT @IndexFileGroupName = ISNULL(IndexFileGroupName, 'default')
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
SET @StandardDatasetAggregationStorageIndexRowId = @@IDENTITY
IF (@CreateIndexOnExistingTablesInd = 1)
BEGIN
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
)
BEGIN
SELECT TOP 1
@StandardDatasetTableMapRowId = StandardDatasetTableMapRowId
,@TableNameSuffix = TableNameSuffix
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
ORDER BY StandardDatasetTableMapRowId
SET @Statement = 'CREATE ' + CASE @UniqueInd WHEN 1 THEN 'UNIQUE' ELSE '' END
+ ' INDEX [IX_CUSTOM_' + REPLACE(CAST(@IndexGuid AS varchar(100)), '-', '') + ']'
+ ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + @TableNameSuffix)
+ REPLACE(@IndexDefinition, '''', '''''')
+ ' ON ' + QUOTENAME(@IndexFileGroupName)
EXECUTE (@Statement)
-- indicate no optimization is nesessary on this index
INSERT StandardDatasetOptimizationHistory (
StandardDatasetTableMapRowId
,StandardDatasetAggregationStorageIndexRowId
,OptimizationStartDateTime
,OptimizationDurationSeconds
)
VALUES (
@StandardDatasetTableMapRowId
,@StandardDatasetAggregationStorageIndexRowId
,GETUTCDATE()
,0
)
END
END
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetMaintenance')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetMaintenance AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetMaintenance
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@LockResourceName sysname
,@ExecResult int
,@LockSetInd bit
,@StartDateTime datetime
,@MaintenanceWorkAttemptedInd bit
,@LastOptimizationActionSuccessfulCompletionDateTime datetime
SET @StartDateTime = GETUTCDATE()
SET @MaintenanceWorkAttemptedInd = 0
SELECT @LastOptimizationActionSuccessfulCompletionDateTime = LastOptimizationActionSuccessfulCompletionDateTime
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- process staging area on every call
EXEC StandardDatasetProcessStaging @DatasetId
-- do optimization no more frequently then every 60 seconds
IF EXISTS (SELECT *
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
AND (DATEDIFF(second, LastOptimizationActionDateTime, GETUTCDATE()) > 20)
)
BEGIN
BEGIN TRY
-- set lock to make sure only one process
-- performs aggregations on this data set
SET @LockResourceName = /*CAST(@DatasetId AS varchar(50)) + */'Dataset_Maintenance'
IF (@ExecResult < -1)
BEGIN
RAISERROR(777971001, 16, 1, 'StandardDatasetMaintenance', @ExecResult)
END
-- lock timeout - some other process performing aggregations
IF (@ExecResult = -1)
BEGIN
RETURN
END
SET @LockSetInd = 1
UPDATE StandardDataset
SET LastOptimizationActionDateTime = GETUTCDATE()
WHERE (DatasetId = @DatasetId)
AND (DATEDIFF(second, LastOptimizationActionDateTime, GETUTCDATE()) > 20)
IF (@@ROWCOUNT > 0)
BEGIN
IF (ABS(DATEDIFF(second, @StartDateTime, GETUTCDATE())) < 30)
BEGIN
EXEC StandardDatasetGroom @DatasetId
EXEC StandardDatasetAllocateStorage @DatasetId, 0
IF (ABS(DATEDIFF(second, @StartDateTime, GETUTCDATE())) < 30)
BEGIN
EXEC StandardDatasetOptimize @DatasetId
IF (ABS(DATEDIFF(second, @StartDateTime, GETUTCDATE())) < 30)
BEGIN
EXEC StandardDatasetAggregate @DatasetId
END
END
END
END
-- we sucessfully tried to do maintenance work beyond staging area processint
-- we'll report that to the caller
SET @MaintenanceWorkAttemptedInd = 1
UPDATE StandardDataset
SET @LastOptimizationActionSuccessfulCompletionDateTime = LastOptimizationActionSuccessfulCompletionDateTime = GETUTCDATE()
WHERE DatasetId = @DatasetId
IF (@LockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @LockResourceName
,@LockOwner = 'Session'
SET @LockSetInd = 0
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@LockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @LockResourceName
,@LockOwner = 'Session'
END
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END CATCH
END
-- return information to caller
SELECT
MaintenanceWorkAttemptedInd = @MaintenanceWorkAttemptedInd
,LastOptimizationActionSuccessfulCompletionDateTime = @LastOptimizationActionSuccessfulCompletionDateTime
END
GO
GRANT EXECUTE ON StandardDatasetMaintenance TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetOptimize')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetOptimize AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetOptimize
@DatasetId uniqueidentifier
,@MinAvgFragmentationInPercentToOptimize int = 10
,@MinAvgFragmentationInPercentToRebuild int = 30
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@DebugLevel tinyint
,@DebugMessage nvarchar(max)
,@StandardDatasetTableMapRowId int
,@TableNameSuffix varchar(50)
,@StandardDatasetAggregationStorageRowId int
,@AggregationTypeId int
,@FoundNonoptimalIndexInd bit
,@TableName sysname
,@TableNameWithSchema nvarchar(1000)
,@IndexName sysname
,@PrimaryKeyInd bit
,@AvgFragmentationInPercent float
,@IndexPageCount bigint
,@StandardDatasetOptimizationHistoryRowId bigint
,@OnlineRebuildPossibleInd bit
,@CanPerformBlockingOptimizationInd bit
,@OptimizationStartDateTime datetime
,@IndexOptimized bit
,@IndexRebuild bit
,@InsertInd bit
,@OperationDurationMs bigint
,@MainTableName sysname
,@Statement nvarchar(max)
,@EffectiveStatement nvarchar(max)
,@StandardDatasetAggregationStorageIndexRowId int
,@IndexId int
,@AfterAvgFragmentationInPercent float
,@OnlineRebuildInd bit
,@SchemaName nvarchar(256)
,@BlockingMaintenanceStartTime char(5)
,@BlockingMaintenanceDurationMinutes int
,@StatisticsMaxAgeHours int
,@StatisticsUpdateSamplePercentage int
,@StatisticsUpdateStartDateTime datetime
,@StatisticsUpdateDurationSeconds int
,@StatisticUpdatedInd bit
,@StatisticsUpdateMethod varchar(50)
,@LockResourceName sysname
,@ExecResult int
,@LockSetInd bit
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
,@BlockingMaintenanceStartTime = BlockingMaintenanceDailyStartTime
,@BlockingMaintenanceDurationMinutes = BlockingMaintenanceDurationMinutes
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- set lock to make sure only one process
-- performs optimization on this data set
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_Optimize'
--********************************************************
-- Insert new optimization work items
-- groom optimization history
DELETE StandardDatasetOptimizationHistory
WHERE (OptimizationDurationSeconds IS NOT NULL)
AND (OptimizationStartDateTime < DATEADD(day, -7, GETUTCDATE()))
-- add indexes for optimization
INSERT StandardDatasetOptimizationHistory (StandardDatasetTableMapRowId, StandardDatasetAggregationStorageIndexRowId)
SELECT m.StandardDatasetTableMapRowId, i.StandardDatasetAggregationStorageIndexRowId
FROM StandardDatasetTableMap m
JOIN StandardDatasetAggregationStorage s ON (m.DatasetId = s.DatasetId) AND (m.AggregationTypeId = s.AggregationTypeId)
JOIN StandardDatasetAggregationStorageIndex i ON (s.StandardDatasetAggregationStorageRowId = i.StandardDatasetAggregationStorageRowId)
JOIN StandardDatasetAggregation a ON (m.DatasetId = a.DatasetId) AND (m.AggregationTypeId = a.AggregationTypeId)
WHERE (m.DatasetId = @DatasetId)
AND ((m.OptimizedInd = 0) OR (m.InsertInd = 1))
AND (NOT EXISTS (SELECT *
FROM StandardDatasetOptimizationHistory
WHERE (StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId)
AND (StandardDatasetAggregationStorageIndexRowId = i.StandardDatasetAggregationStorageIndexRowId)
AND ((OptimizationDurationSeconds IS NULL)
OR
(OptimizationStartDateTime > DATEADD(minute, -a.IndexOptimizationIntervalMinutes, GETUTCDATE()))
)
)
)
--********************************************************
-- set "optimized" property on optimized tables
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap m
JOIN StandardDatasetAggregationStorage s ON (m.DatasetId = s.DatasetId) AND (m.AggregationTypeId = s.AggregationTypeId)
LEFT JOIN StandardDatasetAggregationStorageIndex i ON (s.StandardDatasetAggregationStorageRowId = i.StandardDatasetAggregationStorageRowId)
WHERE (m.DatasetId = @DatasetId)
AND (m.OptimizedInd = 0)
AND (m.InsertInd = 0)
AND (m.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (NOT EXISTS (SELECT *
FROM StandardDatasetOptimizationHistory
WHERE (StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId)
AND (StandardDatasetAggregationStorageIndexRowId = i.StandardDatasetAggregationStorageIndexRowId)
AND (OptimizationStartDateTime IS NULL)
)
)
)
BEGIN
SELECT TOP 1
@StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId
,@TableNameSuffix = m.TableNameSuffix
,@AggregationTypeId = m.AggregationTypeId
,@InsertInd = m.InsertInd
,@StatisticsMaxAgeHours = a.StatisticsMaxAgeHours
FROM StandardDatasetTableMap m
JOIN StandardDatasetAggregationStorage s ON (m.DatasetId = s.DatasetId) AND (m.AggregationTypeId = s.AggregationTypeId)
JOIN StandardDatasetAggregation a ON (m.DatasetId = a.DatasetId) AND (m.AggregationTypeId = a.AggregationTypeId)
LEFT JOIN StandardDatasetAggregationStorageIndex i ON (s.StandardDatasetAggregationStorageRowId = i.StandardDatasetAggregationStorageRowId)
WHERE (m.DatasetId = @DatasetId)
AND (m.OptimizedInd = 0)
AND (m.InsertInd = 0)
AND (m.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (NOT EXISTS (SELECT *
FROM StandardDatasetOptimizationHistory
WHERE (StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId)
AND (StandardDatasetAggregationStorageIndexRowId = i.StandardDatasetAggregationStorageIndexRowId)
AND (OptimizationStartDateTime IS NULL)
)
)
ORDER BY m.StandardDatasetTableMapRowId
-- scroll through storage tables
SET @StandardDatasetAggregationStorageRowId = 0
SET @FoundNonoptimalIndexInd = 0
SET @MainTableName = NULL
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationStorageRowId = StandardDatasetAggregationStorageRowId
,@TableName = BaseTableName + '_' + @TableNameSuffix
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
ORDER BY DependentTableInd, StandardDatasetAggregationStorageRowId
IF (@MainTableName IS NULL)
SET @MainTableName = @TableName
-- run through all registered indexes to see if they need reorg or rebuild
SET @StandardDatasetAggregationStorageIndexRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorageIndex
WHERE (StandardDatasetAggregationStorageRowId = @StandardDatasetAggregationStorageRowId)
AND (StandardDatasetAggregationStorageIndexRowId > @StandardDatasetAggregationStorageIndexRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationStorageIndexRowId = StandardDatasetAggregationStorageIndexRowId
,@IndexName = 'IX_CUSTOM_' + REPLACE(CAST(IndexGuid AS varchar(100)), '-', '')
,@PrimaryKeyInd = PrimaryKeyInd
FROM StandardDatasetAggregationStorageIndex
WHERE (StandardDatasetAggregationStorageRowId = @StandardDatasetAggregationStorageRowId)
AND (StandardDatasetAggregationStorageIndexRowId > @StandardDatasetAggregationStorageIndexRowId)
ORDER BY StandardDatasetAggregationStorageIndexRowId
SET @IndexId = NULL
SET @TableNameWithSchema = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
IF (@PrimaryKeyInd = 1)
BEGIN
SET @IndexId = 1
END
ELSE
BEGIN
SELECT @IndexId = indid
FROM sysindexes
WHERE ([name] = @IndexName)
AND (id = OBJECT_ID(@TableNameWithSchema))
END
IF (@IndexId IS NOT NULL)
BEGIN
SELECT
@AvgFragmentationInPercent = avg_fragmentation_in_percent
,@IndexPageCount = page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@TableNameWithSchema), @IndexId, NULL, NULL)
WHERE (alloc_unit_type_desc = 'IN_ROW_DATA') -- exclude blobs
-- do not consider index for optimization
-- we it only has 1 page of data
IF (@IndexPageCount > 1)
BEGIN
IF (@AvgFragmentationInPercent >= @MinAvgFragmentationInPercentToOptimize)
BEGIN
-- check to see if we optimized this index
-- within last 2 optimization intervals and
-- it did not reduce fragmentation
IF (NOT EXISTS (SELECT *
FROM StandardDatasetOptimizationHistory oh
JOIN StandardDatasetTableMap m ON (oh.StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId)
JOIN StandardDatasetAggregation a ON (m.DatasetId = a.DatasetId) AND (m.AggregationTypeId = a.AggregationTypeId)
WHERE (oh.StandardDatasetTableMapRowId = @StandardDatasetTableMapRowId)
AND (oh.StandardDatasetAggregationStorageIndexRowId = @StandardDatasetAggregationStorageIndexRowId)
AND (oh.OptimizationStartDateTime > DATEADD(minute, -2 * a.IndexOptimizationIntervalMinutes, GETUTCDATE()))
AND (oh.AfterAvgFragmentationInPercent >= oh.BeforeAvgFragmentationInPercent)
AND (oh.StandardDatasetOptimizationHistoryRowId = (SELECT TOP 1 StandardDatasetOptimizationHistoryRowId
FROM StandardDatasetOptimizationHistory
WHERE (StandardDatasetTableMapRowId = @StandardDatasetTableMapRowId)
AND (StandardDatasetAggregationStorageIndexRowId = @StandardDatasetAggregationStorageIndexRowId)
AND (OptimizationStartDateTime IS NOT NULL)
ORDER BY CreatedDateTime DESC
))
)
)
BEGIN
SET @FoundNonoptimalIndexInd = 1
BREAK
END
END
-- check stats update date
IF (STATS_DATE(OBJECT_ID(@TableNameWithSchema), @IndexId) IS NULL)
OR
(STATS_DATE(OBJECT_ID(@TableNameWithSchema), @IndexId) < DATEADD(hour, -@StatisticsMaxAgeHours, GETDATE()))
BEGIN
SET @FoundNonoptimalIndexInd = 1
BREAK
END
END
END
END
IF (@FoundNonoptimalIndexInd = 1) BREAK
END
IF (@FoundNonoptimalIndexInd = 0) AND (@InsertInd = 0)
BEGIN
SET @Statement =
'UPDATE StandardDatasetTableMap'
+ ' SET OptimizedInd = 1'
+ ' ,StartDateTime = ISNULL((SELECT MIN([DateTime]) FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName) + ' (TABLOCK)), DATEADD(month, -1, GETUTCDATE()))'
+ ' ,EndDateTime = ISNULL((SELECT MAX([DateTime]) FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName) + ' (TABLOCK)), DATEADD(month, -1, GETUTCDATE()))'
+ ' WHERE (StandardDatasetTableMapRowId = ' + CAST (@StandardDatasetTableMapRowId AS varchar(15)) + ')'
EXECUTE(@Statement)
-- remove optimized partitions where Start/End times are NULL
-- indicating we ran into a bug where partitions with 0 records
-- when optimized are left with NULL in Start/End times
SET @Statement =
'UPDATE StandardDatasetTableMap'
+ ' SET StartDateTime = DATEADD(month, -1, GETUTCDATE())'
+ ' ,EndDateTime = DATEADD(month, -1, GETUTCDATE())'
+ ' WHERE (OptimizedInd = 1)'
+ ' AND (InsertInd = 0)'
+ ' AND (StartDateTime IS NULL)'
+ ' AND (EndDateTime IS NULL)'
EXECUTE(@Statement)
-- create check constraint on the table to
-- ensure optimizer can use it
DECLARE
@TableStartDateTime datetime
,@TableEndDateTime datetime
SELECT
@TableStartDateTime = StartDateTime
,@TableEndDateTime = EndDateTime
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId = @StandardDatasetTableMapRowId)
SET @Statement = ' IF EXISTS (SELECT * FROM sys.check_constraints WHERE name = ''CHK_DateTime_' + @MainTableName + ''' AND parent_object_id = OBJECT_ID(''' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName) + '''))'
+ ' ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName)
+ ' DROP CONSTRAINT ' + QUOTENAME('CHK_DateTime_' + @MainTableName)
+ ' ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName) + ' WITH NOCHECK'
+ ' ADD CONSTRAINT ' + QUOTENAME('CHK_DateTime_' + @MainTableName) + ' CHECK ([DateTime] BETWEEN CONVERT(datetime, ''' + CONVERT(varchar(100), @TableStartDateTime, 121) + ''', 121)'
+ ' AND CONVERT(datetime, ''' + CONVERT(varchar(100), @TableEndDateTime, 121) + ''', 121))'
EXECUTE (@Statement)
-- end date may be far in the future
-- due to multiple reasons
-- set table map date to "today" if
-- it is far in the future, but leave
-- constraints "real"
IF (@TableEndDateTime > DATEADD(month, 1, GETUTCDATE()))
BEGIN
UPDATE StandardDatasetTableMap
SET EndDateTime = GETUTCDATE()
WHERE (StandardDatasetTableMapRowId = @StandardDatasetTableMapRowId)
END
END
END
--********************************************************
-- Optimize next index in queue
SET @StandardDatasetOptimizationHistoryRowId = 0
SET @IndexOptimized = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetOptimizationHistory h
JOIN StandardDatasetAggregationStorageIndex i ON (h.StandardDatasetAggregationStorageIndexRowId = i.StandardDatasetAggregationStorageIndexRowId)
JOIN StandardDatasetAggregationStorage s ON (s.StandardDatasetAggregationStorageRowId = i.StandardDatasetAggregationStorageRowId)
JOIN StandardDatasetTableMap m ON (s.DatasetId = m.DatasetId) AND (s.AggregationTypeId = m.AggregationTypeId)
WHERE (m.DatasetId = @DatasetId)
AND (h.StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId)
AND (h.OptimizationStartDateTime IS NULL)
AND (StandardDatasetOptimizationHistoryRowId > @StandardDatasetOptimizationHistoryRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetOptimizationHistoryRowId = h.StandardDatasetOptimizationHistoryRowId
,@StandardDatasetAggregationStorageIndexRowId = h.StandardDatasetAggregationStorageIndexRowId
,@TableName = s.BaseTableName + '_' + m.TableNameSuffix
,@InsertInd = m.InsertInd
,@IndexName = 'IX_CUSTOM_' + REPLACE(CAST(i.IndexGuid AS varchar(100)), '-', '')
,@PrimaryKeyInd = i.PrimaryKeyInd
,@StatisticsMaxAgeHours = a.StatisticsMaxAgeHours
,@StatisticsUpdateSamplePercentage = a.StatisticsUpdateSamplePercentage
,@OnlineRebuildPossibleInd =
CASE
WHEN ISNULL(i.OnlineRebuildPossibleInd, 1) = 1 THEN 1
WHEN DATEADD(day, 1, ISNULL(i.OnlineRebuildLastPerformedDateTime, '19000101')) < GETUTCDATE() THEN 1
ELSE 0
END
,@CanPerformBlockingOptimizationInd =
CASE
WHEN (DATEDIFF(minute, CONVERT(char(8), GETDATE(), 112) + ' ' + d.BlockingMaintenanceDailyStartTime, GETDATE()) < d.BlockingMaintenanceDurationMinutes)
THEN 1
ELSE 0
END
FROM StandardDatasetOptimizationHistory h
JOIN StandardDatasetAggregationStorageIndex i ON (h.StandardDatasetAggregationStorageIndexRowId = i.StandardDatasetAggregationStorageIndexRowId)
JOIN StandardDatasetAggregationStorage s ON (s.StandardDatasetAggregationStorageRowId = i.StandardDatasetAggregationStorageRowId)
JOIN StandardDatasetTableMap m ON (s.DatasetId = m.DatasetId) AND (s.AggregationTypeId = m.AggregationTypeId)
JOIN StandardDatasetAggregation a ON (s.DatasetId = a.DatasetId) AND (s.AggregationTypeId = a.AggregationTypeId)
JOIN StandardDataset d ON (d.DatasetId = m.DatasetId)
WHERE (m.DatasetId = @DatasetId)
AND (h.StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId)
AND (h.OptimizationStartDateTime IS NULL)
AND (StandardDatasetOptimizationHistoryRowId > @StandardDatasetOptimizationHistoryRowId)
ORDER BY h.CreatedDateTime
SET @IndexId = NULL
SET @TableNameWithSchema = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
IF (@PrimaryKeyInd = 1)
BEGIN
SELECT @IndexId = 1
SELECT @IndexName = i.name
FROM sys.indexes i
WHERE (index_id = 1)
AND (object_id = OBJECT_ID(@TableNameWithSchema))
END
ELSE
BEGIN
SELECT @IndexId = indid
FROM sysindexes
WHERE ([name] = @IndexName)
AND (id = OBJECT_ID(@TableNameWithSchema))
END
IF (@IndexId IS NOT NULL)
BEGIN
SELECT @AvgFragmentationInPercent = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@TableNameWithSchema), @IndexId, NULL, NULL)
WHERE (alloc_unit_type_desc = 'IN_ROW_DATA') -- exclude blobs
END
SET @OptimizationStartDateTime = GETUTCDATE()
IF ((@IndexId IS NULL) OR (@AvgFragmentationInPercent < @MinAvgFragmentationInPercentToOptimize))
BEGIN
-- don't optimize indexes with low fragmentation
UPDATE StandardDatasetOptimizationHistory
SET OptimizationStartDateTime = @OptimizationStartDateTime
,OptimizationDurationSeconds = 0
,BeforeAvgFragmentationInPercent = @AvgFragmentationInPercent
,AfterAvgFragmentationInPercent = @AvgFragmentationInPercent
,OptimizationMethod = CASE WHEN @IndexId IS NULL THEN 'index doesn''t exist' ELSE 'no optimization' END
WHERE (StandardDatasetOptimizationHistoryRowId = @StandardDatasetOptimizationHistoryRowId)
END
ELSE
BEGIN
SET @Statement = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
SET @IndexRebuild = 1
IF ((@AvgFragmentationInPercent > @MinAvgFragmentationInPercentToRebuild) OR (@InsertInd = 0))
BEGIN
SET @OnlineRebuildInd = 0
-- try online rebuild if possible
IF (@OnlineRebuildPossibleInd = 1)
BEGIN
BEGIN TRY
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting online optimization (rebuild) of table ' + @TableName + ' index ' + @IndexName
SET @EffectiveStatement = @Statement + ' REBUILD WITH (ONLINE=ON, FILLFACTOR=' + CASE @InsertInd WHEN 0 THEN '100' ELSE '80' END + ')'
EXECUTE (@EffectiveStatement)
SET @IndexOptimized = 1
SET @OnlineRebuildInd = 1
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished online optimization (rebuild) of table ' + @TableName + ' index ' + @IndexName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @OptimizationStartDateTime))
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Online rebuild failed for table ' + @TableName + ' index ' + @IndexName
+ '. Error number ' + CAST(@ErrorNumber AS varchar(10))
+ ' Message: ' + @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
END
END CATCH
UPDATE StandardDatasetAggregationStorageIndex
SET OnlineRebuildPossibleInd = @IndexOptimized
,OnlineRebuildLastPerformedDateTime = @OptimizationStartDateTime
WHERE (StandardDatasetAggregationStorageIndexRowId = @StandardDatasetAggregationStorageIndexRowId)
END
-- when online rebuild fails, perform blocking rebuild or reorg
IF (@IndexOptimized = 0)
BEGIN
IF (@CanPerformBlockingOptimizationInd = 1)
BEGIN
SET @EffectiveStatement = @Statement + ' REBUILD WITH (FILLFACTOR=' + CASE @InsertInd WHEN 0 THEN '100' ELSE '80' END + ')'
SET @IndexRebuild = 1
END
ELSE
BEGIN
IF (@InsertInd = 1)
BEGIN
SET @EffectiveStatement = @Statement + ' REORGANIZE'
SET @IndexRebuild = 0
END
ELSE
BEGIN
-- don't do "can't do rebuild - reorg instead" on a non-insert table
-- to avoid endless fruitless reorgs
SET @EffectiveStatement = NULL
END
END
END
ELSE
BEGIN
SET @EffectiveStatement = NULL
END
END
ELSE
BEGIN
-- reorg
SET @EffectiveStatement = @Statement + ' REORGANIZE'
SET @IndexRebuild = 0
END
IF (@EffectiveStatement IS NOT NULL)
BEGIN
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting optimization of table ' + @TableName + ' index ' + @IndexName
SET @DebugMessage = @DebugMessage + '. Method: index ' + CASE @IndexRebuild WHEN 0 THEN 'reorg' ELSE 'rebuild' END
EXECUTE(@EffectiveStatement)
SET @IndexOptimized = 1
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished optimization of table ' + @TableName + ' index ' + @IndexName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @OptimizationStartDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@OperationDurationMs = @OperationDurationMs
END
END
END
IF (@IndexOptimized = 1)
BEGIN
SELECT @AfterAvgFragmentationInPercent = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@TableNameWithSchema), @IndexId, NULL, NULL)
WHERE (alloc_unit_type_desc = 'IN_ROW_DATA') -- exclude blobs
UPDATE StandardDatasetOptimizationHistory
SET OptimizationStartDateTime = @OptimizationStartDateTime
,OptimizationDurationSeconds = ABS(DATEDIFF(second, @OptimizationStartDateTime, GETUTCDATE()))
,BeforeAvgFragmentationInPercent = @AvgFragmentationInPercent
,AfterAvgFragmentationInPercent = @AfterAvgFragmentationInPercent
,OptimizationMethod = CASE @IndexRebuild
WHEN 1 THEN CASE @OnlineRebuildInd
WHEN 1 THEN 'online'
ELSE 'offline'
END + ' rebuild'
ELSE 'reorganize'
END
WHERE (StandardDatasetOptimizationHistoryRowId = @StandardDatasetOptimizationHistoryRowId)
BREAK
END
-- check/update statistics on the index
SET @StatisticUpdatedInd = 0
IF (@IndexId IS NOT NULL)
BEGIN
-- check this index stats
IF (STATS_DATE(OBJECT_ID(@TableNameWithSchema), @IndexId) < DATEADD(hour, -@StatisticsMaxAgeHours, GETDATE()))
OR
(STATS_DATE(OBJECT_ID(@TableNameWithSchema), @IndexId) IS NULL)
BEGIN
SET @Statement = 'UPDATE STATISTICS ' + @TableNameWithSchema + ' ' + QUOTENAME(@IndexName)
SET @StatisticsUpdateMethod = 'update stats'
IF (@StatisticsUpdateSamplePercentage > 0)
BEGIN
IF (@StatisticsUpdateSamplePercentage >= 100)
BEGIN
SET @Statement = @Statement + ' WITH FULLSCAN'
SET @StatisticsUpdateMethod = @StatisticsUpdateMethod + ' fullscan'
END
ELSE
BEGIN
SET @Statement = @Statement + ' WITH SAMPLE ' + CAST(@StatisticsUpdateSamplePercentage AS varchar(10)) + ' PERCENT'
SET @StatisticsUpdateMethod = @StatisticsUpdateMethod + ' sample ' + CAST(@StatisticsUpdateSamplePercentage AS varchar(10)) + '%'
END
END
SET @StatisticsUpdateStartDateTime = GETUTCDATE()
EXECUTE (@Statement)
SET @StatisticsUpdateDurationSeconds = ABS(DATEDIFF(second, @StatisticsUpdateStartDateTime, GETUTCDATE()))
-- do not consider quick stats updates
IF (@StatisticsUpdateDurationSeconds > 0)
BEGIN
SET @StatisticUpdatedInd = 1
END
INSERT StandardDatasetOptimizationHistory (
StandardDatasetTableMapRowId
,StandardDatasetAggregationStorageIndexRowId
,OptimizationStartDateTime
,OptimizationDurationSeconds
,BeforeAvgFragmentationInPercent
,AfterAvgFragmentationInPercent
,OptimizationMethod
)
SELECT
StandardDatasetTableMapRowId
,StandardDatasetAggregationStorageIndexRowId
,@StatisticsUpdateStartDateTime
,@StatisticsUpdateDurationSeconds
,BeforeAvgFragmentationInPercent
,AfterAvgFragmentationInPercent
,@StatisticsUpdateMethod
FROM StandardDatasetOptimizationHistory
WHERE (StandardDatasetOptimizationHistoryRowId = @StandardDatasetOptimizationHistoryRowId)
END
END
-- do only one lengthy operation
IF ((@IndexOptimized = 1) OR (@StatisticUpdatedInd = 1))
BEGIN
BREAK
END
END
IF (@LockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @LockResourceName
,@LockOwner = 'Session'
SET @LockSetInd = 0
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@LockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @LockResourceName
,@LockOwner = 'Session'
END
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to optimize data for standard data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @DebugMessageText
END
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@StagingLockSetInd bit
,@StagingLockResourceName sysname
,@ExecResult int
,@DebugLevel int
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
FROM StandardDataset
WHERE DatasetId = @DatasetId
DECLARE @StagingProcessingSpName sysname
SELECT @StagingProcessingSpName = StagingProcessorStoredProcedureName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
IF (@StagingProcessingSpName IS NOT NULL)
BEGIN
-- lock staging to ensure only one process does the transfer
SET @StagingLockResourceName = CAST(@DatasetId AS varchar(50)) + '_Staging'
-- cleanup
IF (@StagingLockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @StagingLockResourceName
,@LockOwner = 'Session'
SET @StagingLockSetInd = 0
END
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process stagingarea for data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
</Install> <Uninstall>
-------------------------------------------------------
--
-- DROP SPs
--
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetAggregate')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StandardDatasetAggregate')
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetAggregationSizeList')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StandardDatasetAggregationSizeList')
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetAllocateStorage')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StandardDatasetAllocateStorage')
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetBuildCoverView')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StandardDatasetBuildCoverView')
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetDelete')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StandardDatasetDelete')
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetGetInsertTableGuid')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StandardDatasetGetInsertTableGuid')
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetGroom')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StandardDatasetGroom')
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetIndexDelete')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StandardDatasetIndexDelete')
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetIndexInsert')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StandardDatasetIndexInsert')
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetMaintenance')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StandardDatasetMaintenance')
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetOptimize')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StandardDatasetOptimize')
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetProcessStaging')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StandardDatasetProcessStaging')
END
GO
-------------------------------------------------------
--
-- DROP Tables
--
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_StandardDatasetOptimizationHistory_StandardDatasetTableMap')
ALTER TABLE StandardDatasetOptimizationHistory DROP CONSTRAINT FK_StandardDatasetOptimizationHistory_StandardDatasetTableMap
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_StandardDatasetAggregationStorageIndex_StandardDatasetAggregationStorage')
ALTER TABLE StandardDatasetAggregationStorageIndex DROP CONSTRAINT FK_StandardDatasetAggregationStorageIndex_StandardDatasetAggregationStorage
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDataset' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.StandardDataset
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetStagingArea' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.StandardDatasetStagingArea
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetAggregation' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.StandardDatasetAggregation
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetAggregationHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.StandardDatasetAggregationHistory
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetAggregationStorage' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.StandardDatasetAggregationStorage
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetAggregationStorageIndex' AND TABLE_SCHEMA = 'dbo')
BEGIN
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_StandardDatasetOptimizationHistory_StandardDatasetAggregationStorageIndex')
ALTER TABLE StandardDatasetOptimizationHistory DROP CONSTRAINT FK_StandardDatasetOptimizationHistory_StandardDatasetAggregationStorageIndex
DROP TABLE dbo.StandardDatasetAggregationStorageIndex
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetOptimizationHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.StandardDatasetOptimizationHistory
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDatasetTableMap' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.StandardDatasetTableMap
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandardDataset' AND COLUMN_NAME = 'LastOptimizationActionSuccessfulCompletionDateTime')
BEGIN
ALTER TABLE dbo.StandardDataset
ADD LastOptimizationActionSuccessfulCompletionDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
END
GO
BEGIN TRAN
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandardDatasetAggregationStorageIndex' AND COLUMN_NAME = 'IndexGuid' AND IS_NULLABLE='YES')
BEGIN
ALTER TABLE dbo.StandardDatasetAggregationStorageIndex
ALTER COLUMN IndexGuid uniqueidentifier NULL
IF @@ERROR <> 0 GOTO QuitError
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandardDatasetAggregationStorageIndex' AND COLUMN_NAME = 'IndexDefinition' AND IS_NULLABLE='YES')
BEGIN
ALTER TABLE dbo.StandardDatasetAggregationStorageIndex
ALTER COLUMN IndexDefinition nvarchar(1000) NULL
IF @@ERROR <> 0 GOTO QuitError
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandardDatasetAggregationStorageIndex' AND COLUMN_NAME = 'PrimaryKeyInd')
BEGIN
ALTER TABLE dbo.StandardDatasetAggregationStorageIndex
ADD PrimaryKeyInd bit NOT NULL DEFAULT (0)
IF @@ERROR <> 0 GOTO QuitError
END
IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE name = 'CHK_StandardDatasetAggregationStorageIndex_Index' AND parent_object_id = OBJECT_ID('StandardDatasetAggregationStorageIndex'))
BEGIN
EXECUTE ('ALTER TABLE dbo.StandardDatasetAggregationStorageIndex
ADD CONSTRAINT CHK_StandardDatasetAggregationStorageIndex_Index CHECK ((PrimaryKeyInd = 1 AND UniqueInd = 1 AND IndexGuid IS NULL AND IndexDefinition IS NULL) OR (PrimaryKeyInd = 0 AND IndexGuid IS NOT NULL AND IndexDefinition IS NOT NULL))')
IF @@ERROR <> 0 GOTO QuitError
END
COMMIT
GOTO Quit
QuitError:
IF (@@TRANCOUNT > 0) ROLLBACK
Quit:
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetAggregate
@DatasetId uniqueidentifier
,@AggregationTargetStartDateTime datetime = NULL
,@IntervalsToAggregate int = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@LockResourceName sysname
,@ExecResult int
,@LockSetInd bit
,@CurrentAggregationIntervalDurationMinutes int
,@AggregationIntervalCount int
,@DebugLevel tinyint
,@LastMidnightDateTime datetime
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
,@StandardDatasetAggregationHistoryRowId int
,@i int
,@j int
,@CurrentAggregationTypeId tinyint
,@AggregationStartDateTime datetime
,@TableGuid uniqueidentifier
,@InsertTableGuid uniqueidentifier
,@TableNameSuffix varchar(50)
,@AggregationExistedInd bit
,@Statement nvarchar(max)
,@RowsDeleted int
,@UtcOffsetMinutes int
SET @UtcOffsetMinutes = DATEDIFF(minute, GETDATE(), GETUTCDATE())
BEGIN TRY
-- set lock to make sure only one process
-- performs aggregations on this data set
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_Aggregate'
IF (@ExecResult < -1)
BEGIN
RAISERROR(777971001, 16, 1, 'StandardDatasetAggregation', @ExecResult)
END
-- lock timeout - some other process performing aggregations
IF (@ExecResult = -1)
BEGIN
RETURN
END
SET @LockSetInd = 1
SELECT
@AggregationIntervalCount = ISNULL(@IntervalsToAggregate, DefaultAggregationIntervalCount)
,@DebugLevel = DebugLevel
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
-- find lowest type aggregations we need to perform
SELECT @CurrentAggregationTypeId = MIN(AggregationTypeId)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > 0)
IF (@CurrentAggregationTypeId IS NULL)
BEGIN
-- dataset does not need aggregations
RETURN
END
SELECT @CurrentAggregationIntervalDurationMinutes = AggregationIntervalDurationMinutes
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
-- adjust parameters if needed
IF (@AggregationIntervalCount < 1)
SET @AggregationIntervalCount = 1
IF (@AggregationTargetStartDateTime IS NULL)
SET @AggregationTargetStartDateTime = GETUTCDATE()
-- find the last aggregation interval which ended before
-- the aggregation start date time
IF (@CurrentAggregationTypeId < 30)
BEGIN
SET @LastMidnightDateTime = CAST(CONVERT(char(8), @AggregationTargetStartDateTime, 112) AS datetime)
SET @IntervalStartDateTime = DATEADD(
minute
,ABS(DATEDIFF(minute, @LastMidnightDateTime, @AggregationTargetStartDateTime)) / @CurrentAggregationIntervalDurationMinutes * @CurrentAggregationIntervalDurationMinutes - @CurrentAggregationIntervalDurationMinutes
,CAST(CONVERT(char(8), @AggregationTargetStartDateTime, 112) AS datetime))
END
ELSE IF (@CurrentAggregationTypeId = 30)
BEGIN
SET @IntervalStartDateTime = DATEADD(minute, @UtcOffsetMinutes, CAST(CONVERT(char(8), GETDATE(), 112) AS datetime))
END
SET @i = @AggregationIntervalCount
DECLARE @Aggregation TABLE
(
AggregationTypeId int NOT NULL
,AggregationDateTime datetime NOT NULL
)
WHILE (@i > 0)
BEGIN
INSERT @Aggregation(AggregationTypeId, AggregationDateTime)
VALUES (@CurrentAggregationTypeId, @IntervalStartDateTime)
SET @i = @i - 1
SET @IntervalStartDateTime = DATEADD(minute, -@CurrentAggregationIntervalDurationMinutes, @IntervalStartDateTime)
END
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > @CurrentAggregationTypeId)
)
BEGIN
SELECT @CurrentAggregationTypeId = MIN(AggregationTypeId)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > @CurrentAggregationTypeId)
IF (@CurrentAggregationTypeId = 20) -- hourly
BEGIN
INSERT @Aggregation (
AggregationTypeId
,AggregationDateTime
)
SELECT DISTINCT
20
,DATEADD(minute, -DATEPART(minute, AggregationDateTime), AggregationDateTime)
FROM @Aggregation
END
IF (@CurrentAggregationTypeId = 30) -- daily
BEGIN
INSERT @Aggregation (
AggregationTypeId
,AggregationDateTime
)
SELECT DISTINCT
30
,AggregationDateTime
FROM @Aggregation
WHERE (ABS(DATEDIFF(minute, DATEADD(minute, -@UtcOffsetMinutes, AggregationDateTime), CONVERT(char(8), DATEADD(minute, -@UtcOffsetMinutes, AggregationDateTime), 112))) < 60)
END
END
-- groom aggregation history
DELETE StandardDatasetAggregationHistory
WHERE (AggregationDateTime < DATEADD(month, -1, GETDATE()))
INSERT StandardDatasetAggregationHistory (
DatasetId
,AggregationTypeId
,AggregationDateTime
)
SELECT
@DatasetId
,a.AggregationTypeId
,a.AggregationDateTime
FROM @Aggregation a
WHERE NOT EXISTS (SELECT * FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = a.AggregationTypeId)
AND (AggregationDateTime = a.AggregationDateTime)
)
AND (a.AggregationTypeId < 30)
-- account for daylight switching in the daily aggregations
INSERT StandardDatasetAggregationHistory (
DatasetId
,AggregationTypeId
,AggregationDateTime
)
SELECT
@DatasetId
,a.AggregationTypeId
,a.AggregationDateTime
FROM @Aggregation a
WHERE NOT EXISTS (SELECT * FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = a.AggregationTypeId)
AND (AggregationDateTime BETWEEN DATEADD(hour, -1, a.AggregationDateTime) AND DATEADD(hour, 1, a.AggregationDateTime))
)
AND (a.AggregationTypeId = 30)
-- select aggregation period to work on
-- first select oldest never aggregated interval
-- if none exist select oldest dirty interval
SELECT TOP 1
@StandardDatasetAggregationHistoryRowId = StandardDatasetAggregationHistoryRowId
,@CurrentAggregationTypeId = ah.AggregationTypeId
,@IntervalStartDateTime = ah.AggregationDateTime
,@IntervalEndDateTime = DATEADD(minute, a.AggregationIntervalDurationMinutes, ah.AggregationDateTime)
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON ((a.DatasetId = ah.DatasetId) AND (a.AggregationTypeId = ah.AggregationTypeId))
WHERE (ah.DatasetId = @DatasetId)
AND (DATEADD(minute, a.AggregationIntervalDurationMinutes + a.AggregationStartDelayMinutes, ah.AggregationDateTime) < GETUTCDATE()) -- aggregation interval + slight delay is in the past
--AND (DATEADD(minute, a.AggregationStartDelayMinutes, ISNULL(ah.DataLastReceivedDateTime, '20050101')) < GETUTCDATE()) -- data last received time + slight delay is in the past
AND (DirtyInd = 1)
AND (ah.AggregationCount = 0) -- interval was never aggregated
ORDER BY ah.AggregationTypeId ASC, ah.AggregationDateTime ASC
IF (@StandardDatasetAggregationHistoryRowId IS NULL)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationHistoryRowId = StandardDatasetAggregationHistoryRowId
,@CurrentAggregationTypeId = ah.AggregationTypeId
,@IntervalStartDateTime = ah.AggregationDateTime
,@IntervalEndDateTime = DATEADD(minute, a.AggregationIntervalDurationMinutes, ah.AggregationDateTime)
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON ((a.DatasetId = ah.DatasetId) AND (a.AggregationTypeId = ah.AggregationTypeId))
WHERE (ah.DatasetId = @DatasetId)
AND (DATEADD(minute, a.AggregationIntervalDurationMinutes + a.AggregationStartDelayMinutes, ah.AggregationDateTime) < GETUTCDATE()) -- aggregation interval + slight delay is in the past
AND (DATEADD(minute, a.AggregationStartDelayMinutes, ISNULL(ah.DataLastReceivedDateTime, DATEADD(minute, a.AggregationIntervalDurationMinutes, ah.AggregationDateTime))) < GETUTCDATE()) -- data last received time + slight delay is in the past
AND (DirtyInd = 1)
AND (NOT EXISTS (SELECT *
FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = ah.AggregationTypeId)
AND (DirtyInd = 1)
AND (AggregationDateTime < ah.AggregationDateTime)
)
) -- this is the oldest aggregation which is dirty
ORDER BY ah.AggregationTypeId ASC, ah.AggregationDateTime ASC
END
BEGIN TRAN
IF (@StandardDatasetAggregationHistoryRowId IS NOT NULL)
BEGIN
-- find and delete old aggregates if they exist
-- the aggregates may only reside in a single table
SET @j = -1
SET @InsertTableGuid = NULL
SET @AggregationExistedInd = 0
WHILE (EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId > @j)
AND (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (@IntervalStartDateTime BETWEEN ISNULL(StartDateTime, '19000101') AND ISNULL(EndDateTime, '99991231'))
)
)
BEGIN
SELECT TOP 1
@j = StandardDatasetTableMapRowId
,@TableNameSuffix = TableNameSuffix
,@TableGuid = TableGuid
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId > @j)
AND (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (@IntervalStartDateTime BETWEEN ISNULL(StartDateTime, '19000101') AND ISNULL(EndDateTime, '99991231'))
ORDER BY StandardDatasetTableMapRowId
SELECT @Statement = DeleteAggregationStoredProcedureName
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
IF (@RowsDeleted > 0)
BEGIN
SET @AggregationExistedInd = 1
SET @InsertTableGuid = @TableGuid
BREAK
END
END
IF (@InsertTableGuid IS NULL)
EXEC StandardDatasetGetInsertTableGuid @DatasetId, @CurrentAggregationTypeId, @InsertTableGuid OUTPUT
-- build new aggregates
SELECT @Statement = BuildAggregationStoredProcedureName
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
IF (@AggregationExistedInd = 1)
BEGIN
-- set table for optimization if required
UPDATE StandardDatasetTableMap
SET OptimizedInd = 0
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (TableGuid = @InsertTableGuid)
END
ELSE
BEGIN
-- reallocate storage if required
EXEC StandardDatasetAllocateStorage @DatasetId, @CurrentAggregationTypeId
END
-- update aggregation history
UPDATE StandardDatasetAggregationHistory
SET AggregationCount = AggregationCount + 1
,FirstAggregationStartDateTime = ISNULL(FirstAggregationStartDateTime, @AggregationStartDateTime)
,FirstAggregationDurationSeconds = ISNULL(FirstAggregationDurationSeconds, ABS(DATEDIFF(second, GETUTCDATE(), @AggregationStartDateTime)))
,LastAggregationStartDateTime = @AggregationStartDateTime
,LastAggregationDurationSeconds = ABS(DATEDIFF(second, GETUTCDATE(), @AggregationStartDateTime))
,DirtyInd =
CASE
WHEN EXISTS (SELECT *
FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (AggregationDateTime < @IntervalStartDateTime)
AND (DirtyInd = 1)
) -- still dirty if there is a prior dirty aggregation of the same level
THEN 1
WHEN EXISTS (SELECT *
FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId < @CurrentAggregationTypeId)
AND (AggregationDateTime >= @IntervalStartDateTime)
AND (AggregationDateTime < @IntervalEndDateTime)
AND (DirtyInd = 1)
) -- still dirty if there is dirty lower-level aggregation inside the interval
THEN 1
ELSE 0
END
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (AggregationDateTime = @IntervalStartDateTime)
END
COMMIT
IF (@LockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @LockResourceName
,@LockOwner = 'Session'
SET @LockSetInd = 0
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@LockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @LockResourceName
,@LockOwner = 'Session'
END
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to aggregate data for standard data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @DebugMessageText
END
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetAggregationSizeList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetAggregationSizeList AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetAggregationSizeList
AS
BEGIN
SET NOCOUNT ON
DECLARE @TableSize TABLE (
TableName sysname NOT NULL
,[RowCount] bigint NOT NULL
,Reserved varchar(30) NOT NULL
,Data varchar(30) NOT NULL
,IndexSize varchar(30) NOT NULL
,Unused varchar(30) NOT NULL
)
DECLARE @Result TABLE (
DatasetId uniqueidentifier NOT NULL
,ServerName sysname NOT NULL
,DatabaseName sysname NOT NULL
,DatasetName nvarchar(256) NOT NULL
,AggregationTypeId int NOT NULL
,AggregationTypeName nvarchar(50) NOT NULL
,MaxDataAgeDays int NOT NULL
,[RowCount] bigint NULL
,MinStartDateTime datetime NULL
,SizeKb bigint NOT NULL
,DailySizeKb float NULL
,DailyRowCount bigint NULL
,TotalSizeKb float NULL
,TotalRowCount bigint NULL
,DataFileGroupName sysname NOT NULL
,IndexFileGroupName sysname NOT NULL
)
SET @DatasetId = '00000000-0000-0000-0000-000000000000'
-- take all standard datasets one-by one
WHILE EXISTS (SELECT *
FROM vDataset d
JOIN StandardDataset sd ON (d.DatasetId = sd.DatasetId)
JOIN vMemberDatabase mdb ON (d.MemberDatabaseRowId = mdb.MemberDatabaseRowId)
WHERE (d.DatasetId > @DatasetId)
AND (d.InstallCompletedInd = 1)
)
BEGIN
SELECT TOP 1
@DatasetId = d.DatasetId
,@SchemaName = sd.SchemaName
,@DatasetName = d.DatasetDefaultName
,@DatasetDescription = d.DatasetDefaultDescription
,@ServerName = mdb.ServerName
,@DatabaseName = mdb.DatabaseName
FROM vDataset d
JOIN StandardDataset sd ON (d.DatasetId = sd.DatasetId)
JOIN vMemberDatabase mdb ON (d.MemberDatabaseRowId = mdb.MemberDatabaseRowId)
WHERE (d.DatasetId > @DatasetId)
AND (d.InstallCompletedInd = 1)
ORDER BY d.DatasetId
-- take each dataset aggregation one-by one
SET @AggregationTypeId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > @AggregationTypeId)
)
BEGIN
SELECT TOP 1
@AggregationTypeId = a.AggregationTypeId
,@AggregationTypeName = at.AggregationTypeDefaultName
,@MaxDataAgeDays = a.MaxDataAgeDays
,@DataFileGroupName = a.DataFileGroupName
,@IndexFileGroupName = a.IndexFileGroupName
FROM StandardDatasetAggregation a
JOIN vAggregationType at ON (a.AggregationTypeId = at.AggregationTypeId)
WHERE (a.DatasetId = @DatasetId)
AND (a.AggregationTypeId > @AggregationTypeId)
ORDER BY a.AggregationTypeId
SET @RowCount = 0
SET @SizeKb = 0
SET @TotalHours = 0
SET @MinStartDateTime = NULL
SET @RowCountForDailyAvg = 0
SET @SizeKbForDailyAvg = 0
-- calculate number of rows and size in Mb for the aggregation
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetTableMapRowId = StandardDatasetTableMapRowId
,@TableGuid = TableGuid
,@TableNameSuffix = TableNameSuffix
,@StartDateTime = StartDateTime
,@EndDateTime = EndDateTime
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
ORDER BY StandardDatasetTableMapRowId
-- walk through all storage tables for this "guid" and calculate there size
SET @StandardDatasetAggregationStorageRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationStorageRowId = StandardDatasetAggregationStorageRowId
,@DependentTableInd = DependentTableInd
,@BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
ORDER BY StandardDatasetAggregationStorageRowId
-- get table creation date in case data was wrong
-- about the date it was generated
SELECT @TableCreatedDateTime = create_date
FROM sys.objects o
JOIN sys.schemas s ON (o.schema_id = s.schema_id)
WHERE (s.name = @SchemaName)
AND (o.name = @BaseTableName + '_' + @TableNameSuffix)
IF (@StartDateTime < @TableCreatedDateTime)
SET @StartDateTime = @TableCreatedDateTime
IF (@EndDateTime > GETUTCDATE())
SET @EndDateTime = GETUTCDATE()
SELECT
@RowCount = @RowCount + CASE WHEN @DependentTableInd = 0 THEN [RowCount] ELSE 0 END
,@SizeKb = @SizeKb + CAST(REPLACE(REPLACE(Reserved, 'KB', ''), ' ', '') as bigint)
FROM @TableSize
IF (@StartDateTime IS NOT NULL) AND (@EndDateTime IS NOT NULL)
BEGIN
SET @TotalHours = @TotalHours + ABS(DATEDIFF(hour, @StartDateTime, @EndDateTime))
SELECT
@RowCountForDailyAvg = @RowCountForDailyAvg + CASE WHEN @DependentTableInd = 0 THEN [RowCount] ELSE 0 END
,@SizeKbForDailyAvg = @SizeKbForDailyAvg + CAST(REPLACE(REPLACE(Reserved, 'KB', ''), ' ', '') as bigint)
FROM @TableSize
SET @MinStartDateTime =
CASE
WHEN @MinStartDateTime IS NULL THEN @StartDateTime
WHEN @StartDateTime < @MinStartDateTime THEN @StartDateTime
ELSE @MinStartDateTime
END
SET @MaxEndDateTime =
CASE
WHEN @MaxEndDateTime IS NULL THEN @EndDateTime
WHEN @EndDateTime > @MaxEndDateTime THEN @EndDateTime
ELSE @MaxEndDateTime
END
END
END -- std ds aggregation storage loop
END -- std ds tbl map loop
SET @TotalHours = ABS(DATEDIFF(hour, @MinStartDateTime, @MaxEndDateTime))
INSERT @Result (
DatasetId
,ServerName
,DatabaseName
,DatasetName
,AggregationTypeId
,AggregationTypeName
,MaxDataAgeDays
,[RowCount]
,MinStartDateTime
,SizeKb
,DailyRowCount
,DailySizeKb
,DataFileGroupName
,IndexFileGroupName
)
SELECT
@DatasetId
,@ServerName
,@DatabaseName
,@DatasetName
,@AggregationTypeId
,@AggregationTypeName
,@MaxDataAgeDays
,@RowCount
,@MinStartDateTime
,@SizeKb
,ROUND(CASE WHEN @TotalHours > 0 THEN @RowCountForDailyAvg / CAST(@TotalHours AS float) * 24.0 ELSE NULL END, 0)
,CASE WHEN @TotalHours > 0 THEN @SizeKbForDailyAvg / CAST(@TotalHours AS float) * 24.0 ELSE NULL END
,ISNULL(@DataFileGroupName, 'default')
,ISNULL(@IndexFileGroupName, 'default')
END -- std ds aggregation loop
END -- std ds loop
-- add config / instance space data
-- loop through tables registered for optimization
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'MaintenanceSetting')
BEGIN
DELETE @TableSize
SET @DomainTableRowId = 0
WHILE EXISTS (SELECT *
FROM DomainTable
WHERE (DomainTableRowId > @DomainTableRowId)
)
BEGIN
SELECT TOP 1
@DomainTableRowId = DomainTableRowId
,@TableName = QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
FROM DomainTable
WHERE (DomainTableRowId > @DomainTableRowId)
ORDER BY DomainTableRowId
SELECT
ServerName
,DatabaseName
,DatasetId
,AggregationTypeId
,DatasetName
,AggregationTypeName
,MaxDataAgeDays
,[RowCount]
,MinStartDateTime
,SizeKb
,SizePercentOfTotalSize = CAST(SizeKb AS float) / (SELECT SUM(SizeKb) FROM @Result) * 100
,DailySizeKb as EstimatedAvgDailySizeKb
,DailyRowCount as EstimatedAvgDailyRowCount
,TotalSizeKb as EstimatedTotalSizeKb
,TotalRowCount as EstimatedTotalRowCount
,TotalSizePercent = CAST(TotalSizeKb AS float) / (SELECT SUM(TotalSizeKb) FROM @Result) * 100
,DataFileGroupName
,IndexFileGroupName
FROM @Result
ORDER BY DatasetName, AggregationTypeId
END
GO
GRANT EXECUTE ON dbo.StandardDatasetAggregationSizeList TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetAllocateStorage')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetAllocateStorage AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetAllocateStorage
@DatasetId uniqueidentifier
,@AggregationTypeId int
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
,@DebugLevel tinyint
SET @ErrorInd = 0
BEGIN TRY
BEGIN TRAN
DECLARE
@LockResourceName sysname
,@ExecResult int
,@DatasetIdString varchar(50)
-- lock table map to ensure only this transaction is granted rights to (re-)create insertion tables
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_TableMap'
IF (@ExecResult < -1)
BEGIN
RAISERROR(777971001, 16, 1, 'Update:StandardDatasetTableMap', @ExecResult)
END
IF (@ExecResult <> -1)
BEGIN
DECLARE
@ExpectedInsertTableCount int
,@ActualInsertTableCount int
,@MapRowId int
,@StorageRowId int
,@TableRowId int
,@BaseTableName sysname
,@TableNameSuffix varchar(50)
,@TableName sysname
,@TableGuid uniqueidentifier
,@MessageText nvarchar(max)
,@TableStatement nvarchar(max)
,@NewTableGuid uniqueidentifier
,@NewTableSuffix varchar(50)
,@MaxTableRowCount int
,@MaxTableSizeKb int
,@TableTemplate nvarchar(max)
,@IndexRowId bigint
,@IndexName sysname
,@UniqueInd bit
,@IndexDefinition nvarchar(1000)
,@IndexStatement nvarchar(2000)
,@IndexFileGroupName sysname
,@SchemaName sysname
DECLARE @TableSize TABLE (
TableRowId int NOT NULL IDENTITY(1, 1)
,TableName sysname NOT NULL
,[RowCount] bigint NOT NULL
,Reserved varchar(30) NOT NULL
,Data varchar(30) NOT NULL
,IndexSize varchar(30) NOT NULL
,Unused varchar(30) NOT NULL
,TableGuid uniqueidentifier NULL
,MaxTableRowCount int NULL
,MaxTableSizeKb int NULL
)
SELECT
@ExpectedInsertTableCount = CASE WHEN @AggregationTypeId > 0 THEN 1 ELSE RawInsertTableCount END
,@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SELECT @ActualInsertTableCount = COUNT(*)
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (InsertInd = 1)
SET @MapRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (InsertInd = 1)
AND (StandardDatasetTableMapRowId > @MapRowId)
)
BEGIN
SELECT TOP 1
@MapRowId = StandardDatasetTableMapRowId
,@TableNameSuffix = TableNameSuffix
,@TableGuid = TableGuid
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (InsertInd = 1)
AND (StandardDatasetTableMapRowId > @MapRowId)
ORDER BY StandardDatasetTableMapRowId
-- build list of tables to check size for
SET @StorageRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
)
BEGIN
SELECT TOP 1
@StorageRowId = StandardDatasetAggregationStorageRowId
,@BaseTableName = BaseTableName
,@MaxTableRowCount = MaxTableRowCount
,@MaxTableSizeKb = MaxTableSizeKb
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
UPDATE @TableSize
SET TableGuid = @TableGuid
,MaxTableRowCount = @MaxTableRowCount
,MaxTableSizeKb = @MaxTableSizeKb
WHERE TableRowId = @TableRowId
END
END
-- check if any of the tables is over the limit(s)
SET @TableGuid = NULL
SELECT TOP 1 @TableGuid = TableGuid
FROM @TableSize
WHERE (([RowCount] > MaxTableRowCount) OR (CAST(REPLACE(Data, 'KB', '') AS int) > MaxTableSizeKb))
DELETE @TableSize
WHERE TableGuid = @TableGuid
IF ((@TableGuid IS NOT NULL) OR (@ExpectedInsertTableCount > @ActualInsertTableCount))
BEGIN
-- re-allocate or create storage
IF ((@DebugLevel > 2) AND (@TableGuid IS NOT NULL))
BEGIN
SET @MessageText = 'Table set ' + CAST(@TableGuid AS varchar(50)) + ' aggregation type ' + CAST(@AggregationTypeId AS varchar(10)) + ' is full. Adding new table to the data set'
IF (@ExecResult < 0)
BEGIN
RAISERROR(777971001, 16, 1, 'Exclusive:StandardDatasetTableMap', @ExecResult)
END
WHILE ((@TableGuid IS NOT NULL) OR (@ExpectedInsertTableCount > @ActualInsertTableCount))
BEGIN
IF (@TableGuid IS NOT NULL)
BEGIN
-- no more inserts into this table
UPDATE StandardDatasetTableMap
SET InsertInd = 0
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (TableGuid = @TableGuid)
END
-- create new table(s)
SET @NewTableGuid = NEWID()
SET @NewTableSuffix = REPLACE(CAST(@NewTableGuid AS varchar(50)), '-', '')
SET @StorageRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
)
BEGIN
SELECT TOP 1
@StorageRowId = StandardDatasetAggregationStorageRowId
,@BaseTableName = BaseTableName
,@TableTemplate = TableTemplate
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
ORDER BY DependentTableInd, StandardDatasetAggregationStorageRowId
-- set correct file groups
SELECT @TableTemplate = REPLACE(@TableTemplate, '$DataFileGroupName$', QUOTENAME(ISNULL(a.DataFileGroupName, 'default')))
FROM StandardDatasetAggregation a
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
SELECT @TableTemplate = REPLACE(@TableTemplate, '$IndexFileGroupName$', QUOTENAME(ISNULL(a.IndexFileGroupName, 'default')))
FROM StandardDatasetAggregation a
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
-- set table name guid
SET @TableStatement = REPLACE(@TableTemplate, '$Guid$', @NewTableSuffix)
EXECUTE(@TableStatement)
-- create indexes
SELECT @IndexFileGroupName = ISNULL(IndexFileGroupName, 'default')
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
-- ensure PK is registered for optimization
IF NOT EXISTS (SELECT *
FROM StandardDatasetAggregationStorageIndex
WHERE (PrimaryKeyInd = 1)
AND (StandardDatasetAggregationStorageRowId = @StorageRowId)
)
AND (@TableStatement LIKE '%primary key%')
BEGIN
INSERT StandardDatasetAggregationStorageIndex (
StandardDatasetAggregationStorageRowId
,PrimaryKeyInd
,UniqueInd
)
VALUES (
@StorageRowId
,1
,1
)
END
SET @IndexRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorageIndex
WHERE (StandardDatasetAggregationStorageRowId = @StorageRowId)
AND (StandardDatasetAggregationStorageIndexRowId > @IndexRowId)
AND (PrimaryKeyInd = 0)
)
BEGIN
SELECT TOP 1
@IndexRowId = StandardDatasetAggregationStorageIndexRowId
,@IndexName = 'IX_CUSTOM_' + REPLACE(CAST(IndexGuid AS varchar(100)), '-', '')
,@UniqueInd = UniqueInd
,@IndexDefinition = IndexDefinition
FROM StandardDatasetAggregationStorageIndex
WHERE (StandardDatasetAggregationStorageRowId = @StorageRowId)
AND (StandardDatasetAggregationStorageIndexRowId > @IndexRowId)
AND (PrimaryKeyInd = 0)
SET @IndexStatement = 'CREATE ' + CASE @UniqueInd WHEN 1 THEN ' UNIQUE ' ELSE '' END + ' INDEX'
+ ' ' + QUOTENAME(@IndexName)
+ ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + @NewTableSuffix)
+ ' ' + REPLACE(@IndexDefinition, '''', '''''')
+ ' ON ' + QUOTENAME(@IndexFileGroupName)
EXECUTE (@IndexStatement)
-- indicate no optimization currently needed for this index
INSERT StandardDatasetOptimizationHistory (
StandardDatasetTableMapRowId
,StandardDatasetAggregationStorageIndexRowId
,OptimizationStartDateTime
,OptimizationDurationSeconds
)
VALUES (
@MapRowId
,@IndexRowId
,GETUTCDATE()
,0
)
END
END
SET @ActualInsertTableCount = @ActualInsertTableCount + 1
SET @TableGuid = NULL
SELECT TOP 1 @TableGuid = TableGuid
FROM @TableSize
WHERE (([RowCount] > MaxTableRowCount) OR (CAST(REPLACE(Data, 'KB', '') AS int) > MaxTableSizeKb))
END
EXEC StandardDatasetBuildCoverView @DatasetId, @AggregationTypeId
END
END
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to allocate storage for standard data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @DebugMessageText
END
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetBuildCoverView')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetBuildCoverView AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetBuildCoverView
@DatasetId uniqueidentifier
,@AggregationTypeId int
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
,@DebugLevel tinyint
SET @ErrorInd = 0
BEGIN TRY
BEGIN TRAN
DECLARE
@StorageRowId int
,@MapRowId int
,@BaseTableName sysname
,@ViewStatement nvarchar(max)
,@NeedUnionAll bit
,@CoverViewSelectClause nvarchar(max)
,@SchemaName sysname
SELECT @SchemaName = SchemaName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
SET @StorageRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
)
BEGIN
SELECT TOP 1
@StorageRowId = StandardDatasetAggregationStorageRowId
,@BaseTableName = BaseTableName
,@CoverViewSelectClause = CoverViewSelectClause
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
ORDER BY DependentTableInd, StandardDatasetAggregationStorageRowId
SET @MapRowId = -1
SET @NeedUnionAll = 0
SET @ViewStatement = 'IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = ''V'' AND uid = SCHEMA_ID(''' + REPLACE(@SchemaName, '''', '''''') + ''') AND name = ''v' + REPLACE(@BaseTableName, '''', '''''') + ''')'
+ ' BEGIN'
+ ' EXECUTE(''CREATE VIEW ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('v' + @BaseTableName) + ' AS SELECT A = 1'')'
+ ' EXECUTE(''GRANT SELECT ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('v' + @BaseTableName) + ' TO OpsMgrReader'')'
+ ' END'
EXECUTE (@ViewStatement)
SET @ViewStatement = 'ALTER VIEW ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('v' + @BaseTableName) + ' AS '
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetTableMapRowId > @MapRowId)
)
BEGIN
SELECT TOP 1
@MapRowId = StandardDatasetTableMapRowId
,@ViewStatement = @ViewStatement + CASE @NeedUnionAll WHEN 1 THEN ' UNION ALL ' ELSE '' END
+ @CoverViewSelectClause
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + REPLACE(CAST(TableGuid AS varchar(50)), '-', '')) + ' (NOLOCK) '
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetTableMapRowId > @MapRowId)
ORDER BY StandardDatasetTableMapRowId
SET @NeedUnionAll = 1
END
EXECUTE (@ViewStatement)
END
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to build cover view for standard data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @DebugMessageText
END
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetDelete
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
BEGIN TRAN
-- delete dataset member tables
DECLARE
@StandardDatasetTableMapRowId int
,@StandardDatasetAggregationStorageRowId int
,@SchemaName sysname
,@BaseTableName sysname
,@TableNameSuffix sysname
,@AggregationTypeId int
,@Statement nvarchar(1000)
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap m
JOIN StandardDataset d ON (m.DatasetId = d.DatasetId)
WHERE (m.DatasetId = @DatasetId)
AND (m.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId
,@AggregationTypeId = m.AggregationTypeId
,@TableNameSuffix = m.TableNameSuffix
,@SchemaName = d.SchemaName
FROM StandardDatasetTableMap m
JOIN StandardDataset d ON (m.DatasetId = d.DatasetId)
WHERE (m.DatasetId = @DatasetId)
AND (m.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
ORDER BY m.StandardDatasetTableMapRowId
-- drop all dependent tables for this aggregation type
-- then drop all "main" tables
DECLARE @i int
SET @i = 1
WHILE (@i >= 0)
BEGIN
SET @StandardDatasetAggregationStorageRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = @i)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationStorageRowId = StandardDatasetAggregationStorageRowId
,@BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = @i)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
ORDER BY StandardDatasetAggregationStorageRowId
SET @Statement =
'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''' + @BaseTableName + '_' + @TableNameSuffix + ''' AND TABLE_SCHEMA = ''' + @SchemaName + ''')'
+ ' DROP TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + @TableNameSuffix)
EXECUTE(@Statement)
-- drop cover view
SET @Statement =
'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ''' + 'v' + @BaseTableName + ''' AND TABLE_SCHEMA = ''' + @SchemaName + ''')'
+ ' DROP VIEW ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('v' + @BaseTableName)
EXECUTE(@Statement)
END
SET @i = @i - 1
END
END
DELETE StandardDatasetOptimizationHistory
WHERE StandardDatasetAggregationStorageIndexRowId IN (
SELECT StandardDatasetAggregationStorageIndexRowId
FROM StandardDatasetAggregationStorageIndex
WHERE StandardDatasetAggregationStorageRowId IN (SELECT StandardDatasetAggregationStorageRowId
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
)
)
DELETE StandardDatasetAggregationStorageIndex
WHERE StandardDatasetAggregationStorageRowId IN (SELECT StandardDatasetAggregationStorageRowId
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
)
DELETE StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
DELETE StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
DELETE StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
DELETE StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
DELETE StandardDatasetStagingArea
WHERE (DatasetId = @DatasetId)
DELETE StandardDataset
WHERE (DatasetId = @DatasetId)
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetGetInsertTableGuid')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetGetInsertTableGuid AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetGetInsertTableGuid
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@InsertTableGuid varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON
SELECT TOP(@TargetTableIndex) @InsertTableGuid = TableGuid
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (InsertInd = 1)
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetGroom
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@DebugLevel tinyint
,@DebugMessage nvarchar(max)
,@OperationDurationMs bigint
,@TableBaseName sysname
,@TableNameSuffix varchar(50)
,@AggregationTypeSuffix varchar(50)
,@BaseTableName sysname
,@TableName sysname
,@Statement nvarchar(max)
,@CurrentAggregationTypeId int
,@LockResourceName sysname
,@ExecResult int
,@LastGroomingDateTime smalldatetime
,@MaxRowsToGroom int
,@RawInsertTableCount int
,@InsertTableCount int
,@CutoffDateTime datetime
,@StorageRowId int
,@MapRowId int
,@TableGuid uniqueidentifier
,@GroomingStartedDateTime datetime
,@RowsDeleted int
,@SchemaName sysname
BEGIN TRY
BEGIN TRAN
-- set lock to make sure only one process
-- performs grooming on this data set
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_Groom'
IF (@ExecResult < -1)
BEGIN
RAISERROR(777971001, 16, 1, 'StandardDatasetGroom', @ExecResult)
END
IF (@ExecResult = -1)
BEGIN
COMMIT
RETURN
END
SELECT
@DebugLevel = DebugLevel
,@RawInsertTableCount = RawInsertTableCount
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SET @CurrentAggregationTypeId = -1
SELECT TOP 1
@CurrentAggregationTypeId = AggregationTypeId
,@MaxRowsToGroom = MaxRowsToGroom
,@CutoffDateTime = DATEADD(minute, ISNULL(AggregationIntervalDurationMinutes, 0),
DATEADD(day, -MaxDataAgeDays, GETUTCDATE()))
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > @CurrentAggregationTypeId)
AND (DATEADD(minute, GroomingIntervalMinutes, ISNULL(LastGroomingDateTime, '19000101')) < GETUTCDATE())
ORDER BY AggregationTypeId
IF (@CurrentAggregationTypeId <> -1)
BEGIN
-- no-raw aggregations may only have 1 insert table
SELECT @InsertTableCount =
CASE
WHEN @CurrentAggregationTypeId > 0 THEN 1
ELSE @RawInsertTableCount
END
SET @LastGroomingDateTime = GETUTCDATE()
IF (@InsertTableCount = (SELECT COUNT(*)
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId))
)
BEGIN
-- this aggregation only has insert tables - groom each of them
SET @MapRowId = -1
SELECT @Statement = GroomStoredProcedureName
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
WHILE (EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (InsertInd = 1)
AND (StandardDatasetTableMapRowId > @MapRowId))
)
BEGIN
SELECT TOP 1
@MapRowId = StandardDatasetTableMapRowId
,@TableGuid = TableGuid
,@TableNameSuffix = TableNameSuffix
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (InsertInd = 1)
AND (StandardDatasetTableMapRowId > @MapRowId)
ORDER BY StandardDatasetTableMapRowId
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting grooming of tables with suffix ' + @TableNameSuffix + ' aggregation type ' + CAST(@CurrentAggregationTypeId AS varchar(10)) + '. Method: delete rows'
SET @GroomingStartedDateTime = GETUTCDATE()
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished grooming of tables with suffix ' + @TableNameSuffix + ' aggregation type ' + CAST(@CurrentAggregationTypeId AS varchar(10))
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @GroomingStartedDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@OperationDurationMs = @OperationDurationMs
END
END
END
ELSE
BEGIN
-- run through optimized non-insert tables only
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (OptimizedInd = 1)
AND (InsertInd = 0)
AND (EndDateTime < @CutoffDateTime))
BEGIN
SELECT TOP 1 @TableNameSuffix = TableNameSuffix
FROM StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (OptimizedInd = 1)
AND (InsertInd = 0)
AND (EndDateTime < @CutoffDateTime)
ORDER BY StandardDatasetTableMapRowId
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting grooming of tables with suffix ' + @TableNameSuffix + '. Method: drop table'
SET @GroomingStartedDateTime = GETUTCDATE()
-- drop all dependent tables if any first
SET @StorageRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (DependentTableInd = 1)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
)
BEGIN
SELECT TOP 1
@BaseTableName = BaseTableName
,@StorageRowId = StandardDatasetAggregationStorageRowId
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StorageRowId)
AND (DependentTableInd = 1)
ORDER BY StandardDatasetAggregationStorageRowId
SET @Statement = 'DROP TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + @TableNameSuffix)
EXECUTE (@Statement)
END
-- drop main table
SELECT @BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (DependentTableInd = 0)
-- delete table map record
DELETE StandardDatasetTableMap
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
AND (TableNameSuffix = @TableNameSuffix)
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished grooming of tables with suffix ' + @TableNameSuffix
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @GroomingStartedDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@OperationDurationMs = @OperationDurationMs
END
END
END
UPDATE StandardDatasetAggregation
SET LastGroomingDateTime = @LastGroomingDateTime
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @CurrentAggregationTypeId)
EXEC StandardDatasetBuildCoverView
@DatasetId = @DatasetId
,@AggregationTypeId = @CurrentAggregationTypeId
END
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to groom data for standard data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @DebugMessageText
END
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetIndexDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetIndexDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetIndexDelete
@DatasetId uniqueidentifier
,@IndexGuid uniqueidentifier
,@DeleteIndexOnExistingTablesInd bit
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
BEGIN TRAN
DECLARE
@BaseTableName sysname
,@StandardDatasetAggregationStorageRowId int
,@AggregationTypeId int
,@StandardDatasetTableMapRowId int
,@TableNameSuffix varchar(50)
,@Statement nvarchar(1000)
,@IndexId int
,@IndexName sysname
,@TableName sysname
,@SchemaName sysname
SELECT @SchemaName = SchemaName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
SELECT
@BaseTableName = BaseTableName
,@StandardDatasetAggregationStorageRowId = s.StandardDatasetAggregationStorageRowId
,@AggregationTypeId = s.AggregationTypeId
FROM StandardDatasetAggregationStorageIndex i
JOIN StandardDatasetAggregationStorage s ON (s.StandardDatasetAggregationStorageRowId = i.StandardDatasetAggregationStorageRowId)
WHERE (i.IndexGuid = @IndexGuid)
AND (s.DatasetId = @DatasetId)
IF (@BaseTableName IS NOT NULL)
BEGIN
DELETE StandardDatasetAggregationStorageIndex
WHERE (IndexGuid = @IndexGuid)
AND (StandardDatasetAggregationStorageRowId = @StandardDatasetAggregationStorageRowId)
END
IF (@DeleteIndexOnExistingTablesInd = 1)
BEGIN
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
)
BEGIN
SELECT TOP 1
@StandardDatasetTableMapRowId = StandardDatasetTableMapRowId
,@TableNameSuffix = TableNameSuffix
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
ORDER BY StandardDatasetTableMapRowId
SET @IndexId = NULL
SET @IndexName = 'IX_CUSTOM_' + REPLACE(CAST(@IndexGuid AS varchar(100)), '-', '')
SET @TableName = @BaseTableName + '_' + @TableNameSuffix
SELECT @IndexId = indid
FROM sysindexes
WHERE ([name] = @IndexName)
AND (id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)))
IF (@IndexId IS NOT NULL)
BEGIN
SET @Statement = 'DROP INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
EXECUTE (@Statement)
END
END
END
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetIndexInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetIndexInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetIndexInsert
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@DependentTableInd tinyint
,@TableTag nvarchar(50)
,@UniqueInd bit
,@IndexGuid uniqueidentifier
,@IndexDefinition nvarchar(1000)
,@CreateIndexOnExistingTablesInd bit
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
BEGIN TRAN
DECLARE
@BaseTableName sysname
,@StandardDatasetAggregationStorageRowId int
,@Statement nvarchar(2000)
,@StandardDatasetTableMapRowId int
,@TableNameSuffix varchar(50)
,@IndexFileGroupName sysname
,@StandardDatasetAggregationStorageIndexRowId int
SELECT @SchemaName = SchemaName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
SELECT
@StandardDatasetAggregationStorageRowId = StandardDatasetAggregationStorageRowId
,@BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = @DependentTableInd)
AND ((TableTag IS NULL) AND (@TableTag IS NULL))
OR
(TableTag = @TableTag)
-- check if table was located
IF (@StandardDatasetAggregationStorageRowId IS NULL)
BEGIN
SELECT
@DatasetIdString = CAST(@DatasetId AS varchar(100))
,@TableTagString = CASE WHEN @TableTag IS NULL THEN '<NULL>' ELSE @TableTag END
RAISERROR (777971050, 16, 1
,@DatasetIdString
,@AggregationTypeId
,@DependentTableInd
,@TableTagString)
END
-- check if index already exists
IF EXISTS (SELECT *
FROM StandardDatasetAggregationStorageIndex
WHERE (StandardDatasetAggregationStorageRowId = @StandardDatasetAggregationStorageRowId)
AND (IndexGuid = @IndexGuid)
)
BEGIN
SELECT
@DatasetIdString = CAST(@DatasetId AS varchar(100))
,@TableTagString = CASE WHEN @TableTag IS NULL THEN '<NULL>' ELSE @TableTag END
,@IndexGuidString = CAST(@IndexGuid AS varchar(100))
RAISERROR (777971051, 16, 1
,@IndexGuidString
,@DatasetIdString
,@AggregationTypeId
,@DependentTableInd
,@TableTagString)
END
SELECT @IndexFileGroupName = ISNULL(IndexFileGroupName, 'default')
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
SET @StandardDatasetAggregationStorageIndexRowId = @@IDENTITY
IF (@CreateIndexOnExistingTablesInd = 1)
BEGIN
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
)
BEGIN
SELECT TOP 1
@StandardDatasetTableMapRowId = StandardDatasetTableMapRowId
,@TableNameSuffix = TableNameSuffix
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
ORDER BY StandardDatasetTableMapRowId
SET @Statement = 'CREATE ' + CASE @UniqueInd WHEN 1 THEN 'UNIQUE' ELSE '' END
+ ' INDEX [IX_CUSTOM_' + REPLACE(CAST(@IndexGuid AS varchar(100)), '-', '') + ']'
+ ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + @TableNameSuffix)
+ REPLACE(@IndexDefinition, '''', '''''')
+ ' ON ' + QUOTENAME(@IndexFileGroupName)
EXECUTE (@Statement)
-- indicate no optimization is nesessary on this index
INSERT StandardDatasetOptimizationHistory (
StandardDatasetTableMapRowId
,StandardDatasetAggregationStorageIndexRowId
,OptimizationStartDateTime
,OptimizationDurationSeconds
)
VALUES (
@StandardDatasetTableMapRowId
,@StandardDatasetAggregationStorageIndexRowId
,GETUTCDATE()
,0
)
END
END
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetMaintenance')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetMaintenance AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetMaintenance
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@LockResourceName sysname
,@ExecResult int
,@LockSetInd bit
,@StartDateTime datetime
,@MaintenanceWorkAttemptedInd bit
,@LastOptimizationActionSuccessfulCompletionDateTime datetime
SET @StartDateTime = GETUTCDATE()
SET @MaintenanceWorkAttemptedInd = 0
SELECT @LastOptimizationActionSuccessfulCompletionDateTime = LastOptimizationActionSuccessfulCompletionDateTime
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- process staging area on every call
EXEC StandardDatasetProcessStaging @DatasetId
-- do optimization no more frequently then every 60 seconds
IF EXISTS (SELECT *
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
AND (DATEDIFF(second, LastOptimizationActionDateTime, GETUTCDATE()) > 20)
)
BEGIN
BEGIN TRY
-- set lock to make sure only one process
-- performs aggregations on this data set
SET @LockResourceName = /*CAST(@DatasetId AS varchar(50)) + */'Dataset_Maintenance'
IF (@ExecResult < -1)
BEGIN
RAISERROR(777971001, 16, 1, 'StandardDatasetMaintenance', @ExecResult)
END
-- lock timeout - some other process performing aggregations
IF (@ExecResult = -1)
BEGIN
RETURN
END
SET @LockSetInd = 1
UPDATE StandardDataset
SET LastOptimizationActionDateTime = GETUTCDATE()
WHERE (DatasetId = @DatasetId)
AND (DATEDIFF(second, LastOptimizationActionDateTime, GETUTCDATE()) > 20)
IF (@@ROWCOUNT > 0)
BEGIN
IF (ABS(DATEDIFF(second, @StartDateTime, GETUTCDATE())) < 30)
BEGIN
EXEC StandardDatasetGroom @DatasetId
EXEC StandardDatasetAllocateStorage @DatasetId, 0
IF (ABS(DATEDIFF(second, @StartDateTime, GETUTCDATE())) < 30)
BEGIN
EXEC StandardDatasetOptimize @DatasetId
IF (ABS(DATEDIFF(second, @StartDateTime, GETUTCDATE())) < 30)
BEGIN
EXEC StandardDatasetAggregate @DatasetId
END
END
END
END
-- we sucessfully tried to do maintenance work beyond staging area processint
-- we'll report that to the caller
SET @MaintenanceWorkAttemptedInd = 1
UPDATE StandardDataset
SET @LastOptimizationActionSuccessfulCompletionDateTime = LastOptimizationActionSuccessfulCompletionDateTime = GETUTCDATE()
WHERE DatasetId = @DatasetId
IF (@LockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @LockResourceName
,@LockOwner = 'Session'
SET @LockSetInd = 0
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@LockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @LockResourceName
,@LockOwner = 'Session'
END
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END CATCH
END
-- return information to caller
SELECT
MaintenanceWorkAttemptedInd = @MaintenanceWorkAttemptedInd
,LastOptimizationActionSuccessfulCompletionDateTime = @LastOptimizationActionSuccessfulCompletionDateTime
END
GO
GRANT EXECUTE ON StandardDatasetMaintenance TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetOptimize')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetOptimize AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetOptimize
@DatasetId uniqueidentifier
,@MinAvgFragmentationInPercentToOptimize int = 10
,@MinAvgFragmentationInPercentToRebuild int = 30
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@DebugLevel tinyint
,@DebugMessage nvarchar(max)
,@StandardDatasetTableMapRowId int
,@TableNameSuffix varchar(50)
,@StandardDatasetAggregationStorageRowId int
,@AggregationTypeId int
,@FoundNonoptimalIndexInd bit
,@TableName sysname
,@TableNameWithSchema nvarchar(1000)
,@IndexName sysname
,@PrimaryKeyInd bit
,@AvgFragmentationInPercent float
,@IndexPageCount bigint
,@StandardDatasetOptimizationHistoryRowId bigint
,@OnlineRebuildPossibleInd bit
,@CanPerformBlockingOptimizationInd bit
,@OptimizationStartDateTime datetime
,@IndexOptimized bit
,@IndexRebuild bit
,@InsertInd bit
,@OperationDurationMs bigint
,@MainTableName sysname
,@Statement nvarchar(max)
,@EffectiveStatement nvarchar(max)
,@StandardDatasetAggregationStorageIndexRowId int
,@IndexId int
,@AfterAvgFragmentationInPercent float
,@OnlineRebuildInd bit
,@SchemaName nvarchar(256)
,@BlockingMaintenanceStartTime char(5)
,@BlockingMaintenanceDurationMinutes int
,@StatisticsMaxAgeHours int
,@StatisticsUpdateSamplePercentage int
,@StatisticsUpdateStartDateTime datetime
,@StatisticsUpdateDurationSeconds int
,@StatisticUpdatedInd bit
,@StatisticsUpdateMethod varchar(50)
,@LockResourceName sysname
,@ExecResult int
,@LockSetInd bit
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
,@BlockingMaintenanceStartTime = BlockingMaintenanceDailyStartTime
,@BlockingMaintenanceDurationMinutes = BlockingMaintenanceDurationMinutes
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- set lock to make sure only one process
-- performs optimization on this data set
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_Optimize'
--********************************************************
-- Insert new optimization work items
-- groom optimization history
DELETE StandardDatasetOptimizationHistory
WHERE (OptimizationDurationSeconds IS NOT NULL)
AND (OptimizationStartDateTime < DATEADD(day, -7, GETUTCDATE()))
-- add indexes for optimization
INSERT StandardDatasetOptimizationHistory (StandardDatasetTableMapRowId, StandardDatasetAggregationStorageIndexRowId)
SELECT m.StandardDatasetTableMapRowId, i.StandardDatasetAggregationStorageIndexRowId
FROM StandardDatasetTableMap m
JOIN StandardDatasetAggregationStorage s ON (m.DatasetId = s.DatasetId) AND (m.AggregationTypeId = s.AggregationTypeId)
JOIN StandardDatasetAggregationStorageIndex i ON (s.StandardDatasetAggregationStorageRowId = i.StandardDatasetAggregationStorageRowId)
JOIN StandardDatasetAggregation a ON (m.DatasetId = a.DatasetId) AND (m.AggregationTypeId = a.AggregationTypeId)
WHERE (m.DatasetId = @DatasetId)
AND ((m.OptimizedInd = 0) OR (m.InsertInd = 1))
AND (NOT EXISTS (SELECT *
FROM StandardDatasetOptimizationHistory
WHERE (StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId)
AND (StandardDatasetAggregationStorageIndexRowId = i.StandardDatasetAggregationStorageIndexRowId)
AND ((OptimizationDurationSeconds IS NULL)
OR
(OptimizationStartDateTime > DATEADD(minute, -a.IndexOptimizationIntervalMinutes, GETUTCDATE()))
)
)
)
--********************************************************
-- set "optimized" property on optimized tables
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap m
JOIN StandardDatasetAggregationStorage s ON (m.DatasetId = s.DatasetId) AND (m.AggregationTypeId = s.AggregationTypeId)
LEFT JOIN StandardDatasetAggregationStorageIndex i ON (s.StandardDatasetAggregationStorageRowId = i.StandardDatasetAggregationStorageRowId)
WHERE (m.DatasetId = @DatasetId)
AND (m.OptimizedInd = 0)
AND (m.InsertInd = 0)
AND (m.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (NOT EXISTS (SELECT *
FROM StandardDatasetOptimizationHistory
WHERE (StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId)
AND (StandardDatasetAggregationStorageIndexRowId = i.StandardDatasetAggregationStorageIndexRowId)
AND (OptimizationStartDateTime IS NULL)
)
)
)
BEGIN
SELECT TOP 1
@StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId
,@TableNameSuffix = m.TableNameSuffix
,@AggregationTypeId = m.AggregationTypeId
,@InsertInd = m.InsertInd
,@StatisticsMaxAgeHours = a.StatisticsMaxAgeHours
FROM StandardDatasetTableMap m
JOIN StandardDatasetAggregationStorage s ON (m.DatasetId = s.DatasetId) AND (m.AggregationTypeId = s.AggregationTypeId)
JOIN StandardDatasetAggregation a ON (m.DatasetId = a.DatasetId) AND (m.AggregationTypeId = a.AggregationTypeId)
LEFT JOIN StandardDatasetAggregationStorageIndex i ON (s.StandardDatasetAggregationStorageRowId = i.StandardDatasetAggregationStorageRowId)
WHERE (m.DatasetId = @DatasetId)
AND (m.OptimizedInd = 0)
AND (m.InsertInd = 0)
AND (m.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (NOT EXISTS (SELECT *
FROM StandardDatasetOptimizationHistory
WHERE (StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId)
AND (StandardDatasetAggregationStorageIndexRowId = i.StandardDatasetAggregationStorageIndexRowId)
AND (OptimizationStartDateTime IS NULL)
)
)
ORDER BY m.StandardDatasetTableMapRowId
-- scroll through storage tables
SET @StandardDatasetAggregationStorageRowId = 0
SET @FoundNonoptimalIndexInd = 0
SET @MainTableName = NULL
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationStorageRowId = StandardDatasetAggregationStorageRowId
,@TableName = BaseTableName + '_' + @TableNameSuffix
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
ORDER BY DependentTableInd, StandardDatasetAggregationStorageRowId
IF (@MainTableName IS NULL)
SET @MainTableName = @TableName
-- run through all registered indexes to see if they need reorg or rebuild
SET @StandardDatasetAggregationStorageIndexRowId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorageIndex
WHERE (StandardDatasetAggregationStorageRowId = @StandardDatasetAggregationStorageRowId)
AND (StandardDatasetAggregationStorageIndexRowId > @StandardDatasetAggregationStorageIndexRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationStorageIndexRowId = StandardDatasetAggregationStorageIndexRowId
,@IndexName = 'IX_CUSTOM_' + REPLACE(CAST(IndexGuid AS varchar(100)), '-', '')
,@PrimaryKeyInd = PrimaryKeyInd
FROM StandardDatasetAggregationStorageIndex
WHERE (StandardDatasetAggregationStorageRowId = @StandardDatasetAggregationStorageRowId)
AND (StandardDatasetAggregationStorageIndexRowId > @StandardDatasetAggregationStorageIndexRowId)
ORDER BY StandardDatasetAggregationStorageIndexRowId
SET @IndexId = NULL
SET @TableNameWithSchema = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
IF (@PrimaryKeyInd = 1)
BEGIN
SET @IndexId = 1
END
ELSE
BEGIN
SELECT @IndexId = indid
FROM sysindexes
WHERE ([name] = @IndexName)
AND (id = OBJECT_ID(@TableNameWithSchema))
END
IF (@IndexId IS NOT NULL)
BEGIN
SELECT
@AvgFragmentationInPercent = avg_fragmentation_in_percent
,@IndexPageCount = page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@TableNameWithSchema), @IndexId, NULL, NULL)
WHERE (alloc_unit_type_desc = 'IN_ROW_DATA') -- exclude blobs
-- do not consider index for optimization
-- we it only has 1 page of data
IF (@IndexPageCount > 1)
BEGIN
IF (@AvgFragmentationInPercent >= @MinAvgFragmentationInPercentToOptimize)
BEGIN
-- check to see if we optimized this index
-- within last 2 optimization intervals and
-- it did not reduce fragmentation
IF (NOT EXISTS (SELECT *
FROM StandardDatasetOptimizationHistory oh
JOIN StandardDatasetTableMap m ON (oh.StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId)
JOIN StandardDatasetAggregation a ON (m.DatasetId = a.DatasetId) AND (m.AggregationTypeId = a.AggregationTypeId)
WHERE (oh.StandardDatasetTableMapRowId = @StandardDatasetTableMapRowId)
AND (oh.StandardDatasetAggregationStorageIndexRowId = @StandardDatasetAggregationStorageIndexRowId)
AND (oh.OptimizationStartDateTime > DATEADD(minute, -2 * a.IndexOptimizationIntervalMinutes, GETUTCDATE()))
AND (oh.AfterAvgFragmentationInPercent >= oh.BeforeAvgFragmentationInPercent)
AND (oh.StandardDatasetOptimizationHistoryRowId = (SELECT TOP 1 StandardDatasetOptimizationHistoryRowId
FROM StandardDatasetOptimizationHistory
WHERE (StandardDatasetTableMapRowId = @StandardDatasetTableMapRowId)
AND (StandardDatasetAggregationStorageIndexRowId = @StandardDatasetAggregationStorageIndexRowId)
AND (OptimizationStartDateTime IS NOT NULL)
ORDER BY CreatedDateTime DESC
))
)
)
BEGIN
SET @FoundNonoptimalIndexInd = 1
BREAK
END
END
-- check stats update date
IF (STATS_DATE(OBJECT_ID(@TableNameWithSchema), @IndexId) IS NULL)
OR
(STATS_DATE(OBJECT_ID(@TableNameWithSchema), @IndexId) < DATEADD(hour, -@StatisticsMaxAgeHours, GETDATE()))
BEGIN
SET @FoundNonoptimalIndexInd = 1
BREAK
END
END
END
END
IF (@FoundNonoptimalIndexInd = 1) BREAK
END
IF (@FoundNonoptimalIndexInd = 0) AND (@InsertInd = 0)
BEGIN
SET @Statement =
'UPDATE StandardDatasetTableMap'
+ ' SET OptimizedInd = 1'
+ ' ,StartDateTime = ISNULL((SELECT MIN([DateTime]) FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName) + ' (TABLOCK)), DATEADD(month, -1, GETUTCDATE()))'
+ ' ,EndDateTime = ISNULL((SELECT MAX([DateTime]) FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName) + ' (TABLOCK)), DATEADD(month, -1, GETUTCDATE()))'
+ ' WHERE (StandardDatasetTableMapRowId = ' + CAST (@StandardDatasetTableMapRowId AS varchar(15)) + ')'
EXECUTE(@Statement)
-- remove optimized partitions where Start/End times are NULL
-- indicating we ran into a bug where partitions with 0 records
-- when optimized are left with NULL in Start/End times
SET @Statement =
'UPDATE StandardDatasetTableMap'
+ ' SET StartDateTime = DATEADD(month, -1, GETUTCDATE())'
+ ' ,EndDateTime = DATEADD(month, -1, GETUTCDATE())'
+ ' WHERE (OptimizedInd = 1)'
+ ' AND (InsertInd = 0)'
+ ' AND (StartDateTime IS NULL)'
+ ' AND (EndDateTime IS NULL)'
EXECUTE(@Statement)
-- create check constraint on the table to
-- ensure optimizer can use it
DECLARE
@TableStartDateTime datetime
,@TableEndDateTime datetime
SELECT
@TableStartDateTime = StartDateTime
,@TableEndDateTime = EndDateTime
FROM StandardDatasetTableMap
WHERE (StandardDatasetTableMapRowId = @StandardDatasetTableMapRowId)
SET @Statement = ' IF EXISTS (SELECT * FROM sys.check_constraints WHERE name = ''CHK_DateTime_' + @MainTableName + ''' AND parent_object_id = OBJECT_ID(''' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName) + '''))'
+ ' ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName)
+ ' DROP CONSTRAINT ' + QUOTENAME('CHK_DateTime_' + @MainTableName)
+ ' ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName) + ' WITH NOCHECK'
+ ' ADD CONSTRAINT ' + QUOTENAME('CHK_DateTime_' + @MainTableName) + ' CHECK ([DateTime] BETWEEN CONVERT(datetime, ''' + CONVERT(varchar(100), @TableStartDateTime, 121) + ''', 121)'
+ ' AND CONVERT(datetime, ''' + CONVERT(varchar(100), @TableEndDateTime, 121) + ''', 121))'
EXECUTE (@Statement)
-- end date may be far in the future
-- due to multiple reasons
-- set table map date to "today" if
-- it is far in the future, but leave
-- constraints "real"
IF (@TableEndDateTime > DATEADD(month, 1, GETUTCDATE()))
BEGIN
UPDATE StandardDatasetTableMap
SET EndDateTime = GETUTCDATE()
WHERE (StandardDatasetTableMapRowId = @StandardDatasetTableMapRowId)
END
END
END
--********************************************************
-- Optimize next index in queue
SET @StandardDatasetOptimizationHistoryRowId = 0
SET @IndexOptimized = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetOptimizationHistory h
JOIN StandardDatasetAggregationStorageIndex i ON (h.StandardDatasetAggregationStorageIndexRowId = i.StandardDatasetAggregationStorageIndexRowId)
JOIN StandardDatasetAggregationStorage s ON (s.StandardDatasetAggregationStorageRowId = i.StandardDatasetAggregationStorageRowId)
JOIN StandardDatasetTableMap m ON (s.DatasetId = m.DatasetId) AND (s.AggregationTypeId = m.AggregationTypeId)
WHERE (m.DatasetId = @DatasetId)
AND (h.StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId)
AND (h.OptimizationStartDateTime IS NULL)
AND (StandardDatasetOptimizationHistoryRowId > @StandardDatasetOptimizationHistoryRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetOptimizationHistoryRowId = h.StandardDatasetOptimizationHistoryRowId
,@StandardDatasetAggregationStorageIndexRowId = h.StandardDatasetAggregationStorageIndexRowId
,@TableName = s.BaseTableName + '_' + m.TableNameSuffix
,@InsertInd = m.InsertInd
,@IndexName = 'IX_CUSTOM_' + REPLACE(CAST(i.IndexGuid AS varchar(100)), '-', '')
,@PrimaryKeyInd = i.PrimaryKeyInd
,@StatisticsMaxAgeHours = a.StatisticsMaxAgeHours
,@StatisticsUpdateSamplePercentage = a.StatisticsUpdateSamplePercentage
,@OnlineRebuildPossibleInd =
CASE
WHEN ISNULL(i.OnlineRebuildPossibleInd, 1) = 1 THEN 1
WHEN DATEADD(day, 1, ISNULL(i.OnlineRebuildLastPerformedDateTime, '19000101')) < GETUTCDATE() THEN 1
ELSE 0
END
,@CanPerformBlockingOptimizationInd =
CASE
WHEN (DATEDIFF(minute, CONVERT(char(8), GETDATE(), 112) + ' ' + d.BlockingMaintenanceDailyStartTime, GETDATE()) < d.BlockingMaintenanceDurationMinutes)
THEN 1
ELSE 0
END
FROM StandardDatasetOptimizationHistory h
JOIN StandardDatasetAggregationStorageIndex i ON (h.StandardDatasetAggregationStorageIndexRowId = i.StandardDatasetAggregationStorageIndexRowId)
JOIN StandardDatasetAggregationStorage s ON (s.StandardDatasetAggregationStorageRowId = i.StandardDatasetAggregationStorageRowId)
JOIN StandardDatasetTableMap m ON (s.DatasetId = m.DatasetId) AND (s.AggregationTypeId = m.AggregationTypeId)
JOIN StandardDatasetAggregation a ON (s.DatasetId = a.DatasetId) AND (s.AggregationTypeId = a.AggregationTypeId)
JOIN StandardDataset d ON (d.DatasetId = m.DatasetId)
WHERE (m.DatasetId = @DatasetId)
AND (h.StandardDatasetTableMapRowId = m.StandardDatasetTableMapRowId)
AND (h.OptimizationStartDateTime IS NULL)
AND (StandardDatasetOptimizationHistoryRowId > @StandardDatasetOptimizationHistoryRowId)
ORDER BY h.CreatedDateTime
SET @IndexId = NULL
SET @TableNameWithSchema = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
IF (@PrimaryKeyInd = 1)
BEGIN
SELECT @IndexId = 1
SELECT @IndexName = i.name
FROM sys.indexes i
WHERE (index_id = 1)
AND (object_id = OBJECT_ID(@TableNameWithSchema))
END
ELSE
BEGIN
SELECT @IndexId = indid
FROM sysindexes
WHERE ([name] = @IndexName)
AND (id = OBJECT_ID(@TableNameWithSchema))
END
IF (@IndexId IS NOT NULL)
BEGIN
SELECT @AvgFragmentationInPercent = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@TableNameWithSchema), @IndexId, NULL, NULL)
WHERE (alloc_unit_type_desc = 'IN_ROW_DATA') -- exclude blobs
END
SET @OptimizationStartDateTime = GETUTCDATE()
IF ((@IndexId IS NULL) OR (@AvgFragmentationInPercent < @MinAvgFragmentationInPercentToOptimize))
BEGIN
-- don't optimize indexes with low fragmentation
UPDATE StandardDatasetOptimizationHistory
SET OptimizationStartDateTime = @OptimizationStartDateTime
,OptimizationDurationSeconds = 0
,BeforeAvgFragmentationInPercent = @AvgFragmentationInPercent
,AfterAvgFragmentationInPercent = @AvgFragmentationInPercent
,OptimizationMethod = CASE WHEN @IndexId IS NULL THEN 'index doesn''t exist' ELSE 'no optimization' END
WHERE (StandardDatasetOptimizationHistoryRowId = @StandardDatasetOptimizationHistoryRowId)
END
ELSE
BEGIN
SET @Statement = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
SET @IndexRebuild = 1
IF ((@AvgFragmentationInPercent > @MinAvgFragmentationInPercentToRebuild) OR (@InsertInd = 0))
BEGIN
SET @OnlineRebuildInd = 0
-- try online rebuild if possible
IF (@OnlineRebuildPossibleInd = 1)
BEGIN
BEGIN TRY
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting online optimization (rebuild) of table ' + @TableName + ' index ' + @IndexName
SET @EffectiveStatement = @Statement + ' REBUILD WITH (ONLINE=ON, FILLFACTOR=' + CASE @InsertInd WHEN 0 THEN '100' ELSE '80' END + ')'
EXECUTE (@EffectiveStatement)
SET @IndexOptimized = 1
SET @OnlineRebuildInd = 1
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished online optimization (rebuild) of table ' + @TableName + ' index ' + @IndexName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @OptimizationStartDateTime))
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Online rebuild failed for table ' + @TableName + ' index ' + @IndexName
+ '. Error number ' + CAST(@ErrorNumber AS varchar(10))
+ ' Message: ' + @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
END
END CATCH
UPDATE StandardDatasetAggregationStorageIndex
SET OnlineRebuildPossibleInd = @IndexOptimized
,OnlineRebuildLastPerformedDateTime = @OptimizationStartDateTime
WHERE (StandardDatasetAggregationStorageIndexRowId = @StandardDatasetAggregationStorageIndexRowId)
END
-- when online rebuild fails, perform blocking rebuild or reorg
IF (@IndexOptimized = 0)
BEGIN
IF (@CanPerformBlockingOptimizationInd = 1)
BEGIN
SET @EffectiveStatement = @Statement + ' REBUILD WITH (FILLFACTOR=' + CASE @InsertInd WHEN 0 THEN '100' ELSE '80' END + ')'
SET @IndexRebuild = 1
END
ELSE
BEGIN
IF (@InsertInd = 1)
BEGIN
SET @EffectiveStatement = @Statement + ' REORGANIZE'
SET @IndexRebuild = 0
END
ELSE
BEGIN
-- don't do "can't do rebuild - reorg instead" on a non-insert table
-- to avoid endless fruitless reorgs
SET @EffectiveStatement = NULL
END
END
END
ELSE
BEGIN
SET @EffectiveStatement = NULL
END
END
ELSE
BEGIN
-- reorg
SET @EffectiveStatement = @Statement + ' REORGANIZE'
SET @IndexRebuild = 0
END
IF (@EffectiveStatement IS NOT NULL)
BEGIN
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting optimization of table ' + @TableName + ' index ' + @IndexName
SET @DebugMessage = @DebugMessage + '. Method: index ' + CASE @IndexRebuild WHEN 0 THEN 'reorg' ELSE 'rebuild' END
EXECUTE(@EffectiveStatement)
SET @IndexOptimized = 1
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished optimization of table ' + @TableName + ' index ' + @IndexName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @OptimizationStartDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@OperationDurationMs = @OperationDurationMs
END
END
END
IF (@IndexOptimized = 1)
BEGIN
SELECT @AfterAvgFragmentationInPercent = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@TableNameWithSchema), @IndexId, NULL, NULL)
WHERE (alloc_unit_type_desc = 'IN_ROW_DATA') -- exclude blobs
UPDATE StandardDatasetOptimizationHistory
SET OptimizationStartDateTime = @OptimizationStartDateTime
,OptimizationDurationSeconds = ABS(DATEDIFF(second, @OptimizationStartDateTime, GETUTCDATE()))
,BeforeAvgFragmentationInPercent = @AvgFragmentationInPercent
,AfterAvgFragmentationInPercent = @AfterAvgFragmentationInPercent
,OptimizationMethod = CASE @IndexRebuild
WHEN 1 THEN CASE @OnlineRebuildInd
WHEN 1 THEN 'online'
ELSE 'offline'
END + ' rebuild'
ELSE 'reorganize'
END
WHERE (StandardDatasetOptimizationHistoryRowId = @StandardDatasetOptimizationHistoryRowId)
BREAK
END
-- check/update statistics on the index
SET @StatisticUpdatedInd = 0
IF (@IndexId IS NOT NULL)
BEGIN
-- check this index stats
IF (STATS_DATE(OBJECT_ID(@TableNameWithSchema), @IndexId) < DATEADD(hour, -@StatisticsMaxAgeHours, GETDATE()))
OR
(STATS_DATE(OBJECT_ID(@TableNameWithSchema), @IndexId) IS NULL)
BEGIN
SET @Statement = 'UPDATE STATISTICS ' + @TableNameWithSchema + ' ' + QUOTENAME(@IndexName)
SET @StatisticsUpdateMethod = 'update stats'
IF (@StatisticsUpdateSamplePercentage > 0)
BEGIN
IF (@StatisticsUpdateSamplePercentage >= 100)
BEGIN
SET @Statement = @Statement + ' WITH FULLSCAN'
SET @StatisticsUpdateMethod = @StatisticsUpdateMethod + ' fullscan'
END
ELSE
BEGIN
SET @Statement = @Statement + ' WITH SAMPLE ' + CAST(@StatisticsUpdateSamplePercentage AS varchar(10)) + ' PERCENT'
SET @StatisticsUpdateMethod = @StatisticsUpdateMethod + ' sample ' + CAST(@StatisticsUpdateSamplePercentage AS varchar(10)) + '%'
END
END
SET @StatisticsUpdateStartDateTime = GETUTCDATE()
EXECUTE (@Statement)
SET @StatisticsUpdateDurationSeconds = ABS(DATEDIFF(second, @StatisticsUpdateStartDateTime, GETUTCDATE()))
-- do not consider quick stats updates
IF (@StatisticsUpdateDurationSeconds > 0)
BEGIN
SET @StatisticUpdatedInd = 1
END
INSERT StandardDatasetOptimizationHistory (
StandardDatasetTableMapRowId
,StandardDatasetAggregationStorageIndexRowId
,OptimizationStartDateTime
,OptimizationDurationSeconds
,BeforeAvgFragmentationInPercent
,AfterAvgFragmentationInPercent
,OptimizationMethod
)
SELECT
StandardDatasetTableMapRowId
,StandardDatasetAggregationStorageIndexRowId
,@StatisticsUpdateStartDateTime
,@StatisticsUpdateDurationSeconds
,BeforeAvgFragmentationInPercent
,AfterAvgFragmentationInPercent
,@StatisticsUpdateMethod
FROM StandardDatasetOptimizationHistory
WHERE (StandardDatasetOptimizationHistoryRowId = @StandardDatasetOptimizationHistoryRowId)
END
END
-- do only one lengthy operation
IF ((@IndexOptimized = 1) OR (@StatisticUpdatedInd = 1))
BEGIN
BREAK
END
END
IF (@LockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @LockResourceName
,@LockOwner = 'Session'
SET @LockSetInd = 0
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@LockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @LockResourceName
,@LockOwner = 'Session'
END
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to optimize data for standard data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @DebugMessageText
END
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StandardDatasetProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StandardDatasetProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StandardDatasetProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@StagingLockSetInd bit
,@StagingLockResourceName sysname
,@ExecResult int
,@DebugLevel int
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
FROM StandardDataset
WHERE DatasetId = @DatasetId
DECLARE @StagingProcessingSpName sysname
SELECT @StagingProcessingSpName = StagingProcessorStoredProcedureName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
IF (@StagingProcessingSpName IS NOT NULL)
BEGIN
-- lock staging to ensure only one process does the transfer
SET @StagingLockResourceName = CAST(@DatasetId AS varchar(50)) + '_Staging'
-- cleanup
IF (@StagingLockSetInd = 1)
BEGIN
EXEC @ExecResult = sp_releaseapplock
@Resource = @StagingLockResourceName
,@LockOwner = 'Session'
SET @StagingLockSetInd = 0
END
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process stagingarea for data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
SET NOCOUNT ON
-- set "end date time" to "today"
-- where it is far in the future
UPDATE StandardDatasetTableMap
SET EndDateTime = GETUTCDATE()
WHERE (EndDateTime > DATEADD(month, 1, GETUTCDATE()))
-- set up PKs of existing tables for optimization
INSERT StandardDatasetAggregationStorageIndex (
StandardDatasetAggregationStorageRowId
,PrimaryKeyInd
,UniqueInd
)
SELECT
s.StandardDatasetAggregationStorageRowId
,1
,1
FROM StandardDatasetAggregationStorage s
WHERE (TableTemplate LIKE '%primary key%')
AND NOT EXISTS (SELECT *
FROM StandardDatasetAggregationStorageIndex
WHERE (PrimaryKeyInd = 1)
AND (StandardDatasetAggregationStorageRowId = s.StandardDatasetAggregationStorageRowId)
)
GO
-- drop check constraints and set tables
-- to rebuild it using regular rout (StdDSOptimize SP)
-- clean up orphan tables omitted by grooming
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@DatasetId uniqueidentifier
,@SchemaName sysname
,@BaseTableName sysname
,@TableNameSuffix sysname
,@MainTableName sysname
,@Statement nvarchar(max)
,@AggregationTypeId int
,@TableMapRowId int
,@ObjectId int
,@DependentTableInd int
BEGIN TRY
-- go through all datasets
SET @SchemaName = ''
WHILE EXISTS (SELECT *
FROM StandardDataset d
WHERE (d.SchemaName > @SchemaName)
)
BEGIN
SELECT TOP 1
@SchemaName = d.SchemaName
,@DatasetId = d.DatasetId
FROM StandardDataset d
WHERE (d.SchemaName > @SchemaName)
ORDER BY d.SchemaName
-- go through all aggregations
SET @AggregationTypeId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation a
WHERE (a.DatasetId = @DatasetId)
AND (a.AggregationTypeId > @AggregationTypeId)
)
BEGIN
SELECT TOP 1
@AggregationTypeId = a.AggregationTypeId
FROM StandardDatasetAggregation a
WHERE (a.DatasetId = @DatasetId)
AND (a.AggregationTypeId > @AggregationTypeId)
ORDER BY a.AggregationTypeId
-- get base table name for main table
SELECT
@BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
-- go through all main tables for this dataset and aggregation type
SET @TableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap m
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetTableMapRowId > @TableMapRowId)
AND (OptimizedInd = 1)
)
BEGIN
SELECT TOP 1
@TableMapRowId = StandardDatasetTableMapRowId
,@TableNameSuffix = TableNameSuffix
FROM StandardDatasetTableMap m
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (StandardDatasetTableMapRowId > @TableMapRowId)
AND (OptimizedInd = 1)
ORDER BY StandardDatasetTableMapRowId
SET @MainTableName = @BaseTableName + '_' + @TableNameSuffix
IF EXISTS (SELECT *
FROM sys.check_constraints
WHERE (name = 'CHK_DateTime_' + @MainTableName)
AND (parent_object_id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName)))
)
BEGIN
-- constraint found - drop it and set table 'non optimized'
SET @Statement = ' ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName)
+ ' DROP CONSTRAINT ' + QUOTENAME('CHK_DateTime_' + @MainTableName)
EXECUTE (@Statement)
UPDATE StandardDatasetTableMap
SET OptimizedInd = 0
WHERE (StandardDatasetTableMapRowId = @TableMapRowId)
END
END
END
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
Home
CHS