무료 도구 SQLIndexManager 검토

아시다시피 인덱스는 DBMS에서 필요한 레코드를 빠르게 검색하는 중요한 역할을 합니다. 그렇기 때문에 적시에 서비스를 제공하는 것이 매우 중요합니다. 인터넷을 포함하여 분석 및 최적화에 관한 많은 자료가 작성되었습니다. 예를 들어, 이 주제는 최근에 검토되었습니다. 이 간행물.

이에 대한 많은 유료 및 무료 솔루션이 있습니다. 예를 들어, 기성품이 있습니다. 결정, 적응형 인덱스 최적화 방법을 기반으로 합니다.

다음으로 무료 유틸리티를 살펴보겠습니다. SQLIndexManager, 작성자: 앨런덴턴.

SQLIndexManager와 다른 여러 유사체 간의 주요 기술적 차이점은 작성자가 직접 제공합니다. 여기에 и 여기에.

이 기사에서는 프로젝트와 이 소프트웨어 솔루션의 운영 기능을 외부적으로 살펴보겠습니다.

이 유틸리티에 대해 논의하기 여기에.
시간이 지나면서 대부분의 댓글과 버그가 수정되었습니다.

이제 SQLIndexManager 유틸리티 자체에 대해 살펴보겠습니다.

이 애플리케이션은 Visual Studio 4.5의 C# .NET Framework 2017로 작성되었으며 양식에 DevExpress를 사용합니다.

다음과 같이 보입니다.

모든 요청은 다음 파일에서 생성됩니다.

1. 색인
2. 질문
3. 쿼리엔진
4. 서버 정보

데이터베이스에 연결하고 DBMS에 쿼리를 보낼 때 애플리케이션은 다음과 같이 서명됩니다.

ApplicationName=”SQLIndexManager”

애플리케이션을 실행하면 연결을 추가할 수 있는 모달 창이 열립니다.

여기서는 로컬 네트워크를 통해 액세스할 수 있는 모든 MS SQL Server 인스턴스의 전체 목록을 로드하는 것이 아직 작동하지 않습니다.

기본 메뉴의 가장 왼쪽 버튼을 사용하여 연결을 추가할 수도 있습니다.

다음으로 DBMS에 대한 다음 쿼리가 시작됩니다.

1. DBMS에 대한 정보 얻기

   SELECT ProductLevel  = SERVERPROPERTY('ProductLevel')
        , Edition       = SERVERPROPERTY('Edition')
        , ServerVersion = SERVERPROPERTY('ProductVersion')
        , IsSysAdmin    = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT)
   

2. 간략한 속성과 함께 사용 가능한 데이터베이스 목록 가져오기

   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
   

위 스크립트를 실행하면 선택한 MS SQL Server 인스턴스의 데이터베이스에 대한 간략한 정보가 포함된 창이 나타납니다.

권한별로 확장된 정보가 표시된다는 점은 주목할 만합니다. 만약 거기에 시스템 관리자을 선택하면 보기에서 데이터를 선택할 수 있습니다. sys.master_files. 그러한 권한이 없으면 요청 속도가 느려지지 않도록 더 적은 양의 데이터가 반환됩니다.

여기에서 관심 있는 데이터베이스를 선택하고 "확인" 버튼을 클릭해야 합니다.

다음으로, 선택된 각 데이터베이스에 대해 다음 스크립트가 실행되어 인덱스 상태를 분석합니다.

