Fabrício Lima

Fala Pessoal,

Um certo dia, no meio do meu expediente normal de trabalho, recebi a notificação abaixo por e-mail:

T: Label:TempDB is 92 Percentage, threshold value for this monitor is 80 Percentage

Estava acabando o espaço em disco no drive T: onde o Tempdb está armazenado em um dos  meus servidores SQL Server.

Na mesma hora, executei a query abaixo, que identifica o espaço que está sendo utilizado por cada conexão do SQL Server:

SELECT A.session_id,B.host_name, B.Login_Name ,
(user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 as TotalalocadoMB,
D.Text
FROM sys.dm_db_session_space_usage A
JOIN sys.dm_exec_sessions B  ON A.session_id = B.session_id
JOIN sys.dm_exec_connections C ON C.session_id = B.session_id
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) As D
WHERE A.session_id > 50
and (user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 > 100 — Ocupam mais de 100 MB
ORDER BY totalalocadoMB desc
COMPUTE sum((user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128)

Com essa query, identifiquei uma conexão de uma pessoa da nossa própria equipe de TI que estava ocupando 51.5 GB do Tempdb.

Na mesma hora matei a conexão e fui obrigado a executar um Shrink(Fui Obrigado!) para liberar espaço no drive do Tempdb. Caso você não consiga diminuir o espaço ocupado pelo TempDB executanto um shrink, que foi exatamente o meu caso, esse artigo é a solução para esse problema: tempdb, Please allow me to shrink you.

As duas variáveis que são somadas para contar o espaço alocado do Tempdb tem as seguintes propriedades:
User_objects_alloc_page_count – Número de páginas reservadas ou alocadas para objetos de usuários, tais como:

• Tabelas e Índices de Usuários
• Tabelas e Índices de Sistema
• Tabelas e Índices temporários globais
• Tabelas e Índices temporários locais
• Tabelas de variáveis
• Tabelas retornadas em uma Table-Function

Internal_objects_alloc_page_count – Número de páginas reservadas ou alocadas para objetos internos, tais como:

• Work Tables para cursor, Spool Operations e LOB storage
• Work Files para operações como Hash Join
• Sort Runs

Essa query de monitoramento do espaço alocado no TempDB, retorna apenas informações de consultas que já rodaram, portanto, as querys em execução não são consideradas. Para monitorar as querys que ainda estão em execução, aconselho a utilização da procedure Who_is_Active.

Abraços,

Fabrício França Lima

Fala Pessoal,

Antes de incluir/excluir/alterar um campo de uma tabela ou antes de incluir/excluir/alterar uma procedure, function ou view se faz necessário procurar no script do banco de dados se esses objetos que serão alterados são utilizados por alguma função, view ou SP.

Constantemente eu preciso realizar esse tipo de operação. Para isso, utilizo a query abaixo que procura um determinado texto em procedures, view e functions em uma determinada database.

– Compatível com o SQL Server 2000

SELECT B.name ,
case type when ‘P’ then ‘Stored procedure’
when FN’ then ‘Function’
when ‘TF’ then ‘Function’
when ‘TR’ then ‘Trigger’
when ‘V’ then ‘View’
else ‘Outros Objetos’
end
FROM syscomments A (nolock)
JOIN sysobjects B (nolock) on A.Id = B.Id
WHERE A.Text like ‘%Nome_Objeto%’  –Objto a ser procurado
ORDER BY 2 DESC

– 2005/2008
SELECT type_desc, obj.name AS SP_NAME,  sqlmod.definition AS SP_DEFINITION
FROM sys.sql_modules AS sqlmod
INNER JOIN sys.objects AS obj ON sqlmod.object_id = obj.object_id
WHERE sqlmod.definition LIKE ‘%Nome_Objeto%’  –Objto a ser procurado
ORDER BY type_desc

Entretanto, seu objeto ainda pode ser utilizado diretamente dentro de algum step de seus jobs. Para fazer uma busca em todos os Steps de Jobs por um texto, basta rodar a query abaixo:

– Procura dentro de Job’s
SELECT name NOME_JOB, step_name ,command CODIGO, last_run_date
FROM msdb.dbo.sysjobs A
join msdb.dbo.sysjobsteps B on A.Job_id = B.Job_Id
WHERE command like ‘%Nome_Objeto%’
ORDER BY name

Também existem ferramentas de terceiros que realizam essas consultas.

Nunca encontrei um modo parecido de realizar uma busca de um objeto, que está sendo utilizado dentro de uma task de um DTS(SQL Server 2000) ou SSIS (2005/2008). Caso exista, deixe um comentário que será de grande valor.

Abraços,

Fabrício França Lima

Fala Pessoal,

Em meu ambiente de trabalho, possuo um servidor 32 bits com o SQL Server 2005 32 bits, com 4 GB de memória e com a opção /3GB habilitada. Esse servidor possui uma média de 1500 conexões simultâneas no banco de dados durante o horário de pico e possui 200 Gb de dados. Repetindo, 200 GB de dados e 4 GB de memória.

Como pode ser visto na figura abaixo, o serviço do SQL Server consegue utilizar 2.51 GB de memória nesse servidor.

Essa informação foi gerada com a query abaixo:

select counter_name ,cntr_value,cast((cntr_value/1024.0)/1024.0 as numeric(8,2)) as Gb
from sys.dm_os_performance_counters
where counter_name like ‘%server_memory%’;

Vocês devem concordar comigo que 2.51 GB de memória é muito pouco para a quantidades de dados que possuo. Com a query abaixo é possível verificar o contador Page Life Expectancy, que é a expectativa de vida em segundos de uma página de dados na memória. No meu ambiente, esse contador tinha uma média entre 30 e 40  segundos durante o dia, sendo que um número considerável aceitável para esse contador é acima de 300 segundos.

SELECT cntr_value AS ‘Page Life Expectancy’
FROM sys.dm_os_performance_counters
WHERE object_name = ‘SQLServer:Buffer Manager’
AND counter_name = ‘Page life expectancy’

Resultado:

Antes de realizar uma reestruturação nos meus índices (criar missed index, alterar o fillfactor, excluir índices duplicados e excluir indices não utilizados), esse contador tinha uma média abaixo de 20 segundos em determinadas horas do dia.

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

Olha que absurdo, 1.5 GB para alguns caches (Ex: Caches de Plano de Execução de Procedures e de Adhoc Querys)  e apenas 1 GB para o Buffer Pool, onde realmente estão as paginas de dados que são utilizadas por qualquer manipulação de dados no SQL Server.
Quando você realiza uma consulta, caso as páginas de dados que sua consulta deveria retornar não estejam no Buffer Pool, as páginas são lidas do disco, armazenadas no Buffer Pool e em seguida são retornadas para a sua consulta. Ou seja, quanto maior for o tamanho do Buffer Pool, mais páginas de dados serão armazenadas na memória, menos leituras serão realizadas no disco e, consequentemente, o ganho de performance será muito grande.

Executando as querys abaixo, foi possível identificar o quanto de memória estava sendo utilizado por alguns tipos de Cache do SQL Server:

– Utilização por tipo de cache
SELECT  type, SUM(single_pages_kb)/1024. AS [SPA Mem, MB],SUM(Multi_pages_kb)/1024. AS [MPA Mem,MB]
FROM sys.dm_os_memory_clerks
GROUP BY type
HAVING  SUM(single_pages_kb) + sum(Multi_pages_kb)  > 40000 — Só os maiores consumidores de memória
ORDER BY SUM(single_pages_kb) DESC

– Total utilizado
SELECT  SUM(single_pages_kb)/1024. AS [SPA Mem, KB],SUM(Multi_pages_kb)/1024. AS [MPA Mem, KB]
FROM sys.dm_os_memory_clerks

A figura abaixo mostra como estava o resultado dessas querys no meu ambiente no horário de produção.

O CACHESTTORE_OBJCP  é o cache das Stored procedures, Triggers e Functions.

O CACHESTORE_SQLCP é o cache de Ad-hoc queries e não é muito reutilizado pelo SQL Server, pois para uma mesma consulta com parâmetros diferentes, são gerados dois planos de execuções diferentes.

O USERSTORE_TOKENOERM é o cache que armazena várias informações de segurança que são utilizadas pela Engine do SQL Server.

Analisando essas informações, tomei as seguintes medidas:

Como o CACHESTTORE_OBJCP é muito reutilizado, não mechi no mesmo pois poderia piorar ainda mais meu problema de performance. Se no meu servidor tivesse a versão do SQL Server 2008, eu poderia excluir individualmente do cache alguns planos de execução que ocupam mais espaço na memória e são poucos utilizados. Mas como ainda está na versão do SQL Server 2005, não tive o que fazer.

Já os caches CACHESTORE_SQLCP e USERSTORE_TOKENOERM, que ocupavam juntos mais de 1 GB de memória, me ajudariam bastante liberando memória para o Buffer Pool. Entretanto, limpando o cache CACHESTORE_SQLCP eu aumentaria meu consumo de CPU pois incrementaria o número de compilações de algumas querys. Todavia, como meu gargalo é memória, resolvi limpar esses caches assim mesmo.

Criei uma Stored Procedure que roda de 3 em 3 minutos para realizar a limpeza desses caches  e ainda armazeno um log da frequencia com que esses caches são limpos em uma tabela para histórico conforme os scripts abaixo:

CREATE TABLE [dbo].[Log_Limpeza_Cache](
[Id_Log] [int] IDENTITY(1,1) ,
[Dt_Limpeza] [datetime] ,
[Tipo_Cache] [varchar](30) ,
[Tamanho_MB] [numeric](15, 2) )

CREATE PROCEDURE [dbo].[stpLimpa_Memory_Cache]
AS
BEGIN
DECLARE @USERSTORE_TOKENPERM numeric(15,2), @CACHESTORE_SQLCP numeric(15,2)
SELECT @CACHESTORE_SQLCP = SUM(single_pages_kb+Multi_pages_kb)/1024.
FROM sys.dm_os_memory_clerks
WHERE type = ‘CACHESTORE_SQLCP’

SELECT @USERSTORE_TOKENPERM = SUM(single_pages_kb+Multi_pages_kb)/1024.
FROM sys.dm_os_memory_clerks
WHERE type = ‘USERSTORE_TOKENPERM’

IF @USERSTORE_TOKENPERM > 30
begin
insert into Log_Limpeza_Cache(Dt_Limpeza,Tipo_Cache,Tamanho_MB)
select getdate(), ‘USERSTORE_TOKENPERM’, @USERSTORE_TOKENPERM
DBCC FREESYSTEMCACHE(‘TokenAndPermUserStore’)
end
IF @CACHESTORE_SQLCP > 60
begin
insert into Log_Limpeza_Cache(Dt_Limpeza,Tipo_Cache,Tamanho_MB)
select getdate(), ‘CACHESTORE_SQLCP’, @CACHESTORE_SQLCP

DBCC FREESYSTEMCACHE(‘SQL Plans’)
end

END

Após a primeira limpeza do cache(momentos de tensão), fiquei acompanhando meu trace com as querys mais demoradas do banco de dados para ver se teria algum problema de performance. Entretanto, isso não aconteceu. Pelo contrário, a quantidade de querys lentas diminuíram.

Após alguns minutos, analisando os contadores de performance, o Page life Expectancy passou a ter uma média entre 100 e 120 segundos. Simplesmente triplicou.

Meu buffer pool, aumentou para mais de 2 GB, ou seja, a quantidade de dados que o SQL Server disponibiliza em memória para ser utilizada sem acessar o disco dobrou. Eu verifico essa informação com a query abaixo:

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 ––- exclude system databases
AND database_id <> 32767– –- exclude ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC;

Como já era esperado, também tive um aumento de processamento e de recompiles, mas o benefício da liberação de memória foi maior para o meu ambiente. O número de procedimentos demorando mais de 3 segundos diminuíu consideravelmente.

Além dessa alteração, fiz muitas outras alterações de performance para esse servidor aguentar o grande aumento de acesso que temos no Banco de Dados nesse Natal, mas acredito que essa alteração foi a que fez a maior diferença e me proporcionou um Natal bem tranquilo. Um excelente presente de Natal.

Claro que antes de realizar essas alterações no meu servidor de produção, testei em outros servidores menos críticos. Deixo essa experiência para quem possui um ambiente com esse mesmo tipo de problema. Espero que possa ter ajudado.

Abraços

Fabrício França Lima

Fala pessoal,

Algum tempo atrás, compartilhei uma experiência que tive no Blog do Fabrício Catae (Blog|Twitter), mas também resolvi deixar registrado por aqui.

Muitos de vocês já utilizaram a dmv sys.dm_db_index_usage_stats para verificar a utilização e atualização dos índices de uma tabela. Também sabemos que essa dmv tem seus dados reiniciados quando o serviço do SQL Server é reiniciado. Assim, como os servidores do meu ambiente de banco de dados possuem uma atualização mensal de segurança, os servidores são reiniciados mensalmente e os dados dessa dmv seriam perdidos. Ou seja, eu só teria essas valiosas informações sobre os índices durante o período de um mês.

Para resolver esse problema, criei uma tabela que armazena diariamente a utilização dos índices. Com isso, posso analisar durante um período muito grande, a utilização dos meus índices antes de excluí-los. Eu já possuo mais de 1 ano de baseline já que o espaço ocupado por essas informações é pequeno.

Para quem quiser possuir um histórico dessas informações, o script abaixo cria uma tabela de histórico e insere as informações de utilização dos índices nessa tabela.

CREATE TABLE [dbo].[Historico_Utilizacao_Indices](
[Id_Historico_Utilizacao_Indices] [int] IDENTITY(1,1) NOT NULL,
[Dt_Historico] [datetime] NULL,
[Nm_Servidor] [varchar](30) NULL,
[Nm_Database] [varchar](30) NULL,
[Nm_Tabela] [varchar](50) NULL,
[Nm_Indice] [varchar](50) NULL,
[User_Seeks] [int] NULL,
[User_Scans] [int] NULL,
[User_Lookups] [int] NULL,
[User_Updates] [int] NULL,
[Ultimo_Acesso] [datetime] NULL )

INSERT INTO Historico_Utilizacao_Indices(Dt_Historico, Nm_Servidor, Nm_Database, Nm_Tabela, Nm_Indice,  User_Seeks, User_Scans,User_Lookups, User_Updates, Ultimo_acesso)
SELECT getdate(), @@SERVERNAME, db_name(db_Id()), o.Name, i.name, s.user_seeks,s.user_scans,s.user_lookups, s.user_Updates,  isnull(s.last_user_seek,isnull(s.last_user_scan,s.last_User_Lookup)) Ultimo_acesso
FROM sys.dm_db_index_usage_stats s
join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
join sys.sysobjects o on i.object_id = o.id
WHERE s.database_id = db_id()
ORDER BY o.Name, i.name, s.index_id

Para armazenar as informações, criei um job que roda essa query de INSERT para cada database que eu defini guardar os históricos.

Assim, eu utilizo essa tabela de histórico para analisar uma possível exclusão dos índices que são pouco utilizados ou que não são utilizados. Agora, compartilhando a experiência que tive, eu possuo uma tabela com muitas consultas e alterações que armazena 50 milhões de registros. Nessa tabela, tenho um índice em um campo chamado Fl_Situacao que pode possuir os valores 0, 1, 2, 3 ou 4.

Sempre acompanhei esse índice e verifiquei que tinha algumas utilizações somente no inicio do mês. Um certo dia, resolvi excluir esse índice seguindo o raciocínio de que o índice era pouco seletivo, a tabela é muito grande e o índice era pouco utilizado, não valendo a pena o custo de manutenção do mesmo. Após excluir o índice, acompanhando meu trace com as querys que demoram mais de 3 segundos, verifiquei que nenhuma query apresentou problema de lentidão.

Show de bola, diminuí uma operação de manutenção de um índice em uma tabela muito utilizada.

Entretanto, no inicio do mês, existia uma query com uma condição “where Fl_Situacao = 2″ dentre outras restrições. Quando essa query rodou sem o índice que eu excluí, a mesma fez um clustered index scan nessa tabela, me causando um grande problema de lentidão no banco de dados. Isso aconteceu pois, dos 50 milhões de registros existentes na tabela, apenas 1.000 registros possuíam o campo Fl_Situacao = 2, o que tornava o índice existente nessa coluna extremamente eficiente para essa query.

Resultado, como não dava para alterar a consulta, tive que recriar o índice na mesma noite.

Mais uma vez eu digo, vivendo e aprendendo!!! Meu maior aprendizado está no meu dia a dia de trabalho.

Abraços,

Fabrício França Lima