Exato, mas com que frequencia voc\u00ea rodaria essa rotina? Quais as tabelas que precisam ter seus \u00edndices desfragmentados com uma maior frequ\u00eancia?<\/p>\n
Imagina que voc\u00ea possua muitas tabelas com milhares de registros e s\u00f3 tenha uma janela de 1 hora para desfragmentar todos os seus \u00edndices. Voce ter\u00e1 que dividir a desfragmenta\u00e7\u00e3o dos \u00edndices em v\u00e1rios dias al\u00e9m de tamb\u00e9m definir a frequ\u00eancia com que os \u00edndices ser\u00e3o reorganizados ou reconstru\u00eddos.<\/p>\n
Esse \u00e9 exatamente o ambiente que possuo, com muitas tabelas grandes e com apenas uma hora de janela para realizar esse procedimento.<\/p>\n
Digamos que eu possua 100 tabelas em uma determinada database, desfragmentar as 80 menores tabelas demoram 40 minutos, outras 10 tabelas juntas demoram 50 min, outras 5 tabelas juntas demoram 45 minutos, 3 tabelas demoram 40 minutos e 2 tabelas demoram 50 minutos.<\/p>\n
Nessa situa\u00e7\u00e3o, eu criaria 5 jobs de manuten\u00e7\u00e3o dos \u00edndices:<\/p>\n
- \n
- Job 1 – 80 tabelas<\/li>\n
- Job 2 – 10 tabelas<\/li>\n
- Job 3 – 5 tabelas<\/li>\n
- Job 4 – 3 Tabelas<\/li>\n
- Job 5 – 2 Tabelas<\/li>\n<\/ul>\n
Mas como eu saberia com que frequ\u00eancia rodar cada um desses jobs?<\/p>\n
Para definir essa frequ\u00eancia, eu utilizo uma rotina que armazena diariamente a fragmenta\u00e7\u00e3o de todos os \u00edndices de minhas principais databases. Como a query para verificar a fragmenta\u00e7\u00e3o dos \u00edndices \u00e9 um pouco custosa, eu utilizo um servidor D-1 que recebe um restore di\u00e1rio das bases de produ\u00e7\u00e3o para executar esse procedimento. Caso voc\u00ea n\u00e3o possua um servidor D-1 voc\u00ea deve agendar um job para executar esse procedimento em um hor\u00e1rio de pouco movimento.<\/p>\n
Inicialmente, devemos criar uma tabela que armazenar\u00e1 nosso hist\u00f3rico de fragmenta\u00e7\u00e3o.<\/p>\n
CREATE TABLE [dbo].[Hitorico_Fragmentacao_Indice](\r\n[Id_Hitorico_Fragmentacao_Indice] [int] IDENTITY(1,1) NOT NULL,\r\n[Dt_Referencia] [datetime] NULL,\r\n[Nm_Servidor] [varchar](50) NULL,\r\n[Nm_Database] [varchar](50) NULL,\r\n[Nm_Tabela] [varchar](50) NULL,\r\n[Nm_Indice] [varchar](70) NULL,\r\n[Avg_Fragmentation_In_Percent] [numeric](5, 2) NULL,\r\n[Page_Count] [int] NULL,\r\n[Fill_Factor] [tinyint] NULL)\r\n<\/pre>\n
Para popular essa tabela, basta rodar a query abaixo que\u00a0retorna a fragmenta\u00e7\u00e3o de todos os \u00edndices da database em que ela est\u00e1 sendo executada.<\/p>\n
INSERT INTO\u00a0Hitorico_Fragmentacao_Indice(Dt_Referencia,Nm_Servidor,Nm_Database,Nm_Tabela,Nm_Indice,Avg_Fragmentation_In_Percent,\r\nPage_Count,Fill_Factor)\r\nSELECT getdate(), @@servername,\u00a0 db_name(db_id()), object_name(B.Object_id), B.Name,\u00a0 avg_fragmentation_in_percent,page_Count,fill_factor\r\nFROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null) A\r\njoin sys.indexes B on a.object_id = B.Object_id and A.index_id = B.index_id\r\nORDER BY object_name(B.Object_id), B.index_id\r\n<\/pre>\n
OBS: Lembrando que essa \u00e9 a query demorada e deve ser executada em um hor\u00e1rio de pouco movimento do banco de dados.<\/strong><\/p>\n
Com essa importante informa\u00e7\u00e3o sendo armazenada no banco de dados, recebo no meu CheckList di\u00e1rio do banco de dados<\/a> um relat\u00f3rio\u00a0de todos os \u00edndices que est\u00e3o com uma fragmenta\u00e7\u00e3o maior que 5% e consigo identificar os \u00edndices que mais est\u00e3o se fragmentando para analisar um poss\u00edvel aumento na frequ\u00eancia do job de desfragmenta\u00e7\u00e3o.<\/p>\n
Segue abaixo a query que gera os dados para a planilha:<\/p>\n
declare @Dt_Referencia datetime\r\nset @Dt_Referencia = cast(floor(cast( getdate() as float)) as datetime)\r\n\r\nSELECT Nm_Servidor, Nm_Database, Nm_Tabela, Nm_Indice, Avg_Fragmentation_In_Percent, Page_Count, Fill_Factor\r\nFROM\u00a0Hitorico_Fragmentacao_Indice (nolock)\r\nWHERE\u00a0Avg_Fragmentation_In_Percent > 5\r\nAND page_count > 1000 \u00a0\u00a0-- Eliminar \u00edndices pequenos\r\nAND Dt_Referencia >= @Dt_Referencia\r\n<\/pre>\n
Essa query retorna o seguinte resultado:
\n![\"\"](\"https:\/\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2011\/02\/Fragmentacao_Indice-1.png\")
<\/p>\nQuando a fragmenta\u00e7\u00e3o de algum \u00edndice ultrapassa 15%, essa linha ja vem\u00a0vermelha e em negrito na minha planilha de CheckList.<\/p>\n
Devido a facilidade de implementa\u00e7\u00e3o dessa rotina (para quem possui uma janela de execu\u00e7\u00e3o), acredito que seria bem interessante guardar essa informa\u00e7\u00e3o e aumentar a baseline do seu banco de dados.<\/p>\n
Al\u00e9m disso, no meu pr\u00f3ximo post mostrarei como essa informa\u00e7\u00e3o me ajuda a definir o valor do FILL FACTOR de minhas tabelas.<\/p>\n
<\/p>\n
Gostou dessa Dica?<\/b><\/p>\n
Curta, comente, compartilhe\u2026<\/p>\n
Assine meu canal no Youtube<\/a> e curta minha p\u00e1gina no Facebook<\/a> para receber Dicas de Leituras, V\u00eddeos e Eventos sobre SQL Server.<\/p>\n