About Microsof SQL Server

Compilação e recompilação.

Quando uma query é executada pela primeira vez o sql server executa uma serie de procedimentos,mas a grosso modo ele compila a consulta e gera um plano de execução otimizado e executa o comando.Esse plano de otimização que ele gerou ele deixa em uma área reservada chamada procedure cachê,o procedure cachê armazena todos os comandos que executamos no sql server.Se executarmos esse comando novamente o sql server primeiro checa se existe já esse plano otimizado no procedure cachê se existe ele re-utiliza o plano de excução gerado,diminuindo o uso da cpu,pois não é preciso executar novamente um plano de execução para a consulta.Segue abaixo alguns exemplos que força o sql server recompilar o seu comando gerando um custo maior para a CPU.

*Mudança na estrutura do objeto – Se o objeto referenciado(uma tabela) é mudado,causa uma recompilação nos objetos que se referenciam a ele,também se temos um comando que junta comandos DDL(create,alter,drop) e DML(Select,Insert,Update,Delete),isso vai gerar uma recompilação.

*Set option – Tem algumas opções que quando referenciada em criação de procedures gera uma recompilação do objeto toda vez,como ANSI_PADDINGS,ANSI_NULLS,ANSI_NULL e ARITHABORT,se usarmos essas opções durante uma batch,vai forçar uma recompilação.

*Atualização de estatística – como já era de se esperar se atualizarmos as estatísticas e essa informação for muito significante,o slq server ira recompilar sua batch,pois lembra como foi comentado o sql server é baseado em custo para otmizar,e a primeira coisa que ele verifica é a informação das estatísticas,mas toda vez que for atualizada vai recompilar?A resposta é NÃO,somente se tivermos uma mudança muito grande,por exemplo tenho um índice nonclustered que foi atualizado dia 1 de janeiro pela ultima vez e hoje(26 DE ABRIL) atualizo ele,mas somente 10 registros foram incluídos,isso é uma mudança drástica?NÃO,mas se no caso foram atualizados 100000 registros,no caso é uma mudança drástica,pois o otimizador de consulta terá que mudar a sua estratégia de otimização do plano de execução.

*E por fim como o próprio nome diz,a opção RECOMPILE especificada em uma stored procedure sempre recompilara o plano de execução toda vez que for executada,isso é ruim?Depende do seu código.Falo sempre para as pessoas,isso depende do ambiente e da query.

No sql server 2005 temos 3 maneiras de visualizar o gargalo causado por compilação e recompilação,uma é usando o System Monitor,a outra o profiler e por fim as na minha opinião FANTÁSTICAS DMV e DMF.

No system monitor podemos usar a SQLSERVER:SQL Statistics: Batch/Sec, SQLSERVER:SQL Statistics: SQL Compilations/Sec e SQLSERVER:SQL Statistics: Recompilations/Sec .

Para verificarmos se temos gargalo,basta fazer uma correlação do contador Batch/sec(Comandos enviados as seu servidor por segundo) com os contadores compilations e recompilations,se os contadores de compilação e recompilação estiver quase no mesmo nível do batch/sec estamos tendo um gargalo e precisa ser avaliado novamente o  seu ambiente usando o profiler e/ou DMV´s.

Abaixo esta uma query que mostra quanto tempo o sql server gasta para otimizar a consulta:

SELECT * from sys.dm_exec_query_optimizer_info

Where counter = 'optimizations' or counter = 'elapsed time'

Esses contadores são acumulativos desde que a instancia do sql server foi iniciada,o interessante é fazer “snapshot” desse comando comparando dias.O 'optimizations' representa o numero total de query/batch e o elapsed time represente o tempo tempo gasto em segundos para otimizar a consulta.

Também podemos verificar quais são as querys que mais geraram recompilação.

Select top 10 plan_generation_num,execution_count,

(select substring(text,statement_start_offset/2 + 1,(case when statement_end_offset = -1 then len(convert(nvarchar(max),text)) * 2

Else statement_end_offset

End  - statement_start_offset)/2)

From sys.dm_exec_sql_text(sql_handle)) as query_text

From sys.dm_exec_query_stats

Where plan_generation_num>1

