Entendendo os PAGE SPLITs e o que seria o FILL FACTOR.
Olá pessoal tudo bem com vocês? Espero que sim!
Estava bem sumido do meu blog (https://sqlmagu.blogspot.com.br/) mas vou tentar voltar as poucos (devagar rsrs) e agora vou postar as coisas novas por aqui.
Como DBA SQL Server você provavelmente já sabe que o SQL Server aloca páginas de dados e índices de 8 KB e talvez, indo mais fundo no assunto, provavelmente já tenha ouvido falar sobre a operação PAGE SPLIT e o termo FILL FACTOR certo? Bom, no artigo de hoje vou explicar cada um deles e sugerir algumas coisas que podem ser úteis na administração da sua instância com SQL Server.
O que seria um PAGE SPLIT?
Quando o SQL Server vai fazer uma alocação em uma página de dados proveniente de um INSERT ou de um UPDATE em um índice CLUSTERED ou NON-CLUSTERED (nível folha e intermediário) e não existe mais espaço na página para manipular a linha afetada, ele realiza o que chamamos de PAGE SPLIT, ou seja, cria uma nova página de dados e movimenta mais ou menos a metade das linhas para ela. Em uma tabela sem índices (HEAP) não existem PAGE SPLITS, pois não existe uma ordenação logica dos dados, as alocações são baseadas nos FORWARDED RECORDS.
Certo, mas onde entra o FILL FACTOR nesta história toda?
Nas páginas de um índice CLUSTERED ou NON CLUSTERED, o FILL FACTOR de um índice define o quanto da página de dados será preenchido, onde ele é considerado nas seguintes situações:
- Na criação do índice ou em seu REBUILD.
- No nível folha e nos níveis intermediários quando o PAD_INDEX é especificado para o índice. Onde o PAD_INDEX é sempre igual ao FILL FACTOR.
Se você tem em determinado índice um FILL FACTOR em 70% por exemplo, significa que 30% do tamanho total da página será deixado sem preenchimento, este espaço vazio fica entre as linhas do índice e não no final dele.
O bom PAGE SPLIT e o PAGE SPLIT ruim
Bom, agora que você já tem uma noção do que é PAGE SPLIT e do que é FILL FACTOR, podemos falar sobre a diferença entre um bom PAGE SPLIT e um PAGE SPLIT ruim.
O bom PAGE SPLIT é aquele que ocorre sempre ao final do índice, imagine uma inserção em um campo IDENTITY, onde os dados são sempre inseridos em ordem ao final do índice, quando não existe mais espaço na última página, uma nova página vazia será alocada e os dados continuarão sendo inseridos. Agora pense por exemplo em uma inserção no meio do índice, ou mesmo em um UPDATE que cresce a linha de modo que ela deixe de caber na página, o SQL Server precisa, portanto, alocar uma nova página e movimentar parte dos dados da página original para ela, isso acaba gerando mais IO, fragmentação e aumento do transaction log, este seria o PAGE SPLIT ruim.
Certo...o que eu consigo fazer para nunca mais ter PAGE SPLITS ruins? A reposta é: nada, massss você pode tentar algumas coisas para pelo menos diminuir a ocorrência deles! Como? Lembra do FILL FACTOR?
Ajustando o FILL FACTOR
Não existe uma formula mágica de calcular qual é o FILL FACTOR ideal para cada índice que você possui, o melhor a fazer é acompanhar a carga de trabalho, identificar os índices com maior ocorrência de PAGE SPLITS e ir ajustando aos poucos o seu valor. De que forma você pode fazer isso? Se você está em um SQL Server anterior ao 2012, você pode começar olhando as colunas leaf_allocation_count e nonleaf_allocation_count da DMV sys.dm_db_index_operational_stats, dê uma olhada na query mencionada no site abaixo:
Com ela você pode ter uma ideia dos PAGE SPLITS considerando que ela captura a informação somente se o metadados dos objetos estiverem em cache, caso o serviço do SQL Server seja reiniciado ou a base alvo passe por um detach/attach por exemplo, a DMV não irá retornar à informação esperada, outro ponto aqui é entender que todas operações de PAGE SPLITS serão consideradas, sejam eles bons ou ruins.
Existe uma outra forma de saber como estão os PAGE SPLITS dos índices ao longo do tempo usando EXTENDED EVENTS e a vantagem se comparado a DMV anterior é que podemos filtrar a informação somente nos PAGE SPLITS ruins, basicamente precisamos criar uma sessão de xevents com o evento sql_server.transaction_log e filtrar o campo operation com o valor = 11, que significa LOP_DELETE_SPLIT. Esta operação indica que o SQL Server está movendo dados de uma página a outra, ou seja, PAGE SPLITS ruins.
Existe um artigo excelente do Kehayias que te ajuda a montar esta sessão de eventos estendidos:
A query abaixo é uma adaptação do código do Kehayias para que a informação possa ser lida do arquivo ao invés do histograma:
SELECT o.name AS table_name,i.name AS index_name,SplitCount,i.fill_factorFROM (SELECTDB_NAME(TAB.database_id) AS DatabaseName,COUNT(TAB.[alloc_unit_id]) AS SplitCount,alloc_unit_idFROM(SELECTDATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),xevents.event_data.value('(event/@timestamp)[1]','datetime2')) AS [event time] ,xevents.event_data.value('(event/data[@name="database_id"]/value)[1]', 'int')AS [database_id],xevents.event_data.value('(event/data[@name="database_name"]/value)[1]', 'varchar(max)')AS [database name],xevents.event_data.value('(event/data[@name="alloc_unit_id"]/value)[1]', 'BIGINT')AS [alloc_unit_id]FROM sys.fn_xe_file_target_read_file('C:\temp\PageSplitTracking*.xel', -- ALTERE AQUI COM AS INFORMAÇÕES DO SEU ARQUIVO'C:\temp\PageSplitTracking*.xem', -- ALTERE AQUI COM AS INFORMAÇÕES DO SEU ARQUIVONULL, NULL)CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) AS xevents) TABGROUP BY TAB.alloc_unit_id, TAB.database_id) AS tabJOIN sys.allocation_units AS auON TAB.alloc_unit_id = au.allocation_unit_idJOIN sys.partitions AS pON au.container_id = p.partition_idJOIN sys.indexes AS iON p.OBJECT_ID = i.OBJECT_IDAND p.index_id = i.index_idJOIN sys.objects AS oON p.OBJECT_ID = o.OBJECT_ID
Pessoal, espero que este artigo seja útil para vocês no dia a dia quando o assunto for diminuir a ocorrência de PAGE SPLITS ruins, qualquer dúvida estou à disposição!
Um abraço!
André Rodrigues
Referências: https://sqlblog.com/blogs/kalen_delaney/archive/2009/11/11/fragmentation-and-forwarded-records-in-a-heap.aspx https://blogs.msdn.microsoft.com/craigfr/2009/07/29/correction-to-my-prior-post-on-sys-dm_db_index_operational_stats/ https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-operational-stats-transact-sql https://technet.microsoft.com/en-us/library/bb630339(v=sql.110).aspx https://www.sqlskills.com/blogs/paul/how-expensive-are-page-splits-in-terms-of-transaction-log/