• Microsoft SQL Server 2005
• Microsoft SQL Server 2008
Introdução
É fato que, atualmente, as colunas do tipo varchar, nvarchar, varbinary e XML podem se tornar verdadeiros pesadelos para desenvolvedores e administradores de banco de dados, se, durante a fase de arquitetura de seus respectivos bancos, não forem limitados seu tamanho.
Ademais, é sabido que somente as informações carregadas do disco para memória podem ser manipuladas pelo Sistema de Gerenciamento de Banco de Dados (SGBD). Assim, se pudéssemos de alguma maneira organizar estes tipos de dados em outra página de dados o SGBD poderia armazenar uma quantidade maior de informação em uma única página, ou seja, em uma página de 8k poderiam ser armazenados mais registros se estes tipos de campos fossem armazenados em uma extensão desta página.
Os resultados de tais suposições seria a possibilidade de armazenar mais dados, ocorrendo menos I/O de disco e menos processamento, eis que seria necessário carregar menos páginas para resgatar as informações mais relevantes de uma tabela.
Isso pode se tornar útil em cenários onde não é possível redefinir a estrutura de tabelas ou então um grande volume de I/O de disco e memória deve ser reduzido.
Neste artigo tentarei explicar o comando sp_tableoption e a opção large value types out of row e suas implicações.
Opções de Configuração de Tabela
O comando sp_tableoption é um comando que pode ser usado para determinar o comportamento dos registros de uma tabela. Especificamente para campos do tipo varchar, nvarchar, varbinary e XML.
Sintaxe:
sp_tableoption [ @TableNamePattern = ] 'table'
, [ @OptionName = ] 'option_name'
, [ @OptionValue = ] 'value'
, [ @OptionName = ] 'option_name'
, [ @OptionValue = ] 'value'
A opção large value types out of row, é responsável por determinar se os tipos de dados varchar, nvarchar, varbinary e XML devem ser ou não armazenados na mesma página de dados da tabela.
O valor padrão é ‘OFF’, desta maneira os dados são contidos na mesma página de dados junto com os demais campos da tabela.
Já o valor ‘ON’ determina que estes campos devem permanecer em uma extensão da página de dados.
Também, existe a opção de informarmos um valor de bytes entre 24 e 7000. Isso delimita a quantidade de bytes que será armazenado no registro.
O valor configurado para a opção pode ser verificado consultando a tabela sys.tables e observado o valor da coluna large_value_types_out_of_row. O valor 1 indica que está ‘ON’.
A Tabela Customers
Adicionei na tabela “Customers” do banco de dados “Northwind” o campo “Comments”.
Alter Table Customers Add Comments nvarchar(Max) null
go
Update Customers Set Comments = 'Microsoft SQL Server Microsoft SQL Server Microsoft SQL Server Microsoft Microsoft SQL Server Microsoft SQL Server Microsoft SQL Server Microsoft Microsoft SQL Server'
go
Update Customers Set Comments = 'Microsoft SQL Server Microsoft SQL Server Microsoft SQL Server Microsoft Microsoft SQL Server Microsoft SQL Server Microsoft SQL Server Microsoft Microsoft SQL Server'
Observe a nova definição da tabela:
Figura 1 – resultado do comando sp_help Customers.
Nesse momento é importante obtermos algumas estatísticas de I/O, referentes a algumas consultas nesta tabela:
Set nocount on
go
Set Statistics io on
go
select * From Customers
go
Set Statistics io on
go
select * From Customers
Table 'Customers'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
O dado mais importante para nós é exibido em “logical reads” e “lob logical reads”. O primeiro indica quantas páginas da memória foram lidas para formar o resultado. O Segundo informa quantas extensões da página de dados ou large object binary foram lidos para formar o resultado.
Para resgatar as informações dos clientes é necessário consultar nove páginas de dados. Isso significa que as informações do campo Comments estão definitivamente na mesma página de dados com o restante das informações, pois nenhum outro tipo de página foi lido.
Também, executando a seguinte consulta, isso fica mais claro.
set nocount on
go
Set Statistics io on
go
select
CustomerID
,CompanyName
,ContactName
,ContactTitle
,Address
,City
,Region
,PostalCode
,Country
,Phone
,Fax
From Customers
go
Set Statistics io on
go
select
CustomerID
,CompanyName
,ContactName
,ContactTitle
,Address
,City
,Region
,PostalCode
,Country
,Phone
,Fax
From Customers
Table 'Customers'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Podemos observar que as mesmas nove páginas foram lidas, mesmo removendo a coluna Comments da consulta.
Armazenando Grandes Valores Fora dos Registros
No cenário acima é preciso ler as nove páginas da tabela Customers, mesmo não utilizando a coluna Comments. Observe agora, quando utilizamos a opção de manter os grandes tipos de dados em outra página.
Alter Table Customers Add Comments nvarchar(Max) null
go
Exec sp_tableoption
@TableNamePattern = 'Customers'
,@OptionName = 'large value types out of row'
,@OptionValue = 'ON'
go
Update Customers Set Comments = 'Microsoft SQL Server Microsoft SQL Server Microsoft SQL Server Microsoft Microsoft SQL Server Microsoft SQL Server Microsoft SQL Server Microsoft Microsoft SQL Server'
go
Exec sp_tableoption
@TableNamePattern = 'Customers'
,@OptionName = 'large value types out of row'
,@OptionValue = 'ON'
go
Update Customers Set Comments = 'Microsoft SQL Server Microsoft SQL Server Microsoft SQL Server Microsoft Microsoft SQL Server Microsoft SQL Server Microsoft SQL Server Microsoft Microsoft SQL Server'
Executamos a consulta à tabela Customers novamente e obtemos o seguinte resultado:
set nocount on
go
Set Statistics io on
go
select * From Customers
go
Set Statistics io on
go
select * From Customers
Table 'Customers'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 192, lob physical reads 0, lob read-ahead reads 0.
Note que foram necessárias somente seis páginas para armazenar as informações da tabela Customer e outras 192 páginas para armazenar os dados da coluna Comments. As informações agora estão sendo armazenadas em outra estrutura, permitindo que mais dados sejam adicionados nas mesmas páginas de dados. Isso permite que dados mais relevantes e mais registros estejam na mesma página.
Se consultarmos todas as colunas com exceção da coluna Comments obtemos um valor de leitura de páginas de somente seis leituras lógicas. O que não seria possível se os dados da coluna Comments estivessem na mesma página de dados.
set nocount on
go
Set Statistics io on
go
select
CustomerID
,CompanyName
,ContactName
,ContactTitle
,Address
,City
,Region
,PostalCode
,Country
,Phone
,Fax
From Customers
go
Set Statistics io on
go
select
CustomerID
,CompanyName
,ContactName
,ContactTitle
,Address
,City
,Region
,PostalCode
,Country
,Phone
,Fax
From Customers
Table 'Customers'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
O resultado é uma economia de leitura de três páginas. Isso para uma tabela com pouquíssimos registros.
Links Relacionados
• MSDN sp_tableoption
• MSDN text in row option
Conclusão
A utilização e otimização de recursos avançados é uma necessidade no cenário mundial, principalmente diante de necessidades cada vez maiores do setor tecnológico. Todavia, tal situação deve ser avaliada com cuidado, principalmente levando em consideração o cenário em questão. Há muitos outros fatores que impactam no desempenho de I/O de páginas de dados, podemos citar além deste, a fragmentação, o fator de preenchimento e outros.
Verificando as informações do artigo, constatamos que podemos armazenar mais informações relevantes dentro de uma página de dados, quando armazenamos campos do tipo varchar, nvarchar, varbinary e XML fora da estrutura da tabela.
São comuns termos campos de “Observações”, “Comentários” e etc. Para estes casos, onde a maioria das consultas não os utiliza, podemos usar a opção large value types out of row para aperfeiçoar essas consultas.
Com isso, conseguimos diminuir I/O de disco e memória, e processamento, visto que podemos retornar mais informação consultando menos páginas de dados.
