Analisando o trace flag 1118



Conforme havia prometido no ultimo post, vamos analisar o padrão de alocação das páginas no SQL Server, antes e depois do trace flag 1118 ser habilitado. E para fazer isso, nada melhor que um script comentado…


 


*********************************************************


 


/*


          0) Cria o banco de dados Inside


*/


USE MASTER


GO


 


IF EXISTS (SELECT * FROM SYSDATABASES WHERE [Name] = ‘Inside’)


BEGIN


          DROP DATABASE Inside


END


GO


 


CREATE DATABASE Inside


ON


(


Name = ‘Inside_data’,


FileName = ‘C:\Program Files\Microsoft SQL Server\MSSQL$INST2000_01\Data\Inside_data.mdf’,


Size = 10MB,


FileGrowth = 5MB,


MaxSize = UNLIMITED


)


LOG ON


(


Name = ‘Inside_log’,


FileName = ‘C:\Program Files\Microsoft SQL Server\MSSQL$INST2000_01\Data\Inside_log.mdf’,


Size = 5MB,


FileGrowth = 3MB,


MaxSize = UNLIMITED


)


GO


 


USE TempDB


go


 


/*


          1 – PRIMEIRA ETAPA


          ==============================================================================


 


          Mostra que inicalmente o SQL Server aloca 1 página em extents mistos até que 8 páginas sejam utilizadas. A partir daí o SQL Server passa a alocar um extent uniforme (8 páginas) quando necessário.


*/


 


/*


          1.1) Cria nova tabela para verificar modelo alocação


*/


IF EXISTS (SELECT * FROM SysObjects WHERE [Name] = ‘PageAlloc_01’ and Xtype = ‘U’)


BEGIN


          DROP TABLE PageAlloc_01


END


GO


 


CREATE Table PageAlloc_01


(


          Data CHAR(1000) NOT NULL


)


GO


 


INSERT INTO PageAlloc_01 VALUES (’01’)


 


DBCC EXTENTINFO(TempDB, ‘PageAlloc_01’, 0)


GO


 


INSERT INTO PageAlloc_01 VALUES (’02’)


INSERT INTO PageAlloc_01 VALUES (’03’)


INSERT INTO PageAlloc_01 VALUES (’04’)


INSERT INTO PageAlloc_01 VALUES (’05’)


INSERT INTO PageAlloc_01 VALUES (’06’)


INSERT INTO PageAlloc_01 VALUES (’07’)


 


DBCC EXTENTINFO(TempDB, ‘PageAlloc_01’, 0)


GO


 


INSERT INTO PageAlloc_01 VALUES (’08’)


 


/*


          1.2) Uma nova página foi alocada, também em um extent misto.


*/


DBCC EXTENTINFO(TempDB, ‘PageAlloc_01’, 0)


 


INSERT INTO PageAlloc_01 VALUES (’09’)


INSERT INTO PageAlloc_01 VALUES (’10’)


INSERT INTO PageAlloc_01 VALUES (’11’)


INSERT INTO PageAlloc_01 VALUES (’12’)


INSERT INTO PageAlloc_01 VALUES (’13’)


INSERT INTO PageAlloc_01 VALUES (’14’)


 


DBCC EXTENTINFO(TempDB, ‘PageAlloc_01’, 0)


GO


 


INSERT INTO PageAlloc_01 VALUES (’15’)


 


/*


          1.3) Uma nova alocação de página se repete…


*/


DBCC EXTENTINFO(TempDB, ‘PageAlloc_01’, 0)


GO


 


/*


          1.4) Preenche as outras páginas até que o objeto tenha alocado 8 páginas distribuídas em extents mistos


*/


 


DECLARE @Contador INT


SET @Contador = 16


 


WHILE @Contador < 57


BEGIN


          INSERT INTO PageAlloc_01 VALUES (@Contador) 


          SET @Contador = @Contador + 1


END


 


DBCC EXTENTINFO(TempDB, ‘PageAlloc_01’, 0)


GO


 


/*


          1.5) Quando o registo 57 for inserido, o SQL Server passará a alocar extents uniformes, como pode


          ser visto na saída do ExtentInfo.


 


          Extent size = 8


*/


INSERT INTO PageAlloc_01 VALUES (57)


 


DBCC EXTENTINFO(TempDB, ‘PageAlloc_01’, 0)


GO


 


