Vou iniciar uma seqüência de artigos sobre os novos tipos de dados no SQL Server 2008, segue abaixo uma breve descrição:
- DATE: armazena somente a data.
- TIME: armazena somente a hora.
- DATETIMEOFFSET: armazena data e hora obedecendo timezone.
- DATETIME2: similar ao DATETIME, porém com precisão variável.
- GEOMETRY: dados espaciais utilizando representação plana da Terra ("Flat Earth").
- GEOGRAPHY: dados espaciais utilizando representação redonda da Terra ("round earth").
- HIERARCHYID: representa hierarquias.
Os primeiros quatro tipos de dados (DATE, TIME, DATETIMEOFFSET, DATETIME2) são tipos regulares do SQL Server, já os três últimos foram desenvolvidos como UDT (User Defined Type) com .NET.
No próximo post vou falar dos dois primeiros tipos de dados: DATE e TIME.
Até lá, Landry.
Informações técnicas sobre SQL Server 2005, 2008, 2008 R2, 2012, 2014, 2016 e 2017
sábado, 26 de abril de 2008
Novos tipos de dados do SQL Server 2008
terça-feira, 8 de abril de 2008
Reduzindo o espaço ocupado em um Banco de Dados utilizando Vardecimal no SQL Server 2005 SP2
Neste artigo irei mostrar uma novidade no armazenamento do tipo de dados DECIMAL disponível a partir do Service Pack 2 do SQL Server 2005. Este novo recurso pode reduzir o tamanho da linha com consequente redução na ocupação do banco de dados, proporcionando melhor desempenho. Porém a adoção deste novo recurso deverá ser considerado com muito cuidado como veremos neste artigo.
Já uma coluna decimal é armazenada na área da linha destinada a colunas de tamanho fixo, ocupando espaço de acordo com a tabela abaixo:
EXEC sp_tableoption 'TBdecimal', 'vardecimal storage format', 1

