none
Timeout em INSERT RRS feed

  • Pergunta


  • Ola, pessoal


    Estou tendo problemas com um comando Insert em uma tabela com milhões de registros.

    O ambiente é de "baixo custo", quero dizer, mesmo essa tabela sendo grande, o sistema (web) funciona bem, com SQL Server Express, o servidor com Server 2012, deve ter 8Gb de memoria...
    O tempo limite da execução de comandos com essa conexão eu acho que está no padrão, (30 segundos);


    - Acontece um erro de timeout bastante esporádico em comandos Insert nessa tabela, e de forma bastante aleatória. Outros diversos clientes usam esse mesmo sistema, com configuração bem parecida, e não acontece nada disso. Porém, este cliente especificamente tem uma movimentação maior do que os demais, em média.

    Pensei em algum tipo de LOCK de tabela. Que situações o SGBD pode fazer lock?

    Analisado o problema mais a fundo, vimos que há relatorios em que se abre um SqlDataReader, e trata os registros em loop. 
    Nesse momento, enquanto o DR estiver aberto, é feito lock que impeça Insert na tabela? (não são queries sem "where", nunca).

    Tambem tem outras considerações: em um processo de reintegração de registros, que pode efetuar milhares de Insert nessa tabela em um curto espaço de tempo (cada uma abrindo a conexão novamente);

    Alguem tem indicações do que pode ser?

    Julio C.

    sexta-feira, 15 de abril de 2016 19:00

Respostas

  • Julio,

    Uma consideração, durante este período de insert por acaso existe algum outro procedimento de Select, Update ou Delete que possa estar ocorrendo nesta mesma tabela?

    O select abaixo vai lhe ajudar a encontrar possíveis locks que venham a ocorrer neste banco:

    SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS LockedResource, L.request_mode AS LockType, ST.text AS SqlStatementText, ES.login_name AS LoginName, ES.host_name AS HostName, TST.is_user_transaction as IsUserTransaction, AT.name as TransactionName, CN.auth_scheme as AuthenticationMethod FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE resource_database_id = db_id()

    order by request_session_id



    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    sexta-feira, 15 de abril de 2016 23:28
  • Julio,

    Na query que alimenta seu SqlDataReader coloque a instrução "WITH(NOLOCK)" para ver se o problema ainda continua.

    Sobre o timeout no INSERT, caso não seja LOCK, analise os índices que existem na tabela (já passei por algo parecido e no meu caso o problema era excesso de índices na tabela)

    []s


    Abraços, Wellington Rodrigues

    • Marcado como Resposta Julio Costi sexta-feira, 15 de abril de 2016 23:40
    sexta-feira, 15 de abril de 2016 19:20

