Análise: sysprocesses - Identificando Alto Consumo de CPU
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
- Blocker Script original: sp_blocker_pss80
- Versão SQL2000: Blocker Modificado
- Coleta de dados no SQL 2008–Script 1
- Coleta de dados no SQL 2008–Script 2
- Coleta de dados no SQL 2008–Script 3
- Versão final: Monitor SQL (Versão atualizada do Blocker Script)
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.
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.
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.
