Tag Archives: CPU

Intel 新的 CPU 其 HyperThread 架構不同?

看到 SQL MVP 一連串的討論,有人強調 Intel 新的 CPU 其 HyperThread 架構不同(但未提哪一款之後叫”新”…),不會造成 SQL Server 大型平行運算效能低落,有的強調仍會…

不知許多曾經發生停掉 HyperThread 就提升效能的朋友是否有在新的 Server 上測試開啟 HyperThread…

邏輯 CPU 總數超過 64 時,SQL Server 2008 以前的版本會有問題

感謝承修提供了一個有趣的連結:

在Windows 2008 R2高端机器上运行SQL Server 2008时,CPU个数的考量

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