MCDBA Brasil


  • Home
  • Sobre
  • Contato

Livros





Links Rápidos

SQL Server Builds (All Versions/Editions)


Download SQL Server 2017 (trial)


SQL Server 2017 Feature Pack


SQL Server 2016 Feature Pack


Cumulative Update SQL Server 2017 builds


Cumulative Update SQL Server 2016 builds


Cumulative Update SQL Server 2014 builds


Cumulative Update SQL Server 2012 builds


SQL Server 2005/2008 Samples Databases


Documentando o Servidor SQL Server


Analisando a Performance do Servidor-CheckList


Virtual PASS PT


Faça parte do maior virtual chapter do PASS com conteúdos técnicos em Português sobre SQL Server.

Todos os meses um evento Online para você! Acompanhe aqui os WebCasts já agendados

Sindicalize seu blog ou site ao VirtualPASSPT

SQL Server Blogs

SQL Server Query Processing Team


SQL Programmability & API Development Team


SQL Server Manageability Team


Latin America Support Team


Database + Disk + Performance


Microsoft SQL Server Support


SQL CLR Team


SQL Query Optimization Team


SQL 2005 Code Samples


SQL Server Express Team


SQL SMO Samples


SQL Storage Engine Team


SQL CAT Team


SQL Protocols Team


PSS SQL Server Engineers


Slava Oks on SQLOS


Ken Henderson’s blog


LUTI@Microsoft Blog


kimberly L. Trip’s blog


Fernando Garcia Blog

Artigos

Analisando a Performance do Servidor – CheckList

por Nilton Pinheiro abril 5, 2005 Nenhum comentário

Na verdade, a causa da lentidão em um servidor pode ser várias: uma query que não faz uso adequado dos índices, tabelas sem índices, proplemas de modelagem, falta de memória, discos ruins e assim vai…

Independente da causa da lentidão, alguns passos básicos podem ser seguidos para tentar identificar o problema. Neste artigo estarei descrevendo alguns desses passos e dizendo como interpretá-los durante a verificação de problemas de performance em seu servidor. Porém, quero deixar claro que os passos decritos aqui não é um padrão e que a metodologia de análise pode variar de DBA para DBA.


O PASSO-A-PASSO


Passo 1: Verificar as conexões existentes no servidor e se existe problemas de blocks.


O primeiro passo para identificar problemas de performance é saber o que está rolando no servidor. Quais são as conexões existentes? O que elas estão executando? Existem conexões bloqueadas? Quem é o causador do bloqueio? Quem está bloqueado? etc…


A procedure de sistema sp_who é uma procedure muito utilizada para responder estas questões, mas a principal pergunta a ser respondida aqui é: Existem conexões bloqueadas?


Para responder a esta pergunta execute a procedure sp_who e verifique se existe alguma conexão onde a coluna Blk seja diferente de 0 (blk<>0). Se existir, já sabemos que estamos tendo problemas de block de conexões.


Passo 1.2: Havendo conexões bloqueadas, identificar o causador do bloqueio e a instrução SQL envolvida.
 
Uma vez detectado problemas de bloqueio, o próximo passo é identificar a conexão que está causando o bloqueio. Os bloqueios ocorrem porque normalmente duas ou mais conexões estão tentando utilizar um mesmo recurso. Imagine que temos duas conexões, uma com o SPID 64 e outra com o SPID 65. O SPID 65 efetua um lock exclusivo em uma tabela para efetuar uma atualização de dados e neste mesmo momento a conexão com SPID 64 tenta ler estas informações.


Neste caso, como a conexão SPID 65 abriu uma transação e efetuou um lock exclusivo, a conexão com SPID 64 ficará bloqueada pelo SPID 65 até que o mesmo conclua sua atualização. Uma das maiores causas de problemas de bloqueio são tabelas sem índices ou queries que não utilizam os índices de forma correta. Na maioria das vezes, a simples criação de uma índice clustered na tabela envolvida pode solucionar este tipo de problema.


Em grandes ambientes (ambientes com muitas conexões), a dificuldade aqui pode estar em identificar a conexão causadora do bloqueio. Isto porque muitas das vezes pode haver um certo encadeamento de bloqueio. Para facilitar este trabalho, você poderá estar utilizando a procedure sp_usrheadblocker.


