RESOURCE DATABASE

Galera, bom dia!

Hoje irei falar sobre a quinta base de dados de sistema do SQL SERVER, é isso mesmo! A partir do SQL 2005 a Microsoft introduziu uma quinta base de dados de sistema conhecida como Resource Database, seu nome real é mssqlsystemresource. Esta é uma base de dados que fica escondida aos usuários.

Porque a Microsoft introduziu esta base de dados?

A Resource Database é uma base read-only e oculta aos usuários como já falei acima, fisicamente ela contém todos objetos de sistema do SQL SERVER. Logicamente, todos objetos estão na sysschemas existente em cada base de dados. A Resource Database não contem dados de usuário ou metadados.
O ID desta base é sempre 32767, o principal propósito desta base é tornar os updates mais rápidos e fáceis. Desde que todos objetos residem na Resource Database, durante um update nós podemos apenas sobrescrever os seus arquivos com arquivos novos que isto já ira atualizar todos objetos de sistema existentes. Nas versões antigas do SQL todos objetos de sistema precisavam ser excluídos e recriados durante o upgrade e a instalação poderia vir a falhar caso um desses objetos apresentasse problemas. Por default, no SQL 2008 você irá encontrar os arquivos da Resource Database no diretório <drive>:\Program Files\Microsoft SQL Server\MSSQL1.<instance_name>\Binn\, já no SQL 2005 os arquivos se encontram em <drive>:\ Program Files\Microsoft SQL Server\MSSQL1\Data, são dois arquivos, um chamado mssqlsystemresource.mdf e o outro mssqlsystemresource.ldf.

É possível extrair um backup desta base de dados?

Por esta ser uma base escondida não é possível extrair seu backup via T-SQL, o que se pode fazer é parar os serviços do SQL SERVER, copiar seus arquivos para outro local e então iniciar os serviços do SQL novamente.

É possível verificar o que existe internamente na Resource Database?

Da mesma forma que não é possível a extração de um backup via T-SQL, um acesso ao conteúdo desta base via T-SQL também é inviável. Para checar o conteúdo da base podemos nos utilizar de um dos dois métodos abaixo:

1. Attach dos arquivos da base.
2. Rodando o SQL Server em single user mode.

METODO 1: Atachando os arquivos

1. Para o serviço do SQL SERVER.
2. Copie os arquivos da Resource Database para outro local.
3. Suba os serviços do SQL SERVER.
4. Realize o attach dos arquivos como se fosse uma base de usuário.

sp_attach_db 'ResourceDB_test','c:\TEST\mssqlsystemresource.mdf','c:\TEST\mssqlsystemresource.ldf'

Pronto! Você estará hábil a verificar seu conteúdo.

METODO 2: Rodando o SQL em single user mode.

1. Inicie o SQL em single user mode colocando o –m em seus parâmetros de inicialização no configuration manager.
2. Conecte-se ao SQL via cmd, você ainda não será capaz de ver a base porém poderá dar um “USE” na mesma que ira funcionar:
2.1 sqlcmd -S .\MSSQL2012A –E
2.2 USE mssqlsystemresource
2.3 go

No modo comum, só podemos obter duas informações da Resource Database, sua versão e data da última atualização que ela sofreu.

Capturando a versão:

SELECT SERVERPROPERTY('ResourceVersion')

Verificando última atualização:

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime')

Espero que tenham gostado do post até a próxima!

André CR

PAGE COMPRESSION SQL SERVER 2008



A Microsoft introduziu a compressão de tabelas e índices a partir do SQL SERVER 2008 para as edições enterprise e developer. A mesma  pode ser implementada em dois níveis: linha e página. Hoje irei comentar sobre a compressão de dados por página a qual utiliza-se de duas técnicas, a Prefix Compression e Dictionary Compression.

Vamos criar algumas tabelas e testar a compressão das páginas em seus variados aspectos:

Vamos criar uma tabela sem a opção de compressão e adicionar alguns dados a mesma:

/****** Object:  Table [dbo].[NoNCompressed Table]   
        Script Date: 06/12/2009 02:24:23 ******/
IF  EXISTS (SELECT * FROM sys.objects
        WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table]')
        AND type in (N'U'))
DROP TABLE [dbo].[NoNCompressed Table]
GO
CREATE TABLE [NoNCompressed Table]
(id int, FName char(100), LName char(100))

-- Add 10,000 rows

declare @n int
set @n=0
while @n<=10000
begin
insert into [NoNCompressed Table] values
(1,'Adam','Smith'),(2,'Maria','carter'),
(3,'Walter','zenegger'),(4,'Marianne','smithsonian')
set @n=@n+1
end
GO

Agora vamos verificar o tamanho da mesma com o comando abaixo:

EXEC sp_spaceused [NONCompressed Table]

Resultado:

name,rows,reserved,data,index_size,unused

NoNCompressed Table,40004,8456 KB,8424 KB,8 KB,24 KB

Vamos criar agora uma tabela usando a compressão e adicionar o mesmo volume de dados da tabela anterior:

/****** Object:  Table [dbo].[Compressed Table]   
        Script Date: 06/12/2009 02:24:57 ******/
IF  EXISTS (SELECT * FROM sys.objects
        WHERE object_id = OBJECT_ID(N'[dbo].[Compressed Table]')
        AND type in (N'U'))
DROP TABLE [dbo].[Compressed Table]
GO
CREATE TABLE [Compressed Table]
(id int, FName char(100), LName char(100)) with
(Data_compression = PAGE)
declare @n int
set @n=0

-- Add 10,000 rows

while @n<=10000
begin
insert into [Compressed Table] values
(1,'Adam','Smith'),(2,'Maria','carter'),
(3,'Walter','zenegger'),(4,'Marianne','smithsonian')
set @n=@n+1
end
GO

Vamos verificar seu tamanho com o comando abaixo:

EXEC sp_spaceused [Compressed Table]

Resultado:

name,rows,reserved,data,index_size,unused
Compressed Table,40004,        904 KB,896 KB,8 KB,0 KB

O resultado mostra que o tamanho do reservado e das colunas de dados são muito menores na tabela com compressão do que na tabela sem compressão. Se deseja estimar o quanto foi efetiva a compressão em sua tabela, utilize o comando abaixo:
 
Exec sp_estimate_data_compression_savings 'dbo','NoNCompressed Table',NULL,NULL,'PAGE'

Exec sp_estimate_data_compression_savings 'dbo','NoNCompressed Table',NULL,NULL,'ROW'

Quando esta procedure é executada,a mesma pega uma amostragem dos dados e carrega na tempdb, então, ela realiza a compressão no que foi carregado na tempdb e gera uma estimativa.

Você também pode comprimir uma tabela que já existe através do comando ALTER TABLE. Vamos simular isto criando outra tabela sem compressão. Vamos adicionar dados similares aos acima na mesma:

