quarta-feira, 22 de agosto de 2012

Como ver o espaço ocupado por cada tabela

Olá pessoal! Aí vai uma dica 2 em 1. A primeira dica é de uma stored procedure não documentada que existe desde o SQL Server 6.5 (veeeelho) e permite que se execute uma função, query ou stored procedure por cada tabela existente no banco de dados. A segunda dica é como usar essa stored procedure para ver o tamanho ocupado por cada tabela.

A stored procedure não documentada é a sp_MSforeachtable, que permite que você rapidamente execute uma função usando as tabelas existentes no banco como parâmetro. Caso você queira saber o count(*) de cada tabela no banco, é possível fazer isso:



exec sp_MSforeachtable ' select ''?'' as Tabela, count(*) as Registros from ? '

E para utilizar isso para ver o tamanho das tabelas, é só utilizar a procedure sp_spaceused da seguinte maneira:

exec sp_MSforeachtable 'EXEC sp_spaceused ''?'''

O resultado é apresentado no formato abaixo:


Temos o nome da tabela, número de registros, espaço reservado no datafile, espaço ocupado pelos dados, espaço ocupado pelos índices e o espaço não utilizado, ou seja, a diferença entre o que foi utilizado por dados e índices e o espaço reservado. Métricas úteis em várias situações. O problema é que as queries são executadas individualmente e não é possível fazer alguma ordenação como, por exemplo, listar as tabelas ordenadas decrescentemente por espaço em disco reservado, etc. Mas aproveito e já mostro um script para isso.

CREATE TABLE #tempTable
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

insert #tempTable exec sp_MSforeachtable 'EXEC sp_spaceused ''?'''

select [name], [rows],
cast(replace(reserved, ' KB','') as int) as reserved,
cast(replace(data, ' KB','') as int) as data,
cast(replace(index_size, ' KB','') as int) as index_size,
cast(replace(unused, ' KB','') as int) as unused
into #finalTable
from #tempTable

-- Modifique o order by abaixo para ter a ordenação desejada
select * from #finalTable order by reserved desc

drop table #finalTable
drop table #tempTable

Acho esse script bem legal para ver índices sobrecarregados e também ver as tabelas mais carregadas quando tenho o primeiro contato com um banco de dados. Com frequência encontro bancos de dados com tantos índices feitos para atender uma consulta em particular que os índices ocupam muito mais espaço que os dados.

Novamente, agradeço a leitura. O que mais quero desse blog é que ele seja útil, então enviem suas dúvidas, críticas e sugestões. Ah, e se puderem clicar em um dos anúncios na página, não fico nada chateado. Consegui ganhar 80 centavos de dólar no primeiro mês, que maravilha! Abraços a todos!


terça-feira, 21 de agosto de 2012

Muitas inserções ou consultas? Melhore a performance com SET NOCOUNT ON

Olá pessoal, aqui vai um post mais simples mas bastante útil. Caso você tenha uma stored procedure ou function que executa muitos INSERTs, UPDATEs, DELETEs e SELECTs no banco de dados e você precisa retornar estes dados para outra máquina e nem usa as messages do servidor nesta outra máquina para contar o número de registros afetados, coloque um SET NOCOUNT ON no início da procedure e veja se há melhora no desempenho.

Estou conectado em um servidor SQL Server 2008 via TCP/IP na Internet, link de 10Mbit e executando os scripts via Management Studio. Então caso você tenha uma situação similar e queira comprovar os benefícios do SET NOCOUNT ON de uma maneira bem simples, execute primeiro esta rotina:

create proc TESTE_NOCOUNT_OFF as
begin
      create table #temp (
            ID int not null primary key,
            CAMPOTESTE varchar(10)
      )

      set nocount off

      declare @contador int
      set @contador = 1
      while @contador < 100000
      begin
            insert into #temp (ID, CAMPOTESTE) values (@contador, 'TESTETESTE')
            set @contador = @contador + 1
      end

      drop table #temp