Todas as Respostas

  • Julio,

    Na query que alimenta seu SqlDataReader coloque a instrução "WITH(NOLOCK)" para ver se o problema ainda continua.

    Sobre o timeout no INSERT, caso não seja LOCK, analise os índices que existem na tabela (já passei por algo parecido e no meu caso o problema era excesso de índices na tabela)

    []s


    Abraços, Wellington Rodrigues

    • Marcado como Resposta Julio Costi sexta-feira, 15 de abril de 2016 23:40
    sexta-feira, 15 de abril de 2016 19:20
  • Julio,

    Uma consideração, durante este período de insert por acaso existe algum outro procedimento de Select, Update ou Delete que possa estar ocorrendo nesta mesma tabela?

    O select abaixo vai lhe ajudar a encontrar possíveis locks que venham a ocorrer neste banco:

    SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS LockedResource, L.request_mode AS LockType, ST.text AS SqlStatementText, ES.login_name AS LoginName, ES.host_name AS HostName, TST.is_user_transaction as IsUserTransaction, AT.name as TransactionName, CN.auth_scheme as AuthenticationMethod FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE resource_database_id = db_id()

    order by request_session_id



    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    sexta-feira, 15 de abril de 2016 23:28
  • Wellington,

    Eu fiz alguns testes já, numa base local, gerando relatorios e ao mesmo tempo inserindo, e não tive problemas... o DR (por si só), apesar de  manter a conexão aberta com o BD o tempo todo, a principio, não bloqueou os Insert que eu fiz na tabela.... Ao menos no meu ambiente...

    Eu cheguei a ler algumas coisas sobre o nolock, não sei bem que situações ele bloquearia a tabela inteira, mas acho que não é o caso.. mas numa segunda etapa acho que vou testar isso mesmo (os contras que tem no uso do nolock seriam relacionados a UPDATE eu acho, e nessa tabela são rarissimos os update);

    Valeu pela atenção.

    --

    Junior Galvão,

    "por acaso existe algum outro procedimento de Select, Update ou Delete que possa estar ocorrendo nesta mesma tabela"

    Sim, é exatamente esse o cenario. Essa tabela mantem a movimentação contabil, são no minimo, centenas de registros inseridos em uma manhã, por exemplo, e ao mesmo tempo são tirados relatorios e usado o sistema todo, onde com certeza são feitas consultas na tabela de contabilidade... Mas enfim, isso ocorre ja em todos os clientes (o mesmo sistema), e não tem esse problema.

    Deixei rodando um SQL Express Profiler (ferramenta de terceiros, não avançada como o Xtend Events, mas bem interessante e leve), para tentar identificar... o "grande problema" dessa questão é que é algo esporádico (e aleatório), ou seja, vai ter que ficar rodando um bom tempo, até dar novamente algum erro.

    Vou fazer uns testes tambem com essa consulta que me passou, tirando os relatorios e executar ele... achei que pode ser bem interessante (para diversos casos, inclusive).

    Obrigado!


    Julio C.

    sábado, 16 de abril de 2016 12:36
  • Atualização:

    Olhando melhor o ambiente, vi que a tabela está com mais de 1.000.000 de registros, e é muito movimento, em alguns momentos, há varias gravações e consultas simultaneas. 

    O erro é totalmente aleatório, semana passada deu 5x uma atras da outra (e depois nunca mais). Hoje deu novamente, 1x só.

    Sera que nesse caso esta com sobrecarga, por ser SQL Server Express?


    Julio C.

    sexta-feira, 29 de abril de 2016 18:51
  • Julio,

    Então, sobrecarga por ser o SQL Server Express sinceramente não acredito, o que poderíamos tentar fazer é pegar este query realizar o seu processamento dentro do Management Studio e analisar o seu plano de execução, bem como, verificar se existe a necessidade de criar algum índice.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sábado, 30 de abril de 2016 14:32
  • Obrigado pela opinião sobre o descarte da ideia de sobrecargar...

    realmente, estava analisando o BD agora, novamente, o sistema grava a Data de Criação dos registros, então consultando desta forma:

    select... from tabela... where DataCriacao >= '02/05/2016 09:18:00'  and DataCriacao <= '02/05/2016 09:25:30'

    pude ver que ficou um intervalo de uns 2~3 minutos tanto antes quanto depois de um registro que deu erro, sem ter nenhuma inserção na tabela.

    Como os comandos que falharam são guardados, via sistema, a gente está executando estes insert, um por um, quando identificada a falha (manualmente), pelo management, e é sempre instantenea a resposta de inserção com sucesso, independentemente do momento em que se faça.

    Acontece isso muito esporadica e aleatoriamente.. não sei se é viavel/possivel e se adiantaria, analisar o plano de execução. estou pensando mais  no sentido de algum bloqueio geral da tabela...  (a chave dela é um identity, e tem índices criados para as constraints);


    Julio C.



    • Editado Julio Costi terça-feira, 3 de maio de 2016 14:48
    terça-feira, 3 de maio de 2016 14:45
  • tambem; estou rodando um programa de terceiros que captura todos os comandos que estão acontecendo no BD (profiler express); o problema é que ele registra muito pouco tempo (bastante movimento), e acabo não conseguindo visualizar o que ocorria no momento da falha (a lista segura uns 2 , 3 min de comandos, apenas)

    Julio C.

    terça-feira, 3 de maio de 2016 14:50
  • Julio,

    Talvez seja o caso de tentar identificar o ID desta ou demais transações que estão fazendo acesso a este banco de dados e através do comando DBCC InputBuffer ou da dmv sys.dm_exec_sql_text tentar obter exatamente o comando que esta sendo processado.

    Um recurso interessante seria utilizar o Activity Monitor dentro do Management Studio para justamente obter as querys que estão sendo processadas no momento.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 3 de maio de 2016 15:42
  • Junior Galvão, 

    Não entendi muito bem sobre tentar pegar o ID das transações, como seria isso? 

    Eu comentei acima que já tenho o comando que falhou (gravo numa tabela quando falha a execução do comando pelo Ado.net, com um Try / Catch), e executando-o novamente (no management) é instantaneo (assim como todas as outras inserções que ocorrem normalmente nessa tabela);

    Comentei tambem que analisando outros registros gravados nessa mesma tabela, não há nenhum registro por varios minutos ANTES e APOS o erro... (nem UPDATES) por isso, não seria por "concorrencia", nem nada do tipo.



    Assim, o grande problema em estar monitorando o ambiente exatamente QUANDO ocorrer a falha é que é muito aleatório, e o profiler express (que fiz testes) guarda apenas cerca de 2 ou 3 minutos de comandos (os comandos mais antigos vão sumindo, para não superlotar a lista). Teria que estar monitorando no exato momento em que ocorre o problema...

    Tambem fiz testes executando a consulta que voce passou , para tentar identificar LOCKS. Sempre que executo, não retorna nada...

    é dificil pois teria que estar monitorando (executando a consulta) exatamente no momento em que ocorre a demora no insert, OU em uma situação que ocorreria a falha no insert, se fosse executado naquele momento.

    Enfim, uma situação dificil de resolver....


    Julio C.

    terça-feira, 3 de maio de 2016 23:28
  • "Um recurso interessante seria utilizar o Activity Monitor dentro do Management Studio para justamente obter as querys que estão sendo processadas no momento."

    Mesma situação sobre o  Select dos locks... 
    Em qual momento? eu não consigo saber quando esta situação vai acontecer novamente.

    É por isso que eu estou tentando levantar hipoteses.......

    locks na tabela, sobrecarga no sql server (ja descartada), ... o que mais pode causar erro de timeout num simples "insert" que normalmente é executado em menos de 1 segundo?


    Julio C.

    terça-feira, 3 de maio de 2016 23:34
  • Julio,

    Se descobrir (pois sei que vai), me conta. (:D)

    Sei lá, não existe alguma função desenvolvida em TSQL que possa estar gerando tal lentidão? Talvez constraints do tipo CHECK ou muitos índices... 

    Enfim, está muito estranho essa lentidão estar assim do nada... 

    Embora não tenha nada a ver mas qual o volume de dados nesta tabela? Qual a quantidade de consultas que a mesma recebe por segundo?

    quarta-feira, 4 de maio de 2016 12:26
  • Julio, 

    Complementando... (embora acho que já tenha verificado isso)

    Não custa nada olhar se existe alguma "trigger" para operações de "INSERT" que esteja levando a performance lá para o chão.

    As vezes estes detalhes acabam passando batido e talvez alguma manutenção tenha sido feita e você não esteja sabendo.

    []s

    quarta-feira, 4 de maio de 2016 12:38
  • Não tem triggers...

    Como comentei, em 99,99% do tempo, os insert nessa tabela ocorrem instantaneamente. Não tem demora.
    (eu executo exatamente o mesmo sql que falhou por timeout, instantaneamente)

    Tem indices, que poderiam prejudicar a performance, mas acho que seria "sempre". 
    Pesquisei tambem sobre a fragmentação, acho que mesmo que o indice clusterizado estivesse bastante fragmentado, não demoraria tanto assim (No entanto, também não é o caso, pois até recriei os indices, e o problema continua, aleatoriamente)

    A quantidade de linhas nela, como comentei acima, está em 1 milhão e poucas.

    Mas como já comentei, os indices, o volume de dados... etc... seriam pontos que ocasionariam uma performance baixa sempre, e não é o caso.

    Veja, a tabela tem 1 milhão de registros, mas o insert é instantaneo sempre, e quando dá o timeout (ta por padrão 30), quer dizer que há uma demora NO MINIMO 30x maior (ou vai saber quanto, pois estourou o tempo - se fosse 60 poderia estourar tbm... e assim por diante);

    Sobre consultas:

    No uso normal do sistema, não tem tantos acessos assim a essa tabela (a ponto de demorar tanto o insert), tanto que normalmente, estão correndo select's nela, e os insert ocorrem normalmente.
    O que pode talvez é algum relatorio mais pesado, que faça diversas consultas a essa tabela, estar causando muita perda de performance, ou bloqueando a tabela.. Mas não estou conseguindo capturar isso.

    Me parece algo no sentido de bloqueio, ou perda de performance por causa de algum tipo de consulta, talvez, que eu desconheço... meu conhecimento pára nesse ponto... não imagino o que tem mais probabilidade de causar isso.


    Julio C.


    • Editado Julio Costi quarta-feira, 4 de maio de 2016 13:15
    quarta-feira, 4 de maio de 2016 13:14
  • Junior Galvão,

    " o que poderíamos tentar fazer é pegar este query realizar o seu processamento dentro do Management Studio e analisar o seu plano de execução, bem como, verificar se existe a necessidade de criar algum índice."

    este procedimento , quanto a analisar a query, teria que ser feito no ambiente de produção do cliente, estou certo? 

    ou pode ser num backup restaurado do BD no proprio cliente;;; ou pode ser no meu ambiente?

    Obrigado!


    Julio C.

    quarta-feira, 4 de maio de 2016 14:44
  • Julio,

    O mais indicado é fazer o ambiente que esta apresentando este cenário, quando mais próximo da realidade melhor será.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 5 de maio de 2016 15:08
  • update:

    Analisando novamente, com o Express Profiler, identifiquei uma situação:

    tem umas consultas bem pesadas e demoradas na tabela de contabilidade; nos momentos em que essa consulta (UMA delas especificamente) aparecem no profiler (que estou colocando tempo > 10s),  tem um INSERT que o tempo de execução dele foi la para cima tambem (não chegou a dar timeout nesse caso);


    Julio C.


    • Editado Julio Costi sexta-feira, 6 de maio de 2016 19:51
    sexta-feira, 6 de maio de 2016 18:29
  • estranho que, pegando a consulta "demorada" e o Insert, executando-os  pelo Management, o Insert é bem rapido....

    Ou seja, enquanto roda a consulta (uns 50seg)  , noutra janela roda varias vezes o insert (bem rapido)

    --

    Update: Nada disso. Enquanto está rodando essa consulta, o Insert fica realmente lento, e daria timeout se tivesse definido tempo definido para isso (não sei se no Management tem, se tem deve ser muito maior do que o do CMD padrão do ADO.NET).

    Assim que termina a consulta (alias é interessante que, logo que inicia, ainda dá Insert rapido, mais adiante, depois de uns 3 segundos, é que o Insert bloqueia);

    - Inclusive, usando a sp_lock aparece uma conexão com WAIT, possivelmente a do sspid do insert....

    Sendo assim em tese, em parte, o problema está ao menos identificado.

    --

    Obrigado a todos pelas dicas. Vou testar hint para não bloquear, mas não sei se vai ser a solução..

    Update 2: Recriamos um indice que estava a bastante tempo sem ser recriado (ficou de fora da rotina, por esquecimento) e a consulta melhorou bastante a performance.
    • Editado Julio Costi sexta-feira, 6 de maio de 2016 21:44
    sexta-feira, 6 de maio de 2016 19:30