How to troubleshoot a slow running query in SQL Server

 “ I have a slow running query , what steps can I take to speed up the query and achieve an optimised execution plan?” .

Troubleshooting database server performance is an in-depth topic. A methodical  and repeatable approach solving the root cause is the perfect situation for any DBA.   There are different approaches to SQL Server Tuning. In reality , the root cause may not be a SQL query issue , it may be SAN  , SQL Server configuration issue or workload has suddenly increased.

But of course, you’re under pressure and the query is causing  delays for the end users.The steps below expand on earlier post :SQL Server Rapid Tuning, providing a very simple emergency approach.

The steps below are a straightforward approach to troubleshooting sub optimal SQL Execution Plans. Follow the steps and repeat as necessary.

Step 1)       Statistics – Is Auto Create and Update Statistics Enabled? If Auto Create statistics is disabled , this may indicate out of date statistics. If Disabled, than proceed to  Update Statistics – use sp_updatestats to update all statistics on the database. Run the query and check if any improvement.

Step 2)       If  Auto Create and Update Statistics is Enabled, Identify the longest running queriesor highest impact queries .( If the queries have high CPU usage go to step 6). Long duration and highest IO should be a priority.

Step 3)       Place every query in the SSMS and analyse the execution plan. First – check for tables or index scans.  If large table \ index scans are occurring – progress with Query Analysis.  The Query Analysis should ask questions such as : Are all JOINS valid ? Are the JOINS returning excessive data ? Search argument validity?   Functions in predicate? 

Step 4)       If no table or index scans exist and  the query is complex , for example a large transaction managing a booking process –  check for : excessive joins, temp tables, DDL changes,sub – queries , no set based approach to writing the queries. Review  the query ,  break it down into smaller parts or analyse the JOINS to invoke a new execution plan , ensuring a similar transaction integrity is retained

Step 5)       If the query is simple and no no index \table scans exists and executing in  SSMS responds with acceptable  performance - analyse the Application and how it processes the resultset.   Ask the right question a) are just relevant results returned?  Talk to the application developers.

Step  6)       If the query is no faster in SSMS , more complex query tuning is required,  research other methods or contact a performance tuning expert

Step 7)       If in Step  2 you identified queries  with high CPU usage , analyse in SSMS for Hash Joins, Sorts, Filters.  If any of these exists , progress with Query Analysis.

Step 8)       Repeat until the problem disappears.

Identify SQL Server Database Backup Version

How to Identify SQL Server Database Backup Version.
Following is the query:

RESTORE HEADERONLY FROM DISK='C:DatabaseBackupsDatabaseBackup.bak'

Specify the correct path of the backup file.
When this query is executed read the values of the columns  

SoftwareVersionMajor,SoftwareVersionMinor and SoftwareVersionBuild.
The version of the SQL Server backup can be found by concatinating the values in three columns.
version of backup = SoftwareVersionMajor.SoftwareVersionMinor.SoftwareVersionBuild
If the output is
SoftwareVersionMajor = 10
SoftwareVersionMinor = 50
SoftwareVersionBuild = 2500
Then the version of database backup is 10.50.2500
SoftwareVersionMajor also gives an idea of the major version of SQL Server.
It helps to determine if the database backup is of SQL Server 2005 or SQL Server 2008.

Identify Database Backup Version
SoftwareVersionMajor = 9	backup is of SQL Server 2005
SoftwareVersionMajor = 8	backup is of SQL Server 2000
SoftwareVersionMajor = 10 and SoftwareVersionMinor = 0	backup is of SQL Server 2008
SoftwareVersionMajor = 10 and SoftwareVersionMinor = 50	backup is of SQL Server 2008 R2

Data Mining na Prática: Algoritmo K-Means

A idéia do algoritmo K-Means (também chamado de K-Médias) é fornecer uma classificação de informações de acordo com os próprios dados. Esta classificação, como será vista a seguir, é baseada em análise e comparações entre os valores numéricos dos dados. Desta maneira, o algoritmo automaticamente vai fornecer uma classificação automática sem a necessidade de nenhuma supervisão humana, ou seja, sem nenhuma pré-classificação existente. Por causa desta característica, o K-Means é considerado como um algoritmo de mineração de dados não supervisionado.

Para entender como o algoritmo funciona, vamos imaginar que temos uma tabela com linhas e colunas que contêm os dados a serem classificados. Nesta tabela, cada coluna é chamada de dimensão e cada linha contém informações para cada dimensão, que também são chamadas de ocorrências ou pontos. Geralmente, trabalha-se com dados contínuos neste algoritmo, mas nada impede que dados discretos sejam utilizados, deste que eles sejam mapeados para valores numéricos correspondentes.

Como foi dito, o algoritmo vai analisar todos os dados desta tabela e criar classificações. Isto é, o algoritmo vai indicar uma classe (cluster) e vai dizer quais linhas pertencem a esta classe. O usuário deve fornecer ao algoritmo a quantidade de classes que ele deseja. Este número de classes que deve ser passada para o algoritmo é chamado de k e é daí que vem a primeira letra do algoritmo: K-Means.

Para gerar as classes e classificar as ocorrências, o algoritmo faz uma comparação entre cada valor de cada linha por meio da distância. Geralmente utiliza-se a distância euclidiana para calcular o quão ‘longe’ uma ocorrência está da outra. A maneira de calcular esta distância vai depender da quantidade de atributos da tabela fornecida. Após o cálculo das distâncias o algoritmo calcula centróides para cada uma das classes. Conforme o algoritmo vai iterando, o valor de cada centróide é refinado pela média dos valores de cada atributo de cada ocorrência que pertence a este centróide. Com isso, o algoritmo gera k centróides e coloca as ocorrências da tabela de acordo com sua distância dos centróides.

Para simplificar a explicação de como o algoritmo funciona vou apresentar o algoritmo K-Means em cinco passos:

PASSO 01: Fornecer valores para os centróides.

Neste passo os k centróides devem receber valores iniciais. No início do algoritmo geralmente escolhe-se os k primeiros pontos da tabela. Também é importante colocar todos os pontos em um centróide qualquer para que o algoritmo possa iniciar seu processamento.

PASSO 02: Gerar uma matriz de distância entre cada ponto e os centróides.

Neste passo, a distância entre cada ponto e os centróides é calculada. A parte mais ‘pesada’ de cálculos ocorre neste passo pois se temos N pontos e k centróides teremos que calcular  N x k distâncias neste passo.

PASSO 03: Colocar cada ponto nas classes de acordo com a sua distância do centróide da classe.

Aqui, os pontos são classificados de acordo com sua distância dos centróides de cada classe. A classificação funciona assim: o centróide que está mais perto deste ponto vai ‘incorporá-lo’, ou seja, o ponto vai pertencer à classe representada pelo centróide que está mais perto do ponto. É importante dizer que o algoritmo termina se nenhum ponto ‘mudar’ de classe, ou seja, se nenhum ponto for ‘incorporado’ a uma classe diferente da que ele estava antes deste passo.

PASSO 04: Calcular os novos centróides para cada classe.

Neste momento, os valores das coordenadas dos centróides são refinados. Para cada classe que possui mais de um ponto o novo valor dos centróides é calculado fazendo-se a média de cada atributo de todos os pontos que pertencem a esta classe.

PASSO 05: Repetir até a convergência.

O algoritmo volta para o PASSO 02 repetindo iterativamente o refinamento do cálculo das coordenadas dos centróides.

Notem que desta maneira teremos uma classificação que coloca cada ponto em apenas uma classe. Desta maneira dizemos que este algoritmo faz uma classificação hard (hard clustering) uma vez que cada ponto só pode ser classificado em uma classe. Outros algoritmos trabalham com o conceito de classificação soft onde existe uma métrica que diz o quão ‘dentro’ de cada classe o ponto está.

O leitor que deseja obter mais informações sobre o algoritmo K-Means pode dar uma olhada no link abaixo e até fazer o download de outras implementações do algoritmo:

http://www.kdkeys.net/forums/3538/ShowPost.aspx

Vamos ver agora um exemplo prático da utilização do algoritmo K-Means.

Exemplo de uso de algoritmo

Neste exemplo vamos considerar que uma determinada empresa vende produtos para clientes por meio de pedidos compostos por itens de pedidos. Para facilitar o entendimento do cenário e do modelo de dados vamos utilizar a base de dados de exemplo Northwind do SQL Server 2000. O diagrama de entidades com as tabelas que nos interessa é apresentado na Figura 01.

Figura 01. Diagrama com as principais tabelas da base de dados NorthWind.

A tabela que contém os itens de pedidos se chama Order Details e possui uma chave primária composta nas colunas OrderID e ProductID. Existem duas chaves estrangeiras na tabelas Order Details, sendo que a primeira chave estrangeira relaciona a coluna ProductID da tabela de produtos chamada Products com a coluna ProductID da tabela Order Details. A segunda chave estrangeira relaciona a coluna OrderId da tabela de pedidos chamada Orders com a coluna OrderId da tabela Order Details. O modelo ainda apresenta a tabela de clientes Customers relacionado à tabela de pedidos Orders por meio das colunas CustomerID presente em ambas as tabelas.

Com base neste modelo, o departamento de marketing da empresa deseja segmentar os clientes para poder oferecer descontos diferenciados e outros benefícios. A segmentação dos clientes deve dividir todos os clientes da base de dados em três categorias: Clientes Ouro, Clientes Prata e Clientes Bronze. O critério de classificação dos clientes deve levar em consideração apenas duas variáveis: o total de pedidos de cada cliente e a quantidade total gasta pelo cliente em todos os pedidos, sem considerar descontos. Obviamente os clientes que possuírem mais pedidos e o maior valor gasto serão classificados como Clientes Ouro.

Com base nestas informações, inicialmente vamos calcular o total de pedidos para cada cliente e a quantidade gasta pelo cliente em todos os pedidos por meio da consulta apresentada na listagem 01. O resultado desta consulta foi armazenado em uma tabela chamada PERFIL.

Listagem 01. Obtendo o perfil de cada cliente.

Com os dados armazenados na tabela PERFIL um gráfico de pontos foi gerado no Excel  com a Quantidade de Pedidos x o Total Gasto. A figura 02 apresenta este gráfico gerado a partir dos dados da tabela PERFIL.

Figura 02. Gráfico de pontos mostrando a Quantidade de Pedidos x Total Gasto por cliente.

Para classificar os dados da tabela PERFIL de acordo com o que o departamento de marketing deseja podemos utilizar o algoritmo K-Means. Como especificado somente dois atributos, Quantidade de Pedidos e Total Gasto, serão utilizados para classificar os clientes. Na vida real o algoritmo K-Means pode trabalhar com qualquer quantidade de atributos para classificar os valores.

Analisando os dados da Figura 02 podemos ver claramente que três clientes serão classificados como Clientes Ouro, pois fica fácil de ver a distância entre estes clientes e os demais. Porém não fica fácil a classificação dos demais clientes em Clientes Prata e Clientes Bronze.

Para ajudar a classificar estes clientes vamos utilizar uma implementação do algoritmo K-Means que vai trabalhar com apenas dois atributos. Esta implementação foi colocada na stored procedure ST_KMEANS baseada em instruções SQL do dialeto T-SQL, linguagem padrão do SQL Server. Com algumas poucas mudanças a stored procedure pode ser implementada em outros bancos de dados e também poderá receber mais de dois atributos para a classificação.

Para tornar mais modular o algoritmo, a implementação do cálculo da distância entre os pontos foi feita na função DIST(), que deve ser criada antes da stored procedure. A listagem 02 apresenta a chamada da stored procedure ST_KMEANS para o exemplo da tabela PERFIL. O primeiro parâmetro que deve ser passado para a procedure é o nome da tabela, seguido pelos parâmetros dos atributos. O quarto parâmetro indica qual é a quantidade de classificações que o algoritmo deve utilizar (clusters). A listagem 02 apresenta os 23 primeiros pontos classificados de acordo com o algoritmo K-Means, colocando-os os em ordem de acordo com a classe a que pertencem.

Listagem 02. Chamada à stored procedure ST_KMEANS

No resultado apresentado pela stored procedure a coluna X equivale ao valor da primeira coluna passada como parâmetro, que no nosso exemplo é QTD_PEDIDOS, e a coluna Y equivale ao valor da segunda coluna passada como parâmetro, que no nosso exemplo é QTD_GASTA.

A Stored Procedure ainda possui um quinto parâmetro. Se este parâmetro não for passado a stored procedure retorna os dados classificados como na listagem 02. Se o quinto parâmetro for passado como 1, a stored procedure retorna as coordenadas dos centróides de cada classe. A Listagem 03 apresenta a chamada à stored procedure com o uso do quinto parâmetro e o seu resultado.

Listagem 03. Execução da stored procedure ST_KMEANS com cinco parâmetros.

Notem que os pontos que definem os centróides de cada classe não existem no conjunto de pontos iniciais.

No nosso exemplo, o algoritmo classificou os dados em três classes: classe 1, 2 e 3. De acordo com a definição do tipo de cliente a classe 1 equivale ao Cliente Bronze, a classe 2 equivale a Cliente Prata e a classe 3 equivale ao Cliente Ouro. Colocando esta dados em um gráfico de pontos podemos visualizar mais facilmente a classificação dos clientes. Este gráfico é apresentado na Figura 03.

Figura 03. Classificação dos clientes de acordo com o algoritmo KMeans.

No gráfico da Figura 03 os clientes representados pela cor amarelo escuro (triângulo) são aos Clientes Ouro, os clientes de cor cinza (quadrado) são os Clientes Prata e os clientes de cor amarelo claro (losango)  são os Clientes Bronze. Os três pontos em preto indicam os centróides calculados pelo algoritmo

Com a utilização do algoritmo pode-se classificar os clientes existentes de acordo com sua Quantidade de Pedidos e Total Gasto em todos os pedidos, da maneira que o departamento de marketing desejou. Para classificar de um novo cliente basta executar novamente a stored procedure e verificar qual é a sua classificação. Desta maneira, todos os clientes serão novamente analisados e re-classificados.

Como alternativa pode-se comparar os dados de um novo cliente aos dados dos centróides antes de incluir o cliente na tabela. Esta comparação é feita por meio da distância entre os valores do novo cliente e os valores dos centróides fornecidos pelo algoritmo quando este recebe o valor 1 para o quinto parâmetro.

Install and Configure SQL Server 2008 R2

MTBF: um parâmetro muito mal compreendido

MTBF significa Mean Time Between Failures ou Tempo Médio Entre Falhas e muitos profissionais utilizam este valor para medir a confiabilidade de dispositivos de hardware, especialmente discos rígidos. Mas esta ideia normalmente está equivocada.

Para entender porque comparar MTBF de equipamentos diferentes pode ser um erro, nós precisamos primeiro entender como este parâmetro é medido.

Em um laboratório, o fabricante executa um teste usando uma quantidade “x” de amostras do produto a ser testado durante “y” horas.

Primeiro o fabricante calcula a quantidade total de horas em que os equipamentos ficaram ligados, também chamado TPOH (Total Power-On Hours). Isto é feito multiplicando-se a quantidade de dispositivos testados (“x”) pelo número de horas que o teste foi executado (“y”). Feito isso o TPOH é dividido pela quantidade de dispositivos que falharam no teste, e este resultado é o MTBF.

Por exemplo, se 1.000 dispositivos são testados por 30 dias seguidos (720 horas), nós temos um TPOH de 720.000 horas. Se durante este período apenas dez dispositivos apresentarem problemas, nós temos um MTBF de 72.000 horas.

Este número, no entanto, não significa que você deve esperar que o produto apresente problema, em média, após 72.000 horas, simplesmente porque cada dispositivo individual não foi testado 720.000 horas, mas apenas durante 720 horas.

E você não pode usar este número para comparar produtos que utilizam metodologias de teste de MTBF diferentes. Por exemplo, se você tem um produto com um MTBF de 72.000 horas, como no exemplo dado, e compará-lo com um produto com MTBF de 100.000 horas, isto não necessariamente significa que o segundo produto tem uma confiabilidade maior. O segundo fabricante pode ter usado uma combinação diferente de dispositivos testados e horas de funcionamento para obter este resultado. Como os resultados foram obtidos usando metodologias diferentes, eles não podem ser comparados.

Então o que o MTBF de um equipamento significa? Significa que no teste conduzido pelo fabricante, uma amostra apresenta problema a cada “x” horas de teste – o MTBF. Em nosso exemplo, um dispositivo apresenta problema a cada 72.000 horas de teste, em média. Mas sem saber exatamente a metodologia usada, é praticamente impossível fazer comparações corretas entre produtos.

Para piorar as coisas, a definição de falha pode variar entre fabricantes. Por exemplo, um fabricante testando o MTBF de um arranjo RAID1 pode considerar uma falha quando um dos discos rígidos apresenta problemas, mas outro fabricante pode não considerar esta falha se o sistema ainda continuar funcionando mesmo com um dos discos dando defeito (pois o disco de espelhamento entrou em ação impedindo o usuário de parar o trabalho e perder dados). A mesma coisa é válida para todos os outros tipos de equipamentos. Se uma fonte de alimentação desligou, mas não “queimou”, durante o cálculo do MTBF, o fabricante pode escolher não contar isto como uma falha (já que a fonte ainda está funcionando depois de religada). Mais uma vez, você não pode comparar valores de MTBF usando metodologias diferentes.

Confiabilidade dependerá de vários outros fatores que não são medidos pela metodologia de cálculo do MTBF. Como um exemplo prático, a temperatura padrão para testes de equipamento é de 25º C, mas várias vezes os componentes começam a apresentar problemas em temperaturas mais elevadas. Portanto, qualquer tipo de teste conduzido pelo fabricante não necessariamente reflete a realidade ambiental de onde o produto será usado.

Usando colunas esparsas

Colunas esparsas são colunas comuns que têm um armazenamento otimizado para valores nulos. Elas reduzem os requisitos de espaço para valores nulos às custas de maior sobrecarga para recuperar valores não nulos. Considere o uso de colunas esparsas quando o espaço salvo for pelo menos de 20 a 40 por cento. As colunas esparsas e os conjuntos de colunas são definidos usando as instruções CREATE TABLE ou ALTER TABELA.
As colunas esparsas podem ser usadas com conjuntos de colunas e índices filtrados:

• Conjuntos de colunas
  As instruções INSERT, UPDATE e DELETE podem referenciar colunas esparsas pelo nome. Entretanto, você também pode exibir e trabalhar com todas as colunas esparsas de uma tabela, combinadas em uma única coluna XML. Essa coluna é denominada conjunto de colunas. Para obter mais informações sobre conjuntos de colunas, consulte Usando conjuntos de colunas.
• Índices filtrados
  Como as colunas esparsas têm muitas linhas de valor nulo, elas são especialmente apropriadas para índices filtrados. Um índice filtrado em uma coluna esparsa pode indexar somente as linhas com valores populados. Isso cria um índice menor e mais eficiente. Para obter mais informações, consulte Diretrizes de criação de índice filtrado.

As colunas esparsas e os índices filtrados habilitam aplicativos, como o Windows SharePoint Services, para armazenar e acessar com eficiência um grande número de propriedades definidas pelo usuário usando o SQL Server.

Índices no SQL Server

Índices nos bancos de dados são utilizados para facilitar a busca de informações em uma tabela com o menor número possível de operações de leituras, tornado assim a busca mais rápida e eficiente.

O exemplo clássico para explicar a utilização de índices é comparar uma tabela do banco de dados a uma lista telefônica, onde a mesma possui um índice por ordem alfabética do sobrenome dos “participantes”. Sabendo a letra inicial do sobrenome é possível refinar a pesquisa iniciando a mesma pela página correspondente a letra do sobrenome.

O SQL Server utiliza o mesmo principio da lista telefônica gravando as informações dos índices em uma estrutura chamada de B-Tree.

Uma estrutura B-Tree possui um nó-raiz que contem uma única página de dados, uma ou mais páginas de níveis intermediários e uma ou mais páginas de níveis folhas. Abaixo segue um exemplo de uma estrutura de B-Tree.

 
Figura 1: Estrutura B-Tree.

Uma B-Tree sempre é simétrica, ou seja, possui o mesmo número de páginas à esquerda e a direita de cada nível.

Obs: Uma página no SQL Server armazena até 8.060 bytes de dados.

Abaixo conforme Figura 2, mostra-se um exemplo de índice em uma estrutura B-Tree para um campo código do tipo inteiro.

Figura 2: Exemplo de índice.

