Lista de Exercícios – Avaliação Inicial de Conhecimentos em SQL Server

Boa Noite Pessoal,

Estou aqui em Seattle na casa de um amigo agüentando esse frio de lascar. O MVP Summit acabou e ainda estou me preparando para postar como foi essa excelente experiência. Enquanto aguardo outros amigos chegarem para decidirmos onde iremos fazer as compras, estou aqui preparando mais um artigo para postagem. Acho que desde que comecei a participar mais de fóruns e comunidades, é bem recorrente que alguém me pergunte se tenho alguma lista, simulado ou algo para "testar" o conhecimento em SQL Server. Normalmente quem me pede isso é sempre algum aluno de faculdade ou alguém que almeje a certificação. Em um dos meus artigos mais polêmicos (Como evitar contratar DBAs "Fake" e despreparados) comento sobre a realização de uma prova (há a teórica e a prática). De fato a realização de uma prova é uma forma de fazer com que o candidato saia do "eu fiz isso, eu fiz aquilo, eu sou assim, eu sou assado" e parta para algo mais mensurável. Infelizmente não dá para postar todas as provas que possuo e uso para as entrevistas, mas eventualmente postarei algumas listas de exercícios por aqui.

A lista de exercício a seguir é composta por 12 questões e eu a elaborei há alguns anos atrás. Dois amigos pretendiam tentar a prova 70-431 – Microsoft SQL Server 2005 – Implementation and Maintenance e perguntaram-me se eu poderia prepará-los para o exame. Eu nunca atuei como professor particular para certificações e exames Microsoft e embora parecesse uma boa idéia, eu iria sentir um certo incômodo se os amigos não passassem no exame após a preparação. Se eles conseguissem, o mérito seria deles, mas se eles falhassem talvez a culpa fosse minha (bem típico de um jogo de futebol quando se comparam os jogadores e os técnicos). Então antes partirmos para a prova, nada melhor do que mensurar o nível deles. Afinal se estivesse abaixo, talvez fosse melhor reforçar os fundamentos do que partir para uma prova de certificação. Pensando em uma forma de mensurar o nível técnico, elaborei uma pequena prova.

Questão 1 – Você é o DBA sênior de uma grande e respeitada instituição financeira. Os desenvolvedores estão se queixando de que as operações de inserção e exclusão estão demorando muito na tabela de lançamentos e que a principal consulta do sistema está apresentando lentidão. A consulta é exposta abaixo:

SELECT C.CPF, C.Nome, C.Telefone, L.Valor, L.Tipo, L.Data
FROM
    Clientes C INNER JOIN Lancamentos L ON C.CPF = L.CPF
WHERE
    C.CPF = ‘70015423121’ AND
    YEAR(L.Data) = 2007 AND MONTH(L.Data) = 7

Analisando as estruturas de índices, você identificou a existência dos seguintes índices:

  • Tabela Clientes – IDX_CLI_CPF – Clustered (CPF)
  • Tabela Lancamentos – IDX_LAN_CPF – Clustered (CPF)
  • Tabela Lancamentos – ID_LAN_Data – NonClustered

Os desenvolvedores informaram ainda que os lançamentos são inseridos por ordem de data. O que você faria nessa situação ? Escolha as duas melhores alternativas:

(   ) Trocaria o índice ID_LAN_Data para Clustered e o índice IDX_LAN_CPF para NonClustered
(   ) Trocaria o índice IDX_CLI_CPF da tabela Clientes para NonClustered
(   ) Substituiria as funções YEAR e MONTH por outras construções
(   ) Trocaria a ordem da cláusula WHERE especificando a data antes do CPF
(   ) Criaria uma stored procedure para encapsular essa consulta

Questão 2 – Foi instalado o SQL Server 2005 Express Edition como instância padrão na máquina de todos os desenvolvedores para que os mesmos possam realizar testes utilizando suas próprias estações. Os desenvolvedores queixam-se de que as aplicações funcionam localmente, mas que ao tentar o acesso via rede o mesmo é negado. Anteriormente, ao desenvolver as aplicações com o MSDE esse problema não ocorria. Qual é a provável causa do problema ?

(   ) Provavelmente o firewall do Windows está bloqueando conexões remotas
(   ) O serviço SQL Browser não está iniciado
(   ) Esse é um Bug conhecido e é preciso aplicar o Service Pack 1
(   ) Por padrão, o SQL Server Express rejeita conexões remotas
(   ) No SQL Server 2005 (qualquer versão), é preciso escolher uma porta diferente da 1433

Questão 3 – Os desenvolvedores estão utilizando o novo tipo de dados XML do SQL Server 2005 e desejam extrair informações de uma coluna XML. É preciso realizar uma consulta e converter o valor de um atributo XML em um tipo de dados do SQL Server. Qual método deve ser utilizado para isso ?

(   ) Query
(   ) Value
(   ) Exist
(   ) Modify

Questão 4 – Na instituição onde você trabalha existe um banco de dados SQL Server 2005 com diversas informações financeiras. Você deseja combinar essas informações com algumas taxas fornecidas por um Web Service de uma outra instituição bancária em uma única instrução SQL. O Web Service não foi implementado em uma tecnologia .NET. O que deve ser feito ?

