O blocker script (versão modificada) é utilizado para coletar informações que auxiliam no diagnóstico de performance SQL Server 2000. Nesse breve artigo, será destacado o trecho que auxilia no diagnóstico de problema de CPU.

Links Relacionados

 

Coleta de Dados

O trecho abaixo apresenta o comando que captura informações relacionadas com as queries que levam ao alto consumo de CPU.

SELECT spid, status, blocked, open_tran, 
        waitresource, waittype, waittime, cmd, lastwaittype, 
        cpu, physical_io,memusage, 
        last_batch=convert(VARCHAR(26), last_batch,121),
        login_time=convert(VARCHAR(26), login_time,121),
        net_address,net_library, 
        dbid, ecid, kpid, hostname, hostprocess, loginame, program_name, 
        nt_domain, nt_username, uid, sid,
        sql_handle, stmt_start, stmt_end
    FROM master.dbo.sysprocesses
    WHERE 
        (kpid<>0 OR waittype<>0x0000 OR open_tran<>0) AND (spid>50)

Importante: A versão modificada (sp_blocker_pfe80) captura informações relacionadas com alto consumo de CPU.

 

O resultado está apresentado abaixo.

SYSPROCESSES
spid   status     blocked open_tran waitresource                   waittype waittime    cmd              lastwaittype                     cpu         physical_io          memusage    last_batch                 login_time                 net_address  net_library  dbid   ecid   kpid   hostname                         hostprocess loginame                         program_name                                                     nt_domain                        nt_username                      uid    sid                                                                                                                                                                            sql_handle                                 stmt_start  stmt_end
------ ---------- ------- --------- ------------------------------ -------- ----------- ---------------- -------------------------------- ----------- -------------------- ----------- -------------------------- -------------------------- ------------ ------------ ------ ------ ------ -------------------------------- ----------- -------------------------------- ---------------------------------------------------------------- -------------------------------- -------------------------------- ------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------ ----------- -----------
51     runnable   0       0                                        0x0000   0           SELECT           WRITELOG                         2046        0                    9           2010-10-13 14:06:27.473    2010-10-13 14:04:08.960    0050569B1E32 TCP/IP       96     0      2856   SRVWEB101                        0           USRAL10                          .Net SqlClient Data Provider                                                                                                       5      0xD394CAAPA4BD0F42A373538079910EBC00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x02006000D2588231000000000100000000000000 222         -1
66     runnable   0       0                                        0x0000   0           SELECT                                            144853      14                   8           2010-10-13 14:06:38.083    2010-10-13 12:12:16.693    821BAAD44ACD TCP/IP       30     0      2720   SRVWAP5101                       0           USRWEBA                          .Net SqlClient Data Provider                                                                                                       0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x02000C00F704CD06000000000100000000000000 132         -1
72     runnable   0       0                                        0x0000   0           SELECT                                            3796        0                    15          2010-10-13 14:06:AP.583    2010-10-13 14:02:58.747    00145E7B6D34 TCP/IP       30     0      3584   SRVWAP5101                       19500       USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
74     runnable   0       0                                        0x0000   0           SELECT                                            10608       0                    14          2010-10-13 14:06:19.740    2010-10-13 13:56:22.163    00145E7B149A TCP/IP       30     0      8004   SRVWAP6102                       45200       USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
92     runnable   0       0                                        0x0000   0           SELECT                                            4343        0                    15          2010-10-13 14:06:27.300    2010-10-13 14:01:43.840    00145E7B6C82 TCP/IP       30     0      2484   SRVWAP7101                       2916        USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
93     runnable   0       0                                        0x0000   0           SELECT                                            6501        0                    15          2010-10-13 14:06:AP.690    2010-10-13 14:00:25.470    00145E7B149A TCP/IP       30     0      3032   SRVWAP6102                       45200       USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
97     runnable   0       0                                        0x0000   0           SELECT                                            8313        0                    15          2010-10-13 14:06:37.473    2010-10-13 13:57:20.AP3    00145E7B149A TCP/IP       30     0      4908   SRVWAP6102                       45200       USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
98     runnable   0       0                                        0x0000   0           SELECT                                            2795        0                    14          2010-10-13 14:06:39.643    2010-10-13 14:04:20.900    00145E7B6CF1 TCP/IP       30     0      4624   SRVWAP6101                       43468       USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
100    runnable   0       0                                        0x0000   0           SELECT                                            60AP        0                    15          2010-10-13 14:06:39.847    2010-10-13 14:00:15.890    00145E7B6CF1 TCP/IP       30     0      6520   SRVWAP6101                       43468       USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
107    runnable   0       0         2:1:24                         0x0000   0           EXECUTE          PAGELATCH_UP                     8782        188                  28          2010-10-13 13:12:01.407    2010-10-13 13:11:22.923    0050569B71EF TCP/IP       1      0      7660   SDEVMM1687                       16720       dbasql5                          Microsoft SQL Server Management Studio - Query                                                                                     1      0x6F703C59090E8647A5EBF0D00C32989000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x03000100BF5F9202010000000000000000000000 0           -1
110    runnable   0       0                                        0x0000   0           SELECT                                            9811        0                    20          2010-10-13 14:06:30.350    2010-10-13 13:57:14.100    00145E7B6C82 TCP/IP       30     0      7912   SRVWAP7101                       2916        USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
113    runnable   0       0                                        0x0000   0           SELECT                                            16316       16                   15          2010-10-13 14:06:40.487    2010-10-13 13:34:37.220    001185C2C8C8 TCP/IP       96     0      2200   SRVW268102                       0           USRAL10                          .Net SqlClient Data Provider                                                                                                       0      0xD394CAAPA4BD0F42A373538079910EBC00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x02006000AEB9A870000000000100000000000000 1658        2460
117    runnable   0       0                                        0x0000   0           SELECT                                            7972        3                    13          2010-10-13 14:06:42.207    2010-10-13 13:45:58.997    001185C2C8C8 TCP/IP       96     0      8060   SRVW268102                       0           USRAL10                          .Net SqlClient Data Provider                                                                                                       5      0xD394CAAPA4BD0F42A373538079910EBC00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x02006000AEB9A870000000000100000000000000 1658        2460
127    runnable   0       0                                        0x0000   0           SELECT                                            1389        0                    16          2010-10-13 14:06:16.503    2010-10-13 14:05:11.600    00145E7B14F8 TCP/IP       30     0      1788   SRVWAP5102                       36748       USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
132    runnable   0       0         96:1:129408                    0x0000   0           SELECT           PAGELATCH_KP                     2157        0                    9           2010-10-13 14:06:27.347    2010-10-13 14:06:21.753    001185C2C8C8 TCP/IP       96     0      2AP2   SRVW268102                       0           USRAL10                          .Net SqlClient Data Provider                                                                                                       5      0xD394CAAPA4BD0F42A373538079910EBC00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x02006000D2588231000000000100000000000000 222         -1
137    runnable   0       0                                        0x0000   0           SELECT                                            22627       7                    22          2010-10-13 14:06:39.660    2010-10-13 13:14:42.947    0050569B1E32 TCP/IP       96     0      7428   SRVWEB101                        0           USRAL10                          .Net SqlClient Data Provider                                                                                                       0      0xD394CAAPA4BD0F42A373538079910EBC00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x02006000AEB9A870000000000100000000000000 1658        2460
149    runnable   0       0                                        0x0000   0           SELECT                                            7826        0                    15          2010-10-13 14:06:14.630    2010-10-13 13:59:16.5AP    00145E7B6C82 TCP/IP       30     0      4588   SRVWAP7101                       2916        USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
151    runnable   0       0                                        0x0000   0           SELECT           NETWORKIO                        4955        0                    14          2010-10-13 14:06:39.893    2010-10-13 13:46:45.400    001185C2C8C8 TCP/IP       96     0      6176   SRVW268102                       0           USRAL10                          .Net SqlClient Data Provider                                                                                                       0      0xD394CAAPA4BD0F42A373538079910EBC00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x02006000AEB9A870000000000100000000000000 1658        2460
153    runnable   0       0                                        0x0000   0           SELECT                                            5955        0                    15          2010-10-13 14:06:30.927    2010-10-13 14:00:25.313    00145E7B6C82 TCP/IP       30     0      6324   SRVWAP7101                       2916        USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
159    runnable   0       0                                        0x0000   0           SELECT                                            7826        0                    15          2010-10-13 14:06:36.490    2010-10-13 13:59:35.410    00145E7B6D34 TCP/IP       30     0      5396   SRVWAP5101                       19500       USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
164    runnable   0       0                                        0x0000   0           SELECT                                            2329        0                    15          2010-10-13 14:06:39.770    2010-10-13 14:04:31.803    00145E7B14F8 TCP/IP       30     0      6476   SRVWAP5102                       36748       USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
100    runnable   0       0                                        0x0000   0           SELECT                                            250         0                    15          1900-01-01 00:00:00.000    2010-10-13 14:06:30.020    00145E7B6CF1 TCP/IP       30     0      6384   SRVWAP6101                       43468       USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
184    runnable   0       0                                        0x0000   0           SELECT                                            796         0                    15          2010-10-13 14:06:27.050    2010-10-13 14:05:48.193    00145E7B149A TCP/IP       30     0      7748   SRVWAP6102                       45200       USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
191    runnable   0       0                                        0x0000   0           SELECT                                            8140        0                    14          2010-10-13 14:06:36.160    2010-10-13 13:58:47.283    00145E7B14F8 TCP/IP       30     0      5000   SRVWAP5102                       36748       USRWEBA                          Internet Information Services                                                                                                      0      0x6124CCFC79E7224492F77DCBP9512B2700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000C00AED08A19C06096270000000000000000 100         -1
  

 

 

