SQL Server 的 CPU 使用歷程

感謝承修提供的連結

Getting CPU Utilization Data from SQL Server

 

 

SQL Server Performance Counter 的查詢

SET NOCOUNT ON;

 

— Variables for Counters

DECLARE @BatchRequestsPerSecond BIGINT;

DECLARE @CompilationsPerSecond BIGINT;

DECLARE @ReCompilationsPerSecond BIGINT;

DECLARE @LockWaitsPerSecond BIGINT;

DECLARE @PageSplitsPerSecond BIGINT;

DECLARE @CheckpointPagesPerSecond BIGINT;

 

— Variable for date

DECLARE @stat_date DATETIME;

 

— Table for First Sample

DECLARE @RatioStatsX TAbLE(

       [object_name] varchar(128)

      ,[counter_name] varchar(128)

      ,[instance_name] varchar(128)

      ,[cntr_value] bigint

      ,[cntr_type] int

      )

 

— Table for Second Sample

DECLARE @RatioStatsY TABLE(

       [object_name] VARCHAR(128)

      ,[counter_name] VARCHAR(128)

      ,[instance_name] VARCHAR(128)

      ,[cntr_value] BIGINT

      ,[cntr_type] INT

      );

 

— Capture stat time

SET @stat_date = getdate();

 

INSERT INTO @RatioStatsX (

     [object_name]

      ,[counter_name]

      ,[instance_name]

      ,[cntr_value]

      ,[cntr_type] )

      SELECT [object_name]

            ,[counter_name]

            ,[instance_name]

            ,[cntr_value]

            ,[cntr_type] FROM sys.dm_os_performance_counters;

 

— Capture each per second counter for first sampling

SELECT TOP 1 @BatchRequestsPerSecond = cntr_value

      FROM @RatioStatsX

    WHERE counter_name = ‘Batch Requests/sec’

      AND object_name LIKE ‘%SQL Statistics%’;

 

SELECT TOP 1 @CompilationsPerSecond = cntr_value

      FROM @RatioStatsX

    WHERE counter_name = ‘SQL Compilations/sec’

      AND object_name LIKE ‘%SQL Statistics%’;

 

SELECT TOP 1 @ReCompilationsPerSecond = cntr_value

      FROM @RatioStatsX

    WHERE counter_name = ‘SQL Re-Compilations/sec’

      AND object_name LIKE ‘%SQL Statistics%’;

 

SELECT TOP 1 @LockWaitsPerSecond = cntr_value

      FROM @RatioStatsX

    WHERE counter_name = ‘Lock Waits/sec’

      AND instance_name = ‘_Total’

      AND object_name LIKE ‘%Locks%’;

 

SELECT TOP 1 @PageSplitsPerSecond = cntr_value

      FROM @RatioStatsX

    WHERE counter_name = ‘Page Splits/sec’

      AND object_name LIKE ‘%Access Methods%’;

 

SELECT TOP 1 @CheckpointPagesPerSecond = cntr_value

      FROM @RatioStatsX

      WHERE counter_name = ‘Checkpoint Pages/sec’

        AND object_name LIKE ‘%Buffer Manager%’;                                        

 

WAITFOR DELAY ’00:00:01′

 

— Table for second sample

INSERT INTO @RatioStatsY (

            [object_name]

            ,[counter_name]

            ,[instance_name]

            ,[cntr_value]

            ,[cntr_type] )

   SELECT [object_name]

            ,[counter_name]

            ,[instance_name]

            ,[cntr_value]

            ,[cntr_type] FROM sys.dm_os_performance_counters

 

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]

      ,c.cntr_value  AS [PageLifeExpectency]

      ,d.[BatchRequestsPerSecond]

      ,e.[CompilationsPerSecond]

      ,f.[ReCompilationsPerSecond]

      ,g.cntr_value AS [UserConnections]

      ,h.LockWaitsPerSecond

      ,i.PageSplitsPerSecond

      ,j.cntr_value AS [ProcessesBlocked]

      ,k.CheckpointPagesPerSecond

      ,GETDATE() AS StatDate                                    

FROM (SELECT * FROM @RatioStatsY

               WHERE counter_name = ‘Buffer cache hit ratio’

               AND object_name LIKE ‘%Buffer Manager%’) a 

     CROSS JOIN 

      (SELECT * FROM @RatioStatsY

                WHERE counter_name = ‘Buffer cache hit ratio base’

                AND object_name LIKE ‘%Buffer Manager%’) b   

     CROSS JOIN

      (SELECT * FROM @RatioStatsY

                WHERE counter_name = ‘Page life expectancy ‘

                AND object_name LIKE ‘%Buffer Manager%’) c

     CROSS JOIN

     (SELECT (cntr_value – @BatchRequestsPerSecond) /

                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0

                           THEN  1

                           ELSE datediff(ss,@stat_date, getdate()) END) AS [BatchRequestsPerSecond]

                FROM @RatioStatsY

                WHERE counter_name = ‘Batch Requests/sec’

                AND object_name LIKE ‘%SQL Statistics%’) d  

     CROSS JOIN

     (SELECT (cntr_value – @CompilationsPerSecond) /

                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0

                           THEN  1

                           ELSE datediff(ss,@stat_date, getdate()) END) AS [CompilationsPerSecond]

                FROM @RatioStatsY

                WHERE counter_name = ‘SQL Compilations/sec’

                AND object_name LIKE ‘%SQL Statistics%’) e

     CROSS JOIN

     (SELECT (cntr_value – @ReCompilationsPerSecond) /

                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0

                           THEN  1

                           ELSE datediff(ss,@stat_date, getdate()) END) AS [ReCompilationsPerSecond]

                FROM @RatioStatsY

                WHERE counter_name = ‘SQL Re-Compilations/sec’

                AND object_name LIKE ‘%SQL Statistics%’) f

     CROSS JOIN

     (SELECT * FROM @RatioStatsY

               WHERE counter_name = ‘User Connections’

               AND object_name LIKE ‘%General Statistics%’) g

     CROSS JOIN

     (SELECT (cntr_value – @LockWaitsPerSecond) /

                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0

                           THEN  1

                           ELSE datediff(ss,@stat_date, getdate()) END) AS [LockWaitsPerSecond]

                FROM @RatioStatsY

                WHERE counter_name = ‘Lock Waits/sec’

                AND instance_name = ‘_Total’

                AND object_name LIKE ‘%Locks%’) h

     CROSS JOIN

     (SELECT (cntr_value – @PageSplitsPerSecond) /

                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0

                           THEN  1

                           ELSE datediff(ss,@stat_date, getdate()) END) AS [PageSplitsPerSecond]

                FROM @RatioStatsY

                WHERE counter_name = ‘Page Splits/sec’

                AND object_name LIKE ‘%Access Methods%’) i

     CROSS JOIN

     (SELECT * FROM @RatioStatsY

               WHERE counter_name = ‘Processes blocked’

               AND object_name LIKE ‘%General Statistics%’) j

     CROSS JOIN

     (SELECT (cntr_value – @CheckpointPagesPerSecond) /

                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0

                           THEN  1

                           ELSE datediff(ss,@stat_date, getdate()) END) AS [CheckpointPagesPerSecond]

                FROM @RatioStatsY

                WHERE counter_name = ‘Checkpoint Pages/sec’

                AND object_name LIKE ‘%Buffer Manager%’) k

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

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

Twitter picture

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

Facebook照片

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

Google+ photo

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

連結到 %s

%d 位部落客按了讚: