Agradeceríamos tu ayuda para extender nuestros tutoriales con relación a las ciencias informáticas.
Solución:
¿Está seguro de haber identificado de manera positiva y precisa esta tabla del sistema como la única fuente de “presión innecesaria en el grupo de búfer y también afecta negativamente el rendimiento de las operaciones, como calcular el tamaño de todas las tablas en una base de datos”? ¿Está seguro de que esta tabla del sistema no se autoadministra de tal manera que (a) la fragmentación se minimice o se mantenga bajo control en secreto o simplemente (b) se administre de manera eficiente en la memoria para que los niveles de desfragmentación realmente no afecten mucho?
Puede ver cuántas páginas están en uso y cuánto espacio libre hay en las paginas que estan en la memoria (page_free_space_percent es siempre NULL en las asignaciones DMF, pero esto es disponible en el búfer DMV) – esto debería darle una idea si lo que le preocupa es realmente algo por lo que debería preocuparse:
SELECT
Number_of_Pages = COUNT(*),
Number_of_Pages_In_Memory = COUNT(b.page_id),
Avg_Free_Space = AVG(b.free_space_in_bytes/8192.0)
FROM sys.dm_db_database_page_allocations
(
DB_ID(),
OBJECT_ID(N'sys.syscolpars'),
NULL,NULL,'DETAILED'
) AS p
LEFT OUTER JOIN sys.dm_os_buffer_descriptors AS b
ON b.database_id = DB_ID()
AND b.page_id = p.allocated_page_page_id
AND b.file_id = p.allocated_page_file_id;
Si su número de páginas es pequeño (como probablemente <10000 para las tablas del sistema) o si el espacio libre es "bajo" (no estoy seguro de cuáles son sus umbrales típicos para la reorganización/reconstrucción), concéntrese en otras frutas más interesantes y al alcance de la mano. .
Si su número de páginas es grande y el espacio libre es “alto”, está bien, entonces tal vez le estoy dando demasiado crédito a SQL Server por su propio mantenimiento. Como mostró en la otra pregunta, esto funciona …
ALTER INDEX ALL ON sys.syscolpars REORGANIZE;
…y lo hace reducir la fragmentación. Aunque puede requerir permisos elevados (no lo intenté como peón).
Tal vez pueda hacer esto periódicamente como parte de su propio mantenimiento, si lo hace sentir bien y/o si tiene alguna evidencia de que tiene algún impacto positivo en su sistema.
Basado en la orientación de la respuesta de Aaron, así como en investigaciones adicionales, aquí hay una breve reseña del enfoque que tomé.
Por lo que puedo decir, las opciones para inspeccionar la fragmentación de las tablas base del sistema son limitadas. Seguí adelante y presenté un problema de conexión para brindar una mejor visibilidad, pero mientras tanto parece que las opciones incluyen cosas como examinar el grupo de búfer o verificar el número promedio de bytes por fila.
Luego creé un procedimiento para realizar `ALTER INDEX…REORGANIZE en todas las tablas base del sistema. La ejecución de este procedimiento en algunos de nuestros servidores de desarrollo más (abusados) mostró que el tamaño acumulativo de las tablas base del sistema se redujo hasta en 50 GB (con tablas de usuarios de ~5MM en el sistema, claramente un caso extremo).
Una de nuestras tareas de mantenimiento nocturnas, que ayuda a limpiar muchas de las tablas de usuarios creadas por varias pruebas unitarias y desarrollo, tardaba aproximadamente 50 minutos en completarse. Una combinación de sp_whoisactive, sys.dm_os_waiting_tasksy DBCC PAGE mostró que las esperas estaban dominadas por E/S en las tablas base del sistema.
Después de la reorganización de todas las tablas base del sistema, la tarea de mantenimiento se redujo a ~15 minutos. Todavía había algunas esperas de E/S, pero se redujeron significativamente, quizás debido a una mayor cantidad de datos que quedan en la caché y/o más lecturas anticipadas debido a una menor fragmentación.
Por lo tanto, mi conclusión es que sumando ALTER INDEX...REORGANIZE para las tablas base del sistema en un plan de mantenimiento puede ser algo útil a considerar, pero probablemente solo si tiene un escenario en el que se crea una cantidad inusual de objetos en una base de datos.
Si entiendes que ha resultado provechoso este artículo, sería de mucha ayuda si lo compartes con el resto seniors y nos ayudes a difundir nuestra información.