Algum de vocês já experimentou o seguinte, e você encontrou uma solução:
Uma grande parte do back-end de nosso site é o MS SQL Server 2005. A cada semana ou duas semanas, o site começa a ficar mais lento - e vejo as consultas demorando mais e mais tempo para serem concluídas no SQL. Eu tenho uma consulta que gosto de usar:
USE master
select text,wait_time,blocking_session_id AS "Block",
percent_complete, * from sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 order by start_time asc
O que é bastante útil ... dá um instantâneo de tudo o que está funcionando naquele momento contra o seu servidor SQL. O que é legal é que mesmo que sua CPU esteja atrelada a 100% por algum motivo e o Activity Monitor esteja se recusando a carregar (tenho certeza que alguns de vocês estiveram lá) essa consulta ainda retorna e você pode ver qual consulta está matando seu banco de dados.
Quando eu executo isso, ou o Activity Monitor durante os períodos em que o SQL começa a desacelerar, não vejo nenhuma consulta específica que esteja causando o problema - eles estão TODOS executando de forma mais lenta em toda a linha. Se eu reiniciar o MS SQL Service, então tudo está bem, ele acelera - por uma semana ou duas até que aconteça novamente.
Nada que eu possa pensar mudou, mas isso só começou há alguns meses atrás ... Idéias?
- Adicionado
Por favor, note que quando esta desaceleração do banco de dados acontece, não importa se estamos recebendo 100 mil page views por hora (hora mais ocupada do dia) ou 10 mil page views por hora (slow time) as consultas demoram mais tempo para concluir do que o normal. O servidor não está sob estresse - a CPU não está alta, o uso do disco não parece estar fora de controle ... parece fragmentação do índice ou algo do tipo, mas não parece ser caso.
Quanto a colar os resultados da consulta colada acima, eu realmente não posso fazer isso. A consulta acima lista o login do usuário realizando a tarefa, toda a consulta, etc etc .. e eu realmente não gostaria de distribuir os nomes dos meus bancos de dados, tabelas, colunas e os logins online:) ... eu posso dizer-lhe que as consultas em execução nesse momento são normais, consultas padrão para o nosso site que correm o tempo todo, nada fora da norma.
- 24 de março
Já faz cerca de duas semanas desde a última reinicialização. Fiz várias alterações: encontrei algumas consultas em que estávamos fazendo uso pesado de tabelas temporárias que eram totalmente desnecessárias e que nossos desenvolvedores mudaram como estavam fazendo isso. Eu ajustei o tamanho de alguns dos bancos de dados em constante crescimento (lenta mas seguramente) para um tamanho inteligente para seu crescimento. Eu ajustei as configurações de autogrowth para tudo, bem como para ser mais inteligente (eles estavam todos definidos para um crescimento de 1MB). Por fim, limpei um pouco o MSDB. Fazemos log shipping e realmente não precisamos manter anos e anos de pontos de backup, eu escrevi alguns scripts que mantêm isso em apenas alguns meses. Continuarei atualizando este tópico, pois é muito cedo para saber se o problema já foi resolvido.
Nós encontramos. Descobriu-se que na verdade era um servidor da Web que tinha um problema com um de seus pools de aplicativos. Ele ficaria paralisado executando o mesmo conjunto de consultas várias vezes (o que aconteceu para lidar com tabelas temporárias). Seria apenas loop e loop e, eventualmente, causar o servidor SQL ficar triste. Uma vez que esta máquina ofensora / pool de aplicativos foi encontrado e 'colocado para baixo' tudo foi resolvido.
Você precisa se perguntar: o que acontece em uma reinicialização do SQL? Muitas coisas, mas dois pontos relevantes vêm à mente:
1) A memória SQL é liberada.
É possível (não sei se é provável), que se a configuração de MaxMemory estiver definida muito alta, que o serviço SQL cresça para usar toda a memória disponível e o Windows comece a trocar coisas importantes arquivo de troca. Verifique se MaxMemory está definido para um valor razoável, deixando memória adicional suficiente para o que mais precisa ser executado nessa caixa (é um servidor SQL dedicado? Ou é também o servidor de aplicativos?)
2) O TempDB é reconstruído a partir dos tamanhos padrão.
Verifique os tamanhos dos arquivos tempdb padrão, especialmente o tamanho padrão e o intervalo de crescimento do arquivo de log do TempDB. Se o intervalo de crescimento estiver muito baixo, o log pode criar uma incrível fragmentação interna, o que pode reduzir drasticamente o uso normal. Veja estes dois excelentes artigos de blog da Kimberly Tripp.
Você faz uso pesado de tabelas temporárias ou cursores? Verifique se todos os cursores estão sendo fechados e desalocados corretamente. Também atente para os servidores vinculados - temos que usar um driver com bugs para um antigo servidor Informix vinculado e periodicamente significa que temos que reinicializar o servidor.
Se parece estranho, então procure pelo estranho.
Se ajustes nas configurações do sql server não ajudarem no gerenciador de tarefas do Windows:
vá para a guia Processos e, em seguida, Opções > colunas > adicione o tempo da cpu, lida, leia, escreva, outro e as opções da memória.
Volte para a lista de processos. Para cada coluna, classifique de alto a menor e observe os 5 principais processos. Alguma coisa fora do comum? por exemplo. Um vazamento de memória em um processo terá um número bizarro de alças. Temos algumas impressoras * ki que adicionam um identificador ao processo DCSLoader a cada 2 segundos. Depois de algumas semanas, uma máquina lista muita memória livre e cpu, mas um processo com 100.000 alças e mal move o ponteiro do mouse.
Verifique também a sua lista de tarefas agendadas. Diga ao seu AV para não escanear arquivos .mdf.
Dave,
Você verificou as estatísticas de espera? A consulta que você deu acima lista a coluna "last_wait_type". essa coluna pode ter alguns detalhes sobre o que as consultas estão esperando (rede, cpu, etc.)
Se o seu "Modelo de Recuperação" de backup estiver COMPLETO, então fazer um backup do DB e, em seguida, um backup dos logs de transação melhorará as coisas? Em um sistema que está ficando sem espaço em disco, esse tipo de coisa pode explicar o problema.
Eu pareço ter uma configuração muito semelhante à sua (16Gb, atualizado para 32Gb e MD1000 com um terabyte de discos, dual quadcore xeon).
A única coisa que me ajudou a diagnosticar problemas bizarros como esse no passado é beta_lockinfo por Erland Sommarskog. Corra quando estiver demorando e compare.
Também tive uma quantidade insana de problemas com o SQL 2005 antes do SP2, mas o SP3 é realmente estável.
Espero que isso forneça mais informações úteis:
SELECT D.text SQLStatement,
A.Session_ID SPID,
C.BlkBy,
ISNULL(B.status, A.status) Status,
A.login_name Login,
A.host_name HostName,
DB_NAME(B.Database_ID) DBName,
B.command,
ISNULL(B.cpu_time, A.cpu_time) CPUTime,
ISNULL((B.reads + B.writes), (A.reads + A.writes)) DiskIO,
A.last_request_start_time LastBatch,
A.program_name
FROM sys.dm_exec_sessions A
LEFT JOIN sys.dm_exec_requests B
ON A.session_id = B.session_id
LEFT JOIN (
SELECT A.request_session_id SPID,
B.blocking_session_id BlkBy
FROM sys.dm_tran_locks AS A
INNER JOIN sys.dm_os_waiting_tasks AS B
ON A.lock_owner_address = B.resource_address
) C
ON A.Session_ID = C.SPID
OUTER APPLY sys.dm_exec_sql_text(sql_handle) D
WHERE DB_NAME(B.Database_ID) = 'YourDBName' -- Comment out line for all db's
ORDER BY ISNULL(B.cpu_time, A.cpu_time) + ISNULL((B.reads + B.writes), (A.reads + A.writes)) DESC
Certifique-se de que o db está bem com:
DBCC CHECKDB -- Checks the allocation and structural integrity of all the objects in the specified database.
DBCC UPDATEUSAGE (bybox) -- Reports and corrects pages and row count inaccuracies in the catalog views
Fique de olho no espaço do log com:
DBCC SQLPERF(LOGSPACE)
Se você vir expansão acontecendo, isso irá desacelerar as coisas. Se você executar isso, verá que o seu espaço de log ficará mais próximo e próximo de 100%, então o log será expandido e a porcentagem diminuirá, já que tem algum espaço. Espero que você nunca consiga vê-lo expandir antes que seu backup seja ativado e limpe o log.
Principalmente configuração idiota. Acontece.
Primeiro, você deve executar regularmente a desfragmentação do índice em uma execução de manutenção. Agende como atividade, antes ou depois de fazer backups.
Em segundo lugar, não aumente automaticamente o banco de dados e, principalmente, não o autoshrink. Dependendo da carga, o autogrow / autoshrink são basicamente configurações de suicídio.
Não vejo um abrandamento do SQL Server como esse praticamente sempre. Você pode postar os resultados dessa consulta em momentos de estresse hugh? Claro que nada do seu lado sobrecarrega o SQL Server na época?