Bases de Dados de Sistema – Distribution

No inicio da minha carreira como DBA, me lembro de entrar numa instância e ver uma DB diferente misturada com as DBs de sistema. A primeira coisa que pensei foi: “Quem foi o artista que conseguiu fazer attach de uma DB junto com as outras de sistema??? Nem sabia que dava para fazer isso!!!”.

Como um bom DBA junior, fui perguntar ao meu colega “guru”, também conhecido como “Books OnLive”. A partir deste momento fiquei a saber que esta DB era necessária para suportar a replicação, que um dia tinha sido configurada naquele servidor…

Aprofundando mais o tema, a base de dados “distribution” suporta a replicação guardando meta-dados e histórico para todos os tipos de replicação, sendo que, para replicações transacionais, a DB guarda também as transações que serão replicadas.

Mas cuidado, nem sempre a DB distribution vai se chamar “distribution”, pois o nome é configurável. Como “distribution” é o nome sugerido pelo SQL Server, geralmente é o que os DBAs escolhem. Porém, pelo facto de o SQL Server permitir a criação de várias distribution DB, o nome fica por conta do freguês.

Mas porque ter mais do que uma distribution?
Quem já trabalhou com replicação sabe que podemos ter vários “Publisher”, e é possível utilizar uma única DB distribution para todos eles. Porém, imagine a carga que esta DB vai receber? Ainda mais se foram replicações transacionais. Por isso é uma boa prática criar uma distribution para cada publisher.

Já que estamos na onda das boas práticas, outra sugestão para aumentar a eficiência é alojar os ficheiros de dados e log desta DB em discos separados (um disco para cada ficheiro). Obviamente se a carga não for tão grande, podemos ter o MDF junto com os outros MDFs e o LDF no disco de logs das outras DBs (Você já separa isso, certo? :).

Backups
A DB distribution requer FULL backups regulares, por motivos óbvios. Esta DB vem por default em simple recovery model, então não precisamos fazer backups de log.

Ficamos aqui por hoje! Semana que vem irei escrever sobre a misteriosa e oculta “Resource” database. No que será o último, artigo desta série de bases de dados de sistema.

See ya later! ;)

Outros posts da série “Bases de Dados de Sistema”:

Bases de Dados de Sistema – Master

Aqui estamos para mais um artigo sobre DBs de sistema. E desta feita temos nada mais, nada menos do que a temida MASTER!!!! A master é tida por muitos profissionais como a DB intocável, e realmente tem uma supra importância dentro de uma instância de SQL Server. Mas o que a master tem de tão especial, além deste sugestivo nome? Seguem alguns factos acerca desta base de dados:

  • A master guarda toda informação a nível de sistema da instância. Logins, linked servers, endpoints e definições de configuração estão guardados nesta DB.
  • A informação acerca de todas as outras DBs, incluíndo a localização dos ficheiros, também reside na master.
  • Objectos de sistemas, como SPs e views eram guardados na master, e não são mais. Existe uma BD “invisível” que guarda isso tudo, chamada “Resource”.
  • Informação necessária para a inicialização da instância também fica na master.
  • Espero que pouca gente tenha passado por isso, mas s1em a master, a instância NÃO inicializa!

São poucos factos, porém bons factos. Já deu para perceber a força da master, certo? A master vem, por default, com um ficheiro de dados MDF e um de log MDF e definida em “simple” recovery model. Não é necessário, e nem possível, adicionar ficheiros para melhorar o desempenho desta DB, que aliás, deverá ser alvo de backups periodicamente, visto a sua criticidade.
Alguns DBAs costumam guardar objectos como Stored Procedures de manutenção e tabelas na master, o que faz o backup ser uma necessidade ainda maior. Falando de criação de objectos na master, evite criar SPs com o prefixo “sp_”, pois o SQL Server vai primeiramente verificar a existência deste objecto na master, antes de verificar na DB correcta. Os malefícios deste facto são óbvios.

Mas, se minha master for a vida, o que eu faço? 
Existem aqui duas opções: Restaurar um backup recente (o ideal) ou fazer rebuild da master.

Vamos falar primeiro do caso de ser feito um rebuild. Primeiro de tudo, tenha em mente que o SQL Server guarda definições da instância, localização de ficheiros, logons, etc… Como já dito anteriormente. Um rebuild irá “limpar” tudo isso! Ou seja, isso é um caso extremissimo, mais comum quando temos a nossa master corrompida, ou quando é necessário se alterar a collation da instância. 

Portanto, se você está vendo a seguinte mensagem no seu ERRORLOG, e não tem um backup, voce é um coitado está no lugar certo!