지수 현황 분석

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 <= @PreDescribeSize
AND r.[index_level] = 0
AND r.[alloc_unit_type_desc] = 'IN_ROW_DATA'
AND i.IndexType IN (0, 1, 2)
IF OBJECT_ID('tempdb.dbo.#Columns') IS NOT NULL
DROP TABLE #Columns
CREATE TABLE #Columns (
ObjectID     INT NOT NULL
, ColumnID     INT NOT NULL
, ColumnName   SYSNAME NULL
, SystemTypeID TINYINT NULL
, IsSparse     BIT
, IsColumnSet  BIT
, MaxLen       INT
, PRIMARY KEY (ObjectID, ColumnID)
)
INSERT INTO #Columns
SELECT ObjectID     = [object_id]
, ColumnID     = [column_id]
, ColumnName   = [name]
, SystemTypeID = [system_type_id]
, IsSparse     = [is_sparse]
, IsColumnSet  = [is_column_set]
, MaxLen       = [max_length]
FROM sys.columns WITH(NOLOCK)
WHERE [object_id] IN (SELECT DISTINCT i.ObjectID FROM #Indexes i)
IF OBJECT_ID('tempdb.dbo.#IndexColumns') IS NOT NULL
DROP TABLE #IndexColumns
CREATE TABLE #IndexColumns (
ObjectID   INT NOT NULL
, IndexID    INT NOT NULL
, OrderID    INT NOT NULL
, ColumnID   INT NOT NULL
, IsIncluded BIT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, ColumnID)
)
INSERT INTO #IndexColumns
SELECT ObjectID   = [object_id]
, IndexID    = [index_id]
, OrderID    = CASE WHEN [is_included_column] = 0 THEN [key_ordinal] ELSE [index_column_id] END
, ColumnID   = [column_id]
, IsIncluded = ISNULL([is_included_column], 0)
FROM sys.index_columns ic WITH(NOLOCK)
WHERE EXISTS(
SELECT *
FROM #Indexes i
WHERE i.ObjectID = ic.[object_id]
AND i.IndexID = ic.[index_id]
AND i.IndexType IN (1, 2)
)
IF OBJECT_ID('tempdb.dbo.#Lob') IS NOT NULL
DROP TABLE #Lob
CREATE TABLE #Lob (
ObjectID    INT NOT NULL
, IndexID     INT NOT NULL
, IsLobLegacy BIT
, IsLob       BIT
, PRIMARY KEY (ObjectID, IndexID)
)
INSERT INTO #Lob (ObjectID, IndexID, IsLobLegacy, IsLob)
SELECT c.ObjectID
, IndexID     = ISNULL(i.IndexID, 1)
, IsLobLegacy = MAX(CASE WHEN c.SystemTypeID IN (34, 35, 99) THEN 1 END)
, IsLob       = 0
FROM #Columns c
LEFT JOIN #IndexColumns i ON c.ObjectID = i.ObjectID AND c.ColumnID = i.ColumnID
WHERE c.SystemTypeID IN (34, 35, 99)
GROUP BY c.ObjectID
, i.IndexID
IF OBJECT_ID('tempdb.dbo.#Sparse') IS NOT NULL
DROP TABLE #Sparse
CREATE TABLE #Sparse (ObjectID INT PRIMARY KEY)
INSERT INTO #Sparse
SELECT DISTINCT ObjectID
FROM #Columns
WHERE IsSparse = 1
OR IsColumnSet = 1
IF OBJECT_ID('tempdb.dbo.#AggColumns') IS NOT NULL
DROP TABLE #AggColumns
CREATE TABLE #AggColumns (
ObjectID        INT NOT NULL
, IndexID         INT NOT NULL
, IndexColumns    NVARCHAR(MAX)
, IncludedColumns NVARCHAR(MAX)
, PRIMARY KEY (ObjectID, IndexID)
)
INSERT INTO #AggColumns
SELECT t.ObjectID
, t.IndexID
, IndexColumns = STUFF((
SELECT ', [' + c.ColumnName + ']'
FROM #IndexColumns i
JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID
WHERE i.ObjectID = t.ObjectID
AND i.IndexID = t.IndexID
AND i.IsIncluded = 0
ORDER BY i.OrderID
FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
, IncludedColumns = STUFF((
SELECT ', [' + c.ColumnName + ']'
FROM #IndexColumns i
JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID
WHERE i.ObjectID = t.ObjectID
AND i.IndexID = t.IndexID
AND i.IsIncluded = 1
ORDER BY i.OrderID
FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
FROM (
SELECT DISTINCT ObjectID, IndexID
FROM #Indexes
WHERE IndexType IN (1, 2)
) t
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)
)

쿼리 자체에서 볼 수 있듯이 임시 테이블이 자주 사용됩니다. 이는 재컴파일이 없도록 하기 위한 것이며, 대규모 스키마의 경우 테이블 변수 삽입은 하나의 스레드에서만 가능하므로 데이터 삽입 시 병렬로 계획을 생성할 수 있다.

위 스크립트를 실행하면 인덱스 테이블이 있는 창이 나타납니다.

여기에 다음과 같은 기타 세부 정보를 표시할 수도 있습니다.

1. 데이터베이스
2. 섹션 수
3. 마지막 통화 날짜 및 시간
4. 압축
5. 파일 그룹

그리고 t. 라.
스피커 자체를 맞춤 설정할 수 있습니다.

수정 열의 셀에서 최적화 중에 수행할 작업을 선택할 수 있습니다. 또한 검사가 완료되면 선택한 설정에 따라 기본 작업이 선택됩니다.

처리를 위해 원하는 인덱스를 선택해야 합니다.

기본 메뉴를 사용하여 스크립트를 저장할 수 있습니다(동일한 버튼으로 인덱스 최적화 프로세스 자체가 시작됩니다).

테이블을 다양한 형식으로 저장합니다(동일한 버튼을 사용하면 인덱스 분석 및 최적화를 위한 세부 설정을 열 수 있습니다).

메인 메뉴의 돋보기 옆에 있는 왼쪽 세 번째 버튼을 클릭하여 정보를 업데이트할 수도 있습니다.

돋보기가 있는 버튼을 사용하면 고려할 데이터베이스를 선택할 수 있습니다.

현재 포괄적인 도움말 시스템이 없습니다. 그러므로 “?” 버튼을 누르면 소프트웨어 제품에 대한 기본 정보가 포함된 모달 창이 나타납니다.

위에서 설명한 모든 것 외에도 기본 메뉴에는 검색 창이 있습니다.

인덱스 최적화 프로세스를 시작할 때:

창 하단에서 수행된 작업의 로그를 볼 수도 있습니다.

인덱스 분석 및 최적화를 위한 세부 설정 창에서는 보다 세부적인 옵션을 구성할 수 있습니다.

신청서 요청:

1. 인덱스뿐만 아니라 다양한 방법(전체 업데이트 또는 부분 업데이트)으로 선택적으로 통계 업데이트 가능
2. 데이터베이스뿐만 아니라 다른 서버도 선택할 수 있도록 합니다. (MS SQL Server 인스턴스가 많을 때 매우 편리합니다.)
3. 사용 유연성을 높이려면 다음과 같이 명령을 라이브러리에 래핑하여 PowerShell 명령으로 출력하는 것이 좋습니다.
4. dbatools.io/commands
5. 전체 애플리케이션과 필요한 경우 MS SQL Server의 각 인스턴스 및 각 데이터베이스에 대한 개인 설정을 저장하고 변경할 수 있습니다.
6. 지점 2와 4에서 설정이 동일한 데이터베이스별로 그룹을 만들고 MS SQL Server 인스턴스별로 그룹을 생성하려고 합니다.
7. 중복 인덱스 검색(완전 및 불완전, 약간 다르거나 포함된 열에서만 다름)
8. SQLIndexManager는 MS SQL Server DBMS에만 사용되므로 이를 이름에 반영해야 합니다. 예를 들면 다음과 같습니다. SQLIndexManager for MS SQL Server
9. 애플리케이션의 GUI가 아닌 모든 부분을 별도의 모듈로 이동하고 .NET Core 2.1에서 다시 작성합니다.

이 글을 쓰는 시점에서 소원 항목 6이 활발히 개발되고 있으며 완전하고 유사한 중복 항목을 검색하는 형태로 이미 지원이 있습니다.

소스

• 적응형 인덱스 최적화 방법
• SQLIndexManager 유틸리티
• SQLIndexManager 유틸리티에 대한 간략한 기술 개요
• SQLIndexManager 유틸리티에 대한 토론
• SQL 인덱스 관리자 – SQL Server 및 Azure의 인덱스 유지 관리를 위한 무료 GUI 도구
• SQL Server 인덱스 분석 및 최적화

출처 : habr.com