PROJECT SERVER. OPTIMIZACION DE LOS INDICES DE LAS BASES DE DATOS
En este post queríamos agradecer a los compañeros de SQL Raquel Vicente de la Rosa, Jorge Pérez Campos y Marcos Celada por sus consejos y ayudas varias en las múltiples ocasiones que requerimos de su conocimiento para tratar asuntos “espinosos” de SQL; resulta una gran satisfacción contar con colegas tan preparados y dispuestos a ayudarnos como estos tres mosqueteros de SQL Server; si queréis estar al día en esta tecnología, os recomendamos le echéis un vistazo a su blog:
https://blogs.msdn.com/b/esecuelesinfronteras/
Gracias a ellos, nos pusieron en la pista de este artículo realmente útil, donde se habla de los índices, y como tenerlos “bajo control”:
https://msdn.microsoft.com/en-us/library/ms188917.aspx
En el apéndice D tenemos un script a tener en cuenta; su utilidad es muy sencilla: verifica el índice de fragmentación de los índices en la base de datos que le indiquemos, y si superan unos determinados umbrales, los reorganiza o reconstruye directamente (nos ha gustado tanto, que nos permitimos copiarlo a continuación):
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Podemos crear un trabajo de SQL que ejecute este script a diario, y sólo hará algo, si se superan los umbrales preestablecidos, por lo tanto, podemos estar tranquilos, que de esta manera tendremos los índices de nuestras bases de datos en un estado adecuado.
Esperamos os resulte útil, un saludo
Jorge Puig