(   ) Isso não é possível de ser feito. Será necessário carregar os dados para uma tabela e combiná-la com as informações financeiras.
(   ) É necessário que o Web Service seja implementado no próprio SQL Server
(   ) É preciso que o Web Service seja implementado utilizando uma tecnologia .NET.
(   ) É necessário criar um assembly que manipule o Web Service e registrá-lo no SQL Server

Questão 5 – O implementador de banco de dados criou uma tabela de Clientes com a opção SET ANSI_NULL_DFLT_ON. A tabela possui uma CHECK Constraint e uma trigger AFTER. A CHECK Constraint verifica se a coluna “Renda” é maior que zero e a trigger faz um registro em uma tabela de auditoria informando o nome do operador que cadastrou o cliente. Acidentalmente um operador tentou realizar dois cadastros "Incorretos". No primeiro cadastro ele informou a renda igual a zero e no segundo a renda nula. Marque todas as alternativas corretas:

(   ) Ocorrerá um erro nos dois cadastros, mas as triggers irão disparar
(   ) Ocorrerá um erro nos dois cadastros e as triggers não irão disparar
(   ) O 1º cadastro será rejeitado e o 2º permitido e haverá disparo das triggers
(   ) O 1º cadastro será permitido e o 2º rejeitado e haverá disparo das triggers
(   ) O 1º cadastro irá falhar e a trigger não será disparada. O segundo cadastro será feito normalmente com disparo da trigger.

Questão 6 – A equipe de administradores de banco de dados da empresa XYZ realizou os seguintes backups em uma base de dados:

  • Dom 03:00 – Backup full
  • Seg (09:00 às 19:00) – Backup de log de hora em hora
  • Seg (20:00) – Backup diferencial
  • Ter (09:00 às 19:00) – Backup de log de hora em hora
  • Ter (20:00) – Backup diferencial
  • Qua (09:00 às 19:00) – Backup de log de hora em hora

Na quarta-feira às 19:25 houve o encerramento das atividades naquele banco de dados. Às 19:30, o arquivo de dados corrompeu-se e o banco de dados entrou em estado de Suspect. Você faz parte da equipe de administradores de banco de dados e precisa voltar o banco de dados minizando a perda de dados. Marque quatro das respostas abaixo:

(   ) Restaurar backup full
(   ) Restaurar todos os backups de log de segunda-feira
(   ) Restaurar o backup diferencial da segunda-feira
(   ) Restaurar todos os backups de log de terça-feira
(   ) Restaurar o backup diferencial da terça-feira
(   ) Restaurar todos os backups de log de quarta-feira
(   ) Realizar e restaurar um backup de log do tipo tail log
(   ) Reconstruir as transações de 19:00 a 19:25 com o comando DBCC LOG

Questão 7 – Uma determinada aplicação está causando muita lentidão em um servidor de banco de dados SQL Server 2005. Qual ferramenta deveria ser utilizada para identificar as consultas mais lentas ?

(   ) SQL Surface Area Configuration
(   ) SQL Server Management Studio
(   ) System Monitor
(   ) Database Engine Tuning Advisor
(   ) SQL Profiler

Questão 8 – Uma tabela de clientes possui uma coluna sexo com dois valores possíveis (M – Masculino e F – Feminino). Grande parte das consultas considera o sexo como critério de pesquisa na cláusula WHERE juntamente com outros campos. Que tipo de índice que deve ser utilizado nessa coluna ?

(   ) Clustered Index
(   ) Nonclustered Index
(   ) Bitmap Index
(   ) Não deve ser utilizado um índice nessa coluna por sua alta densidade
(   ) Não deve ser utilizado um índice nessa coluna por sua alta seletividade

Questão 9 – Qual das alternativas é desnecessária para a criação de views indexadas ?

(   ) Os objetos devem estar no mesmo banco de dados
(   ) É necessário especificar a cláusula SCHEMABINDING
(   ) A opção CHECK OPTION precisa estar presente
(   ) As funções MIN, MAX e AVG não podem ser utilizadas

Questão 10 –  Um determinado banco de dados está com alto crescimento de seus arquivos de log deixando o servidor com pouco espaço em disco. O banco de dados está no ambiente de desenvolvimento e não necessita de um restore em um ponto específico. Qual a melhor medida a ser adotada para impedir o alto crescimento dos arquivos de log ?

(   ) O Recovery Model deve ser marcado como FULL
(   ) O Recovery Model deve ser marcado como Simple
(   ) O Recovery Model deve ser marcado como Bulk Logged
(   ) A opção “Trunc. Log On Chkpt Option” deve estar marcada

Questão 11 – Em um determinada rede existem dois servidores (SQL1 e SQL2) e dois bancos de dados (BD1 e BD2). O banco BD1 está em SQL1 e o banco BD2 está em SQL2. É preciso que que o banco BD1 consiga enxergar os dados no banco BD2 em SQL2. Qual medida garante isso ?

(   ) Configurar um linked server do servidor SQL1 para o servidor SQL2
(   ) Configurar um linked server do servidor SQL2 para o servidor SQL1
(   ) Configurar a replicação de dados do servidor SQL2 para o servidor SQL1
(   ) Configurar o servidor SQL2 como Remote Server no SQL1

Questão 12 – Um banco de dados de 40GB sofreu as seguintes ações:

  • Seg 02:00 – Backup full
  • Seg 12:00 – Database Snapshot
  • Seg 13:00 – Backup diferencial
  • Seg 15:00 – Back de log
  • Seg 17:00 – Backup de log
  • Seg 18:00 – Database Snapshot
  • Seg 19:00 – Backup de log

Às 18:00 ocorreu o fechamento contábil e as atividades nesse banco de dados foram encerradas. Um desenvolvedor realizou um update erroneamente invalidando o banco de dados. Que medidas devem ser realizadas para restaurar o banco de dados no estado das 18:00 o mais rápido possível ?

(   ) Restauração do backup full
(   ) Exclusão do Snapshot (12h)
(   ) Restauração do backup diferencial
(   ) Restauração de todos dos backups de log
(   ) Reversão para o Snapshot (18h)
(   ) Aplicação do último backup de log com a opção STOPAT para às 18h

Agora que as questões foram expostas, vejamos o gabarito comentado.

Questão 1 – Você é o DBA sênior de uma grande e respeitada instituição financeira. Os desenvolvedores estão se queixando de que as operações de inserção e exclusão estão demorando muito na tabela de lançamentos e que a principal consulta do sistema está apresentando lentidão. A consulta é exposta abaixo:

SELECT C.CPF, C.Nome, C.Telefone, L.Valor, L.Tipo, L.Data
FROM
    Clientes C INNER JOIN Lancamentos L ON C.CPF = L.CPF
WHERE
    C.CPF = ‘70015423121’ AND
    YEAR(L.Data) = 2007 AND MONTH(L.Data) = 7

Analisando as estruturas de índices, você identificou a existência dos seguintes índices:

  • Tabela Clientes – IDX_CLI_CPF – Clustered (CPF)
  • Tabela Lancamentos – IDX_LAN_CPF – Clustered (CPF)
  • Tabela Lancamentos – ID_LAN_Data – NonClustered

Os desenvolvedores informaram ainda que os lançamentos são inseridos por ordem de data. O que você faria nessa situação ? Escolha as duas melhores alternativas:

(   ) Trocaria o índice ID_LAN_Data para Clustered e o índice IDX_LAN_CPF para NonClustered
(   ) Trocaria o índice IDX_CLI_CPF da tabela Clientes para NonClustered
(   ) Substituiria as funções YEAR e MONTH por outras construções
(   ) Trocaria a ordem da cláusula WHERE especificando a data antes do CPF
(   ) Criaria uma stored procedure para encapsular essa consulta

O fato do índice ser clustered, influenciará na organização dos registros da tabela. Se o índice IDX_LAN_CPF é clustered, ele se beneficiaria bastante se os registros fossem inseridos na ordem de CPF. Como uma tabela de lançamentos bancários jamais terá seus registros inseridos por ordem de CPF, esse índice é uma péssima escolha para Clustered. Ainda que o fato dele ser clustered beneficiasse os JOINs, a quantidade de INSERTs fora da ordem de CPF provoca uma grande fragmentação no índice (e por conseqüência a tabela) retardando inserções e exclusões além de provocar diversos splits (possível justificativa para o primeiro problema). Por outro lado se os registros são inseridos por ordem de data, clusterizar um índice com base na data não irá provocar diversos splits e fragmentações. Como a consulta pesquisa um RANGE de data, um índice clustered sobre esse campo seria uma implementação melhor.

O uso de funções como YEAR, MONTH, DAY, CONVERT, etc sobre um coluna na cláusula WHERE pode provocar full scans (seja de índice ou de tabela), pois, será necessário aplicar a função sobre cada registro para comparar seu resultado e realizar o cálculo. Mesmo que um índice exista sobre o campo Data, não há como garantir que a ordem do índice será a mesma ordem do resultado da função. Substituir o predicado YEAR(L.Data) = 2007 AND MONTH(L.Data) = 7 por Data >= ‘20070701’ AND Data < ‘20070801’ contemplará todos os dias do mês de julho (7) do ano de 2007 e representa uma construção muito mais eficiente uma vez que o índice será corretamente utilizado.

Trocar o índice da tabela IDX_CLI_CPF para NonClustered não é uma boa opção, uma vez que não foi especificado qualquer informação sobre a ordem em que os clientes são inseridos e excluídos. Como a consulta é baseada em um único CPF, não haveria diferenças significativas de desempenho, uma vez que o CPF é uma coluna muito restritiva.

Trocar a ordem das colunas na cláusula WHERE não tem absolutamente nenhum efeito na execução e no resultado da consulta. Definitivamente não faz qualquer diferença em perguntar "Quais foram os lançamentos do cliente de CPF 70015423121 em julho de 2007 ?" ou "Quais foram os lançamentos em julho de 2007 do cliente de CPF 70015423121 ?"

Uma stored procedure pode melhorar o desempenho em boa parte das situações através do plano pré-compilado, mas uma vez que a consulta já esteja lenta em virtude da indexação, haveria uma melhora praticamente imperceptível, pois, embora o plano pré-compilado estivesse presente, o plano de execução da consulta continuaria muito ruim.

Questão 2 – Foi instalado o SQL Server 2005 Express Edition como instância padrão na máquina de todos os desenvolvedores para que os mesmos possam realizar testes utilizando suas próprias estações. Os desenvolvedores queixam-se de que as aplicações funcionam localmente, mas que ao tentar o acesso via rede o mesmo é negado. Anteriormente, ao desenvolver as aplicações com o MSDE esse problema não ocorria. Qual é a provável causa do problema ?

(   ) Provavelmente o firewall do Windows está bloqueando conexões remotas
(   ) O serviço SQL Browser não está iniciado
(   ) Esse é um Bug conhecido e é preciso aplicar o Service Pack 1
(   ) Por padrão, o SQL Server Express rejeita conexões remotas
(   ) No SQL Server 2005 (qualquer versão), é preciso escolher uma porta diferente da 1433

O SQL Server 2005 e 2008 possuem a opção de permitir conexões remotas. Essa opção pode vir desabilitada em algumas edições como a Standard Edition ou a WorkGroup Edition. Como pressupõe-se que o SQL Server Express é utilizado para pequenas aplicações e quase sempre monousuários, por padrão o SQL Server Express bloqueia conexões remotas e é necessário mudar essa opção para que elas sejam possíveis. Seu antecessor, o MSDE não tinha esse tipo de controle e por isso o problema não era perceptível.

O firewall do Windows pode ser um problema comum de rejeição de conexões remotas. Se o firewall bloqueia o tráfego na porta utilizada para conexões no SQL Server Express, é normal que as mesmas sejam recusadas. Em todo caso, o MSDE funcionava anteriormente e isso significa que o firewall não estava configurado para impedir conexões remotas.

O SQL Browser é um serviço que surgiu a partir do SQL Server 2005 que ajuda na conexão de instâncias nomeadas. Instâncias nomeadas (Computador\SQLExpress por exemplo) possuem um processo de conexão e autenticação um pouco diferente e mais complexo (dependendo do driver). Boa parte dos problemas de conexão com instâncias nomeadas ocorre porque a resolução do nome da instância e descoberta da porta não pode ser realizado. Quando SQL Browser está iniciado, ele auxilia nesse processo diminuindo a incidência de problemas desse tipo. Como a instância é a Default e não se trata de uma instância nomeada, o SQL Browser não irá auxiliar no processo de resolução do nome da instância de descoberta da porta. Ele pode ser inclusive parado se a instância utilizada for a Default. Para instâncias nomeadas, é bom que ele esteja iniciado (embora não se aplique nesse caso).

O Service Pack 1 do SQL Server 2005 tornou possível que algumas funcionalidades passassem a ser suportadas (a exemplo do Database Mirror). É sempre recomendável estar com o Service Pack mais recente na maioria das situações, mas a aplicação desse Service Pack não está relacionada a esse problema, uma vez que não se trata de um BUG, mas uma configuração.

O SQL Server pode trabalhar com outras portas que não a 1433. A porta 1433 é a porta utilizada em instâncias padrão (instâncias nomeadas semprem escolhem uma porta diferente a menos que sejam forçadas a escolher a 1433). É uma boa prática utilizar outras portas, pois, a 1433 é sempre a mais visada, mas o SQL Server 2005 não força a escolha de uma porta diferente da 1433.

Questão 3 – Os desenvolvedores estão utilizando o novo tipo de dados XML do SQL Server 2005 e desejam extrair informações de uma coluna XML. É preciso realizar uma consulta e converter o valor de um atributo XML em um tipo de dados do SQL Server. Qual método deve ser utilizado para isso ?

(   ) Query
(   ) Value
(   ) Exist
(   ) Modify

O tipo de dados XML introduzido no SQL Server 2005 permite trabalhar com XML de uma forma mais nativa além de cobrir várias lacunas existentes no SQL Server 2000. O método Value é o método capaz de extrair nós do XML e converter para um tipo presente no SQL Server (Int, Varchar, etc). É preciso que a consulta retorne um valor atômico para que ele possa ser utilizado.

O método Query é capaz de extrair informações de um tipo de dados XML com um excelente suporte a XPath (melhorado ainda mais no SQL Server 2008). O retorno do método Query é também um tipo XML e não é possível utilizar esse tipo de dados para conversão para outros tipos.

O método Exist é utilizado para verificar se uma determinada condição é verdadeira através de uma assertiva escrita em XPath / XQuery (consultar se um elemento existe, se um atributo é ou não igual a um valor especificado, etc). Não é possível utilizar esse método para extrair informações para um tipo de dados do SQL Server.

O método Modify permite que colunas XML possam ter seu conteúdo alterado. Ao invés de copiar todo o documento XML, realizar uma pequena alteração e regravar o documento, o método Modify permite que pequenas alterações sejam realizadas diretamente contra o documento XML incorrendo em menos I/O e mais desempenho principalmente se índices XML estiverem presentes. Por ser um método de alteração e não de consulta não será possível utilizá-lo para extrair informações para outros tipos de dados.

Questão 4 – Na instituição onde você trabalha existe um banco de dados SQL Server 2005 com diversas informações financeiras. Você deseja combinar essas informações com algumas taxas fornecidas por um Web Service de uma outra instituição bancária em uma única instrução SQL. O Web Service não foi implementado em uma tecnologia .NET. O que deve ser feito ?

(   ) Isso não é possível de ser feito. Será necessário carregar os dados para uma tabela e combiná-la com as informações financeiras.
(   ) É necessário que o Web Service seja implementado no próprio SQL Server
(   ) É preciso que o Web Service seja implementado utilizando uma tecnologia .NET.
(   ) É necessário criar um assembly que manipule o Web Service e registrá-lo no SQL Server