/****** Object:  Table [dbo].[NoNCompressed Table]    Script Date: 06/12/2009 02:24:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table2]') AND type in (N'U'))
DROP TABLE [dbo].[NoNCompressed Table2]
GO
CREATE TABLE [NoNCompressed Table2]
(id int, FName char(100), LName char(100))


declare @n int
set @n=0
while @n<=10000
begin
insert into [NoNCompressed Table2] values
(1,'Adam','Smith'),(2,'Maria','carter'),
(3,'Walter','zenegger'),(4,'Marianne','smithsonian')
set @n=@n+1
end
GO

 Vamos verificar seu tamanho com o comando abaixo:

EXEC sp_spaceused [NONCompressed Table2]

 Resultado:

name,rows,reserved,data,index_size,unused
NoNCompressed Table2,40004,           8456 KB,8424 KB,8 KB,24 KB

 Vamos adicionar a compressão com o comando ALTER TABLE conforme abaixo:

ALTER TABLE [NoNCompressed Table2]
REBUILD WITH (DATA_COMPRESSION = PAGE );

Agora vamos verificar seu tamanho pós-compressão:

EXEC sp_spaceused [NONCompressed Table2]

Resultado:

name,rows,reserved,data,index_size,unused

NoNCompressed Table2,40004,           592 KB,472 KB,8 KB,112 KB

 A compressão também pode ser utilizada em tabelas com índices clustered e nonclustered, vamos exemplificar:
 Crie uma tabela sem compressão com uma grande quantidade de dados através do script abaixo:

/****** Object:  Table [dbo].[NoNCompressed Table3]    Script Date: 06/13/2009 02:24:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table3]') AND type in (N'U'))
DROP TABLE [dbo].[NoNCompressed Table3]
GO
CREATE TABLE [NoNCompressed Table3]
(id int, FName char(100), LName char(100))


declare @n int
set @n=0
while @n<=10000
begin
insert into [NoNCompressed Table3] values
(1,'Adam','Smith'),(2,'Maria','carter'),(3,'Walter','zenegger')
set @n=@n+1
end
GO

 Vamos verificar seu tamanho com o comando abaixo:

EXEC sp_spaceused [NONCompressed Table3]

 Resultado:

name,rows,reserved,data,index_size,unused

NoNCompressed Table3,30003,           6472 KB,6408 KB,8 KB,56 KB

Adicione um índice clustered na mesma através do comando abaixo:

create clustered index [NoNCompressed Table3_Cl_Idx] on
[NoNCompressed Table3](ID)

Vamos verificar seu tamanho com o comando abaixo:

EXEC sp_spaceused [NONCompressed Table3]

Resultado:

name,rows,reserved,data,index_size,unused

NoNCompressed Table3,30003,           6784 KB,6672 KB,64 KB48 KB

Adicione um índice Nonclustered na tabela através do comando abaixo:

create Nonclustered index [NoNCompressed Table3_NonCl_Idx] on
[NoNCompressed Table3](Fname) 

Vamos verificar agora seu tamanho com o comando abaixo:

EXEC sp_spaceused [NONCompressed Table3]

Resultado:

name,rows,reserved,data,index_size,unused
NoNCompressed Table3,30003,10656 KB,  6672 KB,3752 KB,232 KB

Vamos adicionar a compressão na tabela através dos comandos abaixo:

ALTER TABLE [NoNCompressed Table3]
REBUILD WITH (DATA_COMPRESSION = PAGE );

ALTER INDEX [NoNCompressed Table3_Cl_Idx] on [NoNCompressed Table3]
REBUILD WITH (DATA_COMPRESSION = PAGE );


ALTER INDEX [NoNCompressed Table3_NonCl_Idx] on [NoNCompressed Table3]
REBUILD WITH (DATA_COMPRESSION = PAGE );

Vamos verificar agora seu tamanho pós-compressão com o comando abaixo:

EXEC sp_spaceused [NONCompressed Table3]

Resultado:

name,rows,reserved,data,index_size,unused
NoNCompressed Table3,30003,           808 KB,336 KB,320 KB,152 KB

Com os resultados obtidos é possível verificar a eficácia da compressão nas tabelas! Para a implementação desta nova funcionalidade existem algumas restrições, no entanto não irei entrar detalhes neste post, posteriormente irei comentar sobre as mesmas e irei falar também da outra forma de compressão, a compressão de linhas (“row compression”).

 Espero que tenham gostado do post!

 Um abraço!

DMV'S/DMF's

Eae galera tudo certo? Espero que sim =)

  Bom, hoje vou abordar um assunto muito importante na vida de um DBA de SQL SERVER! A utilização das Dynamic Management Views (DMV's) e das Dynamic Management Functions (DMF's). Acredito que a grande maioria aqui ja trabalhou com a versão 2000 do SQL SERVER e devem saber o quanto era dificil monitorar certas coisas relacionadas a performance, um exemplo? Simples! Como você capturava um plano de execução de um procedure no cache? Pois é, você não capturava pois não tinha uma forma de se fazer isso! Isso mudou com o lançamento do SQL SERVER 2005, foram introduzidas as DMV's e as DMF's!

 As DMV's e as DMF's podem lhe fornecer uma informação mais detalhada do que está ocorrendo internamente no SQL SERVER, usadas em conjunto elas se tornam ferramentas extremamente poderosas para investigar a causa de algum problema, monitorar o ambiente dentre outras utilidades.

 Vou listas abaixo as DMV's e DMF's mais utilizadas pelos DBA's hoje em dia e na sequência darei um exemplo de como capturar planos de execução das procedures do cache:


  • sys.dm_exec_cached_plans - Retorna planos armazenados no cache do SQL que permitem a execução mais rápida das queries, no caso o plano que esta sendo utilizado
  • sys.dm_exec_query_stats - Planos armazenados no cache do SQL, planos "antigos" e atuais
  • sys.dm_exec_sessions - Sessões abertas com o SQL Server.
  • sys.dm_exec_connections - Conexões com o SQL Server.
  • sys.dm_db_index_usage_stats - Estatisticas de seeks, scans, lookups por indice.
  • sys.dm_io_virtual_file_stats - Estatisticas de IO para os arquivos das bases de dados.
  • sys.dm_tran_active_transactions - Estado de uma transação no SQL Server.
  • sys.dm_exec_sql_text - Retorna o código T-SQL.
  • sys.dm_exec_query_plan - Retorna o plano de execução.
  • sys.dm_os_wait_stats - Retorna informação sobre os recursos em espera no SQL Server.
  • sys.dm_os_performance_counters - Retorna contadores do performance monitor relacionados ao SQL Server.
Exemplo para capturar planos do cache:

   select * from sys.dm_exec_cached_plans cp
   cross apply sys.dm_exec_query_plan (plan_handle) qp
   cross apply sys.dm_exec_sql_text (plan_handle) st


 Espero que tenham gostado do post! Obviamente que não é só isso que tenho a falar das DMV's e DMF's, então, fiquem atentos nas atualizações do blog =)

 Abraço! 

Parametrization Forced


Galera, bom dia! Hoje nós iremos falar de um recurso bem interessante que surgiu no SQL 2005, o Parametrization Forced.


 Um dos beneficios de utilizar uma stored procedure para executar uma instrução no sql é que uma vez compilada e executada pela primeira vez, o plano de execução é armazenado no cache pelo SQL SERVER. Logo, da próxima vez que a mesma procedure executar (assumindo que ela possui os mesmos parâmetros), o SQL não tem que recompilar a mesma novamente, ele ira reutilizar o plano de execução armazenado em cache anteriormente. Esta reutilização do plano de execução pode reduzir a solicitação de recursos da parte do SQL Server e proporcionar um ganho na performance.

Porém como todos nós sabemos, nem todas aplicações baseadas em SQL SERVER utilizam as procedures para se comunicarem com o mesmo. Muitas vezes instruções SQL são mandadas da aplicação para o SQL de forma estática ou dinâmica. Por default o SQL tem a habilidade de fazer algo chamado de SIMPLE PARAMETERIZATION.

      A SIMPLE PARAMETERIZATION nada mais é do que o SQL analisar uma instrução estática ou dinâmica enviada da aplicação a ele e tentar localizar valores que podem ser tratados como parâmetros, isto ira parametrizar  a instrução SQL, o que permitira que seu plano de execução seja reutilizado futuramente assim como as procedures.

Vamos exemplificar, suponhamos que o SQL receba a seguinte query de SELECT:



SELECT NAME, ZIP FROM TESTE WHERE ZIP = ‘65742’



Quando o SQL olha para este simples SELECT, ele é capaz de reconhecer o ‘65742’ como um parâmetro, e provavelmente outra query similar ira ser executada posteriormente, porem com um valor diferente no parâmetro. Por conta disso, quando o SQL esta query ele ira parametrizar a mesma e armazenar seu plano de execução em cachê para que uma query similar futura o utilize.

Por exemplo, as queries a seguir irão reutilizar o mesmo plano de execução da primeira, salvando os recursos e aumentando a performance.



SELECT NAME, ZIP FROM TESTE WHERE ZIP = ‘10005’



SELECT NAME, ZIP FROM TESTE WHERE ZIP = ‘99686’



Apesar de tudo, por default, apenas as queries relativamente simples são parametrizadas, ou seja, se sua aplicação utiliza queries mais complexas daí surge a necessidade de talvez habilitar a nova opção disponível no SQL 2005, chamada de FORCED PARAMETERIZATION. Quando esta opção é ativada, ela diz ao SQL para forçar a parametrização de todas instruções de SELECT, INSERT, UPDATE E DELETE enviadas a ele. Ainda assim algumas queries ficam de fora, mais são poucas as exceções.

Com a FORCED PARAMETERIZATION ativa, o SQL ira executar poucas compilações pois agora ele esta apto a reutilizar mais planos de execução do que antes, reduzindo a utilização dos recursos e aumentando a performance.

Contudo, algumas considerações importantes devem ser feitas.

- O SQL ira ter um trabalho a mais para as novas parametrizações.

- Algumas queries com parâmetros que sofrem muitas mudanças podem acabar utilizando planos impróprios, reduzindo a performance.

 - Devido as constantes em uma query que são alteradas para parâmetros, nem sempre o query optimizer pode optar pelo melhor plano, reduzindo a performance.

- O query optimizer pode não escolher o plano ideal para queries em tabelas particionadas ou views.

- O query optimizer pode não surtir efeito em views indexadas ou em colunas computadas com indices.

A Microsoft recomenda que a force parameterization é melhor utilizada em situações limitadas, como em aplicações com grandes volumes de queries concorrentes que utilizam instruções estáticas ou dinâmicas, sem muitas chamadas via procedures. Caso sua aplicação não se encaixe nestes quesitos a force parameterization provavelmente ira piorar a performance do seu ambiente ao invés de melhorar.

A Forced Parameterization é ligada e desligada a nível de banco de dados, ou seja, ou esta ativa ou não. Contudo, se você quiser, você pode utilizar o SQL 2005 PLAN GUIDES para ligar a force parameterization em um grupo de queries (assumindo que este esteja fora da base de dados) ou dizer ao SQL para não usar o force parameterization em certas queries (assumindo que este esta ativo na base de dados). Porem fazendo o proposto acima, você aumentara a carga de trabalho no SQL.



Para ativar a force parameterization use o seguinte comando (exemplo):



ALTER DATABASE DB_NETREPORT SET PARAMETERIZATION FORCED





Para desativar:



ALTER DATABASE DB_NETREPORT SET PARAMETERIZATION SIMPLE



Para verificar se a force parameterization esta ativa ou não faça o SELECT abaixo:



SELECT NAME, IS_PARAMETERIZATION_FORCED FROM SYS.DATABASES



Analise com muito cuidado seu ambiente pois a force parameterization pode melhorar o ambiente como pode também piorar o mesmo do ponto de vista da performance. 

Espero que tenham gostado do post e até a próxima!