Baixe Sistemas de Bases de Dados e outras Notas de estudo em PDF para Informática, somente na Docsity! Sistemas de Bases de Dados Faculdade de Ciências e Tecnologia Mestrado em Engenharia Informática Análise de um Sistema de Gestão de Bases de Dados: Microsoft SQL Server 2005 Trabalho realizado por: Arlindo Lima Nº 26651 Hugo Aguiar Nº 29672 Tiago Melo Nº 32629 9 de Dezembro de 2009 Contents 1 Introdução 1 1.1 Introdução histórica 1 1.2 Notas sobre instalação do sistema 2 2 Cobertura do SQL 3 2.1 DDL 3 2.2 DML 5 2.3 Tipos de Dados 6 3 Armazenamento e file structure 9 3.1 O Buffer Pool 9 3.2 Acesso às páginas em memória 10 3.3 Gestão de páginas na cache de dados 10 3.4 Arquitectura NUMA 11 3.5 Read-ahead 11 3.6 Sistema de ficheiros 12 3.7 Páginas e Extents 12 3.8 Organização dos ficheiros 13 3.9 Mecanismo de partições 13 4 Indexação e Hashing 17 4.1 Estrutura B-tree 17 4.2 Tipos de Índices 20 4.2.1 Índices clustered 20 4.2.2 Índices non-clustered 21 4.3 Implementação de Índices 23 4.3.1 Índices com colunas incluídas 23 4.3.2 Indexação de views 23 4.3.3 Indexação full-text 23 4.3.4 Indexação XML 24 4.4 Criação de Índices 24 4.4.1 Argumentos do comando CREATE INDEX 25 4.4.2 Opções do comando CREATE INDEX 25 4.5 Indexação e Hashing - Oracle 10g vs MS SQL 2005 27 5 Processamento e Optimização de Queries 29 5.1 Algoritmos para Junções 30 5.1.1 Nested Loop 30 5.1.2 Merge 31 i 1 Introdução A sociedade moderna encontra-se inundada de dados - dados científicos, dados clínicos, da- dos demográficos, dados financeiros, entre outros. Estes dados precisam de ser armazenados, acedidos e alterados. Estas são as funções das bases de dados. Hoje em dia, poucas são as organizações que conseguem funcionar sem esta tecnologia. Para ajudar na manutenção das bases de dados, foram criados os Sistemas de Gestão de Bases de Dados (SGBD), que são um conjunto de programas que controlam a organização, armazenamento e acesso aos dados. Existem vários SGBD, sendo o sistema Oracle, considerado o melhor, pela indústria. No entanto, o SGBD da Microsoft, SQL Server, tem vindo a ganhar terreno, principalmente por oferecer uma elevada facilidade de instalação, para um SGBD do seu calibre. Neste relatório, iremos apresentar algumas características do Microsoft SQL Server, que mostram que este SGBD consegue concorrer com o seu rival comercial Oracle. 1.1 Introdução histórica Em 1987, a Microsoft e a Sybase aliaram-se para desenvolver um sistema de gestão de base de dados, baseado no software Sybase DataServer (ainda por ser lançado). A Sybase ficaria com os direitos para distribuir a versão para Unix e VMS (esta versão acabaria por se chamar Sybase SQL Server) e a Microsoft iria distribuir a versão para OS/2. Assim, em 1989, foi lançada a primeira versão do Microsoft SQL Server, SQL Server 1.0. Em 1990 foi lançada a versão para Windows: SQL Server 1.1. Na altura em que o Windows NT foi lançado, a popularidade do OS/2 tinha caído bastante. Além disso o Windows NT já suportava novas arquitecturas que o OS/2 não suportava. Isto fez 1 1. INTRODUÇÃO 1.2. Notas sobre instalação do sistema com que a Microsoft quisesse passar a desenvolver o SQL Server exclusivamente para o Win- dows, usando as capacidades do seu sistema operativo. A Sybase, no entanto, queria continuar a desenvolver software multi-plataforma, pelo que as duas empresas se separaram. Assim, em 1995, a Microsoft lançou o SQL Server 6.0 que, pela primeira vez, estava com- pletamente integrado com o Windows NT. Na tentativa de tornara o seu SGBD cada vez mais intuitivo, foi lançado em 1998 o SQL Server 7.0, o primeiro SGBD com uma verdadeira interface gráfica. A marcha não ficou por aqui. Em 2000 foi lançado o SQL Server 2000 que dava con- tinuidade a um produto que por esta altura já era bastante reconhecido no mercado empresarial (em 2003 seria lançada a versão 64 bits - que também se tornaria na primeira versão deste SGBD a fazer uso desta arquitectura). Em 2005 foi lançado o SQL Server 2005 (a versão estudada neste relatório) e, recentemente, em Agosto de 2008, foi lançada a versão 2008. Para a realização deste trabalho foram consultados os manuais [10], [6], [5], [4], [8], [7], [3], [2], [9] e o site [1]. 1.2 Notas sobre instalação do sistema Antes de instalar: 1. Desinstale a Framework .NET 1.2 (as versões 1.0 e 1.1 não têm de ser obrigatoriamente desinstaladas). A seguir instale a Framework .NET 2.0. 2. Instale o SQL Server 2005, seguindo os vários passos: 3. Depois de satisfeitos os pré-requisitos, siga com a instalação clicando em "install". 4. Escolha os componentes a instalar (todos). 5. Em seguida, escolha o local de instalação dos componentes. 6. Clique "seguinte" nos restantes ecrãs e a instalação será iniciada. 2 2. COBERTURA DO SQL 2.2. DML 2.2 DML DML é o conjunto das operações que manipulam os dados. Quer isso dizer que serão us- adas para inserir, remover e editar dados, nomeadamente com os comandos INSERT, UPDATE e DELETE. Também está incluido nesse conjunto o comando SELECT, para acesso aos dados, que será a longe prazo o mais usado. Para poder trabalhar com os dados temos de primeiro inseri-los. O INSERT do T-SQL é muito versátil deixando, entre outros, inserir dados Null, tuplos com menos dados que colunas, por diferentes ordens, através de tabelas temporárias e views e até usar o SELECT e EXECUTE dentro do INSERT. Podemos posteriormente alterar estes dados com o UPDATE. Este suporta copiar dados de outras tabelas, alterar só um valor com o SET e indicando o nome da coluna, seleccionar que tuplos actualizar usando o WHERE e podemos até inferir diferentes valores usando a cláusula CASE. A forma mais comum de remover dados é indicando a tabela e que condições o tuplo precisa de ter, mas o T-SQL tam- bém tem suporte para remover sem indicar condições, remover vários tuplos, remover o tuplo onde se encontra um cursor e até remover tuplos seleccionados com uma sub consulta. Caso não deseje editar os dados o T-SQL disponibiliza o comando SELECT, com suporte para a estrutura do SELECT do standard SQL, para aceder aos dados. Dados esses que podem ser Views, cursores, tabelas, subconjunto de tabelas e mesmo resultados de outro SELECT. Pode- mos usar o SELECT desde a sua forma mais básica, SELECT ’Hello World’, que simplesmente irá devolver ’Hello World’ ou SELECT (select_list) INTO (new_table) FROM (table source) WHERE (search_condition) GROUP BY (group_by_expression) HAVING (search_condition) ORDER BY (order_expression) [ASC / DESC], onde já se encontram algumas das muitas clausulas para o qual há suporte. Iremos agora explicar algumas das cláusulas: • AS: permite renomear as colunas. • INTO: guarda os resultados noutra tabela. • FROM: define que tabela irá ser interrogada. • WHERE/HAVING: clausula condicional, que restringe os resultados. • GROUP BY: permite agrupar os resultados indicando as colunas. • ODER BY [ASC | DESC]: ordena os resultados pelos valores da coluna indicada. • COMPUTE: permite fazer cálculos como a média, o Max, mínimo e somatório. • FOR XML: indica que o resultado da query deve ter o formato XML. • JOINS: é o produto entre duas ou mais resultados. Tem suporte para INNER e [LEFT|RIGHT|TOTAL] OUTTER JOIN. • UNION: une dois resultados. 5 2. COBERTURA DO SQL 2.3. Tipos de Dados 2.3 Tipos de Dados O T-SQL tem suporte para os seguintes tipos de dados: Valores numéricos inteiros: • bigint • int • smallint • tinyint • bit • decimal • numeric • money • smallmoney Valores numéricos aproximados: • float • real Data e hora: • datetime • smalldatetiem Strings: • char • varchar • text • nchar • nvarchar • ntext 6 2. COBERTURA DO SQL 2.3. Tipos de Dados Strings binárias: • binary • varbinary • image Outros: • cursor • sql_variant • table • timestamp • uniqueidentifier • xml 7 3. ARMAZENAMENTO E FILE STRUCTURE 3.2. Acesso às páginas em memória restrição. Nos sistemas x64 esta restrição não existe, pelo que é ignorada a configuração para esta API. Além do tamanho do VAS, é calculado o número de páginas que se espera ser possível alocar (este valor é designado por Target Memory). É possível ver estas informações, bem como outras relevantes ao buffer pool, na Dynamic Management View (DMV) chamada sys.dm_os_sys_info. Colunas interessantes incluem: physical_memory_in_bytes - memória física disponível. virtual_memory_in_bytes - memória virtual disponível. bpool_commited - número de páginas no buffer pool (não inclui memória reservada). bpool_commit_target - Target Memory. 3.2 Acesso às páginas em memória O SQL Server tenta manter o acesso às páginas, na cache de dados, rápido (não é eficiente pesquisar em toda a cache) pelo que as páginas são guardadas numa hash table (implementada como uma página que contém um array - que na verdade é uma lista ligada - de apontadores para as páginas na cache), em que a combinação dos identificadores da base de dados (serve para identificar a que base de dados o ficheiro pertence), do ficheiro e da página, são os argumentos de entrada da função de hash. 3.3 Gestão de páginas na cache de dados No SQL Server, o mesmo mecanismo é responsável por escrever as alterações das páginas em disco e por marcar como livre a memória ocupada por páginas que não sejam referenciadas há algum tempo (sendo mantida uma lista ligada com os endereços das páginas "livres"). Cada buffer na cache de dados tem um cabeçalho que contém informação sobre as duas úl- timas vezes que cada página foi referenciada, além de outras informações sobre o estado dessas mesmas páginas, incluindo o facto de uma página estar ou não dirty (o seu conteúdo ter alterado desde que foi lida de disco). Esta informação é usada na implementação da política de substitu- ição de páginas, que usa o algoritmo LRU-K (Least Recently Used-K). Este algoritmo baseia-se na filosofia do algoritmo LRU, mas mantém-se a par das últimas K vezes que a página foi refer- enciada (sendo usado o valor 2 para K, na implementação do SQL Server). Assim, buffers que contenham páginas consideradas mais importantes, mantêm-se no buffer pool activo, enquanto buffers com páginas menos referenciadas acabam por regressar à lista de buffers livres. O trabalho de percorrer o buffer, escrevendo páginas "dirty" em disco e adicionando buffers à lista de buffers livres é feito por um processo assíncrono, denominado lazywriter. Este também é responsável por diminuir ou aumentar a cache de dados, de modo a manter a memória física livre do sistema operativo em cerca de 5MB, de modo a evitar paginação. Existe outro processo também responsável por percorrer a cache de buffer, periodicamente, 10 3. ARMAZENAMENTO E FILE STRUCTURE 3.4. Arquitectura NUMA e escrever em disco páginas "dirty": checkpoint. A diferença entre os processos lazywriter e checkpoint é o facto de este último nunca colocar buffers na lista livre. O único objectivo do checkpoint é assegurar que páginas alteradas há mais de x tempo são, efectivamente, escritas em disco, de modo a minimizar o tempo que o SQL Server necessitaria para recuperar de uma falha. Este processo também corre automaticamente, mas também pode ser accionado manualmente através do comando CHECKPOINT. Uma característica que existia nas versões do SQL Server anteriores à 2005 era a possi- bilidade de marcar tabelas, de modo a que as suas páginas fossem mantidas em memória, in- definidamente. Este processo, chamado pinning de uma tabela, podia ser accionado usando a opção pintable do procedimento sp_tableoption. Este comando ainda existe no SQL Server 2005, mas não tem efeito (de modo a se manter retro-compatível). 3.4 Arquitectura NUMA O SQL Server 2005 é compatível com a arquitectura Non-Uniform Memory Access (NUMA). A maior vantagem desta arquitectura é o facto de ser uma solução escalável para o aumento do número de CPUs nos computadores. Na arquitectura NUMA os CPUs estão agrupados em pequenos conjuntos, designados por nós NUMA, sendo cada nó servido por um bus de sistema, além de ter a sua própria memória interna. Cada CPU pode aceder à memória de outros nós, de forma coerente, embora seja mais rápido aceder à memória local do seu nó. Assim, ao contrário da arquitectura Symmetric Multiprocessing (SMP), onde todo o acesso a memória é feito pelo mesmo bus partilhado (o que até funciona bem para um pequeno numero de CPUs), NUMA alivia este "bottleneck" limitando o número de CPUs num mesmo bus. O SQL Server tem ainda a vantagem de permitir subdividir um (ou mais) nós NUMA físicos em nós mais pequenos, denominados software NUMA (soft-NUMA). Tipicamente, usa-se soft- NUMA quando se está na presença de vários CPUs, mas não de hardware NUMA. O uso de soft-NUMA permite reduzir o I/O e "bottlenecks" provocados pelo processo lazy- writer. Por exemplo, num computador com oito CPUs e sem hardware NUMA, temos apenas uma thread para I/O e um lazywriter (o que pode causar entupimentos). Configurando quatro nós soft-NUMA, passamos a ter quatro threads para I/O e quatro lazywriters, o que ajuda na performance. 3.5 Read-ahead O SQL Server suporta um mecanismo chamado "read-ahead" que antecipa as necessidades de páginas de dados e índices, carregando, para o buffer pool, estas páginas antes delas serem pedidas. Esta optimização aumenta bastante a eficiência do processamento de dados. Este mecanismo é gerido internamente, sem necessidade de ser configurado. 11 3. ARMAZENAMENTO E FILE STRUCTURE 3.6. Sistema de ficheiros 3.6 Sistema de ficheiros Uma base de dados no SQL Server 2005 pode ser vista como uma colecção de ficheiros que contêm dados e meta-dados, correspondendo cada ficheiro da base de dados a um ficheiro do Windows (no entanto, cada ficheiro tem um nome lógico e um nome físico). Esta dependência, embora diminua a portabilidade deste SGBD, tem a vantagem de permitir fazer uso das fer- ramentas disponibilizadas por esses sistemas de ficheiros (tal como encriptação e definição de quotas/permissões) de modo totalmente transparente para o SQL Server. Existem os seguintes três tipos de ficheiros no SQL Server: Ficheiros de dados primários - contêm informação sobre todos os ficheiros na base de dados, além de guardarem dados. Ficheiros de dados secundários - guardam dados dos utilizadores (índices, vistas, tabelas e procedimentos). Têm a função de replicar esta informação por vários discos. Ficheiros Log - contêm informação necessária para a recuperação de todas as transacções efectuadas. Os ficheiros de dados podem ser atribuídos a um filegroup, uma característica que permite agrupar ficheiros. Isto pode ser útil para colocar dados (e índices) numa unidade de disco especifica ou para a criação de um regime de backup que apenas salvaguarda os ficheiros de determinados filegroups. Os ficheiros log, no entanto, não podem ser atribuídos a um filegroup porque são guardados separadamente dos ficheiros de dados. 3.7 Páginas e Extents A um nível de abstracção superior ao do sistema de ficheiros, o armazenamento da base de dados no SQL Server 2005 é feito em páginas e extents. O objecto de armazenamento básico é a página (que, tal com já foi dito, tem 8KB), cuja estrutura é semelhante à de outros objectos do sistema operativo Windows. Primeiro, a página é identificada por um identificador único. Em seguida, contém um cabeçalho (header), de 96 bytes, com informação pertinente sobre a página incluindo o seu tipo, espaço livre, etc. Seguem- se as linhas com os dados (podendo, no máximo, uma linha ocupar 8 060 bytes), que crescem do início da página para o final. No fim da página, encontra-se o slot array com apontadores para cada linha de dados (cresce do final da página para o início). O tamanho das linhas (de dados) de uma página não se pode "exceder" para outras páginas, pelo que, para armazenar tipos de dados que ocupem mais espaço (como imagens, texto muito longo e XML) são combinadas mais páginas. Os extents são usados para alocar espaço para tabelas e índices e são a unidade básica de gestão de memória. Cada extent contém até oito páginas contíguas, fazendo com que o seu tamanho máximo seja de 64KB. 12 3. ARMAZENAMENTO E FILE STRUCTURE 3.9. Mecanismo de partições O SQL Server 2005 disponibiliza ainda comandos para alterar, arquivar e eliminar par- tições. Existe também uma DMV para recolher informação sobre as partições, designada sys.dm_db_partition_stats. 15 4 Indexação e Hashing O SQL Server 2005 processa o acesso a dados de duas formas distintas. A primeira é através de um table scan onde todas as páginas, começando no inicio da tabela, são varridas extraindo assim a informação definida na query. A segunda é através de índices. Os índices são estruturas que foram desenhadas para aumentar a velocidade de acesso ao repositório de dados de um SGBD. Se simplificarmos a utilidade destes índices percebemos que funcionam como o índice de um livro - ao usarmos índices podemos encontrar rapidamente dados específicos sem termos que ler todo o conteúdo de uma tabela. Os índices não são estruturas obrigatórias, afectam a performance das queries mas não afectam a sua funcionalidade. No entanto, esta diferença de performance pode ter um impacto muito grande no sistema. Esta performance é aumentada reduzindo o trabalho de pesquisa às queries. Sem índices, é necessário ler todos os dados de uma tabela. De facto o uso dos índices está directamente ligado aos movimentos de I/O. Quando é executado um table scan, são gerados milhares de I/O, estas operações têm um custo elevado. Com o uso de índices são necessárias menos leituras e consequentemente a performance aumenta e a utilização de recursos diminui. Nesta secção explica-se como funcionam e como podem ajudar a melhorar a performance de um sistema. 4.1 Estrutura B-tree A estrutura de indexação é implementada por árvores B+. Uma árvore é constituída por uma raiz (root), ramos (branch nodes) e folhas(leaf nodes). A árvore começa com a primeira página do índice (nó raiz). Esta raiz contem a gama das chaves de pesquisa e os ponteiros para 17 4. INDEXAÇÃO E HASHING 4.2. Tipos de Índices 4.2 Tipos de Índices O SQL Server implementa índices de duas classes distintas -clustered index e non-clustered index. 4.2.1 Índices clustered Um índice clustered guarda os dados ordenados da tabela em sintonia com a estrutura da árvore. O acesso aos dados das tabelas indexadas por um índice clustered, tem obrigatoriamente que ser feito através do índice, pois os dados são guardados no próprio índice. Como os dados da tabela estão guardados segundo a ordem do índice, foi necessário implementar um mecanismo que permite fazer uma pesquisa global à tabela (Full Table Scan). Para que um índice seja lido em sequência num full table scan, os nós folha do índice estão ligados uns aos outros através de apontadores - cada nó tem um apontador para o nó anterior e para o nó seguinte formando assim uma lista ligada. Figure 4.3: Índice clustered Uma tabela pode ter apenas um índice clustered. A ordenação dos dados fisicamente é a mesma do índice. Uma tabela que tenha um índice clustered denomina-se clustered table. A 20 4. INDEXAÇÃO E HASHING 4.2. Tipos de Índices selecção das colunas para o índice clustered é muito importante e deve ter em conta a forma como é normalmente feito o acesso aos dados. Num tipo de índice devem ser consideradas as seguintes colunas: • Aquelas que são normalmente alvo de um acesso sequencial. • Aquelas que contêm um número elevado de valores distintos • Aquelas que são normalmente alvo de queries que usam operadores como BETWEEN, >, >=, <, <= ou dentro da cláusula WHERE. • Aquelas que são frequentemente usadas por queries que fazem joins ou agrupam os re- sultados. 4.2.2 Índices non-clustered Os índices non clustered não alteram a ordenação dos dados da tabela. Esta indepêndencia dos dados permite que sejam criados vários índices deste tipo sobre a mesma tabela - o SQL Server 2005 suporta até 249 indices non clustered sobre a mesma tabela. As folhas da árvore de um indice non clustered guardam um apontador para os dados da tabela e não os próprios dados como acontece num índice clustered. Embora os dados não estejam guardados nas folhas, sempre que é feita uma alteração na tabela é preciso reorganizar o índice. O facto do índice não conter os dados da tabela significa que o processamento da query necessita de um passo adicional para encontrar os dados. Uma tabela que tenha um índice non clustered denomina-se heap - os dados não têm uma ordem lógica e são gravados nas páginas que têm espaço disponível. A figura figura 4.4 mostra um diagrama simples de um indice non clustered definido sobre a coluna firstname. 21 4. INDEXAÇÃO E HASHING 4.2. Tipos de Índices Figure 4.4: Índice non clustered A utilização de índices non clustered deve ser considerada nos seguintes casos: • Queries que não retornam muitos resultados. • Colunas que são normalmente usadas na clausula WHERE e que retornam resultados exactos. • Colunas com alta densidade(bastante valores repetidos). É essencial ter uma noção clara sobre como é feito o acesso aos dados para serem criados índices non clustered. O SQL Server tem ferramentas como o SQL Server Profiler e o Database Engine Tuning Advisor que ajudam a avaliar o acesso aos dados e a determinar quais as colunas candidatas. É possivel definir um índice non clustered sobre um índice clustered. Neste caso, a pesquisa por uma chave do índice non clustered resultará numa chave de pesquisa a ser utilizada no índice clustered. Este processo é conhecido por bookmark lookup. 22 4. INDEXAÇÃO E HASHING 4.4. Criação de Índices 4.4.1 Argumentos do comando CREATE INDEX UNIQUE Cria um índice exclusivo em uma tabela ou exibição. Um índice exclusivo é aquele no qual duas linhas não podem ter o mesmo valor de chave de índice. Se um índice não for UNIQUE , poderão existir vários val- ores para a mesma chave de pesquisa. CLUSTERED | NON-CLUSTERED Define-se um índice como clustered ou non-clustered. Por omissão é non-clustered. Apenas é permitido definir um índice clustered por tabela. index_name É o nome do índice. Os nomes de índice devem ser exclusivos uma tabela ou view, mas não precisam ser exclusivos no banco de dados. object Especifica o nome da tabela ou view a ser indexada. column_name É a coluna, ou colunas, em que o índice se baseia. Es- pecifica dois ou mais nomes de coluna para criar um índice composto. As colunas devem ser definidas por ordem de prioridade de classificação, entre parênte- ses, depois do table_or_view_name. ASC | DESC Determina se a ordem de classificação é crescente ou decrescente. Por omissão é ASC. INCLUDE (column [ ,... n]) Especifica as colunas não chave que serão adi- cionadas ao nível folha do índice non clustered. relational_index_option Especifica as opções a serem a usadas ao criar o índice. 4.4.2 Opções do comando CREATE INDEX Abaixo é apresentada a lista das opções mais relevantes para o comando CREATE INDEX. PAD_INDEX = ON | OFF ON - A percentagem de espaço livre especificada pelo fillfactor é aplicada às páginas de nível intermediário do índice. 25 4. INDEXAÇÃO E HASHING 4.4. Criação de Índices OFF - As páginas de nível intermediário são preenchidas quase até ao máximo da sua ca- pacidade. FILLFACTOR = fillfactor Especifica a percentagem a preencher pelo Mecanismo de Banco de Dados deve preencher no nível folha de cada página do índice durante a criação ou recriação do mesmo. SORT_IN_TEMPDB = ON | OFF Especifica se os resultados da classificação temporários devem ser armazenados no tempdb. Por omissão é OFF. Esta opção pode aumentar a velocidade de criação do índice mas necessita de mais espaço de disco. STATISTICS_NORECOMPUTE = ON | OFF Especifica se as estatísticas de distribuição (usadas pelo optimizador da query) são recom- putadas. Quando é selecionada a opção ON as estatisticas não são recalculadas automatica- mente. O padrão é OFF. DROP_EXISTING = ON | OFF Especifica se o índice nomeado clustered ou non clustered pré-existente é descartado e re- criado. Se for seleccionada a opção ON o índice existente é descartado e recriado. O nome de índice especificado deve ser igual ao índice existente actualmente; no entanto, a definição de índice pode ser modificada. Por exemplo, pode ser especificadas colunas, ordens de classifi- cação, esquemas de partição ou opções de índice diferentes. O Padrão é OFF - será exibido um erro caso o nome de índice especificado já exista. ONLINE = ON | OFF Especifica se as tabelas subjacentes e os índices associados estão disponíveis para consultas e modificação de dados durante a operação de índice. O padrão é OFF. ALLOW_ROW_LOCKS = ON | OFF Especifica se é permitido bloquear tuplos durante o acesso ao índice. O padrão é ON. ALLOW_PAGE_LOCKS = ON | OFF Especifica se é permitido bloquear páginas du- rante o acesso ao índice. MAXDOP = max_degree_of_parallelism Substitui a opção de configuração grau máx- imo de paralelismo enquanto durar a operação de índexação. Usa-se MAXDOP para limitar o número de processadores usados numa execução de plano paralelo. O máximo é de 64 proces- sadores. O max_degree_of_parallelism pode ser: =1 - suprime a geração do plano de paralelismo. 26 4. INDEXAÇÃO E HASHING 4.5. Indexação e Hashing - Oracle 10g vs MS SQL 2005 >1 - Restringe o número máximo de processadores usados numa operação de índice ao número especificado. =0 (por omissão) - usa o número real de processadores, ou menos, dependendo da carga do trabalho de processamento. 4.5 Indexação e Hashing - Oracle 10g vs MS SQL 2005 O sistema Oracle disponibiliza um índice extra em relação ao Microsoft SQL Server 2005, esse é o grande trunfo do Oracle quando comparados os dois sistemas, o tipo de índice denomina- se Bitmap. Os Bitmaps são excelentes escolhas para colunas com pouca selectividade devido ao menor custo de armazenamento. Adicionalmente o Oracle fornece árvores B+ de leitura invertida que são bastante úteis e eficientes nas inserções sequenciais. Concluímos que no que no contexto da indexação, o sistema Oracle fornece mais mecan- ismos do que o Sql Server 2005, fornecendo inclusivamente uma alternativa à utilização de árvores B+, o que aumenta o leque de possibilidades para melhorar a eficiência das consultas. 27 5. PROCESSAMENTO E OPTIMIZAÇÃO DE Queries 5.1. Algoritmos para Junções custo de um índice non clustered que por sua vez é menor de que o custo de um full table scan. • A ordem de execução das operações de junção. • Que algoritmos são escolhidos para assegurar a melhor eficiência, baseada no custo das operações derivado das estatísticas armazenadas. O optimizador de consultas é considerado o cérebro de uma base de dados relacional, per- mite que ela opere de forma inteligente e eficiente. Uma base de dados relacional com um mecanismo sofisticado de optimização, tem mais probabilidade de conseguir executar uma con- sulta de forma mais rápida do que numa base de dados com um mecanismo simples de opti- mização. Quanto mais complexas forem as consultas, maiores as diferenças de performance. 5.1 Algoritmos para Junções O SQL Server 2005 faz a pesquisa de dados através de índices clustered, non clustered, pesquisa total no índice e full table scan. Seguidamente apresentamos os algorítmos usados para processar junções. 5.1.1 Nested Loop Nesta abordagem, existe uma divisão entre os dois operandos da operação de junção em tabela interior e tabela exterior. Para cada linha da tabela interior, é percorrida a tabela exterior e feita a comparação segundo as condições de junção definidas na query. As linhas que satisfaçam as condições são seleccionadas. Existem três tipos de nested loops: • Naive Nested-loop - Iteração completa do índice ou da tabela. • Index Nested-loop - Acesso aos dados através da técnica de bookmark lookup, utilizando um índice. • Temporary index nested-loop join - Utilização de um índice criado pelo optimizador para funcionamento apenas nesta consulta durante o seu tempo de optimização. 30 5. PROCESSAMENTO E OPTIMIZAÇÃO DE Queries 5.1. Algoritmos para Junções Figure 5.1: Plano com um nested loop 5.1.2 Merge O algoritmo merge join é mais eficiente que o nested loop join quando usado para volumes de dados maiores. Num merge join as duas tabelas são ordenadas. Para cada linha da tabela exterior, considera- se o grupo contíguo de linhas na tabela interior, cujo atributo da relação de junção coincide. Cada linha nas condições supracitadas, que respeite as condições da consulta, será adicionada ao conjunto resultado. Esta técnica pode ter um custo elevado devido às operações de ordenação necessária, no entanto, se os atributos de junção pertenceram à chave de um índice, ou se, por natureza, os túplos de pelo menos uma das tabelas estiver ordenado pelo atributo de junção, então o utilizador tende a escolhê-la. Se excluirmos as operações de ordenação a complexidade do algoritmo é baixa. 31 5. PROCESSAMENTO E OPTIMIZAÇÃO DE Queries 5.1. Algoritmos para Junções Figure 5.2: Plano com um merge join 5.1.3 Hash O algoritmo mais complicado é o hash join. Este algorimto é uma boa opção quando quer- emos usar volumes de dados maiores em que os inputs não estejam ordenados. Também é um algoritmo útil quando não há indices sobre as tabelas para aumentar a performance do join. Neste caso uma tabela de dispersão pode ser criada se o tamanho de uma tabela for significati- vamente mais pequeno do que o da outra. O hash join é executado em duas fases - construção e prova. Durante a construção são gerados dois inputs, o input de prova e o input de construção. A cada linha, existente no input de construção, será aplicado um algoritmo de dispersão sobre atributo a ser ligado. A tabela mais pequena funcionará como input de construção, consequentemente a tabela maior funcionará como input de prova. A tabela de dispersão será composta por listas ligadas que serão chamadas por pacotes de dispersão. Durante a execução, o input de prova é percorrido 32 5. PROCESSAMENTO E OPTIMIZAÇÃO DE Queries 5.4. Paralelismo • Fase 0 : O optimizador baseia-se num conjunto limitado de regras para geração de planos de execução para consultas com pelo menos quatro tabelas. Nesta fase são considera- dos apenas alguns algoritmos para processamento de junções e um número reduzido de ordens das mesmas é tido em conta. Após esta fase, caso seja encontrado algum plano de execução cujo custo seja inferior a um treshold previamente definido, a optimização termina. • Fase 1: São avaliadas mais algumas ordens de execução de junções e são aplicadas as regras de geração de planos de execução. Caso seja encontrado algum plano de execução cujo custo seja inferior a um treshold previamente definido ou for encontrado algum plano com um custo inferior a qualquer dos planos encontrados na fase 0, a optimização termina. • Fase 2: Caso o valor para o custo continue a ser superior ao treshold anteriormente referido, a optimização continuará, aplicando desta vez métodos que poderão ser menos eficientes em termos de complexidade temporal, como são exemplo a recombinação de vários índices, materialização de índices e de views. Esta fase tem um limite máximo de tempo de execução e devolve o melhor plano encontrado até então. 5.4 Paralelismo O SQL Server fornece consultas paralelas para optimizar a execução de consultas e oper- ações de índice para computadores que têm mais de um microprocessador (CPU). O SQL Server pode executar uma consulta ou uma operação de índice em paralelo usando vários threads do sistema operacional, a operação pode ser executada de forma rápida e eficiente. O resultado desta divisão é conhecido por nivelação de paralelismo. Esta nivelação é processada cada vez que a consulta é invocada. Os critérios escolhidos pelo optimizador quando determina o nível de paralelismo são os seguintes: • Número de processadores • Número de utilizadores activos concorrentes • Quantidade de memória disponível • Tipo de consulta • Avaliação da necessidade de utilização do paralelismo considerando o número de túplos a processar 5.5 Estatisticas Os algoritmos de optimização e de escolha de planos de execução baseiam-se em estatísticas dos dados e dos recursos do sistema (tipicamente transacções I/O). 35 5. PROCESSAMENTO E OPTIMIZAÇÃO DE Queries 5.5. Estatisticas O SQL Server armazena as seguintes métricas e informação: • Número de linhas numa tabela ou num índice. • Número de páginas de memória ocupadas pela tabela ou índice. • A altura em que as estatísticas foram feitas. • Número de linhas usadas para produzir o histograma e a densidade da informação. • Comprimento médio da chave. • Histogramas de coluna única, incluindo os degraus. • Uma string indicando se a coluna contém dados em forma caracteres Um histograma é um conjunto que contém até 200 valores de uma determinada coluna - pelo menos uma amostra dos dados, estão ordenados. A sequência ordenada é dividida no máximo em 199 intervalos, de forma a que a informação mais significantiva seja capturada. Normal- mente estes intervalos não têm a mesma dimensão. Os seguintes valores, ou a informação necessária para os obter, estão guardados em cada degrau do histograma. RANGE_HI_KEY Indica a fronteira superior de um degrau de um his- tograma. RANGE_ROWS Especifica quantas linhas estão incluídas na gama de um degrau. Este valor é inferior ao RANGE_HI_KEY do seu degrau mas é maior que o valor do degrau anterior. EQ_ROWS Valor que indica quantas linhas são iguais ao valor de RANGE_HI_KEY AVG_RANGE_ROWS Número médio de linhas por valores distintos dentro da gama de degraus. DISTINCT_RANGE_ROWS Especifica quantas chaves distintas estão dentro da gama. No Microsoft SQL Server 2005, os histogramas apenas são construídos para a primeira coluna do conjunto de chaves do objecto a ser estimado. 36 5. PROCESSAMENTO E OPTIMIZAÇÃO DE Queries 5.6. Pistas em consultas 5.6 Pistas em consultas Os mecanismos de armazenamento temporário e de optimização funcionam para grande parte das consultas, no entanto, ocasionalmente é necessário forçar um plano de execução a funcionar de uma forma particular para se conseguir resultados personalizados. As pistas são directivas que influenciam o comportamento do optimizador sem alterar a semântica da con- sulta ou dos resultados. Esta funcionalidade deverá ser utilizada com precaução e sob constante monitorização, pois força o optimizador a tomar em consideração certos mecanismos em preter- imento de outros. Como o optimizador de queries do SQL Server selecciona o melhor plano de execução para uma consulta, é recomendável que os developers e administradores experientes do SGBD só usem estas pistas apenas em último recurso. Existem três tipos de pistas que podem ser utilizadas no Microsoft SQL Server 2005, através da utilização da cláusula OPTION: • Join - Definidas para forçar o uso de algoritmos de junção. • Query - Definidas para forçar o comportamento das cláusulas INSERT, SELECT, UP- DATE e DELETE. • Table - Definidas para forçar o comportamento das formas de consulta sobre a tabela. Neste capitulo iremos descrever cada um deles. 5.6.1 Pistas do tipo Join As pistas deste tipo forçam o optimizador a usar uma estratégia de junção entre duas tabelas. • LOOP | HASH | MERGE - Especifica que a junção na consulta deve usar loop, hash ou mesclagem. O uso de LOOP | HASH | MERGE JOIN força uma junção específica entre duas tabelas. LOOP não pode ser especificado com RIGHT ou FULL como um tipo de junção. • REMOTE - Especifica que a operação de junção é executada no site da tabela direita. Isso é útil quando a tabela esquerda é uma tabela local e a tabela direita é uma tabela remota. REMOTE deverá ser usado somente quando a tabela esquerda tiver menos linhas do que a tabela direita. Se a tabela direita for local, a junção será executada localmente. Se ambas as tabelas forem remotas, mas de fontes de dados diferentes, REMOTE fará com que a junção seja executada no site da tabela direita. Se ambas as tabelas forem tabelas remotas da mesma fonte de dados, REMOTE não será requerido. Uma pista deste tipo não poderá ser usada quando um dos valores que são comparados no predicado de junção for lançado em um agrupamento diferente usando a cláusula COLLATE. Esta pista só pode ser usada somente para operações de INNER JOIN. 37 5. PROCESSAMENTO E OPTIMIZAÇÃO DE Queries 5.6. Pistas em consultas • RECOMPILE - Força a recompilação do plano de execução da consulta cada vez que a mesma for executada. Após a compilação o plano é revogado. • ROBUST PLAN - Força o optimizador de consulta a tentar criar um plano que trabalhe com o tamanho máximo de típlos. Os túplos podem ser tão grandes que, às vezes, o operador particular não consegue processá-las. Se isso ocorrer, o Mecanismo de Banco de Dados produzirá um erro durante a execução da consulta. • KEEP PLAN - Permite reduzir o número de vezes que o plano de execução de uma consulta é recompilado. • KEEP FIXED PLAN - Proibe o optimizador de recompilar o plano de execução devido a alterações estatísticas. • EXPAND VIEWS - Desactiva a utilização de indexed views no plano de execução. • USE PLAN N’xml_plan’ - Força a utilização do plano de execução descrito em XML. 40 6 Gestão de transacções e controlo de concorrência O modo como as transacções são geridas é fundamental para qualquer SGBD. As transacções são a unidade básica de trabalho do Microsoft SQL Server, consistindo num conjunto de oper- ações (sobre a base de dados) que deverão ser completadas como um todo ou, no caso de falha, deverão ser abortadas e rolled back, de modo a que nenhuma se realize. De modo a garantir a integridade dos dados, o SQL Server garante que as suas transacções satisfazem quatro características conhecidas como propriedades ACID: • Atomicidade. Todas as operações da transacção são reflectidas na base de dados ou nenhuma é. O mecanismo de gestão de transacções do SQL Server consegue determinar se uma transacção foi, ou não, completada com sucesso, desfazendo as modificações dos dados, se necessário. • Consistência. Após o término de uma transacção (admitindo que não existem outras transacções a ser executadas concorrentemente), todos os dados e estruturas internas de- verão ficar num estado consistente e reflectir, correctamente, a transacção que acabou de ser executada, quer esta tenha sido realizada com sucesso ou falhado, partindo do pressu- posto que a base de dados já se encontrava num estado consistente aquando da ocorrência da transacção. O SQL Server garante esta propriedade, permitindo, ainda, vários níveis de consistência. • Consistência. Após o término de uma transacção (admitindo que não existem outras 41 6. GESTÃO DE TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA 6.1. Tipos de transacções transacções a ser executadas concorrentemente), todos os dados e estruturas internas de- verão ficar num estado consistente e reflectir, correctamente, a transacção que acabou de ser executada, quer esta tenha sido realizada com sucesso ou falhado, partindo do pressu- posto que a base de dados já se encontrava num estado consistente aquando da ocorrência da transacção. O SQL Server garante esta propriedade, permitindo, ainda, vários níveis de consistência. • Durabilidade. Assim que uma transacção realiza commit, os seus efeitos reflectem- se de forma permanente na base de dados, mesmo que ocorram falhas de sistema. O transaction log do SQL Server garante esta propriedade, uma vez que, em caso de falha, o SQL Server usa o transaction log para refazer as transacções committed (que tenham sido afectadas pela falha) e fazer o roll back das transacções uncommitted. No entanto, cabe ao gestor da base de dados a tarefa de salvaguardar os ficheiros que compõem o transaction log (fazendo backups, por exemplo), uma vez que sem eles, poderá não ser possível recuperar a base de dados. 6.1 Tipos de transacções O SQL Server suporta quatro tipos (modos) de transacções. 6.1.1 Autocommit No modo autocommit, o modo por omissão do SQL Server, cada comando T-SQL (select, insert, update, etc.) é, automaticamente, committed quando termina ou rolled back quando falha, ou seja, cada comando é visto como uma transacção completa. Eis um exemplo: – início da transacção (implícito) UPDATE contas SET saldo = saldo + 200 WHERE iban = "PT50000201231234567890154" – fim da transacção (rollback ou commit - implícito) Se ocorrer algum erro durante a execução deste comando, este é rolled back, caso contrário, a acção é completada, sendo gravados os seus efeitos. 6.1.2 Transacções explícitas Numa transacção explícita é necessário definir explicitamente o início e fim da mesma. Este tipo de transacções é útil quando se quer que várias acções sejam todas completadas ou que nenhuma se realize, de modo a não se acabar com dados inconsistentes na base de dados. Em T-SQL são usados os seguintes comandos: • BEGIN TRANSACTION. Este comando marca o início de uma transacção. Pode ser usada a palavra abreviada TRAN em vez de TRANSACTION. 42 6. GESTÃO DE TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA 6.1. Tipos de transacções COMMIT TRANSACTION Assumindo que o erro é infrequente, pode ser mais eficiente fazer o roll back para o savepoint do que testar a validade do DELETE (se este custo for muito elevado). 6.1.3 Transacções implícitas No modo de transacções implícitas, o SQL Server inicia, automaticamente, uma transacção quando encontra determinados comandos SQL (a não ser que uma transacção já esteja em pro- gresso). Para terminar a transacção implicitamente iniciada, é necessário realizar um COMMIT TRANSACTION ou ROLLBACK TRANSACTION. Por omissão, o modo de transacções implícitas, encontra-se desactivado. Para activar ou desactivar este modo, pode-se usar o seguinte comando T-SQL: SET IMPLICIT_TRANSACTIONS ON | OFF Após activada esta opção, os comandos que iniciam, implicitamente, uma transacção são: ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE e UPDATE. Isto é útil para correr scripts que precisem de alterar dados dentro de transacções. Pode-se ligar o modo de transacções implícitas no início do script e desactivá-lo no fim do mesmo. 6.1.4 Batch-scoped No modo de transacções batch-scoped, as queries, executadas via MARS (ver a seguir), são executadas num ambiente dedicado. Apenas quando a execução de todos os trabalhos é terminada, é que as alterações ocorridas no ambiente dedicado são copiadas para o ambiente usual da base de dados. Multiple Active Result Sets (MARS) MARS é uma arquitectura, baseada em multiplexing, que permite às aplicações, que acedem à base de dados, executar múltiplas operações SQL numa mesma conexão (mas não paralela- mente). As várias operações são executadas de forma intercalada, embora a execução das mes- mas possa ser interrompida em pontos bem definidos. Na realidade, a maior parte dos comandos SQL são executados de forma atómica (i.e. têm que ser completados antes de a execução poder ser mudada para outro pedido MARS). Apenas alguns comandos podem ser interrompidos du- rante a sua execução (e.g. SELECT, FETCH, READTEXT, RECEIVE e BULK INSERT). Por exemplo, imaginemos que efectuamos um SELECT, que nos devolverá dois milhões de tuplos. A meio desta "devolução", é feito um INSERT via MARS. Uma vez que o comando SELECT pode ser interrompido, este pára e o INSERT é realizado. Como o INSERT não pode multiplexado, o INSERT terá que decorrer até ser terminado. Apenas quando isto acontecer é que o SELECT será retomado. Sem a arquitectura MARS, o INSERT apenas poderia ser realizado após o término da operação SELECT. De notar que, caso o INSERT demore muito tempo a ser realizado, o gestor de deadlocks do SQL Server fará com que a operação SELECT 45 6. GESTÃO DE TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA 6.2. Locks falhe (esta não ficará eternamente bloqueada). 6.2 Locks Os protocolos usados pelo SQL Server para garantir o isolamento das transacções são basea- dos em locks. Estes objectos permitem que múltiplos utilizadores acedam, simultaneamente e de modo sincronizado, a um mesmo conjunto de dados. A gestão dos locks é feita internamente pelo gestor de locks do SQL Server. Estes são adquiridos e libertados, de acordo com as acções dos utilizadores, sem que seja necessário programá-los explicitamente (embora seja possível "influenciar" o gestor de locks). A view sys.dm_tran_locks contém o registo de todos os locks existente no sistema, bem como outras informações pertinentes (recurso sobre o qual o lock está aplicado, modo do lock, etc.). Nesta secção examinaremos algumas propriedades do sistema de locking disponibilizado por este SGBD. 6.2.1 Níveis de granularidade O SQL Server está optimizado para determinar qual o tipo de lock a obter sobre um recurso - por exemplo, se for feita a inserção, remoção ou actualização de um único tuplo, é obtido um row level lock; page level lock para pesquisas parciais numa tabela; e table level lock para pesquisas em toda a tabela. A figura 6.2 mostra, em maior pormenor, os níveis de granularidade dos locks do SQL Server. Figure 6.2: Granularidade dos Locks À medida que o nível de granularidade do lock se vai tornando mais grosseiro, a concorrên- cia aos dados na base de dados diminui. Fazer lock a uma tabela completa impede o seu acesso 46 6. GESTÃO DE TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA 6.2. Locks por parte de outros utilizadores, embora diminua o overhead criado pela gestão de locks, uma vez que o número de locks em uso diminui. Em contrapartida, com um nível de granularidade fino - tal como row level - a concorrência aumenta, com outros utilizadores a terem permissão de acesso a diferentes tuplos, na mesma tabela, ao mesmo tempo. Mas, neste caso, também aumenta o overhead relacionado com a gestão de locks. É, no entanto, responsabilidade do SQL Server determinar, automaticamente, qual o nível de granularidade apropriado para cada tarefa (embora se possa indicar, explicitamente, qual o lock que se prefere usar, através do uso de hints). 6.2.2 Modos de lock O modo do lock especifica como um recurso sobe o qual o lock está aplicado, pode ser acedido por utilizadores/transacções concorrentes. Cada lock referido na secção anterior é adquirido num dos seguintes modos: Shared Este modo é adquirido automaticamente, quando se procede à leitura de dados (e.g. co- mando SELECT). Shared locks permitem a consulta do mesmo recurso, por várias transacções concorrentes, mas não permitem que este seja alterado. Normalmente, neste modo, os locks são libertados assim que os dados acabam de ser lidos, a não ser que o nível de isolamento ou hints alterem este comportamento. Exclusive Exclusive locks são usados em operações onde há alterações de dados, devido à utilização dos comandos INSERT, DELETE ou UPDATE. Quando um exclusive lock é aplicado a um re- curso, nenhuma outra transacção pode lê-lo ou modificá-lo (na verdade é possível ler o recurso, sem bloquear o exclusive lock, usando hints ou níveis de isolação). Update Este modo é usado quando o SQL Server executa uma operação de modificação de dados mas, primeiro, necessita de pesquisar a tabela para encontrar os recursos que irão ser modifica- dos. Se a transacção realmente fizer uma alteração (devido ao facto de terem sido encontrados tuplos que satisfazem a condição de pesquisa, por exemplo) o lock passa para o modo exclu- sive; caso contrário, passa para o modo shared. Este modo é importante para evitar deadlocks (situação abordada mais à frente). Intent Intent locks são usados para qualificar os 3 modos já apresentados. Ou seja, pode-se ter intent shared locks, intent exclusive locks e intent update locks (a lista completa encontra-se 47 6. GESTÃO DE TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA 6.3. Transacções de longa duração • SNAPSHOT. Isolamento Snapshot especifica que os dados lidos por um comando dev- erão apenas ver modificações que já tenham sido committed antes do início da transacção, ou seja, os comandos SQL "vêem" snapshots dos dados committed, tal como existiam no início da transacção. A opção ALLOW_SNAPSHOT_ISOLATION deverá estar a ON. • READ_COMMITTED_SNAPSHOT. Este nível de isolamento é semelhante ao nível Snapshot, excepto que, ao invés de usar um snapshot do início da transacção, é usado um snapshot do início de cada comando SQL. 6.2.4 Locking Hints Locking hints são keywords que podem ser usadas com os comandos SELECT, INSERT, UPDATE e DELETE, de modo a direccionarem o SQL a usar um tipo de locks preferido, numa determinada tabela (ou view), podendo se sobrepor a um nível de isolamento. Os locking hints que existem no SQL Server 2005 são: HOLDLOCK, NOLOCK, PA- GLOCK, READCOMMITTED, READCOMMITTEDLOCK, READPAST, READUNCOM- MITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, TABLOCK, TABLOCKX, UPDLOCK e XLOCK. 6.2.5 Deadlocks Sem intervenção, dois processos envolvidos num deadlock ficariam, indefinidamente, à es- pera um do outro. No entanto, o SQL Server detecta, automaticamente, situações de deadlock e resolve-as terminando o processo que considera menos dispendioso de fazer roll back, esti- mando a partir da quantidade de trabalho que já foi realizado pelo processo (este também recebe um erro 1205 que pode ser tratado pelo processo). Por defeito, o gestor de locks, de 5 em 5 segundos procura por situações plausíveis de ser deadlocks, variando este intervalo consoante a frequência com que deadlocks aparecem. Como o número de deadlocks, em geral, é pequeno, este sistema diminui a carga do sistema, na detecção de deadlocks. O utilizador também pode definir qual o processo a ser terminado, definindo os valores LOW, NORMAL (por omissão) ou HIGH para a opção DEADLOCK_PRIORITY. 6.3 Transacções de longa duração Tal como já foi referido, os metadados referentes às transacções são guardados no transac- tion log. No entanto, se uma transacção for de muito longa duração, poderá "entupir" o log, o que coloca a recuperabilidade da base de dados em risco. Uma solução passa por fazer backup do log e truncar a parte da qual já se fez o backup. O SQL Server disponibiliza um comando que permite consultar e obter informação sobre a 50 6. GESTÃO DE TRANSACÇÕES E CONTROLO DE CONCORRÊNCIA 6.3. Transacções de longa duração transacção activa mais antiga no sistema: DBCC OPENTRAN. Este comando devolve o identi- ficador do processo que iniciou a transacção, o identificador do utilizador, o nome da transacção, entre outras informações. Deste modo é possível identificar qual a transacção que, potencialmente, poderá causar problemas no futuro e terminá-la, caso seja necessário. 51 7. SUPORTE PARA BASES DE DADOS DISTRIBUÍDAS 7.2. Mirroring 7.2 Mirroring Figure 7.2: Mirroring Mirroring é uma solução que o SQL Server disponibiliza que tem como intuito garantir aa disponibilidade de uma base de dados. Para tal, por cada base de dados, irão existir dois servidores (7.2). Um deles irá funcionar como principal, prestando serviços aos clientes, e o outro será uma cópia de segurança da base de dados. Quando o servidor principal falha, as aplicações dos clientes conseguem recuperar rapida- mente, necessitando apenas de conectar se ao outro servidor. Existem dois tipos de estado para o servidor secundário: • "hot standby server": quando o servidor está sincronizado, onde não ocorrem perca de dados caso o servidor principal falhar. • "warm standby server": quando este não se encontra sincronizado e poderá levar a perca de dados. 7.3 Log Shipping 55 7. SUPORTE PARA BASES DE DADOS DISTRIBUÍDAS 7.4. Transacções Distribuídas Figure 7.3: log shiping Nesta solução, também com o intuito de manter uma alta disponibilidade do sistema, o servidor principal irá realizar automaticamente cópias de segurança dos registos de transacções da sua base de dados. Estas cópias de segurança irão ser posteriormente copiadas para outros servidores e aplicadas nas bases de dados secundárias. Opcionalmente poderá existir um ter- ceiro tipo de servidor, o monitor, que mantém o estado e histórico de todos os servidores e se houver alguma falha lançam um alerta (7.3)). Ao contrário da solução anterior, nesta as aplicações, no caso de falha do servidor, não tentam conectar-se automaticamente a um servidor secundário. Para que o sistema volte a funcionar os servidores secundários tem de ser disponibilizados online manualmente. 7.4 Transacções Distribuídas O SQL Sever também permite a realização de transacções distribuídas. Para que estas transacções possam ocorrer é necessário que o gestor de transacções de cada servidor tenha suporte para o Microsoft Distributed Transaction Coordinator ou Open Group XA. Para garan- tir a atomicidade das transacções é utilizado o protocolo 2-phase commit. 7.5 Acesso a outras Bases de Dados Como já referido, o SQL Server permite replicação de base de dados heterogénea, no entanto replicação por merge não é possível. O sistema de base de dados com maior suporte é o Oracle, neste é possível definir Publishers e Subscribers. 56 7. SUPORTE PARA BASES DE DADOS DISTRIBUÍDAS 7.6. Federação Para os restantes sistema só há suporte para Subscribers. Para que a funcionalidade de Publisher funcione num servidor Oracle é necessário que se crie um utilizador com privilégios de administrador de sistema; garantir privilégios de SELECT, de todas as tabelas que irão ser publicadas, ao utilizador previamente criado; instalar a aplicação de cliente do Oracle e o OLE DB provider no servidor onde se encontra o Distributor e final- mente configurar a base de dados Oracle como Publisher no Distributor. Para o caso Subscribers qualquer sistema de base de dados com suporte para OBDC ou OLE DB consegue subscrever, no entanto tem de usar push subscriptions. 7.6 Federação O SQL Server permite estabelecer base de dados federadas. Este tipo de base de dados consiste na fragmentação dos dados, neste caso por particionamento vertical, e dispersá-los por diversos servidores. Todo esse processo é transparente para os clientes. Esta acção ira levar a uma redução carga de processamento por servidor e a aumento de rendimento do sistema em geral. 57 Bibliography [1] MSDN, http://msdn.microsoft.com/en-us/sqlserver/default. aspx. [2] Kalen Delaney. Inside Microsoft SQL Server 2005: The Storage Engine. Microsoft Pres, 2006. [3] Kalen Delaney. Inside Microsoft SQL Server 2005: Query Tuning and Optimization. Microsoft Pres, 2007. [4] Ken England and Gavinl Powell. Microsoft SQL Server 2005 Performance Optimization and Tuning Handbook. Digital, 2007. [5] Darril Gibson. MCITP SQL Server 2005 Database Developer All-in-One Exam Guide (Exams 70-431, 70-441 & 70-442). McGraw-Hill, Inc., New York, NY, USA, 2008. [6] David Gornshtein and Boris Tamarkin. Features, strengths and weaknesses comparison between MS SQL 2005 (Yukon) and Oracle 10g databases. WisdomForce Technologies, Inc, 2004. http://www.wisdomforce.com. [7] Ross Mistry, Chris Amaris, and Alec Minty. Microsoft®sql server 2005 management and administration. Sams, Indianapolis, IN, USA, 2007. [8] Ray Rankins, Paul Bertucci, Chris Gallelli, and Alex T. Silverstein. Microsoft(R) SQL Server 2005 Unleashed. Sams, Indianapolis, IN, USA, 2006. [9] Jeffrey R. Shapiro. Microsoft SQL Server 2005: The Complete Reference. McGraw- Hill,Osborne, 2007. [10] Edward Whalen, Marcilina Garcia, Burzin Patel, Stacia Misner, and Victor Isakov. Mi- crosoft SQL Server 2005 administrator’s companion. Microsoft Press, Redmond, WA, USA, 2006. 61