con respecto a la regeneracion de indices lo hice mediante este procedure
Código SQL:
Ver originalALTER PROCEDURE [dbo].[job_degragmentarDb]
AS
SET NOCOUNT ON --- INSTRUCCION QUE OMITE LAS SALIDAS DE LAS SENTENCIAS DENTRO DEL PROCEDURE PARA EVITAR USO DEL ANCHO DE BANDA AL ENVIAR INFORMACION A LA APLICACION CLIENTE
DECLARE @contador INT = 1
DECLARE @limiteContador INT
DECLARE @tblDefragmentar TABLE(
id INT IDENTITY(1,1) NOT NULL,
cmd VARCHAR(180)
)
--- INSERTAMOS COMANDOS A EJECUTAR EN FORMATO CADENA DE LOS INDICES CON FRAGMENTACION SUPERIOR AL 30% EN LA VARIABLE @tblDefragmentar LAS CUALES SERAN REGENERADAS
INSERT INTO @tblDefragmentar(cmd) (
SELECT
('ALTER INDEX ' + name + ' ON ' + OBJECT_NAME(tblFrag.object_id) + ' REBUILD WITH(ONLINE = ON)')
FROM sys.dm_db_index_physical_stats(db_id('SSLProduccion'),NULL,NULL,NULL,NULL)tblFrag
JOIN sys.indexes i ON tblFrag.object_id = i.object_id AND tblFrag.index_id = i.index_id
WHERE tblFrag.avg_fragmentation_in_percent > 30 AND NOT name IS NULL
)
--- INSERTAMOS COMANDOS A EJECUTAR EN FORMATO CADENA DE LOS INDICES CON FRAGMENTACION ENTRE EL 5 Y 30 % EN LA VARIABLE @tblDefragmentar LAS CUALES SERAN REORGANIZADAS
INSERT INTO @tblDefragmentar(cmd) (
SELECT
('ALTER INDEX ' + name + ' ON ' + OBJECT_NAME(tblFrag.object_id) + ' REORGANIZE')
FROM sys.dm_db_index_physical_stats(db_id('SSLProduccion'),NULL,NULL,NULL,NULL)tblFrag
JOIN sys.indexes i ON tblFrag.object_id = i.object_id AND tblFrag.index_id = i.index_id
WHERE tblFrag.avg_fragmentation_in_percent BETWEEN 5 AND 30 AND NOT name IS NULL
)
--- ASIGNAMOS EL LIMITE DEL CONTADOR A LA VARIABLE @limiteContador PARA RECORRER LAS FILAS DE LA TABLA @tblDefragmentar
SET @limiteContador = (SELECT COUNT(*) FROM @tblDefragmentar)
--- RECORREMOS LAS FILAS DE LA VARIABLE @tblDefragmentar
WHILE(@contador <= @limiteContador)
BEGIN
DECLARE @cmd VARCHAR(180)
--- ASIGNAMOS EL VALOR DEL COMANDO A SER EJECUTADO EN FORMATO CADENA A LA VARIABLE @cmd
SET @cmd = (SELECT cmd FROM @tblDefragmentar WHERE id = @contador)
--- EJECUTAMOS EL COMANDO PARA DESFRAGMENTAR LOS INDICES
EXEC(@cmd)
--- INCREMENTAMOS EL CONTADOR DE RECORRIDO DE FILAS DE LA TABLA
SET @contador = @contador + 1
END
pero el resultado es que aproximadamente la mitad de la cantidad total de los indices que tenian fragmentacion promedio superior al 5 %, se redujeron por debajo de los 5% el resto no lo hizo algunos se redujeron y quedaron con 30 hasta 50% en la fragmentacion promedio estos datos los saque de aqui
sys.dm_db_index_physical_stats del campo avg_fragmentation_in_percent
esto porque sucede? ya no disminuyen cada vez que vuelvo a regenerar o reorganizar los indices
estos son los indices que se quedaron por encima de 5%
lo campos estan en este orden:
database_id | object_id | index_id | partition_number | index_type_desc | alloc_unit_type_desc | index_depth | index_level | avg_fragmentation_in_percent | fragment_count | avg_fragment_size_in_page | page_count
Código result set:
Ver original7 37575172 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 11.5942028985507 15 4.6 69
7 37575172 12 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 6.66666666666667 3 5 15
7 37575172 13 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 6.66666666666667 3 5 15
7 125399666 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 33.3333333333333 2 1.5 3
7 208823906 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 6.66666666666667 2 7.5 15
7 208823906 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 20 2 2.5 5
7 208823906 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 25 2 2 4
7 213575799 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 11.1111111111111 2 4.5 9
7 213575799 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 25 2 2 4
7 213575799 9 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 33.3333333333333 2 1.5 3
7 229575856 16 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 5.57377049180328 51 5.98039215686275 305
7 243531951 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 12.5 2 4 8
7 243531951 12 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 11.1111111111111 2 4.5 9
7 295112242 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 20 2 2.5 5
7 301348238 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 50 2 1 2
7 352108395 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 12.8205128205128 18 4.33333333333333 78
7 352824419 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 7.69230769230769 19 5.47368421052632 104
7 352824419 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 24.4444444444444 15 3 45
7 389576426 22 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 11.8279569892473 21 4.42857142857143 93
7 389576426 23 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 10.2564102564103 25 4.68 117
7 389576426 27 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 5.40540540540541 26 5.69230769230769 148
7 389576426 28 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 9.01639344262295 25 4.88 122
aprovecho para comentarles que el aplicativo que estoy revisando tiene problemas de lentitud y bloqueos en las operaciones, revisando me eh dado cuenta que realizan muchas transacciones dentro de los procedures y se crean en ocaciones transacciones anidadas y dentro de ellas hay select quizas como hay un select y mientras ese select no termine de arrojar todo el resultado la tabla se mantiene bloqueada impidiendo asi que otro usuario pueda acceder a ella para este caso como podria detectar cuales son las queries o procedures que causan mas bloqueos y por tiempos largos? una posible solucion seria quitar los select que haya dentro de transacciones y ponerlas fuera, pero como hay algunos casos en la que un procedure llama a otro y cada procedure tiene su propia transaccion si quito el select fuera de la transaccion en uno de los procedures seguira dentro de la otra transaccion del procedure padre el que invoca como podria darle solucion a esto?
quitar todas las transacciones de todos los procedures ? seria muy tedioso hacerlo con un montos de procedures 1500 promedio