No momento, estou passando por um problema estranho que meu entendimento sobre o SQL Server não combina muito bem. Usamos o SQL como armazenamento de arquivos para nosso serviço de armazenamento interno e nosso banco de dados tem cerca de meio milhão de linhas. A maioria dos arquivos (86%) tem 1mb ou menos, mas mesmo em cópias novas de nosso banco de dados onde simplesmente preenchemos a tabela com dados para fins de teste, parece que as linhas com grandes quantidades de dados armazenados em um BLOB frequentemente causar tempos limite quando o nosso SQL Server está sob carga.
Meu entendimento de como o SQL Server exclui linhas é que é um processo de coleta de lixo, ou seja, a linha é marcada como um fantasma e a linha é posteriormente excluída pelo processo de limpeza fantasma depois que as alterações são copiadas no log de transações. Isso sugere que, independentemente do tamanho dos dados no blob, a exclusão de linhas deve estar próxima de instantânea. No entanto, ao excluir essas linhas, estamos definitivamente experimentando um grande número de tempos limite e um desempenho incrivelmente baixo.
No nosso conjunto de dados de teste, seus arquivos com mais de 30 MB causam esse problema. Este é um caso extremo, não nos deparamos com frequência com eles e, apesar de estarmos analisando o fluxo de arquivos SQL como uma solução para alguns de nossos problemas, estamos tentando restringir a origem desses problemas.
Estamos realizando nossas exclusões dentro de uma transação. Também estamos realizando atualizações de metadados, como estatísticas de tamanho de arquivo, mas elas existem em uma tabela separada, longe dos dados do arquivo em si. Os dados da hierarquia são armazenados na tabela que contém as informações do arquivo.
Realmente, no final, não é exatamente o que estamos fazendo em torno das exclusões que importam, simplesmente não encontramos nenhuma referência ao baixo desempenho de exclusão nas linhas que contêm uma grande quantidade de dados em um BLOB. Estamos tentando determinar se essa é mesmo uma avenida que vale a pena explorar ou se deve ser um dos nossos processos em torno da exclusão que está causando o problema.Existe alguma situação em que isso possa ocorrer? É comum um servidor de banco de dados chegar ao ponto de tempo limite completo quando muitas dessas exclusões estão ocorrendo simultaneamente? Existe uma maneira de combater este problema, se existir?
Você pode definir um "tempo limite"? Quanto tempo demora o comando a concluir?
O SQL Server registra a exclusão no log de transações e, em seguida, quando os pontos de verificação do banco de dados, a exclusão é realmente removida do arquivo de dados. Quando você executa a exclusão, as páginas são carregadas na memória (se ainda não estiverem lá) e a exclusão é gravada no log de transações.
Quando você exclui dados da tabela, quantos registros você está tentando excluir de uma só vez?
Existe alguma chave estrangeira referenciando esta tabela, ou a própria tabela possui chaves estrangeiras? Eu já vi o desempenho da exclusão cair no banheiro se houver uma chave estrangeira sem índice na coluna, especialmente quando você tem tantas linhas.
Talvez tente incluir o plano de execução durante a exclusão e veja se há etapas inesperadas (por exemplo, varreduras de tabela). Verifique também o DMV sys.dm_exec_requests quando você tiver uma solicitação bloqueada ativa, particularmente as colunas wait_type, wait_time e wait_resource.