none
Erros de disco. I/O requests, Autogrow, Deadlock RRS feed

  • Pergunta

  • Bom dia!

    Há alguns meses estou recebendo erros no log do SQL Server referentes ao acesso à disco.

    Os erros mais comuns são:

    1 SQL Server has encountered 1767 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [ProducaoLog_Data.mdf] in database [ProducaoLog] (13);
    2 Autogrow of file 'ProducaoLog_Log' in database 'ProducaoLog' was cancelled by user or timed out after 59405 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file SIZE;
    3 Autogrow of file 'templog' in database 'tempdb' was cancelled by user or timed out after 34960 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file SIZE;
    4 Could not adjust the space allocation for file 'ProducaoLog_log';
    5 Deadlock encountered .... Printing deadlock information;

    Onde : 
    1 A mensagem 1 é muito frequente, ocorre durante dia e noite;
    3 e 4 Os bancos ProducaoLog e tempdb estão com autogrowt ON e com 5% de crescimento para logfile e 10% para datafile. O banco ProducaoLog tem 250Gb e o tempdb tem 9 datafiles com 900 Mb cada um e um logfile com 3.5 Gb, os valores são aproximados;

    5 Sobre a mensagem 5, os deadlocks, costumam ocorrer em situações quando há criação de FK com as tabelas mais utilizadas do sistema e são os piores pois matam vários outros processos. Ocorrem também em UPDATEs nas tabelas principais do sistema e costumam ocorrer logo depois de ocorrências sequenciais da mensagem 1, I/O requests, e com menos frequência ocorrem logo antes da mesma mensagem.

    Essa instância é de um sistema de comércio eletrônico com várias lojas.

    Sobre o servidor onde está instalada a instância temos um cluster com dois nós para o SQL Server, um storage compartilhado com outras instâncias em uma rede gigabit.

    Ao executar SELECT @@Version tenho a seguinte versão do SQL:

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
    Jun 17 2011 00:54:03 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    Infelizmente não tenho acesso aos detalhes sobre modelos do servidor e storage.

    Eu preciso saber se é realmente um erro de infra, seja relacionado à disco ou à rede, ou algum erro de SQL. De qualquer forma preciso de uma solução. Por favor, se tiverem alguma consideração escrevam.

    Agradeço.



    [@...]




    • Editado Alex Elias sexta-feira, 23 de maio de 2014 17:07
    sexta-feira, 23 de maio de 2014 12:59

Todas as Respostas

  • Alex,

    Você sabe me dizer como esta configurado a taxa de crescimento do seu arquivo de dados e log?

    Analisando a mensagem de erro, esta informando que o valor de crescimento esta pequeno ou precisa ser redefinido.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    sexta-feira, 23 de maio de 2014 17:51
    Moderador
  • A configuração de crescimento dos arquivos de log está assim:

    Na base ProducaoLog

    DataFile crescimento em 5% ilimitado;
    LogFile crescimento em 10% ilimitado.

    Na base tempdb

    DataFile crescimento em 10% ilimitado
    LogFile crescimento em 10% ilimitado


    Alex Elias

    sexta-feira, 23 de maio de 2014 19:53
  • Alex,

    A unidade em que estes bancos de dados estão localizados possui espaço livre?

    Acredito que a melhor solução é fazer uma análise do crescimento do banco de dados de dados para tentarmos identificar qual seria a melhor configuração de crescimento.

    O Management Studio possui uma relação de relatórios de análise tanto no nível de servidor, como também, no nível de Banco de Dados, uma possibilidade seria você analisar estes relatórios para tentar identificar mais informações sobre o seu banco.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quinta-feira, 29 de maio de 2014 13:33
    Moderador
  • Olá Alex.

    Apenas complementando, quando o SQL Server precisa crescer o arquivo acontece uma sequência de operações de IO pesadas, que precisam preencher todo o novo espaço do arquivo com zeros. 

    Uma forma bastante eficiente para evitar isso quando o crescimento é no arquivo de dados seria dando o direito de "Perform Volume Maintenance Tasks" para o usuário do serviço do SQL Server. Desta forma o SQL Server apenas aloca o novo espaço sem perder tempo escrevendo zero em todo o novo espaço. Já no arquivo do Transaction Log isso não é possível, por isso é importante dimensioná-lo corretamente.

    É importante saber também, através dos Contadores do Perfmon, se você está exigindo demais dos discos, fazendo com que a latência fique alta. Verifique se a quantidade de IOPS (Transfers/sec) é alta em relação a quantidade de IOPS máxima que seus discos oferecem, se você está com arquivos de TLog junto com arquivos de dados, se você faz uso intenso da TempDB (quantas transações são na TempDB do total), etc. Algumas vezes, mesmo separando dados de TLog, quando são muitas transações/sec, pode ser necessário separar o TLog da base mais utilizada em um volume específico pra isso, outro para o TLog da TempDB, etc.

    É muito importante saber como funciona o TLog, pois seu tamanho, sua organização e sua fragmentação podem impactar severamente na performance.

    Este curto vídeo em português explica como funciona e quais as melhores práticas para gerenciá-lo:

    mms://passmedia.sqlpass.org/24hours/2013_Portuguese/19.wmv

    Veja que o protocolo é MMS, procure abrí-lo no Internet Explorer em um PC com Windows Media Player.

    Abraços!


    Luiz Mercante | MCITP SQL 2008 | MCTS SQL 2008 | MTA Database Fundamentals | MCTS Windows Apps | MCTS Windows Network | MCP 2003 | sqldicas@outlook.com | http://sqldicas.com.br --> Se a resposta foi útil de alguma forma, classifique como resposta ou vote como útil.

    • Sugerido como Resposta Renato Siqueira sexta-feira, 1 de agosto de 2014 18:43
    domingo, 15 de junho de 2014 14:53
    Moderador
  • Obrigado a todos.

    O problema era infra. A storage não estava suportando a demanda das várias instâncias que continha. Foi trocada e os problemas resolvidos.


    Alex Elias

    • Sugerido como Resposta Durval Ramos quarta-feira, 23 de julho de 2014 17:33
    sexta-feira, 18 de julho de 2014 02:05
  • Apesar de ter resolvido com a troca da Storage, a recomendação do Luiz Mercante é o metodo mais indicado para resolver os problemas relacionados com criação ou crescimento de arquivos.

    Instant File Initialization

    "Uma forma bastante eficiente para evitar isso quando o crescimento é no arquivo de dados seria dando o direito de "Perform Volume Maintenance Tasks" para o usuário do serviço do SQL Server."


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.


    sábado, 19 de julho de 2014 17:56