O SQL Server 2005 introduziu o SQLCLR (melhorado ainda mais no SQL Server 2008) que permite entre várias coisas, criar funções, stored procedures, triggers, etc utilizando o framework .NET. Se o WebService é externo à organização, é preciso criar uma function utilizando o SQLCLR, compilá-la e gerar o Assembly para posteriormente registrar no SQL Server. Anteriormente era possível fazer isso através de Extended Stored Procedures, mas sua escrita era bem mais complexa e o processo do SQL Server tornava-se mais vulnerável a erros de aplicação. Uma aplicação clássica é utilizar um WebService para realizar conversões de moeda e retornar o valor corrigido em uma instrução SELECT.

O SQL Server 2005 disponibilizou o recurso de WebServices nativo. Ao invés de utilizar o IIS ou outro servidor de aplicação para hospedar o WebService que roda uma aplicação que acessa o SQL Server, é possível fazer com que requisições HTTP acessem o SQL Server diretamente sem passar por um servidor de aplicação. O desempenho tende a ser melhor já que há menos camadas, além de representar uma outra forma de acesso que não o TDS (Tabular Data Stream) clássico utilizado pela maioria de drivers. Em contrapartida se as requisições HTTP são feitas diretamente ao SQL Server, a segurança deve ser muito reforçada. O requisito é que o SQL Server acesse um WebService externo. Nesse caso ele é um consumidor de WebServices e não um provedor. Não há porque implementar o WebService no SQL Server.

O WebService é peça chave em questões de interoperabilidade já que utiliza protocolos e especificações não proprietárias. Se o framework .NET é capaz de trabalhar com WebServices implementados em outras tecnologias, não é preciso que o SQLCLR esteja limitado a um WebService implementado em .NET.

Questão 5 – O implementador de banco de dados criou uma tabela de Clientes com a opção SET ANSI_NULL_DFLT_ON. A tabela possui uma CHECK Constraint e uma trigger AFTER. A CHECK Constraint verifica se a coluna “Renda” é maior que zero e a trigger faz um registro em uma tabela de auditoria informando o nome do operador que cadastrou o cliente. Acidentalmente um operador tentou realizar dois cadastros. No primeiro cadastro ele informou a renda igual a zero e no segundo a renda nula. Marque todas as alternativas corretas:

(   ) Ocorrerá um erro nos dois cadastros, mas as triggers irão disparar
(   ) Ocorrerá um erro nos dois cadastros e as triggers não irão disparar
(   ) O 1º cadastro será rejeitado e o 2º permitido e haverá disparo das triggers
(   ) O 1º cadastro será permitido e o 2º rejeitado e haverá disparo das triggers
(   ) O 1º cadastro irá falhar e a trigger não será disparada. O segundo cadastro será feito normalmente com disparo da trigger.

Triggers AFTER são disparados após o evento de origem ter sido executado. Se uma CONSTRAINT for violada (PK, FK, NOT NULL, Check, etc) o evento não será concluído e por isso uma trigger AFTER não poderá ser disparada. No caso do 1º cadastro a renda é igual a zero e portanto a Check Constraint é violada. Essa violação irá impedir que o registro seja inserido e a trigger não será disparada. No caso do 2º cadastro, a renda nula é permitida, pois, por padrão CHECK Constraint não rejeitam registros nulos (a menos que se coloque essa restrição na especificação da CHECK Constraint). O uso da opção SET ANSI_NULL_DFLT_ON garante que a coluna foi criada de forma a permitir valores nulos e portanto o 2º registro será considerado válido. Uma vez que o registro foi realizado sem violar constraints, a trigger será disparada para o 2º cadastro.

Questão 6 – A equipe de administradores de banco de dados da empresa XYZ realizou os seguintes backups em uma base de dados:

  • Dom 03:00 – Backup full
  • Seg (09:00 às 19:00) – Backup de log de hora em hora
  • Seg (20:00) – Backup diferencial
  • Ter (09:00 às 19:00) – Backup de log de hora em hora
  • Ter (20:00) – Backup diferencial
  • Qua (09:00 às 19:00) – Backup de log de hora em hora

Na quarta-feira às 19:25 houve o encerramento das atividades naquele banco de dados. Às 19:30, o arquivo de dados corrompeu-se e o banco de dados entrou em estado de Suspect. Você faz parte da equipe de administradores de banco de dados e precisa voltar o banco de dados minizando a perda de dados. Marque quatro das respostas abaixo:

(   ) Restaurar backup full
(   ) Restaurar todos os backups de log de segunda-feira
(   ) Restaurar o backup diferencial da segunda-feira
(   ) Restaurar todos os backups de log de terça-feira
(   ) Restaurar o backup diferencial da terça-feira
(   ) Restaurar todos os backups de log de quarta-feira
(   ) Realizar e restaurar um backup de log do tipo tail log
(   ) Reconstruir as transações de 19:00 a 19:25 com o comando DBCC LOG

Os backups realizados cobrem até o momento de 19:00 da quarta-feira quando foi realizado o último backup de log. Como o arquivo corrompido foi o arquivo de dados (MDF), o arquivo de log (LDF) ficou intacto e possui as transações até o momento da falha. Mesmo o banco em um estado de Suspect, é possível realizar um backup de log se o arquivo de log estiver intacto e salvar as transações entre o último backup de log e o momento da falha. Essa modalidade de backup é conhecida como Tail Log. Um backup do tipo Tail Log preservaria em um arquivo as transações entre 19:00 e 19:25 da quarta-feira permitindo que os backups cobrissem até o momento da falha.

