SQL Server 2008: Declarative Management Framework (DMF) Parte 2

No ultimo post (http://sqlserver-brasil.blogspot.com/2008/01/sql-server-2008-declarative-management.html) escrevi sobre os principais objetos que compõe o DMF, neste post vou mostrar como criar uma regra para evitar o uso do prefixo SYS para criar tabela e SP_ e XP_ para criar Stored Procedure.

Criando uma Condition
O primeiro passo é criar uma Condition que irá definir as regras utilizando uma Facet. Para criar uma Condition basta abrir o Management Studio, abrir conexão com o servidor SQL Server 2008 (Relational Engine), expandir Management, clicar com o botão direito em Conditions e seleciona New Condition.

Vou utilizar a Facet Multipart Name que disponibiliza dois atributos: @Name e @Schema. Estes atributos se referem ao nome e schema de alguns objetos de banco de dados como tabelas, stored procedure, sinônimo, funções, etc. Observe na imagem abaixo que utilizei a propriedade @Name com o operador NOT LIKE, comparando com ‘sys%’, ‘sp_%’ e ‘xp_%’.

Criando uma Policy
O próximo passo é criar uma Policy utilizando a Condition criada acima, definindo o modo como as regras será implantadas. Para criar uma Policy basta clicar como o botão direito na pasta Policy e selecionar New Policy.

Na janela New Policy definimos o nome em Name e selecionamos a Condition criada acima Landry Prefixos Proibidos. Em Against targets irá aparecer a lista de objetos que esta Condition se aplica, basta marcar StoredProcedure e Table. Você poderá clicar em Every e filtrar os objetos, por exemplo, aplicar a regra só em um banco de dados, ou apenas bancos de dados que comecem com ‘RH%’.

Em Execution Mode vamos escolher On Change – Prevent que utiliza Trigger DDL para aplicar a restrição no momento da criação do objeto. Existe ainda a possibilidade de aplicar a regra apenas a servidores (instâncias) específicos, como por exemplo, servidores 64bits.

Testando o Policy
Para testar a regra criada acima basta tentar criar uma tabela com prefixo SYS ou uma Stored Procedure iniciando com SP_ ou XP_, veja na figura abaixo a mensagem de erro.

Acredito que este recurso será muito utilizado no SQL Server 2008, principalmente para forçar a adoção de boas práticas na configuração e convenção de nomes de objetos.

Até o próximo post,
Landry.

SQL Server 2008: Declarative Management Framework (DMF) Parte 1

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

Declarative Management Framework é um sistema de regras (policy) aplicadas em um ou mais servidores SQL Server 2008. Podemos até comparar este recurso ao Group Policy do Windows!

O SQL Server Management Studio (SSMS) é usado para criar as regras e aplicá-las manualmente ou automaticamente utilizando Triggers DDL, Events Notification ou Jobs do SQL Server Agent.

Até o SQL Server 2005 para garantir com que todos os servidores da empresa obedeçam a uma configuração específica (Autoshrink OFF em todos os bancos em todas as instâncias), o administrador teria que entrar em cada servidor e verificar manualmente, ou escrever script e rodar em cada instância. Com o DMF podemos garantir um padrão de configuração em todas as instâncias facilmente.

Por exemplo, com DMF fica fácil garantir que qualquer tabela nova não tenha o prefixo SYS. Quando o usuário tentar criar uma tabela com prefixo SYS o sistema não deixa, veja abaixo:



Para criar estas regras você pode utilizar o SSMS ou um script, abaixo podemos observar um novo nó em Management:

Objetos do DMF
Todos os objetos que compõe o DMF ficam armazenados em tabelas de sistema no banco de dados MSDB. Para administrar é necessário pertencer ao role PolicyAdministratorRole localizado na MSDB.

► Facets
É o objeto básico do DMF, representando um objeto do SQL Server (como tabela, trigger, servidor, banco de dados, etc). Existe um total de 38 Facets contendo inúmeras propriedades utilizadas pelo próximo objeto Conditions. Não se pode criar novas Facets, apenas utilizar as fornecidas pelo sistema.

► Conditions
É um grupo composto de uma ou mais propriedades de um Facet, um operador de comparação e valor. Por exemplo, a propriedade Name do Facet Stored Procedure é do tipo string e pode utilizar os operadores de comparação =, !=, LIKE, NOT LIKE, IN ou NOT IN, sendo comparado a uma string ou uma lista de strings.

O SQL Server 2008 já vem com 69 Conditions que podemos utilizar e alterar livremente, além de criar novas. Na figura abaixo temos a Condition Windows Authentication Mode que utiliza a Facet Server Security e define a propriedade @LoginMode com o valor “Integrated”.

► Policy
Um Policy está associado a uma Condition que poderá ser aplicada a um ou mais servidores manualmente ou automaticamente. O modo de execução do Policy determina como a condição será aplicada:

• On demand – o administrador executa o Policie manualmente.
• On Schedule – verifica sob uma agenda e retorna um log com o resultado. Utiliza Job do SQL Server Agent.
• On Changes – verifica o Policie quando ocorre uma alteração na propriedade e gera um log (assíncrono). Utiliza Event Notification.
• Enforce – verifica no momento da alteração, não aceitando se não estiver de acordo com a Policy (síncrono). Utiliza Trigger DDL.

Existem Facets onde alguns modos de execução (listados acima) não estão disponíveis, para obter a lista dos modos de execução por Facet basta executar a query abaixo:

USE MSDB
go
WITH FacetCTE (ModoID, ModoNome)
AS
(SELECT * FROM (VALUES (0,'On demand'), (1, 'Enforce Compliance'),
(2,'Check on change and log'), (4,'Check on schedule and log')) AS EM(ModoID, ModoNome))

SELECT p.management_facet_id as FacetID, p.[name] as NomeFacet, f.ModoNome

FROM syspolicy_management_facets as p JOIN FacetCTE as f
ON p.[execution_mode] & f.[ModoID] = f.[ModoID]
ORDER BY p.[name],f.ModoNome

Todas as Facets possuem On Schedule, só aquelas Facets que as alterações podem ser capturadas por um evento possuem On change. As Facets com suporte transacional a instruções DDL podem utilizar Enforce.

► Policy Group
Agrupa Policies facilitando ao administrador no momento de associar a um banco ou instância. Cada Policy só poderá pertencer a um Policy Group.

No próximo post vou mostrar como é simples criar uma regra utilizando este novo recurso, até lá.

Novidades no SQL Server 2008

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

O SQL Server 2008 já está em fase final de desenvolvimento e quem quiser pode fazer o download e dar uma olhada nas novidades:
http://www.microsoft.com/downloads/details.aspx?FamilyId=3BF4C5CA-B905-4EBC-8901-1D4C1D1DA884&displaylang=en

Segue abaixo a relação de algumas novidades do CTP de Novembro do SQL Server 2008:

SQL Server Analysis Services (SSAS)
Novo Attribute Relationship designer
Melhorias no Dimension Wizard
Melhorias no Backup e Restore
Melhoria no desempenho
Melhora no algoritmo Microsoft Time Series

SQL Server Database Engine
Novos Query hints
Novos tipos de dados: hierárquico (hierarchyid), espacial, filestream, data, hora, etc.
Data Collector (coletor de dados de desempenho de vários servidores)
Melhorias no Database Mirroring
Declarative Management Framework (DMF)
Melhorias no T-SQL
Criptografia transparente para a aplicação
Compactação no Backup
Compactação dos dados
Integração com a linguagem LINQ (Microsoft Language Integrated Query)

Replicação
Melhorias na replicação Ponto-a-Ponto
Melhoria no Replication Monitor

Integration Services (SSIS)
Novos tipos de dados
Continua o suporte ao DTS do SQL Server 2000
Suporte a outras linguagens .NET para escrever scripts

Reporting Services (SSRS)
Novas fontes de dados
Report Model para Oracle
Integração com SharePoint

Nos próximos posts estarei mostrando em detalhes algumas destas novidades, aguardem.
Landry.

Missing Index (Índices Ausentes)

Neste post vou falar sobre um recurso novo no SQL Server 2005 chamado Missing Index.

Quando o otimizador elabora um plano de execução, ele analisa os melhores índices para uma condição de pesquisa. Se os melhores índices não forem encontrados o otimizador gera um plano que não seria o ideal, porém registra a ausência destes índices. O otimizador só gera informações sobre os índices ausentes, para queries com cláusula WHERE e que não foram resolvidas utilizando Plano Trivial (Trivial Plane).

Estas informações são mantidas até o SQL Server reiniciar. Nas versões RTM e SP1 são armazenados até 500 índices ausentes, alcançando o limite o otimizador para de registrar. No SP2, quando o limite de 500 é alcançado, o otimizador passa a apagar 20% dos índices menos relevantes para dar espaço a novos índices ausentes.

Cada indice ausente pertence a um grupo de índices ausentes, porém no SQL Server 2005 existe relação de 1-1 entre grupo de índices e índices. Em edições futuras a Microsoft pretende agrupar índices que resolveriam uma query, facilitando a análise de queries complexas que utilizam vários índices.

O SQL Server expõe as informações dos índices ausentes em 3 DMVs (Dynamic Views) e uma função:

► sys.dm_db_missing_index_details
Esta view retorna uma linha para cada índice que o otimizador não encontra ao elaborar o plano de execução de uma query. Ela retorna a lista de colunas que devem ser utilizadas como chave e Include.

► sys.dm_db_missing_index_group_stats
Atualizada a cada execução de query (e não a cada compilação), retornando informações consolidadas sobre grupos de índices ausentes (no SQL 2005 existe uma relação de 1-1 entre grupo de índice e índice).

► sys.dm_db_missing_index_groups
Relaciona cada índice em sys.dm_db_missing_index_details com um grupo de índices em sys.dm_db_missing_index_group_stats.

► dm_db_missing_index_columns
Função que retorna uma tabela com a lista de colunas (chave e Include) que compõe um índice ausente.

Para observar os índices ausentes vamos criar duas tabelas no banco de dados TEMPDB com o script abaixo:

USE tempdb
go
SELECT * INTO dbo.OrderHeader FROM Adventureworks.Sales.SalesOrderHeader;
SELECT * INTO dbo.Customers FROM Adventureworks.Sales.Customer;
go

Agora vamos executar uma query com JOIN utilizando as tabelas criadas no script anterior. Reparem que estas tabelas não possuem índices.

SELECT SalesOrderID, OrderDate, [Status], h.CustomerID, c.AccountNumber
FROM dbo.OrderHeader h JOIN dbo.Customers c ON h.CustomerID = c.CustomerID
WHERE c.TerritoryID = 2

Executando a instrução abaixo, utilizando a DMV sys.dm_db_missing_index_details, podemos observar os índices ausentes gerados pelo otimizador de consultas.

select index_handle, object_name(object_id) as 'Tabela',
equality_columns,inequality_columns,included_columns
from sys.dm_db_missing_index_details
where database_id = db_id('tempdb') and
[object_id] in (object_id('dbo.OrderHeader'),object_id('dbo.Customers'))

A função sys.dm_db_missing_index_columns retorna a relação de colunas de um índice ausente. Ela recebe como parâmetro do handle do índice obtido na coluna index_handle da DMV anterior.

SELECT * FROM sys.dm_db_missing_index_columns(11);

Implementando um JOIN entre as três DMVs obtemos mais informações, como a coluna user_seeks que retorna um contador incrementado a cada query executada que utilizaria o índice ausente.

SELECT d.*,s.* FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
where database_id = db_id('tempdb') and
[object_id] in (object_id('dbo.OrderHeader'),object_id('dbo.Customers'))

No próximo post vou escrever sobre como associar um índice ausente e uma query utilizando outro recurso novo do SQL Server 2005 que é plano de execução em XML.

Estrutura Interna do arquivo de Log - Parte 3

Parte 3 - "Virtual Log Files" (Logs Virtuais)

Um arquivo de LOG é dividido internamente em porções chamadas de "Virtual Log Files" (VLFs), onde seu tamanho é determinado pelo seu tamanho inicial e sua taxa de crescimento. Quando um arquivo de LOG é criado o SQL Server cria de 4 a 16 VLFs, passando a ser a unidade de crescimento e redução no tamanho.

Um VLFs pode estar em um de 4 estados possíveis:

1 - "Active"
A porção ativa do LOG começa no MinLSN (visto na Parte 1 do artigo) e termina no final lógico. Basta um VLFs conter uma pequena porção ativa do LOG para estar com o status "Active". Este VLFs nunca poderá ser truncado!

2 - "Recoverable"
Porção do LOG anterior a mais antiga transação em aberto, necessária penas para manter a sequência de transaçãoes em um processo de recuperação de dados através de Backup do Log.

3 - "Reusable"
Quando ocorre um Backup do Log ou "Checkpoit" (no "Recovery Model Simple"), os VLFs com Status de "Recoverable" passam para "Reusable", e poderão ser sobrescritos.

4 - "Unused"
Compreende um ou mais VLFs ao final do arquivo de LOG que ainda não receberam registros.

Vamos criar um banco de dados para analisar o seu arquivo de Log com a instrução DBCC LOGINFO.

create database DBLog
go

Os VLFs podem ser vistos com a instrução não documentada DBCC LOGINFO sem parâmetro, devendo ser executada no banco corrente da conexão. Executando DBCC LOGINFO no banco DBLog, sem alteração após a sua criação, teremos o resultado abaixo:

Cada linha representa um Log Virtual (VLFs), sendo que na coluna FileId temos o ID do arquivo de LOG que contém o VLFs (o banco DBLog possui um arquivo de LOG, por isso todos os VLFs possuem o mesmo valor em FileID).

A coluna StartOffset retorna a posição em bytes de inicio do VLFs dentro do arquivo de LOG; a coluna FileSize retorna o tamanho do VLFs em bytes. Reparem que existe uma região no início do arquivo de LOG que não apareceu no DBCC LOGINFO (de 0 a 8192 bytes), pois representa o Header (cabeçalho) do Arquivo. A primeira linha representa o primeiro VLF iniciando em 8192 bytes e finalizando em 262143 bytes (8192 + 253952).

Já a coluna FSeqNo indica a seqüência de utilização dos VLFs, no exemplo acima o último VLF utilizado foi o primeiro (FSeqNo = 21), o segundo VLF nunca foi utilizado pois permanece com zero. A coluna Status indica se o VLF está em uso (valor 2) ou disponível para uso (valor 0).



Um arquivo de Log com muitos VLFs gera lentidão no processo de recovery, sendo a quantidade de VLFs determinada em função do tamanho inicial do arquivo e a sua razão de crescimento. Por este motivo é indicado criar o arquivo de Log com o tamanho que se espera utilizar e definir o crescimento em unidades grandes, para gerar poucos VLFs.

Se o seu arquivo de Log já está com muitos VLFs, basta executar um SHRINK no arquivo de Log para acertar a casa!

Até o próximo post!
Landry.

Estrutura Interna do arquivo de Log - Parte 2

Parte 2 – Fases do “Restart Recovery”

No post anterior falei sobre a estrutura interna de um arquivo de Log (.LDF), além de uma pequena introdução ao “Recovery”. Neste post vou escrever sobre as fases internas do processo de “Restart Recovery”.

O processo de “Restart Recovery” possui 3 fases e analisa os registros no LOG a partir do MinLSN até o último registro, podendo até retornar e analizar registros anteriores ao LSN.

FASE 1: "Analysis"
Analisa os registros iniciando no MinLSN até o último registro. Nesta fase o SQL Server monta uma tabela com o endereço das páginas de dados alteradas (DPT - "Dirty Page Table") a partir dos registros no LOG. Além da DPT ele també cria uma tabela de transações ativas, aquelas que não possuem registros como COMMIT ou ROOLBACK.

FASE 2: "Redo"
Esta fase lê os registros no LOG a partir do LSN da mais antiga transação abreta encontrada na tabela gerada na fase anterior. O MinLSN no DPT representa o ponto inicial para o SQL Server implementar a atualização no arquivo de DADOS, contudo ele tem que retornar ao LSN da transação em aberto mais antiga para reter locks necessários para o processo. Este retorno a transação mais antiga para reter lock server para disponibilizar uma funcionalidade nova no SQL Server 2005 chamado de "Fast Recovery", que será esplicado mais afrente.

FASE 3: "Undo"
Já esta fase utiliza a lista de transações não finalizadas gerada na Fase 1, para implementar o ROOLBACK individual de cada transação, seguindo o identificador de transação de cada entrada no LOG (link entre os registros que possuem a mesma transação).

"Fast Recovery"
Durante a Fase 2 ("Redo") do "Restart Recovery" o SQL Server retornou até o LSN da última transação em aberto para reter locks nos objetos. Iste abordagem possibilita liberar o banco de dados para uso no início da Fase 3 ("Undo"), pois os objetos que estarão envolvidos no ROOLBACK ficarão bloqueados. Esta funcionalidade só está presente nas edições Enterprise e Developer.

No próximo post vou escrever sobre os Logs Virtuais, até lá!
Landry.

Estrutura Interna do arquivo de Log - Parte 1

Parte 1 - Introdução ao Log de Transações e "Recovery"

Neste primeiro post vou escrever sobre a estrutura interna do arquivo de Log (.LDF) além de uma pequena introdução a operação de Recovery.

Um arquivo de Log de Transações no SQL Server 2005 mantém registros das operações de atualização que ocorrem em um Banco de Dados. Cada banco de dados no SQL Server possui um ou mais arquivos de LOG (vou utilizar o termo LOG para fazer referências ao Log de Transações), sendo o mais comum utilizar um único arquivo. O SQL Server utiliza múltiplos arquivos de LOG em serial, um após o outro, por este motivo não existe ganho de performance no uso de arquivos de LOG em discos diferentes. O único motivo para se ter mais de um arquivo de LOG é por necessidades de espaço em disco.

Um componente do SQL Server chamado de "Buffer Manager" garante que a atualização do LOG ocorra antes da atualização do arquivo de dados ("write-ahead logging"). Quando uma conexão executa uma instrução de atualização, o SQL Server altera as páginas em memória e registra a operação no arquivo de LOG no disco, retornando mensagem indicando o sucesso da operação para a conexão. Posteriormente, de modo assíncrono, o arquivo de DADOS é atualizado por dois processos "Lazywriter" e "Checkpoints". Podemos concluir que na maior parte do tempo existe uma diferença nos dados que estão no arquivo de LOG e no arquivo de DADOS! A porção do arquivo de LOG que contém as informações que ainda não foram sincronizadas com o arquivo de DADOS é chamada de Porção Ativa do Log.

Cada registro em um arquivo de LOG recebe um número seqüencial chamado de "Log Sequence Number" (LSN). Ao final de cada "Checkpoint" o SQL Server registrar o valor do LSN que representa o último registro lido (MinLSN - "Minimum Log Sequence Number"). No próximo "Checkpoint" o SQL Server analisa apenas os registros do MinLSN até o fim.

Quando o SQL Server atualiza uma página de dados durante o "Lazywriter" ou o "Checkpoints", o "Header" (cabeçalho) da página é atualizado com o LSN correspondente ao registro no LOG que gerou a atualização. Desta maneira o SQL consegue determinar se um registro no LOG já foi aplicado ou não na página de dados, comparando o LSN do registro do LOG com o LSN do "Header" da página. Na Figura 1 a página está com o LSN 2:200, porém existe um registro no Transaction Log com LSN posterior (2:210) indicando a necessidade de aplicar o UPDATE definido no LOG na Página.

Se o Servidor SQL for desligado (queda de energia, por exemplo), os arquivos de DADOS e LOG estarão em diferentes estados. Quando o Servidor for reinicializado o SQL Server executa um processo chamado de "Restart Recovery" em cada banco de dados, verificando a existência de uma porção ativa do LOG e sincronizando os dados com o arquivo de DADOS.

No próximo post vou escrever com maior detalhe o processo de “Restart Recovery”, até lá!

Primeiro Post

Olá,

Bem vindo ao meu Blog sobre o Microsoft SQL Server. Meu objetivo neste blog é publicar alguns artigos de minha autoria sobre o Microsoft SQL Server, disponibilizando a toda comunidade de Administradores e Desenvolvedores em SQL Server.

Durante os meus estudos de SQL Server 2005 resolvi escrever alguns artigos como método de me aprofundar mais em alguns assuntos que considero importantes.

Em breve estarei escrevendo alguns posts sobre novidades da nova versão 2008!

A opinião de vocês é muito importante, fazendo críticas, sugestões de assuntos, etc. Por favor, se você for um ex-aluno deixe seu comentário!

Até breve,
Landry.