Para construir os níveis raiz e intermediário pega-se o primeiro valor de cada página do nível abaixo junto com o ponteiro da página de onde o valor de dados veio. A cada instrução de inserção, exclusão ou até mesmo alteração é modificado a estrutura dos índices. No caso das páginas utilizadas pelo índice estarem cheias, acontece um processo chamado de divisão de página (page splitting) para comportar a nova estrutura com mais páginas.

Uma busca pelo índice inicia-se no nível raiz percorrendo todas as linhas até achar a cadeia de valores a qual o mesmo se encaixa e através do ponteiro pular para a página do nível intermediário que o mesmo se refere. No nível intermediário repete o mesmo processo até achar a cadeia de valores e pular para a página de nível folha conforme o ponteiro. No nível folha novamente repete-se o processo até achar o valor desejado e nesse momento é localizado os dados necessários.

Por exemplo, conforme a Figura 2, para achar o código 23 iniciaria a busca pelo nível raiz percorrendo as linhas. Como o código 23 está entre 21 e 41 o SQL Server calcula que o código 23 se encontra na seqüência do código 21 e pula para a página do nível intermediário que contem os valores 21 a 31. Em seguida analisaria que a primeira opção (21) se encaixa para a busca e pularia para a página de nível folha que contem a cadeia de 21 a 30, percorreria a mesma até achar o código 23 e finalizaria a busca.

No SQL Server é possível criar índices clusterizados (clustered), não clusterizados (nonclustered), XML e espaciais, sendo os dois últimos para melhorar os recursos de pesquisas em documentos XML e aplicações espaciais, e neste artigo os mesmos não serão abordados. Para maiores esclarecimentos consultar o books online.

Índices clusterizados são ordenados conforme a chave do cluster fornecendo assim uma ordem de classificação para o armazenamento da tabela. Esta ordem de classificação não é a ordem física dos dados e sim a classificação lógica das páginas do índice. É possível definir somente um índice clusterizado por tabela, pois a mesma só pode ser ordenada de uma única maneira.

Índices não clusterizados não classificam ordens e portando é possível criar até 1000 índices nonclustered por tabela tendo cada um no máximo 900 bytes na chave de índice e no máximo 16 colunas. Ao percorrer um índice não clusterizado até seu nível folha em busca do ponteiro para retornar os dados necessários, o mesmo trata os seguintes casos:

·          Existe índice clusterizado na tabela, desta forma o ponteiro aponta para a chave do cluster.

·          Não existe índice clusterizado na tabela, então o ponteiro aponta para a linha de dados da tabela.

A sintaxe para criação de um índice no SQL Server é a seguinte:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name

    ON( column [ ASC | DESC ] [ ,...n ] )

    [ INCLUDE ( column_name [ ,...n ] ) ]

    [ WITH ( [ ,...n ] ) ]

    [ ON { partition_scheme_name ( column_name )

         | filegroup_name

         | default

         }

    ]

[ ; ]

::=

{

    [ database_name. [ schema_name ] . | schema_name. ]

        table_or_view_name

}

::=

{

    PAD_INDEX  = { ON | OFF }

  | FILLFACTOR = fillfactor

  | SORT_IN_TEMPDB = { ON | OFF }

  | IGNORE_DUP_KEY = { ON | OFF }

  | STATISTICS_NORECOMPUTE = { ON | OFF }

  | DROP_EXISTING = { ON | OFF }

  | ONLINE = { ON | OFF }

  | ALLOW_ROW_LOCKS = { ON | OFF }

  | ALLOW_PAGE_LOCKS = { ON | OFF }

  | MAXDOP = max_degree_of_parallelism

}

Algumas dicas a serem consideradas na hora de criar índices:

Campos para serem indexados a fim de ganhar desempenho:

·          Chaves Primárias;

·          Chaves Estrangeiras;

·          Colunas acessadas por ranges (between);

·          Campos utilizados em group by ou order by;

Campos que não devem ser indexados:

·          Campos dos tipos: text, image, decimais;

·          Campos calculados;

·          Campos com alta cardinalidade (Masculino ou Feminino);

Criar índices para campos que compreendem uma query que é utilizada com freqüência. Nesse caso de um índice construído de modo que o SQL Server possa satisfazer as consultas completamente lendo apenas o mesmo é chamado de índice de cobertura (covering indexes).

Criar índice clusterizado para campos de chave primária com a propriedade identity.

Criar índice com colunas incluídas (Include). Por exemplo:

CREATE TABLE clientes

(

      Cod INT IDENTITY(1,1) PRIMARY KEY,

      Nome VARCHAR(100),

      UF VARCHAR(2),

      CEP VARCHAR(8)

)

No caso acima é automaticamente criado um índice clusterizado para a chave primária. Vamos seguir o exemplo dizendo que é necessário rodar a seguinte query:

SELECT CEP FROM clientes WHERE UF='RS'

Analisando a query (com fins de demonstrar o exemplo) poderia criar um índice nonclustered para o campo UF. Nesse caso o índice ajudaria a encontrar o valor desejado de uma forma mais rápida, porem o processo de pesquisa seria percorrer o índice e achar o valor desejado que obrigatoriamente o ponteiro apontaria para o cluster que por sua vez apontaria para o dado físico que por sua vez retornaria o valor CEP. Para facilitar esse processo pode-se criar o seguinte índice:

CREATE NONCLUSTERED INDEX IDX_UF ON clientes (UF ASC) INCLUDE(CEP)

Com o índice criado, ao percorrer o mesmo e encontrar o valor para UF desejado o campo CEP incluído não faz parte da indexação, porem é utilizado para retornar sem necessidade de apontar para o cluster, e desta forma se ganha maior desempenho.

Obs: O Exemplo acima foi criado com a finalidade de demonstrar o uso de índices com colunas incluídas, sem levar em consideração análise de desempenho.

Mantendo a integridade dos índices.

Tabelas que sofrem muitas alterações (Insert, Update e Delete) refletem essas modificações nos índices, pois acabam deixando espaços em brancos nas páginas dos mesmos. Estes espaços não utilizados refletem em maior espaço em disco o que acarreta um desperdício de tempo ao percorrer a estrutura do índice.

Para resolver esses problemas é necessário manter a integridade dos índices, utilizando os seguintes comandos:

ALTER INDEX {nome_indice | ALL} ON REBUILD

ALTER INDEX {nome_indice | ALL} ON REORGANIZE

A opção REORGANIZE remove somente a fragmentação no nível folha e a opção REBUILD reconstrói todos os níveis do índice.

Para os comandos acima citados, é possível substituí-los respectivamente por:

DBCC DBREINDEX

DBCC INDEXDEFRAG

Métodos de acessos aos índices e tabelas.

Os acessos aos dados das tabelas e índices podem ser de duas formas, SEEK ou SCAN.

·         SCAN - busca em TODOS os elementos da estrutura (que pode ser uma tabela ou um índice). É usado quando não possui índices que atendam a instrução de select ou quando a quantidade de registros que a query retorna (em percentual) é grande.

·         SEEK - busca binária nos elementos de um índice. É usado quando existe um índice que é adequado e a quantidade de registros (em percentual) retornados é pequena.