A seqüência natural seria restaurar o backup full para deixar o banco na situação de domingo (03:00), aplicar o último diferencial para deixar o banco na situação de terça-feira (20:00), aplicar os logs da quarta-feira para deixar o banco na situação de quarta-feira (19:00) e posteriormente aplicar o backup na modalidade tail log para recuperar as transações até 19:25 da quarta-feira não incorrendo em nenhuma perda de dados. Opcionalmente pode-se usar o parâmetro STOPAT para especificar um momento de parada em 19:25.

Uma vez que há um backup diferencial na terça-feira, esse backup terá todas as alterações do último backup full (domingo às 03:00) até o momento do backup diferencial (terça-feira às 20:00). Não é necessário utilizar os logs de segunda-feira. Eles seriam úteis para restaurar outros pontos (ex: segunda-feira às 16:00) anteriores ao diferencial de segunda-feira.

O backup diferencial da segunda-feira tem todas as alterações desde o último backup full (domingo às 03:00) até o momento do backup diferencial (segunda-feira às 20:00). Uma vez que há um backup diferencial na terça-feira é mais rápido utilizá-lo, pois, ele está em um ponto mais próximo do desejado (quarta-feira às 19:25). Não há necessidade de utilizar o backup diferencial de segunda-feira.

O backup diferencial da terça-feira já deixará o banco de dados na situação de terça-feira às 20:00. Os logs não serão necessários para a restauração no ponto de quarta-feira às 19:25. Eles seriam úteis para restaurar outros pontos (ex: terça-feira às 16:00) anteriores ao diferencial de terça-feira.

O comando DBCC LOG é um comando não documentado que fornece algumas informações sobre o log de transações. De forma nenhuma ele é capaz de reconstruir transações em um banco no estado de SUSPECT.

Questão 7 – Uma determinada aplicação está causando muita lentidão em um servidor de banco de dados SQL Server 2005. Qual ferramenta deveria ser utilizada para identificar as consultas mais lentas ?

(   ) SQL Surface Area Configuration
(   ) SQL Server Management Studio
(   ) System Monitor
(   ) Database Engine Tuning Advisor
(   ) SQL Profiler

O SQL Server Profiler é uma excelente ferramenta para monitorar que comandos estão sendo repassados ao SQL Server (a partir do SQL Server 2005 é possível capturar comandos MDX contra o Analysis Services também). Das ferramentas citadas é a mais indicada para investigar problemas de lentidão. DMVs e DMFs também seriam muito úteis (embora não estejam listadas).

O SQL Server Surface Area Configuration é uma ferramenta de configuração (substituida no SQL Server 2008 pelo Policy Management) que serve para configurar serviços, conexões e funcionalidades. Ela não será útil para descobrir quais são as consultas mais lentas.

O SQL Server Management Studio pode ser usado para identificar as consultas mais lentas desde que em conjunto com o SQL Server Performance Dash Board. Ele sozinho não é capaz de apontar as consultas mais lentas. O Profiler é uma opção mais adequada.

O Database Engine Tuning Advisor é o substituto do Index Tuning Wizard sendo indicado para sugerir a criação de índices e estatísticas para melhorar o desempenho das consultas. Embora seja uma boa ferramenta na resolução de problemas de desempenho, ele não pode ser utilizado para identificar as consultas mais lentas.

Questão 8 – Uma tabela de clientes possui uma coluna sexo com dois valores possíveis (M – Masculino e F – Feminino). Grande parte das consultas considera o sexo como critério de pesquisa na cláusula WHERE juntamente com outros campos. Que tipo de índice que deve ser utilizado nessa coluna ?

(   ) Clustered Index
(   ) Nonclustered Index
(   ) Bitmap Index
(   ) Não deve ser utilizado um índice nessa coluna por sua alta densidade
(   ) Não deve ser utilizado um índice nessa coluna por sua alta seletividade

Considerando a ausência de maiores detalhes sobre a distribuição dos dados (não há como saber se existem mais mulheres ou homens) teremos que considerar que a quantidade de mulheres e homens são próximas. Se a coluna sexo permite apenas os valores M e F e há aproximadamente metade de homens e metade de mulheres, então toda vez que o índice fosse utilizado teria que procurar em aproximadamente metade dos registros. Pesquisar um índice para posteriormente pesquisar metade dos registros é muito dispendioso e nesse caso é melhor ignorá-lo já que a coluna sexo é muito densa. Um coluna é dita muito densa quanto possui uma alta taxa de repetição. Colunas como Sexo, Estado Civil e raça são exemplos de colunas densas, pois, devido a existirem poucos valores possíveis, irão levar a altas taxas de repetição. Essas colunas podem ser utilizadas em índices para "coberturas", mas para filtros é um total desperdício já que quase sempre serão ignoradas (não podemos adotar a idéia de coberturas, pois, o enunciado não especificou que tipo de consultas são realizadas e nem outros detalhes de estrutura).

Utilizar um índice clustered sobre colunas do tipo Sexo é uma péssima escolha, pois, os índices clustered influenciam na forma como os registros são armazenados. Como os registros não são inseridos por ordem de Sexo, um índice clustered na coluna sexo irá provocar uma alta taxa de page splits e fragmentação além do que não é interessante eleger uma coluna com repetições para índices clustered (embora seja possível).

