Home

Trabalho de Sistemas de Base de Dados

1. DROP FULLTEXT INDEX ON table name Este comando remove um FULLTEXT da tabela table name Para mais detalhe sobre as convers es da sintaxe consulte http msdn microsoft com en us library ms177563 aspx Para mais detalhe sobre as convers es da sintaxe consulte http msdn microsoft com en us library ms177563 aspx 43 4 2 Estruturas de Dados O SQL Server para implementar ndices s implementa rvores B Em termos de organiza o as rvores em SQL Server est o organizadas em e CLUSTERED e NONCLUSTERED 4 2 1 CLUSTERED Indez 8 Nos indices CLUSTERED as folhas da rvore cont m os dados da tabela que estamos a indexar A raiz da rvore e os n veis interm dios cont m p ginas de valores indexados Cada valor indexado contem um apontador para outro n vel interm dio ou para as folhas As p ginas de cada n vel est o ligadas por uma double linked list Por defeito os ndices CLUSTERED t m s uma parti o Quando um ndice CLUSTERED tem v rias parti es cada parti o contem uma rvore B com os dados dessa parti o ia index id 1 root page Root node Intermediate level Index rows Index rows Index rows Leaf nodes data pages Figura 4 1 Clustered Index 44 4 2 2 NONCLUSTERED Indezx 24 Os indices NONCLUSTERED possuem a mesma estrutura que os indices CLUSTERED excepto nas seguintes diferen as e Os dados da ta
2. Page header Data row 2 Data row 3 Free space Row offsets Figura 3 2 Organiza o de p ginas Se uma linha guardar mais 8 KB de mem ria o SQL Server gere esse facto dinamicamente movendo colunas de dados overflow para p ginas de forma a guardar um apontador para a p gina inicial guardando os dados Esta gest o feita quer as linhas aumentem ou diminuam de tamanho 3 3 Extens es e filegroups Extens es s o a unidade b sica de espa o gerida pelo sistema sendo o seu tamanho de 64 KB Para que a gest o do espa o seja eficiente o SQL Server n o guarda extens es para tabelas com poucos dados Desta forma as p gi nas de cada extens o podem ser de objectos diferentes referenciando tabelas diferentes Uma nova tabela ou ndice normalmente armazenada numa extens o mista referencia v rias tabelas e medida que vai crescendo essa exten s o pode ser uniformizada se j tiver tamanho suficiente na sua cria o a extens o logo uniformizada Para gerir onde se guardam as extens es e o espa o livre de forma a diminuir o n mero de acessos a disco e n mero de vezes que as p ginas t m que ser reorganizadas o SQL Server tem duas estruturas de dados o GAM e o SGAM Cada um tem um bit para indicar se est a ser utilizado ou n o essa extens o GAM ou se uma extens o mista ou n o SGAM Existe tamb m o PFS para detectar o espa o livre e marca lo sendo mantido quanto 26
3. ent o o ndice apagado e criado o novo se estiver OFF ent o gerado um erro e ALLOW ROW LOCKS ON OFF Especifica se os bloqueios s linha s o permitidos e ALLOW PAGE LOCKS ON OFF Especifica se os bloqueios a p ginas s o permitidos e MAXDOP max degree of parallelism Especifica o n mero de processadores a serem usados no plano de execu o paralela O n mero m ximo de processadores 64 37 4 1 3 Cria o de ndices SPATIAL 12 32 O SQL Server 2008 suporta dados espaciais Isto inclui suporte para planos geom tricos os seja pontos linhas e poligonos num sistema de coordenadas euclidianas O tipo de dados geography representa objectos geogr ficos na su perf cie da Terra como um terreno Um ndice SPATIAL num campo geogra phy mapeia dados geogr ficos para um espa o bidimensional n o euclidiano Um ndice SPATIAL definido num campo de uma tabela que cont m dados geogr ficos Cada ndice SPATIAL refere se a um espa o finito A sintaxe para criar um ndice SPATIAL CREATE SPATIAL INDEX index name ON lt object gt spatial column name USING lt geometry grid tessellation gt WITH lt bounding box gt lt tesselation parameters gt n lt spatial index option gt n USING lt geography grid tessellation gt WITH lt tesselation parameters gt n lt spatial index option gt an lt sp oa _
4. 2 RECONFIGURE 5 2 2 Materializagao O SQL Server implementa materializa o Quando uma opera o requer que os dados de entrada sejam todos usados antes de retornar o resultado para o operador pai necess rio guardar esses dados para serem consumidos pela opera o Infelizmente n o encontr mos muito mais informa o acerca deste tema mas conseguimos saber que o SQL Server faz uso deste mecanismo 53 5 2 3 Pipelining O SQL Server implementa pipelining e usa pipelining para certos operadores como projec es selec es e jun es Isto permite que os resultados obtidos sejam enviados para o output muito mais rapidamente Infelizmente n o encontr mos muito mais informa o acerca deste tema mas conseguimos saber que o SQL Server faz uso deste mecanismo 54 5 3 Estatisticas 6 O SQL Server mant m estatisticas sobre indices de colunas actualizadas Esta op o est ligada por omiss o Para se poder desligar esta op o temos que fazer ALTER DATABASE universidade SET AUTO UPDATE STATISTICS OFF Para optimizar essas actualiza es o SQL Server usa um algoritmo para actualizar as estat sticas que executado quando existe um grande n mero de modifica es na determinada coluna ou quando o tamanho da tabela aumenta Por exemplo o SQL Server actualiza as estat sticas quando uma tabela que n o tinha tuplos passa a ter mais que 1 quando o tamanho da tabela menor que 500 tuplos
5. No SQL Server existem tr s tipos de ficheiros para guardar dados relativos s bases de dados Os ficheiros prim rios s o os mais importantes dentro de uma base de dados e t m apontadores para outros ficheiros dentro da base de dados sendo a sua extens o mdf Os ficheiros secund rios organizam os dados e uma base de dados pode n o ter ficheiros deste tipo dependendo das suas caracter sticas sendo tamb m poss vel ter v rios Estes ficheiros que gerem os metadados da aplica o servem como dicion rios de dados sua extens o ndf Os ficheiros de log guardam toda a informa o de logs que utilizada para recuperar a base de dados sendo necess rio pelo menos um numa A extens o ldf Estas extens es n o s o obrigat rias mas sim recomendadas No SQL Server a localiza o de todos os ficheiros numa base de dados est o guardados no ficheiro prim rio ou na base de dados master Esta uma base de dados que guarda toda a informa o de um sistema SQL Server Dados sobre informa es dentro do sistema contas configura o servidores s o guardados nesta base de dados bem como registos de todas as outras bases de dados e informa es de inicializa o sendo que desta maneira o SQL 24 Server nunca inicia se a base de dados master n o estiver operacional Os objectos do sistema s o guardados numa base de dados de recursos separada desta O SQL Server utiliza quase sempre o ficheiro de localiza o desta base
6. ndice cont m uma parti o que tem to das as suas p ginas estando essa parti o sempre s num filegroup Quando uma tabela ou ndice utiliza m ltiplas parti es os dados s o divididos hor 28 izontalmente para que os grupos de linhas sejam mapeados em parti es individuais baseadas numa coluna espec fica Essas parti es podem ser colocadas em mais que um filegroup na base de dados As tabelas ou ndices s o tratados como uma entidade l gica nica quando os dados s o acedidos Este particionamento torna as tabelas ou ndices mais f ceis de gerir pois os v rios conjuntos de dados podem ser geridos e acedidos rapidamente e com efici ncia Desta forma mais r pida a manuten o de certos tipos de dados bem como ir buscar esses mesmos dados sendo as diferen as de tempo consider veis Para criar uma parti o utiliza se o seguinte comando CREATE PARTITION FUNCTION partition function name input parameter type AS RANGE LEFT RIGHT FOR VALUES boundary value n E Existem duas maneiras distintas para se organizarem as p ginas de tabelas no SQL Server dentro de uma parti o Tabelas clustered s o tabelas que t m um ndice clustered estando as linhas de dados organizadas segundo a chave do ndice As p ginas em cada n vel do ndice incluindo as p ginas de dados no n vel da folha ligadas por uma lista duplamente ligada A nave ga o feita entre n veis determinada
7. o volta a ser AutoCommitted quando a transac o em modo explicito fizer commit transaction ou rollback transaction 6 1 2 Transac es Impl citas 36 No modo implicito de transac es n o se define o inicio da transac o ape nas o fim dela aplicando COMMIT Neste modo cada opera o inicia uma nova transac o A execu o desta efectuada ap s a transac o anterior terminar O conjunto de instru es DDL e DML que iniciam a transac o neste modo est o descritas na tabela abaixo ALTER TABLE INSERT CREATE OPEN DELETE REVOKE DROP SELECT FETCH TRUNCATE TABLE GRANT UPDATE 68 Exemplo de transac o em modo impl cito 69 6 1 3 Transacgdes Explicitas 35 Neste modo de transac o quem cria a transac o define explicitamente quando a transac o come a escrevendo BEGIN TRANSACTION e indica tamb m explicitamente quando acaba escrevendo COMMIT TRANSAC TION um modo til de executar uma transac o pois se uma transac o for efectuada v rias vezes ao longo do tempo o utilizador pode iniciar essa transac o que j est criada e assim n o precisa de escrever novamente todas as opera es Exemplo de cria o de transac o explicita BEGIN TRANSACTION actualizar nota declare aluno int declare Qcadeira int SET Galuno select alunos num_aluno from alunos where alunos nome LIKE Paula Antunes SET cadeira select cadeiras cod
8. Mixed extent 66 6 66 6 6 Sb table2 indexi index2 table2 table3 index3 table table3 Uniform extent 6000000 6 tablei tablei tablet tablet tablei tablet tablei tablei Figura 3 3 Tipos de extens es espa o est livre para estruturas de heap e p ginas de texto ou imagem Header page 0 Figura 3 4 Gest o de extens es Existem tamb m estruturas para gerir o espa o gasto por objectos nas tabelas e para marcar quais as extens es que foram modificadas aumentando em muito a efici ncia do sistema O tamanho dos ficheiros no SQL Server cresce automaticamente a partir do seu tamanho original crescendo este por um determinado incremento que pode ser escolhido Os filegroups s podem crescer quando todos os seus ficheiros est o cheios Limites de tamanho podem ou n o ser escolhidos sendo que sem limite um ficheiro cresce at n o haver espa o Por motivos de administra o tanto ficheiros da base de dados como objectos podem ser juntos em filegroups Existem os filegroups prim rios que cont m o ficheiro de dados prim rio e todas as p ginas das tabelas do sistema Os definidos pelo utilizador definem se com o FILEGROUP ao criar ou alterar tabelas definido um filegroup por defeito onde as p ginas ser o localizadas podendo este ser mudado apenas por membror de db owner Nenhum ficheiro pode estar em mais que um filegroup sendo que tabelas ndices ou grandes objectos de dados
9. Parallel plan optimization Generate execution plan Save plan in procedure cache Figura 5 2 Passos do Optimizer 48 5 1 Algoritmos implementados 5 1 1 Jun o O SQL Server implementa 3 tipos de algoritmos de jun o e Hash join e Merge join e Nested Loop join Hash Join 1 Em baixo descrevemos brevemente o algoritmo apresentando o pseudo c digo for all row R1 in the build table do calculate hash value on R1 join key s insert R1 into the appropriate hash bucket end for for all row R2 in the probe table do calculate hash value on R2 join key s for all row R1 in the corresponding hash bucket do if R1 joins with R2 then return R1 R2 end if end for end for A build table a tabela com menos tuplos criada uma hash table em mem ria O algoritmo percorre tuplo a tuplo da build table e calcula uma valor de hash atrav s da chave de jun o das tabelas e insere o respectivo tuplo no bucket corresponde ao valor de hash O algoritmo depois calcula para cada tuplo da probe table o seu valor de hash e verifica se existe um bucket associado e encontra o tuplo que combina Se for encontrado retorna o resultado dessa jun o para o output A imagem abaixo mostra os passos usamos no algoritmo 49 Start build phase Build in memory hash table Scan build input Compute hash key for a build input row Scan probe input for a probe input row Compute hash key for the prob
10. o T SQL SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED READ COMMITTED REPEATABLE READ SNAPSHOT SERIALIZABLE 73 6 3 1 READ UNCOMMITTED 27 Este o n vel mais baixo que o SQL Server suporta Usando este n vel de isolamento uma transac o pode ler modifica es que n o foram confirmadas committed pelas transac es concorrentes Significa que uma transac o poder ler dados sujos devido ao facto de locks partilhados serem mantidos e os locks exclusivos serem ignorados O efeito id ntico ao colocar a instru o NOLOCK em todos os comandos SELECT Este n vel s vantajoso se todas as transac es concorrentes modificarem dados diferentes em cada uma delas o que muito pouco prov vel de acontecer 6 3 2 READ COMMITTED 26 Este o n vel de omiss o do SQL Server Usando este n vel de isolamento uma transac o s l modifica es que foram confirmadas Neste modo uma transac o nunca l dados sujos Todas as leituras que tentem aceder aos dados durante a modifica o destes noutra transac o concorrente estas leituras ser o bloqueadas at haver um commit na transac o que modifica os dados 6 3 3 REPEATED READ 28 Este nivel de isolamento especifica que as instru es nao podem ler dados que foram modificados por uma transac o e que esta n o fez commit das altera es e tamb m especifica que quando uma transac o est a ler os dados nenhuma outra pode a
11. o do SQL Server para o Windows Mais tarde a Sybase mudou o nome do seu sistema de base de dados para Adaptive Server Enterprise para n o haver confus es com o sistema da Microsoft At 1994 o Sql Server da Microsoft trazia tr s avisos de copyright da Sybase como indica o de origem Desde da separa o v rias revis es ao sistema foram feitas de forma independente O SQL Server 7 0 foi reescrito baseado no c digo da Sybase Este foi sucedido por o SQL Server 2000 que foi a primeira vers o com uma variante para a arquitectura IA 64 A partir dessa existiram mais duas vers es do SQL Server a de 2005 e a de 2008 que a actual Versao Ano Release Name Codename 1 0 1989 SQL Server 1 0 OS 2 4 21 1993 SQL Server 4 21 WinNT 6 0 1995 SQL Server 6 0 SQL95 6 5 1996 SQL Server 6 5 Hydra 7 0 1998 SQL Server 7 0 Sphinx 1999 SQL Server 7 0 Plato OLAP Tools 8 0 2000 SQL Server 2000 Shiloh 8 0 2003 SQL Server 2000 Liberty 64 bit Edition 9 0 2005 SQL Server 2005 Yukon 10 0 2008 SQL Server 200 Katmai O SQL Server um sistema de gest o de bases de dados com uma enorme escalabilidade estando dispon vel para todo o tipo de m quinas desde port teis at servidores empresariais que TB de dados Existe tamb m a compatibilidade com o sistema PocketPC permitindo a sua utiliza o em PDA s e mecanismos com essa aplica o incorporada Este sistema te
12. o os dados podem ser distribu dos por v rias localiza es atrav s de liga es de rede A replica o no SQL Server utiliza uma met fora de funcionamento segunda a ind stria de publica o desta forma existem publicadores dis tribuidores subscritores publica es artigos e subscri es Desta maneira pode haver mais do que um publicadores tendo cada pub lica o um ou mais artigos O publicador pode distribuir os dados directa mente ou utilizando um distribuidor recebendo os subscritores publica es sobre o que subscreveram Um subscritor pode fazer altera es base de dados distribu da e um publicador pode alterar artigos de uma publica o A topologia de replica o explicada anteriormente define a rela o entre os servidores e c pias dos dados clarificando como os dados se movimentam dentro do sistema Agentes s o processos de replica o que copiam e movem dados entre publicadores e subscritores como exemplificado na imagem 82 Custom Application Custom Application Q Q Replication Agents Publisher pray Subscriber 0 0 9 A Articles g Articles Publication Distributor Publication Figura 7 2 Topologia de replica o Agora aprofundaremos o que cada uma destas fun es j divulgadas Um publicador uma inst ncia da base de dados que torna os dados dispon veis para outras localiza es atrav s de replica o podendo ter v rias publica es com determinad
13. schema name schema name table name Clausula ALTER A sintaxe simplificada da instru o ALTER ALTER TABLE database name schema name schema name table name ALTER COLUMN column name type schema name type name precision scale max xml schema collection COLLATE collation name ADD DROP ROWGUIDCOL PERSISTED NOT FOR REPLICATION WITH CHECK NOCHECK ADD lt column definition gt lt computed column definition gt lt table constraint gt e DROP CONSTRAINT constraint_name WITH lt drop clustered constraint option gt n GODUMN column name jer D end WITH CHECK NOCHECK CHECK NOCHECK CONSTRAINT ALL constraint name n ENABLE DISABLE TRIGGER ALL trigger name n SWITCH PARTITION source partition number expression TO target table PARTITION target partition number expression ld Para mais detalhes na altera o de tabelas consulte http msdn microsoft com en us library ms190273 aspx 2 1 2 Triggers O SQL Server suporta Triggers Um trigger um procedimento executado em resposta a algum evento Existem instru es para cria o altera o e remo o de triggers Para criar triggers existe a instru o CREATE TRIGGER trigger name ON table name FOR AFTER INSTEAD OF 10 INSERT UPDATE
14. Cria o de ndices XML 13 21 Cria o de ndices SPATIAL 12 32 Cria o de Indices FULLTEXT 10 5 18 33 Remo o de Indices 17 2 2200000 10 12 12 20 20 20 21 21 21 21 22 22 24 26 28 31 4 1 6 Remo o de Indices FULLTEXT 16 4 2 Estruturas de Dados 0 0 002000054 4 2 1 CLUSTERED Ideas as pie bo RG eS Rir 42 2 NONCLUSTERED Indez 24 Processamento e optimiza o de perguntas 6 5 0 3 Query Optimizer cccclcccccc 5 1 Algoritmos implementados Delile JUNGA sz sad Sh oe a a RSI SERENA ee Se ee Ah 5 2 Mecanismos para express es complexas 5 21 Pa ralelismol es Senge es asda nab cial EG We he wel e AU e 9 2 2 Materializa o anecs oa oaa E ohak aig 022020008 a 5 2 3 Pipelining sossa dosd edi ge So a Bree ee 5 3 Estat sticas 6 a e e ach aot ae oe Se eh eh 4 5 3 1 Tipo de Estimativas 5 3 2 Algumas considerag es 22 022 533 Exemplos aus ace saara g tnd gemia ko ee a eS 5 4 Caching de planos de execucgdo 5 2 0 002 5 4 1 Exemplo Adhoc Workload 5 4 2 Exemplo Prepared Workload 5 5 Mecanismos para ver planos de execu o Do Graficamente s ae idee Add eed ae eee AO RO S ASI Poa PormatosX MIs qu a temia iii AS APS CE Ag Tap ADE TES a ae DDD TERO ss stale ale a Lo a O ak DA ae SR Gest o
15. DROP FULLTEXT CATALOG ALTER FULLTEXT INDEX DROP FULLTEXT INDEX BACKUP RECONFIGURE CREATE DATABASE RESTORE CREATE FULLTEXT CATALOG UPDATE STATISTICS CREATE FULLTEXT INDEX 72 6 3 Protocolos de isolamento O isolamento uma propriedade fundamental das transac es Existem v rios n veis diferentes de isolamento e um n vel define a forma com as mod ifica es feitas por uma transac o s o vis veis nas transac es executadas concorrentemente Eles definem e controlam v rios factores relativamente aos bloqueios sobre os dados Por exemplo definem 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 e READ UNCOMMITTED e READ COMMITTED e REPEATABLE READ e SNAPSHOT e SERIALIZABLE O protocolo de isolamento por omiss o no SQL Server o READ COM MITTED Podemos modificar o modo de isolamento usado a seguinte in stru
16. Nome do servidor onde a tabela est guardada e database name Nome da base de dados que cont m a tabela e table name Nome da tabela onde ser o removidos tuplos e WHERE lt search condition gt Restringir os tuplos a serem removi dos Restringir segundo as condi es impostas na clausula WHERE e WITH lt table hint limited gt n Especifica as op es que podemos permitir para a tabela de destino Um exemplo disso especi ficar qual o recurso bloqueado aquando da inser o p e TABLOCK A sintaxe para esta op o a seguinte lt table hint limited gt t KEEPIDENTITY KEEPDEFAULTS FASTFIRSTROW HOLDLOCK IGNORE CONSTRAINTS IGNORE TRIGGERS NOWAIT PAGLOCK READCOMMITTED READCOMMITTEDLOCK READPAST REPEATABLEREAD ROWLOCK SERIALIZABLE TABLOCK 17 A cl usula FROM utilizada em todas as anteriores cl usulas portanto a sua defini o a seguinte Os argumentos mais importantes da cl usula FROM est o detalhados abaixo e lt table source gt Tabela usada na instru o T SQL e table or view name Indica o nome da tabela ou da vista usada para a instru o T SQL e WITH lt table_hint gt Especifica que o query optimizer usa uma optimiza o ou uma estrat gia de locks para a tabela referida As estrat gias de Lock permitidas s o NOLOCK READUNCOMMIT TED UPDLOCK REPEATABLEREAD SERIALIZABLE READ COMMITTED FASTFIRSTROW TABLOCK TABLOCKX PAGLOCK
17. ROWLOCK NOWAIT READPAST XLOCK and NOEXPAND A estrat gia de locks importante para transac es e lt join type gt Especifica qual o tipo de jun o a ser usado numa jun o se houver jun o 19 2 3 Tipos O SQL Server fornece muitas op es para cria o e armazenamento de dados Estes dados ter o que ter um tipo associado O SQL Server permite v rios tipos de dados e esses tipos est o divididos por categorias Categorias Num ricos Exactos Strings unicode Num ricos Aproximados Caracteres Bin rios Data e Hora Strings Outros tipos de dados Cada categoria tem v rios tipos de dados Abaixo est o listados os tipos de dados associados a cada categoria 2 3 1 Num ricos Exactos Tipo Descri ao int Inteiro de 32 bit bigint Inteiro de 64 bits smallint Inteiro de 16 bits tinyint Inteiro de 8 bits De referir que o SQL Server n o tem um tipo booleano Portanto esse valor booleano simulado por um tipo inteiro designado por Bit Se o valor for 1 ent o o valor booleano associado True e se o valor for 0 o valor booleano associado False Num ricos Aproximados Tipo Tamanho Precis o float 32 Bits 4 bytes 7 D gitos float 64 Bits 8 bytes 15 D gitos real 32 Bits 4 bytes 7 Digitos 2 3 2 Data e Hora e date e datetimeoffset e datetime2 e smalldatetime
18. Zaj Compiled Plan Prepared Figura 5 9 Prepared o plano de execu o foi utilizado por 3 vezes O numero de vezes que foi executada a mesma pergunta apesar de utilizar vari veis diferentes 63 5 5 Mecanismos para ver planos de execu o Usando o Microsoft Management Studio podemos ver os planos de execu o criados para uma pergunta Temos a oportunidade de os ver graficamente como texto e em formato XML 5 5 1 Graficamente SELECT alunos nome cadeiras nome inscricoes nota FROM ig alunos IN a inscricoes ON alunos num_aluno inscricoes num_aluno cadeiras ON inscricoes cod_cadeira cadeiras cod_cadeira where nota gt 10 HI Li Messages TR Execution plan Query 1 Query cost relative to the batch 100 SELECT alunos nome cadeiras nome inscricoes nota FROM alunos JOIN inscricoes ON alunos num aluno inscric a tel tel bj gt Nested Loops Nested Loops Clustered Index Scan Clustered Inner Join Inner Join alunos PK alunos 4720268007020 Cost 0 Cost 0 amp Cost 27 amp ty Clustered Index Seek Clustered Linscricoes PK_inscrico_C86FCF2 Cost 37 4 oy Clustered Index Seek Clustered cadeiras PK_cadeiras_E0 3A8260 Cost 36 Figura 5 10 Visualiza o gr fica 64 5 5 2 Formato XML OHH 0 lt ShowPlanXML xmlns http schemas microsoft com sqiserver 2004 07 showplan Version 1 1 Build 10 0 25
19. a sua repre senta o em string O m todo Null cria o valor Null dete tipo e o m todo IsNull verifica de o tipo NULL Com este tipos de dados podemos fazer queries mais complexas como por exemplo SELECT pl point ToString AS a p2 point ToString AS b pl point DistanceFromXY p2 point X p2 point Y AS distance FROM Points AS pl Points AS p2 where pl point lt gt p2 point ORDER BY distance DESC Que retorna as distancias de todos os pontos 8 2 XML 20 Como foi referenciado na seccao 4 1 2 o SQL Server tem suporte para dados do tipo XML e indexa los O tipo de dados XML permite guardar documento ou fragmentos XML no SQL Server Um fragmento XML uma inst ncia de XML que falta o elemento de topo Pode criar colunas e vari veis de XML Pode opcionalmente associar um XML schema a uma coluna um par metro ou uma vari vel de XML Os XML schema servem para validar o XML As limita es do tipo de dados XML s o e As Inst ncias de XML n o podem ultrapassar os 2GB e N o suporta o cast para text ou ntext tem de usar varchar maz ou nvarchar max Nos tipos de dados XML temos os seguintes m todos 39 e query XQuery Possibilita fazer uma query em xpath sobre a in st ncia do XML e value XQuery SQLType Faz o mesmo que o query mas retorna os dados no tipo especificado 98 e exist XQuery Verifica se a inst ncia XML contem a query em xpath e modify XML_ DML Modifica a in
20. agrega o definidos por o utilizador s o duas boas capacidades de expandir as capacidades de armazenamento e de consultar a base de dados e Melhor performance e escalabilidade O c digo NET compi lado faz com que seja mais r pido do que o TSQL Para activar esta op o tem de se executar os seguintes comandos sp configure show advanced options 1 GO RECONFIGURE GO sp configure clr enabled 1 GO RECONFIGURE GO 94 8 1 1 Stored Procedures em CLR Para fazer uma Stored Procedure em CLR primeiro temos de o programar em uma linguagem suportada pela a CLR O seguinte exemplo uma Stored Procedure em C que retorna um n mero aleat rio entre 0 e 1000 using System using System Data using System Data SqlClient using System Data SqglTypes using Microsoft SqlServer Server public partial class StoredProcedures Microsoft SqlServer Server SqlProcedure public static void random SqlContext Pipe Send new Random Next 1000 ToString J Depois de compilado imagine que o bin rio deste c digo se encontra em C testes MyAssembly ddl Para importar a DLL para o SQL Server temos de criar uma Assembly no servidor para isso usamos o seguinte comando CREATE ASSEMBLY Testes FROM CiNtestes MyAssembly ddl WITH PERMISSION SET SAFE go Depois s falta criar s Stored Procedure usando o seguinte comando CREATE PROCE
21. cadeira from cadeiras where cadeiras nome LIKE Programacao 1 UPDATE inscricoes SET nota 18 where inscricoes num aluno aluno and inscricoes cod cadeira Qcadeira COMMIT TRANSACTION actualizar nota 70 6 2 T picos avan ados sobre Transac es 6 2 1 Transac es Nested 37 O SQL Server suporta transac es nested em transac es explicitas isto significa que suporta cria o de transac es dentro de outras transac es Assim sendo existir o v rios n veis de transac es A transac o pai designada como outer transaction e a transac o filho designada por inner transaction A necessidade deste tipo de transac es importante para se poder definir procedimentos que cont m transac es e posteriormente poder executa los numa outra transac o tornando assim o c digo mais extens vel 6 2 2 Transac o de longa dura o 38 O SQL Server 2008 tem suporte para transac es de longa dura o Ele utiliza mecanismo de logging virtual para manter as actividades activas Este logging virtual cont m o primeiro log de registo da transac o Por vezes poder ser necess rio parar uma transac o e podemos fazer isso fazendo kill numero do processo Um dos grandes problemas deste mecanismo o facto de o log ficar demasiado grande e preciso tratamento caso isso aconte a 6 2 3 Savepoints 29 O SQL Server tem um mecanismo de savepoints em que permite guardar po
22. de dados excepto em algumas excep es onde utiliza o ficheiro prim rio Os ficheiros t m dois tipos de nomes os l gicos e os os sistema opera tivo Os l gicos servem para guardar informa es de uma frase de Transact SQL Os nomes de ficheiros de sistema operativo s o o nome do ficheiro f sico e a sua directoria seguindo as regras do sistema operativo corrente Desta maneira os ficheiros de dados e log do SQL Server podem ser guardados tanto em FAT como NTFS sendo recomendado o NTFS por quest es de seguran a Existem algumas limita es com o sistema de compress o de ficheiros do NTFS quanto a ficheiros de log e filegroups Quando h v rias instancias do SQL Server a correr na mesma m quina cada um tem uma directoria diferente por defeito para guardar os ficheiros dessas bases de da dos Uma base de dados de SQL Server pode guardar no m ximo 2 objectos e ter v rios ficheiros de sistema operativo com um tamanho m ximo de 22 TB P ginas de ficheiros de dados s o numeradas sequencialmente come ando no 0 para a primeira p gina de determinado ficheiro Cada ficheiro numa base de dados tem um identificador num rico nico que o diferencia dos outros sendo necess rio para identificar uma p gina numa base de dados o identificador desta e do respectivo ficheiro onde se localiza Primary Data File File ID 01 Figura 3 1 Organiza o de p ginas em ficheiros Exemplo de um ficheiro da numera o e
23. de execu o 46 Passos do Optimizer cccccccccs a 48 Hash dO sa a Shee se as Ra ARDER a a ES ce 2 50 Comando updatestats cccccccsc a 57 Comando autostats 0 6 a 57 Verificar se a cria o de estat sticas esta ligada 58 AUN OG 2s cs SR Abs a io di aah ach do A ROS cd O MTO Vea an 61 Adho garras Bake SD Tie RSS Re ge o Re BEE EE es 62 Prepare Sard ot ee eee Boe Phe Aa as ey a Ca 63 Visualiza o gr fica ep aiea pedea i a ee 64 DOME ee hoe ee e ag E eee ALR ee hee A as kee ads ea qa 65 Tetto umas Boe Sie Boke aoe Love as BARS RS CR E JE 66 DeGdlOCk es ss t o eect epee Ee SE go ok yh A Reso a Rete eh a 78 Sistema de bases de dados distribu das em localiza es distintas 82 Topologia de replica o 2 ee 83 Replica o transaccional 2 aaa 86 Replica o Merges ostos cirt adaa ee 87 Replica o snapshot 6 aaa aaa a 87 Arquitectura de liga o dos servidores 89 Capitulo 1 Introducao Este trabalho foi desenvolvido no mbito da disciplina de Sistemas de Bases de Dados onde a organiza o e implementa o deste tipo de sistemas foram estudadas ao detalhe Durante as aulas a mat ria dada foi acompanhada pelo estudo do sistema Oracle dentro dos temas falados O objectivo deste trabalho era analisar outro sistema de gest o de base de dados sem ser o Oracle 10g dentro da mat ria estudada no mbito da disciplina Um sistema de gest o de base de dados tem como intuito
24. deixar a gest o manipula o e organiza o de informa o dentro do sistema a cargo deste sendo f cil para o utilizador o acesso e todo o tipo de altera es a dados sem que seja necess rios grandes conhecimentos do funcionamento interno do sistema O sistema de base de dados que foi eleito por n s para analisarmos foi o Microsoft SQL Server 2008 Esta escolha foi feita por este sistema funcionar em Windows de maneira simples e pois t nhamos bastante informa o sobre as v rias tem ticas a desenvolver no trabalho 1 1 Estrutura Este trabalho est organizado para que a informa o demonstrada seja se quencial ou seja toda a informa o referida j foi explicada anteriormente ou est a ser desenvolvida nesse cap tulo Inicialmente daremos uma pe quena introdu o hist rica deste sistema de bases de dados e onde este aplic vel bem como um pequeno manual de instala o deste sistema De seguida ser descrita a cobertura que o SQL Server faz do standard de SQL especificando a sua cobertura ao n vel de DML e DDL Ap s esta explica o inicial iremos entrar dentro da especifica o e implementa o do sistema em concreto Come amos por explicar todo o mecanismo de armazenamento de dados e como estes s o organizados em ficheiros de maneira a serem ace didos alterados adicionados e removidos Seguidamente foram enumerados todos os mecanismos que o SQL Server proporciona para indexa o e hash ing bem como as e
25. e datetime e time 20 2 3 3 Strings Unicode e nchar e nvarchar e ntext 2 3 4 Strings Bin rias e Binary e Image e varbinary 2 3 5 Strings e char e varchar e text 2 3 6 Outros tipos de dados e cursor e timestamp e hierarchyid e uniqueidentifier e sql variant e xml e table Tabela geral de tipos de dados bigint binary bit char CLR cursor date datetime datetime datetimeoftset decimal float hierarchyid image int money nchar ntext numeric nvarchar real rowversion smalldatatime smallint smallmoney sql variant table text time timestamp int bigint smallint and tinyint varbinary varchar uniqueidentifier xml 21 Capitulo 3 Armazenamento e file structure Nesta seccao iremos debater toda a estrutura de armazenamento de dados que o SQL Server possui de forma a gerir a informa o que ir estar presente dentro de qualquer base de dados implementada Tamb m aprofundaremos as no es de como esses dados s o guardados em ficheiros do sistema de forma a permitir uma f cil gest o destes Estes t picos ser o explorados ao detalhe sendo especificadas todas as suas implementa es poss veis no SQL Server explicitando ainda a maneira como funcionam de forma a poder se compreender melhor as bases de ar mazenamento de dados deste sistema de bases de dados Inicialmente ir ser falada a gest o da mem ria e o buffer de gest o de dados com as suas pol t
26. folder log folder Qlog file log file Qlog file size log file size Qmin distretention min distretention Qmax distretention max distretention Qhistory retention history retention Qsecurity mode security mode login login password password Qereatemode createmode Qfrom scripting from scripting i Um subscritor uma base de dados que recebe dados replicados podendo receber v rias publica es de v rios publicadores Dependendo do tipo de replica o utilizado um subscritor poder tamb m comunicar altera es de dados ao publicador ou republica los noutros subscritores Configura se um subscritor com o seguinte comando sp_addsubscriber subscriber subscriber Qtype type login login password password commit_batch_size commit batch size Qstatus batch size status batch size Qflush frequency flush frequency Qfrequency type frequency type 3 Qfrequency interval frequency interval 5 Qfrequency relative interval frequency relative interval frequency_recurrence_ factor frequency recurrence factor frequency_subday frequency subday frequency_subday_interval frequency subday interval Gencrypted password encrypted password publisher publisher active s
27. identifica o de p ginas num ficheiro prim rio de 4 MB e um secund rio de 1 MB A primeira p gina de cada ficheiro um cabe alho que guarda informa es sobre os atributos do ficheiro e muitas outras p ginas no in cio do ficheiro tamb m podem conter outras informa es como aloca o de mapas Existe uma p gina de boot contendo informa o sobre o sistema que guardada tanto nos ficheiros de dados como nos de log A p gina a unidade de informa o do SQL Server e compondo estas os 25 ficheiros as opera es de leitura e escrita sao feitas a p ginas Uma extens o uma lista de 8 p ginas sendo que todas as p ginas est o guardadas em extens es de maneira a facilitar a gest o destas No SQL Server uma p gina tem 8 KB havendo portanto 128 p ginas por cada MB Cada p gina tem os cabe alhos j discutidos de 96 bytes incluindo informa o de identificadores Existem v rios tipos de p gina para guardar determinados dados p gina de dados de ndex de texto ou imagem e informa es relevantes j discu tidas Linhas de dados s o colocadas na p gina em s rie come ando depois do cabe alho O offset das linhas come a no fim da p gina e cada uma delas tem uma tabela que refer ncia a cada linha da p gina Estas entradas est o em ordem inversa das linhas da p gina sendo que cada entrada guarda o qu o longe do inicio da p gina est o primeiro byte dessa linha Microsoft SQL D Server Data Page
28. pode ser dividida e ser executada em paralelo isto se houver mais que 1 processador Para escolher o melhor plano o Optimizer recorre a dados estat sticos 5 0 3 Query Optimizer Existem v rias maneiras de executar a perguntar presente na Query Pro cessor Tree Essas maneiras s o calculadas e o custo de cada uma delas comparado com todas as outras para achar o plano de execu o de menor custo O Query Optimizer ou s Optimizer adopta v rias t cnicas para encontrar o melhor plano de execu o As t cnicas usadas s o e Igualar planos de execu o O SQL Server mant m em sua posse uma lista de planos triviais de execu es de perguntas Ele verifica se j tem na lista um plano de execu o para essa pergunta e se tiver criado um plano para a pergunta sem qualquer optimiza o e Multiplas Fases de Optimiza o Usando esta t cnica o optimizer tenta encontrar as melhores t cnicas de jun o e de ndices para essa pergunta A t cnica usa estatisticas associadas s colunas referidas nas cl usulas WHERE para avaliar a melhor maneira de como fazer as pesquisas usando ndices ou n o e quais as melhores estrat gias de jun o e Paralelismo Falado mais frente neste relat rio 47 Trivial Plan Match Trivial plan found No Y Simplification Multiple Optimization Phases 1 to n Configurations Cheap strategy found Yes Strategy cost lt parallel plan threshold
29. por chaves Esta a estrutura de uma rvore B com ndices Estes clusters de informa o s o criados como ndices normais discutido noutro capitulo Para tabelas sem ndice clustered s o usados heaps Nestes heaps as linhas de dados n o est o organizados em nenhuma ordem espec fica bem como as p ginas n o est o ordenadas e n o sendo guardadas numa lista duplamente ligada Um heap por defeito est s numa parti o mas se um heap tem v rias parti es cada parti o tem uma estrutura heap que guarda os seus dados O SQL Server utiliza p ginas IAM para percorrer o heap sendo que estas representam extens es na mesma ordem que elas aparecem no ficheiro Desta maneira para procura numa tabela apenas s o pesquisadas as p ginas IAM segundo a imagem seguinte De notar que os dados podem n o aparecer na ordem em que foram inseridos mas que o ficheiro pesquisado sequencialmente atrav s das p ginas IAM Para mais detalhe sobre as convers es da sintaxe consulte http msdn microsoft com en us library ms177563 aspx 29 index_id 0 first_iam_page Data rows Data rows Data rows Figura 3 7 Pdginas IAM em heaps Indices n o clustered organizam se em rvores B como os que falamos anteriormente diferen a entre estes que estes n o afectam a ordem das linhas de dados sendo que o n vel de folha contem ndices que cont m a chave e o valor imagem a seguir servir para exp
30. where cod curso i Ww lt Ed Results 13 Messages usecounts cacheobjtype objtype text Adhoc select from alunos where cod curso 1 Figura 5 7 Adhoc Como se pode verificar esta pergunta com exactamente as mesmas var i veis foi feita por 4 vezes Se fizermos esta mesma pergunta mas mudando a vari vel ent o ser criado um novo plano de execu o e guardado em cache 61 SELECT c usecounts c cacheobjtype c objtype t text FROM sys dm exec cached plans c CROSS APPLY sys dm exec sqi_text c plan_ handle t WHERE t text select from alunos where cod curso 12 SELECT c usecounts c cacheobjtype c objtype t text FROM sys dm exec cached plans c CROSS APPLY sys dm exec sql text c plan handle t WHERE t text select from alunos where cod curso 2 4 Compiled Plan Adhoc select from alunos where cod curso 1 usecounts cacheobjtype obitype text Figura 5 8 Adhoc 5 4 2 Exemplo Prepared Workload Neste exemplo fazemos a mesma pergunta do exemplo Adhoc O plano ser criado com as vari veis parametrizadas isto significa que quando fizermos uma mesma pergunta com outras vari veis o SQL Server ir utilizar este plano de execu o Portanto fizemos as seguintes 3 perguntas 62 WHERE cod curso 3 E o resultado este olhando apenas para a 2 linha Como se pode verificar usecounts cacheobitype obitype text 1 4 Compiled Plan Prepared
31. 31 o a lt BatchSequence gt Sa lt Batch gt lt Statements gt lt StmtSimple StatementText SELECT nome xD s xA FROM cfxD cfxA alunos sfxD cfxA where num aluno 1 lt StatementSetOptions QUOTED_IDENTIFIER true ARITHABORT true CONCAT_NULL_YIELDS_NULL true ANSI lt QueryPlan CachedPlanSize 8 CompileTime 1 CompileCPU 1 CompileMemory 64 gt lt Rel0p NodeId 0 PhysicalOp Clustered Index Seek LogicalOp Clustered Index Seek EstimateRows lt OutputList gt lt ColumnReference Database universidade Schema dbo Table alunos Column nome gt lt OutputList gt lt IndexScan Ordered 1 ScanDirection FORWARD ForcedIndex 0 ForceSeek 0 NoExpandHint 0 gt lt DefinedValues gt lt DefinedValue gt lt ColumnReference Database universidade Schema dbo Table alunos Column nome lt DefinedValue gt lt DefinedValues gt lt Object Database universidade Schema dbo Table alunos Index PK alunos 4720268 lt SeekPredicates gt lt SeekPredicateNew gt lt SeekKeys gt lt Prefix ScanType EQ gt lt RangeColumns gt lt ColumnReference Database universidade Schema dbo Table alunos Column lt RangeColumns gt lt RangeExpressions gt lt ScalarOperator ScalarString CONVERT_IMPLICIT int 1 0 gt lt Convert DataType int Style 0 Implicit 1 gt lt ScalarOperator gt lt Identifier gt lt ColumnReference Column 1 gt lt I
32. 59 a vari vel para joao o SQL Server iria guardar esse plano de execu o em vez de reutilizar o que estava em cache H maneira de parametrizar as vari veis e existem 3 t cnicas para o fazer que se inserem na categoria Prepared Workload Nesta categoria as vari veis das perguntas s o parametrizadas o que permite que possamos usar o mesmo plano de execu o para uma pergunta se mudarmos apenas as vari veis que est o parametrizadas Existem 3 m todos para submeter as perguntas desta forma e Armazenamento de procedimentos e Executando comando T SQL sp executesql e Preparar executar modelo Armazenamento de procedimentos Quando um procedimento cri ado e compilado criado um plano de execu o em que as vari veis s o parametrizadas Comando T SQL sp executesql E um mecanismo que permite sub meter uma ou mais perguntas com as vari veis parametrizadas fornecendo a reutiliza o dos planos de execu o como procedimentos Preparar executar modelo N o iremos descrever nem mostrar exemp los pois n o achamos relevante 60 5 4 1 Exemplo Adhoc Workload Para exemplificar o armazenamento de planos de execu o Adhoc fizemos a seguinte pergunta por 4 vezes SELECT FROM alunos WHERE cod curso 1 SELECT c usecounts c cacheobjtype c objtype t text FROM sys dm exec cached plans c CROSS APPLY sys dm exec sql text c plan handle t where t text select from alunos
33. 934196 aspx 100 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Microsoft Create xml index transact sql http msdn microsoft com en us library bb934097 aspx Microsoft Deadlocks http msdn microsoft com en us library ms177433 aspx Microsoft Detec o de deadlocks e termina o de deadlocks http msdn microsoft com en us library ms178104 aspx Microsoft Drop fulltext index transact sql http technet microsoft com en us library ms184393 aspx Microsoft Drop index transact sql http msdn microsoft com en us library ms176118 aspx Microsoft Full text search overview http msdn microsoft com en us library ms142547 aspx Microsoft Granularidade http msdn microsoft com en us library ms189849 aspx Microsoft Implementing xml in sql server http msdn microsoft com en us library ms189887 aspx Microsoft Indexes on xml data type columns http msdn microsoft com en us library ms191497 aspx Microsoft Instru es t sql permitidas em transac es http msdn microsoft com en us library ms191544 aspx Microsoft Modos de lock http msdn microsoft com en us library ms175519 aspx Microsoft Nonclustered index structures http msdn microsoft com en us library ms177484 aspx Microsoft Programming clr triggers http msdn microsoft com en us library ms179562 aspx Microsoft R
34. DELETE AS PROCEDURE RAISERROR GO Para alterar triggers existe a instrucao ALTER TRIGGER trigger name ON table name FOR AFTER INSTEAD OF lesion UPDATE DELETE PROCEDURE RAISERROR GO Para remover triggers existe a seguinte instruccao DROP TRIGGER trigger_ name Para informa es mais detalhadas sobre Triggers consulte http msdn microsoft com en us library ms189833 aspx 11 2 2 DML A DML ou Data Manipulation Language um conjunto de linguagens que manipulam dados em Bases de Dados Permitem que utilizadores insiram dados removam dados actualizem dados e obtenham dados de uma Base de Dados importante referir que o DML manipula apenas os dados e n o o modelo de dados e o modelo rela o entidades No SQL as principais instru es DML s o o SELECT INSERT UPDATE e DELETE 2 2 1 Principais Clausulas Cl usula SELECT SELECT statement WITH lt common_table_expression gt n lt query expression gt ORDER BY order by expression column position ASC DESC 0 er COMPUTE AVG COUNT MAX MIN SUM expression n Y expression n loo 5 lt FOR Clause gt OPTION lt query hint gt n lt query expression gt lt query specification gt lt query expression gt UNION ALL EXCEPT INTERSECT lt query specification gt lt que
35. DEPARTAMENTO DE INFORMATICA FACULDADE DE CIENCIAS E TECNOLOGIA UNIVERSIDADE NOVA DE LISBOA Trabalho de Sistemas de Base de Dados SQL Server 2008 2009 2010 Autores Diogo Andr Ribeiro Mour o n 31213 Jo o Andr Figueiredo Gon alves Saramago n 32461 Pedro Rafael Pereira Martins n 31227 Contetido 1 Introdu o 4 Indexa o 4 1 Sintaxe 4 1 1 4 1 2 4 1 3 4 1 4 4 1 5 1d Estrutura steps ak be ae Go Le ee De ee Eee 1 2 Hist ria e Aplicabilidade 2 2 aaa 1 3 Notas sobre instala o do sistema 2 Cobertura do SQL 21 DDE a ee e a a oe Wa SA BEE AT Ses 21 Tabelas scene ea Boas amp Se Mowe ee So as Boe QL TIPS cms 2A pes is QA a A ee ER Va 3 2 2 TDMA tena Syn pe Beh he ees Se ete a oe 2 2 1 Principais Clausulas Duo LIDOS uy aloe es do Mig 28 DIO Sos amp oy bin sor at E ei eats DO 2 3 1 Num ricos Exactos 0204 273 2 Data eHOr sis to re ote ee ye a go RO 2 3 3 Strings Unicode clic 2 3 4 Strings Bin rias cccccccl 22325 Stn SS bak we oie BA A A oa OD 2 3 6 Outros tipos de dados 3 Armazenamento e file structure 3 1 Gest o de memoria 0 00 0020 2 eee 3 2 Ficheiros e paginas 1 2 ee 3 3 Extens es e filegroups 2 0 00080500 e 3 4 Estrutura de dados e parti es 2 2 ee ee 3 5 Recupera o de dados 2 00 2000048 Cria o de ndices Relacionais 11
36. DURE random AS EXTERNAL NAME Testes StoredProcedures random go 95 8 1 2 Triggers em CLR O seguinte exemplo um Trigger em C7 que escreve a mensagem Trigger FIRED quando se faz um update tabela alunos using System using System Data using System Data SqlClient using Microsoft SqlServer Server public partial class Triggers Microsoft SglServer Server SqlTrigger Name Triggerl Target alunos Event FOR UPDATE public static void Triggerl SqlContext Pipe Send Trigger FIRED Se adicionar mos manualmente este trigger na base de dados temos de o atribuir tabela CREATE TRIGGER Triggerl ON alunos AFTER UPDATE AS external NAME Testes Triggers Triggerl GO 8 1 3 User Defined Types em CLR No SQL Server podemos criar User Defined Types Esta funcionalidade poder ajudar os programadores a guardar directamente estruturas de dados em base de dados O seguinte c digo mostra como criar um ponto em 2d 96 97 return Math Sqrt Math Pow iX x 2 0 Math Pow iY _y 2 0 As propriedades X e Y servem para ir buscar as componentes do ponto O m todo DistanceFromXY calcula a distancia entre o ponto e a coordenada dada O m todo Parse serve para quando um utilizador inserir um ponto como string ele converte para ponto Este est preparado para o formato x y O m todo ToString faz o contr rio passa de ponto para
37. Figura 5 5 Comando autostats 57 Abaixo segue um exemplo de como um administrador pode verificar se as op es de criar estat sticas e actualizar est o ligadas Se o resultado da consulta for 1 ent o porque est o ligadas select is auto create stats on from sys databases where name universidade select is auto update stats on from sys databases where name universidade Figura 5 6 Verificar se a cria o de estat sticas esta ligada 58 5 4 Caching de planos de execugao 5 Antes da execu o de uma pergunta o SQL Server cria um plano de execu o que minimiza os custos da sua execu o O SQL Server fornece um mecan ismo para guardar planos de execu o em cache para serem reutilizados mais tarde se poss vel Os planos n o s o guardados para sempre pois n o boa pol tica guardar planos de execu o que sejam usados e reutilizados durante um pequeno per odo de tempo e depois nunca mais sejam utilizados O SQL Server para descartar estes planos de execu o recorre a um m todo muito simples Ele atribui um valor a esse plano de execu o denominado de age Este valor atribu do no momento da sua cria o e o seu valor num rico depende da complexidade da pergunta ou seja uma pergunta simples tem um valor age muito mais pequeno que numa pergunta complexa Se uma pergunta reuti lizar um dos planos em cache o valor actual age incrementado pelo valor age que l
38. UPDATE STATISTICS Quando esta op o est ligada o optimizador de perguntas define quando as estat sticas podem estar desactualizadas e quando deve actualizar as estat sticas Ele actualiza as estat sticas com base na quantidade de novos tuplos ou na quanti dade de dados alterados Por omiss o esta op o est ligada e AUTO UPDATE STATISTICS ASYNC Quando esta op o est ligada o optimizador de perguntas actualiza as estat sticas de forma ass ncrona Por omiss o esta op o est desligada e EXEC sp updatestats Executando este comando o administrador da base de dados actualiza as estat sticas Esta costuma ser uma boa op o mas temos que ter em conta que isto obriga a que consultas com piladas ter o que ser recompiladas com base na actualiza o Conv m aplicar esse comando quando as perguntas de consulta come am a ficar muito lentas e EXEC sp autostats alunos Mostra a ultima actualiza o de estat s ticas acerca dos atributos da tabela alunos 5 3 3 Exemplos 56 No exemplo abaixo mostramos como um administrador pode estat sticas Para isso usa o comando sp updatestats actualizar as I EXEC sp updatestats 7 ua Ga Messages Updating dbo departamentos PK departam 2109190E03317Z3D update is not necessary O index es statistic s have been updated 1 did not require update Updating dbo alunos PK alunos 4720268007020F21 update is not nec
39. agem de erro 1205 aplica o A mensagem que retornada Your transaction process ID 52 was deadlocked on lock communi cation buffer thread resources with another process and has been chosen as the deadlock victim Rerun your transaction O retorno deste erro pode ser til para que numa aplica o a ocorr ncia de deadlocks seja transparente para o utilizador final 79 6 6 Granularidade 19 O SQL Server tem v rios n veis de granularidade para locks de recursos de tipos diferentes Bloquear recursos de n veis baixos de granularidade como por exemplo tuplos aumenta a simultaneidade e resultar numa sobrecarga maior devido exig ncia de manter v rios locks activos se houverem muitos locks sobre v rios tuplos Por outro lado bloquear recursos de n veis de granularidade elevados como por exemplo tabelas prejudicial em termos de simultaneidade pois nenhuma outra transac o pode operar sobre essa tabela se outra transac o tiver o lock sobre ela No entanto a sobrecarga menos pois menos locks ter o que ser mantidos Abaixo segue uma tabela que indica os recursos que se podem haver locks sobre eles Recurso Descri o RID Bloqueia um identificador de linha usado para fazer lock uma nica linha Key Bloqueia uma linha de um ndice usado para proteger um intervalo de chaves em transac es serializadas Page Bloqueia uma pagina de uma base de dados com
40. ansac o tentar fazer exactamente o mesmo e ter exactamente as mesmas consequencias Portanto T1 est bloqueado espera de T2 e T2 espera de T1 e a isto designamos de deadlock 6 4 4 Intent Locks Os intent locks locks intencionais s o usados para proteger locks em n veis de granularidade menores como p ginas ou linhas que possam ser requeridos por uma transac o de serem bloqueados exclusivamente por parte de outra transac o que bloqueia recursos de n veis superiores de granularidade O modo implementado custa de cada transac o sinalizar a inten o de fazer lock a algum recurso Assim evita se a possibilidade de haverem locks sobrepostos por transac es e com isso evitam se deadlocks 6 4 5 Schema Locks Este modo constituido por duas categorias e Schema modification e Schema Stability O Schema Modification usado quando uma opera o DDL Data Def inition Language efectuada Neste modo uma tabela bloqueada e por isso n o ser o permitidos acessos a esta tabela enquanto o lock persistir O Schema Stability usado no momento em que as queries est o a ser compiladas Durante este processo os locks de outras transac es n o s o bloqueadas inclu do locks exclusivos mas instru es DDL e DML que usem schema modification lock n o podem sem executadas sobre a tabela enquanto este modo estiver activo 6 4 6 Key range Locks O modo Key Range usado para bloquear index r
41. bela indexada n o s o guardados ou ordenados pela a ordem da sua chave do ndice e As folhas de um indice NONCLUSTERED s o referencias para as p gi nas de dados Um ndice NONCLUSTERED pode ser definido numa tabela ou view com um ndice CLUSTERED ou com Heap Cada valor num ndice NON CLUSTERED contem a chave e um localizador do valor O localizador aponta para dos dados que est o um ndice CLUSTERED ou numa Heap Para criar numa tabela este tipo de ndices esta tem de ter previamente um ndice CLUSTERED ia indexa gt 0 root page Ea Index rows Root node Nonclustered Leaf nodes index Heap or clustered index Figura 4 2 NONCLUSTERED Index 45 Capitulo 5 Processamento e optimiza o de perguntas 6 O SQL Server utiliza v rias t cnicas para minimizar o custo das perguntas queries e estas t cnicas determinam a estrat gia de processamento das perguntas O optimizador considera v rios factores para decidir sobre quais os indexes e opera es de jun o utilizar no processamento da pergunta factores esses que s o baseados em estat sticas e meta dados Minimizando o impacto desses factores no custo final da execu o da pergunta criado um plano que o plano que minimiza o custo de todas as opera es envolvidas no processamento da pergunta A seguinte imagem indica as t cnicas que o SQL Server utiliza para op timizar as perguntas e cria
42. ckup das bases de dados Existem tamb m v rios modos de backup desde backups parciais a totais dependendo das caracter sticas do sistema Com estes dois mecanismos a funcionar em conjunto o SQL Server permite a longevidade dos seus dados mesmo ap s situa es anormais 31 Capitulo 4 Indexa o O objectivo da Indexa o de melhorar o acesso aos dados Podemos indexar um ou mais campos de forma a que as querys corram da melhor forma poss vel Mas se o utilizador criar mal os seu indexes podemos ter uma reac o contraria a de atrasar as querys em vez de melhorar 4 1 Sintaxe 4 1 1 Cria o de ndices Relacionais 11 Um ndice em Sql Server tem a seguinte sintaxe CREATE UNIQUE CLUSTERED NONCLUSTERED INDEX index name ON lt object gt column ASC DESC n INCLUDE column name n WHERE lt filter predicate gt WITH lt relational index option gt n ON partition scheme name column name filegroup name default FILESTREAM ON filestream filegroup name partition scheme name NULL O ndice criado com o nome index name sobre uma tabela ou view lt object gt Os campos onde v o ser aplicados o ndice s o os com o nome column e com a ordem ASC ou DESC Podemos criar ndices com 16 colunas e o tamanho dos valores dos campos n o pode ultrapassar os 900 bytes Os campos com tipo de dados large object LOB c
43. dados do SQL Server um gestor de recursos e um gestor de transac es o MS DTC Microsoft Distributed Transaction Coordenator 90 Uma transac o dentro de uma nica inst ncia do mecanismo de base de dados que abrange duas ou mais bases de dados na verdade um gestor de transac es gerindo a transac o internamente sendo uma transac o local para o utilizador Na aplica o a transac o distribu da controlada de maneira parecida a uma transac o local sendo que no fim da transac o a aplica o pede transac o para estar commited ou rolled back Um commit distribu do tem que ser gerido de maneira diferente pelo gestor de transac es de maneira a diminuir o risco de que uma falha de rede possa resultar em commit nuns gestores e rolled back noutros numa determinada transac o Para isto acon tecer o commit gerido em duas fases implementado um protocolo de duas fases Na fase de prepara o quando um gestor recebe um commit este envia um comando de prepara o para todos os gestores de recursos envolvidos na transac o Cada um deles faz tudo o que necess rio para fazer com que a transac o dure e todos os buffers que guardam imagens de log s o guardados em disco sendo que quando cada gestor acaba esta fase retorna se a fase de prepara o foi sucedida ou n o ao gestor de transac es De seguida vem a fase de commit em que se o gestor de transac es receber prepara es de sucesso de
44. das para o publicador e validadas e caso seja aceite o processo decorre normalmente podendo haver uma resolu o de conflitos O m todo da altera o imediata faz com que as altera es s podem ser feitas se o publicador as aceitar imediatamente e caso sejam aceites ser o propagadas para todos os subscritores Os subscritores t m que estar sempre ligados ao publicador para que estas altera es sejam feitas nele utilizando o protocolo de duas fases referido anteriormente Existe a possibilidade de ter os dois modos ao mesmo tempo formando um terceiro modo Este faz com que em caso de falha da liga o ao publicador seja utilizado as altera es em fila e caso isto n o acontece as altera es imediatas Este modo de actualiza o pode ser especificado aquando da cria o da subscri o utilizando as op es deste comando referido anteriormente al terando o seu update mode 92 7 10 Formas restritivas de uso O SQL Server disp e de formas restritivas de uso para as suas bases de dados neste caso distribu das Um snapshot de uma base de dados uma vista s de leitura de uma base de dados raiz sendo que podem existir muitos snapshots para uma base de dados estando na mesma inst ncia desta Cada um consistente nas transac es com a base de dados na altura em que foi criada persistindo at ser apagada pelo administrador da base de dados Ao contr rio do comportamento normal de uma base de dados uma base de dados s
45. de transac es e controlo de concorr ncia 6 1 Modos de transac o 2 a 6 1 1 Transacgdes AutoCommitted 34 002 6 1 2 Transac es Impl citas 36 002 6 1 3 Transac es Explicitas 35 00 6 2 T picos avan ados sobre Transac es 6 2 1 Transac es Nested 37 00 6 2 2 Transac o de longa dura o 38 02 Bavepoimits 29 a a x ic 2 a epa RE E Pad 6 2 4 Instru es T SQL permitidas em Transac es 22 6 3 Protocolos de isolamento 2004 6 3 1 READ UNCOMMITTED 27 us eta aa ee 6 3 2 READ COMMITTED 26 casas pao bee as 6 3 3 REPEATED READ 28 sua bla pe bbe Roe OG 6 3 4 SNAPSHOTS ce para ese a bas EMA gd 6 3 5 SERIALIZABLE SO us eus sua ee Rae e e A SS 6 3 6 Row versioning cccclcll a 6 4 Modos de Lock 23 sans a ti E Abi dd Ed ana 0 41 Shared lockers eee posso Ao nen AS ta GUS Dota ee a 6 4 2 Exclusive Locks 2 0 02 0 02 02 2000 64 37 Update Locks s cas eo bo oe E EE 6AA Intent Locks sus 05 der le ei ate ee Bes 6 4 5 Schema Lockie me uid mp e Mace Nee ee ales 6 4 6 Key range Locks 00 6 5 Deadlocks 14 sia ed Re ae ee Ba 6 5 1 Detec o de deadlocks e termina o de deadlocks 15 6 5 2 Detec o de deadlock e escolha da vitima 6 6 Cranularidade 19 am veo ad Wd dw ek ee we we Suporte para bases de dados distribuidas 7 1 Base de Dados homog nea
46. de utilizar esse espa o A informa o da base de dados est guardada em ficheiros que est o em disco e como a leitura destes muito mais lenta que a leitura da mem ria Como um dos principais objectivos do SQL Server minimizar o I O do disco este constr i um buffer de cache em mem ria para guardar p ginas lidas da base de dados O SQL Server tem uma grande preocupa o em diminuir as leituras e escritas f sicas do disco por gastarem muitos recur sos tendo dois objectivos principais impedir que o buffer cres a tanto que o sistema fique sem mem ria e minimizar o I O f sico da base de dados maximizando o tamanho do buffer de cache Para tornar estes objectivos poss veis o SQL Server tem um sistema de gest o do buffer referido anteriormente sendo fulcral para ter a efic cia nos objectivos tra ados A gest o do buffer consiste em dois mecanismos o gestor do buffer para aceder e actualizar p ginas e o buffer de cache para reduzir o I O com os ficheiros da base de dados O buffer de gest o uma p gina de 8 KB em mem ria do tamanho dos dados como veremos mais frente dividindo assim o buffer de cache em v rias p ginas de 8 KB O gestor do buffer controla as fun es de leitura de dados ou p ginas de ndex dos ficheiros de disco da base de dados para o buffer de cache e as escritas das p ginas modificadas para disco Uma p gina fica no buffer de cache at que o gestor precise de espa o para ler mais dados send
47. dentifier gt Figura 5 11 XML 65 5 5 3 Texto Go SELECT alunos nome cadeiras nome inscricoes nota FROM alunos JOIN inscricoes ON alunos num aluno inscricoes num aluno JOIN cadeiras ON inscricoes cod cadeira cadeiras cod cadeira WHERE inscricoes nota gt 10 GO SET SHOWPLAN TEXT OFF Go 4 m pieeessesesensssnssssnensonensnonssssssnsasnsnsssensnsussasnsnssnsnsnenssusnsscensuseusnenssnsnsnsnssasasssensnanssasesnsensnsnaneseseseenly Rascesosseseessssssssesenssssecsessessesresssssnsssssnsenssnssnsensssseasenssessaseosssssassnsessessosuesssaceseesssscasssssasecsenseasnes Nested Loops Inner Join OUTER REFERENCE Clustered Index Scan OBJECT Juniversidad Clustered Index Seek OBJECT Juniversidad Clustered Index Seek OBJECT universidade of WH Figura 5 12 Texto 66 Capitulo 6 Gestao de transaccoes e controlo de concorr ncia Uma transac o uma sequ ncia de opera es sobre dados existentes numa base de dados em que essa sequ ncia de opera es do ponto de vista l gico uma nica opera o Para isto ser garantido a transac o tem que obedecer a 4 propriedades Atomicidade Consist ncia Isolamento e Durabilidade Frequentemente designadas como propriedades A C 1 D Atomicidade Para uma transac o ser considerada at mica todas as opera es realizadas pela transfer ncia s o executadas com sucesso ou nenhuma delas execu
48. e aumenta pelo menos para o dobro e quando uma tabela que tenha mais que 500 tuplos aumenta em 20 da sua capacidade Nestas situa es as estat sticas s o actualizadas As estat sticas tamb m podem ser actualizadas assincronamente Isto as consultas s o compiladas com estat sticas existentes mesmo que as es tat sticas estejam desactualizadas O query optimizer poder escolher um plano de execu o com maior custo se as estat sticas estiverem desactual izadas na compila o da consulta Se essa consulta provoca actualiza o das estat sticas essas estat sticas poder o ser actualizadas depois de a consulta ser executada Se fosse feito de forma s ncrona isto n o aconteceria ou seja o optimizador procuraria sempre actualizar as estat sticas antes da execu o da pergunta A op o de actualizar as estat sticas de forma assincrona est desactivada por omiss o O SQL Server tamb m cria estat sticas para atrib utos sem ndices associados Essa op o est activa por omiss o mas poder ser desactivada aconselh vel ter essa op o activa S faria sentido de sactivar se n o valesse a pena tal como fazer sempre consultas diferentes n o habitual 5 3 1 Tipo de Estimativas O SQL Server guarda v rias informa es relativamente s estat sticas de uma tabela Ele guarda informa o sobre quantas linhas tem a tabela a ltima data de actualiza o das estat sticas o tamanho m dio da chave a de
49. e heterog nea t 2 Replica o sic damas mo E re ao we Bk ae he oe Bt ee 2 73 Fragmenta o osa a e se ee we ee ee a a 7 4 Bases de dados Heterog neas 2 7 5 Transpar ncia de dados 22 0280085 7 6 Transac es distribu das 004 7 7 Resolu o de conflitos 2 0 02020 008 T8 bock el bais of gies Yb Drop del eo BAS ee 7 9 Propaga o de actualizagdes 2 2 ee ee 7 10 Formas restritivas de uso 02 02 0004 Outras caracteristicas do sistema estudado 8 1 Integra o com o CLRI7 25 9 020 8 1 1 Stored Procedures em CLR 8 1 2 Triggersem CLR 2 2 2 2 02 0000 2 8 1 3 User Defined Types em CLR 8 2 eM 20 te eet oes ey yeh Ae gdh HR RE he ye AE ak Lista de Figuras 3 1 3 2 3 3 3 4 3 9 3 6 3 7 3 8 4 1 4 2 5 1 5 2 5 3 5 4 5 5 5 6 5 7 5 8 5 9 5 10 5 11 5 12 6 1 7 1 7 2 7 3 7 4 T 5 7 6 Organiza o de p ginas em ficheiros 24 25 Organiza o de p ginas 1 2 ee ee ee 26 Tipos de extens es 1 A 27 Gest o de extens es 6 anana a 27 Exemplo da organiza o de uma BD 04 4 28 Arquitectura de uma tabela cc ccccccc 28 P ginas IAM em heaps cccclcll ee 30 ndices n o clustered 2 0 ee aaa 30 Clustered Index sota i dilda ai ea e oane a a a u a aia a 44 NONCLUSTERED Index 0 aaa aaa 45 Passos para cria o dos planos
50. e input row Scan corresponding hash bucket in hash table Row match found matched row For all rows in probe input Hash bucket for hash key exists Y Create hash bucket in hash table Yes lt 3 Insert row in the hash bucket No Figura 5 3 Hash Join Merge join 2 O SQL Server implementa o Merge join de igual forma ao Oracle O algoritmo merge join requer que ambas as tabelas estejam ordenadas pelo atributo de jun o O algoritmo utiliza as duas tabelas em simult neo na medida em que obt m o primeiro tuplo de cada tabela de input e compara se o atributo de jun o de cada tuplo igual Se for ent o retorna a jun o dos 2 tuplos e obt m o 2 tuplo da 2 tabela de input e repete os passos acima descritos Se n o forem iguais e se o 1 tuplo da 12 tabela de input for menor que o 1 tuplo da 2 tabela de input ent o obt m o pr ximo tuplo da tabela de input e repete os passos atr s Se for maior ent o o obt m o pr ximo tuplo da 2 tabela de input Para melhor compreens o em baixo segue o pseudo c digo do algoritmo 50 get first row R1 from input 1 get first row R2 from input 2 while not at the end of either input do if R1 joins with R2 then return R1 R2 get next row R2 frim input 2 else if R1 lt R2 then get next row R1 frim input 1 else get next row R2 frim input 2 end if end if end while De real ar que as tabelas podem estar ordenadas pela
51. ead committed http msdn microsoft com en us library ms173763 aspx Microsoft Read uncommitted http msdn microsoft com en us library ms173763 aspx Microsoft Repeated read http msdn microsoft com en us library ms173763 aspx Microsoft Savepoints http msdn microsoft com en us library ms178157 aspx 101 30 Microsoft Serializable http msdn microsoft com en us library ms173763 aspx 31 Microsoft Snapshot http msdn microsoft com en us library ms173763 aspx 32 Microsoft Spatial indexing overview http technet microsoft com en us library bb964712 aspx 33 Microsoft Stopwords and stoplists http msdn microsoft com en us library ms142551 aspx 34 Microsoft Transac es autocommitted http msdn microsoft com en us library ms187878 aspx 35 Microsoft Transaccdes explicitas http msdn microsoft com en us library ms188317 aspx 36 Microsoft Transac es impl citas http msdn microsoft com en us library ms188317 aspx 37 Microsoft Transac es nested http msdn microsoft com en us library ms189336 aspx 38 Microsoft Transac o de longa dura o http msdn microsoft com en us library ms366331 aspx 39 Microsoft xml data type methods http msdn microsoft com en us library ms190798 aspx 102
52. endo uma estrutura organizacional bastante dirigida possibilita a protec o de dados em caso de perdas melhora a performance do sistema etc Contudo para que estas bases de dados funcionem de maneira correcta s o necess rios mecanismos complexos de maneira a suportar todas as pro priedades pretendidas Seguidamente iremos descrever estes mecanismos no SQL Server bem como algumas propriedades do servi o que este sistema disponibiliza e o modo de utiliza o de cada mecanismo atrav s dos coman dos disposi o 7 1 Base de Dados homog nea e heterog nea O SQL Server tem suporte tanto para bases de dados distribu das homog neas como heterog neas De seguida ser o explicado como o SQL Server suporta ambas as op es sendo que a replica o a maneira utilizada para distribuir os dados por v rias bases de dados SQL Server ou seja distribui o ho mog nea Na distribui o heterog nea h liga es a outras bases de dados que ser o explicadas e como configur las 81 Montreal Boston Figura 7 1 Sistema de bases de dados distribu das em localiza es distintas 7 2 Replica o Para suportar as capacidades de distribui o de dados o SQL Server utiliza sofisticados m todos de replica o Esta replica o um conjunto de tec nologias que permite copiar e distribuir dados e objectos de base de dados em v rias sincronizando as para manter a consist ncia dos dados Ao usar replica
53. ermitido inserir novos valores com chaves inclu das na gama de chaves obtendo se um efeito muito negativo na concorr ncia das transac es 6 3 6 Row versioning Os n veis de isolamento snapshot snapshot e read committed snapshot usam um mecanismo de row versioning Este mecanismo mant m vers es de cada linha modificada Cada vez que uma linha modificada o SQL Server guarda uma vers o dos dados modificados mas n o confirmados numa base de dados chamada tempdb Cada vers o guardada marcada com um n mero de sequ ncia da transac o que a possui Este mecanismo faz com que as leituras n o fa am locks aos dados a outras transac es To 6 4 Modos de Lock 23 Os modos de lock determinam como os recursos s o acedidos pelas transac es executadas concorrentemente O SQL Server suporta e utiliza 6 modos de locks Eles s o e Shared S e Exclusive X e Update U e Intent e Schema e Key range 6 4 1 Shared locks Os Shared Locks locks partilhados permitem que transac es concorrentes fa am leituras ao mesmo recurso tuplo tabela etc Usam um controlo de concorr ncia pessimista Enquanto pelo menos uma transac o tiver um lock partilhado a um dado recurso nenhuma outra transac o poder modificar esses recursos Os locks partilhados sobre um recurso s o libertados quando uma opera o de leitura executada 6 4 2 Exclusive Locks Os Exclusive Locks locks exclusivos s o usados em ope
54. especi ficar qual o recurso bloqueado aquando da inser o p e TABLOCK A sintaxe para esta op o a seguinte lt table hint limited gt KEEPIDENTITY KEEPDEFAULTS FASTFIRSTROW HOLDLOCK IGNORE_CONSTRAINTS IGNORE _TRIGGERS NOWAIT PAGLOCK READCOMMITTED READCOMMITTEDLOCK READPAST REPEATABLEREAD ROWLOCK SERIALIZABLE TABLOCK TABLOCKX UPDLOCK XLOCK Clausula UPDATE WITH lt common table expression gt n UPDATE TOP expression PERCENT lt object gt rowset function limited WITH lt Table Hint Limited gt n J SET column name expression DEFAULT NULL udt_column_name property_name expression field name expression method_name argument n Ne bi 15 Os argumentos mais importantes da cl usula UPDATE est o explica dos na clausula INSERT anteriormente apresentada Nao existe nenhum argumento de acr scimo mais importante Clausula DELETE OPTION lt Query Hint gt n lt object gt server name database name schema name database name schema name schema name table or view name Os argumentos mais importantes da cl usula DELETE est o detalhados abaixo e TOP expression Especifica o n mero ou percentagem de tuplos aleat rios que ser o inseridos e FROM Palavra opcional entre a instru o DELETE e o nome da tabela alvo e server name
55. essary UQ alunos 1FECOB4DOSDE7BCC update is not necessary WA Sys 00000002 0515C6AF update is not necessary O index es statistic s have been updated 3 did not require update Updating dbo cadeiras PK cadeiras 063A8260F975522 update is not necessary O index es statistic s have been updated 1 did not require update Updating dbo curso cadeira PK curso ca F4C4E646145C0A3F update is not necessary O index es statistic s have been updated 1 did not require update Updating dbo inscricoes PK__inscrico__C86FCF2E1A14E395 update is not necessary WA Sys 00000005 182C9B23 update is not necessary WA Sys 00000006 182C9B23 update is not necessary O index es statistic s have been updated 3 did not require update Updating dbo categorias PK categori F05222071FCDBCEB update is not necessary O index es statistic s have been updated 1 did not require update m Figura 5 4 Comando updatestats Como se pode verificar pelo exemplo as actualiza es n o foram necess rias Executando o comando sp autostats verificamos quando foi a ltima actu aliza o 1 EXEC sp autostats alunos af m E Resutts 7 Messages AUTOSTATS Last Updated ON 2009 11 28 17 53 04 740 ON 2009 11 28 17 53 04 747 ON 2009 11 28 17 51 50 200 1 PK alunos 4720268007020F21 2 UQ alunos 1F8COB4DOSDE7BCC 3 LWA Sys 00000002 0519C6AF
56. gada a outra em que o seu sistema o Oracle De seguida iremos explicar como se cria esta arquitectura de bases de dados heterog neas no SQL Server Para que os servidores no SQL Server se liguem uns aos outros s o utiliza dos servidores virtuais Linked Servers que s o suportados por um cliente nativo OLE DB que tem uma API utilizada para aceder a dados coorde nando a distribui o dos dados A vantagem desta configura o que per 88 mite o acesso a servidores remotos permite a distribui o de perguntas transac es fonte de dados heterog neas e controla las de maneira igual Estes servidores t m duas componentes um OLE DB provider que um DLL que gere e interage com uma fonte de dados espec fica e um OLE DB data source que identifica a base de dados especifica que pode ser acedida atrav s do OLE DB Client D Client application Server tier g SQL Server OLE DB OLE OB providers DLLs SQL Server Oracle ODBC Access Database server tier T B T B SQL SQL Oracle ODBC Access Server Server Figura 7 6 Arquitectura de liga o dos servidores Estes servidores costumam ser usados para controlar as perguntas dis tribu das pois quando um cliente executa uma atrav s de um servidor destes o SQL Server faz o parse do comando e envia o para o OLE DB Para uma fonte de dados retornar informa o atrav s de um servidor destes o DLL do OLE DB para essa fonte de dados tem que estar n
57. guisticas sobre os dados de texto nos indices FULL TEXT operando em palavras e fases com base em regras linguisticas de um determinado idioma O SQL Server 2008 disponibiliza a funcionalidade executar queries FULL TEXT em vez de se usar as queries baseadas por caracteres Antes de as queries FULL TEXT poderem usadas numa tabela o administrador da base de dados tem de criar um indice FULL TEXT na tabela O indice FULL TEXT pode ser criado sobre um ou mais campos de texto Estes campos podem ser destes tipos de dados e char e varchar e nchar e nvarchar e text e niert e image e xml e varbinary e varbinary maz Cada ndice FULL TEXT indexa uma ou mais colunas e cada coluna pode ter uma idioma definida No SQL Server 2008 os ndices de FULL TEXT suporta mais de 50 idiomas diferentes como o Ingl s Portugu s Espanhol Chin s Japon s rabe Bengali e Hindu A sintaxe para criar um ndice FULLTEXT CREATE FULLTEXT INDEX ON table name column name TYPE COUUMN type column name LANGUAGE language term een J KEY INDEX index_ name ON lt catalog filegroup option gt WITH lt with option gt n Para mais detalhe sobre as convers es da sintaxe consulte http msdn microsoft com en us library ms177563 aspx Al Cria um indice de FULL TEXT na tabela ou view indexada table name sobre a coluna column name Se na lista de colunas indicar mos colunas d
58. he atribu do Para entender melhor vamos descrever um pequeno exemplo Imagine uma queryl e outra query2 e os respectivos planos de execu o guardados em cache Vamos supor que a query1 menos complexa que a query2 logo o valor age da queryl menor que o valor age da query2 Vamos supor que o valor age de queryl 10 e o valor age da query2 100 Se uma nova pergunta Base de Dados for feita e reutilizar o plano de execu o da pergunta 1 o valor age ir ser incrementado para 20 10 valor actual 10 valor atribu do O mesmo se passar para a query2 O valor age ir sendo decrementado ao longo do tempo e quando chegar a 0 o plano de execu o passa a ser um candidato a ser removido da cache O SQL Server s remove os planos com valor age 0 se necessitar de os remover importante de referir que planos de execu o com custos extremamente reduzidos n o ser o guardados em cache H duas categorias de perguntas no armazenamento de planos de exe cu o e Adhoc Workload e Prepared Workload Na categoria Adhoc as perguntas s o guardadas sem isolamento explic ito das vari veis isto por exemplo as vari veis da cl usula where s o guardadas no plano em vez de serem parametrizadas Imaginemos o seguinte exemplo SELECT x FROM alunos WHERE nome diogo O plano de execu o para esta pergunta guardado em cache com a vari vel impl cita isto implica que se fizer a mesma pergunta mas mudando
59. icas de substitui o De seguida ser o introduzidas as p ginas do sistema que guardam os dados bem como as extens es e como elas s o guardadas dentro de ficheiros Seguidamente falar se das estruturas de dados que suportam toda a informa o das bases de dados como parti es rvores B heap s etc Para finalizar ser feita uma breve apresenta o das pol ticas de recupera o de dados e backup destes dentro do sistema do SQL Server 3 1 Gest o de mem ria Vamos iniciar o estudo do armazenamento e estrutura de ficheiros do SQL Server pela arquitectura de gest o de mem ria explicando como funciona e a sua import ncia para o bom desempenho das bases de dados em implemen tadas no SQL Server O SQL Server adquire ou liberta espa o em mem ria segundo o que neces sita no momento n o tendo o administrador de especificar quanta mem ria quer dispon vel sendo no entanto poss vel este especificar tal atributo Como todas as aplica es de 32 bits t m 4 GB de espa o de endere amento sendo utilizados 2GB pelo kernel do Windows cada aplica o poder ter 2GB deste espa o No entanto o SQL Server disp e de um sistema de Adress Windowing 22 Extension AWE que permite aplica o dispor de tanta mem ria quanta o sistema operativo tiver Desta feita o SQL Server poder correr com um espa o de endere amento at 64 GB O SQL Server tamb m usa o bloqueio de p ginas em mem ria para impedir o sistema operativo
60. licitar melhor esta organiza o Nonclustered index Heap or Rows of data Rows of data data Rows of data clustered index Figura 3 8 Indices n o clustered No SQL Server tamb m pode ser utilizado XML para guardar algumas informa es que pretendermos Esta uma op o a considerar quando as tabelas t m uma estrutura muito fixe e propensa ao XML Desta forma poder se tornar a base de dados mais forte tendo em conta as qualidades do XML sempre que as tabelas tenham uma estrutura em que se aplique este caso Unidades de aloca o s o p ginas dentro de um heap ou uma rvore B usadas para gerir dados baseados no seu tipo Existem tr s tipos de unidades diferentes sendo que numa parti o espec fica uma estrutura qualquer s 30 pode ter um dos tipos Existe um tipo para dados normais um para objectos muito grandes e outro de overflow para dados de tamanho vari vel 3 5 Recupera o de dados Para finalizar falaremos um pouco dos mecanismos que o SQL Server tem para recuperar os dados ap s uma queda da base de dados Este tem v rios modos de recupera o Um modo simples onde s recuperada a ltima base de dados em backup Um completo em que guarda ficheiros de log em backup e n o perdido nenhum trabalho normalmente e outro id ntico ao completo mas que permite uma performance elevada em certos casos Todos estes modos de recuperar informa o t m por base m todos de ba
61. lterar os dados at estar completa O nome vem do facto que se uma transac o fizer sempre a mesma leitura ir sempre obter os mesmos dados Altera es feitas a esses dados ao mesmo tempo que a transac o decorria n o ser o vis veis na transac o Locks partilhados de leitura s o colocados em todos os dados lidos pela transac o e mantidos at ao final da transac o para que nenhuma outra transac o altere os dados que foram lidos pela transac o 6 3 4 SNAPSHOT 31 Este n vel de isolamento especifica que os dados lidos por uma transac o s o de uma vers o dos dados imediatamente antes do inicio da transac o e por isso l consistentes Uma caracter stica importante para a efici ncia da concorr ncia a aus ncia de locks nas leituras face a escritas e vice versa pois as leituras e escritas s o feitas a partir da c pia dos dados Esta caracter stica permite evitar deadlocks 74 6 3 5 SERIALIZABLE 30 Este o nivel mais alto de isolamento suportado pelo SQL Server Este nivel de isolamento especifica que uma transac o executada completamente isolada de transac es concorrentes Os resultados obtidos por todas as transac es em curso dever o ser exactamente os mesmos como se tivessem sido executados em s rie Para garantir a serializa o feito um lock a todo o espa o de chaves que tenham sido lidas sendo este mantido durante toda a dura o da transac o Devido a este bloqueio n o p
62. m a sua principal aplicabilidade em suportar bases de dados que podem ser dis tribu das de forma a guardar toa a informa o necess ria 1 3 Notas sobre instala o do sistema Instalar este Sistema de Base de Dados muito simples e n o exige nenhum skill em especial Portanto remetemos a informa o de instala o para um tutorial com imagens incluidas num site WEB O site com o tutorial de instala o o seguinte http www packtpub com article microsoft sql server 2008 installation made easy Capitulo 2 Cobertura do SQL 2 1 DDL A DDL ou Data Definition Language uma linguagem usada para defini o de estrutura de dados No SQL as instru es DDL t m como objectivo definir o modelo de dados e o modelo entidade rela es de uma Base de Dados As instru es DDL que manipulam tabelas s o as seguintes CREATE DROP e ALTER 2 1 1 Tabelas Cl usula CREATE A sintaxe simplificada para a instru o CREATE CREATE TABLE database name schema name schema name table name lt column definition gt lt computed column definition gt lt table constraint gt n ON partition scheme name partition column name filegroup default TEXTIMAGE ON filegroup default NR Para mais detalhes na cria o de tabelas consulte http msdn microsoft com en us library ms174979 aspx Cl usula DROP DROP TABLE database name
63. napshot criada com a op o ALLOW SNAPSHOT ISOLATION ligada sem ter em conta esta op o na base de dados prim ria ou no modelo da base de dados do sistema Estes snapshots podem ser utilizados para motivos de relat rios de vari ados atributos Podem tamb m ser usados caso existam erros de utilizador na base de dados raiz podendo esta ser mudada para o estado em que estava na altura do snapshot eliminando os erros recentes Os dados modificados depois deste snapshot s o perdidos pois a base de dados volta toda a um momento anterior est vel Estas bases de dados s o criadas utilizando os comandos normais para criar novas bases de dados inserindo a express o AS SNAPSHOT OF Desta maneira acabamos a nossa exposi o dos conte dos do SQL Server para suportar bases de dados distribu das 93 Capitulo 8 Outras caracteristicas do sistema estudado 8 1 Integra o com o CLR 7 25 9 O SQL Server implementa uma tecnologia chamada SQLCLR ou SQL Com mon Language Runtime que consiste em guardar assemblies de net CLR no SQL Server para serem usados em Stored Procedures Triggers user defined types user defined functions e user defined aggregate functions Os princi pais benef cios desta integra o s o e Um melhor modelo de programa o As linguagens da NET Framework s o muito mais poderosas que o TSQL Transact SQL e Habilidade de criar tipos de dados e fun es de agrega o Os tipos de dados e fun es de
64. nsidade de combina es das colunas O SQL Server guarda tamb m v rias estruturas internas chamadas his togramas Esta estrutura consiste na amostra de uma distribui o de dados sobre uma coluna ou indexes Para se perceber um pouco melhor o his tograma um gr fico de barras onde no intervalo X o intervalo entre X e 55 X 1 chamado de passo que pode ser interpretado como um intervalo de val ores O intervalo Y representa a amostra para esse intervalo X Imaginemos uma tabela com 2 atributos inteiros a chave e um valor podemos imaginar que seja o saldo de uma conta e a chave o n mero da conta Imaginemos que criado um histograma para estat sticas sobre o saldo O intervalo entre X e X 1 representa um conjunto de contas e o valor Y uma amostra Imag inemos que no histograma temos um passo de 10000 isto o intervalo de X n mero da conta do histograma ser 0 10000 20000 30000 40000 e para cada passo teremos informa o acerca do maior saldo nas contas nesse intervalo de n mero de contas o valor m dio do saldo o n mero de contas com o maior saldo e o n mero de saldos diferentes O SQL Server utiliza estas estat sticas para poder criar um plano de menor custo 5 3 2 Algumas considera es e AUTO CREATE STATISTICS Quando esta op o est ligada o optimizador de perguntas cria estat sticas para os atributos de cada predicado se for necess rio Por omiss o esta op o est ligada e AUTO
65. nte referir que podemos definir qual das transac es dever ser abortada em caso de deadlock Ao criar a transac o podemos atribuir um valor a uma vari vel chamada de DEADLOCK PRIORITY em que o intervalo de valores entre 10 e 10 e em que 5 significa LOW 0 NORMAL e 5 HIGH A transac o que abortada escolhida com base nesse valor Uma transac o tem o valor zero NORMAL como omiss o SET DEADLOCK PRIORITY LOW NORMAL HIGH lt numeric priority gt deadlock_var GQdeadlock intvar lt numeric priority gt 10 10 Uma outra maneira de terminar com deadlocks definir timeouts para os locks Ou seja uma transac o s poder obter um lock sobre um determi nado recurso durante o intervalo de tempo que especifica Se o intervalo de tempo especificado for 1 significa que a transac o aguardar at ao infinito 78 pelo desbloqueio Para definir um valor para o timeout timeout period definido em milissegundos executar a seguinte instru o SET LOCK TIMEOUT timeout period Se pretendermos ver qual o valor atribu do basta executar a seguinte instru o SELECT LOCK_TIMEOUT GO Se nenhum intervalo foi anteriormente definido esta consulta devolvera o valor 1 esperar at ao infinito 6 5 2 Detec o de deadlock e escolha da vitima Quando um deadlock encontrado pelo monitor de detec o de deadlocks do SQL Server ele retorna a mens
66. o ndice Se estiver ON a percentagem de espa o livre que especificado por o fulfactor aplicado as p ginas interm dias do ndice Se estiver OFF ou o fillfactor n o for especificado as p ginas interm dias do ndice s o cheias FILLFACTOR fillfactor Especifica a percentagem de preenchi mento das folhas durante a constru o do ndice ou no rebuild A percentagem toma valores entre O e 100 Se tomar os valores O ou 100 cria um ndice com as folhas cheias SORT IN TEMPDB ON OFF Especifica se ordena os resultados numa tabela tempor ria STATISTICS NORECOMPUTE ON OFF Especifica se as estat sticas s o recalculadas Se estiver OFF estat sticas desac tualizadas n o s o recalculadas automaticamente se estiver ON s o calculadas automaticamente DROP EXISTING ON OFF 4 Especificas se existir um outro ndice com o mesmo nome apagado ou n o Se estiver ON ent o o ndice apagado e criado o novo se estiver OFF ent o gerado um erro ALLOW ROW LOCKS ON OFF Especifica se os bloqueios s linha s o permitidos ALLOW PAGE LOCKS ON OFF J Especifica se os bloqueios a p ginas s o permitidos 39 e MAXDOP max degree of parallelism Especifica o n mero de processadores a serem usados no plano de execu o paralela O n mero m ximo de processadores 64 40 4 1 4 Cria o de Indices FULLTEX T 10 5 18 33 As queries FULL TEXT realizam pesquisas lin
67. o agente de leitura da fila e o agente de merge Todos estes s o utilizados para cada um cumprir as suas fun es dentro dos tipos de replica o descritos anteriormente de forma a se ter os resultados relatados 7 3 Fragmentacao A fragmenta o no SQL Server tem tr s tipos horizontal vertical e mista sendo feita a partir de filtros de artigos para a fragmenta o horizontal e particionamento de colunas para a fragmenta o vertical Na fragmenta o horizontal podem se seleccionar apenas os tuplos dese jados atrav s dos filtros de artigo utilizados da seguinte maneira sp articlefilter publication publication article article filter_clause filter clause Na fragmenta o vertical o SQL Server faz o particionamento das colunas dos artigos seleccionando desta forma s os atributos desejados Isto pode ser feito da seguinte maneira sp articlecolumn Qpublication publication article article column column operation operation A fragmenta o mista a conjun o das duas anteriormente referidas utilizadas ao mesmo tempo sendo preciso correr os dois comandos para a efectuar 7 4 Bases de dados Heterog neas O SQL Server permite uma arquitectura distribu da com servidores de bases de dados de diferentes sistemas de gest o de bases de dados Assim uma base de dados distribu da em SQL Server pode estar li
68. o dados ou paginas de ndices Extent Bloqueia um grupo cont guo de 8 paginas como dados ou paginas de ndices HoBT Bloqueia um heap ou rvore B Um lock protegendo uma rvore B ou heap de paginas de dados que n o tem um ndice clustered Table Bloqueia uma tabela inteira inclusive dados e ndices File Bloqueia um ficheiro da base de dados Application Bloqueia uma especifica aplica o Meta data Bloqueia meta dados Database Bloqueia a base de dados inteira Podemos definir qual o n vel de granularidade a ser bloqueado aquando de uma instru o DML SELECT UPDATE INSERT e DELETE Para definir usamos a seguinte estrutura por exemplo num UPDATE UPDATE table WITH option option ROWLOCK TABLOCK PAGLOCK DBLOCK De referir de que se a instru o for de consulta ele obt m um Shared Lock S para o respectivo recurso 80 Capitulo 7 Suporte para bases de dados distribuidas Nesta fase do trabalho iremos falar dos mecanismos que o SQL Server disp e para suportar a distribui o de determinados dados por v rias bases de dados consoante a organiza o pretendida Chamam se a estas bases de dados distribu das e a informa o pode ser espalhada em v rios locais na rede sem estarem ligados fisicamente uns aos outros tornando os dados sempre dispon veis para qualquer opera o pretendida Esta distribui o da informa o traz bastantes vantagens aos sistemas em geral t
69. o e limites superiores e inferiores da mem ria do sistema Quando outras aplica es correm na m quina diminuindo o espa o em mem ria dedicado para o SQL Server este gestor consegue ajustar rapida mente o consumo de mem ria da base de dados aumentando ou diminuindo o espa o para o SQL Server Estes ajustes s o r pidos tendo em conta a situa o geral da m quina que est a correr a base de dados Para gerir as grandes bases de dados o SQL Server utiliza o seu compo nente de AWE j referido de maneira a poder guardar grandes quantidades de dados em mem ria evitando muitos acessos ao disco de forma a aumen tar a performance nestas bases de dados de grandes dimens es que poderiam trazer pouca efici ncia no que toca ao SQL Server 3 2 Ficheiros e p ginas Depois de explicada a arquitectura de mem ria do SQL Server com o seu sistema de gest o do buffer e o buffer de cache que s o implementados de forma a melhorar os acessos aos ficheiros no disco da base de dados vamos mostrar como os dados s o guardados nestes ficheiros e todas as constru es envolventes na estrutura o da informa o O SQL Server mapeia a base de dados num conjunto de ficheiros do sistema operativo Informa o e logs s o sempre guardados em ficheiros diferentes sendo que cada ficheiro s utilizado para uma base de dados enquanto que os filegroups s o v rios ficheiros que coordenam a localiza o dos dados e tarefas de recupera o e backup
70. o mesmo servidor do SQL Server Quando se configura um servidor remoto tem que se registar o servidor sp addlinkedserver server server srvproduct product name provider provider name Qdatasrc data source location location provstr provider string catalog catalog Para verificar as informa es sobre os servidores configurados deve se aceder tabela sys servers Sendo necess rio utilizar o comando sp dropserver para remover o servidor e as suas defini es Para criar um mapeamento de um login de um servidor local com a seguran a de um servidor remoto executa se o seguinte comando 89 sp_addlinkedsrvlogin rmtsrvname rmtsrvname Quseself TRUE FALSE NULL locallogin locallogin rmtuser rmtuser rmtpassword rmtpassword Para definir certas op es no servidor remoto utiliza se o comando sp serveroption A conven o de nomes com v rias partes a seguinte server name database name schema name object name O SQL Server suporta nomea o por tr s partes como conven o quando se faz refer ncia a servidores sendo que a conven o ISO tamb m tem tr s nomes mas diferentes dos do SQL Server Desta forma o SQL Server mapeia nos equivalentes da norma ISO da seguinte maneira Da
71. o que informa o s escrita em disco se foi modificada uma ou v rias vezes quando estava no buffer A esta pol tica de substitui o no buffer chama se clock replacement policy As leituras e escritas feitas por este componente s o ass ncronas Ao iniciar o SQL Server reserva o espa o para o buffer de cache segundo alguns par metros como a mem ria f sica do sistema e o n mero m ximo de threads do servidor No entanto este s preenche inicialmente a carga corrente da mem ria f sica actual Durante este processo at o SQL Server obter a mem ria que necessita para o buffer pedidos de leitura v o enchendo o buffer quando necess rio Este tempo chama se ramp up e especializado em tornar leituras de uma p gina em pedidos de oito p ginas tornando mais r pida a inicializa o O buffer utiliza quase toda a mem ria dispon vel para o SQL Server interagindo com o gestor de recursos o sistema operativo do SQL Server e o gestor de logs de maneira a maximizar a sua efici ncia dentro do sistema O gestor do buffer suporta ainda algumas funcionalidades de forma a melhorar o seu funcionamento estas s o o buffer est preparado para acessos de dados n o uniformes suporta Hot Add Memory permitindo a adi o de mem ria f sica sem recome ar o servidor e permite gerir mem ria dinamicamente 23 Esta gest o din mica feita atrav s de mecanismos que ajustam a mem ria necess ria tendo em conta o congestionamento do servi
72. o tipo varbinary varbinary max ou image para guardar ficheiros ent o podemos indicar o tipo de ficheiro noutra coluna para isso usamos a clausula TYPE COLUMN type column name A coluna type column name tem de ser do tipo char nchar varchar ou nvarchar A clausula LANGUAGE serve para indicar qual a coluna esta guardada o idioma do tuplo A clausula KEY INDEX serve para indicar um indice nico para ser chave do ndice FULL TEXT A clausula ON serve para indicar mos em que filegroup e ou que catalog armazenado o ndice A clausula WITH serve para indicar mos op es da cria o do ndice As op es s o as seguintes e CHANGE TRACKING MANUAL AUTO OFF NO POPULATION Especifica quando s o feitas altera es feitas na tabela e esta forem abrangidas pelo o ndice estas ser o propa gadas do SQL Server at ao ndice de FULL TEXT e STOPLIST OFF SYSTEM stoplist name Associa uma STOPLIST ao ndice Uma STOPLIST uma lista de palavras comuns para impedir que o indice fique cheio destas palavras 42 4 1 5 Remo o de ndices 17 A sintaxe para remover um ndice relacional SPATIAL ou XML DROP INDEX lt drop relational or xml or spatial index gt n Este comando remove um indice relacional SPATIAL ou XML com nome lt drop relational or eml or spatial index gt 4 1 6 Remo o de ndices FULLTEXT 16 A sintaxe para remover um ndice FULLTEXT
73. omo por exemplo ntext text varchar mazx nvarchar max Para mais detalhe sobre as convers es da sintaxe consulte http msdn microsoft com en us library ms177563 aspx 32 varbinary maz cml e image nao podem ser chaves de indices Se indicar mos que este UNIQUE ent o nao permite que duas linhas tenha os mesmos valores nos campos do ndice A ordem pr definia dos campos Ascendente ASC Poderemos indicar se CLUSTERED ou NONCLUSTERED Podemos indicar campos n o chave adicionais para serem adicionados no ndice dos NONCLUSTERED Para adicionar estes campos usamos a a palavra reservada INCLUDE e a lista dos campos A clausula WHERE cria um ndice filtrado ou seja um indice com al gumas linhas do objecto lt object gt A clausula WITH serve para indicar mos op es da cria o do ndice As op es s o as seguintes e PAD INDEX ON OFF Especifica o padding do ndice Se estiver ON a percentagem de espa o livre que especificado por o fulfactor aplicado as p ginas interm dias do ndice Se estiver OFF ou o fillfactor n o for especificado as p ginas interm dias do ndice s o cheias FILLFACTOR fillfactor Especifica a percentagem de preenchi mento das folhas durante a constru o do ndice ou no rebuild A percentagem toma valores entre 0 e 100 Se tomar os valores 0 ou 100 cria um ndice com as folhas cheias e SORT IN TEMPDB ON OFF Especifica se ordena os re
74. op ON filegroup_ name default O ndice criado com o nome index name na tabela lt object gt sobre o campo spatial column name A clausula USING indica o esquema de mosaico para o indice SPATIAL Esta clausula pode tomar os seguintes valores e geometry e geography Um indice SPATIAL s pode ser criado sobre campos do tipo geometry ou geography caso contrario o SQL Server indicar um erro A clausula ON filegroup name cria o ndice no especificado filegroup A clausula WITH serve para indicar mos op es da cria o do ndice As op es s o as seguintes e GEOMETRY GRID Especifica o grid tessellation em geometry e GEOGRAPHY GRID Especifica o grid tessellation em geography e BOUNDING BOX Especifica um quadruplo num rico que define quatro coordenadas de uma caixa delimitadora O z min e y min do canto inferior esquerdo e s max y maz do canto superior direito Para mais detalhe sobre as convers es da sintaxe consulte http msdn microsoft com en us library ms177563 aspx 38 GRIDS Define a densidade da grid em cada nivel Os niveis possiveis s o LEVEL 1 LEVEL 2 LEVEL 3 LEVEL 4 E as densidades poss veis s o LOW MEDIUM HIGH CELLS PER OBJECT n Especifica o numero de c lulas tes sellation por um nico objecto spatial num ndice por um processo tessellation N pode ser um inteiro entre 1 e 8192 inclusive PAD INDEX ON OFF Especifica o padding d
75. op o em particular a seguinte lt query hint gt 4 HASH ORDER GROUP CONCAT HASH MERGE UNION LOOP MERGE HASH JOIN FAST number_rows FORCE ORDER MAXDOP number of processors OPTIMIZE FOR Gvariable name UNKNOWN literal constant n OPTIMIZE FOR UNKNOWN PARAMETERIZATION SIMPLE FORCED RECOMPILE ROBUST PLAN KEEP PLAN KEEPFIXED PLAN EXPAND VIEWS MAXRECURSION number USE PLAN N xml_ plan TABLE HINT exposed object name lt table hint gt ER Bai lt table hint gt NOEXPAND INDEX index value n INDEX index value FASTFIRSTROW FORCESEEK HOLDLOCK NOLOCK 13 Clausula INSERT Os argumentos mais importantes da cl usula INSERT est o detalhados abaixo e TOP expression Especifica o n mero ou percentagem de tuplos aleat rios que ser o inseridos e INTO uma palavra opcional que usada entre a instru o SELECT e o nome da tabela para onde se quer inserir tuplos 14 e server name Nome do servidor onde a tabela est guardada e database name Nome da base de dados que cont m a tabela e table or view name Nome da tabela ou nome da vista para a qual queremos inserir os tuplos e VALUES Especifica os valores dos atributos do tuplo a inserir e WITH lt table hint limited gt n Especifica as op es que podemos permitir para a tabela de destino Um exemplo disso
76. ordem natural do atributo de jun o ou ent o poder usar se um ndex sobre esse atributo Em geral usado o ndex desse atributo pois tem um custo menor Nested Loop Join 3 A implementa o deste algoritmo exactamente igual ao implementado pelo Oracle O pseudo c digo segue abaixo for all row R1 in the outer table do for all row R2 in the inner table do if R1 joins with R2 then return R1 R2 end if end for end for Uma forma de melhorar o algoritmo criar um ndex sobre o atributo da jun o numa das tabelas Imaginando o seguinte exemplo create table Clientes client_id int nome varchar 10 insert Customers values 1 Diogo insert Customers values 2 Pedro insert Customers values 3 Joao create table Vendas lient id int produto varchar 10 insert Sales values Camera Computador insert Sales values Monitor Z c 2 insert Sales values 3 3 insert Sales values 4 Impressora 51 Se previamente existir um index na tabela Vendas sobre o atributo client id o plano de execu o escolher usar uma pesquisa por ndex em vez de per correr a tabela inteira ou seja percorre se cada tuplo da tabela Clientes e para cada um deles procurar pela correspond ncia na tabela Vendas usando um index seek 52 5 2 Mecanismos para express es complexas 5 2 1 Paralelismo 4 O SQL Server permite execu o de pergunta
77. os dados replicados Define se um publicador atrav s do seguinte comando sp_adddistpublisher publisher publisher distribution_db distribution db Qsecurity mode security mode login login password password Qworking directory working directory trusted trusted encrypted_password encrypted_ password Qthirdparty flag thirdparty flag Qpublisher type publisher type Um distribuidor uma inst ncia da base de dados que guarda dados replicados tendo em conta um ou mais publicadores sendo que cada publi cador est relacionado com uma base de dados distribu da no distribuidor Os distribuidores guardam dados replicados meta dados sobre as publica es e pode ter a fun o de fila de dados entre o publicador e o subscritor Um distribuidor pode ser tamb m publicador ao mesmo tempo chamado um distribuidor local e quando isto n o acontece chama se distribuidor remoto Define se um distribuidor configurando primeiro o servidor como distribuidor e depois criando a base de dados do distribuidor 83 sp adddistributor Qdistributor distributor Qheartbeat interval heartbeat interval password password from_scripting from scripting sp_adddistributiondb Qdatabase database Qdata folder data_folder Qdata file data_file Qdata file size data file size Qlog
78. ows de forma a conseguir responder ao requerido por parte das transac es que usam o nivel de iso lamento serializable Pelo facto de bloquear os ndex rows dos ndex keys acedidos durante a dura o da transac o nenhuma linha que esteja contida no conjunto dos ndex bloqueados pode ser inserida actualizada ou apagada Permite mais concorr ncia TT 6 5 Deadlocks 14 Deadlock um problema especial de concorr ncia onde duas transac es bloqueiam as actividades uma da outra Imaginemos duas transac es T1 e T2 Imaginemos que T1 est a bloquear tem um lock sobre um recurso que T2 quer aceder e vice versa Este acontecimento designado por deadlock Transaction 1 Transaction 2 Supplier Part Holds Holds lock on Supplier ty lock on Part Wants lock on Part Do a bio A ae lock on Supplier Figura 6 1 Deadlock Por padr o as transa es SQL Server n o t m tempo limite a menos que LOCK TIMEOUT seja configurado 6 5 1 Detec o de deadlocks e termina o de deadlocks 15 Ambas as transac es ficar o em espera a menos que um processo externo quebre o deadlock O SQL Server tem um mecanismo designado por monitor que detecta deadlocks Se o monitor detectar uma depend ncia c clica ele escolhe uma das transac es e aborta essa transac o faz rollback e a outra efectuada Ap s a transac o que continuou a execu o terminar ent o a que foi abortada pode ser executada importa
79. podem ser adicionados a um Desta maneira todas as suas p ginas ser o adicionadas ao filegroup ou as tabelas e os ndices podem ser particionados podendo ficar dados de tabelas ou ndices em v rios filegroups aquando deste particionamento Esta estrutura est resumida na imagem seguinte De seguida iremos descrever como que as tabelas s o organizadas guardadas e acedidas dentro das extens es 27 Database MyDB Primary filegroup c Program Files Microsoft SQL Server MSSQL 1 MSSQL Data MyDB_Prm mdf Log file c Program Files Microsoft SQL Server MSSQL 1 MSSQL Data MyDB ldf E MyDB FG1 filegroup c Program Files Microsoft SQL Server MSSQL 1 MSSQL Data MyDB_FG1_1 ndf 1MB c Program Files Microsoft SQL Server MSSQL 1 MSSQL Data MyDB_FG1_2 ndf E Figura 3 5 Exemplo da organiza o de uma BD 3 4 Estrutura de dados e parti es Uma tabela est contida numa ou mais parti es e cada parti o cont m linhas de dados num heap ou numa estrutura de ndices agrupados clus tered Estes s o geridos numa ou mais unidades de localiza o dependendo dos tipos de colunas nas linhas de dados Table Partition 1 Partition n ex Heap or EE tree IEZ Heap or Ea tree E Bea E ada Data LOB Row Data LOB Row Overflow Overflow Figura 3 6 Arquitectura de uma tabela Uma parti o uma unidade de organiza o dados definida pelo uti lizador e por defeito cada tabela ou
80. por c pias da base de dados nos momen tos de sincroniza o Desta maneira tirada uma c pia base de dados num determinado momento aparecendo os dados como estavam num momento es pec fico em que a c pia foi feita Estas c pias podem ser geradas e aplicadas logo ap s a subscri o das publica es ou de acordo com um tempo pr definido na publica o Estas c pias s o guardadas numa pasta de c pias do publicador e dados enquanto guardada informa o para monitorizar a base de dados de distribui o para os distribuidores Seguidamente as c pias s o distribu das pelos subscritores que podem ser actualizados automaticamente caso escolham esse par metro caso contr rio seguem o esquema descrito anteriormente Publisher Snapshot Agent as D Distribution Database Subse O Ema es Subscription Figura 7 5 Replica o snapshot Todos estes tipos de replica o utilizam programas que correm sozin 87 hos chamados agentes para as tarefas de monitoriza o de altera es e dis tribui o dos dados Por defeito estes agentes correm agendados pelo SQL Server Agent que estar a correr para os outros funcionarem Os agentes de replica o podem ser corridos na linha de comando ou por aplica es como o gestor de replica o de objectos Os agentes existentes s o SQL Server Agent o agente de snapshot o agente de leitura de logs o agente de distribui o
81. r es de transac es e com isso poder voltar atr s na transac o at ao sitio onde foi feito o savepoint ou seja se fizermos rollback podemos voltar at ao ponto gravado em vez de voltarmos ao inicio da transac o impor tante referir que quando aplicado um savepoint os recursos modificados antes do savepoint de uma transac o ficam bloqueados at ser feito com mit ou rollback Se nenhum savepoint for referido numa transac o se for necess rio fazer rollback todas as altera es da transac o s o desfeitas e a transac o ter que ser executada desde o inicio O seu uso s vantajoso em situa es em que a probabilidade de haver erros muito baixa Imag inemos que temos uma transac o que tem que fazer uma valida o de uma actualiza o e a actualiza o e estas opera es s o caras e por isso mais eficiente fazer rollback savepoint name se a valida o falhar em que vez de voltar a fazer a transac o desde o inicio Para criar um savepoint executar a seguinte instru o SAVE TRANSACTION savepoint name Para fazer rollback para o ponto salvo executar a seguinte instru o ROLLBACK TRANSACTION savepoint name 71 6 2 4 Instru es T SQL permitidas em Transac es 22 Todas as instruc oes T SQL sao permitidas em transac oes excepto as in struc oes descritas no quadro abaixo ALTER DATABASE DROP DATABASE ALTER FULLTEXT CATALOG
82. r o respectivo plano de execu o para a pergunta Parse Tree Parser Z T SQL DML Statement Yes Y Algebrizer e a Storage Engine Query Processor Tree Optimizer Figura 5 1 Passos para cria o dos planos de execu o 46 e Parser O parser valida a sintaxe da pergunta Se a sintaxe estiver errada a pergunta ou uma sequencia de perguntas nao sao executadas Se a sintaxe estiver correcta o parser cria uma estrutura chamada parse tree para ser consumida pelo componente Algebrizer e Algebrizer O Algebrizer verifica se as tabelas e respectivos atributos referenciados na pergunta existem na base de dados O Algebrizer retorna uma estrutura chamada Query Processor Tree que uma rep resenta o l gica dos passos necess rios para a execu o do comando SQL O Query Processor Tree enviado para o pr ximo passo da ex ecu o da consulta que a an lise do Query Optimizer Nem sempre o resultado obtido pelo Algebrizer enviado para o Query Optimizer Um exemplo disso a cria o de uma tabela N o faz sentido enviar o resultado desse comando para o Optimizer pois s existe uma nica maneira de executar o comando SQL e Optimizer O query optimizer o componente principal para escolher o melhor plano para a execu o de uma pergunta Ele escolhe que algoritmos de jun o selec o etc que podem ser utilizados para minimizar o custo da execu o e se a pergunta
83. ra es de modifi ca o de dados INSERT UPDATE e DELETE para impedir que o mesmo recurso seja alterado em simult neo por v rias transac es Quando uma transac o tem um lock exclusivo de um determinado recurso nenhuma outra transac o pode ler modificar os recursos pois n o consegue obter nenhum tipo de lock sobre esses recursos 6 4 3 Update Locks Os Update Locks locks de actualiza o s o uma combina o dos dois tipos anteriores Shared e Exclusive em opera es de actualiza o UPDATE que impliquem a leitura dos dados antes da sua altera o Ou seja para se fazer uma actualiza o poder ser necess rio ler os dados em primeiro lugar Para isso a transac o obt m um lock partilhado sobre esses dados Depois ser necess rio escrever os dados e para isso a transac o obter um lock exclusivo para alterar esses mesmos dados importante de referir que apenas uma transac o poder ter um update lock a cada momento Este tipo de locks importante para evitar situa es de deadlock Imaginemos um 76 exemplo em que duas transac es concorrentes t m um lock partilhado sobre uma determinada tabela da base de dados Agora imaginemos que ambas tentam actualizar os dados em simult neo e portanto uma das transac o tentar modificar o seu lock partilhado para exclusivo mas n o conseguir porque existe uma outra transac o com lock partilhado e consequentemente ter que esperar A outra tr
84. rdem cronol gica Custom Application Transactional replication typically begins with the generation and application of the snapshot Publisher EA i e Articies Publication Distributor a 5 Log Reader Agent Distribution gt Agent Distribution Database Subscriber Articles Subscription Figura 7 3 Replica o transaccional A replica o merge come a como a transaccional com o snapshot da base de dados As altera es subsequentes aos dados feitas no publicador ou nos subscritores s o controladas atrav s de triggers sendo que os subscritores sincronizam se com o publicador quando ligados rede e trocam todos os dados necess rios que mudaram entre publicador e subscritor desde a ltima sincroniza o Desta forma cada site pode actuar de forma aut noma po dendo ser feitas actualiza es nos servidores de forma independente sendo os dados alterados no publicador ou nos subscritores com a possibilidade de ocorrer conflitos Viola es das restri es s o permitidas at sincroniza o do sistema onde h a resolu o de conflitos nas actualiza es que violam restri es somente nessa altura 86 Merge replication typically begins with the generation and application of the snapshot Publisher Subscriber Distributor ED History Data Distribution Database Figura 7 4 Replica o Merge A replica o snapshot funciona
85. ry_expression gt n lt query specification gt SELECT ALL DISTINCT TOP expression PERCENT WITH TIES lt select list gt INTO new table FROM lt table source gt n WHERE lt search condition gt GROUP BY ALL group by expression n WITH CUBE ROLLUP HAVING lt search condition gt Os argumentos mais importantes da cl usula SELECT est o detalhados abaixo e ALL Especifica que linhas duplicadas aparecem no resultado da per gunta e DISTINCT Especifica que linhas duplicas n o aparecem no resultado 12 TOP expression Indica que apenas um certo n mero de resultados ou uma percentagem de resultados da consulta ser o retornados lt select list gt Especifica quais as colunas seleccionadas para o resul tado da consulta _ Todos os atributos de todas as tabelas referidas ser o mostrados table name view name table alias Indica qual a tabela pela qual queremos mostrar todos os seus atributos no resultado da con sulta column name Indica a coluna que aparecer no resultado da con sulta GROUP BY Especifica como os grupos de dados ser o colocados no resultado retornado HAVING Especifica uma condi o de pesquisa para um grupo de dados tipicamente usado em conjunto com a cl usula GROUP BY OPTION Especifica que query hint dever ser utilizada durante a execu o da query A sintaxe desta
86. s em paralelo quando poss vel e necess rio para optimiza o de perguntas Durante o processo de optimiza o o query optimizer procura pelas perguntas que possam ser executadas em paralelo para criar um plano adequado Claro que s ir procurar se hou ver mais que 1 CPU no Servidor se houver mem ria suficiente entre mais outros factores caso contr rio o optimizer s considerar um nico CPU Para permitirmos que mais que 1 CPU seja usado para o processa mento de perguntas devemos alterar uma configura o do SQL Server que chamada de affinty mask A affinty mask na realidade um bitmap onde o n mero de 1 s o n mero de CPU s que queremos que fiquem dispon veis para a execu o das perguntas Podemos alterar o n mero de CPU s exe cutando os seguintes comandos USE master EXEC sp configure show advanced option 1 RECONFIGURE EXEC sp_configure affinity mask 15 bit map 0001111 RECONFIGURE Ap s a execu o destes comandos estou a indicar que quero usar 4 CPU s para a execu o das perguntas Podemos definir o n mero de CPU s usados apenas para o paralelismo Isto se tiver 4 CPU s e representados na affinity mask podemos apenas definir 2 CPU s para o paralelismo de perguntas Isso pode ser feito execu tando os seguintes comandos USE master EXEC sp configure show advanced option 1 RECONFIGURE EXEC sp configure max degree of parallelism
87. s op es da cria o do ndice As op es s o as seguintes e PAD_INDEX ON OFF Especifica o padding do indice Se estiver ON a percentagem de espa o livre que especificado por o fillfactor aplicado as p ginas interm dias do indice Se estiver OFF ou o fillfactor n o for especificado as p ginas interm dias do ndice s o cheias FILLFACTOR fillfactor Especifica a percentagem de preenchi mento das folhas durante a constru o do ndice ou no rebuild A percentagem toma valores entre 0 e 100 Se tomar os valores 0 ou 100 cria um indice com as folhas cheias e SORT IN TEMPDB ON OFF Especifica se ordena os resultados numa tabela tempor ria e IGNORE DUP KEY ON OFF Especifica o tipo de erro gerado quando se introduz valores repetidos num ndice nico Se estiver ON lan ar um warning e as linhas que violem as restri es n o ser o inseridas Se estiver OFF lan ar um erro e todo o insert n o ser inserido STATISTICS NORECOMPUTE ON OFF Especifica se as estat sticas s o recalculadas Se estiver OFF estat sticas desac tualizadas n o s o recalculadas automaticamente se estiver ON s o calculadas automaticamente e DROP EXISTING ON OFF 3 Especificas se existir um outro ndice com o mesmo nome apagado ou n o Se estiver ON Para mais detalhe sobre as convers es da sintaxe consulte http msdn microsoft com en us library ms177563 aspx 36
88. st ncia XML 99 Bibliografia 1 2 3 4 pean 5 6 7 ia 8 9 10 11 12 Craig Freeman Hash join http blogs msdn com craigfr archive 2006 08 10 687630 aspx Craig Freeman Merge join http blogs msdn com craigfr archive 2006 08 03 687584 aspx Craig Freeman Nested loop join http blogs msdn com craigfr archive 2006 07 26 679319 aspx Grant Fritchey and Sajal Dam Execution plan cache analisys SQL Server 2008 Query Performance Tuning Distilled Grant Fritchey and Sajal Dam Sql query perfomance analisys SQL Server 2008 Query Performance Tuning Distilled Grant Fritchey and Sajal Dam Statistics analisys SQL Server 2008 Query Performance Tuning Distilled Manuel Gomes Using net assemblies inside sql server Quick start guide http www tryexcept com articles 2009 09 22 using net assemblies inside sql server quick start guide html Microsoft Clustered index structures http msdn microsoft com en us library ms177443 aspx Microsoft Common language runtime clr integration overview http msdn microsoft com en us library ms131089 aspx Microsoft Create fulltext index transact sql http msdn microsoft com en us library ms187317 aspx Microsoft Create index transact sql http msdn microsoft com en us library ms188783 aspx Microsoft Create spatial index transact sql http msdn microsoft com en us library bb
89. struturas de dados que os suportam O processamento e optimiza o de perguntas o pr ximo tema abordado no trabalho sendo descritos os mecanismos que servem para este prop sito dentro do sistema A gest o de transac es e controlo de concorr ncia em sistemas SQL Server abordada a seguir com a explica o de como feita e que mecanismos suportam estas caracter sticas De seguida descrevemos a implementa o de bases de dados distribu das no SQL Server apontando como pode ser feita as suas caracter sticas essenciais e toda a implementa o que suporta este sistema Para finalizar falamos de algumas caracter sticas que achamos in teressantes dentro do SQL Server bem como de ferramentas que poder o estar associadas ao sistema 1 2 Hist ria e Aplicabilidade O c digo base do SQL Server at vers o 7 0 tem como origem o Sybase SQL Server e foi o come o da Microsoft no mercado das base de dados empresariais competindo com a Oracle IBM e posteriormente a Sybase Microsoft a Sybase e a Ashton Tate uniram se em 1989 para criar a primeira vers o chamada SQL Server 1 0 para o OS 2 que foi uma implementa o do Sybase SQL Server 3 0 para UNIX O Microsoft SQL Server 6 0 foi a primeira vers o pata Windows NT sem a participa o da Sybase No tempo do Windows NT a Sybase e a Microsoft separaram se e cada um desenvolveu o seu sistema de base de dados A Microsoft renegociou os direitos exclusivos de todas as vers
90. sultados numa tabela tempor ria e IGNORE DUP KEY ON OFF Especifica o tipo de erro gerado quando se introduz valores repetidos num ndice nico Se estiver ON lan ar um warning e as linhas que violem as restri es n o ser o inseridas Se estiver OFF lan ar um erro e todo o insert n o ser inserido STATISTICS NORECOMPUTE ON OFF Especifica se as estat sticas s o recalculadas Se estiver OFF estat sticas desac tualizadas n o s o recalculadas automaticamente se estiver ON s o calculadas automaticamente e DROP EXISTING ON OFF Especificas se existir um outro ndice com o mesmo nome apagado ou n o Se estiver ON ent o o ndice apagado e criado o novo se estiver OFF ent o gerado um erro e ONLINE ON OFF Especifica se as tabelas e os seus ndices est o dispon veis para consultas e modifica o durante a opera o de ndice Esta op o s se encontra dispon vel nas vers es SQL Server Enterprise Developer e de Evaluation 33 e ALLOW _ ROW _LOCKS ON OFF Especifica se os bloqueios s linha s o permitidos e ALLOW PAGE LOCKS ON OFF Especifica se os bloqueios a p ginas s o permitidos e MAXDOP max degree of parallelism Especifica o n mero de processadores a serem usados no plano de execu o paralela O n mero m ximo de processadores 64 A clausula ON partition scheme name column name serve para especificarmos o
91. tabase Catalog Schema Schema Object Object e tipo de dados definidos pelo utilizador Domain 7 5 Transpar ncia de dados Nas consultas dentro do sistema de bases de dados distribu do o SQL Server fornece alguma transpar ncia de dados ou seja o utilizador n o precisa de saber onde e como est o guardados os dados dentro do sistema a que est a aceder Desta maneira o SQL Server tem dois tipos de transpar ncias dispon veis dentro do seu sistema O SQL Server n o tem transpar ncia de nome por isso quando se acede a dados noutros servidores necess rio especificar o nome do servidor em quest o a que se est a aceder aos dados A transpar ncia de localiza o no SQL Server feita atrav s de vistas sendo estas criadas de forma a guardar os dados fazendo com que seja trans parente a localiza o destes ao utilizador que em vez de ter que saber onde os dados est o apenas consultam a vista A transpar ncia de replica o fornecida atrav s dos tr s tipos de repli ca o e actualiza o que j foram referidos anteriormente pois a exist ncia variada dos dados dentro do sistema invis vel ao utilizador 7 6 Transaccoes distribu das As transac es distribu das abrangem mais do que um servidor que s o con hecidos como gestores de recursos sendo que a gest o da transac o coor denada entre estes gestores por um componente do servidor chamado gestor de transac o O mecanismo de base de
92. tada Consist ncia Quando a transac o efectuada os dados t m que continuar consistentes Isto a transac o n o pode modificar a es trutura interna do modelo de dados de forma a que os dados fiquem inconsistentes Isolamento Esta propriedade garante que uma transac o ao ser ex ecutada concorrentemente com outra ela execute com dados consis tentes isto a transac o s ir operar sobre os dados anteriores realiza o da outra transac o ou apenas com os dados actualizados ap s a outra transac o e nunca opere sobre dados num estado inter m dio Durabilidade Esta propriedade garante que ap s a realiza o da transac o os efeitos da transac o permane am no sistema mesmo que o sistema falhe 67 6 1 Modos de transac o O SQL Server suporta v rios modos de transac o Os modos que suporta s o os seguintes e Transac es AutoCommitted e Transac es Impl citas e Transac es Explicitas 6 1 1 Transac es AutoCommitted 34 Este o modo por omiss o do SQL Server Neste modo uma instru o T SQL que n o falhe automaticamente committed ap s sucesso ou rolledback caso falhe Este modo usado para todas as transac es menos quando imposto o modo impl cito ou explicito sobre as transac es O modo AutoCommitted volta a operar at um dos outros dois modos for desligado No caso do modo explicito quando a transac o operar nesse modo o modo de transac
93. tart time of day active start time of day Gactive end time of day active end time of day GOactive start date active start date QOactive end date active end date GQdescription description security mode security mode eel Mal 84 Para disponibilizar os dados replicados o SQL Server utilize artigos pub lica es e subscri es Uma publica o um conjunto de artigos de uma base de dados permitindo esta organiza o uma forma f cil de representar logi camente as unidades de replica o Uma publica o pode ter v rios tipos explicados posteriormente De seguida s o mostrados os comandos para criar uma publica o merge e outra snapshot ou transaccional 3 sp_addmergepublication Qpublication publication 3 sp_addpublication publication publication 2 Um artigo um conjunto de dados contido num publica o podendo ser tabelas vistas procedimentos ou outros objectos Quando s o tabelas podem ser utilizados filtros para restringir os dados passados aos subscritores Define se um artigo da seguinte maneira pi sp addarticle publication publication article article Uma subscri o um pedido de c pia de uma publica o para ser en tregue a subscritores sendo definida que publica es v o ser recebidas e quando Existem dois tipos de subscri es as push e as p
94. tipo de parti es do ndice A clausula ON filegroup name cria o indice no especificado filegroup A clausula FILESTREAM ON especifica a coloca o dos dados FILESTREAM para a tabela quando o ndice criado O FILESTREAM ON permite os dados FILESTREAM movidos para diferentes FILESTREAM filegroup ou esquema de parti o 34 4 1 2 Cria o de ndices XML 13 21 O ndices de XML podem ser criados sobre campos com tipos de dados XML Este indexa todas as tags valores e caminhos sobre a inst ncia XML na coluna e beneficia do desempenho da coluna As aplica es podem beneficiar dos ndices de XML nas seguintes situ a es e Se as queries sobre colunas de XML forem comuns O custo de manuten o dos ndices XML durante altera o dos dados t m de ser considerados e Os dados de XML forem bastante grandes e os bocados pretendidos s o pequenos Construir ndices de XML evita o parse de todos os dados em run time e beneficia das pesquisas por ndices para o processamento de queries eficiente Existem duas categorias neste tipo de ndices e Primary XML index e Secondary XML index O primeiro ndice num campo do tipo de dados XML tem de ser Primary XML index Usando um Primary XML index os seguintes tipos de Secondary XML index s o suportados caminho valor e propriedades Dependendo do tipo de queries os Secondary XML index podem melhorar o desempenho da query Quando indicamos que um ndice PRIMARY ent o
95. todos os gestores de recursos s o envia dos comandos de commit para eles todos podendo estes depois completar o commit Se todos os gestores de recursos reportarem sucesso no commit o gestor de transac es envia uma notifica o de sucesso para a aplica o Se algum dos gestores de recursos reportar uma falha enviado um comando de rollback para todos os gestores intervenientes na transac o indicando a falha na transac o Estas transac es s o geridas atrav s do Trasact SQL ou a API da base de dados Para efectuar uma transac o distribu da faz se da seguinte maneira BEGIN DISTRIBUTED TRAN TRANSACTION 1 transaction name Qtran name variable eal 7 7 Resolu o de conflitos Como o SQL Server suportar base de dados distribuidas bem como consultas e transac es distribu das necess rio que tenha mecanismos para resolver problemas de concorr ncia de maneira distribu da Desta maneira o SQL Server implementa alguns m todos de resolver conflitos distribu dos que s o chamados quando um acontece O m todo que resolve conflitos distribu dos por defeito o m todo das prioridades em que s o atribu das prioridades a cada servidor e quando dois actualizam o mesmo dado esse actualizado pelo servidor com a melhor prioridade 91 O SQL Server implementa tamb m o m todo do timestamp DATE TIME em que necess rio existir um campo timestamp para quando um valor inserido ou act
96. ualizado Seguidamente utilizado um dos m todos em que ou as altera es mais antigas s o sempre aplicadas ou as mais recentes necess rio para este m todo funcionar uma sincroniza o dos tempos dos servidores Outros algoritmos utilizados para resolver conflitos no SQL Server s o o de aditivo e m dia e o do m nimo e m ximo que utilizam n meros de diferentes maneiras para definir qual ser actualizado 7 8 Locks globais O controlo de concorr ncia do SQL Server j foi discutido anteriormente e os seus pormenores detalhados Estes mesmos bloqueios s o utilizados nas transac es distribu das segundo o protocolo que foi explicado de como elas decorrem com esperas de respostas por parte do gestor de transac es 7 9 Propaga o de actualiza es O SQL Server utilizando a replica o tem a possibilidade de distribuir uma base de dados tendo que arranjar maneira de propagar as actualiza es feitas em cada uma delas As actualiza es nos modos de replica o transaccional e snapshot podem ser feitas atrav s da escolha da op o update subscriptions num subscritor enquanto na replica o merge os dados s o actualizados automaticamente A propaga o destas mesmas actualiza es pode ser feita de tr s maneiras distintas As altera es em fila permitem que as modifica es sejam feitas no subscritor quando este n o estiver ligado ao publicador Quando o subscritor se volta a ligar as altera es s o envia
97. ull podendo estas ser definidas como merge ou snapshot e transaccional Nas push o subscritor n o pede altera es ao publicador enquanto nas pull pede Estas definem se da seguinte maneira sp addsubscription publication publication sp addmergepullsubscription publication publication sp addpullsubscription publisher publisher O SQL Server fornece tr s tipos de replica o poss veis para a distribui o de dados em bases de dados e Replica o transaccional e Replica o merge e Replica o snapshot O tipo de replica o escolhido depende de alguns factores a ter em conta como o n mero de computadores envolvidos e os seus tipos o tipo e a quan tidade de dados e a regularidade de actualiza o nos subscritores A replica o transaccional inicia se com um snapshot dos objectos e da dos da base de dados publicada Ap s este snapshot ser tirado as altera es de dados no publicador s o enviadas para os subscritores em quase tempo real sendo que estas altera es s o aplicadas ao subscritor na mesma or dem e com os mesmos limites do que as que aconteceram no publicador e 85 desta forma em todas as publica es a consist ncia transaccional man tida Detalhadamente s o guardados logs das opera es que alteram os da dos permitindo que as opera es sejam capturadas individualmente e propa gadas para os subscritores Estas altera es s o propagadas na sua o
98. um ndice clustered criado com a chave formada a partir de a chave da tabela e o identificador do n XML Cada tabela pode ter at 249 ndices de XML Quando criar um ndice de XML tem de ter em conta e Um indice clustered tem de existir como primary key da tabela e chave da primary key tem de ser limitada a 15 campos e Cada campo de XML pode ter um primary XML index e m ltiplos secondary XML indexes e Um primary XML index num campo de XML tem de existir antes de um secondary XML index e Um ndice de XML s pode ser criado sobre um campo XML N o pode criar ndices de XML em campos que n o s o XML e n o pode criar um ndice normal em campos de XML e N o pode criar ndices XML em campos XML em views 35 A sintaxe para criar um indice de XML CREATE PRIMARY XML INDEX index name ON lt object gt xml column name USING XML INDEX xml index name FOR VALUE PATH PROPERTY WITH lt xml index option gt n ee i O indice criado com o nome index name na tabela lt object gt sobre o campo xml_column_name Se indicarmos a op o USING XML IN DEX aml index name ent o estamos a dizer que o seguinte ndice se cund rio XML vai usar a chave do ndice de XML prim rio com o nome amil index name como combina o da usa chave Para indicarmos que tipo de dados vamos indexar usamos a op o FOR VALUE PATH PROP ERTY A clausula WITH serve para indicar mo

Trabalho de Sistemas de Base de Dados

Contents

Download Pdf Manuals

Related Search

Related Contents