Durante muchos años trabajando como DBA de SQL Server y administrando servidores, luego optimizando el rendimiento. En general, quería hacer algo útil en mi tiempo libre para el Universo y nuestros colegas. Así que al final obtuvimos una pequeña
herramienta de mantenimiento de índice de
código abierto para SQL Server y Azure.
Idea
A veces, cuando se trabaja en sus prioridades, las personas pueden parecerse a una batería tipo dedo: hay suficiente carga de motivación para un solo flash, y eso es todo. Y hasta hace poco, no era la excepción a esta observación de la vida. A menudo me visitaban las ideas para crear algo propio, pero las prioridades cambiaban y nada llegaba al final.
El trabajo en la empresa Jarkov Devart, que se dedicaba a la creación de software para el desarrollo y administración de bases de datos SQL Server, MySQL y Oracle, proporcionó una influencia bastante fuerte en mi motivación y desarrollo profesional.
Antes de llegar a ellos, tenía poca idea de los detalles de la creación de mi propio producto, pero ya en el proceso adquirí muchos conocimientos sobre la estructura interna de SQL Server. Después de haber optimizado las solicitudes de metadatos en sus líneas de productos durante más de un año, poco a poco comencé a comprender qué funcionalidad tiene más demanda en el mercado que ninguna otra.
En cierta etapa, surgió la idea de crear un nuevo producto de nicho, pero debido a las circunstancias, esta idea no despegó. En ese momento, para el nuevo proyecto, no había recursos curiosos suficientes suficientes dentro de la empresa sin perjuicio del negocio principal.
Ya cuando trabajaba en un lugar nuevo e intentaba hacer el proyecto por su cuenta, tenía que comprometerse constantemente. La idea inicial de hacer un producto grande lleno de características rápidamente quedó en nada y gradualmente se transformó en una dirección diferente: dividir la funcionalidad planificada en mini-herramientas separadas e implementarlas de forma independiente.
Como resultado, nació
SQL Index Manager , una herramienta gratuita de mantenimiento de índices para SQL Server y Azure. La idea principal era tomar como base alternativas comerciales de RedGate y Devart y tratar de mejorar su funcionalidad. Proporcionar, tanto para usuarios principiantes como experimentados, la capacidad de analizar y mantener índices convenientemente.
Implementación
En palabras, todo siempre suena simple ... echó un vistazo a un par de vidosikov motivadores, se paró en un estante y comenzó a hacer un producto genial. Pero en la práctica, no todo es tan optimista, ya que existen muchas dificultades al trabajar con la función de tabla del sistema sys.dm_db_index_physical_stats y, en combinación, el único lugar desde donde puede obtener información relevante sobre la fragmentación del índice.
Desde los primeros días de desarrollo, hubo una gran oportunidad para hacer un camino triste entre los esquemas estándar y copiar la lógica ya depurada del trabajo de las aplicaciones competidoras, al tiempo que agregaba una pequeña mordaza. Pero después de analizar las solicitudes de metadatos, quería hacer algo más optimizado, que, debido a la burocracia de las grandes empresas, nunca habría aparecido en sus productos.
Al analizar RedGate SQL Index Manager (1.1.9.1378 - $ 155), puede ver que la aplicación utiliza un enfoque muy simple: con una consulta obtenemos una lista de tablas y vistas de usuarios, y después de la segunda consulta, se devuelve una lista de todos los índices dentro de la base de datos seleccionada.
SELECT objects.name AS tableOrViewName , objects.object_id AS tableOrViewId , schemas.name AS schemaName , CAST(ISNULL(lobs.NumLobs, 0) AS BIT) AS ContainsLobs , o.is_memory_optimized FROM sys.objects AS objects JOIN sys.schemas AS schemas ON schemas.schema_id = objects.schema_id LEFT JOIN ( SELECT object_id , COUNT(*) AS NumLobs FROM sys.columns WITH (NOLOCK) WHERE system_type_id IN (34, 35, 99) OR max_length = -1 GROUP BY object_id ) AS lobs ON objects.object_id = lobs.object_id LEFT JOIN sys.tables AS o ON o.object_id = objects.object_id WHERE objects.type = 'U' OR objects.type = 'V' SELECT i.object_id AS tableOrViewId , i.name AS indexName , i.index_id AS indexId , i.allow_page_locks AS allowPageLocks , p.partition_number AS partitionNumber , CAST((c.numPartitions - 1) AS BIT) AS belongsToPartitionedIndex FROM sys.indexes AS i JOIN sys.partitions AS p ON p.index_id = i.index_id AND p.object_id = i.object_id JOIN ( SELECT COUNT(*) AS numPartitions , object_id , index_id FROM sys.partitions GROUP BY object_id , index_id ) AS c ON c.index_id = i.index_id AND c.object_id = i.object_id WHERE i.index_id > 0 -- ignore heaps AND i.is_disabled = 0 AND i.is_hypothetical = 0
Luego, en un ciclo, para cada sección del índice, se envía una solicitud para determinar su tamaño y nivel de fragmentación. Al final de la exploración, los índices que pesan menos que el umbral de entrada se descartan en el cliente.
EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 1, @partitionNr = 1 EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 2, @partitionNr = 1 EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 3, @partitionNr = 1
Al analizar la lógica de esta aplicación, puede encontrar muchas deficiencias. Por ejemplo, si encuentra fallas en las pequeñeces, antes de enviar una solicitud, no se verifica si la sección actual contiene cadenas para excluir las secciones vacías del escaneo.
Pero el problema es más grave en otro aspecto: el número de solicitudes del servidor será aproximadamente igual al número total de líneas de sys.partitions. Dado el hecho de que las bases de datos reales pueden contener decenas de miles de secciones, este matiz puede conducir a una gran cantidad de solicitudes similares al servidor. En una situación en la que la base de datos es remota, el tiempo de escaneo se hará aún más largo debido a mayores retrasos en la red para cada solicitud, incluso la más simple.
A diferencia de RedGate, un producto similar desarrollado en Devart: dbForge Index Manager para SQL Server (1.10.38 - $ 99) recibe información en una consulta grande y luego muestra todo en el cliente:
SELECT SCHEMA_NAME(o.[schema_id]) AS [schema_name] , o.name AS parent_name , o.[type] AS parent_type , i.name , i.type_desc , s.avg_fragmentation_in_percent , s.page_count , p.partition_number , p.[rows] , ISNULL(lob.is_lob_legacy, 0) AS is_lob_legacy , ISNULL(lob.is_lob, 0) AS is_lob , CASE WHEN ds.[type] = 'PS' THEN 1 ELSE 0 END AS is_partitioned FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s JOIN sys.partitions p ON s.[object_id] = p.[object_id] AND s.index_id = p.index_id AND s.partition_number = p.partition_number JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id LEFT JOIN ( SELECT c.[object_id] , index_id = ISNULL(i.index_id, 1) , is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END) , is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END) FROM sys.columns c LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id AND i.index_id > 0 WHERE c.system_type_id IN (34, 35, 99) OR c.max_length = -1 GROUP BY c.[object_id], i.index_id ) lob ON lob.[object_id] = i.[object_id] AND lob.index_id = i.index_id JOIN sys.objects o ON o.[object_id] = i.[object_id] JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id WHERE i.[type] IN (1, 2) AND i.is_disabled = 0 AND i.is_hypothetical = 0 AND s.index_level = 0 AND s.alloc_unit_type_desc = 'IN_ROW_DATA' AND o.[type] IN ('U', 'V')
Logramos deshacernos del problema principal con el velo del mismo tipo de consultas en un producto de la competencia, pero las desventajas de esta implementación son que no se pasan parámetros adicionales a la función sys.dm_db_index_physical_stats que pueden limitar el escaneo de índices obviamente innecesarios. De hecho, esto lleva a obtener información sobre todos los índices en el sistema y cargas de disco adicionales en la etapa de escaneo.
Es importante tener en cuenta que los datos obtenidos de sys.dm_db_index_physical_stats no se almacenan en caché de forma permanente en el grupo de búferes, por lo tanto, minimizar las lecturas físicas al obtener información sobre la fragmentación del índice fue una de las tareas prioritarias durante el desarrollo.
Después de varios experimentos, resultó combinar ambos enfoques, dividiendo el escaneo en dos partes. Primero, una consulta grande determina el tamaño de las secciones, prefiltrando aquellas que no están en el rango de filtrado:
INSERT INTO #AllocationUnits (ContainerID, ReservedPages, UsedPages) SELECT [container_id] , SUM([total_pages]) , SUM([used_pages]) FROM sys.allocation_units WITH(NOLOCK) GROUP BY [container_id] HAVING SUM([total_pages]) BETWEEN @MinIndexSize AND @MaxIndexSize
A continuación, obtenemos solo aquellas secciones que contienen datos para evitar operaciones de lectura innecesarias de índices vacíos.
SELECT [object_id] , [index_id] , [partition_id] , [partition_number] , [rows] , [data_compression] INTO #Partitions FROM sys.partitions WITH(NOLOCK) WHERE [object_id] > 255 AND [rows] > 0 AND [object_id] NOT IN (SELECT * FROM #ExcludeList)
Dependiendo de la configuración, solo se obtienen los tipos de índices que el usuario desea analizar (se admite el trabajo con montones, índices agrupados / no agrupados e indicadores de columna).
INSERT INTO #Indexes SELECT ObjectID = i.[object_id] , IndexID = i.index_id , IndexName = i.[name] , PagesCount = a.ReservedPages , UnusedPagesCount = a.ReservedPages - a.UsedPages , PartitionNumber = p.[partition_number] , RowsCount = ISNULL(p.[rows], 0) , IndexType = i.[type] , IsAllowPageLocks = i.[allow_page_locks] , DataSpaceID = i.[data_space_id] , DataCompression = p.[data_compression] , IsUnique = i.[is_unique] , IsPK = i.[is_primary_key] , FillFactorValue = i.[fill_factor] , IsFiltered = i.[has_filter] FROM #AllocationUnits a JOIN #Partitions p ON a.ContainerID = p.[partition_id] JOIN sys.indexes i WITH(NOLOCK) ON i.[object_id] = p.[object_id] AND p.[index_id] = i.[index_id] WHERE i.[type] IN (0, 1, 2, 5, 6) AND i.[object_id] > 255
Después de esto, comienza un poco de magia: para todos los índices pequeños, determinamos el nivel de fragmentación llamando repetidamente a la función sys.dm_db_index_physical_stats con una indicación completa de todos los parámetros.
INSERT INTO #Fragmentation (ObjectID, IndexID, PartitionNumber, Fragmentation) SELECT i.ObjectID , i.IndexID , i.PartitionNumber , r.[avg_fragmentation_in_percent] FROM #Indexes i CROSS APPLY sys.dm_db_index_physical_stats(@DBID, i.ObjectID, i.IndexID, i.PartitionNumber, 'LIMITED') r WHERE i.PagesCount <= @PreDescribeSize AND r.[index_level] = 0 AND r.[alloc_unit_type_desc] = 'IN_ROW_DATA' AND i.IndexType IN (0, 1, 2)
A continuación, devolvemos toda la información posible al cliente, filtrando el exceso de datos:
SELECT i.ObjectID , i.IndexID , i.IndexName , ObjectName = o.[name] , SchemaName = s.[name] , i.PagesCount , i.UnusedPagesCount , i.PartitionNumber , i.RowsCount , i.IndexType , i.IsAllowPageLocks , u.TotalWrites , u.TotalReads , u.TotalSeeks , u.TotalScans , u.TotalLookups , u.LastUsage , i.DataCompression , f.Fragmentation , IndexStats = STATS_DATE(i.ObjectID, i.IndexID) , IsLobLegacy = ISNULL(lob.IsLobLegacy, 0) , IsLob = ISNULL(lob.IsLob, 0) , IsSparse = CAST(CASE WHEN p.ObjectID IS NULL THEN 0 ELSE 1 END AS BIT) , IsPartitioned = CAST(CASE WHEN dds.[data_space_id] IS NOT NULL THEN 1 ELSE 0 END AS BIT) , FileGroupName = fg.[name] , i.IsUnique , i.IsPK , i.FillFactorValue , i.IsFiltered , a.IndexColumns , a.IncludedColumns FROM #Indexes i JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = i.ObjectID JOIN sys.schemas s WITH(NOLOCK) ON s.[schema_id] = o.[schema_id] LEFT JOIN #AggColumns a ON a.ObjectID = i.ObjectID AND a.IndexID = i.IndexID LEFT JOIN #Sparse p ON p.ObjectID = i.ObjectID LEFT JOIN #Fragmentation f ON f.ObjectID = i.ObjectID AND f.IndexID = i.IndexID AND f.PartitionNumber = i.PartitionNumber LEFT JOIN ( SELECT ObjectID = [object_id] , IndexID = [index_id] , TotalWrites = NULLIF([user_updates], 0) , TotalReads = NULLIF([user_seeks] + [user_scans] + [user_lookups], 0) , TotalSeeks = NULLIF([user_seeks], 0) , TotalScans = NULLIF([user_scans], 0) , TotalLookups = NULLIF([user_lookups], 0) , LastUsage = ( SELECT MAX(dt) FROM ( VALUES ([last_user_seek]) , ([last_user_scan]) , ([last_user_lookup]) , ([last_user_update]) ) t(dt) ) FROM sys.dm_db_index_usage_stats WITH(NOLOCK) WHERE [database_id] = @DBID ) u ON i.ObjectID = u.ObjectID AND i.IndexID = u.IndexID LEFT JOIN #Lob lob ON lob.ObjectID = i.ObjectID AND lob.IndexID = i.IndexID LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNumber = dds.[destination_id] JOIN sys.filegroups fg WITH(NOLOCK) ON ISNULL(dds.[data_space_id], i.DataSpaceID) = fg.[data_space_id] WHERE o.[type] IN ('V', 'U') AND ( f.Fragmentation >= @Fragmentation OR i.PagesCount > @PreDescribeSize OR i.IndexType IN (5, 6) )
Después de esto, las consultas de puntos determinan el nivel de fragmentación para índices grandes.
EXEC sp_executesql N' DECLARE @DBID INT = DB_ID() SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'') WHERE [index_level] = 0 AND [alloc_unit_type_desc] = ''IN_ROW_DATA''' , N'@ObjectID int,@IndexID int,@PartitionNumber int' , @ObjectId = 1044198770, @IndexId = 1, @PartitionNumber = 1 EXEC sp_executesql N' DECLARE @DBID INT = DB_ID() SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'') WHERE [index_level] = 0 AND [alloc_unit_type_desc] = ''IN_ROW_DATA''' , N'@ObjectID int,@IndexID int,@PartitionNumber int' , @ObjectId = 1552724584, @IndexId = 0, @PartitionNumber = 1
Debido a este enfoque, al generar consultas, fue posible resolver los problemas con el rendimiento de escaneo que se encontraron en las aplicaciones de la competencia. Esto podría completarse, pero en el proceso de desarrollo, aparecieron nuevas ideas gradualmente que permitieron ampliar el alcance de la aplicación de su producto.
Inicialmente, se implementó el soporte para trabajar con WAIT_AT_LOW_PRIORITY, luego se hizo posible usar DATA_COMPRESSION y FILL_FACTOR para reconstruir índices.
La aplicación estaba ligeramente cubierta de funcionalidades no planificadas previamente, como el mantenimiento de columnas de columnas:
SELECT * FROM ( SELECT IndexID = [index_id] , PartitionNumber = [partition_number] , PagesCount = SUM([size_in_bytes]) / 8192 , UnusedPagesCount = ISNULL(SUM(CASE WHEN [state] = 1 THEN [size_in_bytes] END), 0) / 8192 , Fragmentation = CAST(ISNULL(SUM(CASE WHEN [state] = 1 THEN [size_in_bytes] END), 0) * 100. / SUM([size_in_bytes]) AS FLOAT) FROM sys.fn_column_store_row_groups(@ObjectID) GROUP BY [index_id] , [partition_number] ) t WHERE Fragmentation >= @Fragmentation AND PagesCount BETWEEN @MinIndexSize AND @MaxIndexSize
O la capacidad de crear índices no agrupados basados en información de dm_db_missing_index:
SELECT ObjectID = d.[object_id] , UserImpact = gs.[avg_user_impact] , TotalReads = gs.[user_seeks] + gs.[user_scans] , TotalSeeks = gs.[user_seeks] , TotalScans = gs.[user_scans] , LastUsage = ISNULL(gs.[last_user_scan], gs.[last_user_seek]) , IndexColumns = CASE WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NOT NULL THEN d.[equality_columns] + ', ' + d.[inequality_columns] WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NULL THEN d.[equality_columns] ELSE d.[inequality_columns] END , IncludedColumns = d.[included_columns] FROM sys.dm_db_missing_index_groups g WITH(NOLOCK) JOIN sys.dm_db_missing_index_group_stats gs WITH(NOLOCK) ON gs.[group_handle] = g.[index_group_handle] JOIN sys.dm_db_missing_index_details d WITH(NOLOCK) ON g.[index_handle] = d.[index_handle] WHERE d.[database_id] = DB_ID()
Resumen
Después de seis meses de la fase de desarrollo activo, me alegra que los planes no terminen allí, porque quiero seguir desarrollando este producto. El siguiente paso es agregar funcionalidad para buscar índices duplicados o no utilizados, así como implementar soporte completo para servir estadísticas dentro de SQL Server.
Basado en el hecho de que hay muchas soluciones pagas en el mercado, quiero creer que debido al posicionamiento libre, una descripción de metadatos más optimizada y la presencia de varias pequeñas cosas útiles para alguien, este producto definitivamente será útil en las tareas cotidianas.
La versión actual de la aplicación se puede descargar en
GitHub . Las fuentes están en el mismo lugar.