Canal DBA - MS SQL Server Related

Erros Com Tabelas: spidxx Attempt to fetch logical page (1:2032) in database 5 failed. It belongs to allocation unit xxxxx.... not to xxxxx....

2011-02-08T12:26:00.000-08:00

Primeiro conselho que dou é checar o banco de dados na qual as tabelas estão com um DBCC CHECKDB.
Os erros apresentados no output do DBCC são semelhantes aos demonstrados abaixo;

Msg 2533, Level 16, State 1, Line 1
Table error: page (1:540) allocated to object ID 325576198, index ID 1, partition ID 72057625823870976, alloc unit ID 72057625827803136 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.

Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 325576198, index ID 1, partition ID 72057625823870976, alloc unit ID 72057625827803136 (type In-row data). Page (1:540) was not seen in the scan although its parent (1:546) and previous (1:399) refer to it. Check any previous errors.

Tendo os Object ID em mão fica fácil determinar onde os problemas estão, basta rodar a query abaixo;

SELECT * FROM sys.objects
WHERE object_ID in ('325576198') -- Utilizo o IN aqui, pois podemos ter vários objetos com problemas

Agora que temos os objetos / tabelas com problemas, vamos dar o Fix neste problema.

- Deixe o DB em Single_User
- Rode o Comando DBCC CHECKDB(nome_db, repair_allow_data_loss)
- Volte o DB para Multi_User

Isso irá resolver o seu problema de Fetch Logical Pages e também erros de consistência do DB.

Abraços.

Obtendo informações dos SPIDs no pool

2010-11-06T09:20:00.000-07:00

Resolvi postar aqui uma série de scripts para trabalhar com SPIDs logados no SQL Server:

-----------------------------------------------
-- Ver situação do seu SPID atual
SELECT * FROM master.dbo.sysprocesses
WHERE spid = (SELECT CAST(@@spid AS CHAR(10)))
-----------------------------------------------

-----------------------------------------------
-- Ver situação de certo SPID
DECLARE @spid AS INT
SET @spid = 1 -- Mudar este valor

SELECT * FROM master.dbo.sysprocesses
WHERE spid = @spid
-----------------------------------------------

-----------------------------------------------
-- Ver qual o Login Name do seu SPID
SELECT spid, loginame FROM master.dbo.sysprocesses
WHERE spid = (SELECT CAST(@@spid AS CHAR(10)))
-----------------------------------------------

-----------------------------------------------
-- Ver qual o Login Name de certo SPID
DECLARE @spid AS INT
SET @spid = 1

SELECT spid, loginame FROM master.dbo.sysprocesses
WHERE spid = @spid
-----------------------------------------------

Abraço.

Error Handling no SQL Server

2010-11-06T09:13:00.000-07:00

Trabalhar com programação em T-SQL ficou ainda melhor com Error Handling, podemos dar direções diferentes dependendo do que está acontencendo em nosso código; podemos trabalhar com testes de ambiente etc com essa "nova maravilha" do SQL Server;

Abaixo está um script completo com minhas anotações sobre Error Handling:

-- Cria uma tabela para teste
CREATE TABLE mytable
( ID INT NOT NULL PRIMARY KEY)

-- Inicia uma transação e não trata o erro
-- repare que somente um registro não será inserido
-- na tabela, pois estamos dando commmit na transação
-- e não tratando erros
BEGIN TRAN
INSERT mytable VALUES (1)
INSERT mytable VALUES (1)
INSERT mytable VALUES (2)
COMMIT TRAN

SELECT * FROM mytable -- Seleciona os registros da tabela
TRUNCATE TABLE mytable -- Deleta todos os dados da tabela

--// -------------------------------

-- Após configurar a função XACT_ABORT para ON
-- o sql passará a fazer rollback de toda a transação dentro
-- do bloco de transação
SET XACT_ABORT ON
GO
BEGIN TRAN
INSERT mytable VALUES (3)
INSERT mytable VALUES (3)
INSERT mytable VALUES (4)
COMMIT TRAN
GO
SET XACT_ABORT OFF
GO