O uso de um índice NonClustered não é indicado em virtude da grande taxa de repetições. Possivelmente ele seria ignorado nas consultas, mas seria um retardo adicional para as instruções de INSERT e DELETE (UPDATE somente no caso da mudança de sexo).

O índice do tipo Bitmap é altamente indicado para colunas com poucas opções de valores e seria muito indicado para uma coluna do tipo Sexo. Entretanto atualmente esse tipo de índice não está disponível para o SQL Server e não pode ser utilizado.

A seletividade significa o quanto um registro pode ser diferente em relação aos demais. Se um consulta retorna um registro em um universo de um bilhão, pode-se dizer que a consulta é altamente seletiva (0,0000001% de retorno). Se uma consulta retorna 7 registros em um universo de 10, pode-se dizer que a consulta é muito pouco seletiva (70%) de retorno. Quanto mais seletiva for uma coluna, melhor candidata ela se torna para um índice. Colunas como Sexo tem baixa seletividade.

Questão 9 – Qual das alternativas é desnecessária para a criação de views indexadas ?

(   ) Os objetos devem estar no mesmo banco de dados
(   ) É necessário especificar a cláusula SCHEMABINDING
(   ) A opção CHECK OPTION precisa estar presente
(   ) As funções MIN, MAX e AVG não podem ser utilizadas

As Views Indexadas (ou Materialized Views em outros SGBDs) permitem que uma consulta materialize os dados, ou seja, a View passa a guardar uma réplica dos dados e nesse caso, o acesso é mais rápido, pois, JOINs, GROUP BYs, etc são desnecessários uma vez que os dados já estão materializados na View e podem ser acessados diretamente. A principal desvantagem é que qualquer alteração nos dados que compõe a View irão provocar alterações nos dados que foram materializados incorrendo em perda de desempenho para operações de INSERT, DELETE e UPDATE. Views Indexadas possuem uma série de restrições que podem ser encontradas no Books OnLine (não é qualquer View que pode ser indexada). A opção CHECK OPTION não é uma obrigatoridade para que uma view possa ser indexada.

Views Indexadas só podem referenciar objetos no mesmo banco de dados. Se uma View combina dados de diferentes servidores ou banco de dados, essa View não poderá ser indexada.

A cláusula SCHEMABINDING fará com que todas as tabelas e scalar functions utilizadas nas Views não possam ter sua estrutura modificada. Ela é obrigatória na criação de Views Indexadas para prevenir que alterações nos objetos chamados pela View deixem a View inválida (Binding Errors).

Infelizmente se a View possuir funções como MIN, MAX e AVG ela não poderá ser indexada. É possível utilizar as funções SUM e COUNT_BIG para agregar resultados. Com o uso dessas funções é possível calcular o AVG, mas não há um paleativo para o MIN, MAX e outras funções de agregação como STDEV, VAR, etc.

Questão 10 –  Um determinado banco de dados está com alto crescimento de seus arquivos de log deixando o servidor com pouco espaço em disco. O banco de dados está no ambiente de desenvolvimento e não necessita de um restore em um ponto específico. Qual a melhor medida a ser adotada para impedir o alto crescimento dos arquivos de log ?

(   ) O Recovery Model deve ser marcado como FULL
(   ) O Recovery Model deve ser marcado como Simple
(   ) O Recovery Model deve ser marcado como Bulk Logged
(   ) A opção “Trunc. Log On Chkpt Option” deve estar marcada

A opção SIMPLE do Recovery Model fará com que o log tenha um comportamento circular, ou seja, o arquivo é constantemente reutilizado à medida em que as entradas de log mais antigas são excluídas e substituídas por entradas mais novas. Esse tipo de RECOVERY MODEL é indicado para evitar problemas de estouro de log e ambientes menos críticos. Em contra partida, não será possível fazer um backup de log já que o log é reciclado automaticamente. Sem backups de log não é possível retornar o banco de dados em um ponto específico. Se essa característica não é requerida, o Recovery Model SIMPLE é o mais indicado.

No Recovery Model FULL, o log de transações tem um comportamento Archieve, ou seja, as entradas de log são armazenadas no log de transações e não são liberadas até que um backup de log seja feito. Caso o backup de log demore a ser realizado, o log de transações irá crescer. A demora na realização do log pode levar a crescimentos desproporcionais do log de transações e inclusive fazer com que o espaço em disco seja esgotado provocando indisponibilidades em potencial. Esse tipo de Recovery Model é indicado para ambientes de produção, pois, como as entradas de log são arquivadas e podem ter um backup é possível voltar o banco em um momento específico no caso de falha. É necessário uma administração para evitar que o log cresça de forma indesejável.

A opção Trunc. Log On Chkpt Option é o sinônimo do Recovery Model Simple e só está disponível no SQL Server 7. As versões mais recentes não possuem essa opção.

Questão 11 – Em um determinada rede existem dois servidores (SQL1 e SQL2) e dois bancos de dados (BD1 e BD2). O banco BD1 está em SQL1 e o banco BD2 está em SQL2. É preciso que que o banco BD1 consiga enxergar os dados no banco BD2 em SQL2. Qual medida garante isso ?