Durante muito tempo o recurso de armazenamento variável ficou restrito aos tipos de dados string (varchar, nvarchar, text e ntext) e binário (varbinary e image), restando aos tipos de dados numéricos o armazenamento em tamanho fixo. O grande benefício do armazenamento variável é a redução do tamanho total da linha proporcionando uma melhor ocupação da página de dados (Data Page 8Kb), com conseqüente redução de I/O e melhora no desempenho.
Se uma coluna NomeCliente for do tipo de dado Varchar(100) o SQL Server só irá ocupar um byte por caractere incluído pelo usuário mais dois bytes do ponteiro que irá indicar o término da coluna dentro da linha. Por exemplo, se o usuário incluir “Maria Lucia” na coluna acima o SQL Server irá alocar 11 bytes referentes a cada caractere do nome e 2 bytes para o ponteiro, somando 13 bytes no total!
Se uma coluna NomeCliente for do tipo de dado Varchar(100) o SQL Server só irá ocupar um byte por caractere incluído pelo usuário mais dois bytes do ponteiro que irá indicar o término da coluna dentro da linha. Por exemplo, se o usuário incluir “Maria Lucia” na coluna acima o SQL Server irá alocar 11 bytes referentes a cada caractere do nome e 2 bytes para o ponteiro, somando 13 bytes no total!
Já uma coluna decimal é armazenada na área da linha destinada a colunas de tamanho fixo, ocupando espaço de acordo com a tabela abaixo:
Observando uma coluna Decimal é muito comum termos uma precisão muito maior do que utilizamos na maior parte dos dados, sendo claro o benefício do armazenamento variável na redução do espaço ocupado e conseqüente melhora no desempenho.
Para comprovar a redução do espaço ocupado vamos criar um banco de dados contendo uma tabela com uma coluna Decimal, utilize o script abaixo.
Para comprovar a redução do espaço ocupado vamos criar um banco de dados contendo uma tabela com uma coluna Decimal, utilize o script abaixo.
Create database DBvardecimal
alter database DBvardecimal set recovery simple
go
use DBvardecimal
go
Create table TBdecimal (
alter database DBvardecimal set recovery simple
go
use DBvardecimal
go
Create table TBdecimal (
col1 int identity(1,1),col2 char(500),col3 decimal(22,6))
declare @cont int,@val char(500)
set @cont = 1
while @cont <= 100000 begin
set @cont = 1
while @cont <= 100000 begin
set @val = 'Linha - ' + convert(varchar(20),@cont)
insert TBdecimal (col2,col3) values (@val,12.2)
set @cont = @cont + 1
end
go
Foram incluídas 100.000 linhas na tabela, utilize o script abaixo para retornar o espaço ocupado pela tabela após a inclusão das linhas.
sp_spaceused 'TBdecimal'
-- Linhas: 100000
-- Reservado: 61640 KB
-- Dados: 61600 KB
-- Linhas: 100000
-- Reservado: 61640 KB
-- Dados: 61600 KB
As 100.000 linhas da tabela estão ocupando 61.600 KB em 7.700 páginas de dados (Data Pages 8KB). Agora vamos habilitar o armazenamento variável para o tipo de dado Decimal utilizando o script abaixo.
EXEC sp_db_vardecimal_storage_format 'DBvardecimal', 'ON'
EXEC sp_tableoption 'TBdecimal', 'vardecimal storage format', 1
O primeiro EXEC habilita o armazenamento Vardecimal no Banco de Dados DBvardecimal utilizando a Stored Procedure sp_db_vardecimal_storage_format e o segundo habilita o armazenamento Vardecimal na tabela TBdecimal. Se a Stored Procedure sp_db_vardecimal_storage_format for executada sem parâmetro ela retorna a lista dos Bancos de Dados na Instância indicando indicando se o armazenamento Vardecimal está habilitado ou não.
Basta rodar novamente a Stored Procedure sp_spaceused e comparar os resultados, veja a diferença na tabela abaixo.
Basta rodar novamente a Stored Procedure sp_spaceused e comparar os resultados, veja a diferença na tabela abaixo.
Para determinar se a tabela foi habilitada para o armazenamento Vardecimal utilize a consulta abaixo.
SELECT name, object_id, type_desc FROM sys.objects
WHERE OBJECTPROPERTY(object_id,'TableHasVarDecimalStorageFormat') = 1
WHERE OBJECTPROPERTY(object_id,'TableHasVarDecimalStorageFormat') = 1
A estrutura da linha foi alterada para comportar o tamanho variável no tipo de dados Decimal, por isso o Relational Engine das edições anteriores ao Service Pack 2 não conseguem manipular Bancos de Dados com Vardecimal habilitado.
Se você tentar fazer um Attach pelo Management Studio de um Banco de Dados com Vardecimal habilitado em um SQL Server 2005 SP1 irá receber a seguinte mensagem de erro: “An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.
Se você tentar fazer um Attach pelo Management Studio de um Banco de Dados com Vardecimal habilitado em um SQL Server 2005 SP1 irá receber a seguinte mensagem de erro: “An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.
No log da Instância irá aparecer a mensagem: “The database ‘DBvardecimal’ cannot be opened because it is version 612. This server supports version 611 and earlier. A downgrade path is not supported”.
Já no Restore a mensagem de erro será: “The database was backed up on a Server running version 9.00.3042. That version is incompatible with this server, which is running version 9.00.2047…”.
Já no Restore a mensagem de erro será: “The database was backed up on a Server running version 9.00.3042. That version is incompatible with this server, which is running version 9.00.2047…”.
Muito cuidado! Uma vez habilitado o armazenamento Vardecimal não poderemos mais retornar ao Service Pack 1, por isso testem bastante as aplicações antes de adotar o armazenamento Vardecimal.
Até a próximo post,
Landry.
domingo, 30 de março de 2008
Atualização das Estatísticas de Banco de Dados - Parte 2
Parte 2 – Atualizando as Estatísticas de Banco de Dados
No post anterior (http://sqlserver-brasil.blogspot.com/2008/03/atualizao-das-estatsticas-de-banco-de.html) vimos que o Otimizador de consultas utiliza as informações das Estatísticas de Banco de Dados na escolha do plano de execução, sendo assim fundamental para se ter um bom desempenho mantê-las atualizadas. Se ocorrer uma grande alteração na distribuição de valores em uma coluna indexada e a estatística não for Atualizada, o Otimizador pode escolher um plano de execução menos eficiente.
O SQL Server disponibiliza uma propriedade de banco de dados chamada AUTO UPDATE STATISTICS (habilitada por padrão), que monitora a atualização nas tabelas e dispara um UPDATE STATISTICS quando o volume de alterações for grande. Mesmo com este recurso habilitado, um volume pequeno de alterações pode não disparar a atualização automática das estatísticas e fazer com que o Otimizador tenha como base valores desatualizados, escolhendo o plano de execução menos eficiente.
Por exemplo, vamos utilizar a tabela DETAILS da Parte 1 do artigo, onde a query abaixo retorna 301 linhas sendo mais eficiente o plano de execução com Index Seek e Boulkmark Lookup. Habilitando as estatísticas de I/O observamos:
SET STATISTICS IO ON
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 305
Agora vamos executar uma alteração na tabela modificando a quantidade de linhas com valores em SalesOrderID maiores que 75000.
UPDATE dbo.details SET SalesOrderID = 76000
WHERE SalesOrderID < color="#006600">-- 14.148 linhas alteradas
Esta alteração não provocou a atualização automática das estatísticas, pois representa um volume de linhas alteradas pequeno comparando com o total de linhas que a tabela possui 121.317 (11,66% de linhas alteradas). Executando a query novamente observamos que o Otimizador adotou o mesmo plano de execução (Index Seek com Bulkmark Lookup), porém neste caso o menos eficiente, já que agora o filtro WHERE seleciona 14.449 linhas.
SET STATISTICS IO ON
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 14479
-- Index Seek com Bulkmark Lookup
SELECT * FROM dbo.details with(index(0)) WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 994
-- Table Scan
Neste caso o plano de execução ideal seria o Table Scan, basta comparar o volume de páginas lidas (Logical Reads):
- 14479 páginas lidas com o plano Index Seek com Bulkmark Lookup.
- 994 páginas lidas com o plano Table Scan.
Repare o uso do Hint de tabela with(index(0)), obrigando o Otimizador a resolver a query com Table Scan!
Para atualizar as Estatísticas de Banco de Dados utilizamos à instrução UPDATE STATISTCS, atualizando todas as estatísticas de uma tabela. Executando a instrução abaixo o Otimizador passa a escolher o melhor plano, Table Scan:
UPDATE STATISTICS dbo.details
SET STATISTICS IO ON
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 994
-- Table Scan
Podemos concluir que é necessário atualizar as Estatísticas de Banco de Dados periodicamente, pois apenas a propriedade de banco de dados AUTO UPDATE STATISTICS não garante a freqüência ideal de atualização das estatísticas. O comando UPDATE STATISTICS deve ser executado para cada tabela, um modo mais simples é utilizar a Stored Procedure SP_UPDATESTATS que atualiza as estatísticas de todas as tabelas.
Até o proximo post.
Landry.
No post anterior (http://sqlserver-brasil.blogspot.com/2008/03/atualizao-das-estatsticas-de-banco-de.html) vimos que o Otimizador de consultas utiliza as informações das Estatísticas de Banco de Dados na escolha do plano de execução, sendo assim fundamental para se ter um bom desempenho mantê-las atualizadas. Se ocorrer uma grande alteração na distribuição de valores em uma coluna indexada e a estatística não for Atualizada, o Otimizador pode escolher um plano de execução menos eficiente.
O SQL Server disponibiliza uma propriedade de banco de dados chamada AUTO UPDATE STATISTICS (habilitada por padrão), que monitora a atualização nas tabelas e dispara um UPDATE STATISTICS quando o volume de alterações for grande. Mesmo com este recurso habilitado, um volume pequeno de alterações pode não disparar a atualização automática das estatísticas e fazer com que o Otimizador tenha como base valores desatualizados, escolhendo o plano de execução menos eficiente.
Por exemplo, vamos utilizar a tabela DETAILS da Parte 1 do artigo, onde a query abaixo retorna 301 linhas sendo mais eficiente o plano de execução com Index Seek e Boulkmark Lookup. Habilitando as estatísticas de I/O observamos:
SET STATISTICS IO ON
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 305
Agora vamos executar uma alteração na tabela modificando a quantidade de linhas com valores em SalesOrderID maiores que 75000.
UPDATE dbo.details SET SalesOrderID = 76000
WHERE SalesOrderID < color="#006600">-- 14.148 linhas alteradas
Esta alteração não provocou a atualização automática das estatísticas, pois representa um volume de linhas alteradas pequeno comparando com o total de linhas que a tabela possui 121.317 (11,66% de linhas alteradas). Executando a query novamente observamos que o Otimizador adotou o mesmo plano de execução (Index Seek com Bulkmark Lookup), porém neste caso o menos eficiente, já que agora o filtro WHERE seleciona 14.449 linhas.
SET STATISTICS IO ON
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 14479
-- Index Seek com Bulkmark Lookup
SELECT * FROM dbo.details with(index(0)) WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 994
-- Table Scan
Neste caso o plano de execução ideal seria o Table Scan, basta comparar o volume de páginas lidas (Logical Reads):
- 14479 páginas lidas com o plano Index Seek com Bulkmark Lookup.
- 994 páginas lidas com o plano Table Scan.
Repare o uso do Hint de tabela with(index(0)), obrigando o Otimizador a resolver a query com Table Scan!
Para atualizar as Estatísticas de Banco de Dados utilizamos à instrução UPDATE STATISTCS
UPDATE STATISTICS dbo.details
SET STATISTICS IO ON
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 994
-- Table Scan
Podemos concluir que é necessário atualizar as Estatísticas de Banco de Dados periodicamente, pois apenas a propriedade de banco de dados AUTO UPDATE STATISTICS não garante a freqüência ideal de atualização das estatísticas. O comando UPDATE STATISTICS deve ser executado para cada tabela, um modo mais simples é utilizar a Stored Procedure SP_UPDATESTATS que atualiza as estatísticas de todas as tabelas.
Até o proximo post.
Landry.
Marcadores:
SQL Server 2005,
SQL Server 2008,
Tuning Query
segunda-feira, 24 de março de 2008
Atualização das Estatísticas de Banco de Dados - Parte 1
Parte 1 – Entendendo o uso das Estatísticas de Banco de Dados
Venho trabalhando com consultorias e treinamento em SQL Server desde a versão 7.0, e um dos problemas mais comuns são as consultas de baixo desempenho. Um motivo freqüente para o baixo desempenho de uma consulta é ter Estatísticas de Banco de Dados desatualizadas, pois o Otimizador de consultas acaba selecionando um plano de execução menos eficiente.
Este artigo foi dividido em duas partes, na primeira veremos como o Otimizador de consultas utiliza a Estatística de Banco de Dados para elaborar o plano de execução. Na segunda parte do artigo vou mostrar o prejuízo que uma Estatística desatualizada causa no desempenho de uma consulta.
As Estatísticas de Banco de Dados fornecem informações valiosas ao Otimizador, orientando a escolha do plano de execução de uma query. Ao criar um índice o SQL Server cria automaticamente uma estatística associada, contendo a distribuição dos valores da primeira coluna da chave do índice. Quando a propriedade de banco de dados AutoCreateStatistics está com TRUE (configuração padrão), o Otimizador cria a estatística sempre que detectar sua ausência durante a elaboração de um plano de execução.
Um dos componentes principais das Estatísticas de Banco de Dados é a distribuição de valores da coluna chave, onde é armazenada a quantidade de linhas contendo cada valor individual (se existem muitos valores individuais o SQL Server divide estes valores em intervalos e armazena o total de ocorrência dentro do intervalo).
Vamos observar o uso da estatística na prática criando a tabela abaixo e incluindo algumas linhas:
USE AdventureWorks
GO
IF OBJECT_ID('dbo.details', 'U') IS NOT NULL
DROP TABLE dbo.details
GO
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
OrderQty, ProductID,SpecialOfferID, UnitPrice,
UnitPriceDiscount, ModifiedDate
INTO dbo.details
FROM AdventureWorks.Sales.SalesOrderDetail
CREATE NONCLUSTERED INDEX idx_nc_col2
ON dbo.details(SalesOrderID)
GO
Foi criado um índice na tabela DETAILS utilizando como chave a coluna SALESORDERID, sendo criada uma estatística de banco de dados para esta coluna. Vamos agora trilhar o caminho que o Otimizador utilizou para elaborar o plano de execução da query:
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
Existem dois planos que o Otimizador poderá escolher:
1) Table Scan verificando o filtro WHERE linha a linha
2) Index Seek para resolver o filtro WHERE e depois Bookmark Lookup para recuperar as demais linhas da tabela que não se encontram no índice (veja que no SELECT temos *, isto é, todas as colunas).
Vamos contabilizar o custo de I/O (Imput/Output - leituras de página de dados e índice) necessário para resolver cada plano acima. Para determinar o Plano 1 (Table Scan), o SQL Server recorre ao catálogo para identificar quantas páginas de dados a tabela está ocupando, utilizando a query abaixo:
SELECT rows as QtdLinhas, data_pages Paginas8k
FROM sys.partitions p JOIN sys.allocation_units a
ON p.hobt_id = a.container_id
WHERE p.[object_id] = object_id('details')
and index_id in (0,1)
Resultado:
QtdLinhas: 121317
Paginas8k: 994
Desta maneira o Otimizador identificou que para resolver a query com o Plano 1 (Table Scan) serão necessárias 994 leituras de páginas de 8kb (páginas de dados – data pages).
Para contabilizar o I/O do Plano 2 (Index Seek + Bulkmark Lookup) o Otimizador identifica quantas linhas serão retornadas pelo filtro WHERE SalesOrderID > 75000, para isso ele utiliza a Estatística de Banco de Dados criada quando o índice na coluna SalesOrderID foi gerado. Para visualizar uma Estatística de Banco de Dados o SQL Server disponibiliza a instrução DBCC SHOWSTATISTICS.
DBCC SHOW_STATISTICS ('dbo.details','idx_nc_col2')
Executando esta instrução obtemos o resultado abaixo:

Em azul temos a data e hora que a estatística foi atualizada, informação importante como vocês verão mais a frente. Em vermelho a coluna Steps determina a quantidade de faixas de valores geradas no terceiro e último Grid (o terceiro Grid possui 143 linhas). Se você rolar este Grid até o final veremos os valores abaixo:

Vamos analisar o resultado utilizando as duas últimas linhas em vermelho:
RANGE_HI_KEY – representa o último valor de cada faixa, por exemplo: na linha 142 (em vermelho na figura acima) temos o valor 75122 representando a faixa que tem início no valor 74661 (o valor seguinte da linha 141 valor 74660) até o próprio 75122.
RANGE_ROWS – quantidade de linhas que possuem valores iguais ao da faixa, excluindo o último valor em RANGE_HI_KEY, por exemplo: na linha 142 (em vermelho na figura acima) temos a faixa de 74661 até 75122, porém 1077 representa a quantidade de linhas contendo os valores de 74661 até 75121. O Otimizador não tem como saber a exata distribuição das ocorrências dentro da faixa, porém as demais colunas fornecem uma boa idéia desta distribuição!
EQ_ROWS – quantidade de linhas que possuem o último valor da faixa, definido em RANGE_HI_KEY, por exemplo: na linha 142 (em vermelho na figura acima) podemos afirmar que existem duas linhas na tabela com o valor 75122.
DISTINCT_RANGE_ROWS – quantidade de valores dentro da faixa (SELECT DISTINCT dentro do intervalo da faixa), por exemplo: na linha 142 (em vermelho na figura acima) existem 461 valores dentro da faixa (75122 - 74661 = 461). Observamos que é um seqüencial sem pular valor!
AVG_RANGE_ROWS – igual a RANGE_ROWS / DISTINCT_RANGE_ROWS, por exemplo: 1077 / 461 = 2.336226.
Agora, utilizando a estatística da Figura 2, podemos ter uma idéia aproximada da quantidade de linhas retornadas pelo filtro WHERE SalesOrderID > 75000:
Linha 142 da figura acima: 75121 – 75000 = 121 valores dentro da faixa, em AVG_RANGE_ROWS temos 2.336226 multiplicando por 121 encontramos 282.683346. Somando as duas linhas em EQ_ROWS referente ao valor 75122, temos um total de 284.683346
Linha 143 da figura acima: 3 linhas com valor 75123.
O Otimizador concluiu então que seriam retornadas aproximadamente 287.683346 linhas com o filtro WHERE SalesOrderID > 75000, gerando 287 Bulkmark Lookups para as páginas de dados. Somando-se alguns poucos I/Os para navegar no índice (Index Seek), ficou bem a baixo do Plano 1 (Table Scan) com um total de 994 páginas.
Reparem que analisando a estatística o Otimizador errou por pouco a previsão da quantidade de linhas 287, onde na verdade a query retornou 301 linhas gerando 305 I/Os (301 páginas no Bulkmark Lookups e 4 páginas no Index Seek).

Obs.: No SQL Server 2005 o Bulkmark Lookup aparece no plano de execução composto por duas fases: Nested Loops e RID Lookup (se a tabela não tem índice Cluster) ou Cluster Index Seek (se a tabela tem índice cluster) até SP1, no SP2 aparece como Key Lookup. No SQL Server 2000 aparecia uma única fase chamada de Bulkmark Lookup.
Agora que já conhecemos as estatísticas de Banco de Dados, veremos no proximo post a importância de mantê-las atualizadas, até lá!
Landry.
Venho trabalhando com consultorias e treinamento em SQL Server desde a versão 7.0, e um dos problemas mais comuns são as consultas de baixo desempenho. Um motivo freqüente para o baixo desempenho de uma consulta é ter Estatísticas de Banco de Dados desatualizadas, pois o Otimizador de consultas acaba selecionando um plano de execução menos eficiente.
Este artigo foi dividido em duas partes, na primeira veremos como o Otimizador de consultas utiliza a Estatística de Banco de Dados para elaborar o plano de execução. Na segunda parte do artigo vou mostrar o prejuízo que uma Estatística desatualizada causa no desempenho de uma consulta.
As Estatísticas de Banco de Dados fornecem informações valiosas ao Otimizador, orientando a escolha do plano de execução de uma query. Ao criar um índice o SQL Server cria automaticamente uma estatística associada, contendo a distribuição dos valores da primeira coluna da chave do índice. Quando a propriedade de banco de dados AutoCreateStatistics está com TRUE (configuração padrão), o Otimizador cria a estatística sempre que detectar sua ausência durante a elaboração de um plano de execução.
Um dos componentes principais das Estatísticas de Banco de Dados é a distribuição de valores da coluna chave, onde é armazenada a quantidade de linhas contendo cada valor individual (se existem muitos valores individuais o SQL Server divide estes valores em intervalos e armazena o total de ocorrência dentro do intervalo).
Vamos observar o uso da estatística na prática criando a tabela abaixo e incluindo algumas linhas:
USE AdventureWorks
GO
IF OBJECT_ID('dbo.details', 'U') IS NOT NULL
DROP TABLE dbo.details
GO
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
OrderQty, ProductID,SpecialOfferID, UnitPrice,
UnitPriceDiscount, ModifiedDate
INTO dbo.details
FROM AdventureWorks.Sales.SalesOrderDetail
CREATE NONCLUSTERED INDEX idx_nc_col2
ON dbo.details(SalesOrderID)
GO
Foi criado um índice na tabela DETAILS utilizando como chave a coluna SALESORDERID, sendo criada uma estatística de banco de dados para esta coluna. Vamos agora trilhar o caminho que o Otimizador utilizou para elaborar o plano de execução da query:
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
Existem dois planos que o Otimizador poderá escolher:
1) Table Scan verificando o filtro WHERE linha a linha
2) Index Seek para resolver o filtro WHERE e depois Bookmark Lookup para recuperar as demais linhas da tabela que não se encontram no índice (veja que no SELECT temos *, isto é, todas as colunas).
Vamos contabilizar o custo de I/O (Imput/Output - leituras de página de dados e índice) necessário para resolver cada plano acima. Para determinar o Plano 1 (Table Scan), o SQL Server recorre ao catálogo para identificar quantas páginas de dados a tabela está ocupando, utilizando a query abaixo:
SELECT rows as QtdLinhas, data_pages Paginas8k
FROM sys.partitions p JOIN sys.allocation_units a
ON p.hobt_id = a.container_id
WHERE p.[object_id] = object_id('details')
and index_id in (0,1)
Resultado:
QtdLinhas: 121317
Paginas8k: 994
Desta maneira o Otimizador identificou que para resolver a query com o Plano 1 (Table Scan) serão necessárias 994 leituras de páginas de 8kb (páginas de dados – data pages).
Para contabilizar o I/O do Plano 2 (Index Seek + Bulkmark Lookup) o Otimizador identifica quantas linhas serão retornadas pelo filtro WHERE SalesOrderID > 75000, para isso ele utiliza a Estatística de Banco de Dados criada quando o índice na coluna SalesOrderID foi gerado. Para visualizar uma Estatística de Banco de Dados o SQL Server disponibiliza a instrução DBCC SHOWSTATISTICS.
DBCC SHOW_STATISTICS ('dbo.details','idx_nc_col2')
Executando esta instrução obtemos o resultado abaixo:
Em azul temos a data e hora que a estatística foi atualizada, informação importante como vocês verão mais a frente. Em vermelho a coluna Steps determina a quantidade de faixas de valores geradas no terceiro e último Grid (o terceiro Grid possui 143 linhas). Se você rolar este Grid até o final veremos os valores abaixo:
Vamos analisar o resultado utilizando as duas últimas linhas em vermelho:
RANGE_HI_KEY – representa o último valor de cada faixa, por exemplo: na linha 142 (em vermelho na figura acima) temos o valor 75122 representando a faixa que tem início no valor 74661 (o valor seguinte da linha 141 valor 74660) até o próprio 75122.
RANGE_ROWS – quantidade de linhas que possuem valores iguais ao da faixa, excluindo o último valor em RANGE_HI_KEY, por exemplo: na linha 142 (em vermelho na figura acima) temos a faixa de 74661 até 75122, porém 1077 representa a quantidade de linhas contendo os valores de 74661 até 75121. O Otimizador não tem como saber a exata distribuição das ocorrências dentro da faixa, porém as demais colunas fornecem uma boa idéia desta distribuição!
EQ_ROWS – quantidade de linhas que possuem o último valor da faixa, definido em RANGE_HI_KEY, por exemplo: na linha 142 (em vermelho na figura acima) podemos afirmar que existem duas linhas na tabela com o valor 75122.
DISTINCT_RANGE_ROWS – quantidade de valores dentro da faixa (SELECT DISTINCT dentro do intervalo da faixa), por exemplo: na linha 142 (em vermelho na figura acima) existem 461 valores dentro da faixa (75122 - 74661 = 461). Observamos que é um seqüencial sem pular valor!
AVG_RANGE_ROWS – igual a RANGE_ROWS / DISTINCT_RANGE_ROWS, por exemplo: 1077 / 461 = 2.336226.
Agora, utilizando a estatística da Figura 2, podemos ter uma idéia aproximada da quantidade de linhas retornadas pelo filtro WHERE SalesOrderID > 75000:
Linha 142 da figura acima: 75121 – 75000 = 121 valores dentro da faixa, em AVG_RANGE_ROWS temos 2.336226 multiplicando por 121 encontramos 282.683346. Somando as duas linhas em EQ_ROWS referente ao valor 75122, temos um total de 284.683346
Linha 143 da figura acima: 3 linhas com valor 75123.
O Otimizador concluiu então que seriam retornadas aproximadamente 287.683346 linhas com o filtro WHERE SalesOrderID > 75000, gerando 287 Bulkmark Lookups para as páginas de dados. Somando-se alguns poucos I/Os para navegar no índice (Index Seek), ficou bem a baixo do Plano 1 (Table Scan) com um total de 994 páginas.
Reparem que analisando a estatística o Otimizador errou por pouco a previsão da quantidade de linhas 287, onde na verdade a query retornou 301 linhas gerando 305 I/Os (301 páginas no Bulkmark Lookups e 4 páginas no Index Seek).
Obs.: No SQL Server 2005 o Bulkmark Lookup aparece no plano de execução composto por duas fases: Nested Loops e RID Lookup (se a tabela não tem índice Cluster) ou Cluster Index Seek (se a tabela tem índice cluster) até SP1, no SP2 aparece como Key Lookup. No SQL Server 2000 aparecia uma única fase chamada de Bulkmark Lookup.
Agora que já conhecemos as estatísticas de Banco de Dados, veremos no proximo post a importância de mantê-las atualizadas, até lá!
Landry.
Marcadores:
SQL Server 2005,
SQL Server 2008,
Tuning Query
terça-feira, 18 de março de 2008
SQL Server 2005: Executando relatórios customizados no Management Studio




