DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText varchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
INSERT StandardDatasetAggregation
(
DatasetId
,AggregationTypeId
,AggregationIntervalDurationMinutes
,AggregationStartDelayMinutes
,BuildAggregationStoredProcedureName
,DeleteAggregationStoredProcedureName
,GroomStoredProcedureName
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,LastGroomingDateTime
,DataFileGroupName
,IndexFileGroupName
)
SELECT
'$Config/DatasetId$'
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Subhourly' THEN 10
WHEN 'Hourly' THEN 20
WHEN 'Daily' THEN 30
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
WHEN 'Subhourly' THEN AggregationIntervalDurationMinutes
WHEN 'Hourly' THEN 60
WHEN 'Daily' THEN 24*60
END
,AggregationStartDelayMinutes
,'PerformanceAggregate'
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'PerformanceAggregationDelete'
END
,'PerformanceGroom'
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,GETUTCDATE()
,DataFileGroupName
,IndexFileGroupName
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname'
,MaxTableRowCount int
,MaxTableSizeKb int
,AggregationStartDelayMinutes int
,MaxDataAgeDays int
,GroomingIntervalMinutes int
,MaxRowsToGroom int
,IndexOptimizationIntervalMinutes int
,DataFileGroupName nvarchar(256)
,IndexFileGroupName nvarchar(256)
,AggregationIntervalDurationMinutes int
)
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
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
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
SELECT
'$Config/DatasetId$'
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Subhourly' THEN 10
WHEN 'Hourly' THEN 20
WHEN 'Daily' THEN 30
END
,BaseTableName
,0
,CASE AggregationType
WHEN 'Raw' THEN '
CREATE TABLE ' + QUOTENAME(d.SchemaName) + '.' + QUOTENAME(BaseTableName + '_$Guid$') + '
(
' + QUOTENAME(BaseTableName + 'RowId') + ' bigint NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,PerformanceRuleInstanceRowId int NOT NULL
,ManagedEntityRowId int NOT NULL
,SampleValue float(53) NULL
,CONSTRAINT ' + QUOTENAME('PK_' + BaseTableName + '_$Guid$') + ' PRIMARY KEY CLUSTERED ('
+ QUOTENAME(BaseTableName + 'RowId') + ') ON $DataFileGroupName$
) ON $DataFileGroupName$
'
ELSE '
CREATE TABLE ' + QUOTENAME(d.SchemaName) + '.' + QUOTENAME(BaseTableName + '_$Guid$') + '
(
' + QUOTENAME(BaseTableName + 'RowId') + ' bigint NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,PerformanceRuleInstanceRowId int NOT NULL
,ManagedEntityRowId int NOT NULL
,SampleCount int NOT NULL
,AverageValue float(53) NOT NULL
,MinValue float(53) NOT NULL
,MaxValue float(53) NOT NULL
,StandardDeviation float(53) NOT NULL
,CONSTRAINT ' + QUOTENAME('PK_' + BaseTableName + '_$Guid$') + ' PRIMARY KEY CLUSTERED ('
+ QUOTENAME(BaseTableName + 'RowId') + ') ON $DataFileGroupName$
) ON $DataFileGroupName$
'
END
,CASE AggregationType
WHEN 'Raw' THEN '
SELECT
[DateTime]
,PerformanceRuleInstanceRowId
,ManagedEntityRowId
,SampleValue
'
ELSE '
SELECT
[DateTime]
,PerformanceRuleInstanceRowId
,ManagedEntityRowId
,SampleCount
,AverageValue
,MinValue
,MaxValue
,StandardDeviation
'
END
,MaxTableRowCount
,MaxTableSizeKb
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname'
,BaseTableName varchar(256)
,MaxTableRowCount int
,MaxTableSizeKb int
,DataFileGroupName varchar(256)
,IndexFileGroupName varchar(256)
)
CROSS JOIN StandardDataset d
WHERE (d.DatasetId = '$Config/DatasetId$')
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
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
GO
SET NOCOUNT ON
GO
-- create [DateTime] field indexes for all aggregation levels
DECLARE
@AggregationTypeId int
,@Statement nvarchar(max)
,@IndeGuid uniqueidentifier
SET @AggregationTypeId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
)
BEGIN
SELECT @IndeGuid = NEWID()
-- Hourly data index to support Top N reports and dashboards
EXEC StandardDatasetIndexDelete
@DatasetId = '$Config/DatasetId$'
,@IndexGuid = '04DE2C08-6203-4DA1-8EED-A0984463C46E'
,@DeleteIndexOnExistingTablesInd = 0
-- Daily data index to support Top N reports and dashboards
EXEC StandardDatasetIndexDelete
@DatasetId = '$Config/DatasetId$'
,@IndexGuid = '110C2981-0D21-4C6E-8185-49997A2BA0B5'
,@DeleteIndexOnExistingTablesInd = 0
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = 'Perf')
BEGIN
EXECUTE('CREATE SCHEMA Perf')
END
GO
-- change schema ownership to allow index rebuild and reorganize
ALTER AUTHORIZATION ON SCHEMA::Perf TO OpsMgrWriter
GO
-- change schema alter to allow domain table index rebuild
GRANT ALTER ON SCHEMA::dbo TO OpsMgrWriter
GO
-- grant create table permissions
GRANT CREATE TABLE TO OpsMgrWriter
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
WHILE EXISTS (SELECT *
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
)
BEGIN
SELECT TOP 1 @AggregationType = AggregationType
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
ORDER BY AggregationType
SET @Statement = 'EXEC StandardDatasetAllocateStorage @DatasetId = ''$Config/DatasetId$'', @AggregationTypeId='
+ CASE @AggregationType
WHEN 'Raw' THEN '0'
WHEN 'Subhourly' THEN '10'
WHEN 'Hourly' THEN '20'
WHEN 'Daily' THEN '30'
END
EXECUTE (@Statement)
END
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
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
GO
-- insert staging definition into StandardDatasetStagingArea
BEGIN TRAN
IF EXISTS (SELECT * FROM StandardDatasetStagingArea WHERE DatasetId = '$Config/DatasetId$')
BEGIN
DELETE StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
END
INSERT StandardDatasetStagingArea
(
DatasetId
,StagingTableColumnDefinition
,BatchedProcessingSupportedInd
,MaxRowsToProcessPerTransactionCount
)
VALUES
(
'$Config/DatasetId$'
,'
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,[DateTime] datetime NOT NULL
,ManagedEntityRowId int NOT NULL
,PerformanceRuleInstanceRowId int NOT NULL
,SampleValue float(53) NOT NULL
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PerformanceStage' AND TABLE_SCHEMA = 'Perf')
BEGIN
DECLARE @Statement nvarchar(max)
SELECT @Statement = 'CREATE TABLE Perf.PerformanceStage (' + StagingTableColumnDefinition + ')'
FROM StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
EXECUTE (@Statement)
END
GO
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE ON Perf.PerformanceStage TO OpsMgrWriter
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PerformanceRule' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.PerformanceRule
END
GO
CREATE TABLE dbo.PerformanceRule
(
RuleRowId int NOT NULL
,ObjectName nvarchar(256) NOT NULL
,CounterName nvarchar(256) NOT NULL
,MultiInstanceInd bit NOT NULL DEFAULT (0)
,LastReceivedDateTime smalldatetime NOT NULL DEFAULT (GETUTCDATE())
,CONSTRAINT PK_PerformanceRule PRIMARY KEY CLUSTERED (RuleRowId)
)
GO
CREATE INDEX IX_PerformanceRule_LastReceivedDateTime ON PerformanceRule (LastReceivedDateTime)
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'PerformanceRule'
,@DatasetId = '$Config/DatasetId$'
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PerformanceRuleInstance' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.PerformanceRuleInstance
END
GO
CREATE TABLE dbo.PerformanceRuleInstance
(
PerformanceRuleInstanceRowId int NOT NULL IDENTITY(1, 1)
,RuleRowId int NOT NULL
,InstanceName nvarchar(256) NOT NULL
,LastReceivedDateTime smalldatetime NOT NULL DEFAULT (GETUTCDATE())
CREATE INDEX IX_PerformanceRuleInstance_RuleRowId ON PerformanceRuleInstance (RuleRowId)
GO
CREATE INDEX IX_PerformanceRuleInstance_LastReceivedDateTime ON PerformanceRuleInstance (LastReceivedDateTime)
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'PerformanceRuleInstance'
,@DatasetId = '$Config/DatasetId$'
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vPerformanceRule')
BEGIN
EXECUTE ('CREATE VIEW dbo.vPerformanceRule AS SELECT A = 1')
END
GO
ALTER VIEW dbo.vPerformanceRule
AS
SELECT *
FROM dbo.PerformanceRule (NOLOCK)
GO
GRANT SELECT ON dbo.vPerformanceRule TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vPerformanceRuleInstance')
BEGIN
EXECUTE ('CREATE VIEW dbo.vPerformanceRuleInstance AS SELECT A = 1')
END
GO
ALTER VIEW dbo.vPerformanceRuleInstance
AS
SELECT *
FROM dbo.PerformanceRuleInstance (NOLOCK)
GO
GRANT SELECT ON dbo.vPerformanceRuleInstance TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.PerformanceAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.PerformanceAggregate
@DatasetId uniqueidentifier
,@AggregationTypeId int
,@InsertTableGuid varchar(256)
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SELECT @CoverViewName = 'v' + BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC'
SET @AggregationStartDateTime = GETUTCDATE()
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC'
DECLARE @OperationDurationMs bigint
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@OperationDurationMs = @OperationDurationMs
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
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 = 'PerformanceAggregationDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.PerformanceAggregationDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.PerformanceAggregationDelete
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@AggregationDateTime datetime
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in state data set
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
-- Index hint in the query below ensures that SQL won't
-- consider index on DateTime field to be a good source
-- to find rows to delete - it may happen periodically
-- if SQL thinks there's no rows to delete due to
-- lack of [recently updated] statistics
SET @Statement = 'DELETE t' +
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' t WITH (INDEX(1))'
+ ' WHERE (t.[DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
IF (@DebugLevel > 2)
BEGIN
SELECT @DebugMessage = 'Starting delete of existing performance aggregated data. Table: ' + @TableName + ', aggregation date: ' + CONVERT(varchar(20), @AggregationDateTime, 120)
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to delete aggregated data for state data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.PerformanceGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.PerformanceGroom
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@CutoffDateTime datetime
,@MaxRowsToGroom int
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in perf data set
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
-- groom dynamic dimentions
DECLARE @MaxDataAgeDays int
SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
DELETE pri
FROM PerformanceRuleInstance pri
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE pr
FROM PerformanceRule pr
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
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 performance data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceObjectCounterInstanceRowIdResolve')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.PerformanceObjectCounterInstanceRowIdResolve AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.PerformanceObjectCounterInstanceRowIdResolve
@ManagementGroupGuid uniqueidentifier
,@ObjectKeyXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group is connected to this DW
DECLARE @CheckExecResult int
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF (OBJECT_ID('tempdb..#PerformanceObjectCounterInstance') IS NOT NULL)
DROP TABLE #PerformanceObjectCounterInstance
CREATE TABLE #PerformanceObjectCounterInstance (
NodeOrdinal int NOT NULL
,RuleGuid uniqueidentifier NOT NULL
,ObjectName nvarchar(256) NOT NULL
,CounterName nvarchar(256) NOT NULL
,InstanceName nvarchar(256) NOT NULL
)
INSERT #PerformanceObjectCounterInstance (
NodeOrdinal
,RuleGuid
,ObjectName
,CounterName
,InstanceName
)
SELECT
NodeOrdinal
,RuleGuid
,ObjectName
,CounterName
,InstanceName
FROM OPENXML(@XmlDocHandle, 'Keys/Key', 2) WITH (
NodeOrdinal int '@mp:id'
,RuleGuid uniqueidentifier 'RuleGuid'
,ObjectName nvarchar(256) 'ObjectName'
,CounterName nvarchar(256) 'CounterName'
,InstanceName nvarchar(256) 'InstanceName') xml
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
-- update PerformanceRule domain
INSERT PerformanceRule (
RuleRowId
,ObjectName
,CounterName
)
SELECT DISTINCT
r.RuleRowId
,'' -- set object/counter to empty string in case
,'' -- we have more then one rule-o/c mapping in keys collection
FROM #PerformanceObjectCounterInstance p
JOIN vRule r ON (p.RuleGuid = r.RuleGuid)
WHERE NOT EXISTS (SELECT * FROM PerformanceRule WHERE RuleRowId = r.RuleRowId)
UPDATE pr
SET ObjectName = p.ObjectName
,CounterName = p.CounterName
,LastReceivedDateTime = GETUTCDATE()
FROM PerformanceRule pr (TABLOCK)
JOIN vRule r ON (pr.RuleRowId = r.RuleRowId)
JOIN #PerformanceObjectCounterInstance p ON (r.RuleGuid = p.RuleGuid)
WHERE (pr.ObjectName <> p.ObjectName COLLATE database_default)
OR (pr.CounterName <> p.CounterName COLLATE database_default)
OR (pr.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
-- update Rule <-> Instance domain
UPDATE pri
SET LastReceivedDateTime = GETUTCDATE()
FROM PerformanceRuleInstance pri
JOIN vRule r ON (r.RuleRowId = pri.RuleRowId)
JOIN #PerformanceObjectCounterInstance p ON (r.RuleGuid = p.RuleGuid) AND (pri.InstanceName = p.InstanceName COLLATE database_default)
WHERE (pri.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT PerformanceRuleInstance (RuleRowId, InstanceName)
SELECT DISTINCT r.RuleRowId, p.InstanceName
FROM #PerformanceObjectCounterInstance p
JOIN vRule r ON (r.RuleGuid = p.RuleGuid)
WHERE NOT EXISTS (SELECT *
FROM PerformanceRuleInstance pri (TABLOCK)
JOIN vRule r ON (r.RuleRowId = pri.RuleRowId)
WHERE (r.RuleGuid = p.RuleGuid)
AND (pri.InstanceName = p.InstanceName COLLATE database_default))
-- figure out which rules are multi-instance
UPDATE pr
SET MultiInstanceInd = 1
FROM PerformanceRule pr (TABLOCK)
JOIN (SELECT RuleRowId
FROM PerformanceRuleInstance
GROUP BY RuleRowId
HAVING count(*) > 1) ic ON (ic.RuleRowId = pr.RuleRowId)
WHERE MultiInstanceInd = 0
SELECT
ISNULL(pri.PerformanceRuleInstanceRowId, 0)
FROM #PerformanceObjectCounterInstance p
LEFT JOIN vRule r ON (r.RuleGuid = p.RuleGuid)
LEFT JOIN PerformanceRuleInstance pri ON (pri.RuleRowId = r.RuleRowId) AND (pri.InstanceName = p.InstanceName COLLATE database_default)
ORDER BY p.NodeOrdinal
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#PerformanceObjectCounterInstance') IS NOT NULL)
DROP TABLE #PerformanceObjectCounterInstance
-- 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
GRANT EXECUTE ON PerformanceObjectCounterInstanceRowIdResolve TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.PerformanceProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.PerformanceProcessStaging
@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
DECLARE
@DebugLevel int
,@SchemaName sysname
,@ExecResult int
,@LockResourceName sysname
,@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText varchar(max)
,@OperationDurationMs bigint
,@RawDataMaxAgeDays int
,@MaxStagingRowsToProcess int
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PerformanceStage2Process' AND TABLE_SCHEMA = 'Perf')
BEGIN
DECLARE @ProcessAllRowsInd bit
SET @ProcessAllRowsInd = 1
-- figure out what we need to do with staging area
SELECT
@Statement = StagingTableColumnDefinition
,@MaxStagingRowsToProcess = MaxRowsToProcessPerTransactionCount
FROM StandardDatasetStagingArea
WHERE DatasetId = @DatasetId
IF (@MaxStagingRowsToProcess IS NOT NULL)
BEGIN
DECLARE @PerfStageRowCount int
DECLARE @SpaceUsedInfo TABLE (
[name] sysname
,[rows] int
,reserved nvarchar(100)
,data nvarchar(100)
,index_size nvarchar(100)
,unused nvarchar(100)
)
IF (@ExecResult < 0)
BEGIN
RAISERROR(777971003, 16, 1, @ExecResult)
END
SELECT
@Statement = 'CREATE TABLE Perf.PerformanceStage (' + StagingTableColumnDefinition + ')'
FROM StandardDatasetStagingArea
WHERE DatasetId = @DatasetId
EXECUTE (@Statement)
END
ELSE
BEGIN
-- need to process subset of rows
-- create extra column to store original row id
SELECT @Statement =
'CREATE TABLE Perf.PerformanceStage2Process ('
+ @Statement
+ ',OriginalPerformanceStageRowId int NOT NULL'
+ ')'
EXECUTE (@Statement)
SELECT @Statement =
'DELETE ps'
+ ' FROM Perf.PerformanceStage ps'
+ ' JOIN Perf.PerformanceStage2Process psp ON (ps.PerformanceStageRowId = psp.OriginalPerformanceStageRowId)'
EXECUTE(@Statement)
END
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE ON Perf.PerformanceStage TO OpsMgrWriter
GRANT CONTROL ON Perf.PerformanceStage2Process TO OpsMgrWriter
COMMIT
END -- IF NOT EXISTS Perf.PerformanceStage2Process
-- create index on datetime field
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_PerformanceStage2Process_DateTime')
BEGIN
CREATE INDEX IX_PerformanceStage2Process_DateTime ON Perf.PerformanceStage2Process(DateTime)
END
BEGIN TRAN
-- lock table map to ensure we insert into table opened for insertion
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_TableMap'
IF (@DebugLevel > 3)
BEGIN
SELECT @MessageText = 'Starting to insert ' + CAST(COUNT(*) AS varchar(30)) + ' performance samples into ' + @InsertTableName
FROM Perf.PerformanceStage2Process
-- mark existing affected aggregations as dirty
UPDATE ah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON (ah.DatasetId = a.DatasetId AND ah.AggregationTypeId = a.AggregationTypeId)
CROSS JOIN Perf.PerformanceStage2Process p
WHERE (ah.DatasetId = @DatasetId)
AND (ah.AggregationDateTime > DATEADD(day, -@RawDataMaxAgeDays, GETUTCDATE()))
AND (p.[DateTime] >= ah.AggregationDateTime)
AND (p.[DateTime] < DATEADD(minute, a.AggregationIntervalDurationMinutes, ah.AggregationDateTime))
-- delete all inserted data from staging
DROP TABLE Perf.PerformanceStage2Process
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting performance samples into ' + @InsertTableName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process data in the performance data set staging area. 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
GRANT EXECUTE ON dbo.PerformanceProcessStaging TO OpsMgrWriter
GO
</Install> <Uninstall>
-- drop SPs
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceAggregate')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.PerformanceAggregate')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceAggregationDelete')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.PerformanceAggregationDelete')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceGroom')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.PerformanceGroom')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceObjectCounterInstanceRowIdResolve')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.PerformanceObjectCounterInstanceRowIdResolve')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceProcessStaging')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.PerformanceProcessStaging')
END
-- drop views
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vPerformanceRule')
BEGIN
EXECUTE ('DROP VIEW vPerformanceRule')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vPerformanceRuleInstance')
BEGIN
EXECUTE ('DROP VIEW vPerformanceRuleInstance')
END
-- drop tables
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PerformanceStage' AND TABLE_SCHEMA = 'Perf')
BEGIN
EXECUTE('DROP TABLE Perf.PerformanceStage')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PerformanceRule' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.PerformanceRule')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PerformanceRuleInstance' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.PerformanceRuleInstance')
END
-- delete std dataset related data
EXEC StandardDatasetDelete '$Config/DatasetId$'
GO
</Uninstall> <Upgrade>
SET NOCOUNT ON
UPDATE StandardDatasetAggregationStorage
SET TableTemplate = REPLACE(TableTemplate, ' int NOT NULL IDENTITY(1, 1)', ' bigint NOT NULL IDENTITY(1, 1)')
WHERE DatasetId = '$Config/DatasetId$'
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.PerformanceAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.PerformanceAggregate
@DatasetId uniqueidentifier
,@AggregationTypeId int
,@InsertTableGuid varchar(256)
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SELECT @CoverViewName = 'v' + BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC'
SET @AggregationStartDateTime = GETUTCDATE()
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC'
DECLARE @OperationDurationMs bigint
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@OperationDurationMs = @OperationDurationMs
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
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 = 'PerformanceAggregationDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.PerformanceAggregationDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.PerformanceAggregationDelete
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@AggregationDateTime datetime
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in state data set
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
-- Index hint in the query below ensures that SQL won't
-- consider index on DateTime field to be a good source
-- to find rows to delete - it may happen periodically
-- if SQL thinks there's no rows to delete due to
-- lack of [recently updated] statistics
SET @Statement = 'DELETE t' +
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' t WITH (INDEX(1))'
+ ' WHERE (t.[DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
IF (@DebugLevel > 2)
BEGIN
SELECT @DebugMessage = 'Starting delete of existing performance aggregated data. Table: ' + @TableName + ', aggregation date: ' + CONVERT(varchar(20), @AggregationDateTime, 120)
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to delete aggregated data for state data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.PerformanceGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.PerformanceGroom
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@CutoffDateTime datetime
,@MaxRowsToGroom int
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in perf data set
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
-- groom dynamic dimentions
DECLARE @MaxDataAgeDays int
SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
DELETE pri
FROM PerformanceRuleInstance pri
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE pr
FROM PerformanceRule pr
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
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 performance data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceObjectCounterInstanceRowIdResolve')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.PerformanceObjectCounterInstanceRowIdResolve AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.PerformanceObjectCounterInstanceRowIdResolve
@ManagementGroupGuid uniqueidentifier
,@ObjectKeyXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group is connected to this DW
DECLARE @CheckExecResult int
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF (OBJECT_ID('tempdb..#PerformanceObjectCounterInstance') IS NOT NULL)
DROP TABLE #PerformanceObjectCounterInstance
CREATE TABLE #PerformanceObjectCounterInstance (
NodeOrdinal int NOT NULL
,RuleGuid uniqueidentifier NOT NULL
,ObjectName nvarchar(256) NOT NULL
,CounterName nvarchar(256) NOT NULL
,InstanceName nvarchar(256) NOT NULL
)
INSERT #PerformanceObjectCounterInstance (
NodeOrdinal
,RuleGuid
,ObjectName
,CounterName
,InstanceName
)
SELECT
NodeOrdinal
,RuleGuid
,ObjectName
,CounterName
,InstanceName
FROM OPENXML(@XmlDocHandle, 'Keys/Key', 2) WITH (
NodeOrdinal int '@mp:id'
,RuleGuid uniqueidentifier 'RuleGuid'
,ObjectName nvarchar(256) 'ObjectName'
,CounterName nvarchar(256) 'CounterName'
,InstanceName nvarchar(256) 'InstanceName') xml
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
-- update PerformanceRule domain
INSERT PerformanceRule (
RuleRowId
,ObjectName
,CounterName
)
SELECT DISTINCT
r.RuleRowId
,'' -- set object/counter to empty string in case
,'' -- we have more then one rule-o/c mapping in keys collection
FROM #PerformanceObjectCounterInstance p
JOIN vRule r ON (p.RuleGuid = r.RuleGuid)
WHERE NOT EXISTS (SELECT * FROM PerformanceRule WHERE RuleRowId = r.RuleRowId)
UPDATE pr
SET ObjectName = p.ObjectName
,CounterName = p.CounterName
,LastReceivedDateTime = GETUTCDATE()
FROM PerformanceRule pr (TABLOCK)
JOIN vRule r ON (pr.RuleRowId = r.RuleRowId)
JOIN #PerformanceObjectCounterInstance p ON (r.RuleGuid = p.RuleGuid)
WHERE (pr.ObjectName <> p.ObjectName COLLATE database_default)
OR (pr.CounterName <> p.CounterName COLLATE database_default)
OR (pr.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
-- update Rule <-> Instance domain
UPDATE pri
SET LastReceivedDateTime = GETUTCDATE()
FROM PerformanceRuleInstance pri
JOIN vRule r ON (r.RuleRowId = pri.RuleRowId)
JOIN #PerformanceObjectCounterInstance p ON (r.RuleGuid = p.RuleGuid) AND (pri.InstanceName = p.InstanceName COLLATE database_default)
WHERE (pri.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT PerformanceRuleInstance (RuleRowId, InstanceName)
SELECT DISTINCT r.RuleRowId, p.InstanceName
FROM #PerformanceObjectCounterInstance p
JOIN vRule r ON (r.RuleGuid = p.RuleGuid)
WHERE NOT EXISTS (SELECT *
FROM PerformanceRuleInstance pri (TABLOCK)
JOIN vRule r ON (r.RuleRowId = pri.RuleRowId)
WHERE (r.RuleGuid = p.RuleGuid)
AND (pri.InstanceName = p.InstanceName COLLATE database_default))
-- figure out which rules are multi-instance
UPDATE pr
SET MultiInstanceInd = 1
FROM PerformanceRule pr (TABLOCK)
JOIN (SELECT RuleRowId
FROM PerformanceRuleInstance
GROUP BY RuleRowId
HAVING count(*) > 1) ic ON (ic.RuleRowId = pr.RuleRowId)
WHERE MultiInstanceInd = 0
SELECT
ISNULL(pri.PerformanceRuleInstanceRowId, 0)
FROM #PerformanceObjectCounterInstance p
LEFT JOIN vRule r ON (r.RuleGuid = p.RuleGuid)
LEFT JOIN PerformanceRuleInstance pri ON (pri.RuleRowId = r.RuleRowId) AND (pri.InstanceName = p.InstanceName COLLATE database_default)
ORDER BY p.NodeOrdinal
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#PerformanceObjectCounterInstance') IS NOT NULL)
DROP TABLE #PerformanceObjectCounterInstance
-- 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
GRANT EXECUTE ON PerformanceObjectCounterInstanceRowIdResolve TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PerformanceProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.PerformanceProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.PerformanceProcessStaging
@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
DECLARE
@DebugLevel int
,@SchemaName sysname
,@ExecResult int
,@LockResourceName sysname
,@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText varchar(max)
,@OperationDurationMs bigint
,@RawDataMaxAgeDays int
,@MaxStagingRowsToProcess int
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PerformanceStage2Process' AND TABLE_SCHEMA = 'Perf')
BEGIN
DECLARE @ProcessAllRowsInd bit
SET @ProcessAllRowsInd = 1
-- figure out what we need to do with staging area
SELECT
@Statement = StagingTableColumnDefinition
,@MaxStagingRowsToProcess = MaxRowsToProcessPerTransactionCount
FROM StandardDatasetStagingArea
WHERE DatasetId = @DatasetId
IF (@MaxStagingRowsToProcess IS NOT NULL)
BEGIN
DECLARE @PerfStageRowCount int
DECLARE @SpaceUsedInfo TABLE (
[name] sysname
,[rows] int
,reserved nvarchar(100)
,data nvarchar(100)
,index_size nvarchar(100)
,unused nvarchar(100)
)
IF (@ExecResult < 0)
BEGIN
RAISERROR(777971003, 16, 1, @ExecResult)
END
SELECT
@Statement = 'CREATE TABLE Perf.PerformanceStage (' + StagingTableColumnDefinition + ')'
FROM StandardDatasetStagingArea
WHERE DatasetId = @DatasetId
EXECUTE (@Statement)
END
ELSE
BEGIN
-- need to process subset of rows
-- create extra column to store original row id
SELECT @Statement =
'CREATE TABLE Perf.PerformanceStage2Process ('
+ @Statement
+ ',OriginalPerformanceStageRowId int NOT NULL'
+ ')'
EXECUTE (@Statement)
SELECT @Statement =
'DELETE ps'
+ ' FROM Perf.PerformanceStage ps'
+ ' JOIN Perf.PerformanceStage2Process psp ON (ps.PerformanceStageRowId = psp.OriginalPerformanceStageRowId)'
EXECUTE(@Statement)
END
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE ON Perf.PerformanceStage TO OpsMgrWriter
GRANT CONTROL ON Perf.PerformanceStage2Process TO OpsMgrWriter
COMMIT
END -- IF NOT EXISTS Perf.PerformanceStage2Process
-- create index on datetime field
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_PerformanceStage2Process_DateTime')
BEGIN
CREATE INDEX IX_PerformanceStage2Process_DateTime ON Perf.PerformanceStage2Process(DateTime)
END
BEGIN TRAN
-- lock table map to ensure we insert into table opened for insertion
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_TableMap'
IF (@DebugLevel > 3)
BEGIN
SELECT @MessageText = 'Starting to insert ' + CAST(COUNT(*) AS varchar(30)) + ' performance samples into ' + @InsertTableName
FROM Perf.PerformanceStage2Process
-- mark existing affected aggregations as dirty
UPDATE ah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON (ah.DatasetId = a.DatasetId AND ah.AggregationTypeId = a.AggregationTypeId)
CROSS JOIN Perf.PerformanceStage2Process p
WHERE (ah.DatasetId = @DatasetId)
AND (ah.AggregationDateTime > DATEADD(day, -@RawDataMaxAgeDays, GETUTCDATE()))
AND (p.[DateTime] >= ah.AggregationDateTime)
AND (p.[DateTime] < DATEADD(minute, a.AggregationIntervalDurationMinutes, ah.AggregationDateTime))
-- delete all inserted data from staging
DROP TABLE Perf.PerformanceStage2Process
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting performance samples into ' + @InsertTableName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process data in the performance data set staging area. 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
GRANT EXECUTE ON dbo.PerformanceProcessStaging TO OpsMgrWriter
GO
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
BEGIN TRY
-- lock staging
SET @StagingLockResourceName = '$Config/DatasetId$' + '_Staging'
IF (@ExecResult = -1)
BEGIN
RAISERROR(777971001, 16, 1, 'StageArea', @ExecResult)
END
SET @StagingLockSetInd = 1
IF (@ExecResult < 0)
BEGIN
RAISERROR(777971001, 16, 1, 'StageArea', @ExecResult)
END
UPDATE StandardDatasetStagingArea
SET StagingTableColumnDefinition =
'DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,[DateTime] datetime NOT NULL
,ManagedEntityRowId int NOT NULL
,PerformanceRuleInstanceRowId int NOT NULL
,SampleValue float(53) NOT NULL
,PerformanceStageRowId bigint NOT NULL IDENTITY(1, 1) PRIMARY KEY
'
,MaxRowsToProcessPerTransactionCount = CASE WHEN MaxRowsToProcessPerTransactionCount < 500000 THEN MaxRowsToProcessPerTransactionCount ELSE 500000 END
,BatchedProcessingSupportedInd = 1
WHERE DatasetId = '$Config/DatasetId$'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'PerformanceStage' AND TABLE_SCHEMA = 'Perf' AND COLUMN_NAME = 'PerformanceStageRowId')
BEGIN
ALTER TABLE Perf.PerformanceStage
ADD PerformanceStageRowId bigint NOT NULL IDENTITY(1, 1) CONSTRAINT PK_PerformanceStage PRIMARY KEY
END
-- rename PerformanceStage2Process table if exists
-- because it may be huge and may prevent perf data from flowing
IF (OBJECT_ID('Perf.PerformanceStage2Process') IS NOT NULL)
BEGIN
EXEC sp_rename 'Perf.PerformanceStage2Process', 'PerformanceStage2Process_Sp2_Upgrade_Backup'
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- 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
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
GO
/* raw indexes for UI perf widget perf */
IF NOT EXISTS (SELECT *
FROM StandardDatasetAggregationStorage s
JOIN StandardDatasetAggregationStorageindex si ON (s.StandardDatasetAggregationStorageRowId = si.StandardDatasetAggregationStorageRowId)
WHERE (s.DatasetId = '$Config/DatasetId$')
AND (s.AggregationTypeId = 0)
AND (si.IndexGuid = 'A136E27D-A83E-4F38-B9A3-35EAAFBFCBE8')
)
BEGIN
EXEC StandardDatasetIndexInsert
@DatasetId = '$Config/DatasetId$'
,@AggregationTypeId = 0
,@DependentTableInd = 0
,@TableTag = NULL
,@UniqueInd = 0
,@IndexGuid = 'A136E27D-A83E-4F38-B9A3-35EAAFBFCBE8'
,@IndexDefinition = '(
[ManagedEntityRowId],
[PerformanceRuleInstanceRowId],
[DateTime]
)
INCLUDE
(
[SampleValue]
)
'
,@CreateIndexOnExistingTablesInd = 1
END
GO
-- Hourly data index to support Top N reports and dashboards
IF NOT EXISTS (SELECT *
FROM StandardDatasetAggregationStorage s
JOIN StandardDatasetAggregationStorageindex si ON (s.StandardDatasetAggregationStorageRowId = si.StandardDatasetAggregationStorageRowId)
WHERE (s.DatasetId = '$Config/DatasetId$')
AND (s.AggregationTypeId = 20)
AND (si.IndexGuid = '04DE2C08-6203-4DA1-8EED-A0984463C46E')
)
BEGIN
EXEC StandardDatasetIndexInsert
@DatasetId = '$Config/DatasetId$'
,@AggregationTypeId = 20
,@DependentTableInd = 0
,@TableTag = NULL
,@UniqueInd = 0
,@IndexGuid = '04DE2C08-6203-4DA1-8EED-A0984463C46E'
,@IndexDefinition = '(
[PerformanceRuleInstanceRowId]
,[DateTime]
)
INCLUDE
(
[ManagedEntityRowId]
,[SampleCount]
,[AverageValue]
)
'
,@CreateIndexOnExistingTablesInd = 0
END
GO
-- Daily data index to support Top N reports and dashboards
IF NOT EXISTS (SELECT *
FROM StandardDatasetAggregationStorage s
JOIN StandardDatasetAggregationStorageindex si ON (s.StandardDatasetAggregationStorageRowId = si.StandardDatasetAggregationStorageRowId)
WHERE (s.DatasetId = '$Config/DatasetId$')
AND (s.AggregationTypeId = 30)
AND (si.IndexGuid = '110C2981-0D21-4C6E-8185-49997A2BA0B5')
)
BEGIN
EXEC StandardDatasetIndexInsert
@DatasetId = '$Config/DatasetId$'
,@AggregationTypeId = 30
,@DependentTableInd = 0
,@TableTag = NULL
,@UniqueInd = 0
,@IndexGuid = '110C2981-0D21-4C6E-8185-49997A2BA0B5'
,@IndexDefinition = '(
[PerformanceRuleInstanceRowId]
,[DateTime]
)
INCLUDE
(
[ManagedEntityRowId]
,[SampleCount]
,[AverageValue]
)
'
,@CreateIndexOnExistingTablesInd = 0
END
GO
Home
ENU