SELECT * FROM mytable -- Seleciona os registros da tabela
TRUNCATE TABLE mytable -- Deleta todos os dados da tabela
--// -------------------------------

-- Com o bloco de tratamento de erros é possível
-- direcionar o fluxo para o rollback da transação em
-- vez de tentar comitar as transações corretas.
-- Isto faz com que todo o conteúdo da transação seja desfeito
BEGIN TRY
BEGIN TRAN
INSERT mytable VALUES(1)
INSERT mytable VALUES(1)
INSERT mytable VALUES(2)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'A Transação Não Foi Comitada no Banco'
END CATCH

SELECT * FROM mytable -- Seleciona os registros da tabela
TRUNCATE TABLE mytable -- Deleta todos os dados da tabela

(basta copiar e colar no SQL Server para ter um código mais legível)

Abraço.

Lista de JOBs que falharam no seu ambiente

2010-11-06T09:05:00.000-07:00

O que fazer pra saber quais foram os últimos jobs que tiveram falhas no seu ambiente?
Abaixo postei uma query para simplificar o trabalho de caça aos jobs falhando :)

Como já postei o arquivo em outros sites da internet, vou deixar padrão aqui no meu blog:

--------------------------------------------------------------
/*

Created by - Marcos Rosa

The following select uses the three below tables
from msdb

syscategories: Shows all possible categories
sysjobs: Shows all jobs within the instance
sysjobservers: Shows information for runtime of all jobs

The outcomes information for success or failure of a job is:
0 = Failed
1 = Succeeded
3 = Canceled
5 = Unknown

I used my own aliases to create and label fields feel free to change it at
any time you feel it is reqired.

*/
--------------------------------------------------------------

USE MSDB

SELECT tb.Name, tb.Enabled, tb.Description, tb3.name as Category
, Job_Status =
CASE last_run_outcome
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 3 THEN 'Canceled'
WHEN 5 THEN 'Unknown'
ELSE 'Not for sale'
END , tb2.last_outcome_message as Output_Message
FROM sysjobs as tb, sysjobservers as tb2, syscategories as tb3
WHERE tb.job_id = tb2.job_id and tb.category_id = tb3.category_id
ORDER BY last_run_outcome

Espero que gostem, se tiverem mais informações úteis para a query e estiverem afim de compartilhar, enviem uma nota.

Obrigado e Abraço.

Criando e Trabalhando com CURSOR

2010-11-06T08:53:00.000-07:00

Poucos dias atrás trabalhei com cursor; resolvi então postar aqui um script rápido para você basear seus cursores caso necessário:

Se por exemplo eu quero mostrar todos os nomes contendo Marcos e seus endereços
da minha tabela chamada tabela_funcionarios; Lógicamente se eu tiver que fazer isso na vida real eu escreverei a seguinte Query:

SELECT Nome, Endereco
FROM tabela_funcionarios
WHERE Nome = 'Marcos'

Mas para mostrar como podemos criar um cursor, segue o script abaixo:

DECLARE @Nome char(10)
,@Endereco char(20)

DECLARE cursor_regs CURSOR FOR
SELECT * FROM tabela_funcionarios
FOR READ ONLY

OPEN cursor_regs

FETCH cursor_regs INTO @Nome, @Endereco

WHILE @@FETCH_STATUS = 0
BEGIN

IF @Nome = 'Marcos'
PRINT @Nome + ' ' + @Endereco
ELSE
PRINT 'Nome Comum'
FETCH cursor_regs INTO @Nome, @Endereco
END

CLOSE cursor_regs
DEALLOCATE cursor_regs

Pessoal, tomem muito cuidado quando trabalhar com cursor, atens validem se não existem formas diferentes que sejam menos custosas para o servidor. No caso do nosso exemplo a utilização do cursor foi somente para fins de demonstração, pois está totalmente fora de conceito fazer essa simples query com cursor!

