Database States e suas interações

Olá,

O estado do banco de dados (do inglês, state) vai definir algumas de suas características, tais como acessibilidade e disponibilidade dos dados. Todo banco de dados SQL Server está em um determinado estado. No SQL Server, um banco de dados pode assumir 7 estados diferentes, sendo eles:

  • ONLINE
  • OFFLINE
  • RESTORING
  • RECOVERING
  • RECOVERY PENDING
  • SUSPECT
  • EMERGENCY

A descrição de cada um destes estados pode ser encontrada neste link (http://technet.microsoft.com/en-us/library/ms190442.aspx) do BOL (Books Online para quem está se habituando agora com estes termos – em outras palavras, é o Help do SQL Server).

O ponto que quero destacar aqui é como os estados se um banco de dados interagem. A troca de um estado pode ocorrer de duas formas: através de ação manual por parte do usuário ou automaticamente através de uma ação do próprio SQL Server.

Por exemplo, o DBA pode a qualquer momento alterar o estado de um banco de dados para OFFLINE, procedimento realizado manualmente. Por outro lado, no momento que essa base for alterada para ONLINE, o SQL Server a altera para um estado intermediário (RECOVERING), onde o processo de recovery (ANALYSIS-REDO-UNDO, tópico para outro post) é realizado. Se este processo for realizado com sucesso e o SQL Server não detectar nenhum erro, o SQL Server automaticamente altera o estado da base para ONLINE.

A importância de se conhecer a interação entre estes estados está no fato de auxiliar o DBA a saber a origem daquele estado e talvez lhe ajudar a identificar algum tipo de problema.

Um simples consulta pode retornar o estado atual de um banco de dados:

Consulta na sys.databases

Consulta na sys.databases

 

A imagem abaixo lista todas as possíveis interações entre os estados de um banco de dados.

Interações entre os estados do banco de dados

Interações entre os estados do banco de dados

 

Bons estudos!

Erickson Ricci

Como configurar a quantidade de arquivos do errorlog

Olá,

Por padrão o SQL Server mantém apenas 6 arquivos históricos do errorlog. Se o SQL Server for reiniciado será gerado um arquivo de errorlog novo e o último arquivo (mais antigo) será descartado.

Há uma maneira simples de configurar esta quantidade de arquivos. Através do Management Studio, vá até a pasta Management, SQL Server Logs, e clique com o botão direito na pasta. Acesse o menu Configure. (Veja imagem abaixo)

image1A tela a seguir será apresentada:

image2Nesta tela você poderá alterar a quantidade de arquivos de errorlog que o SQL Server irá manter.

Lembre-se que, se você quiser, você pode “reciclar” o atual arquivo do errorlog executando manualmente a procedure sp_cycle_errorlog.

 

Bons Estudos!
Até mais.

Erickson Ricci

Entenda o erro 18056 – The client was enable to reuse a session with SPID ##

Olá,

Todo DBA já deve ter visto alguma vez na vida o erro 18056. E se viu, provavelmente teve dificuldades para entendê-lo e tratá-lo. Por conta de todas as dúvidas e mal-entendimentos sobre este erro, alguns engenheiros de escalação da Microsoft fizeram alguns posts para esclarecer todas estas dúvidas.

Deixarei aqui a referência de todos os artigos relacionados a este erro.

How It Works: Error 18056 – The client was unable to reuse a session with SPID ##, which had been reset for connection pooling

How It Works: Error 18056 – The client was unable to reuse a session – Part 2

Error 18056 can be unwanted noise in certain scenarios

Breaking Down 18065

Todos estes artigos ( e todos os outros do blog ) são altamente recomendados. Leitura essencial para um bom DBA.

 

Bons estudos! Enjoy!!!
Erickson Ricci

Gargalo de memória direto das trincheiras

Olá,

 

Mais um caso de situação de problema direto das trincheiras do mundo corporativo.

O problema desta vez é contenção de memória. Todo o cenário, praticamente exatamente igual ao que presenciei, foi muito bem descrito em um artigo do Graham Kent. Veja em SQL Server Compilation Bottlenecks – Error: 8628, Severity: 17, State: 0. – Part 1

O que mudou no meu cenário:

  • CPU ficava em 100% somente quando as requisições chegavam no banco de dados (wait SOS_SCHEDULER_YIELD)
  • Gerou time-out de otimização de query (veja mais info abaixo), todas as requisições ficavam com wait RESOURCE_SEMAPHORE_QUERY_COMPILE
  • Meu gargalo foi em Medium Gateway, com 89 requisições em espera (Waiters) – Veja MemoryStatus

A mensagem de erro gerada pelo errorLog é:

2012-10-18 11:20:06.220 spid61       Error: 8628, Severity: 17, State: 1.
2012-10-18 11:20:06.220 spid61       A time out occurred while waiting to optimize the query. Rerun the query.

Outras referências:

How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005

 

Bons Estudos. []’s

 

Non-yielding IOCP Listener

Olá,

Essa semana tive de realizar uma análise de performance em um de nossos servidores e me deparei com um Dump gerado pelo SQL com a seguinte descrição:

2012-09-18 00:21:43.110 Server       **Dump thread - spid = 0, EC = 0x0000000000000000
2012-09-18 00:21:43.130 Server       ***Stack Dump being sent to H:\MSSQL10.xxxxxxxxx\MSSQL\LOG\SQLDump0002.txt
2012-09-18 00:21:43.130 Server       * *******************************************************************************
2012-09-18 00:21:43.130 Server       *
2012-09-18 00:21:43.130 Server       * BEGIN STACK DUMP:
2012-09-18 00:21:43.130 Server       *   09/18/12 00:21:43 spid 9672
2012-09-18 00:21:43.130 Server       *
2012-09-18 00:21:43.130 Server       * Non-yielding IOCP Listener
2012-09-18 00:21:43.130 Server       *
2012-09-18 00:21:43.130 Server       * *******************************************************************************
2012-09-18 00:21:43.130 Server       * -------------------------------------------------------------------------------
2012-09-18 00:21:43.130 Server       * Short Stack Dump
2012-09-18 00:21:43.190 Server       Stack Signature for the dump is 0x0000000000000204
2012-09-18 00:21:46.460 Server       External dump process return code 0x20000001. External dump process returned no errors.

Seguido do Dump, foram identificados alguns erros como os abaixo:

2012-09-18 00:21:46.470 Server       IO Completion Listener (0x25f8) Worker 0x0000000007B8E1A0 appears to be non-yielding on Node 3. Approx CPU Used: kernel 0 ms, user 0 ms, Interval: 15048.
2012-09-19 00:07:26.230 Server       IO Completion Listener (0x25f8) Worker 0x0000000007B8E1A0 appears to be non-yielding on Node 3. Approx CPU Used: kernel 0 ms, user 0 ms, Interval: 15001.
2012-09-19 00:08:18.170 Server       IO Completion Listener (0x25f8) Worker 0x0000000007B8E1A0 appears to be non-yielding on Node 3. Approx CPU Used: kernel 0 ms, user 0 ms, Interval: 25002.

Depois de coletar dados através do Perfmon, aparentemente não havia problemas de consumo de CPU, ou memória… Pesquisando na internet cheguei ao KB2711549 que descreve o mesmo cenário que eu passei.

Este é um Cumulative Update para o SQL Server 2008 e SQL Server 2008 R2. No caso do SQL Server 2008 é o CU7 do SP. Para SQL Server 2008 R2, CU7 para o SP1.

Em breve irei aplicar este CU no ambiente e monitorar novamente para validar que o problema foi resolvido. Fica dica!

 

Bons Estudos!
Erickson Ricci

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

Conhecendo a ferramenta Relog

Olá Pessoal,

No embalo do post anterior, onde falei da ferramenta Logman, vou aproveitar para falar também de outra ferramenta que podemos trabalhar em conjunto com a Logman que é a Relog. Com esta ferramenta você pode manipular os arquivos coletados com o Logman, gerando novos arquivos com períodos diferentes de coleta, unir vários arquivos em um só ou “quebrar” um grande arquivo em vários menores e por ai vai. Vamos ver como fazer isso.

A sintaxe completa com as descrições dos parâmetros do comando Relog segue abaixo:

Microsoft r Relog.exe (6.1.7601.17514)

Relog creates new performance logs from data in existing performance logs by
changing the sampling rate and/or converting the file format. Supports all
performance log formats, including Windows NT 4.0 compressed logs.

Usage:
relog <filename [filename …]> [options]

Parameters:
  <filename [filename …]>     Performance file to relog.

Options:
  -?                            Displays context sensitive help.
  -a                            Append output to the existing binary file.
  -c <path [path …]>          Counters to filter from the input log.
  -cf <filename>                File listing performance counters to filter
                                from the input log. Default is all counters
                                in the original log file.
  -f <CSV|TSV|BIN|SQL>          Output file format.
  -t <value>                    Only write every nth record into the output
                                file. Default is to write every record.
  -o                            Output file path or SQL database.
  -b <dd/MM/yyyy HH:mm:ss>      Begin time for the first record to write
                                into the output file.
  -e <dd/MM/yyyy HH:mm:ss>      End time for the last record to write into
                                the output file.
  -config <filename>            Settings file containing command options.
  -q                            List performance counters in the input file.
  -y                            Answer yes to all questions without prompting.

Na rotina de monitoramento que estou criando, eu quero carregas os dados coletados para dentro do SQL Server, e para isso vou utilizar a seguinte sintaxe:

relog “c:\PerfLogs\MyDataCollector_000001.blg” -f SQL -o SQL:DSN_SQL!SRVPROD

Com esta instrução estou informando que desejo que a saída do meu arquivo seja o SQL Server. Para me conectar a uma instância específica, temos antes de criar uma DSN no servidor que estamos trabalhando. No meu exemplo, minha DSN é a DSN_SQL e o servidor que estou trabalhando é o SRVPROD. Com isso, faremos a carga dos dados coletados no arquivo MyDataCollector_000001.blg para o SQL Server.

Quando os dados são carregados para o SQL Server são criadas 3 tabelas no banco de dados que você especificou para conexão na DSN. As tabelas são:

  • dbo.DisplayToID
  • dbo.CounterData
  • dbo.CounterDetails

A tabela DisplayToID possui informações da coleta como um todo como por exemplo servidor que foram coletadas as informações e início e fim da coleta. Já a tabela CounterDetails possui informações de cada contador utilizado na coleta como nome do objeto, nome do contador, instância do contador e assim por diante. Por fim, a tabela CounterData possui os dados efetivamente da coleta.

Com as informações destas três tabelas você consegue responder quando a coleta foi realizada, quais contadores, com detalhes, foram utilizados e quais os valores coletados. A cada coleta realizada é gerado um novo registro na tabela DisplayToID, com um novo código sequencial. Se novos contadores forem utilizados, estes são adicionados à tabela de detalhes, caso contrário, somente os dados de coleta são adicionados na tabela de dados.

Acho que por enquanto é isso. Boas coletas!

 

[]’s

Erickson Ricci