Order by plan_generation_num desc

Também é possivel que ,se voce esta encontrando um grande numero de compilações,pode ser que o sql server esteja com gargalo de memória,pois ele pode esta tirando os plano de execução da memória.Um comando que eu estou procurando conhecer é o DBCC MemoryStatus que nos mostra como que esta ditribuida nossa memória no sql server nos componentes que ele usa,como o procedure cachê,buffer manager e etc...

Com isso finalizamos um dos gargalos que existe no caso na CPU,no próximo post estarei falando do gargalo de memória.

Abraços e até a próxima.

Antes de falar sobre o tipos de monitoramento antes,crie um BASELINE,o que seria um baseline?

Baseline é uma maneira de voce montar um “esquema” para monitorar a sua performance,os conceitos são simples e fáceis de aplicar,desde que voce conheça as ferramentas e como utiliza-las da melhor maneira possível.

Primeiro Crie um baseline para monitorar uma performance

Segundo Monitore seu ambiente baseado em seu baseline

Terceiro Detecte o problema,isole o problema  e resolva o problema de performance.

Existe muitas rasões para termos problemas de performance,como um índice apagado por acidente.

As ferramentas que podemos utilizar são:

- System Monitor – oferece contadores para monitorar CPU,Disco,Memória e recursos de rede,mas o que isso tem haver com o sql server?O sql server é integrado com o system monitor que oferece excelentes contadores como page life expectancy(Tempo em que as paginas de dados iram permanecer no cache),buffer cachê hit ratio(é a % de acerto que as paginas são buscadas no cache).Tome cuidado na hora de usar o System Monitor,pois  as vezes ele pode causar um overhead dependendo do numero de contadores que voce utilizar.

 - SQL Server Profiler – uma ferramente gráfica para monitorar a instancia do sql server.Voce pode usar o profiler para capturar os dados e salvar em 1 arquivo ou em uma tabela,o ideal é que voce gere em um arquivo,pois voce não gera concorrência no banco de preferência que esse arquivo não fique em nenhum disco usado pelo sql server.o profiler diferente do system monitor o profiler é baseado em evento.Em ambientes pesados é recomendado que use o server-side trace,é uma forma de monitoramente sem interagir com a interface gráfica do profiler,causando menos custo.

Eu vou escrever um artigo falando sobre o server-side trace,que inclusive eu uso para capturar os comandos entre um intervalo de horas,no meu caso das 14:00 as 15:00 da tarde.

 - DTA – Excelente ferramente que analiza os comandos captados pelo profiler e baseado no schema do banco,nos da grandes recomendações de criação de índice por exemplo.

 - Comandos DBCC – eu tenho escutado que esses comandos irão sumir em futuras versões do sql server,que serão substituídos pelas DMV e outros comandos,mas para eu isso é uma lenda..rsrs..Esses comando checam a consistência do banco de dados e nos fornece interesante informações como a fragmentação,statistica de um índice,também nos mostra se o nosso banco de dados esta corrompid o ou não,entre outras informações do banco de dados como DBCC MemoryStatus,DBCC sqlperf(logspace) que nos mostra a porcentagem ocupada no log de transações,de uma pesquisada no books online,são excelentes para monitorar.

 - DMV(Dynamic Management Views) and Functions(DMF) – Foram introduzidas nos sql server 2005 afim de ajudar a monitorar o desempenho,transações e etc..O sql server 2000 era uma caixa preta para monitorar,já no sql server 2005 teve uma grande mudança em questão de monitoramento,ao longo dos meus posts vocês vão se deparar com DIVERSAR DMV,mas para usa-las precisa-se na minha opinião enteder como o sql server funciona internamente como foi mencionado no post anterior.

Bom,já que temos em mente as ferramentas necessárias para montar nosso baseline,agora voce se pergunta,qual que vai ser o foco do meu baseline?Simples,vamos focar em 5 assuntos.

*CPU

*Memoria

*I/O

*Tempdb

*Blocking

No meu blog vou descrever cada uma delas,vamos partir da primeira.

Amanhã estarei falando sobre a CPU em 2 partes,a primeira será divulgada amanhã e a segunda na sexta-feira.

Abraços e até a próxima...