{"id":206,"date":"2010-12-25T11:27:34","date_gmt":"2010-12-25T13:27:34","guid":{"rendered":"http:\/\/fabriciodba.wordpress.com\/?p=206"},"modified":"2017-02-04T14:33:04","modified_gmt":"2017-02-04T16:33:04","slug":"casos-do-dia-a-dia-diminuindo-um-problema-de-memoria-no-sql-server","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2010\/12\/25\/casos-do-dia-a-dia-diminuindo-um-problema-de-memoria-no-sql-server\/","title":{"rendered":"Casos do Dia a Dia – Diminu\u00edndo um problema de mem\u00f3ria no SQL Server"},"content":{"rendered":"
\ufeff<\/div>\n

Fala Pessoal,<\/p>\n

Em meu ambiente de trabalho, possuo um servidor 32 bits com o SQL Server 2005 32 bits,\u00a0com 4 GB de mem\u00f3ria e com a op\u00e7\u00e3o \/3GB habilitada. Esse servidor possui uma m\u00e9dia de 1500 conex\u00f5es simult\u00e2neas no banco de dados durante o hor\u00e1rio de pico e possui 200 Gb de dados. Repetindo, 200 GB de dados e 4 GB de mem\u00f3ria.<\/p>\n

Como pode ser visto na figura abaixo, o servi\u00e7o do SQL Server consegue utilizar 2.51 GB de mem\u00f3ria nesse servidor.
\n<\/a><\/p>\n

<\/a><\/p>\n

\"\"<\/a><\/p>\n

Essa informa\u00e7\u00e3o foi gerada com a query abaixo:<\/p>\n

select counter_name ,cntr_value,cast((cntr_value\/1024.0)\/1024.0 as numeric(8,2)) as Gb
\nfrom sys.dm_os_performance_counters
\nwhere counter_name like ‘%server_memory%’;<\/p>\n

Voc\u00eas devem concordar comigo que 2.51 GB de mem\u00f3ria \u00e9 muito pouco para a quantidades de dados que possuo. Com a query abaixo \u00e9 poss\u00edvel verificar o contador Page Life Expectancy, que \u00e9 a expectativa de vida em segundos de uma p\u00e1gina de dados na mem\u00f3ria. No meu ambiente, esse contador tinha uma m\u00e9dia entre 30 e 40\u00a0 segundos durante o dia, sendo que um n\u00famero consider\u00e1vel aceit\u00e1vel para esse contador \u00e9 acima de 300 segundos.<\/p>\n

SELECT cntr_value AS ‘Page Life Expectancy’
\nFROM sys.dm_os_performance_counters
\nWHERE object_name = ‘SQLServer:Buffer Manager’
\nAND counter_name = ‘Page life expectancy’<\/p>\n

Resultado:
\n\"\"<\/p>\n

Antes de realizar uma reestrutura\u00e7\u00e3o nos meus \u00edndices (criar missed index, alterar o fillfactor, excluir \u00edndices duplicados e excluir indices n\u00e3o utilizados), esse contador tinha uma m\u00e9dia\u00a0abaixo de 20 segundos em determinadas horas do dia.<\/p>\n

Buscando uma forma de amenizar esse problema(at\u00e9 que seja realizado um upgrade de Hardware), dei uma boa pesquisada sobre mem\u00f3ria e verifiquei que desses 2.51 GB de mem\u00f3ria, o SQL Server estava utilizando 1.5 GB de mem\u00f3ria para outros tipos de cache diferentes do Data Cache (Buffer Pool).<\/p>\n

Olha que absurdo, 1.5 GB para alguns caches (Ex: Caches de Plano de Execu\u00e7\u00e3o de Procedures e de Adhoc Querys)\u00a0 e apenas 1 GB para o Buffer Pool, onde realmente est\u00e3o as paginas de dados que s\u00e3o utilizadas por qualquer manipula\u00e7\u00e3o de dados no SQL Server.
\nQuando voc\u00ea realiza uma consulta, caso as p\u00e1ginas de dados que sua consulta deveria retornar n\u00e3o estejam no Buffer Pool, as p\u00e1ginas s\u00e3o lidas do disco, armazenadas no Buffer Pool e em seguida s\u00e3o retornadas para a sua consulta. Ou seja, quanto maior for o tamanho do Buffer Pool, mais p\u00e1ginas de dados ser\u00e3o armazenadas na mem\u00f3ria, menos leituras ser\u00e3o realizadas no disco e, consequentemente, o ganho de performance ser\u00e1 muito grande.<\/p>\n

Executando as querys abaixo, foi poss\u00edvel identificar o quanto de mem\u00f3ria estava sendo utilizado por alguns tipos de Cache do SQL Server:<\/p>\n

— Utiliza\u00e7\u00e3o por tipo de cache
\nSELECT\u00a0 type, SUM(single_pages_kb)\/1024. AS [SPA Mem, MB],SUM(Multi_pages_kb)\/1024. AS [MPA Mem,MB]\nFROM sys.dm_os_memory_clerks
\nGROUP BY type
\nHAVING\u00a0 SUM(single_pages_kb) + sum(Multi_pages_kb)\u00a0 > 40000 — S\u00f3 os maiores consumidores de mem\u00f3ria
\nORDER BY SUM(single_pages_kb) DESC<\/p>\n

— Total utilizado
\nSELECT\u00a0 SUM(single_pages_kb)\/1024. AS [SPA Mem, KB],SUM(Multi_pages_kb)\/1024. AS [MPA Mem, KB]\nFROM sys.dm_os_memory_clerks<\/p>\n

A figura abaixo mostra como estava o resultado dessas querys no meu ambiente no hor\u00e1rio de produ\u00e7\u00e3o.<\/p>\n

\"\"<\/a><\/p>\n

O CACHESTTORE_OBJCP\u00a0 \u00e9 o cache das Stored procedures, Triggers e Functions.<\/p>\n

O CACHESTORE_SQLCP \u00e9 o cache de Ad-hoc queries e n\u00e3o \u00e9 muito reutilizado pelo SQL Server, pois para uma mesma consulta com par\u00e2metros diferentes, s\u00e3o gerados dois planos de execu\u00e7\u00f5es diferentes.<\/p>\n

O USERSTORE_TOKENOERM \u00e9 o cache que armazena v\u00e1rias informa\u00e7\u00f5es de seguran\u00e7a que s\u00e3o utilizadas pela Engine do SQL Server.<\/p>\n

Analisando essas informa\u00e7\u00f5es, tomei as seguintes medidas:<\/p>\n

Como o CACHESTTORE_OBJCP \u00e9 muito reutilizado, n\u00e3o mechi no mesmo pois poderia piorar ainda mais meu problema de performance. Se no meu servidor tivesse a vers\u00e3o do SQL Server 2008, eu poderia excluir individualmente do cache alguns planos de execu\u00e7\u00e3o que ocupam mais espa\u00e7o na mem\u00f3ria e s\u00e3o poucos utilizados. Mas como ainda est\u00e1 na vers\u00e3o do SQL Server 2005, n\u00e3o tive o que fazer.<\/p>\n

J\u00e1 os caches CACHESTORE_SQLCP e USERSTORE_TOKENOERM, que ocupavam juntos mais de 1 GB de mem\u00f3ria, me ajudariam bastante liberando mem\u00f3ria para o Buffer Pool. Entretanto, limpando o cache CACHESTORE_SQLCP eu aumentaria meu consumo de CPU pois incrementaria o n\u00famero de compila\u00e7\u00f5es de algumas querys. Todavia, como meu gargalo \u00e9 mem\u00f3ria, resolvi limpar esses caches assim mesmo.<\/p>\n

Criei uma Stored Procedure que roda de 3 em 3 minutos para realizar a limpeza desses caches\u00a0 e ainda armazeno um log da frequencia com que esses caches s\u00e3o limpos em uma tabela para hist\u00f3rico conforme os scripts abaixo:<\/p>\n

CREATE TABLE [dbo].[Log_Limpeza_Cache](
\n[Id_Log] [int] IDENTITY(1,1) ,
\n[Dt_Limpeza] [datetime] ,
\n[Tipo_Cache] [varchar](30) ,
\n[Tamanho_MB] [numeric](15, 2) )<\/p>\n

CREATE PROCEDURE [dbo].[stpLimpa_Memory_Cache]\nAS
\nBEGIN
\nDECLARE\u00a0@USERSTORE_TOKENPERM numeric(15,2), @CACHESTORE_SQLCP numeric(15,2)
\nSELECT @CACHESTORE_SQLCP = SUM(single_pages_kb+Multi_pages_kb)\/1024.
\nFROM sys.dm_os_memory_clerks
\nWHERE type = ‘CACHESTORE_SQLCP’<\/p>\n

SELECT @USERSTORE_TOKENPERM = SUM(single_pages_kb+Multi_pages_kb)\/1024.
\nFROM sys.dm_os_memory_clerks
\nWHERE type = ‘USERSTORE_TOKENPERM’<\/p>\n

IF @USERSTORE_TOKENPERM > 30
\nbegin
\ninsert into Log_Limpeza_Cache(Dt_Limpeza,Tipo_Cache,Tamanho_MB)
\nselect getdate(), ‘USERSTORE_TOKENPERM’, @USERSTORE_TOKENPERM
\nDBCC FREESYSTEMCACHE(‘TokenAndPermUserStore’)
\nend
\nIF @CACHESTORE_SQLCP > 60
\nbegin
\ninsert into Log_Limpeza_Cache(Dt_Limpeza,Tipo_Cache,Tamanho_MB)
\nselect getdate(), ‘CACHESTORE_SQLCP’, @CACHESTORE_SQLCP<\/p>\n

DBCC FREESYSTEMCACHE(‘SQL Plans’)
\nend<\/p>\n

END<\/p>\n

Ap\u00f3s a primeira limpeza do cache(momentos de tens\u00e3o), fiquei acompanhando meu trace com as querys mais demoradas\u00a0do banco de dados<\/a> para ver se teria algum problema de performance. Entretanto, isso n\u00e3o aconteceu. Pelo contr\u00e1rio, a quantidade de querys lentas diminu\u00edram.<\/p>\n

Ap\u00f3s alguns minutos, analisando os contadores de performance, o Page life Expectancy passou a ter uma m\u00e9dia entre 100 e 120 segundos. Simplesmente triplicou.<\/p>\n

Meu buffer pool, aumentou para mais de 2 GB, ou seja, a quantidade de dados que o SQL Server disponibiliza em mem\u00f3ria para ser utilizada sem acessar o disco dobrou. Eu verifico essa informa\u00e7\u00e3o com a query abaixo:<\/p>\n

SELECT DB_NAME(database_id) AS [Database Name],
\nCOUNT(*) * 8\/1024.0 AS [Cached Size (MB)]\nFROM sys.dm_os_buffer_descriptors
\nWHERE database_id > 4 –\u2013- exclude system databases
\nAND database_id <> 32767– \u2013- exclude ResourceDB
\nGROUP BY DB_NAME(database_id)
\nORDER BY [Cached Size (MB)] DESC;<\/p>\n

Como j\u00e1 era esperado, tamb\u00e9m tive um aumento de processamento e de recompiles, mas o benef\u00edcio da libera\u00e7\u00e3o de mem\u00f3ria foi maior para o meu ambiente. O n\u00famero de procedimentos demorando mais de 3 segundos diminu\u00edu consideravelmente.<\/p>\n

Al\u00e9m dessa altera\u00e7\u00e3o, fiz muitas outras altera\u00e7\u00f5es de performance para esse servidor aguentar o grande aumento de acesso que temos no Banco de Dados nesse Natal, mas acredito que essa altera\u00e7\u00e3o foi\u00a0a que fez a maior diferen\u00e7a e me proporcionou um Natal bem tranquilo. Um excelente presente de Natal.<\/p>\n

Claro que antes de\u00a0realizar essas altera\u00e7\u00f5es no meu servidor de produ\u00e7\u00e3o, testei em outros servidores menos cr\u00edticos. Deixo essa experi\u00eancia para quem possui um ambiente com esse mesmo tipo de problema. Espero que possa ter ajudado.<\/p>\n

 <\/p>\n

Gostou dessa Dica?<\/b><\/p>\n

Curta, comente, compartilhe\u2026<\/p>\n

Assine meu canal no Youtube<\/a> e curta minha p\u00e1gina no Facebook<\/a> para receber Dicas de Leituras, V\u00eddeos e Eventos sobre SQL Server.<\/p>\n

Confira mais experi\u00eancias do Dia a Dia de um DBA no meu Treinamento de Tarefas do Dia a Dia de um DBA<\/a>.<\/p>\n

At\u00e9 a pr\u00f3xima.<\/p>\n

Fabr\u00edcio Lima<\/p>\n

MCITP \u2013 Database Administrator<\/p>\n

Consultor e Instrutor SQL Server<\/p>\n

Trabalha com SQL Server desde 2006<\/p>\n","protected":false},"excerpt":{"rendered":"

\ufeff Fala Pessoal, Em meu ambiente de trabalho, possuo um servidor 32 bits com o SQL Server 2005 32 bits,\u00a0com 4 GB de mem\u00f3ria e com a op\u00e7\u00e3o \/3GB habilitada. Esse servidor possui uma m\u00e9dia de 1500 conex\u00f5es simult\u00e2neas no banco de dados durante o hor\u00e1rio de pico e possui 200 Gb de dados. Repetindo, […]<\/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,6,280],"tags":[75,119,121,124,123,23,84,126,25,127,122,80,29,120,63,128,49,33,81,40,125],"class_list":["post-206","post","type-post","status-publish","format-standard","hentry","category-administracao-de-bd","category-casos-do-dia-a-dia","category-virtual-pass-br","tag-3gb","tag-buffer-manager","tag-cache-sql-server","tag-cachestore_sqlcp","tag-cachesttore_objcp","tag-dba","tag-dbcc","tag-dbcc-freesystemcache","tag-dmv","tag-dm_os_buffer_descriptors","tag-dm_os_memory_clerks","tag-dm_os_performance_counters","tag-memoria","tag-page-life-expectancy","tag-performance","tag-problema-memoria","tag-sql","tag-sql-server","tag-target-server-memory","tag-tuning","tag-userstore_tokenoerm"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/206","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=206"}],"version-history":[{"count":0,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/206\/revisions"}],"wp:attachment":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/media?parent=206"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/categories?post=206"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/tags?post=206"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}