Sendo assim, é possível executar as seguintes operações para acesso nas tabelas/índices:

·         TABLE SCAN - Busca em todos os elementos da tabela, de forma seqüencial;

·         INDEX SCAN - Busca em todos os elementos de um índice nonclustered, de forma seqüencial;

·         INDEX SEEK - Busca binária num índice nonclustered;

·         CLUSTERED INDEX SCAN - Busca em todos os elementos de um índice clustered, de forma seqüencial;

·         CLUSTERED INDEX SEEK - Busca binária num índice clustered.

Quando uma tabela possui mais de um índice, o SQL Server precisa tomar uma decisão de qual (is) deles utilizar em uma consulta de dados. Esta escolhe se dá através das estatísticas (statistics) de acesso.

Quando um índice é criado, o SQL Server gera uma estrutura chamada histograma, que armazena informações sobre a distribuição relativa de valores de dados de uma coluna. À medida que o número de valores exclusivos dentro de uma coluna aumenta, a seletividade de um índice aumenta. Os índices mais seletivos são os escolhidos para satisfazerem uma consulta.

Da mesma forma que um índice pode ser danificado com o passar do tempo, o mesmo acontece para as estatísticas. Para atualizar as estatísticas basta rodar a sintaxe abaixo:

UPDATE STATISTICS <nome_tabela>

O comando acima pode ser substituído pela a seguinte store procedure:

SP_UPDATESTATS

Nota-se que a store procedure não possui nenhum parâmetro e desta forma ela atualiza todas as estatísticas de todas as tabelas do banco de dados no qual a mesmo foi executada.

É possível através do SQL Server monitorar as escolhas que o mesmo fez para acessar os dados em uma instrução de busca. Utilizando a ferramenta Microsoft SQL Server Management Studio é possível visualizar todo o processo de leitura ao rodar uma query habilitando o Execution Plan.

Figura 3: Exibição do Execution Plan.

A Figura 3 demonstra como o SQL Server procedeu para realizar a query. Foi utilizado o SELECT descrito neste artigo ao abordar a criação de índices com colunas incluídas.

Figura 4: Resumo do SELECT

A Figura 4 traz as informações referentes à instrução de SELECT conforme a Figura 3.

Figura 5: Resumo das etapas para executar a instrução de SELECT conforme a Figura 4.

Obs: A tabela foi criada para demonstrar o Execution Plan. A mesma não possui nenhum registro e sendo assim os índices e as estatísticas também não possuem informações.

O SQL Server dispõe de uma ferramenta chamada Database Engine Tuning Advisor que é muito útil para analisar os índices existentes em cima de instruções de acesso a uma base dados para sugerir melhorias referentes aos mesmos. Nesse artigo não será abordado como utilizar a mesma, porem fica a dica.

Varchar, Char, NVarchar, NChar e suas variantes MAX

Se você usa o banco de dados SQL Server em suas aplicações já notou a ampla gama de tipos de dados que ele oferece.
A seguir temos alguns tipos de dados do SQL Server para armazenamento de Strings:
ASCII Strings:

Tipos de dados	Descrição
char(n)	Sequência de caracteres de tamanho fixo. Máximo 8000 caracteres
varchar(n)	Sequência de caracteres de tamanho variável. Máximo 8000 caracteres
varchar(max)	Sequência de caracteres de tamanho variável. Máximo 1.073.741.824 caracteres
text	Sequência de caracteres de tamanho variável. Máximo 2GB de dados de texto

Unicode strings:

Tipos de dados	Descrição
nchar(n)	Dados Unicode de comprimento fixo. Máximo 4000 caracteres
nvarchar(n)	Dados Unicode de comprimento variável. Máximo 4000 caracteres
nvarchar(max)	Dados Unicode de comprimento variável. Máximo 536.870.912 caracteres
ntext	Dados Unicode de comprimento variável. Máximo 2GB de dados de texto

Essa ampla gama de tipos de dados no SQL Sever pode às vezes causar confusão no momento de decidir qual tipo de dados atribuir a um campo de uma tabela principalmente quando eles são parecidos.

Os tipos de dados VARCHAR, CHAR, NCHAR e NVARCHAR se enquadram nesta categoria e por serem muito usados e muito parecidos geralmente causam uma certa dúvida no momento de sua utilização. (Não vou tratar o tipo de dados text/ ntext)

Meu objetivo é tentar esclarecer as dúvidas a respeito das diferenças entre estes dois tipos de dados.

Então vamos lá...

Qual a diferença entre os esses tipos de dados ?
Qual a diferença entre VARCHAR e CHAR ???
CHAR e VARCHAR são tipos de dados caractere, a diferença é que CHAR é um tipo de dado de comprimento fixo e VARCHAR é de comprimento variável.

Usamod CHAR quando os tamanhos que desejamos armazenar na coluna de uma tabela são de tamanho consistentes e semelhantes. Exemplo: Número de telefone, CEP, CPF, CGC, etc. O tipo CHAR possui um tamanho fixo, assim se você tentar armazenar um valor maior que o definido numa coluna do tipo CHAR ele será truncado.

Se você definir uma coluna da tabela (campo) como CHAR(10) e armazenar um caractere apenas ele vai armazenar mais nove espaços em branco.( Por causa desta característica o tipo de dados CHAR é chamado de tipo de dados com tamanho fixo.)
Use VARCHAR quando os tamanhos a serem armazenados na coluna da tabela variam consideravelmente. Ex: Endereço,Nomes, URL, etc. Dessa forma um valor menor irá ocupar menos espaço que um valor maior.
O tipo de dados VARCHAR armazena somente a quantidade de caracteres que foram definidos na usa criação.
Assim se você definir uma coluna da tabela (campo) como VARCHAR(10) e armazernar um caractere ele vai armazenar somente o caractere sem colocar espaços para completar o tamanho definido na criação.
Quando usar CHAR ou VARCHAR ?
O tipo CHAR deve ser usado quando sabemos que todos os dados armazenados em determinada coluna não são variáveis como, por exemplo, uma coluna que armazena a sigla do estado ou o cep que sempre terão o mesmo tamanho.

Já o VARCHAR deve ser utilizado quando não sabemos o que vamos armazenar. Um exemplo pode ser o nome do cliente, endereço, o email que sempre variam de tamanho.
Qual a diferença entre NVARCHAR E NCHAR ?
NCHAR e NVARCHAR são semelhantes a CHAR e VARCHAR a diferença é que eles armazenam dados no formato Unicode e utilizam para isso a representação de 1 byte para cada caractere usando assim uma representação de 16 bits.
Qual a diferença entre VARCHAR e NVARCHAR ???
VARCHAR é uma abreviação para VARiable-length CHARacter string que é uma sequência de caracateres de texto que pode ser tão grande quanto o tamanho da página para a tabela de banco de dados da coluna em questão.
O tipo de dados VARCHAR armazenam os dados em ASCII e utilizam 1 byte ou 8 bits para representar um caractere.
O tamanho de uma página de tabela é 8196 bytes, e não uma linha em uma tabela pode ser superior a 8.060 caracteres. Este, por sua vez, limita o tamanho máximo de um VARCHAR para 8.000 bytes.

