Como se sabe, los índices juegan un papel importante en las bases de datos, proporcionando una búsqueda rápida de los registros deseados. Por eso es tan importante mantenerlos adecuadamente. Se ha escrito bastante sobre el análisis y la optimización, incluso en Internet. Por ejemplo, recientemente se realizó una revisión sobre este tema en .
Existen numerosas soluciones tanto de pago como gratuitas para esto. Por ejemplo, hay una lista completa de , basada en un método de optimización adaptativo de índices.
A continuación, consideraremos la utilidad gratuita , cuyo autor es .
La principal diferencia técnica entre SQLIndexManager y otros análogos es mencionada por el propio autor. y .
En este artículo, echaremos un vistazo al proyecto y a las posibilidades de explotación de esta solución de software.
Se discute esta utilidad .
Con el tiempo, la mayoría de los comentarios y errores han sido corregidos.
Así que ahora pasemos a la propia utilidad SQLIndexManager.
La aplicación está escrita en C# .NET Framework 4.5 en Visual Studio 2017 y utiliza DevExpress para los formularios:
y se ve de la siguiente manera:
Todas las consultas se generan en los siguientes archivos:
- Index
- Query
- QueryEngine
- ServerInfo
Al conectarse a la base de datos y enviar consultas a la base de datos, la aplicación se registra de la siguiente manera:
ApplicationName="SQLIndexManager" Al iniciar la aplicación, se abrirá una ventana modal para agregar la conexión:
Aún no funciona la carga completa de la lista de todas las instancias de MS SQL Server disponibles en las redes locales.
También se puede agregar una conexión con el botón más a la izquierda en el menú principal:
A continuación, se ejecutarán las siguientes consultas a la base de datos:
- Obtención de información sobre la base de datos
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Obtención de una lista de bases de datos disponibles con sus propiedades breves
SELECT DatabaseName = t.[name] , d.DataSize , DataUsedSize = CAST(NULL AS BIGINT) , d.LogSize , LogUsedSize = CAST(NULL AS BIGINT) , RecoveryModel = t.recovery_model_desc , LogReuseWait = t.log_reuse_wait_desc FROM sys.databases t WITH(NOLOCK) LEFT JOIN ( SELECT [database_id] , DataSize = SUM(CASE WHEN [type] = 0 THEN CAST(size AS BIGINT) END) , LogSize = SUM(CASE WHEN [type] = 1 THEN CAST(size AS BIGINT) END) FROM sys.master_files WITH(NOLOCK) GROUP BY [database_id] ) d ON d.[database_id] = t.[database_id] WHERE t.[state] = 0 AND t.[database_id] != 2 AND ISNULL(HAS_DBACCESS(t.[name]), 1) = 1
Después de ejecutar los scripts mencionados anteriormente, aparecerá una ventana con información breve sobre las bases de datos del ejemplo seleccionado de MS SQL Server:
Cabe destacar que la información ampliada se muestra en función de los permisos. Si hay , se pueden seleccionar datos de la vista . Si no hay tales permisos, simplemente se devuelven menos datos para no ralentizar la consulta.
Aquí es necesario seleccionar las bases de datos de interés y presionar el botón “OK”.
A continuación, se ejecutará el siguiente script para cada base de datos seleccionada para analizar el estado de los índices:
Análisis del estado de los índices
declare @Fragmentation float=15;
declare @MinIndexSize bigint=768;
declare @MaxIndexSize bigint=1048576;
declare @PreDescribeSize bigint=32768;
SET NOCOUNT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
IF OBJECT_ID('tempdb.dbo.#AllocationUnits') IS NOT NULL
DROP TABLE #AllocationUnits
CREATE TABLE #AllocationUnits (
ContainerID BIGINT PRIMARY KEY
, ReservedPages BIGINT NOT NULL
, UsedPages BIGINT NOT NULL
)
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
IF OBJECT_ID('tempdb.dbo.#ExcludeList') IS NOT NULL
DROP TABLE #ExcludeList
CREATE TABLE #ExcludeList (ID INT PRIMARY KEY)
INSERT INTO #ExcludeList
SELECT [object_id]
FROM sys.objects WITH(NOLOCK)
WHERE [type] IN ('V', 'U')
AND ( [is_ms_shipped] = 1 )
IF OBJECT_ID('tempdb.dbo.#Partitions') IS NOT NULL
DROP TABLE #Partitions
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)
IF OBJECT_ID('tempdb.dbo.#Indexes') IS NOT NULL
DROP TABLE #Indexes
CREATE TABLE #Indexes (
ObjectID INT NOT NULL
, IndexID INT NOT NULL
, IndexName SYSNAME NULL
, PagesCount BIGINT NOT NULL
, UnusedPagesCount BIGINT NOT NULL
, PartitionNumber INT NOT NULL
, RowsCount BIGINT NOT NULL
, IndexType TINYINT NOT NULL
, IsAllowPageLocks BIT NOT NULL
, DataSpaceID INT NOT NULL
, DataCompression TINYINT NOT NULL
, IsUnique BIT NOT NULL
, IsPK BIT NOT NULL
, FillFactorValue INT NOT NULL
, IsFiltered BIT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)
INSERT INTO #Indexes
SELECT ObjectID = i.[object_id]
, IndexID = i.index_id
, IndexName = i.[name]
, PagesCount = a.ReservedPages
, UnusedPagesCount = CASE WHEN ABS(a.ReservedPages - a.UsedPages) > 32 THEN a.ReservedPages - a.UsedPages ELSE 0 END
, 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
DECLARE @files TABLE (ID INT PRIMARY KEY)
INSERT INTO @files
SELECT DISTINCT [data_space_id]
FROM sys.database_files WITH(NOLOCK)
WHERE [state] != 0
AND [type] = 0
IF @@ROWCOUNT > 0 BEGIN
DELETE FROM i
FROM #Indexes i
LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNumber = dds.[destination_id]
WHERE ISNULL(dds.[data_space_id], i.DataSpaceID) IN (SELECT * FROM @files)
END
DECLARE @DBID INT
, @DBNAME SYSNAME
SET @DBNAME = DB_NAME()
SELECT @DBID = [database_id]
FROM sys.databases WITH(NOLOCK)
WHERE [name] = @DBNAME
IF OBJECT_ID('tempdb.dbo.#Fragmentation') IS NOT NULL
DROP TABLE #Fragmentation
CREATE TABLE #Fragmentation (
ObjectID INT NOT NULL
, IndexID INT NOT NULL
, PartitionNumber INT NOT NULL
, Fragmentation FLOAT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)
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 = @Fragmentation
OR
i.PagesCount > @PreDescribeSize
OR
i.IndexType IN (5, 6)
)
Como se puede ver en las propias consultas, las tablas temporales son utilizadas con bastante frecuencia. Esto se hace para evitar recompilaciones y, en el caso de un esquema grande, se puede generar un plan de manera paralela al insertar datos, ya que la inserción con variables de tabla solo es posible en un solo hilo.
Después de ejecutar el script mencionado anteriormente, aparecerá una ventana con la tabla de índices:
También aquí se puede mostrar otra información detallada, como:
- base de datos
- número de secciones
- fecha y hora del último acceso
- etc.) de un 10% a un 100%.
- grupo de archivos
etc.
Las columnas se pueden ajustar:
En las celdas de la columna Fix se puede seleccionar qué acción se llevará a cabo durante la optimización. Además, al finalizar el escaneo, la acción predeterminada se elige según la configuración seleccionada:
Es necesario seleccionar los índices que se procesarán.
A través del menú principal se puede tanto guardar el script (esta misma botón inicia el proceso de optimización de índices):
como guardar la tabla en diferentes formatos (este mismo botón permite abrir configuraciones detalladas para el análisis y optimización de índices):
También se puede actualizar la información haciendo clic en el tercer botón a la izquierda en el menú principal, junto a la lupa.
El botón con la lupa permite seleccionar las bases de datos que se van a considerar.
No hay un sistema de referencia completo en este momento. Por lo tanto, hacer clic en el botón “?” simplemente hará que aparezca una ventana modal que contiene la información básica sobre el producto de software:
Además de todo lo mencionado anteriormente, hay una barra de búsqueda en el menú principal:
Al iniciar el proceso de optimización de índices:
También se puede ver el registro de acciones realizadas en la parte inferior de la ventana:
En la ventana de configuraciones detalladas para el análisis y optimización de índices se pueden ajustar opciones más finas:
Sugerencias para la aplicación:
- hacer posible actualizar selectivamente estadísticas no solo para índices y de diferentes maneras (actualizar completamente o parcialmente)
- hacer posible no solo seleccionar bases de datos, sino también diferentes servidores (esto es muy conveniente cuando hay muchas instancias de MS SQL Server)
- para una mayor flexibilidad en su uso, se propone envolver comandos en bibliotecas y mostrarlos en comandos de PowerShell, como se hace, por ejemplo, aquí:
- habilitar la posibilidad de guardar y modificar configuraciones personales tanto para toda la aplicación como, si es necesario, para cada instancia de MS SQL Server y cada base de datos
- de los puntos 2 y 4 se deriva el deseo de crear grupos por bases de datos y grupos por instancias de MS SQL Server, para las cuales las configuraciones son las mismas
- realizar la búsqueda de duplicados de índices (completos y parciales, que o bien difieren poco, o solo se diferencian por las columnas incluidas)
- dado que SQLIndexManager se utiliza solo para bases de datos MS SQL Server, es necesario reflejar esto en el nombre, por ejemplo, de la siguiente manera: SQLIndexManager para MS SQL Server
- extraer todas las partes de la aplicación que no son GUI en módulos separados y reescribirlas en .NET Core 2.1
En el momento de escribir este artículo, el punto 6 de los deseos se está desarrollando activamente y ya hay soporte en forma de búsqueda de duplicados completos y similares:
Fuentes
Fuente: habr.com