Abraços.

T-SQL Scripts para o Dia Dia

2010-10-11T21:41:00.000-07:00

Olá pessoal,

Devo desculpas pelo tempo que fiquei sem dar as caras no blog e dever vários posts, mas é que estou um tanto atarefado e isto impactou diretamento n omeu rendimento quanto ao blog.

Bom hoje passo rápidamente dois scrips para o dia dia do DBA; Uma das nossas funções como DBA é garantir que nosso ambiente esteja 100% seguro quando falamos de backups. Para isto temos um script básico e facilitador que se pensarmos em conjunto com as funcionalidades de tasks do windows ou até mesmo criar um job com o script,

SELECT @@SERVERNAME
PRINT '------------------
'
SELECT substring(d.name,1,50) as dbname,
CASE b.type
WHEN 'D' THEN 'BACKUP FULL'
WHEN 'I' THEN 'BACKUP DIFFERENCIAL'
WHEN 'L' THEN 'LOG'
WHEN 'F' THEN 'FILE OR FILEGROUP'
ELSE 'NO BACKUP'
END as TYPE
,max(b.backup_start_date) as backup_date
FROM master..sysdatabases d LEFT JOIN msdb..backupset b
ON d.name = b.database_name
WHERE d.name not in ('model','tempdb')
-- AND b.backup_start_date is null
GROUP BY d.name, type order by d.name

Na clausula WHERE, na linha comentada, podemos utilizá-la para buscar somente databases sem quaisquer backups ou sem informações de backups.

Quaisquer dúvidas, post sua mensagem que eu responderei assim que puder.

Abraços.

LIsta de Imagens para o Post Tuning Índex com DTA

2009-12-21T22:13:00.000-08:00

Este post é complemento do último post sobre Tuning Índex com o DTA;

Aqui você encontra a lista das imagens referenciadas no post.

Figura 1. Acessando o Profiler de dentro do SSMS

Figura 2. Configurando a tela do trace

Figura 3. Tela Configurada Para Realização do Trace

Figura 4. Outras Opções de Trace e detalhamento de notificações para cada evento.

Figura 5. T-SQL consultando um valor pelo índex e uma consulta DML incrementando o índex.

Figura 6. Representação do DTA realizando uma otimização de uma TSQL.

Figura 7. Abrindo o DTA (O DTA também pode ser aberto através do SSMS).

Figura 8. Configurando a tela “General” do DTA.

Figura 9. Configurando as opções do tuning.

Figura 10. Utilizando os scripts para melhoria de desempenho das queries.

Tuning Índex com o DTA

2009-12-21T21:55:00.000-08:00

[Obs: Todas as Imagens estão em um post posterior a este]

Tuning Índex com o DTA
Turbine seus índices utilizando ferramentas de desempenho do SQL Server 2005 e novas idéias

Marcos Leandro Rosa
marcosfac@gmail.com
Atualmente trabalha na IBM como DBA SQL Server em projetos internacionais. Atuou mais de 5 anos na área de desenvolvimento de aplicações comerciais nas linguagens Delphi, VB6/VBA, Ajax, Javascript entre outras; desenvolvimento de banco de dados SQL Server 2000 e 2005, graduando em sistemas de informação e certificado em MCTS SQL Server 2005.

Este artigo foca a utilização do DTA (DataBase Engine Tuning Advisor) como a ferramenta principal para tuning de indexes criados em tabelas dentro de uma base de dados. Mas antes de iniciar a leitura sobre o DTA, é crucial que todos tenham em mente o quanto a informação foi valorizada nos tempos atuais, e, como se tornou vital para estratégias empresariais. Para se ter uma noção exata do quanto a informação foi valorizada nos últimos tempos, basta voltar pouco antes dos anos 50, quando empresas dependiam de muitas pessoas trabalhando no armazenamento de documentos em papéis, e comparar com os dias atuais.
Da mesma maneira que tudo se evoluiu, os problemas e a complexidade das coisas também evoluíram e para tanto, renovação e readaptação são duas palavras constantes na vida de todo profissional de TI; Nós como DBA’s temos que estar aptos a conhecer novas formas de trabalhar todos os dias, e é em função disto que este artigo foi escrito.

Como o artigo foca o tuning de base de dados e indica a criação de índices como um primeiro passo para o ganho de performance, vamos primeiramente conhecer o Profiler do SQL Server 2005 para, somente depois, iniciarmos o tuning de uma base.

Entendendo o Profiler do SQL Server 2005

Utilizamos o Profiler quando queremos ver todas as transações que estão ocorrendo dentro de uma determinada base de dados. O artigo foca a utilização do profiler nesta tarefa, pois é uma ferramenta prática de se trabalhar e que nos poupa muito trabalho.
No dia-a-dia, ao analisar a performance de uma base, é possível se deparar com muitos fatores que influenciam na agilidade do nosso banco, por exemplo; Falta de index em consultas, Consultas que não utilizam índices, Hardware, Rede entre outros. Com o Profiler, tudo isso pode ser percebido com passos bastante simples.
O Profiler é uma ferramenta gráfica instalada junto com o SQL Server (neste caso com o 2005) que possibilita capturarmos informações tais como:
1- Login ou logout de um usuário na base de dados;
2- RPC completadas – Indica que a chamada a uma stored procedure remota foi completada;
3- T-SQL batch iniciado e completado – Indica que um batch de comandos T-SQL foi completado ou está iniciando;
4- Conexões existentes – Demonstra conexões existentes assim que o trace é iniciado.

Como o objetivo deste artigo não é ensinar a trabalhar com o profiler, mas sim com o DTA, vamos apenas conhecer como abrir, executar um trace simples e salvar este trace em arquivo. Para isso, no SQL Server Management Studio (SSMS), abra o Profiler acessando o menu Tools à SQL Server Profiler (veja aFigura 1).

Figura 1. Acessando o Profiler de dentro do SSMS

Ao abrir a tela do Profiler, é importante conhecer o nome do servidor que você está querendo acessar, pois somente assim será possível a criação de um novo trace. Agora, para criar um novo trace, acesse o menu File à New Trace e depois conecte-se ao servidor onde você deseja que o trace seja executado.
Quando a conexão for estabelecida, a janela que será mostrada na tela (Figura 2) deverá ser configurada.

Figura 2. Configurando a tela do trace

No campo denominado Trace name coloque o nome do trace, neste exemplo foi utilizado “ExSQLMagazine”, mas este nome pode ser de sua preferência, desde que seja um nome lógico para o cenário no qual você está trabalhando. O artigo foca de forma superficial a utilização do Profiler, portanto veremos apenas recursos básicos do trace. Em seguida, acesse o campo denominado “Use the Template” e utilize o template “TSQL_Duration”, e logo após selecione a opção “Save to file” para salvar o trace em um arquivo com a extensão “trc”. Se você seguiu todos os passos corretamente, sua tela deverá estar configurada da mesma maneira que a Figura 3.
Agora basta clicar no botão “Run” que o trace iniciará. Na configuração anterior, existe a guia “Events Selection” (Figura4), onde podemos configurar várias opções de traces, e os pontos que cada evento pode afetar. Para entender melhor e conhecer os inúmeros eventos que podemos realizar, busque no books online o assunto SQL Server Profiler.

Figura 3. Tela Configurada Para Realização do Trace

Figura 4. Outras Opções de Trace e detalhamento de notificações para cada evento.

Se apenas iniciarmos o trace e nada ocorrer dentro da base de dados, o mesmo não terá validade alguma. Portanto, para efeito de testes, execute a T-SQL proposta na Listagem 1 para realizarmos alguns exercícios com o âmbito de fixarmos o entendimento sobre o Profiler.
Vamos supor que queremos saber dos clientes de número 270 a 280, quantos compraram acima de 10 itens na data superior a 31 de Dezembro de 2001, veja na listagem 1, que a consulta é complexa, pois conta com várias junções com outras tabelas, portanto, este é um bom exemplo para utilizarmos e realizar a análise do profiler e futuramente no DTA.

Listagem 1. Código TSQL para realização do teste
SELECT Production.Product.ProductID, Production.Product.Name AS ProductName, Sales.SalesOrderDetail.SalesOrderID, Sales.SalesOrderDetail.OrderQty,
Sales.SalesOrderHeader.OrderDate, Sales.SalesTerritory.Name AS TerritoryName, Sales.SalesTerritory.CountryRegionCode,
Sales.SalesPerson.SalesPersonID, Person.Contact.FirstName, Person.Contact.Phone
FROM Sales.SalesPerson INNER JOIN Sales.SalesTerritory INNER JOIN
Sales.Customer ON Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID AND
Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID AND Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID AND
Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID ON Sales.SalesPerson.TerritoryID = Sales.SalesTerritory.TerritoryID AND
Sales.SalesPerson.TerritoryID = Sales.SalesTerritory.TerritoryID AND Sales.SalesPerson.TerritoryID = Sales.SalesTerritory.TerritoryID AND
Sales.SalesPerson.TerritoryID = Sales.SalesTerritory.TerritoryID AND Sales.SalesPerson.TerritoryID = Sales.SalesTerritory.TerritoryID INNER JOIN Sales.SalesOrderHeader ON Sales.SalesPerson.SalesPersonID = Sales.SalesOrderHeader.SalesPersonID AND
Sales.SalesTerritory.TerritoryID = Sales.SalesOrderHeader.TerritoryID AND
Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID INNER JOIN
Production.Product INNER JOIN Production.ProductModel ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID INNER JOIN
Sales.SalesOrderDetail ON Production.Product.ProductID = Sales.SalesOrderDetail.ProductID ON
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID INNER JOIN Person.Contact ON Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID AND
Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID AND Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID AND
Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID AND Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID AND
Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID INNER JOIN
Sales.CustomerAddress ON Sales.Customer.CustomerID = Sales.CustomerAddress.CustomerID
WHERE (Sales.SalesPerson.SalesPersonID BETWEEN 270 AND 280)
AND (Sales.SalesOrderDetail.OrderQty > 10)
AND (Sales.SalesOrderHeader.OrderDate >
CONVERT(DATETIME, '2001-12-31 00:00:00', 102))

Para realizar a consulta da Listagem 1 é necessário que você tenha a base de dados AdventureWorks (encontrada em http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en ) importada para a instância que você está realizando o trace. Logo após, abra um novo arquivo de script pelo SSMS e digite a consulta da Listagem 1. Rode a consulta e verifique no trace quantos milissegundos a consulta levou até ser finalizada. Após consultar, rode-a novamente e verifique na nova linha do trace quantos segundos a consulta durou. Observe agora que o tempo da consulta foi mais rápido, mas não se engane. Isso ocorre em função de que quando a consulta é executada pela segunda vez, a mesma deixa de ser uma consulta AD-HOC e fica armazenada em cache.

Agora você já pode parar o trace. Para isto, acesse o menu File à Stop Trace. Como o trace já foi realizado salvando as informações em arquivo, basta fechar a janela do Profiler.
Apesar de extenso, o conteúdo apresentado sobre o Profiler foi superficial e deve ser estudado de forma mais detalhada. Para isso, recomendamos a leitura do artigo sobre o Profiler da SQL Magazine, Edição 55.

Utilizando Índices nas Tabelas
Quando estamos buscando por um determinado assunto em um livro, a maneira mais rápida e fácil é pesquisar o índice do livro para assim acharmos a página que contém tal assunto. Assim é o uso do índex dentro do banco de dados. Quando estamos trabalhando com queries e estas apresentam falta de desempenho, o primeiro recurso que utilizamos, e, que quase sempre resolve o nosso problema, é o uso de índex nas tabelas.
Antes de programar os indexes já vimos que conhecer o cenário e utilizar ferramentas de análise e monitoramento é importante para termos noção exata do que estamos fazendo. Portanto quando estamos no ponto de empregar os índex nas tabelas, passamos a ter outras preocupações, pois ao implementar um índex, operações como INSERT, UPDATE e DELETE perderão desempenho. Então temos mais este ponto de atenção e de confirmação de que implementar queries de forma a ganhar desempenho na base de dados é um exercício bem complicado e deve ser feito de forma inteligente.
Índices são estruturas que possuem algoritmos otimizados para acessar dados. No SQL Server o índex trabalha com o algoritmo B-Tree. Este algoritmo é construído de maneira a armazenar informações da tabela de forma inteligente quando precisa ser consultado. Podemos dizer que um índex é uma cópia da tabela original, mas armazenada de forma inteligente.
O SQL Server atualiza o índice com dados da tabela origem cada vez que a tabela recebe novos dados ou quando seus dados são atualizados. Quando isto ocorre, o resultado das consultas DML (INSERT, UPDATE e DELETE, por exemplo) perdem desempenho. Observe na Figura 5 que quando uma consulta T-SQL é acionada para buscar um determinado valor dentro de uma tabela, o acesso a este valor é feito através do índice escrito para essa tabela e deste ponto o SQL Server monta o result set de resposta. Diferente das consultas que não manipulam dados; as DMLs fazem com que a B-TREE seja reestruturada realocando valores nos níveis (levels) root, intermediate ou leaf de acordo com os dados que estão sendo inseridos, modificados ou retirados.

Figura 5. T-SQL consultando um valor pelo índex e uma consulta DML incrementando o índex.

Os tipos existentes de índex são: Clusterizado e Não-Clusterizado. Tanto o índex clusterizado como o não-clusterizado acessam o algoritmo B-Tree, então, qual a diferença? A resposta é que o índex clusterizado quando acessa o level folha (leaf level) o ponteiro já aponta diretamente para os dados que estão sendo pesquisados, e o não-clusterizado, em vez de apontar diretamente para os dados, primeiro encontra um valor referência que daí aponta para os dados.Uma consideração importante ao implementar um índex clusterizado é que o SQL Server só permite um índex por tabela, e como boas práticas, este deve ser criado em uma coluna de chave primária. Ao utilizar o não-clusterizado, você pode implementar até 249 índex, e como boas práticas, é aconselhável que tenhamos no máximo 5 índex para cada tabela. É comum que encontrarmos mais de 5 índices em tabelas, mas como citado, faz parte das boas práticas aconselhadas pela Microsoft termos poucos índices.
Para criar um índex utilize a seguinte sintaxe:

CREATE [ UNIQUE ] [ CLUSTERED NONCLUSTERED ] INDEX index_name
ON( column [ ASC DESC] [ ,…n ] )
[ INCLUDE ( column_name [ ,…n ] ) ]
[ WITH ( [ ,…n ] ) ]
[ ON { partition_scheme_name ( column_name )
filegroup_name
default
}
] [ ; ]

Caso seja necessário desabilitar o índex utilize o comando:

ALTER INDEX { index_name ALL }
ON
DISABLE [ ; ]

Quando um índex é desabilitado, você deve recriá-lo toda vez que quiser tê-lo em sua tabela novamente, pois quando você o recria, o SQL Server faz a população do mesmo com os dados atuais da tabela. Para reconstruir um índex, utilize o seguinte código:

ALTER INDEX { índex_name ALL }
ON
REBUILD [ ; ]

Para mais informações sobre o assunto acesse ‘Books Online’, procurando por: “Covering Index” e “Reational Index”.