E o tipo de dados NVARCHAR ?

Bem, o N(VARCHAR) significa uNicode e essencialmente , NVARCHAR nada mais é do que um VARCHAR que suporta Unicode ou seja que usa dois bytes para representar um caractere.

O tipo de dados NVARCHAR armazenam os dados em Unicode e utilizam 2 bytes ou 16 bits para representar um caractere.

Um uso mais comum para esse tipo de de dados e quando você deseja armazenar caracteres de idíomas que exigem mais de u m byte para representar um caractere.
Assim quando você define uma coluna como sendo VARCHAR(30) terá 30 bytes alocados para a colun. Se você usar o tipo NVARCHAR(30) terá 60 bytes alocados.
Portanto o tamanho máximo para o tipo de dados VARCHAR (e CHAR) é 8000 bytes e para o tipo de dados NVARCHAR (e NCHAR) é 4000 bytes.
A partir da versão SQL Server 2005 introduziu um novo tipo de dados , o tipo de dados XML que permite armazenar documentos XML em um banco de dados SQL Server.
Para suportar o novo tipo de dados foram introduzidos também 3 novos tipos de dados diferentes: VARCHAR(MAX), NVARCHAR(MAX) E VARBINARY(MAX)
A utilização de MAX indica que esses tipos de dados podem armazenar um valor superior aos seus respectivos limites: 8000 e 4000 bytes.VARCHAR (MAX) - indica que o tamanho máximo de armazenamento para o tipo de dados VARCHAR é 2^31 bytes ( 2 GB)
NVARCHAR (MAX) - indica que o tamanho máximo de armazenamento para NVARCHAR o tipo de dados é 2^31 bytes ( 2 GB)
VARBINARY (MAX) - indica que o tamanho máximo de armazenamento para o tipo de dados VARBINARY é 2^31 bytes ( 2 GB)

Quando usar VARCHAR ou NVARCHAR ?
Depende ???
Poderíamos usar duas linhas de pensamento:

Usar VARCHAR a menos que precisamos lidar com uma grande quantidade de dados internacionalizados, neste caso use NVARCHAR;

ou

• Usar NVARCHAR para tudo;

Se você tem sites que oferecem suporte a vários idiomas, considere usar os tipos de dados que dão suporte a Unicode como NCHAR ou NVARCHAR para minimizar os problemas de conversão de caracteres.
Se sua aplicação nunca vai precisar tratar textos multilíngues ou lidar com dados internacionalizados você pode usar VARCHAR e assim terá o máximo de 8000 bytes a sua disposição mas se um dia você precisar migrar o tipo de dados o 'bicho vai pegar...'
Lembre que se você usar NVARCHAR para tudo terá a sua disposição somente 4000 bytes no tipo de dados atribuído. (A menos que use MAX).
Existe um detalhe importante quando você usa NVARCHAR OU VARCHAR : os campos NVarchar não sofrem influência do Collation e o Varchar sofre.
Obs: A Collation refere-se ao conjunto de caracteres usado para armazenar dados em campos de texto sendo necessária para fornecer suporte para os muitos idiomas que podem ser usados.
Quando usamos CHAR ou VARCHAR as colunas são atribuidas a Collation padrão do banco de dados a menos que uma collation específico seja atribuido usando a cláusula COLLATE.

A incorreta definição da Collation quando você usar VARCHAR, vai afetar muitos caracteres, e , assim alguns registros que contenham por exemplo a palavra "ausência" será mostrado como "aus?ncia" , ou seja, caracteres acentuados não serão representados corretamente.

Deterministic and Nondeterministic Functions

Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.

The following built-in functions from categories of built-in functions other than aggregate and string functions are always deterministic.

ABS	DATEDIFF	PARSENAME
ACOS	DAY	POWER
ASIN	DEGREES	RADIANS
ATAN	EXP	ROUND
ATN2	FLOOR	SIGN
CEILING	ISNULL	SIN
COALESCE	ISNUMERIC	SQUARE
COS	LOG	SQRT
COT	LOG10	TAN
DATALENGTH	MONTH	YEAR
DATEADD	NULLIF

The following functions are not always deterministic, but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner.

Function	Comments
CAST	Deterministic unless used with datetime, smalldatetime, or sql_variant.
CONVERT	Deterministic unless one of these conditions exists: Source type is sql_variant. Target type is sql_variant and its source type is nondeterministic. Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.
CHECKSUM	Deterministic, with the exception of CHECKSUM(*).
ISDATE	Deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and style is not equal to 0, 100, 9, or 109.
RAND	RAND is deterministic only when a seed parameter is specified.

All the configuration, cursor, metadata, security, and system statistical functions are nondeterministic. For a list of these functions, see Configuration Functions (Transact-SQL), Cursor Functions (Transact-SQL), Metadata Functions (Transact-SQL), Security Functions (Transact-SQL), and System Statistical Functions (Transact-SQL).
The following built-in functions from other categories are always nondeterministic.

@@CONNECTIONS	@@TOTAL_READ
@@CPU_BUSY	@@TOTAL_WRITE
@@DBTS	CURRENT_TIMESTAMP
@@IDLE	GETDATE
@@IO_BUSY	GETUTCDATE
@@MAX_CONNECTIONS	GET_TRANSMISSION_STATUS
@@PACK_RECEIVED	MIN_ACTIVE_ROWVERSION
@@PACK_SENT	NEWID
@@PACKET_ERRORS	NEWSEQUENTIALID
@@TIMETICKS	RAND
@@TOTAL_ERRORS	TEXTPTR

Tail-Log Backups

This topic is relevant only for databases that are using the full or bulk-logged recovery models.
In most cases, under the full or bulk-logged recovery models, SQL Server 2005 and later versions require that you back up the tail of the log to capture the log records that have not yet been backed up. A log backup taken of the tail of the log just before a restore operation is called a tail-log backup.
SQL Server 2005 and later versions usually require that you take a tail-log backup before you start to restore a database. The tail-log backup prevents work loss and keeps the log chain intact. When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure.
Not all restore scenarios require a tail-log backup. You do not have to have a tail-log backup if the recovery point is contained in an earlier log backup, or if you are moving or replacing (overwriting) the database. Also, if the log files are damaged and a tail-log backup cannot be created, you must restore the database without using a tail-log backup. Any transactions committed after the latest log backup are lost. For more information, see "Restoring Without Using a Tail-Log Backup" later in this topic.

DBCC (Transact-SQL)

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.
Database Console Command statements are grouped into the following categories.

Command category	Perform
Maintenance	Maintenance tasks on a database, index, or filegroup.
Miscellaneous	Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
Informational	Tasks that gather and display various types of information.
Validation	Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.

These are some additional server configuration settings:

C2 Audit Mode: Configures the server to record both failed and successful attempts to access statements and objects.

Fill Factor: Specifies how full SQL Server 2008 should make each page when it creates a new index using existing data.

