Antecipadamente: desculpe pela duração da pergunta ... incapaz de obter o equilíbrio certo entre detalhe e brevidade.
Estamos tendo problemas com o DB Server para o nosso aplicativo web, onde as consultas que devem (e geralmente são) executadas em um tempo muito curto (< 10ms) são, em ocasiões aleatórias, de 1 a 30 segundos para serem executadas - sem padrão óbvio. De acordo com nossos rastreadores de profiler, alguns deles são até mesmo consultas "do-nothing", como "exec sp_reset_connection" (geralmente executado em 0ms; picos observados de 3 ~ 6s) e "SET NO_BROWSETABLE ON" , etc. Alguns exemplos são:
SELECT * FROM [Localisation].[TimeZoneRule] WHERE [Name] = 'EU'
Em que TimeZoneRule tem cerca de 500.000 linhas em 5 colunas. Tem uma chave primária substituta e um índice em Name . Geralmente leva 0,97 ms, picos em 11s. A tabela NUNCA foi escrita para (foi pré-preenchida antes da entrada em funcionamento). O Profiler registra como tendo de 0 a 15 CPU, 18 a 25 leituras, 0 a 1 gravações (não tem idéia do motivo da gravação).
UPDATE [Core].[User] SET [LastUsed] = GETUTCDATE() WHERE Id = '<uid>'
Em que User tem cerca de 30.000 linhas em cerca de 10 colunas (uma delas é uma coluna Xml). Id é a chave primária em cluster. A tabela é escrita e lida regularmente. Geralmente leva 10 ~ 20ms, picos em 26s. O Profiler registra como tendo 0 CPU, 15-36 leituras, 0-1 gravações.
INSERT INTO [Log].[Session] (ASPSessionId, Start, ClientAddress, ClientSoftware, ProxyAddress, ProxySoftware)
VALUES(<number>, GETUTCDATE(), '<ipv4address>', '<User agent string>', '<ipv4address>', '<proxy software name (if present)>')
Em que Session tem cerca de 1.000.000 linhas em cerca de 8 colunas. Tem uma chave primária (identidade) substituta e um índice em ASPSessionId . A tabela é escrita regularmente, mas raramente é lida (somente por nós diretamente do SSMS). Geralmente leva 15 ~ 150ms, picos a 5s. Eu não tenho o registro do perfil disponível, mas da memória, a CPU está em torno de 0, as leituras e gravações estavam entre 0 e 100 cada.
A configuração que estamos usando é uma configuração espelhada com um Dell 2950 como princípio (2 4-core xeon 2.6, 16Gb RAM) e um Dell 6850 como espelho (4 HT Xeon 3.2, 8Gb RAM). Ambos executando o SQL 2005 SP4 de 64 bits. O banco de dados em questão não é particularmente grande, com cerca de 16 GB de tamanho. O primário possui 6 discos SAS divididos em 3 volumes RAID-1; uma para System + Page + TempDB, uma para o MDF do banco de dados e outra para o log de transações + backup de log por hora + backup diário de banco de dados. Eu sei que a situação do log está longe de ser a melhor - em termos de disco IO (veja abaixo) e segurança de dados.
Até agora, nós achamos que eliminamos:
- O espelho. Separamos os servidores e rodamos usando um deles (e depois trocamos para o outro), mas os problemas de desempenho permaneceram.
- Bloqueio devido a bloqueios (*).
TimeZoneRule nunca é gravado e, por minha conta, nunca deve ter um bloqueio exclusivo sobre ele. Além disso, verificamos os rastreios e, em muitas ocasiões, a "consulta com problema" é a única em execução - a única outra atividade é a desconexão de outras conexões
- Má indexação. Com os números baixos para leituras e CPU, sugeriria que o SQL Server está usando índices de maneira eficaz.
- Disco IO. O PerfMon indica alguns valores ímpares para a unidade de arquivo de dados (mas apenas essa unidade) - embora as taxas de leitura / gravação de dados raramente excedam 32 KB / s, o Comprimento Atual da Fila de Disco aumenta para cerca de 215 para durações de 2-5 segundos a aproximadamente 45 -Intervalos de 60 minutos sem padrão fixo. No entanto, eles não se correlacionam com tempos de desempenho de consulta ruim. O comprimento da fila de disco para as outras duas unidades [system + page + tempdb] e [log + backups]
nunca excede 3.
(*) Tentamos obter o profiler para capturar eventos relacionados à captura de bloqueio, mas o rastreamento incha para proporções ilegíveis e, pior, o aplicativo da web fica paralisado.
Não sendo DBAs, estamos ficando sem ideias rapidamente. Alguém pode pensar em algo que eu deva considerar olhar para o próximo ou qualquer coisa que eu tenha estupidamente perdido?