Por: Brady Upton | Actualizado: 2016-06-22 | Comentarios (5) | Relacionado: Más > Fragmentación y Mantenimiento de índices
Problema
Todos hemos oído hablar de la fragmentación de bases de datos/índices (y si no lo ha hecho, continúe leyendo), pero ¿qué es? ¿Es un problema? ¿Cómo sé si reside en mi base de datos?¿Cómo lo arreglo? Estas preguntas podrían ser un consejo en sí mismo, pero trataré de darte una idea de cada uno en este consejo.
Solución
Sin entrar en muchos detalles, SQL Server almacena datos en páginas de 8 KB. Cuando insertamos datos en una tabla, SQL Server asignará una página para almacenar esos datos sin que los datos insertados sean de más de 8 KB en los que abarcarían varias páginas.Cada página está asignada a una tabla. Si creamos 10 tablas, tendremos 10 páginas diferentes.
A medida que inserte datos en una tabla, los datos irán primero al archivo de registro de transacciones. El archivo de registro de transacciones es un registro secuencial, lo que significa que a medida que inserta, actualiza y elimina registros, el registro registrará estas transacciones de principio a fin. Por otro lado, el archivo de datos no es esencial. El archivo de registro descargará los datos al archivo de datos creando páginas en todo el lugar.
Ahora que tenemos una idea de cómo se almacenan los datos, ¿qué tiene que ver esto con la fragmentación?
Hay dos tipos de fragmentación: Fragmentación Interna y Fragmentación Externa.
Fragmentación interna de SQL Server
La fragmentación interna de SQL Server es causada por páginas que tienen demasiado espacio libre.Imaginemos que al principio del día tenemos una tabla con 40 páginas que están 100% llenas, pero al final del día tenemos una tabla con 50 páginas que solo están 80% llenas debido a varias declaraciones de borrar e insertar a lo largo del día.Esto causa un problema porque ahora, cuando necesitamos leer de esta tabla, tenemos 50 páginas de toscan en lugar de 40, lo que podría resultar en una disminución del rendimiento.Veamos un ejemplo rápido y sucio.
Digamos que tengo la siguiente tabla con una Clave primaria y un índice no agrupado en el nombre y el apellido:
Hablaré sobre las formas de analizar la fragmentación más adelante en este consejo, pero por ahora podemos hacer clic con el botón derecho en el índice, hacer clic en Propiedades y Fragmentación para ver fragmentación y plenitud de página. Este es un índice completamente nuevo, por lo que está al 0% de fragmentación.
Insertemos 1000 filas en esta tabla:
INSERT INTO Person VALUES('Brady', 'Upton', '123 Main Street', 'TN', 55555)GO 1000
Ahora, revisemos nuestro índice de nuevo:
Puede ver que nuestro índice se fragmenta en un 75% y el porcentaje promedio de páginas completas (plenitud de páginas) aumenta al 80%. Esta tabla sigue siendo tan pequeña que la fragmentación del 75% probablemente no causaría ningún problema de rendimiento, pero a medida que la tabla aumenta de tamaño y el número de páginas aumenta, es posible que el rendimiento se degrade. También puede ver en la pantalla de arriba que esta tabla pasó de 0 páginas a 4.
Fragmentación externa de SQL Server
La fragmentación externa es causada por páginas que no están ordenadas. Al principio del día tenemos una mesa perfectamente ordenada. Durante el día, emitimos cientos de instrucciones de actualización, posiblemente dejando espacio vacío en una página e intentando encajar espacio en otras páginas. Esto significa que nuestro almacenamiento tiene que moverse para obtener los datos necesarios en lugar de leer en una dirección.
Analizar la fragmentación de SQL Server
¿Entonces la fragmentación es un problema? Creo que lo es. Si puede almacenar toda su base de datos en memoria o si su base de datos solo se lee, entonces no me preocuparía, pero la mayoría de nosotros no tenemos ese lujo.He trabajado en miles de servidores y analizar los niveles de fragmentación es una de las primeras cosas que veo. De hecho, con solo arreglar la fragmentación, he visto hasta un 200% de mejoras en el rendimiento de las consultas.
Hablando de analizar los niveles de fragmentación usted puede estar preguntándose cómo podemos hacerlo. Bueno, hay algunas maneras
DBCC SHOWCONTIG-esta característica es antigua y se eliminará en versiones futuras de SQL Server, pero si todavía está utilizando SQL Server 2000 o inferior, esto le ayudará. En lugar de escribir sobre ello, te señalaré aquí o puedes revisar la punta de Chad Boyd aquí. Ambos son buenos recursos.
sys. dm_db_index_physical_stats: Introducido en SQL Server 2005, esta vista de administración dinámica (DMV) devuelve información de tamaño y fragmentación para los datos e índices de la tabla o vista especificada.
SELECT OBJECT_NAME(ips.OBJECT_ID) ,i.NAME ,ips.index_id ,index_type_desc ,avg_fragmentation_in_percent ,avg_page_space_used_in_percent ,page_countFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ipsINNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)ORDER BY avg_fragmentation_in_percent DESC
Este es probablemente el método más utilizado para analizar la fragmentación. Puede ver en la captura de pantalla anterior que tengo un índice llamado IX_RPCust_1 en la tabla de Cpust que está fragmentado al 98,83%. Puedes ver más información sobre este DMV aquí de Arshad Ali.
Software de terceros: El software de terceros le ayudará a identificar la fragmentación para que no tenga que hacerlo. Greg Robidoux ofrece un buen consejo sobre el Administrador de Desfragmentación SQL de Idera aquí.
Arreglando la fragmentación de SQL Server
Ahora que hemos encontrado fragmentación en la base de datos, ¿cómo la solucionamos? Al igual que analizar índices, hay múltiples formas.
Planes de mantenimiento de SQL Server – Los planes de mantenimiento se envían con SQLServer y son buenos para algunas tareas. En mi opinión, el mantenimiento del índice no es uno de ellos. Puede agregar una tarea de reconstruir índice o reorganizar la tarea de índice en el plan de mantenimiento, pero el problema es que realmente no puede aplicar ninguna lógica al plan. Sin lógica, el plan reconstruirá / reorganizará CADA índice. Es posible que algunos índices no estén fragmentados, por lo que no es necesario reconstruirlos. Algunos índices pueden tener una fragmentación mínima, por lo que estarían bien con una reorganización. No hay forma de especificar esto en un plan de mantenimiento. InSQL Server 2016 hay nuevas opciones para gestionar la fragmentación.
Script personalizado-Los scripts personalizados son probablemente el camino a seguir porque puedes aplicar lógica para comprobar la fragmentación del índice y luego decidir si omitir el índice, reorganizarlo o reconstruirlo. Utilizo los guiones deola Hallengren y hacen exactamente lo que necesito que hagan. MSSQLTips.com también tiene estos recursos: Fragmentación de SQL Server y Consejos de Mantenimiento de Índices.
Software de terceros: El software de terceros le ayudará a desfragmentar los índices para que no tenga que hacerlo.
Próximos pasos
- Este consejo le da una idea de algunos de los componentes involucrados en la segmentación de índices. MSSQLTips.com ofrece consejos más completos centrados en cada uno de los temas que mencioné aquí.
- Asegúrese de consultar el tutorial de fragmentación de SQL Server de Chad Boyd, que ofrece una gran información sobre numerosos temas relacionados con la fragmentación
Última actualización: 2016-06-22
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.View all my tips
- More SQL Server DBA Tips…