Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



dbo.sp_sqlagent_get_perf_counters

  No additional text.


Syntax
CREATE PROCEDURE sp_sqlagent_get_perf_counters
  @all_counters BIT = 0
AS
BEGIN

  SET NOCOUNT ON

  -- 32 bit fraction counter types
  DECLARE @perfTypeRawFraction INT
  DECLARE @perfTypeRawBase     INT

  -- A counter of type PERF_RAW_FRACTION, which is a 32-bit counter value.
  SET @perfTypeRawFraction = 537003008 --  In hex, 0x20020400.

   -- A count of type PERF_RAW_BASE, which is the 32-bit divisor used
   -- when handling PERF_RAW_FRACTION types. This counter type should
   -- not be displayed to the user since it is used for mathematical
   -- operations.
  SET @perfTypeRawBase     = 1073939459 -- In hex, 0x40030403.


  -- 64 bit fraction counter types
  DECLARE @perfTypeLargeRawFraction INT
  DECLARE @perfTypeLargeRawBase     INT

  -- A counter of type PERF_LARGE RAW_FRACTION, which is a 64-bit counter value.
  SET @perfTypeLargeRawFraction = 537003264 --  In hex, 0x20020500.

   -- A count of type PERF_LARGE_RAW_BASE, which is the 64-bit divisor used
   -- when handling PERF_LARGE_RAW_FRACTION types. This counter type should
   -- not be displayed to the user since it is used for mathematical
   -- operations.
  SET @perfTypeLargeRawBase     = 1073939712 -- In hex, 0x40030500.



  IF (@all_counters = 0)
  BEGIN

      SELECT 'object_name' = RTRIM(SUBSTRING(spi1.object_name, 1, 50)),
             'counter_name' = RTRIM(SUBSTRING(spi1.counter_name, 1, 50)),
             'instance_name' = CASE spi1.instance_name
                             WHEN N'' THEN NULL
                             ELSE RTRIM(spi1.instance_name)
                           END,
         'value' = CASE spi1.cntr_type
                     WHEN @perfTypeRawFraction -- 32 bit fraction
                       THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
                                                               FROM sys.dm_os_performance_counters spi2
                                                               WHERE (spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('% Base%', spi2.counter_name)))
                                                                 AND (spi1.instance_name = spi2.instance_name)
                                                                 AND (spi2.cntr_type = @perfTypeRawBase))
                     WHEN @perfTypeLargeRawFraction  -- 64 bit fraction
                       THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
                                                               FROM sys.dm_os_performance_counters spi2
                                                               WHERE (spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('% Base%', spi2.counter_name)))
                                                                 AND (spi1.instance_name = spi2.instance_name)
                                                                 AND (spi2.cntr_type = @perfTypeLargeRawBase))
                     ELSE spi1.cntr_value
                   END,
       'type' = spi1.cntr_type

        FROM sys.dm_os_performance_counters spi1,
        (
                SELECT DISTINCT SUBSTRING(performance_condition, 1, CHARINDEX('|', performance_condition, PATINDEX('%_|_%', performance_condition) + 2) - 1)
                 as performance_condition_s  FROM msdb.dbo.sysalerts
                WHERE (performance_condition IS NOT NULL)
                AND ISNULL(event_id, 0) <> 8 -- exclude WMI events that reuse performance_condition field
                AND (enabled = 1)
        ) tmp -- We want to select only those counters that have an enabled performance sysalert
        WHERE (spi1.cntr_type <> @perfTypeRawBase)      -- ignore 32-bit denominator counter type
          AND (spi1.cntr_type <> @perfTypeLargeRawBase) -- ignore 64-bit denominator counter type
          AND (tmp.performance_condition_s = RTRIM(spi1.object_name) + '|' + RTRIM(spi1.counter_name))

  END
  ELSE
  BEGIN

    SELECT 'object_name' = RTRIM(SUBSTRING(spi1.object_name, 1, 50)),
           'counter_name' = RTRIM(SUBSTRING(spi1.counter_name, 1, 50)),
           'instance_name' = CASE spi1.instance_name
                             WHEN N'' THEN NULL
                             ELSE RTRIM(spi1.instance_name)
                           END,
         'value' = CASE spi1.cntr_type
                     WHEN @perfTypeRawFraction -- 32 bit fraction
                       THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
                                                               FROM sys.dm_os_performance_counters spi2
                                                               WHERE (spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('% Base%', spi2.counter_name)))
                                                                 AND (spi1.instance_name = spi2.instance_name)
                                                                 AND (spi2.cntr_type = @perfTypeRawBase))
                     WHEN @perfTypeLargeRawFraction  -- 64 bit fraction
                       THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
                                                               FROM sys.dm_os_performance_counters spi2
                                                               WHERE (spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('% Base%', spi2.counter_name)))
                                                                 AND (spi1.instance_name = spi2.instance_name)
                                                                 AND (spi2.cntr_type = @perfTypeLargeRawBase))
                     ELSE spi1.cntr_value
                   END,
       'type' = spi1.cntr_type
    FROM sys.dm_os_performance_counters spi1
    WHERE (spi1.cntr_type <> @perfTypeRawBase)      -- ignore 32-bit denominator counter type
      AND (spi1.cntr_type <> @perfTypeLargeRawBase) -- ignore 64-bit denominator counter type
  END

END


 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash