主页 / dba / 问题 / 308323
Accepted
Brendan McCaffrey
Asked: 2022-03-05 14:14:56 +0800 CST

按数据库/应用程序报告 SQL Server 的正常运行时间指标

  • 772

我需要按应用程序/数据库报告 SQL Server 正常运行时间的每月指标。这将在集群级别。IE，如果辅助副本离线，但主副本仍可用于处理事务，则正常运行时间仍将被视为 100%。

我已经查看了可能用于此目的的各种工具，其中一些工具接近了。但是，除了 SQL Server 服务处于联机状态并接受连接之外，它们似乎都没有捕获任何东西。他们也无法在集群/AG 级别聚合这些指标。这意味着如果辅助副本离线，这些正常运行时间报告将受到影响。

例如，假设数据库脱机或日志文件已满，并且无法针对单个数据库处理事务。这些工具会说 SQL Server 已启动，但我仍然会有人说这是数据库问题。因此，这些指标需要反映当时 SQL 尚未完全启动。

在这一点上我想出的最好的想法是创建一个 SQL 代理作业，该作业将记录插入每个数据库的 Canary 表中，每分钟一次。然后在月底，查询该表并将上个月的行数除以预期的行数。我认为没有比尝试插入一行更好的方法来证明数据库实际上是可用的。

我已经开发、测试和工作了上述解决方案。但我很好奇是否有人知道更好的方法来做到这一点。包括我可能忽略的任何商品工具或 DMV，我可以用来推断 SQL 实例上所有数据库的最终用户可用性指标？

sql-server metrics

1 个回答

  1. Best Answer

    这是我正在使用的解决方案，它正在工作。

    下面的存储过程可用于填充 Canary 表，并且可以从 SQL 代理作业每分钟执行一次。

    CREATE PROCEDURE [dbo].[SQLUptime]
AS
BEGIN 
    SET NOCOUNT ON

    DECLARE
        @DatabaseName nvarchar(128),
        @Query nvarchar(max);

    DECLARE cDatabases CURSOR FOR
        SELECT [name]
        FROM sys.databases
        WHERE database_id > 4

    OPEN cDatabases;
    FETCH NEXT FROM cDatabases INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            IF COALESCE(sys.fn_hadr_is_primary_replica(@DatabaseName),1) = 1    
            BEGIN
                SET @Query = 'USE [' + @DatabaseName + ']' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) + 
                    'IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N''dba'')' + CHAR(13)+CHAR(10) + 
                    'BEGIN' + CHAR(13)+CHAR(10) +
                    '    EXECUTE sp_executesql N''CREATE SCHEMA [dba]''' + CHAR(13)+CHAR(10) +
                    'END' +  CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) +
            
                    'IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N''[dba].[Canary]'') AND type in (N''U''))' + CHAR(13)+CHAR(10) +
                    'BEGIN' + CHAR(13)+CHAR(10) +
                    '    CREATE TABLE dba.Canary (' + CHAR(13)+CHAR(10) +
                    '        [Time] Datetime NOT NULL' + CHAR(13)+CHAR(10) +
                    '    )' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) +
            
                    '    CREATE CLUSTERED INDEX CIX_Canary_Time ON dba.Canary ([Time]);' + CHAR(13)+CHAR(10) +
                    'END' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) +

                    'INSERT INTO dba.Canary ([Time])' + CHAR(13)+CHAR(10) +
                    'VALUES (getdate());' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) +
            
                    'DELETE FROM dba.Canary' + CHAR(13)+CHAR(10) +
                    'WHERE [Time] < DATEADD(MONTH, -12, GETDATE());'

            /*PRINT @Query*/
            EXECUTE sp_executesql @Query
        END
        END TRY
        BEGIN CATCH
            SELECT
                ERROR_NUMBER(),
                ERROR_STATE(),
                ERROR_SEVERITY(),
                ERROR_PROCEDURE(),
                ERROR_LINE(),
                ERROR_MESSAGE()
        END CATCH
        
        FETCH NEXT FROM cDatabases INTO @DatabaseName
    END

    CLOSE cDatabases;
    DEALLOCATE cDatabases;
END

    然后可以每月使用以下查询来报告基于上个月成功插入 Canary 表的行数的正常运行时间。

    DECLARE
    @StartDate    datetime,
    @CutoffDate   datetime,
    @TotalMinutes int,
    @output       int,
    @SQL          nvarchar(500),
    @Parameters   nvarchar(500),
    @Database     nvarchar(128);

SET @StartDate = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-1,0);
SET @CutoffDate = DATEADD(DAY,0,DATEADD(MONTH,1,@StartDate));

IF @CutoffDate > GETDATE()
    SET @CutoffDate = GETDATE();

SET @TotalMinutes = DATEDIFF(MINUTE, @StartDate,@CutoffDate);
SET @Parameters = N'@retvalOUT int OUTPUT';

IF OBJECT_ID('tempdb..#UptimeMinutes') IS NOT NULL DROP TABLE #UptimeMinutes

CREATE TABLE #UptimeMinutes (
    DatabaseName nvarchar(128),
    UptimeMinutes int
)

DECLARE cursorDatabases CURSOR
FOR
    SELECT [name]
    FROM sys.databases
    WHERE database_id > 4;

OPEN cursorDatabases;
FETCH NEXT FROM cursorDatabases INTO @Database;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'USE [' + @Database + '] SELECT @retvalOUT = COUNT(*) FROM dba.Canary';

    EXEC sp_executesql @SQL, @Parameters, @retvalOUT=@output OUTPUT;

    INSERT INTO #UptimeMinutes (DatabaseName,UptimeMinutes)
    SELECT @Database, @output;

    FETCH NEXT FROM cursorDatabases INTO @Database;
END

SELECT SUM(UptimeMinutes) InstanceUptimeMinutes,
    SUM(@TotalMinutes) AS TargetInstanceUptimeMinutes,
    CONVERT(DECIMAL(10,3),(SUM(UptimeMinutes)/CONVERT(DECIMAL,SUM(@TotalMinutes))) * 100) AS InstanceUptimePercentage
FROM #UptimeMinutes;

SELECT DatabaseName,
    DatabaseUptimeMinutes,
    @TotalMinutes AS TargetDatabaseUptimeMinutes,
    CONVERT(DECIMAL(10,3),(UptimeMinutes/CONVERT(DECIMAL,@TotalMinutes)) * 100) AS DatabaseUptimePercentage
FROM #UptimeMinutes;

CLOSE cursorDatabases;
DEALLOCATE cursorDatabases;
    • 2

相关问题