Análise

A execução da query é rápida e não impacta na performance do servidor. Se o servidor estiver apresentando sintomas de 100% CPU, então o resultado será semelhante a figura a seguir.

image

Observamos que:

  • Há uma grande quantidade de sessões com STATUS=running/runnable e WAITTYPE=0x0000.
  • A coluna CMD indica qual o comando provável pelo alto consumo de CPU

 

Identificando a Query

A query pode ser identificada através das últimas colunas presentes no resultado: sql_handle, stmt_start, stmt_end.

image

Na maioria dos casos, o comando (sql_handle) que apresentar maior número de repetição corresponde ao problema. De acordo com a figura acima, sabemos que o problema afeta o comando:

(sql_handle, stmt_start, stmt_end)
(0x01000C00AED08A19C06096270000000000000000, 100, –1)

Veja que os valores 0x01000C00AED08A19C06096270000000000000000, 100, –1 são aqueles que mais aparecem. Para finalizar, basta rodar o comando:

DECLARE @handle VARBINARY(64) = 0x01000C00AED08A19C06096270000000000000000
DECLARE @start  INT = 100
DECLARE @end    INT = -1
DECLARE @len    INT
                        
SELECT SUBSTRING(text,@start/2,
            CASE WHEN @end > 0    
                THEN  (@end - @start)/2
                ELSE  LEN([text]) 
            END) 
FROM sys.dm_exec_sql_text(@handle)

Retornando a query que causa problemas.

image