Min and Max Server Memory: Reconfigures the amount of memory (in megabytes) in the buffer pool used by an instance of SQL Server.

Nested triggers: Controls whether an AFTER trigger can cascade; that is, perform an action that initiates another trigger, which initiates another trigger, and so on.

Query Governor Cost Limit: Specifies an upper limit on the time period in which a query can run.

Query Wait: Specifies the time in seconds (from 0 through 2147483647) that a query waits for resources before timing out. If the default value of -1 is used, the time-out is calculated as 25 times the estimated query cost.

CLR Integration: Supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support.

Options for Installing Components

Comparing Licensing of SQL Server 2008

Editions of SQL Server 2008

Protocolos de isolamento

O isolamento é uma propriedade fundamental das transações. Existem vários níveis diferentes de isolamento, e um nível de ne a forma com as modi ficações feitas por uma transação são visíveis nas transações executadas concorrentemente. Eles de nem e controlam vários fatores relativamente aos bloqueios sobre os dados. Por exemplo, de nem se são feitos bloqueios aos dados se uma transacção precisa de fazer leituras, por quanto tempo os bloqueios são mantidos, etc.

Um nível baixo de isolamento aumenta a capacidade de concorrência das transacções aos dados mas o ónus que se paga é a maior probabilidade das transacções obterem dados desactualizados ou falsos (chamados dirty reads). Por outro lado, um nível alto de isolamento, reduz a capacidade de concorrência e o aumento da probabilidade de ocorrerem deadlocks. O SQL Server suporta 5 níveis de isolamento.

Abaixo descrevemos os níveis suportados organizados do nível mais baixo para o nível mais alto de isolamento:

ˆ READ UNCOMMITTED
ˆ READ COMMITTED
ˆ REPEATABLE READ
ˆ SNAPSHOT
ˆ SERIALIZABLE

O protocolo de isolamento por omissão no SQL Server é o READ COMMITTED.

SQL Server - Transactions & Locking #5

Deadlocks

Um deadlock é uma condição de erro que pode acontecer em qualquer sistema de multiprocessamento ou multi-threading e que, por isso, não é exclusivo de sistemas de gestão de bases de dados.

No contexto do SQL Server, que nos interessa por agora, um deadlock ocorre quando duas ou mais operações (transações) se bloqueiam permanentemente. Porque cada uma das transações possui um lock sobre um recurso que qualquer outra está a tentar bloquear.

Eis um exemplo muito simples:

    * A transação A adquire um lock de leitura do registo 1.
    * A transação B adquire um lock de leitura do registo 2.
    * A transação A solicita um lock de escrita sobre o registo 2 e fica bloqueada até que a transação B termine e liberte o lock que possui.
    * A transação B solicita um lock de escrita sobre o registo 1 e fica bloqueada, da mesma forma, pela transação A.

Nenhuma das transações tem condições para terminar. Por isso o SQL Server implementa um mecanismo – Deadlock Monitor – que periodicamente analisa as transações activas para detectar este tipo de situações.
Quando uma condição de dealock é encontrada, o monitor termina automaticamente com erro uma das transações de forma que a outra transação possa terminar normalmente.
A transação abortada é conhecida como “dealock victim” e é escolhida segundo critérios baseados no custo envolvido no respectivo ROLLBACK. Fica ao critério da aplicação tratar o erro ocorrido e, eventualmente, submeter novamente a transação.

Tipos de Deadlocks

O exemplo que apresentei ilustra, desde logo, que os deadlocks podem ocorrer sobre vários tipos de recursos – registos, índices, páginas, tabelas ou bases de dados – e por várias operações (não é necessário que exista um lock exclusivo antes da leitura dos dados pela segunda transação).
Na realidade, é possível a ocorrência de dealocks no SQL Server também sobre outros recursos do sistema não directamente relacionados com os dados como, por exemplo, blocos de memória ou worker threads.

Tratamento de Deadlocks

Quando o motor da base de dados detecta um dealock:

    * Selecciona uma das transações como vítima,
    * Cancela a operação em curso,
    * Desfaz todas as operações realizadas por essa transação (ROLLBACK) e
    * Devolve a mensagem de erro 1205.

Em teoria qualquer aplicação que submeta queries à base de dados pode vir a ser seleccionada como vítima num deadlock. Por isso, qualquer aplicação deve incluir um tratamento de erros específico para este efeito (capaz de tratar o código de erro 1205).
Então o “error handler” poderá voltar a submeter a mesma transação de modo que o utilizador poderá mesmo nem se aperceber do erro ocorrido. No entanto, é recomendável que a aplicação seja parada por um período de tempo aleatório antes de o fazer de modo a dar tempo à outra transação para terminar e libertar os recursos que causaram o erro. É importante que esse tempo seja aleatório para evitar que, se, por exemplo, duas transações sejam canceladas para terminar o dealock não voltem a tentar a transação em simultâneo, potenciando a ocorrência de novo deadlock, agora entre essas transações.

Como minimizar a ocorrência de dealocks?

Para além das regras básicas de implementação de transações:

    * Não programar interações com o utilizador dentro de uma transação.
    * Reduzir ao máximo a duração e o número de operações de cada transação.

É muito importante:

    * Aceder aos recursos pela mesma ordem nas várias transações concorrentes.

E ainda é possível, dependendo de cada caso particular:

    * Utilizar um nível de isolamento inferior (para reduzir a duração dos locks).
    * Utilizar “bound connections” (quando usadas, todas as conexões abertas pela mesma aplicação podem cooperar entre si, “partilhando” os locks adquiridos por cada uma, logo nunca se bloqueando entre si).

Conclusões

Um deadlock é uma das condições de erro mais fácil de compreender e explicar a um programador de bases de dados. No entanto, também é um dos erros mais difíceis de diagnosticar e resolver porque envolve, na maioria das vezes, um nível de concorrência ou de sincronização nos testes que é difícil de reproduzir em laboratório. Por outro lado, na perspectiva do utilizador é um erro muito difícil de aceitar até pela mensagem erro apresentada (fala de ter sido vítima de uma coisa, o que parece logo muito mais grave).

Portanto, a abordagem mais correta será, garantidos os princípios básicos de programação de transações (que os minimizam), efectivamente ter um tratamento de erros adequado e pessimista.
Nos próximos posts, já em jeito de conclusão, vamos analisar formas práticas de tratamento de erros de locking (incluindo deadlocks).

SQL Server - Transactions & Locking #4

Isolation Levels

O SQL Server disponibiliza quatro níveis distintos de isolamento (“isolation levels”) entre transações:

    * READ UNCOMMITED
    * READ COMMITED
    * REPEATABLE READ
    * SERIALIZABLE

O nível de isolamento é configurado para cada transação e define o seu grau de isolamento em relação às modificações efectuadas por outras transações sobre os recursos do servidor. Por outras palavras, o nível de isolamento define que efeitos secundários de concorrência são permitidos (aceitáveis) para essa transação.