Utilizando o DTA para tuning
O DTA ou Data Base Engine Tuning Advisor é uma ferramenta que através da análise sobre as queries de entrada no analisador (workload), comparando o custo de cada uma para a base de dados, indica qual a melhor estrutura para suas tabelas, particionamentos ou views indexadas para resolução de problemas de desempenho. Como já vimos no tópico sobre o Profiler, é interessante que você realize as análises utilizando um arquivo trace gerado pelo Profiler. Dessa forma, você poderá filtrar as análises do DTA estreitando-as a analisar somente queries que quiser; desta maneira você não criará índex nem de menos nem demais e não terá o trabalho de analisar todas as consultas da base de dados a próprio punho. Para utilizar o DTA, vamos rever alguns pontos de observação:
O DTA veio no lugar do Index Tuning Wizard (ITW) utilizado nas versões anteriores ao SQL Server 2005;
Você pode utilizar um arquivo criado pelo Profiler e analisá-lo no DTA;
Você pode exportar os relatórios de melhoria do DTA em arquivos XML para utilizá-los em analisadores de outros SGBDs;
Você pode utilizá-lo para analisar uma única query selecionando-a e escolhendo a tool do DTA no menu tools.;
É importante limitar o tempo de análise do DTA quando estiver com muitas consultas complexas para serem analisadas, senão o DTA passará horas ou até dias, dependendo da quantidade de consultas para dar o retorno;
É importante especificar para o DTA em qual base de dados e tabelas ele rodará as analises;
As análises são baseadas no custo de cada consulta que o query optimizer envia ao DTA mediante pesquisas na base de produção. O DTA simula várias combinações de indexação até não existirem maneiras de otimizar a consulta (Figura 6), para somente então, gerar o relatório final;
Realize as análises do DTA utilizando um backup da sua base de produção em outro servidor para não perder desempenho, pois as análises do DTA são realizadas na base de produção;
O resultado do DTA sempre consiste em criar uma nova estrutura e nunca realizar um delete em alguma existente. Por este motivo, sempre verifique se é necessário manter estruturas de índices na qual você já utilizou em algum momento.

Figura 6. Representação do DTA realizando uma otimização de uma TSQL.

O DTA é uma ferramenta fácil de utilizar e lhe oferece várias horas de tranqüilidade, pois realiza todas as análises de forma automática e facilita a vida dos nossos clientes.
Você pode utilizar o DTA para realizar o tuning de um servidor de produção sem realizar backups. Portanto, para agilizar e automatizar a análise, o aconselhável a fazer é utilizar Jobs que iniciem um trace com o Profiler e de tempo em tempo este trace é parado e importado para dentro das análises do DTA. Escolha a opção para criação de índex online para não bloquear nenhuma transação que possa estar em concorrência com as análises (veja as limitações de uso desta opção para as edições do SQL Server 2005 no books online).
Para configurar o DTA, acesse-o, como indica a Figura 7. Selecione em Workload um arquivo TRC (trace do profiler), de acordo com a Figura 8, e especifique as opções de tuning na guia “Tuning Options”, apresentado na Figura 9.

Figura 7. Abrindo o DTA (O DTA também pode ser aberto através do SSMS).

Figura 8. Configurando a tela “General” do DTA.

No campo “Session name“ (Figura 8) indique um nome de preferência ou deixe o padrão. Após fazer isso, indique o “Workload” (File ou Table). Neste caso, utilize o trace que fizemos na sessão do Profiler. Depois é só indicar a base de dados e tabelas (podendo ser selecionadas na lista dropdown ao lado direito do nome da base de dados) para análise.

Figura 9. Configurando as opções do tuning.

