O que acontece quando se altera a configuração MAXDOP

Olá,
Um post rápido para evidenciar o que acontece quando nós alteramos a configuração de paralelismo do SQL Server.

A configuração a qual me refiro é a “Max Degree of Parallelism”, ou mais conhecida como MAXDOP.Esta configuração é utilizada para determinar quantos processadores serão utilizados para processar uma consulta quando esta necessitar de paralelização. Mais informações, acesse Degree of Parallelism

Para visualizar esta configuração, você pode executar a procedure sp_configure:

exec sp_configure

No entanto, com as configurações padrão, você deve ter um resultado como este:

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
allow updates                       0           1           0            0
backup compression default          0           1           0            0
clr enabled                         0           1           0            0
cross db ownership chaining         0           1           0            0
default language                    0           9999        0            0
filestream access level             0           2           0            0
max text repl size (B)              -1          2147483647  65536        65536
nested triggers                     0           1           1            1
remote access                       0           1           1            1
remote admin connections            0           1           0            0
remote login timeout (s)            0           2147483647  20           20
remote proc trans                   0           1           0            0
remote query timeout (s)            0           2147483647  600          600
server trigger recursion            0           1           1            1
show advanced options               0           1           0            0
user options                        0           32767       0            0

Para visualizar o valor do MAXDOP, você deve antes habilitar a visão de opções avançadas (show advanced options):

sp_configure 'show advanced options', 1
reconfigure

Com isso, todas as opções são visualizadas, conforme abaixo. Em destaque a configuração do MAXDOP.

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
access check cache bucket count     0           65536       0            0
access check cache quota            0           2147483647  0            0
Ad Hoc Distributed Queries          0           1           0            0
affinity I/O mask                   -2147483648 2147483647  0            0
affinity mask                       -2147483648 2147483647  0            0
affinity64 I/O mask                 -2147483648 2147483647  0            0
affinity64 mask                     -2147483648 2147483647  0            0
Agent XPs                           0           1           1            1
allow updates                       0           1           0            0
awe enabled                         0           1           0            0
backup compression default          0           1           0            0
blocked process threshold (s)       0           86400       0            0
c2 audit mode                       0           1           0            0
clr enabled                         0           1           0            0
common criteria compliance enabled  0           1           0            0
cost threshold for parallelism      0           32767       5            5
cross db ownership chaining         0           1           0            0
cursor threshold                    -1          2147483647  -1           -1
Database Mail XPs                   0           1           0            0
default full-text language          0           2147483647  1033         1033
default language                    0           9999        0            0
default trace enabled               0           1           1            1
disallow results from triggers      0           1           0            0
EKM provider enabled                0           1           0            0
filestream access level             0           2           0            0
fill factor (%)                     0           100         0            0
ft crawl bandwidth (max)            0           32767       100          100
ft crawl bandwidth (min)            0           32767       0            0
ft notify bandwidth (max)           0           32767       100          100
ft notify bandwidth (min)           0           32767       0            0
index create memory (KB)            704         2147483647  0            0
in-doubt xact resolution            0           2           0            0
lightweight pooling                 0           1           0            0
locks                               5000        2147483647  0            0
max degree of parallelism           0           1024        1            1
max full-text crawl range           0           256         4            4
max server memory (MB)              16          2147483647  26000        26000
max text repl size (B)              -1          2147483647  65536        65536
max worker threads                  128         32767       0            0
media retention                     0           365         0            0
min memory per query (KB)           512         2147483647  1024         1024
min server memory (MB)              0           2147483647  0            0
nested triggers                     0           1           1            1
network packet size (B)             512         32767       4096         4096
Ole Automation Procedures           0           1           0            0
open objects                        0           2147483647  0            0
optimize for ad hoc workloads       0           1           0            0
PH timeout (s)                      1           3600        60           60
precompute rank                     0           1           0            0
priority boost                      0           1           0            0
query governor cost limit           0           2147483647  0            0
query wait (s)                      -1          2147483647  -1           -1
recovery interval (min)             0           32767       0            0
remote access                       0           1           1            1
remote admin connections            0           1           0            0
remote login timeout (s)            0           2147483647  20           20
remote proc trans                   0           1           0            0
remote query timeout (s)            0           2147483647  600          600
Replication XPs                     0           1           0            0
scan for startup procs              0           1           0            0
server trigger recursion            0           1           1            1
set working set size                0           1           0            0
show advanced options               0           1           1            1
SMO and DMO XPs                     0           1           1            1
SQL Mail XPs                        0           1           0            0
transform noise words               0           1           0            0
two digit year cutoff               1753        9999        2049         2049
user connections                    0           32767       0            0
user options                        0           32767       0            0
xp_cmdshell                         0           1           0            0

Um efeito que alguns talvez desconheçam é o que acontece por trás dos bastidores quando esta configuração é alterada. Toda vez que o MAXDOP é alterado o cache do SQL é zerado. Como?!

Isso mesmo, o cache de procedures, queries e outros objetos compilados e zerado, fazendo com que todos os objetos tenham de ser recompilados novamente.

Como podemos comprovar isso? Dando uma olhada no errorlog. O resultado é este:

2012-09-27 15:57:59.320 spid80       Configuration option 'max degree of parallelism' changed from 1 to 0. Run the RECONFIGURE statement to install.
...
2012-09-27 15:57:59.360 spid80       SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-09-27 15:58:01.970 spid80       SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-09-27 15:58:01.980 spid80       SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

O SQL Server registrou ocorrências de “flush” das áreas de cache ‘Object Plans’, ‘SQL Plans’ e ‘Bound Trees’, que são as áreas onde são armazenados os planos de execução das consultas, procedures etc.

Obs: estou sendo bem simplista aqui ao descrever estas áreas de cache. Para mais informações vale a pena dar uma olhada na DMV sys.dm_os_memory_cache_counters , nos tipos para SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) e Bound Trees (CACHESTORE_PHDR).

Portanto, fica a dica. Caso você tenha que alterar esta configuração em um ambiente de produção, saiba que você terá consequências. Sabendo disso, você evitará uma análise equivocada do seu ambiente após alteração desta configuração.

 

Bons Estudos!
Erickson Ricci

Uma resposta para “O que acontece quando se altera a configuração MAXDOP

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s