{"id":12055,"date":"2020-04-19T07:50:48","date_gmt":"2020-04-19T10:50:48","guid":{"rendered":"https:\/\/www.fabriciolima.net\/?p=12055"},"modified":"2020-05-11T20:31:46","modified_gmt":"2020-05-11T23:31:46","slug":"monitoramento-no-sql-server-o-que-fazer-quando-receber-um-alerta-de-cpu","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2020\/04\/19\/monitoramento-no-sql-server-o-que-fazer-quando-receber-um-alerta-de-cpu\/","title":{"rendered":"Monitoramento no SQL Server &#8211; O que fazer quando receber um alerta de CPU?"},"content":{"rendered":"<p>Fala Pessoal,<\/p>\n<p>Em Setembro de 2019 liberei uma nova vers\u00e3o dos meus scripts para a cria\u00e7\u00e3o de 40 alertas no SQL Server:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.fabriciolima.net\/blog\/2019\/09\/22\/passo-a-passo-de-como-criar-40-alertas-para-monitorar-seu-sql-server\/\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/www.fabriciolima.net\/blog\/2019\/09\/22\/passo-a-passo-de-como-criar-40-alertas-para-monitorar-seu-sql-server\/<\/a><\/li>\n<\/ul>\n<p>Legal Fabr\u00edcio, mas o que eu fa\u00e7o quando receber esses Alertas?<\/p>\n<p>Esse \u00e9 o quinto v\u00eddeo para ajudar a dar essa resposta para voc\u00eas.<\/p>\n<p>Nesse v\u00eddeo vou mostrar o que voc\u00ea pode analisar quando receber um alerta de CPU para ajudar a resolver seu problema de lentid\u00e3o:<\/p>\n<p><iframe loading=\"lazy\" title=\"Monitoramento no SQL Server - O que fazer quando receber um alerta de CPU?\" width=\"1170\" height=\"658\" src=\"https:\/\/www.youtube.com\/embed\/z7Gu_ufXuzs?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen><\/iframe><\/p>\n<header><\/header>\n<header>C\u00f3digo utilizado no v\u00eddeo:<\/header>\n<header><\/header>\n<header>\n<pre class=\"lang:default decode:true \">select *\r\nfrom Alert_Parameter\r\nwhere Nm_Alert = 'CPU Utilization'\r\n\r\nupdate Alert_Parameter\r\nset Vl_Parameter = 40\r\nwhere Nm_Alert = 'CPU Utilization'\r\n\r\n-- Rodei as queries das outras conex\u00f5es\r\n\r\nupdate Alert_Parameter\r\nset Vl_Parameter = 85\r\nwhere Nm_Alert = 'CPU Utilization'\r\n\r\n\r\n\r\n\r\n--Conferir a Vers\u00e3o do SQL Server\r\nSELECT @@version\r\n\r\n\r\n-- Quantidade de cores dispon\u00edveis para o SQL Server\r\nSELECT current_tasks_count,runnable_tasks_count,* \r\nFROM sys.dm_os_schedulers\r\nWHERE scheduler_id &lt; 255\r\nAND status = 'VISIBLE ONLINE'\r\n\r\n--J\u00e1 peguei casos do cliente ter 8 sockets configurados no SQL Server Standard, e o SQL estar utilizando apenas 4.\r\n\t \r\n\r\n--------------------- Algumas dicas para se fazer quando tiver um gargalo de CPU\r\n\r\n-- Conferir no task Manager se tem outro processo consumindo muita CPU al\u00e9m do SQL Server\r\n\r\n\r\n-- Validar a o que est\u00e1 rodando no banco de dados\r\nexec sp_whoisactive @get_task_info =2, @get_plans = 1, @delta_interval = 1, @show_sleeping_spids = 0, @get_outer_command = 1\r\n\r\n\r\n-- Queries nesse momento utilizando os cores de CPU\r\nSELECT \r\na.scheduler_id ,\r\nb.session_id,\r\n (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset \/ 2+1 , \r\n      ( (CASE WHEN statement_end_offset = -1 \r\n         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) \r\n         ELSE statement_end_offset END)  - statement_start_offset) \/ 2+1))  AS sql_statement\r\nFROM sys.dm_os_schedulers a \r\nINNER JOIN sys.dm_os_tasks b on a.active_worker_address = b.worker_address\r\nINNER JOIN sys.dm_exec_requests c on b.task_address = c.task_address\r\nCROSS APPLY sys.dm_exec_sql_text(c.sql_handle) AS s2 \r\n\r\n\r\n\r\n-- Se for uma procedure, rodar um sp_recompile\r\n\r\n-- Limpar o PlanCache\r\nGO  \r\nSELECT plan_handle, st.text  \r\nFROM sys.dm_exec_cached_plans   \r\nCROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st  \r\nWHERE text LIKE N'%Gols_Jogadores_SQLGOL%';  \r\nGO  \r\n-- Remove the specific plan from the cache.  \r\nDBCC FREEPROCCACHE (0x0600070002DAC81B200AF7C81402000001000000000000000000000000000000000000000000000000000000);  \r\nGO  \r\nDBCC FREEPROCCACHE\r\n\r\n-- Atualizar as estat\u00edsticas das tabelas envolvidas\r\n\r\n--Analisar a possibilidade de cria\u00e7\u00e3o de um \u00edndice ou reescrita das queries envolvidas\r\n\r\n\r\n--Queries que mais consomem CPU no traces\r\nSELECT *\r\nFROM Traces..Queries_Profile\r\nORDER BY CPU DESC\r\n\r\n\r\n--Queries do Cache com maior consumo de CPU\r\nif object_id('tempdb..#Temp_Trace') is not null drop table #Temp_Trace\r\n\r\nsELECT TOP 50 total_worker_time ,  sql_handle,execution_count,last_execution_time,last_worker_time\r\ninto #Temp_Trace\r\nFROM sys.dm_exec_query_stats A\r\nwhere last_elapsed_time &gt; 20\r\n and last_execution_time &gt; dateadd(ss,-600,getdate()) --ultimos 5 min\r\norder by A.total_worker_time desc\r\n\r\nselect distinct A.*, B.*, DB.name\r\nfrom #Temp_Trace A\r\ncross apply sys.dm_exec_sql_text (sql_handle) B\r\njoin sys.databases DB on B.dbid = DB.database_id\r\norder by 1 DESC\r\n\r\n\r\n--Consumo de CPU por base de dados\r\nWITH DB_CPU_Statistics\r\nAS\r\n(SELECT pa.DatabaseID, DB_NAME(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time\/1000) AS [CPU_Time_Ms]\r\nFROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)\r\nCROSS APPLY (SELECT CONVERT(INT, value) AS [DatabaseID]\r\nFROM sys.dm_exec_plan_attributes(qs.plan_handle)\r\nWHERE attribute = N'dbid') AS pa\r\nGROUP BY DatabaseID)\r\nSELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Ranking],\r\n[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],\r\nCAST([CPU_Time_Ms] * 1.0 \/ SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]\r\nFROM DB_CPU_Statistics\r\nWHERE DatabaseID &lt;&gt; 32767 -- ResourceDB\r\nORDER BY [CPU Ranking] OPTION (RECOMPILE);\r\n\r\n\r\n--Pode olhar as queries que mais consomem CPU no Query Store \r\n\r\n--Posts de query store no meu blog\r\nhttps:\/\/www.fabriciolima.net\/blog\/tag\/query-store\/\r\n<\/pre>\n<p>&nbsp;<\/p>\n<\/header>\n<header class=\"entry-header\"><b>Gostou desse Post?<\/b><\/header>\n<p>Curta, comente, compartilhe\u2026<\/p>\n<p>Curta nossa p\u00e1gina no\u00a0<a class=\"external-link wpel-icon-left\" href=\"https:\/\/www.facebook.com\/SouPowerTuning\" target=\"_blank\" rel=\"external noopener noreferrer nofollow\" data-wpel-link=\"external\">Facebook<\/a>\u00a0,\u00a0<a class=\"external-link wpel-icon-left\" href=\"https:\/\/www.linkedin.com\/company\/sou-powertuning\" target=\"_blank\" rel=\"noopener external noreferrer nofollow\" data-wpel-link=\"external\">LinkedIn<\/a>\u00a0e\u00a0<a class=\"external-link wpel-icon-left\" href=\"https:\/\/www.instagram.com\/soupowertuning\/\" target=\"_blank\" rel=\"noopener external noreferrer nofollow\" data-wpel-link=\"external\">Instagram<\/a>\u00a0para receber Dicas de Leituras, V\u00eddeos e Eventos sobre SQL Server.<\/p>\n<p>At\u00e9 a pr\u00f3xima.<\/p>\n<p>Abra\u00e7os,<\/p>\n<p>Fabr\u00edcio Lima<\/p>\n<p>CEO na Fabr\u00edcio Lima Solu\u00e7\u00f5es em BD<\/p>\n<p>Microsoft Data Platform MVP<\/p>\n<p>Instagram:<a class=\"external-link wpel-icon-left\" href=\"https:\/\/www.instagram.com\/fabriciofrancalima\/\" target=\"_blank\" rel=\"noopener external noreferrer nofollow\" data-wpel-link=\"external\">@fabriciofrancalima<\/a><\/p>\n<p>Linkedin:\u00a0<a class=\"external-link wpel-icon-left\" href=\"https:\/\/www.linkedin.com\/in\/fabriciofrancalima\/\" target=\"_blank\" rel=\"noopener noreferrer external nofollow\" data-wpel-link=\"external\">https:\/\/www.linkedin.com\/in\/fabriciofrancalima\/<\/a><\/p>\n<p>Consultoria:\u00a0comercial@powertuning.com.br<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Fala Pessoal, Em Setembro de 2019 liberei uma nova vers\u00e3o dos meus scripts para a cria\u00e7\u00e3o de 40 alertas no SQL Server: https:\/\/www.fabriciolima.net\/blog\/2019\/09\/22\/passo-a-passo-de-como-criar-40-alertas-para-monitorar-seu-sql-server\/ Legal Fabr\u00edcio, mas o que eu fa\u00e7o quando receber esses Alertas? Esse \u00e9 o quinto v\u00eddeo para ajudar a dar essa resposta para voc\u00eas. Nesse v\u00eddeo vou mostrar o que voc\u00ea pode [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[3,991,1248,33,280],"tags":[1778,1566,1261,1268,1780,1328,1779,1777,1782,1262,1270,1781,33,1271],"class_list":["post-12055","post","type-post","status-publish","format-standard","hentry","category-administracao-de-bd","category-dba-remoto","category-queries-do-dia-a-dia","category-sql-server","category-virtual-pass-br","tag-100-de-cpu","tag-alerta-cpu","tag-alertas","tag-cpu","tag-cpu-alta","tag-cpu-sql-server","tag-gargalo-de-cpu","tag-lentidao-cpu","tag-monitoramento-cpu","tag-monitoramento-sql-server","tag-problema-cpu","tag-queries-consumindo-cpu","tag-sql-server","tag-sql-server-cpu"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/12055","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/comments?post=12055"}],"version-history":[{"count":4,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/12055\/revisions"}],"predecessor-version":[{"id":12160,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/12055\/revisions\/12160"}],"wp:attachment":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/media?parent=12055"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/categories?post=12055"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/tags?post=12055"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}