Na guia “Tuning Options” você indica como as análises devem se comportar e quais tipos de análises serão realizados. Como a tela é bem didática, vamos fazer somente algumas considerações; veja que na parte inferior da tela existe o campo “Description”, este campo dá dicas do comportamento da análise a cada escolha que você realiza nesta tela. Para efeito de teste, selecione as duas opções marcadas na Figura 9, e observe que a descrição apontará um erro. A falha está em optar para que o DTA não trabalhe com estratégias de particionamento e ao mesmo tempo pedir para manter os particionamentos alinhados. Como já foi dito, mesmo não existindo um tempo limite é importante limitar este tempo de analise quando estiver trabalhando em uma base de dados de produção. Por este motivo, mantenha o campo “Limit Tuning Time” marcado. Após todas as configurações, clique em “Start Analysis” para permitir o início das analises.
Quando o DTA inicializa as análises ou termina de realizá-las, a tela principal do DTA é configurada com mais três guias, são elas: Progress, que indica o progresso da análise; Recommendations, que são as recomendações feitas para ganho de performance; e por último a Reports, que faz um resumo da análise. Para utilizar as recomendações feitas pelo DTA, basta selecionar na guia Recommendations a coluna Definitions e clicar no link para que o SQL exiba o script de criação de uma nova estrutura para melhorar a performance das queries contidas no arquivo trace (observe a Figura 10).

Figura 10. Utilizando os scripts para melhoria de desempenho das queries.

Para criar as indexes, Estatísticas e/ou Particionamentos, basta clicar no botão “Copy to Clipboard” da janela popup, e utilizar o script dentro do SSMS.
De acordo com o DTA, se programarmos todas as recomendações que ele propôs para o trace que fizemos (uma única T-SQL), o desempenho da consulta pode melhorar em até 75%. Então, siga as recomendações e após implementar todas elas, teste a consulta da Listagem 1 novamente. Perceba que o DTA não pode melhorar a consulta, pois todas as estruturas necessárias para aumentar o desempenho desta T-SQL já foram então, implementadas anteriormente por você.
Caso não queira utilizar as recomendações do DTA no mesmo momento, você pode salvá-las para utilizar futuramente. Pode também re-analisar na guia reports, vários relatórios gerados para cada item analisado.
Uma maneira de conhecer tudo o que as ferramentas do SQL Server 2005 pode lhe trazer é fazer da utilização do Books Online um hábito, pois nele você encontra especificações extremamente ricas para cada mínima coisa que você está pesquisando. Este conselho fica para estudar ainda mais o DTA.
Conclusão
Com um cenário tão complexo no qual estamos, precisamos apreender a trabalhar com o que temos em mãos e buscar aprimorar nosso conhecimento cada vez mais. A atualidade obriga empresas terem lucros altos, mas investindo pouco. Portanto, é importante que cada DBA faça sua parte nessa estratégia. Conhecer não só as ferramentas que nos cercam, mas também o cenário como um todo é muito importante, pois a cada dia surgem novas tecnologias e devemos estar atualizados; Então o artigo foca a importância da visão sistêmica no dia-a-dia e também o que temos de ferramentas para trabalhar dando desempenho total para as nossas queries.

De que se trata o artigo?
Este artigo utiliza o Database Engine Tuning Advisor abordando boas práticas de sua utilização e os cuidados quanto a implementação de índices

Para que serve?
A tecnologia apresentada nos permite analisar de forma minuciosa tudo que pode ser melhorado em uma querie e em outras estruturas como uma view indexada, também nos oferece agilidade e praticidade para tal análise e implementação.

Em que situação é útil?
Útil para ganho de desempenho das queries que possam estar com certa lentidão ao serem utilizadas e também para encontrarmos possíveis erros lógicos e falhas de disco, já que temos como melhor prática, o uso do Profiler, e ele nos mostra tudo isso.

.

Referências
SQL Server Bible - Paul Nielson, Wiley
A Questão da Informação - Publicação da revista São Paulo, n 4, 1994.
Training Kit – Publicado por Solid Quality Learning, 2007.
Inside Microsoft SQL Server 2005: T-SQL Programming – Itzik Ben-Gan, Dejan Sarka e Roger Wolter.

DBA Channel - Welcome

2009-12-07T21:13:00.000-08:00

Hi Folks

First of all I would like to thank you for your visit and also let you know that this blog will be always receving you with open heart!
Here you can find thounsand of articles related to MS SQL daily routine and stuffs. Keep visiting, commenting and talking to us, doing so we ensure a well done comunication and I will be sure that it is an ongoing guideline!

Thanks again!
Marcos Rosa