Ao executá-la você obterá as seguintes informações:
—SPID => Este é o identificador da conexão.
—Blocked => informa o SPID que está bloqueando a conexão. Os SPIDs que possuirem Blocked=0 é o SPID que está causando os problemas de bloqueio.
—ECID => Informa se a conexão esta fazendo uso de pararelismo.
—WaitTimeMS => informa o tempo (em milessegundo) que a conexão está aguardando pelo recurso. Ou seja, a quanto tempo ela está bloqueada.
—RunAs => informa a quanto tempo (em minutos) a conexão está no servidor.
—Status => O status da conexão.
—CPU => informa o consumo de CPU pela conexão.
—Physical_IO => informa o consumo de I/O pela conexão.
—HostName => O nome da máquina que estabeleceu a conexão.
—LoginName => o nome do login que estabeleceu a conexão.
—DBName => o nome da base de dados.
—Last_Batch => a hora em que a conexão foi estabelecida ou executou a última instrução.
—open_tran => informa se existe alguma transação aberta pela conexão. O número apresentado aqui é o número de transações abertas pela conexão.
—MemUsage => quantidade de memória sendo utilizada pela conexão.
—TextBuffer => informa a instrução T-SQL sendo executada pela conexão.
 
  
Na maioria das vezes, a primeira linha sempre será a conexão causadora do bloqueio e as demais, as que estão aguardando pela liberação do recurso. Nos casos onde você tiver block em vários recursos, as conexões causadoras dos boqueios sempre terão a coluna Blocked=0 e serão as primeiras linhas.


No exemplo abaixo, temos que as conexões 64 e 68 estão sendo bloqueadas pelas conexões 65 e 66 respectivamente. Na coluna TextBuffer, podemos ver que as conexões 65 e 64 estão utilizando o mesmo recurso (a tabela Products). O mesmo acontece com as conexões 66 e 68.


SPID   Blocked WaitTimeMS  TextBuffer
—— ——- ———– ———————————————————————–
65     0       0           begin tran Update products SET UnitsInStock= 38 where ProductID=1
66     0       0           begin tran Update Orders SET ShipCountry = ‘Brasil’ where OrderID=10248
64     65      1408218     select * from Products
68     66      134890      select * from orders


Obs: Algumas colunas foram excluídas para facilitar a visualização. 



Neste caso, as conexões 64 e 68 não serão liberadas enquanto as conexões 65 e 66 não concluírem o processo de atualização (efetuarem um commit tran) ou forem derrubadas com a utilização do comando Kill. Um ponto importante a ser observado é que nem sempre o comando Kill finalizará o processo de forma imediata. Dependendo da instrução sendo executada e dos status das conexões, a conexão pode entrar em Rollback e demorar um certo tempo para ser finalizada.


Caso a coluna TextBuffer não esteja lhe fornecendo informações suficientes sobre a instrução T-SQL sendo executada pela conexão, você pode executar também o comando DBCC Inputbuffer(<spid>). Porém, uma limitação do DBCC Inputbuffer é que nos casos em que a conexão estiver executando uma stored procedure ou view, ele lhe mostrará apenas o nome da procedure ou view. O ideal seria poder identificar o que realmente está sendo executado pela conexão naquele exato momento e para vencer esta limitação do comando DBCC Inputbuffer, você pode utilizar a procedure sp_usrinputbuffer<spid>.


Exemplo:


— Executa o Comando DBCC InputBuffer no QA
dbcc inputbuffer (77)


EventType      Parameters EventInfo                         
————– ———- ———————————-
Language Event 0          exec SalesByCategory ‘Beverages’


  
— Para usar esta procedure o conexão deve estar com o status “Runnable”
Exec sp_usrinputbuffer 77


******** STATEMENT SENDO EXECUTADO NO MOMENTO ************
SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName



Passo 2: Avaliar o estado do servidor.


Se o problema de lentidão ou performance não estiver associado com o bloqueio das conexões, o próximo passo a seguir é fazer uma análise em alguns contadores do Perfmon (Performance Monitor) para tentar identificar possíveis gargalos no nível de hardware.


Alguns dos principais contadores a serem monitorados são listados abaixo:


A) Contadores de Memória
 
Memory: Available Bytes ==>  Indica a quantidade de memória disponível no servidor.


Mamory: Pages/Sec   ==> Indica se está havendo paginação. Em perfeitas condições deve-se ver pouca atividade neste contador. Se o SQL esta rodando em um servidor dedicado, toda a sua memória alocada (aquela configurada nas propriedades do servidor dentro do Enterprise Manager) será mapeada para a memória física e muito pouco swapping (paginação) deverá ocorrer. Se este contador estiver muito alto ou crescente, verifique a memória alocada para o SQL Server, aumentar este valor ou colocar o SQL Server para configurar sua memória dinamicamene pode ajudar a reduzir a paginação.


B) Contadores do SQL Server
 
SQL Server: Buffer Manager: Buffer Cache Hit Ratio ==> Indica o percentual de requisições a dados que são obtidas no próprio cache do sql e portanto, sem precisar acessar o disco. O ideal é estar maior que 90%.


SQL Server:Access Methods:Page Splits/sec ==> Mostra quantos page splits estão ocorrendo no servidor. Este valor deve ser o mais baixo possível. Se o valor estiver alto, configurar os índices com um fillfactor apropriado pode ajudar a reduzir este valor.


SQL Server: Memory Manager: Total Server Memory (KB) ==> Quando maior que a quantidade de memória física pode indicar falta de memória.


 C) Contadores de CPU
 
Processor:% Processor Time:_Total ==> Indica o consumo de CPU no servidor (ideal abaixo de 80%).


Process: % Processor time: sqlservr ==> Indica o consumo do processador pelo processo do SQL Server.