/*


          2 – SEGUNDA ETAPA


          ==============================================================================


 


          Habilitando o trace flag 1118 o SQL Server altera o modelo de alocação, passando a reservar 1 extent uniforme já no primeiro acesso. Isso faz com que o acesso nas páginas PFS e SGAM diminua, já que o SQL Server não necessita consultar diversas vezes essas informações para alocar as 8 páginas. Por outro lado, um objeto de 1 KB vai “usar” 64 KB, provavelmente gerando um aumento do tamanho dos arquivos da tempdb.


*/


 


/*


          2.1) Exibe os trace flags correntes e depois habilita o trace flag 1118


*/


DBCC TRACESTATUS(-1)


DBCC TRACEON(1118)


 


/*


          2.2) Cria nova tabela para verificar modelo alocação


*/


IF EXISTS (SELECT * FROM SysObjects WHERE [Name] = ‘PageAlloc_02’ and Xtype = ‘U’)


BEGIN


          DROP TABLE PageAlloc_02


END


GO


 


CREATE Table PageAlloc_02


(


          Data CHAR(1000) NOT NULL


)


GO


 


INSERT INTO PageAlloc_02 VALUES (’01’)


 


/*


          2.3) Podemos ver na saída do ExtentInfo, que o SQL Server já alocou 8 páginas para este objeto.


 


          Extent size = 8


*/


DBCC EXTENTINFO(TempDB, ‘PageAlloc_02’, 0)


GO


 


/*


          2.4) Repete a inserção de 55 registros para preencher o espaço das 8 páginas inicialmente alocadas.


*/


DECLARE @Contador INT


SET @Contador = 2


 


WHILE @Contador < 57


BEGIN


          INSERT INTO PageAlloc_02 VALUES (@Contador) 


          SET @Contador = @Contador + 1


END


 


DBCC EXTENTINFO(TempDB, ‘PageAlloc_02’, 0)


GO


 


/*


          2.5) Quando o registro 57 for inserido o SQL Server alocará um novo extent uniforme, seguindo a maneira padrão de alocação de páginas para objetos com mais de 64 KB.


 


          Extent size = 8


*/


INSERT INTO PageAlloc_02 VALUES (57)


 


DBCC EXTENTINFO(TempDB, ‘PageAlloc_02’, 0)


GO


 


/*


          2.6) Com isso demonstramos o efeito do trace flag 1118 no SQL Server.


*/


 


 


/*


          3 – TERCEIRA ETAPA


          ==============================================================================


 


          Uma dúvida comum que temos com relação ao trace flag 1118 é se ele somente se aplica ao tempDB ou a qualquer banco de dados que exista no SQL Server. Para descobrirmos isso, vamos apenas reexecutar uma inserção em um banco de dados de usuário.


 


*/


 


/*


          3.1) Cria nova tabela para verificar modelo alocação em um banco de dados de usuário.


*/


USE Inside


GO


 


IF EXISTS (SELECT * FROM SysObjects WHERE [Name] = ‘PageAlloc_03’ and Xtype = ‘U’)


BEGIN


          DROP TABLE PageAlloc_03


END


GO


 


CREATE Table PageAlloc_03


(


          Data CHAR(1000) NOT NULL


)


GO


 


INSERT INTO PageAlloc_03 VALUES (’01’)


 


/*


          3.2) Podemos ver na saída do ExtentInfo, que o SQL Server já alocou 8 páginas para este objeto.


 


          Extent size = 8


*/


DBCC EXTENTINFO(Inside, ‘PageAlloc_03’, 0)


GO


 


/*


          ** Admito que inicialmente eu achava que este comportamento somente se aplicaria ao banco de dados TempDB, apesar do artigo não deixar claro o escopo do trace flag. Nosso exemplo demonstra claramente que o trace flag se aplica a TODO O SERVIDOR SQL SERVER.


 


          Nada como um exemplo para entendermos melhor como o SQL Server funciona…


*/


 


 


Durante a criação deste script, um pequeno detalhe me chamou a atenção… Uma página no SQL Server possui 8KB (8.192 bytes), sendo que 8.096 bytes são reservados para os registros e seus offsets.


As tabelas PageAllocs possuem somente um campo de 1000 bytes, então seria de se esperar que, mesmo com as informações de controle de cada registro, uma página pudesse manter até 8 registros, o que não foi verdade.


Fica a pergunta: como o que o SQL Server está gastando internamente este espaço?


 


Quem sabe um dia eu escrevo sobre isso também. Até lá podem consultar o Inside SQL Server 2000. 😉


Até uma próxima.


 


[]s


Luti

TraceFlag_1118.sql

Comments (1)

  1. laertejunior says:

    Luti, nao entendi bem no que o trace flag 1118 pode ajudar (ou piorar). E em que situações seria interessante a verificação do uso dele.

    Obrigado !!!!

Skip to main content