end
GO

exec TESTE_NOCOUNT_OFF
GO

drop proc TESTE_NOCOUNT_OFF
GO

Anote o tempo de execução com o NOCOUNT desligado (aqui rodou em 44 segundos) e depois execute esta rotina, com o NOCOUNT ligado:

create proc TESTE_NOCOUNT_ON as
begin
      create table #temp (
            ID int not null primary key,
            CAMPOTESTE varchar(10)
      )

      set nocount on

      declare @contador int
      set @contador = 1
      while @contador < 100000
      begin
            insert into #temp (ID, CAMPOTESTE) values (@contador, 'TESTETESTE')
            set @contador = @contador + 1
      end

      drop table #temp

      set nocount off
end
GO

exec TESTE_NOCOUNT_ON
GO

drop proc TESTE_NOCOUNT_ON
GO

Agora sem as mensagens rodou em 1 segundo. Legal, não? O motivo da demora com as mensagens não é o consumo de CPU ou I/O, mas sim rede. Como o SQL Server transmite por padrão as mensagens com os contadores, independente destas serem usadas pelo cliente, a performance da rotina sofre um grande impacto. Eliminando as mensagens, é eliminado este overhead e há uma melhoria sensível na execução.

Qualquer dúvida, avisem. Obrigado por visitar o blog!

segunda-feira, 20 de agosto de 2012

Quanta memória RAM cada banco de dados está utilizando?

Uma ótima dica do blog do John Samson, vale pra SQL Server 2005 e 2008, logo testarei no 2012. Em vários momentos precisamos saber o uso de memória por banco de dados, seja pra migração, seja para monitorar a performance de escrita em disco. Mas bem, vamos à query:


SELECT
(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
COUNT (*) AS 'Page Count',
cast((COUNT (*) * 8) as numeric(10,2))/1024 AS 'MBs used'
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified]
ORDER BY [database_id], [is_modified]


Então vamos entender os registros acima. O que acontece é que o SQL Server faz uso de memória para o Buffer Pool, que é onde ele mantém uma cópia em memória de dados acessados a partir de consultas feitas anteriormente para que ele não precise acessar o disco/storage caso a consulta seja feita novamente. A consulta acima mostra quão grande é o Buffer Pool para cada banco de dados.

Pages são as unidades de armazenamento de dados do SQL Server, e por padrão tem 8 KBytes. O resultado da coluna Page Reads, que pode ser Dirty ou Clean, diferencia o status dos pages. Existem os que contém dados que estão alinhados com o banco de dados (clean) e os que já foram modificados em memória mas ainda não em disco (dirty). Multiplicando o número de pages por 8Kb e dividindo por 1024 temos o número de MBs alocados por banco.

Hoje o post foi mais curto, mas espero que seja útil e obrigado por visitar o blog! :)

domingo, 19 de agosto de 2012

Como visualizar a última query executada por cada conexão aberta

Essa surgiu no meu trabalho. Precisávamos entender o funcionamento de um web service simples para o qual não tínhamos o código-fonte disponível, então em um determinado momento foi necessário ver qual consulta ele estava fazendo no banco a partir de uma determinada solicitação. Novamente, antes de sairmos ligando o Profiler, consideramos que a regra de negócio do web service poderia ser simples e talvez pegando a última query executada por aquela conexão teríamos a resposta. Pois bem, deu certo.

Aí vai a query que resolveu o nosso problema:

SELECT session_idTEXT
FROM 
sys.dm_exec_connectionsCROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handleAS ST

Aparece o session_id e o texto da query executada. Mas caso você queria mais informações para saber de qual máquina veio a query, o seu IP, qual software está fazendo a requisição e outras informações, sem problemas. É só fazem um INNER JOIN com a view de sistema sys.sysprocesses , que é a mesma utilizada pelas stored procedures sp_who e sp_who2, empregadas para visualizar as sessões e processos em aberto. Então aí vai uma versão mais completinha:

SELECT client_net_address as [IP do cliente],
p.hostname as [Nome da máquina do cliente],
[text] as [Texto da consulta],
DB_NAME(p.dbid) as [Nome do BD no qual foi executada a query],
p.[program_name] as [Programa solicitante]
FROM sys.dm_exec_connections c
INNER JOIN sys.sysprocesses p on c.session_id = p.spid
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST

Espero que a dica seja útil! Caso vocês tenham quaisquer dúvidas sobre como resolver um problema no SQL Server, coloquem um comentário que tento responder - dentro da minha capacidade, é claro - o mais rápido possível.

sábado, 18 de agosto de 2012

Forma rápida de verificar queries que estão pesando muito no servidor - Parte II

Olá pessoal, demorei a retomar os posts do blog pela correria do mestrado, mas vamos voltar ao assunto de verificar rapidamente quais são as queries que estão sobrecarregando o seu servidor. Como foi explicado no último post, muitas vezes é melhor utilizar as DMVs e DMFs (dynamic management views / dynamic management functions) do que configurar o Profiler, gerar um workload e fazer a análise subsequente em função da velocidade de resposta. Novamente, estou falando de SQL Server 2005 e 2008, não testei os pontos abaixo em 2012.

O cerne da query que coloquei no post anterior é a DMV sys.dm_exec_query_stats, que contém uma série de dados relevantes para a avaliação das consultas no cache, porém ela falha em alguns pontos. Vou apresentar alguns deles para apoiar a sua decisão, para ver se o melhor é usar o Profiler, fazer uso da DMV ou outra ferramenta:

Queries com a opção RECOMPILE não são capturadas

Não há muito o que dizer além do que está dito no título. Se você faz uso de queries com RECOMPILE, a DMV não vai armazenar informações pertinentes à sua execução e podem ser justamente as consultas causando a sobrecarga no seu banco de dados.

Queries estruturalmente similares não podem ser agrupadas

Isso é um problema maior no SQL Server 2005 do que no 2008, mas para explicar o problema vou lançar um exemplo. Digamos que executamos duas queries simples, como as que coloco abaixo:

SELECT Nome FROM Tabela WHERE id = 1
SELECT Nome FROM Tabela WHERE id = 2

Tanto no SQL Server 2005 quanto no SQL Server 2008, ele vai armazenar a instrução de maneira isolada no cache. Então no campo text retornado pela DMF sys.dm_exec_sql_text , não poderemos usar um GROUP BY, COMPUTE ou qualquer outra função de agrupamento pois em um tem o trecho "id = 1" e no outro "id = 2". No SQL Server 2008, tem algo bem legal que permite que consigamos agrupar as duas queries, que são os novos campos query_hash e query_plan_hash retornados pela DMV sys.dm_exec_query_stats. Estes dois campos geram um hash da consulta desconsiderando os parâmetros, permitindo que você agrupe as consultas estruturalmente iguais mas com parâmetros diferentes.

Algumas informações de execução da procedure não são consideradas

Alguns recursos de temporização como o WAITFOR não são considerados nas DMV e aparecem no Profiler.

Existem alguns outros pontos, mas acredito que os três pontos acima são os de maior criticidade. E até pegando o ponto do agrupamento, dá para alterar a query anterior, desta vez somente para SQL Server 2008, para fazer uso do query_hash e ter estatísticas acerca de queries estruturalmente idênticas. Porém, dica de outro, a Microsoft já lançou uma ferramenta muito legal que já faz isso pra você:

Query Hash Statistics

E cumprindo o que prometi no post anterior, aí vai uma lista de coisas que dá para se analisar com a sys.dm_exec_query_stats no SQL Server 2008 R2. Vou colar a tabela do MSDN (ver artigo aqui) e adicionar alguns comentários em vermelho para deixar alguns pontos mais claros.

Nome da colunaTipo de dadosDescrição
sql_handle varbinary(64) É um token que se refere ao lote ou procedimento armazenado de que a consulta faz parte.
Pode ser usado sql_handle, junto com statement_start_offset e statement_end_offset, para recuperar o texto SQL da consulta, chamando a função de gerenciamento dinâmico sys.dm_exec_sql_text.
statement_start_offset int Indica, em bytes, começando com 0, a posição inicial da consulta que a linha descreve dentro do texto de seu lote ou objeto persistente.
statement_end_offset int Indica, em bytes, começando com 0, a posição final da consulta que a linha descreve dentro do texto de seu lote ou objeto persistente. Um valor de -1 indica o fim do lote.
plan_generation_num bigint Um número de sequência que pode ser usado para distinguir entre instâncias de planos após uma recompilação.
plan_handle varbinary(64) Um token que se refere ao plano compilado de que a consulta faz parte. Este valor pode ser transmitido à função de gerenciamento dinâmico sys.dm_exec_query_plan para a obtenção do plano de consulta.
creation_time datetime Hora em que o plano foi compilado.
last_execution_time datetime Hora do início da execução do plano.
execution_count bigint Número de vezes que o plano foi executado desde sua última compilação.
total_worker_time bigint Tempo total da CPU, relatado em microssegundos (mas preciso somente em milissegundos), que foi consumido pelas execuções desse plano desde que foi compilado.
last_worker_time bigint Tempo de CPU, relatado em microssegundos (mas preciso somente em milissegundos), consumido na última vez em que o plano foi executado.
min_worker_time bigint Tempo de CPU mínimo, relatado em microssegundos (mas preciso somente em milissegundos), que esse plano já consumiu durante uma única execução.
max_worker_time bigint Tempo de CPU máximo, relatado em microssegundos (mas preciso somente em milissegundos), que esse plano já consumiu durante uma única execução.
total_physical_reads bigint Número total de leituras físicas efetuadas por execuções deste plano desde sua compilação.
Novamente, leitura física é pertinente ao acesso ao disco para recuperação de dados da tabela. Logical reads são leituras que acessam o cache de dados e também o disco. O uso de cache é basicamente o resultado da substração Logical Reads - Physical Reads.
last_physical_reads bigint Número de leituras físicas efetuadas na última vez em que o plano foi executado.
min_physical_reads bigint Número mínimo de leituras físicas que este plano efetuou durante uma única execução.
max_physical_reads bigint Número máximo de leituras físicas que este plano efetuou durante uma única execução.
total_logical_writes bigint Número total de gravações lógicas efetuadas por execuções deste plano desde sua compilação.
last_logical_writes bigint Número de gravações lógicas efetuadas na última vez em que o plano foi executado.
min_logical_writes bigint Número mínimo de gravações lógicas que este plano efetuou durante uma única execução.
max_logical_writes bigint Número máximo de gravações lógicas que este plano efetuou durante uma única execução.
total_logical_reads bigint Número total de leituras lógicas efetuadas por execuções deste plano desde sua compilação.
last_logical_reads bigint Número de leituras lógicas efetuadas na última vez em que o plano foi executado.
min_logical_reads bigint Número mínimo de leituras lógicas que este plano efetuou durante uma única execução.
max_logical_reads bigint Número máximo de leituras lógicas que este plano efetuou durante uma única execução.
total_clr_time bigint O tempo, relatado em microssegundos (mas preciso somente em milissegundos), consumido dentro de objetos CLR (Common Language Runtime) Microsoft .NET Framework por execuções desse plano desde sua compilação. Os objetos CLR podem ser procedimentos armazenados, funções, disparadores, tipos e agregados.
Para quem usa objetos CLR, é uma ótima ficar de olho nestes indicadores pois pode existir uma regra de negócio no seu objeto que causa uma flutuação grande no tempo de execução. 
last_clr_time bigint Tempo, relatado em microssegundos (mas preciso somente em milissegundos), consumido pela execução dentro de objetos CLR .NET Framework durante a última execução desse plano. Os objetos CLR podem ser procedimentos armazenados, funções, disparadores, tipos e agregados.
min_clr_time bigint Tempo mínimo, relatado em microssegundos (mas preciso somente em milissegundos), que esse plano já consumiu dentro de objetos CLR .NET Framework durante uma única execução. Os objetos CLR podem ser procedimentos armazenados, funções, disparadores, tipos e agregados.
max_clr_time bigint Tempo máximo, relatado em microssegundos (mas preciso somente em milissegundos), que esse plano já consumiu dentro de CLR .NET Framework durante uma única execução. Os objetos CLR podem ser procedimentos armazenados, funções, disparadores, tipos e agregados.
total_elapsed_time bigint Tempo total decorrido, relatado em microssegundos (mas preciso somente em milissegundos), para execuções concluídas desse plano.
Conforme disse antes, instruções de espera como WAITFOR não são consideradas nesse contadores "_elapsed_time".
last_elapsed_time bigint Tempo decorrido, relatado em microssegundos (mas preciso somente em milissegundos), para a execução completa mais recente desse plano.
min_elapsed_time bigint Tempo decorrido mínimo, relatado em microssegundos (mas preciso somente em milissegundos), para qualquer execução concluída desse plano.
max_elapsed_time bigint Tempo decorrido máximo, relatado em microssegundos (mas preciso somente em milissegundos), para qualquer execução concluída desse plano.
query_hash binary(8) Valor de hash binário calculado na consulta e usado para identificar consultas com lógica semelhante. Você pode usar o hash de consulta para determinar o recurso agregado usado para consultas que são diferentes apenas nos valores literais. Para obter mais informações, consulte Localizando e ajustando consultas semelhantes usando consulta e hashes de plano de consulta.
query_plan_hash binary(8) Valor de hash binário calculado no plano de execução de consulta e usado para identificar planos de execução de consulta semelhantes. Você pode usar o hash de plano de consulta para localizar o custo cumulativo de consultas com planos de execução semelhantes. Para obter mais informações, consulte Localizando e ajustando consultas semelhantes usando consulta e hashes de plano de consulta.
total_rows bigint O número total de linhas retornadas pela consulta. Não pode ser nulo.
last_rows bigint O número total de linhas retornadas pela última execução da consulta. Não pode ser nulo.
min_rows bigint O número mínimo de linhas retornadas pela consulta no número de vezes em que o plano foi executado desde sua última compilação. Não pode ser nulo.
max_rows bigint O número máximo de linhas retornadas pela consulta no número de vezes em que o plano foi executado desde sua última compilação. Não pode ser nulo.

Então é isso pessoal. Qualquer dúvida ou caso queiram "encomendar" uma query, é só lançar nos comentários que responderei o mais rápido possível. Obrigado por visitar o blog e espero publicar novos posts logo mais. Abraços!

quinta-feira, 26 de julho de 2012

Forma rápida de verificar queries que estão pesando muito no servidor - Parte I


Oi pessoal! Aí vai um dica muito boa tirada do SQLServerPedia e que já usei algumas vezes para detectar problemas de performance no servidor. Quando tem uma série de consultas onerando o servidor, uma das primeiras coisas que o DBA pensa é em ver os processos em execução e, caso não encontre um padrão, usar o Profiler. O Profiler, para quem não conhece, é uma ferramenta que consegue listar atividades ocorridas no SQL Server, então é possível utilizá-la para verificar o consumo de CPU e I/O de consultas de maneira comparativa.

Existem situações em que usar o Profiler não é uma escolha: a execução do Profiler onera o servidor e pode piorar ainda mais a situação de um servidor sobrecarregado ou, ainda pior, o usuário pode ter só um Management Studio Express e não ter o Profiler instalado para fazer essa verificação. Nestes casos é possível, para SQL Server 2005 e 2008, apelar para as dynamic management views, as famosas DMVs. As DMVs fornecem informações extremamente valiosas para o gerenciamento de um servidor, e entre elas o procedure cache, que é um cache que armazena planos de execução corriqueiramente utilizados. Então, vamos à consulta:


select total_worker_time/execution_count as MediaCPU
, total_worker_time AS TotalCPU
, total_elapsed_time/execution_count as MediaDuration
, total_elapsed_time AS TotalDuration
, total_logical_reads/execution_count as MediaLogicalReads
, total_logical_reads AS TotalLogicalReads
, total_physical_reads/execution_count as MediaPhysicalReads
, total_physical_reads AS TotalPhysicalReads
, execution_count 
, substring(st.text, (qs.statement_start_offset/2)+1
, ((case qs.statement_end_offset  when -1 then datalength(st.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 1 desc

Se você quer entender os resultados da query acima, basicamente são apresentados nos oito primeiros campos contadores de média e total para uso de CPU, duração da execução, logical reads (leituras de cache) e physical reads, que é leitura direta de valores no disco. Como os indicadores do SQL são inexatos é melhor tomá-los como medidas relativas. O "order by 1 desc" trará as consultas que tiveram mais peso em processamento, alternando para "order by 5 desc" se teria os mais pesados em logical reads e por aí vai. O execution_count apresenta o número de execuções, txt o corpo da consulta e query_plan acaba por apresentar o plano de execução.

Nas primeiras consultas, utilize um "top" para restringir o número de registros retornados, principalmente se o cache comportar muitos registros. Com essa query já se resolvem muitos problemas. Amanhã vou dissecar a sys.dm_exec_query_stats para que, caso vocês queiram, vocês possam aplicar em outros cenários de monitoramento.

Novamente fico à disposição para quaisquer dúvidas. Se eu não souber, corro atrás. Abraços!

P.S.: Um amigo, ex-colega e leitor do blog, Jackson Barbian, sinalizou que não conseguiu executar o script no SQL Server 2005 pois estava apresentando erro de sintaxe. Descobrimos depois que o problema era o modo de compatibilidade do banco de dados com o SQL Server 2000, o que faz com que o CROSS APPLY não funcione. Nesses casos, acesse o banco de dados MASTER ou outro banco de dados e execute a query, pois os dados dela não são sensíveis ao banco de dados selecionado. Valeu Jackson!

quarta-feira, 25 de julho de 2012

Verificando em quais stored procedures e views uma tabela está sendo utilizada

Oi pessoal! Um problema muito frequente que encontro é mapear quais stored procedures e views utilizam uma determinada tabela. Claro, este método não vai capturar usos indiretos, como uma stored procedure que consulta uma view que consulta a tabela desejada, mas sim se o nome da tabela consta no código da stored procedure. Portanto CLR procedures também estão de fora. Mas vamos à query, funciona em 2005 e 2008, ainda vou testar no 2012:

SELECT obj.Name as [Nome da procedure],
sc.TEXT as [Conteudo da procedure]
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%TABELA%' AND TYPE = 'P'

Onde está o texto TABELA, você deve colocar o nome da tabela em questão. Se as procedures utilizam a convenção de nomenclatura de schema + tabela (Exemplo: dbo.tabela) você vai achar os registros facilmente. Se o nome da sua tabela estiver contida no nome de outra tabela ou view e vocês não usar essa convenção de nomenclatura, bem, podem pintar alguns resultados errados, mas ainda assim afunila o volume de procedures a se inspecionar. 

Ah, e para pegar views, é barbadinha. Troque a linha:

AND TYPE = 'P'

Por:

AND TYPE = 'V'

E você listará as views onde o nome da tabela é encontrado. Pra manutenção em sistemas, é uma belezinha. Abração!