Deleng alat gratis SQLIndexManager

Kaya sing sampeyan ngerteni, indeks nduweni peran penting ing DBMS, nyedhiyakake telusuran cepet menyang cathetan sing dibutuhake. Mulane penting banget kanggo nglayani kanthi tepat wektu. Cukup akeh materi sing wis ditulis babagan analisis lan optimasi, kalebu ing Internet. Contone, topik iki bubar dideleng ing publikasi iki.

Ana akeh solusi mbayar lan gratis kanggo iki. Contone, ana sing wis siap keputusan, adhedhasar metode optimasi indeks adaptif.

Sabanjure, ayo goleki sarana gratis SQLIndexManager, ditulis dening AlanDenton.

Bentenane teknis utama antarane SQLIndexManager lan sawetara analog liyane diwenehake dening penulis dhewe kene и kene.

Ing artikel iki, kita bakal nliti proyek lan kemampuan operasional solusi piranti lunak iki.

Ngrembug babagan sarana iki kene.
Swara wektu, akeh komentar lan bug wis didandani.

Dadi, ayo pindhah menyang utilitas SQLIndexManager dhewe.

Aplikasi kasebut ditulis ing C# .NET Framework 4.5 ing Visual Studio 2017 lan nggunakake DevExpress kanggo formulir:

lan katon kaya iki:

Kabeh panjalukan digawe ing file ing ngisor iki:

1. Index
2. Pitakonan
3. QueryEngine
4. ServerInfo

Nalika nyambung menyang database lan ngirim pitakon menyang DBMS, aplikasi kasebut ditandatangani kaya ing ngisor iki:

ApplicationName=”SQLIndexManager”

Nalika mbukak aplikasi, jendhela modal bakal mbukak kanggo nambah sambungan:

Ing kene, mbukak dhaptar lengkap kabeh conto MS SQL Server sing bisa diakses liwat jaringan lokal durung bisa.

Sampeyan uga bisa nambah sambungan nggunakake tombol paling kiwa ing menu utama:

Sabanjure, pitakon ing ngisor iki menyang DBMS bakal diluncurake:

1. Entuk informasi babagan DBMS

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

2. Entuk dhaptar database sing kasedhiya kanthi properti sing ringkes

   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
   

Sawise nglakokake skrip ing ndhuwur, jendhela bakal katon ngemot informasi ringkes babagan database conto MS SQL Server sing dipilih:

Wigati dicathet yen informasi lengkap ditampilake adhedhasar hak. Yen ana sysadmin, banjur sampeyan bisa milih data saka tampilan sys.master_files. Yen ora ana hak kasebut, banjur kurang data mung bali supaya ora alon-alon panjalukan.

Kene sampeyan kudu milih database kapentingan lan klik tombol "OK".

Sabanjure, skrip ing ngisor iki bakal dieksekusi kanggo saben database sing dipilih kanggo nganalisa kahanan indeks:

Analisis status indeks

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)
)

Kaya sing bisa dideleng saka pitakon kasebut, tabel sementara asring digunakake. Iki ditindakake supaya ora ana kompilasi ulang, lan ing kasus skema gedhe, rencana kasebut bisa digawe kanthi paralel nalika nglebokake data, amarga nglebokake variabel tabel mung bisa ditindakake ing siji benang.

Sawise nglakokake skrip ing ndhuwur, jendhela kanthi tabel indeks bakal katon:

Sampeyan uga bisa nampilake informasi rinci liyane ing kene, kayata:

1. database
2. nomer bagean
3. tanggal lan wektu telpon pungkasan
4. komprèsi
5. filegroup

lan liya-liyane.
Speaker dhewe bisa disesuaikan:

Ing sel kolom Fix, sampeyan bisa milih tumindak apa sing bakal ditindakake sajrone optimasi. Uga, nalika pindai rampung, tumindak standar dipilih adhedhasar setelan sing dipilih:

Sampeyan kudu milih indeks sing dikarepake kanggo diproses.

Nggunakake menu utama, sampeyan bisa nyimpen skrip (tombol sing padha miwiti proses optimasi indeks dhewe):

lan simpen tabel ing macem-macem format (tombol sing padha ngidini sampeyan mbukak setelan rinci kanggo nganalisa lan ngoptimalake indeks):

Sampeyan uga bisa nganyari informasi kanthi ngeklik tombol katelu ing sisih kiwa ing menu utama ing jejere kaca pembesar.

Tombol kanthi kaca pembesar ngidini sampeyan milih basis data sing dikarepake kanggo dipikirake.

Saiki ora ana sistem bantuan lengkap. Mulane, pencet tombol "?" mung bakal nyebabake jendhela modal katon ngemot informasi dhasar babagan produk piranti lunak:

Saliyane kabeh sing diterangake ing ndhuwur, menu utama nduweni garis telusuran:

Nalika miwiti proses optimasi indeks:

Sampeyan uga bisa ndeleng log tumindak sing ditindakake ing sisih ngisor jendhela:

Ing jendhela setelan rinci kanggo analisis indeks lan optimasi, sampeyan bisa ngatur opsi sing luwih subtle:

Panjaluk kanggo aplikasi:

1. nggawe bisa nganyari statistik kanthi selektif ora mung kanggo indeks lan uga kanthi cara sing beda-beda (nganyari kanthi lengkap utawa sebagian)
2. nggawe iku bisa ora mung kanggo milih database, nanging uga server beda (iki trep banget nalika ana akeh conto MS SQL Server)
3. Kanggo fleksibilitas sing luwih akeh digunakake, disaranake kanggo mbungkus printah ing perpustakaan lan output menyang printah PowerShell, kaya sing wis rampung, contone, ing kene:
4. dbatools.io/commands
5. nggawe bisa nyimpen lan ngganti setelan pribadhi kanggo kabeh aplikasi lan, yen perlu, kanggo saben conto MS SQL Server lan saben database
6. Saka paragraf 2 lan 4, sampeyan pengin nggawe grup kanthi basis data lan klompok kanthi conto MS SQL Server, sing setelane padha.
7. telusuri indeks duplikat (lengkap lan ora lengkap, sing rada beda utawa mung beda ing kolom sing kalebu)
8. Wiwit SQLIndexManager digunakake mung kanggo MS SQL Server DBMS, iku perlu kanggo nggambarake iki ing jeneng, contone, minangka nderek: SQLIndexManager kanggo MS SQL Server
9. Pindhah kabeh bagean aplikasi non-GUI menyang modul sing kapisah lan tulis maneh ing .NET Core 2.1

Ing wektu nulis, item 6 saka wishes lagi aktif dikembangaké lan wis ana dhukungan ing wangun nggoleki duplikat lengkap lan padha:

Sumber informasi

• metode optimasi indeks adaptif
• Utilitas SQLIndexManager
• ringkesan teknis singkat saka sarana SQLIndexManager
• diskusi babagan sarana SQLIndexManager
• Manajer Indeks SQL - Alat GUI Gratis kanggo Pangopènan Indeks ing SQL Server lan Azure
• Analisis lan Optimasi Indeks SQL Server

Source: www.habr.com