Cada nível de isolamento controla de forma distinta:

    * Que locks são adquiridos pela transação em operações de leitura.
    * A duração dos locks de leitura.
    * Se uma operação de leitura sobre registos modificados por outra transação:
          o É ou não bloqueada até que o lock exclusivo seja libertado.
          o Obtém a versão gravada desses registos no momento em que foi iniciada.
          o Obtém a versão (ainda) não gravada desses registos.

É importante notar que o nível de isolamento não afecta o tipo de locks obtidos em operações de modificação de dados. Sempre que for realizada uma operação desse tipo (INSERT/UPDATE/DELETE) a transação obterá um lock exclusivo sobre os recursos modificados e manterá esse lock até que termine (COMMIT ou ROLLBACK).

Isolation Levels vs Performance

Um nível de isolamento inferior aumenta a concorrência do sistema, permitindo que mais utilizadores acedam aos mesmos dados à custa da possibilidade de ocorrerem mais (e mais graves) efeitos de concorrência (exemplo: dirty reads). Por outro lado, um nível de isolamento superior reduz a possibilidade de ocorrerem conflitos, diminuindo a concorrência entre utilizadores e, consequentemente, o desempenho geral da base de dados.
A escolha do nível adequado de isolamento depende, portanto, do tipo de operações realizadas pelas várias transações, da performance pretendida no acesso aos dados para leitura e da segurança pretendida em termos de integridade dos dados.

READ UNCOMMITED

Neste modo, as operações de leitura não respeitam a existência ou não de locks exclusivos sobre os recursos acedidos. Isto significa que podem ser lidos dados alterados por transações ainda não terminadas. Trata-se do nível de isolamento menos restritivo e, por isso, mais adequado para sistemas analíticos que requeiram um rápido acesso aos dados, com um nível de alterações dos dados muito reduzido (ou nulo).

Exemplo:

| transação 1              | transação 2              | Valores   |
|---------------------------|---------------------------|-----------|
| (Idle)                    | (Idle)                    | TA(B) = 2 |
| BEGIN TRAN                | (Idle)                    |           |
| UPDATE TA SET B = 3       | (Idle)                    | TA(B) = 3 |
| (...)                     | SELECT B FROM TA          | X = 3     |
| ROLLBACK                  | (...)                     | TA(B) = 2 |

Problemas de concorrência:

    * Dirty Read: Sim
    * Nonrepeatable Read: Sim
    * Phantom Read: Sim

READ COMMITED

Este é o nível de isolamento por defeito do SQL Server.
Nenhuma transação conseguirá obter um shared lock (de leitura) sobre recursos que tenham sido alterados (exclusive lock) por transações ainda não terminadas.
Os locks exclusivos têm a mesma duração da transação. Os locks de leitura duram apenas o tempo necessário para completar a operação (não até ao fim da transação).

Exemplo:

| transação 1              | transação 2              | Valores   |
|---------------------------|---------------------------|-----------|
| (Idle)                    | (Idle)                    | TA(B) = 2 |
| (Idle)                    | BEGIN TRAN                |           |
| (Idle)                    | SELECT B FROM TA          | X = 2     |
| BEGIN TRAN                | (...)                     |           |
| UPDATE TA SET B = 3       | (...)                     | TA(B) = 3 |
| (...)                     | SELECT B FROM TA          |           |
| (...)                     | (transação bloqueada)    |           |
| COMMIT                    | (...)                     | TA(B) = 3 |

Problemas de concorrência:

    * Dirty Read: Não
    * Nonrepeatable Read: Sim
    * Phantom Read: Sim

REPEATABLE READ

Este modo difere do anterior porque os locks de leitura são mantidos até ao final da transação. Isto significa que nenhuma transação pode alterar um recurso que tenha sido lido por uma transação não terminada. Por isso impede “nonrepeatable reads”. No entanto, os “phantom reads” não são impedidos porque a nova transação pode ainda inserir registos que afectem a leitura da primeira transação.

Exemplo:

| transação 1              | transação 2              | Valores   |
|---------------------------|---------------------------|-----------|
| (Idle)                    | (Idle)                    | TA(B) = 3 |
| (Idle)                    | BEGIN TRAN                |           |
| (Idle)                    | SELECT B FROM TA          | X = 3     |
| BEGIN TRAN                | (...)                     |           |
| UPDATE TA SET B = 4       | (...)                     |           |
| (transação bloqueada)    | COMMIT                    |           |
| COMMIT                    | (...)                     | TA(B) = 4 |

Problemas de concorrência:

    * Dirty Read: Não
    * Nonrepeatable Read: Não
    * Phantom Read: Sim

SERIALIZABLE

Este é o nível mais restritivo e corresponde, como o nome indica, a serializar as transações que acedem aos mesmos recursos. Como nenhuma transação pode efectuar qualquer tipo de operação de modificação sobre dados que estejam a ser consultados por transações activas, elimina os “phantom reads”. Note-se, no entanto que, por isso mesmo, tem um efeito muito negativo no nível de concorrência do servidor.

Exemplo:

| transação 1              | transação 2              | Valores   |
|---------------------------|---------------------------|-----------|
| (Idle)                    | (Idle)                    | N = 1     |
| (Idle)                    | BEGIN TRAN                |           |
| (Idle)                    | SELECT COUNT(*) FROM TA   | N = 1     |
| BEGIN TRAN                | (...)                     |           |
| INSERT INTO TA ...        | (...)                     |           |
| (transação bloqueada)    | SELECT COUNT(*) FROM TA   | N = 1     |
| (...)                     | COMMIT                    |           |
| COMMIT                    | (Idle)                    | N = 2     |

Problemas de concorrência:

    * Dirty Read: Não
    * Nonrepeatable Read: Não
    * Phantom Read: Não

SET TRANSATION ISOLATION LEVEL

Para alterar o nível de isolamento de uma transação deve ser executado o comando SET TRANSACTION ISOLATION LEVEL. As variações são as seguintes:

    * SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    * SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    * SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    * SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Conclusões

Desta discussão a principal conclusão a retirar é que o nível de isolamento adequado a cada sistema (ou mesmo a cada transação) depende essencialmente do tipo de operações realizadas.

Quando o factor mais importante for a velocidade de leitura de dados (exemplos: sistemas analíticos e serviços de encomendas online), havendo poucas alterações (muito esporádicas) dos dados e, desde que a exactidão dos resultados não seja um factor crítico, é recomendável um nível de isolamento baixo (READ UNCOMMITED).
Em sistemas mais transaccionais (exemplo: ERP) será mais adequado um nível superior, reduzindo a ocorrência de conflitos e incorrecções nos dados, à custa do desempenho das operações de leitura.

O nível de isolamento por default – READ COMMITED – será bastante equilibrado na maioria dos casos. Mas é muito importante que as transações sejam bem desenhadas, reduzindo a sua duração ao mínimo possível, reduzindo as operações de alteração efectuadas e garantindo que quaisquer leituras necessárias para efectuar as posteriores actualizações estão os mais próximas possível.

O SQL Server não oferece, portanto, “almoços grátis” também a este nível.