“SQL Server is exiting. Restore the master database or reinstall.” 

Como fazer o rebuild, então? 

  1. Abra uma janela do terminal e seleccione a paste onde se encontra o Setup.exe (no CD de instalação do SQL Server, ou no folder onde se encontram o binário de instalação).
  2. Execute o seguinte comando, adaptado ao seu caso (altere o nome da instância e o user que será definido como Sysadmin):
  3. Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ]

        Obs.: Se a ideia for alterar a collation, adicione a linha de comando acima o seguinte parâmetro: /SQLCOLLATION=CollationName
        A lista de collations disponíveis pode ser vista aqui: http://technet.microsoft.com/en-us/library/ms144250(v=sql.105).aspx

    1. O comando irá terminar sem nenhuma mensagem. Verifique o ficheiro “<unidade>:Program FilesMicrosoft SQL Server110Setup BootstrapLogsSummary.txt”, para mais informações.
    2. Neste ponto o rebuild estará completo, agora resta iniciar o serviço e reconfigurar tudo outra vez, se for o caso!

    Restaurando a master.

    Para restaurar uma master corrompida, é imprescindível efectuar todos os passos anteriores (em Como fazer o rebuild, então?). Se você chegou ao ponto 4 com sucesso, podemos começar o restauro da master!

    1. Pare o serviço da sua instância
    2. Abra uma janela do Terminal, e execute o seguinte comando para iniciar a sua instância em single-user mode:
    3. NET START MSSQLSERVER /m (Substitua MSSQLSERVER pelo ID da sua instância) 

    4. Em outra janela do terminal, execute o seguinte comando:
    5. sqlcmd -E

      (Este comando funciona para autenticação integrada e instancia default. Se você utilizar outro tipo de autenticação, utiliza -U <USER> -P <PASSWORD> ao invés de -E. Execute sqlcmd sem parâmetros para mais informações) 

    6. Executar o seguinte comando:
    7. RESTORE DATABASE master FROM FILE=’<ªPATH PARA O FICHEIRO DE BACKUP>’
      WITH REPLACE
       GO

    8. Fechar todas as janelas do terminal
    9. Iniciar sua instância normalmente
    10. Se tudo deu certo, sua instância estará funcionando perfeitamente!
    11.  

      Espero ter ajudado! Até a próxima!

    Bases de Dados de Sistema – The top model

    Na sequência desta série sobre bases de dados de sistema, iremos falar sobre o mundo fashion do SQL Sever, sobre a base de dados que todas tem inveja e tentam imitar, a única base de dados que não engorda, e só come memória com baixa caloria. Apresento-lhes a…. MODEL!

    A base de dados “model”, de facto, existe. E seu nome não é mera coincidencia. Esta DB é utilizada como template para todas as DBs que são criadas. Então, sempre que o comando “CREATE DATABASE” é executado é feita uma cópia da DB model, sendo o resto do espaço preenchido por páginas vazias.

    Um exemplo básico é a TempDB. Sempre que o SQL Server é inicializado/reinicializado a TempDB é recriada, E adivinhem quem a TempDB copia??
    Desta forma, se não houver “model”, não há TempDB. E sem a TempDB a instância não irá funcionar.

    O facto de ser fashion oferece um poder adicional a model que nós podemos explorar. Muitos profissionais criam objectos na model, que serão úteis nas bases de dados criadas, para efeitos de monitoria, por exemplo. Outra possibilidade é a definição dos valores de espaço alocado inicialmente para os ficheiros de dados e log, assim como das taxas de crescimento. Os valor que vêm “de fábrica” não são os mais adequados. Desta forma é possível garantir que todas as bases de dados criadas estarão em conformidade com o indicado, excepto se definido o contrário. Assim é possível garantir, de certa forma, a aplicação de boas práticas.

    Minha model está corrompida, o que fazer??

    Verificando o folder “install” presente dentro da root da instância, podemos verificar que não existem ficheiros para criação da model. Desta forma, recomendamos que seja feito backup da DB model. Este backup é ainda mais importante no caso de terem sido feitas alterações/criações de objecto na model.

    Se o ficheiro de log está corrompido, siga os seguintes passos (com o SQL Server offline, obviamente):

    1. Abra uma janela da Command Line (CTRL+R, escreva “cmd”, clique em “Ok”), entre no directório dos binários do SQL Server (ex: E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn) e execute o seguinte comando:
    2. start sqlservr.exe -c -T3608

    3. Abra outra janela do Command Line e execute o seguinte comando 
    4. sqlcmd -E

    5. Agora vamos executar o seguinte comando para fazer “detach” da DB:
    6. sp_detach_db model

      GO

    7. O próximo passo será adicionar novamente a model a instância, sem o ficheiro de log, utilizando o seguinte comando:
    8. Obs: Substitua o valor do parâmetro FILENAME pelo path do ficheiro MODEL.MDF referente a sua instância.

      USE MASTER
      GO
      CREATE DATABASE MODEL
            ON (FILENAME = ‘<DISCO>:PROGRAM FILESMICROSOFT SQL SERVERMSSQL10_50.<INSTANCIA>MSSQLDATAMODEL.MDF’)
            FOR ATTACH ;

    9. Feche todas as sessões da command line e inicialize a instância normalmente.

    Caso o problema seja mesmo o ficheiro de dados (mdf), existem duas hipóteses: Restaurar um backup da model ou copiar a model de outra instância. Ambos os casos podem ser feitos da mesma forma que o exposto anteriormente, alterando apenas o ponto 4 no caso de se fazer um restore.

    Espero que este post tenha serventia não só para pessoas que estão desesperadas, mas também para quem gueria saber mais sobre a model.
    Qualquer dúvida, estou ao dispor!!!

    Até a próxima.

    Outros posts da série “Bases de Dados de Sistema”:

    Bases de Dados de Sistema – R.I.P. MSDB.

    Na continuação da série sobre DBs de sistema, iremos hoje falar sobre mais uma peça importante no funcionamento de uma instância de SQ Server – a MSDB. Basicamente, esta é apenas mais uma DB de sistema. Com recovery model definido como SIMPLE de origem, esta DB é muito conhecida como “a DB do SQL Server Agent”.
    De facto, o SQL Server Agent utiliza esta DB para gerir e guardar informações sobre agendamentos, alertas, operadores e jobs, porém a MSDB também suporta o Service Broker, DB Mail, e mesmo o famoso Management Studio (SSMS)! Um exemplo que pode ser dado é a acção de Restore de uma DB: O SSMS se baseia nos eventos de backup que são guardados na MSDB para sugerir um plano de restore.

    Perdi minha MSDB :( O que fazer? 

    É importante ressaltar que a MSDB não pode entrar em Emergency Mode, então não podemos executar o comando “salvador” dbcc checkdb (msdb, repair_allow_data_loss).

    Se a sua MSDB corromper existem duas formas de recupera-la:

    • O primeiro e mais óbvio: Utilizar um backup! Sim é preciso manter processos de backup periódicos na MSDB… Acho que já dei motivos suficientes para convencer quem não faz, pois coisas importantes estão lá!
    • A segunda opção é recriar a MSDB. Opção esta que apenas servirá para manter a instância em funcionamento, pois sem backup os dados da MSDB estarão perdidos :(
    • Seguem os passos:

    1. Pare o serviço do SQL Server.
    2. Abra uma janela da Command Line (CTRL+R, escreva “cmd”, clique em “Ok”), entre no directório dos binários do SQL Server (ex: E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn) e execute o seguinte comando:
    3. start sqlservr.exe -c -T3608

    4. Abra outra janela do Command Line e execute o seguinte comando 
    5. sqlcmd -E

      (Este comando funciona para autenticação integrada e instancia default. Se você utilizar outro tipo de autenticação, utiliza -U <USERNAME> -P >PASSWORD> ao invés de -E. Execute sqlcmd sem parâmetros para mais informações)

    6. Execute o seguinte comando, na console do SQLCMD:
    7. use master
      go
      sp_detach_db msdb
      go

    8. No Windows Explorer mova os ficheiros da MSDB, msdbdata.mdf e msdblog.ldf, para uma pasta separada.
    9. Feche a consola do SQLCMD, e abra uma nova, porém indicando o parâmetro -i com a path do ficheito “instmsdb.sql” que se encontra na pasta “E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLInstall” (esse o path no meu caso). O comando completo, no meu caso, ficaria:
    10. sqlcmd -E -i “E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLInstallinstmsdb.sql”

    11. Feche todas as janelas de Command Line – O serviço do SQL Server irá parar.
    12. Inicie o SQL Server da forma tradicional (no Services Manager ou Cluster Administrator).
    13. Voila! Agora você tem uma MSDB novinha em folha!!!

    Resumindo, a MSDB é mais uma DB de sistema, e é tão importante quanto a poderosa “master”… Tenha sempre em atenção que um plano de Backups completo é extremamente necessário, incluindo as DBs de sistema, que como visto guarda informações que podem ser cruciais para o correcto funcionamento da sua instância.

    Outros posts da série “Bases de Dados de Sistema”:

    Bases de Dados de Sistema – A minha TEMPDB não inicializa, e agora?

    Na sequência do tema “Bases de dados de sistema”, veremos hoje um procedimento simples, mas que pode ajudar no caso de algum problema com a TEMPDB. Perder o disco, ou a mais comum de todas, alterar a localização da TEMPDB e definir um path inválido são situações que acontecem e, principalmente, em servidores produtivos a resolução deve ser rápida.

    Seguem os passos para recuperar a TEMPDB que, como vimos anteriormente, é recriada a cada restart do SQL Server:

    1. Tomar nota do path onde ficam os binários do SQL Server, como por exemplo:
      “E:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn”
    2. Abra a consola do Windows (CTRL-R, escreva cmd e clique em “Ok”).
    3. Execute o seguinte comando (altere a path dos binários para a referida no ponto 1):

      cd “E:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn”

    4. Inicialize o SQL Server em “single user”, com as configurações mínimas (é óbvio que o serviço do SQL Server deverá estar parado.):

      sqlservr -f -m

    5. Abra outra consola do Windows (CTRL-R, escreva cmd e clique em “Ok”).
    6. Entre no utilitário SQLCMD (Obs.: Se a localização SQLCMD não estiver nas variáveis de sistema, é necessário alterar a localização da CMD para a pasta onde o executável está guardado – por exemplo: E:Program FilesMicrosoft SQL ServerClient SDKODBC110ToolsBinn):

      sqlcmd

    7. Dentro do SQLCMD execute os seguintes comandos, alterando o valor de FILENAME para a localização dos ficheiros da tempdb:

      use master
      go
      ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME=’F:MSSQLDATATempdb.MDF’)
      go
      ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME=’L:MSSQLDATATemplog.LDF’)
      go

    8. Feche o SQLCMD:

      quit

    9. Feche as duas consolas.
    10. Inicie o SQL Server da forma tradicional.

    Se todos os passos forem seguidos, ao se inicializar o SQL Server, os ficheiros da TEMPDB serão criados na localização especificada e os problemas com a TEMPDB serão resolvidos :)

    Por hoje é isso, até a próxima!!

    Outros posts da série “Bases de Dados de Sistema”:

    Bases de Dados de Sistema – Introdução

    No SQL Server temos dois tipos de bases de dados: de sistema e “user defined”. Antes do SQL Server 2005, as bases de dados de sistema eram visivelmente misturadas com as outras bases de dados, no “Enterprise Manager”. Hoje em dia, no SQL Server Management Studio, as BDs de sistema ficam isoladas em uma pasta chamada “System Databases”. Por esta razão, muitos dos novos profissionais de bases de dados não têm a muita curiosidade em saber a função destas BDs.
    Neste post irei descrever as funções principais de cada uma destas curiosas BDs, que já vêm de raiz em uma instância de SQL Server.

    Elas são: master, tempdb, model e msdb… As estrelas do post de hoje!

    • A base de dados master guarda todos os registos a nível do sistema. Nesta base de dados estão guardadas informações sobre logins e todas as definições de sistema, como por exemplo a informação para inicialização do SQL Server. A BD master, também regista a existência de todas as outras bases de dados e a localização de seus ficheiros. Ou seja, sem a master não há milagres, faça sempre backup desta BD (na verdade de todas…).
    • Falaremos agora sobre a famosa tempdb. Sabemos que esta é um ponto comum no âmbito da instância. Também conhecida como a “casa de banho pública” do SQl Server, pois é acedida por todas as bases de dados com necessidades.
      Recriada a cada reinicialização do SQL Server, de uma forma limpa, a tempdb, mais precisamente, guarda objectos temporários como stored procedures, tabelas (tabém globais), variáveis e cursores, além de ser utilizada pelo SQL Server na criação de tabelas necessárias para desempenhar certas funções específicas – as chamadas work tables, que desempenham funções de sort, por exemplo. Todos estes objectos temporários são removidos ao fechar da conexão que os originou. Além do referido, a tempdb ainda tem a função de guardar dados relativos ao “row versioning” e também dados provenientes de operações online sobre índices…. Ufa! Acho que dei motivos suficientes para esta “casa de banho pública” ser bem tratada. Desta forma, siga as boas práticas para sua configuração, como criação de múltiplos ficheiros e armazenamento em um disco rápido e dedicado.
    • A base de dados model, como o seu próprio nome sugere, é um modelo utilizado para criação de outras bases de dados. De uma forma geral, sempre que uma nova base de dados é criada, uma cópia da model é feita, sendo as páginas seguintes preenchidas com o valor vazio. A BD model pode ser modificada, sempre tendo em atenção que tudo o que for alterado/criado, será replicado para novas BDs. Vimos no ponto anterior que a tempdbé criada a cada reinicialização do SQL Server, e obviamente, a BD model serve de base para a sua criação – com algumas nuances.
    • Finalmente chegamos a msdb! Está base de daos é utilizada pelo SQL Server Agent, e tema função de guardar informações relativas a agendamentos, alertas, operadores e jobs. O Service Broker e o Database Mail também trabalham com a msdb, assim como todos os eventos de backups realizados na inctância. A msdb, por defeito, é criada em simple recovery model, porém pela sua grande importância, a recomendação é alterar para FULL e, obviamente, incluir esta BD no plano de backups de T-LOG.

    Levando em conta o descrito acima, e suas dependências, o SQL Server inicia as bases de dados de sistema na seguinte ordem: master, model, msdb e tempdb.

    Existem ainda outras bases de dados importantes como a distribution e mssqlsystemresource. Iremos falar sobre elas numa próxima oportunidade.
    Hoje vimos as quatro principais bases de dados de sistema e suas funções principais, além de sua ordem de inicialização. Vamos seguir nas próximas semanas com mais alguns posts sobre este tema… Mantenha-se ligado ;)

    Outros posts da série “Bases de Dados de Sistema”:

    Como funciona o CHECKPOINT?

    O que é checkpoint?

    O checkpoint é o ponto a partir do qual o Database Engine começa a aplicar as alterações existentes no T-Log, durante um processo de recovery – em um crash, por exemplo.

    O SQL Server, por questões de performance, efectua alterações a nível de memória, alterando páginas do buffer cache, o que é muito mais eficiente do que escrever cada alteração no disco. As páginas modificadas são conhecidas como “dirty pages” e serão escritas em disco no momento do checkpoint - que é executado periodicamente.

    Quando a execução de um checkpoint é feita?

    Existem algumas possibilidades, que se seguem:

    • Automático: O checkpoint é executado sempre que o Database Engine detecta que o limite configurado para o “recovery time” será atingido. O “recovery time” poderá ser configurado da seguinte forma:

      EXEC sp_configure ‘recovery interval’, ‘TEMPO_MAXIMO_DE_RECOVERY_SEGUNDOS’

    • Indirecto: Novidade no SQL Server 2012, o checkpoint indirecto funciona da mesma forma que o automático, a diferença é que este pode ser configurado de forma individual para cada BD, em contraste com a outra forma que é uma configuração a nível global da instância. Esta opção pode ser configurada de forma gráfica, nas propriedades da BD, ou utilizando o seguinte comando:

      ALTER DATABASE [NOME_DA_BD] SET TARGET_RECOVERY_TIME = [TEMPO_MAXIMO_DE_RECOVERY] { SECONDS | MINUTES }

    • Manual: Por analogia todos chegamos lá. Este modo acontece ao se executar o seguinte comando, que “força” o checkpoint (o parâmetro é opcional):

      CHECKPOINT [TEMPO_MAXIMO_EM_SEGUNDOS]

    • Interno: Neste modo, o checkpoint será executado como reacção a várias operações, como a execução de um BACKUP ou de um SNAPSHOT, de forma a manter a consistencia ente o estado actual do T-Log e da imagem em disco.

    O que pode afectar o tempo da operação de checkpoint?

    O tempo de duração de um checkpoint é directamente proporcional ao número de dirty pages: quanto mais dirty pages, mais tempo a operação irá durar.
    Desta forma, sistemas com elevado grau de escritas terão uma frequência de execução de checkpoint maior, pois o SQL Server tentará optimizar a duração de sua execução, sendo que, quanto mais frequente a sua execução, menos páginas terão de ser escritas – na teoria.

    UPDATE: Conforme sugerido pelo colega André Rodrigues, as seguintes trace flags permitem recolher informação sobre a actividade do checkpoint:

    • 3502: Escreve no ERRORLOG quando um checkpoint começa e termina. 
    • 3504: Escreve no ERRORLOG o que o checkpoint esta escrevendo em disco. 

    Mais informações: http://goo.gl/bAF8A

    Impacto na Performance

    Como é óbvio, o processo de checkpoint consome recursos – e isso é inevitável! O importante é perceber a forma como serão alocados recursos a cada execução.
    De maneira geral a “regra” é a seguinte: Quanto menor o tempo máximo de execução definido para o checkpoint, maior será o consumo de recursos por parte do SQL Server associados a este operação. A ideia é simples: Se tenho menos tempo do que antes para concluir uma tarefa de tamanho X, terei que ,e esforçar mais para cumprir os prazos. E é assim que o SQL Server pensa.

    Conclusão

    É possível controlar o tempo de recovery das bases de dados através da frequência de execução do checkpoint, porém temos que ter em tenção vários factores que podem influenciar negativamente o ambiente. Um equilíbrio será necessário para atingir o ponto óptimo. De qualquer maneira, para maioria das bases de dados existentes a configuração por defeito é suficiente.

    Até a próxima!

    Paralelismo – Parte 5 – Final: Ultrapassando Problemas

    Nos primeiros posts desta série, falamos sobre o “Max Degree of Parallelism”, que pode ser definido a nível de instância ou a nível da query - com a hint MAXDOP, e sobre o “Cost Threshold For Parallelism”. Além destas duas opções, existe a possibilidade de controlar quais processadores lógicos serão utilizados pelo SQL Server, definindo o número de processadores disponíveis, o que irá afectar o paralelismo. Esta opção é a “Affinity Mask”.

    Antes de mais nada, vamos introduzir mais alguma coisa sobre paralelismo…Hoje em dia, com servidores cheios de CPU (o que tende a diminuir com o novo modelo de licenças), problemas com o paralelismo são comuns. O valor do “Max Degree of Parallelism”, que por defeito é 0, geralmente não é suficiente para atender a demanda de sistemas que funcionam com um alto grau de concorrencia. O acesso ao CPU passa a ser custoso, gerando um por vezes uma situação aonde o custo para execução da tarefa principal da query é inferior ao custo do sincronismo do paralelismo – o que é chamado de “Trashing”.

    O ideal é encontrar um valor óptimo para o “Max Degree of Parallelism”. Existem, pela Internet afora, vários artigos tratando deste assunto, porém o que eu verifico no dia-a-dia, são dois tipos de abordagem por parte dos DBAs:

    • Abordagem “drástica”:
      Definir o MaxDOP para 1, ou seja, acabar com o paralelismo. Atenção que esta configuração é uma boa prática para algumas aplicações em específico.
    • Abordagem “conservadora”:
      Simplesmente ignorar que este parâmetro existe, o que em alguns casos é justificável, pois a instância pode estar a funcionar perfeitamente com pouca carga. Chamo esta abordagem de conservadora, pois do outro lado da moeda, existem DBAs que preferem deixar as definições como vêm “por defeito”. A justificativa é: “Se veio assim, é porque a Microsoft sabe o que está a fazer”. Bem, se a definição fosse perfeita, provavelmente não daria para alterar :)

    Referi que já existem vários artigos abordando o melhor valor para o “Max Degree of Parallelism”, mas de qualquer forma deixo aqui a abordagem que adoptei, que é de certa forma flexível – nem drástica, nem conservadora.

    • Se o servidor tem um número de CPUs menor ou igual a 8: MaxDOP = (Número de CPUs)/2
    • Se o servidor tem mais do que 8 CPUs:
      1. MaxDOP=8 como baseline.
      2. Monitorizar o estado dos processos após a alteração.
      3. Se estiver bem, adicionar mais 4 ao valor actual do MaxDOP.
      4. Repetir o ponto 2 e 3 até encontrar um valor óptimo.
    • Se nada disso resolver, considere verificar e/ou alterar o valor do “Cost Threshold for parallelism”.

    Existem formas de passar por cima do limite definido no MaxDOP!
    Temos que ter em mente que, além da já referida “Affinity Mask” afectar o comportamento do MaxDOP, as definições a nível do “Resource Governor” e a HINT MAXDOP ultrapassam o limite definido na opção “Max Degree Of Parallelism”.

    O que justificaria tanto trabalho alterando definições?
    Pois é, temos aqui mais um factor! Antes de alterarmos opções a nível da instância, temos que lembrar que todas as BDs e todos os processos irão ser afectados! Este não é apenas um problema para instâncias que hospedam várias bases de dados, pois mesmo em um universo reduzido de uma ou duas bases de dados não faria o menor sentido alterar uma definição global por cause de uma ou duas queries com problemas, certo?

    O resumo de tudo é: Não altere as definições da instância, a não ser que o problema seja global ou que, em caso de aplicações de código fechado, não seja possível alterar as queries, stored procedures, adicionar índices, etc…

    O que fazer então?
    A resposta foi dada acima. A abordagem deve ser feita de um universo reduzido para um universo maior, no nosso caso, de uma simples query até o nível da instância. Então a recomendação é a seguinte:

    1. Identificar as queries com problema e trabalhar uma forma de melhorar a performance, mesmo que seja re-escrevendo toda a query.
    2. Verificar se a manutenção a nível de instância está sendo feita da melhor forma (actualização das estatísticas, defrag e rebuild de índices).
    3. Verificar se o servidor, no geral, não tem problemas de I/O, isso inclui não apenas problemas de hardware, mas também o número de discos dedicados ao SQL Server, a distribuição de ficheiros, etc.
    4. Se o problema ainda não tiver sido resolvido, ajustar a query com um valor óptimo de paralelismo, utilizando a HINT MAXDOP. Assim, somente a tal query irá ser afectada.
    5. Definir regras utilizando o “Resource Governor”, limitando o MaxDOP de uma forma mais global, mas mesmo assim num âmbito menor do que a nível de instância.
    6. Se ainda assim, o número de queries com problemas for muito grande, podemos elevar o detalhe de alteração ao nível da instância.

    Chegamos ao fim desta série!! Agradeço a quem acompanhou até o fim e me disponibilizo para responder a alguma dúvida!!

    Um Abraço a até a próxima (semana que vem :).

    Paralelismo – Parte 4: Planos de Execução

    Nos posts anteriores desta série, já tivemos a oportunidade de entender como funciona a opção “Max Degree of Parallelism (MAXDOP)”, a sua relação com a opção “Cost Threshold for Parallelism” e clarificamos como o SQL Server se comporta – através da metodologia COOPERATIVE.

    Hoje vamos nos focar em planos de execução. Tendo como base os posts anteriores, já temos mais ou menos ideia de como o SQL Server decide se uma certa query deve ser executada em paralelo ou não.

    Vamos nos basear na no plano abaixo, no qual poderão verificar alguns dos pontos que serão descritos, mais a frente:

    Como sabemos que a query irá beneficiar de paralelismo?
    Como podem verificar na imagem acima existe um ícone redondo, com fundo amarelo e duas setas pretas no canto inferior direito de cada iterador.Isso significa que a operação deste iterador irá ser feita em paralelo. Note que podem existir combinações de iteradores paralelizados e não paralelizados em um mesmo plano de execução, sendo que alguns iteradores tem a capacidade de ser paralelizado e outros não tem. O Query Optimizer também pode definir que um certo ramo do plano é mais eficiente se for executado em série.

    Exchange Operators
    Em um plano em que o paralelismo é utilizado, iremos encontrar os chamados “Exchange Operators”, que são iteradores capazes de gerir o paralelismo.
    A função destes operadores é nada mais, nada menos, do que o controle do fluxo de dados numa execução paralelizada. No plano de execução, estes operadores aparecem marcados como “Parallelism”, porem existem três tipos distintos destes operadores, que são os seguintes:

    • Gather Streams: Também conhecido como “Start Parallelism”, recebe N conjuntos de linhas e devolve apenas um conjunto contemplando todos os que recebeu. N é um valor igual ao DOP (Degree of Parallelism).


    • Distribute Streams: Basicamente é o inverso do anterior, pois recebe um único conjunto de linhas e devolve o mesmo conjunto dividido em vários pedaços. Por isso, também é conhecido como “Stop Parallelism”. N é um valor igual ao DOP. 

    • Repartition Streams: Este iterador recebe múltiplos conjuntos de linhas e devolve também múltiplos conjuntos de linha, porém sua função principal é a re-distribuição destes conjuntos em diferentes threads. O número de inputs e outputs é equivalente ao DOP.

    Producer e Consumer
    Como vimos, os Exchange Operators podem ser encarados como operadores de dupla função, pois na verdade têm dois papéis, descritos como Produtor (Producer) e Consumidor (Consumer). O produtor lê as linhas (input) de um certo ramo, transforma estas linhas em pacotes e então direcciona estes pacotes para o Consumidor. O consumidor, por sua vez, recebe este pacotes, extrai as linhas e retorna as mesmas para o iterador que está um nível acima (iterador pai). Cada Exchange operator tem um número de Produtores e Consumidores equivalente ao DOP.

    Partitioning Types
    Vistos estes conceitos, nós podemos então entender melhor os “Partitioning Types”, que são mais uma classificação a se adicionar aos Exchange Operators. Existem cinco tipos diferentes, que se diferenciam pela forma como as linhas transitam, internamente a um Exchange Operator, de um Produtor para um Consumidor. A seguir temos uma explicação mais detalhada de cada um:

    • Broadcast: Basicamente envia todas as linhas para todas os Consumidores.
    • Round Robin: Envia os pacotes de forma sequencial para os Consumidores.
    • Hash: Através de um função Hash, é definido para onde cada linha será enviada
    • Range: Avalia atraves de uma função range, para onde cada linha deve ser enviada. A sua utilização é mais constante e planos de criação de índices. 
    • Demand: Este é o único tipo que utiliza uma abordagem Pull para o fluxo de dados, pois envia as linhas para o primeiro consumidor que requisita.

    Ordenação (Merging e Non-Merging)
    Para terminar, existe uma terceira forma de classificar estes operadores, que tem relação com a ordenação. Assim sendo os operadores podem ser classificados como:

    • Merging: Neste caso os produtores devolvem as linhas de forma ordenada para os consumidores. Porém o função de ordenação não é feita neste operador. Este operador é utilizado apenas em Gather Streams e Repartiotion Streams, pois em Distributed Streams, só existe um produtor.
    • Non-Merging: Nesta situação, a ordem não é preservada.

    De modo geral, vimos como identificar um plano de execução de uma query que utiliza paralelismo e entramos em detalhes dos chamados “Exchange Operators”. Vimos que estes operadores são responsáveis pelo fluxo de conjuntos de linhas entre os iteradores do plano de execução, e que podem ser classificado tendo em conta o número de produtores e consumidores, a sua forma de gerir a ordenação e o seu “Partitioning Type”. No próximo post iremos verificar alguns detalhes de outros operadores.

    Paralelismo – Parte 3: Behind the stage

    Como já vimos anteriormente, no geral, queries com alto nível de I/O são candidatas a serem paralelizadas, o que irá fazer com que um processo seja dividido em várias threads, ou correctamente falando: WORKERS. Hoje iremos mergulhar mais na teoria do paralelismo, apresentando alguns conceitos e explicando como o SQL Server se comporta nos bastidores.

    Cada CPU no seu computador pode executar uma tarefa de cada vez, mas uma tarefa tem que ser finalizada antes de se passar para a próxima. Este sincronismo é definido pelo SCHEDULER, que basicamente define a o que terá tempo de CPU e quando o CPU estará disponível para esta tarefa.
    O Windows utiliza uma metodologia denominada PREEMPTION, que basicamente divide o tempo de CPU igualmente dentre as threads activas, executando de forma uniforme todas as threads, uma de cada vez, no tal tempo definido que tecnicamente se chama QUANTUM. Podemos introduzir aqui o conceito de CONTEXT SWITCH, ou mudança de contexto, que é nada mais nada menos do que a acção tomada pelo CPU no momento em que para de ceder tempo para uma determinada thread, e começa a ceder tempo de CPU para outra thread. O context switch tem seu custo e“ em momentos de grande carga, pode ser mais custoso do que a própria execução das threads em si (trashing).

    Num panorama em que existem threads divididas por prioridades, as threads com maior prioridade são capazes de forçar as de menor prioridade a ceder tempo de CPU, conseguindo assim terminar a sua execução de uma forma mais rápida.

    O SQL Server, como uma aplicação baseada no Windows, está sujeito a metodologia de PREEMPTION, porém em um sistema bem optimizado e dedicado para o SQL Sever, esta divisão de tempo de CPU não será sobreposta pelo modelo internamente utilizado pelo SQL Server, através do SQLOS – a metodologia COOPERATIVE. De uma forma geral, o modelo COOPERATIVE aposta que cada WORKER (o equivalente a uma thread no modelo PREEMPTION) estará em algum momento parado em busca de recursos, e neste momento é dada a chance para outros workers executarem suas tarefas. Em resumo, neste modelo, o tempo de CPU não é dividido. Cada worker tem disponível o tempo de CPU necessário para completar sua tarefa enquanto estiver activo.

    Juntando toda a informação:

    • O SQL Server está sujeito ao modelo PREEMPTION, ou seja, concorre por tempo de CPU com outras aplicações.
    • Cada CPU tem um SCHEDULER.
    • Internamente, o SQL Server atribui tempo de CPU a WORKERS que são atribuído a um SCHEDULER.
    • Um SCHEDULER pode conter vários WORKERS.
    • Apenas um WORKER poderá estar activo em cada CPU, num dado instante.

    Agora, aonde entra o paralelismo aqui? O SQL Server tem a habilidade de dividir certas queries em pequenos pedaços, que serão distribuídos pelos CPU lógicos disponíveis para utilização da instância, através de workers. Estes workers estarão sujeitos a motodologia COOPERATIVE, portanto estarão atribuídos ou não a SCHEDULERS, dependendo do seu wait type.

    No post de hoje, foi possível entender melhor como o SQL Server atribui os nossos processos ao CPU, e de que forma o paralelismo é feito. A informação passada é também útil para a percepção do motivo de certos WAIT TYPES.