System: Processor Queue Length ==> Indica o número de threads aguardando para execução no processador e nunca deve exceder 1 ou 2 (por processador) por um período superior a 10 minutos.


D) Contadores de Disco


PhysicalDisk: % idle Time ==> Indica o percentual de tempo que o disco esta ocioso. Supostamente este contador apresenta uma estimativa mais precisa sobre a utilização dos discos. Subtraindo o valor encontrado de 100, temos uma ideia do quanto o disco esta trabalhando.


PhysicalDisk: % Disk Time ==> Mostra o quanto o disco esta ocupado. Se valor > 60% durante 10 minutos, verificar os contadores PhysicalDisk: % Disk Read Time e PhysicalDisk: % Disk Write Time para identificar se é leitura ou escrita quem está causando maior utilização.


PhysicalDisk: Avg. Disk Queue Length ==> Mostra o número médio de requisições de I/O aguardando para acesso ao disco. Este contador nunca deve exceder 2 (por disco) por um extenso período de tempo.


PhysicalDisk: Avg. Disk Sec/Transfer ==> Taxa de transferência em bytes de ou para um disco durante operações de leitura ou escrita.


D) Contadores de Banco de Dados


SQL Server: Access Methods: Full Scans/sec ==> Mostra a quantidade de Table Scan sendo executada no SQL Server. Este valor deve ser o mais baixo possível e se for constantemente alto, use o Profile para identificar quais queries estão causando os scans.


SQL Server: Locks: Average Wait Time (ms) ==> Este contador mostra quanto tempo em milessegundos um processo está aguardando para obter lock no SQL Server. O valor ideal para este contador é zero. Se este valor estiver muito alto, utilize o Profile para identificar quais processos estão mantendo locks por um período longo de tempo.


Como descrito no início deste artigo, o problema de permance pode estar associado a várias causas, este checklist segue alguns passos básicos para ajudar na identificação do problema. Também é importante observar que este checklist não é um padrão e que por isso, os métodos utilizados para analizar a performance de um servidor pode variar de DBA para DBA. Na verdade, no dia-a-dia de trabalho cada DBA acaba por criar a sua própria metodologia de análise.


Caso prefira, você pode estar baixando aqui um .xls (documento Excel) com os passos descritos neste artigo.


Um abraço a todos
Nilton Pinheiro

Avaliação:
Compartilhe:
  • Anterior Introdução ao Backup do SQL Server 200017 anos atrás
  • Próximo Resolvendo Problemas Inesperados com o Log de Transações17 anos atrás

Deixe uma resposta Cancelar resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

MVP Reconnect Award

Categorias

  • Artigos (359)
  • Dica da Semana (95)
  • Documentação (54)
  • Downloads (113)
  • MSDE 2000 (3)
  • Sem categoria (1)
  • Tutoriais (9)

Posts recentes

  • #FechouBrasil #PartiuPortugal
  • Brigando com o erro “The cached MSI file is missing”
  • MCDBABRASIL está de volta
  • Documentando o Servidor SQL Server
  • Brigando com os Erros 17182, 17826 e 17120

SQL Server AlwaysOn Video Series

Video1: Introdução ao SQLServer2012 AlwaysOn


Video2: Introdução ao SQLServer2012 AlwaysOn Availability Group


Video3: Introdução ao SQLServer2012 AlwaysOn AVG-Demo


Video4: Introdução ao SQLServer2012 AlwaysOn Listener


Video5: Introdução ao SQLServer2012 AlwaysOn Readable Secondaries


Video6: Introdução ao SQLServer2012 AlwaysOn Readable Secondaries-Demo


Video7: Introdução ao SQLServer2012 AlwaysOn Failover Clustering


Serie SQL Server Failover Clustering End-to-End

Parte 1: Configuração da Rede e Ambiente


Parte 2: Configurando o Windows 2008 R2 Domain Controler e DNS


Parte 3: Preparando os nós para o Failover Cluster


Parte 4: Configurando um Failover Cluster de 2 nós


Parte 5: Configurando as LUNs no iSCSI Software Target (Parte 1)


Parte 6: Configurando as LUNs no iSCSI Software Target (Parte 2)


Parte 7: Apresentando as LUNs para os nós do Failover Cluster


Parte 8: Configurando os discos no Failover Cluster


Parte 9: Instalando a primeira instância virtual do SQL Server 2008


Parte 10: Instalando a segunda instância virtual do SQL Server 2008


Parte 11: Instalando e Configurando o MSDTC no Failover Cluster


Parte 12: Configurando Mount Points no Cluster e SQL Server 2008


Vídeo Extra: Removendo uma Instância do SQL Server 2008 R2 em Cluster


Alta Disponibilidade no SQL Server 2008 R2: Failover Clustering Overview


Alta Disponibilidade no SQL Server 2008 R2: Failover Clustering na Prática

Menu

  • Home
  • Sobre
  • Contato

Mais

  • RSS Feeds
2022 MCDBA Brasil.