Um engano comum dos meus alunos dos cursos Oficiais de SQL Server é achar que Service Pack serve apenas para resolver problemas. Além de resolver problemas um Service Pack pode acrescentar e retirar funcionalidades, tornando a leitura da documentação obrigatória antes da sua adoção em produção.
O Service Pack 2 do SQL Server 2005 é um bom exemplo, pois além de corrigir problemas incluiu duas funcionalidades novas: o novo tipo de dados VARDECIMAL e os Relatórios customizados no Management Studio.
Os relatórios de sistema disponíveis no Management Studio do SQL Server 2005 fizeram a alegria de muitos administradores de banco de dados, ficando fácil obter informações valiosas do servidor em um formato eficiente e elegante.
A partir do Service Pack 2 temos agora como acrescentar novos relatórios desenvolvidos com o Reporting Service, gerando um arquivo .RDL e registrando no Management Studio.Vamos observar primeiro a alteração de interface que ocorreu no Service Pack 2 na visualização dos relatórios. Até o SP1 os relatórios eram acessados selecionando um item no Object Explorer, por exemplo um banco de dados, e localizando na janela “Summary” o item “Report”, veja na Figura abaixo.
Já no Service Pack 2 o item “Report” passou da janela “Summary” para o menu de contexto no próprio Object Explorer, como podemos observar na Figura abaixo.
Outra novidade, em destaque na Figura acima (em vermelho), é a possibilidade de acrescentarmos novos relatórios desenvolvidos no Reporting Service. Basta clicar em “Custom Reports” e localizar o arquivo .RDL que o seu relatório será executado e irá aparecer na lista.
É possível customizar os relatórios com parâmetros de entrada de acordo com o objeto que você acionou no menu de contexto. Por exemplo, se você clicar com o botão direito em um banco de dados e executar um relatório, este poderá utilizar o nome do banco como parâmetro. Segue abaixo a relação dos parâmetros que podem ser utilizados:
É possível customizar os relatórios com parâmetros de entrada de acordo com o objeto que você acionou no menu de contexto. Por exemplo, se você clicar com o botão direito em um banco de dados e executar um relatório, este poderá utilizar o nome do banco como parâmetro. Segue abaixo a relação dos parâmetros que podem ser utilizados:
O relatório da Figura abaixo utilizou o parâmetro “DatabaseName” como filtro da query:
Select top(12) convert(char(10),Data,103) as Data, Tamanho_MB,EspacoUtil_MB
from msdb.Crescimento.OCUPACAODB
from msdb.Crescimento.OCUPACAODB
where NomeDB = @DatabaseName order by Data desc
Utilizando no relatório um parâmetro com o mesmo nome da tabela de parâmetros acima, o Management Studio executa o relatório passando o valor referente ao item selecionado no menu de contexto. Na Figura acima o relatório recebeu como parâmetro o nome do banco de dados selecionado no Object Explorer (DW_SQL2005).
Para retirar o relatório do menu de contexto basta excluir o arquivo .RDL e tentar executar o relatório, após o erro o Management Studio irá retirar o nome do relatório da lista automaticamente.
Até o próximo post.
Landry.
segunda-feira, 3 de março de 2008
Executando scripts em múltiplas instâncias no SQL Server 2008
Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.




O novo recurso de execução de scripts em múltiplas instâncias do SQL Server 2008 vem atender as necessidades de muitos administradores. Já passei por situações onde fui obrigado a abrir várias sessões em instâncias diferentes no SQL Server para executar o mesmo script. Em uma empresa que presto consultoria foi desenvolvido um pequeno programa em VBNET para executar scripts em várias instâncias do SQL Server, previamente cadastradas.
Para ser possível a execução de scripts em várias instâncias basta registrar um grupo de servidores na janela “Registered Servers”, e depois abrir uma janela de query a partir do grupo de servidores (figura abaixo) no Management Studio.
No meu ambiente de teste tenho uma instância de SQL Server 2008 chamada SRV2008 e uma instância de SQL Server 2000 chamada MIAMI. Executei a query abaixo que produziu a resposta que aparece na figura abaixo:
Select Name as DatabaseName,Dbid from sysdatabases
Repare na figura acima que foram acrescentadas ao resultado duas colunas que não foram solicitadas na query (“Server Name” e “Login”). No menu Tools\Options você poderá desabilitar a visualização destas colunas, assim como separar o resultado por instância, veja:
Na query abaixo ordenei o resultado pelo nome do servidor e ID do banco de dados:
select @@SERVERNAME ServerName,Name as DatabaseName,Dbid from sysdatabases order by 1,3
Neste último exemplo estou criando uma tabela Teste e incluindo 3 linhas utilizando uma nova opção do INSERT, onde em VALUES estou incluindo valores para 3 linhas.
create table Teste (col int)
insert Teste values (1), (2), (3)
Este script irá falhar na instância Miami, por ser SQL Server 2000, porém na instância SRV2008 foram incluídas as 3 linhas, veja:
Acredito que este recurso será muito utilizado no SQL Server 2008, não sendo mais necessário utilizar ferramentas de terceiros para executar scripts em múltiplas instâncias. Até o próximo post.
Landry.
domingo, 17 de fevereiro de 2008
Compactação do Backup no SQL Server 2008
Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.
O espaço ocupado no disco pelo backup sempre foi uma dor de cabeça para a maioria dos clientes que eu já prestei consultoria, já que até o SQL Server 2005 o backup era gerado sem compactação. Para reduzir a ocupação do disco, os administradores compactavam os arquivos de backup com ferramentas de terceiros. Existem vários problemas nesta solução de compactação após o backup:


Os arquivos ficaram com o tamanho abaixo:

Após a execução do script acima todo backup será compactado, não sendo necessário o uso da cláusula COMPRESSION.
O espaço ocupado no disco pelo backup sempre foi uma dor de cabeça para a maioria dos clientes que eu já prestei consultoria, já que até o SQL Server 2005 o backup era gerado sem compactação. Para reduzir a ocupação do disco, os administradores compactavam os arquivos de backup com ferramentas de terceiros. Existem vários problemas nesta solução de compactação após o backup:
- Necessidade de espaço em disco para fazer backup igual a área ocupada dentro do banco.
- Espaço adicional para executar a compactação e gerar o arquivo compactado, no final excluir o arquivo de backup original.
- Grande consumo de recursos do servidor para executar a compactação (sistema de discos e CPU).
- Espaço adicional para executar a compactação e gerar o arquivo compactado, no final excluir o arquivo de backup original.
- Grande consumo de recursos do servidor para executar a compactação (sistema de discos e CPU).
A necessidade de compactar o backup aparece também na solução de disponibilidade chamada Standby Server (Log Shipping). Esta solução mantém em sincronia uma cópia do banco de dados em outro servidor através de Backup e Restore do Log, para recuperar rapidamente o sistema na falha do servidor de produção. Para reduzir o impacto na rede, gerado pelas cópias freqüentes dos Backups do Log, é necessário compactar o backup antes da transmissão e descompactar no destino antes do Restore.
Com o novo recurso de compactação do backup no SQL Server 2008 ficou agora muito fácil resolver os problemas descritos acima. Vamos explorar esta funcionalidade e analisar a taxa de compactação, comparando com produtos de terceiros.
Primeiro vamos criar um banco de dados contendo uma tabela recheada de linhas, para gerar um arquivo de dados com 50MB. Execute o script abaixo:
Com o novo recurso de compactação do backup no SQL Server 2008 ficou agora muito fácil resolver os problemas descritos acima. Vamos explorar esta funcionalidade e analisar a taxa de compactação, comparando com produtos de terceiros.
Primeiro vamos criar um banco de dados contendo uma tabela recheada de linhas, para gerar um arquivo de dados com 50MB. Execute o script abaixo:
USE master
go
IF DB_ID('TesteBackup') is not null
DROP DATABASE TesteBackup
go
CREATE DATABASE TesteBackup
go
USE TesteBackup
go
CREATE TABLE tbBackup (Cod bigint,Descricao char(1000))
DECLARE @Cod bigint
SET @Cod = 1
WHILE @Cod <= 250000 BEGIN
go
IF DB_ID('TesteBackup') is not null
DROP DATABASE TesteBackup
go
CREATE DATABASE TesteBackup
go
USE TesteBackup
go
CREATE TABLE tbBackup (Cod bigint,Descricao char(1000))
DECLARE @Cod bigint
SET @Cod = 1
WHILE @Cod <= 250000 BEGIN
INSERT tbBackup SELECT @Cod, 'Descricao - ' + convert(varchar(10),@Cod)
SET @Cod = @Cod + 1
END
Agora faremos um Backup Full sem compactação (padrão) e depois com compactação utilizando a nova cláusula COMPRESSION do backup.
BACKUP DATABASE TesteBackup TO DISK = 'C:\Landry\TesteBackup_Full.bak'
Agora faremos um Backup Full sem compactação (padrão) e depois com compactação utilizando a nova cláusula COMPRESSION do backup.
BACKUP DATABASE TesteBackup TO DISK = 'C:\Landry\TesteBackup_Full.bak'
WITH INIT
BACKUP DATABASE TesteBackup TO DISK =
'C:\Landry\TesteBackup_Full_Compact.bak'
WITH INIT,COMPRESSION
Além de apresentar uma redução grande no tamanho do arquivo gerado pelo Backup, apresentou grande redução no tempo total de backup! Agora vamos comparar o resultado com dois compactadores Winzip e Winrar:
Reparem a excelente taxa de compactação que o SQL Server 2008 apresentou, comparando com os principais produtos de mercado! A grande vantagem é não ser necessário executar uma rotina após o backup, reduzindo bastante o tempo total somando Backup e Compactação nas versões anteriores.
Faremos a mesma análise com o Backup do Log, gerando atividade antes de executar o backup com e sem compactação.
-- Trunca o Log para eliminar a atividade anterior
BACKUP LOG TesteBackup TO DISK = 'C:\Landry\TesteBackup_Log.bak'
Faremos a mesma análise com o Backup do Log, gerando atividade antes de executar o backup com e sem compactação.
-- Trunca o Log para eliminar a atividade anterior
BACKUP LOG TesteBackup TO DISK = 'C:\Landry\TesteBackup_Log.bak'
WITH INIT
DECLARE @Cod bigint
SET @Cod = 1
WHILE @Cod <= 100000 BEGIN
DECLARE @Cod bigint
SET @Cod = 1
WHILE @Cod <= 100000 BEGIN
INSERT tbBackup SELECT @Cod, 'Descricao - ' + convert(varchar(10),@Cod)
SET @Cod = @Cod + 1
END
BACKUP LOG TesteBackup TO DISK = 'C:\Landry\TesteBackup_Log.bak'
WITH INIT
go
DECLARE @Cod bigint
SET @Cod = 1
WHILE @Cod <= 10000 BEGIN
INSERT tbBackup SELECT @Cod, 'Descricao - ' + convert(varchar(10),@Cod)
SET @Cod = @Cod + 1
END
BACKUP LOG TesteBackup TO DISK =
'C:\Landry\TesteBackup_Log_Compact.bak'
WITH INIT,COMPRESSION
Os arquivos ficaram com o tamanho abaixo:
A compactação do Backup do Log teve resultado similar, mantendo a excelente taxa de compactação e redução no tempo total.
O padrão da instância é não compactar o backup, sendo necessário executar o script abaixo para alterar:
USE master
go
EXEC sp_configure 'backup compression default', '1'
go
RECONFIGURE WITH OVERRIDE
go
O padrão da instância é não compactar o backup, sendo necessário executar o script abaixo para alterar:
USE master
go
EXEC sp_configure 'backup compression default', '1'
go
RECONFIGURE WITH OVERRIDE
go
Após a execução do script acima todo backup será compactado, não sendo necessário o uso da cláusula COMPRESSION.
Até o próximo post!
Landry.
Assinar:
Postagens (Atom)