Respondendo perguntas...
Já faz algum tempo que eu recebi algumas perguntas referentes aos artigos que eu escrevi. Então, com o devido atraso, vou tentar responder a todas elas.
Pergunta 1: Não entendi quando você disse que tinha contenção na Page Free Space (PFS) e na Shared Global Allocation Map (SGAM) com base na informação "O arquivo texto de saída mostrou no topo da lista as páginas 2:1:1 e 2:1:3, com o waittype PAGELATCH_UP”.
Resposta: Em todo arquivo de dados em qualquer banco, o SQL Server controla quais são as páginas e extents (conjunto de 8 páginas) utilizados através das suas páginas de controle (PFS, GAM, SGAM). Para um detalhamento da função de cada uma dessas páginas, veja o tópico “Managing Extent Allocations and Free Space” no Books Online.
Toda alocação de um objeto (ex.: nova tabela) é inicialmente feita em 1 página de 8K localizada em um extent misto (onde é possível ter páginas de diferentes objetos). Esse procedimento se repete até que 8 páginas estejam preenchidas, quando o SQL Server passa a alocar extents uniformes (64K) para esse objeto. A alocação e espaço utilizado por essas primeiras 8 páginas são controlados pela SGAM (S = Shared – extent mistos são compartilhados com páginas de até 8 objetos) e PFS, respectivamente. Depois de 64k, a alocação passa a ser controlada pela GAM e PFS.
Quando uma tabela temporária é criada na tempdb, o SQL Server tem que consultar/alterar essas páginas para especificar algo do tipo: a página 284, no extent 35 (284 dividido por 8), está em uso com 51% a 80% do seu espaço utilizado.
Esse controle acontece em todo arquivo de dados, sendo que SEMPRE a página 1 será a PFS, a página 2 será a GAM e a página 3 será a SGAM. Como o Tempdb sempre terá o ID = 2, podemos afirmar que a combinação 2:1:1 e 2:1:3 (dbid:fileid:pageid) indica a página PFS e SGAM do primeiro arquivo da tempdb.
O SQL Server utiliza latches (lightweight locks) como objetos de sincronização internos e de curta duração, com a função de proteger as estruturas de alterações indevidas. Por exemplo: quando o SQL Server precisa carregar uma página do disco para a memória, ele precisa garantir que não haverá nenhuma outra thread alterado o conteúdo dessa página enquanto ela está sendo carregada para a memória. Essa garantia é data através de latches de IO (shared). Além dos latches de IO, a engine também precisa garantir que somente uma thread está alterando o conteúdo da página em um determinado momento, usando latches update/exclusive em memória, vulgo PAGELATCH_UP e PAGELATCH_EX.
Então, quando vemos constantemente uma listagem muito grande da sysprocesses com waitresource 2:1:1 e 2:1:3, waittype PAGELATCH_UP e waittime > 0, sabemos que temos uma contenção na tempdb, especificamente nessas páginas.
Pergunta 2: A alteração que você fez foi a criação de mais arquivos físicos para a TEMPDB, correto? Mas ficaram todos no mesmo disco? Eu li um artigo dizendo que é interessante ter o número de arquivos físicos para a TEMPDB conforme o número de processadores da Maquina. Mas retorno a pergunta, sendo no mesmo disco adianta?
Resposta: Sim, todos os arquivos podem ficar no mesmo disco, pois nesse caso não estamos falando sobre um problema de tempo de resposta/contenção no disco, e sim um “hot spot” (ponto com muito acesso) em determinadas páginas que já estão em memória. A criação de novos arquivos, mantendo todos sempre com mesmo tamanho, faz com que o SQL Server trabalhe com rodízio e utilize um arquivo de cada vez (round robin) dividindo a contenção nessas páginas pelo número de arquivos criados, pois cada um deles terá suas próprias páginas de controle.
A recomendação da Microsoft (ela tinha que fornecer um número genérico) é que o número de arquivos físicos seja igual ao número de processadores físicos da máquina. Note que se seu servidor possuir 4 físicos com hyperthread, através do sistema operacional você verá 8 processadores lógicos. Para ver direitinho a organização dos processadores Intel na arquitetura IA-32, utilize o CPUCount (https://www.intel.com/cd/ids/developer/asmo-na/eng/275339.htm).
Com relação aos discos, é mais importante sempre separar dados e log. Mas se ainda assim você tiver outros discos sobrando, aí pode colocar os arquivos da tempdb em um disco separado, criar diversos filegroups, etc. Tudo é claro, acompanhado por testes de desempenho.
Pergunta 3: Nãoentendi bem no que o trace flag 1118 pode ajudar (ou piorar). E em que situações seriam interessantes a verificação do uso dele.
Resposta: Conforme explicado na resposta da pergunta 1, o SQL Server sempre aloca as primeiras 8 páginas em extents mistos antes de passar a alocar extent uniformes para o objeto. Quando o trace flag 1118 é habilitado, o SQL Server muda seu padrão de alocação, passando a alocar desde o início um extent uniforme (64K) para um objeto.
Isso faz com que os primeiros 8 acessos à PFS e SGAM (para alocação do objeto) sejam substituídos por somente um acesso à GAM e PFS, já que todo o extent será reservado para o objeto. Isso diminui em 8 vezes essa contenção inicial, se existir, na SGAM e PFS.
Quando usar: quando houver uma contenção na SGAM e PFS, mesmo depois de criar mais arquivos na tempdb. Isso também pode acontecer em um banco de dados de usuário, mas acho menos provável.
Pergunta 4: Partindo da sua explicação, seria correto dizer que se a modelagem for feita utlizando surrogate keys no índice cluster, unique index nos campos chaves e nonclustered index nos campos de consulta a necessidade de reindex também seria mínima ou quase nula? Pergunto isso porque você trocou a ordem das colunas no índice cluster, fazendo as linhas sempre serem inseridas no final da tabela. Então a saída seria trabalhar com campos indentity no índice cluster nas tabelas?
Resposta: Dessa forma você diminuiria a necessidade de fragmentação do índice cluster sim, mas ainda assim você deve manter uma rotina regular de manutenção, reindexando seus objetos. Os motivos:
- Ainda existiria a fragmentação dos níveis não-folha do seu índice, causados pela necessidade de se criar mais páginas e níveis de acordo com a quantidade de registros da tabela.
- O índice cluster garante a ordenação lógica das páginas (através de uma lista duplamente encadeada), mas fisicamente pode ser que a página corrente seja a #100 e a próxima seja a #75. Uma reindexação também vai tentar organizar as páginas para que a ordem lógica esteja de acordo com a ordem física.
- Os índices não cluster são ordenados de acordo com sua chave, então diferente do seu campo identity eles estão sujeitos a fragmentação. Exemplo: suponha um índice não cluster no campo nome, a cada inserção ordenada pelo identity no índice cluster, o não cluster pode colocar esse registro em qualquer lugar e causar fragmentação.
Sempre tenha uma rotina de manutenção para retirar fragmentação dos objetos do banco de dados!
Um cuidado que você tem que ter com essa estratégia de modelagem é para tabelas com muitas (muitas mesmo!) inserções/alterações nas últimas páginas, pois você pode criar um hot spot e ter problemas de concorrência.
PS: Mas de qualquer forma, eu também gosto de utilizar essa modelagem que você sugeriu.
Pergunta 5: Aproveitando a deixa, no servidor de desenvolvimento eu achei uma situação estranha. Olhando a sysprocesses pude notar muitos waittypes de writelog e oledb para um determinado DB. Olhei no dbcc sqlperf e a informação sobre o writelog (diga-se que o servidor fazia 2 dias que tinha sido reiniciado), estava altíssima. O recovery model é simple, mas as consultas começaram a ficar mais lentas. O que essas informações podem me dizer?
Resposta: O waittype OleDB pode significar muitas coisas, então temos que ver o que exatamente o SPID está fazendo para vermos qual ponto devemos atacar. Aqui está a descrição desse waittype: “This waittype indicates that an SPID has made a function call to an OLE DB provider and is waiting for the function to return the required data. This waittype may also indicate that the SPID is waiting for remote procedure calls or linked server queries to return the required data. The SPID may also be waiting for BULK INSERT commands or full-search queries to return the required data.
The OLEDB waittype is used to indicate several different wait states, including: linked server queries, full-text search queries, BULK INSERT commands, client-side Profiler traces, materialization of virtual tables like the sysprocesses table, log reader, and DBCC CHECKDB. ”
Quando temos uma contenção no writelog, usualmente isso é resultado de uma contenção no disco. Utilize o performance monitor para monitorar os contadores de disco (para todas as unidades) e ver se existe uma contenção no distinto.
Espero que tenha clarificado um pouco mais esses tópicos.
Continuem colaborando com o blog e vamos entender a fundo como funciona o SQL Server!
[]s
Luciano Caixeta Moreira