(   ) Configurar um linked server do servidor SQL1 para o servidor SQL2
(   ) Configurar um linked server do servidor SQL2 para o servidor SQL1
(   ) Configurar a replicação de dados do servidor SQL2 para o servidor SQL1
(   ) Configurar o servidor SQL2 como Remote Server no SQL1

Uma vez que os bancos estejam em servidores separados, não será possível utilizar a nomenclatura baseada em três nomes (Banco.Schema.Objeto), mas sim a nomenclatura baseada em quatro nomes (Servidor.Banco.Schema.Objeto). Para que essa nomenclatura possa ser utilizada é imprescindível que um Linked Server seja criado. Como o banco BD1 deve acessar o BD2 e BD1 está em SQL1 e BD2 está em SQL2, o Linked Server deve ser criado em SQL1 para acessar SQL2. Vale a pena lembrar que o Linked Server é unidirecional (BD1 poderá acessar BD2, mas o contrário não é verdadeiro).

Como o acesso deve ser feito de BD1 para BD2, a configuração do Linked Server deve ser feita em SQL1 para SQL2 e não o contrário. O Linked Server de SQL2 para SQL1 seria necessário se BD2 precisasse acessar os dados de BD1.

A replicação envolve uma cópia de dados e não um acesso remoto. Ela implica em muito mais esforço e há possíveis atrasos já que se trata de uma cópia. Ainda que fosse utilizada, dado o enunciado da questão, o Linked Server seria uma alternativa com um menor esforço.

O Remote Server é uma versão mais antiga do Linked Server e não deve ser utilizada já que é uma solução mais frágil em termos de segurança.

Questão 12 – Um banco de dados de 40GB sofreu as seguintes ações:

  • Seg 02:00 – Backup full
  • Seg 12:00 – Database Snapshot
  • Seg 13:00 – Backup diferencial
  • Seg 15:00 – Backup de log
  • Seg 17:00 – Backup de log
  • Seg 18:00 – Database Snapshot
  • Seg 19:00 – Backup de log

Às 18:00 ocorreu o fechamento contábil e as atividades nesse banco de dados foram encerradas e não houve mais nenhum acesso ou modificação no banco de dados. Durante a madrugada, um script agendado realizou um update erroneamente invalidando o banco de dados. Que medidas devem ser realizadas para restaurar o banco de dados no estado das 18:00 o mais rápido possível ?

(   ) Restauração do backup full
(   ) Exclusão do Snapshot (12h)
(   ) Restauração do backup diferencial
(   ) Restauração de todos dos backups de log
(   ) Reversão para o Snapshot (18h)
(   ) Aplicação do último backup de log com a opção STOPAT para às 18h

O Database Snapshot é uma opção que possibilita "salvar" o banco de dados em pontos específicos. Os Snapshots de 12h e 18h possuem uma versão paralela do banco de dados respectivamente às 12h e às 18h. Uma vez que o Snapshot de 18h tenha a situação do banco às 18h e seja necessário voltar o banco de dados nessa posição, basta apenas executar um comando de Restore com a opção de reverter para o Snapshot de 18h. Como o Restore com o Snapshot só pode ser executado se houver um único Snapshot é necessário excluir o Snapshot de 12h antes de prosseguir.

A utilização de um backup full e combinação com outros tipos de backup (diferencial ou de log) também possibilita avançar até à posição de 18h, mas uma vez que o backup represente uma cópia dos dados, utilizá-los irá requerer muito mais tempo e esforço. O Database Snapshot fará a restauração mais rapidamente, pois, é necessário apenas sobrescrever as páginas de dados que foram alteradas pelo script.

Acho que esse é um bom teste para avaliar os conhecimentos seja de uma vaga de DBA ou de uma preparação de estudos para a certificação. Para um teste mais fidedigno seria de fato preciso uma base maior de questões, mas com essas poucas já é possível ter uma idéia do nível técnico e do que encontrar pela frente. Estarei postando futuramente outras listas de exercícios.

[ ]s,

Gustavo

4 Respostas para “Lista de Exercícios – Avaliação Inicial de Conhecimentos em SQL Server

  1. Obrigado pelos dicas gustavo,fiz os 12 exercicios e vi que aindapreciso estudar bastante para a tao esperada MCTS em MSSQL 2005Abs

  2. Olá Will,Não desanime, pois, essa prova não é tão difícil assim. Se as 12 questões estivessem na prova, boa parte delas estaria entre as mais difíceis. Estude e faça o máximo de simulados que puder. Ainda bem que você descobriu que precisa de preparação antes da prova e não depois (rs). É um economia de U$ 80.00

  3. Ola Gustavo,sobre a prova 70-431 é possivel estudando pelo MOC e TestKing ou para ter melhor aprovaitamento na prova somente com a experencia profissional usando o SQLServer 2005,tambem estou pensando em fazer esse curso online da KaSolution.http://www.ecurso.com.br/produto_pacote.asp?mscssid=&dept_id=2424&pf_id=54Obrigado

  4. Olá Will,Já participei de várias discussões sobre essa prova. Acho que você irá achar muitas informações em:http://social.msdn.microsoft.com/Forums/pt-BR/categories (procure por 70-431 no sistema de busca)http://www.orkut.com (Procure por 70-431 nas comunidades SQL Server Brasil e SQL Brasil)Abs,

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s