SQLIndexManager bepul vositasini ko'rib chiqish

Ma'lumki, indekslar ma'lumotlar bazasida muhim rol o'ynaydi va kerakli yozuvlarni tezkor qidirishni ta'minlaydi. Shuning uchun ularga o'z vaqtida xizmat ko'rsatish juda muhimdir. Tahlil va optimallashtirish haqida juda ko'p materiallar, shu jumladan Internetda yozilgan. Masalan, bu mavzu yaqinda ko'rib chiqildi Ushbu nashr.

Buning uchun ko'plab pullik va bepul echimlar mavjud. Misol uchun, tayyor mavjud qaror, moslashuvchan indeksni optimallashtirish usuliga asoslangan.

Keyinchalik, bepul yordam dasturini ko'rib chiqaylik SQLIndexManager, muallifi AlanDenton.

SQLIndexManager va boshqa bir qator analoglar o'rtasidagi asosiy texnik farq muallifning o'zi tomonidan berilgan shu yerda и shu yerda.

Ushbu maqolada biz loyihani va ushbu dasturiy yechimning operatsion imkoniyatlarini tashqi tomondan ko'rib chiqamiz.

Ushbu yordam dasturini muhokama qilish shu yerda.
Vaqt o'tishi bilan ko'pchilik sharhlar va xatolar tuzatildi.

Shunday qilib, endi SQLIndexManager yordam dasturining o'ziga o'tamiz.

Ilova Visual Studio 4.5 da C# .NET Framework 2017 da yozilgan va shakllar uchun DevExpress dan foydalanadi:

va shunday ko'rinadi:

Barcha so'rovlar quyidagi fayllarda yaratiladi:

1. indeks
2. So'rov
3. QueryEngine
4. ServerInfo

Ma'lumotlar bazasiga ulanish va ma'lumotlar bazasiga so'rov yuborishda dastur quyidagicha imzolanadi:

ApplicationName=”SQLIndexManager”

Ilovani ishga tushirganingizda, ulanishni qo'shish uchun modal oyna ochiladi:

Bu erda mahalliy tarmoqlar orqali kirish mumkin bo'lgan barcha MS SQL Server nusxalarining to'liq ro'yxatini yuklash hali ishlamaydi.

Shuningdek, siz asosiy menyuning eng chap tugmasi yordamida ulanishni qo'shishingiz mumkin:

Keyinchalik, ma'lumotlar bazasiga quyidagi so'rovlar ishga tushiriladi:

1. DBMS haqida ma'lumot olish

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

2. Mavjud ma'lumotlar bazalari ro'yxatini ularning qisqacha xususiyatlari bilan olish

   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
   

Yuqoridagi skriptlarni bajargandan so'ng, MS SQL Serverning tanlangan nusxasining ma'lumotlar bazalari haqida qisqacha ma'lumotni o'z ichiga olgan oyna paydo bo'ladi:

Shuni ta'kidlash kerakki, kengaytirilgan ma'lumotlar huquqlar asosida ko'rsatiladi. Agar bo'lsa tizim boshqaruvchisi, keyin siz ko'rinishdan ma'lumotlarni tanlashingiz mumkin sys.master_files. Agar bunday huquqlar bo'lmasa, so'rovni sekinlashtirmaslik uchun kamroq ma'lumotlar qaytariladi.

Bu erda siz qiziqqan ma'lumotlar bazalarini tanlashingiz va "OK" tugmasini bosishingiz kerak.

Keyinchalik, indekslar holatini tahlil qilish uchun har bir tanlangan ma'lumotlar bazasi uchun quyidagi skript bajariladi:

Indeks holatini tahlil qilish

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

So'rovlarning o'zidan ko'rinib turibdiki, vaqtinchalik jadvallar juda tez-tez ishlatiladi. Bu qayta kompilyatsiya bo'lmasligi uchun amalga oshiriladi va katta sxema bo'lsa, ma'lumotlarni kiritishda reja parallel ravishda yaratilishi mumkin, chunki jadval o'zgaruvchilarini kiritish faqat bitta ipda mumkin.

Yuqoridagi skriptni bajargandan so'ng, indeks jadvali bo'lgan oyna paydo bo'ladi:

Bu yerda siz boshqa batafsil ma'lumotlarni ham ko'rsatishingiz mumkin, masalan:

1. malumotlar bazasi
2. bo'limlar soni
3. oxirgi qo'ng'iroq sanasi va vaqti
4. qisqarishi
5. fayllar guruhi

va hokazo.
Karnaylarning o'zlari moslashtirilishi mumkin:

Tuzatish ustunining kataklarida siz optimallashtirish vaqtida qanday harakat bajarilishini tanlashingiz mumkin. Shuningdek, skanerlash tugagach, tanlangan sozlamalar asosida standart amal tanlanadi:

Qayta ishlash uchun kerakli indekslarni tanlashingiz kerak.

Asosiy menyudan foydalanib, siz skriptni saqlashingiz mumkin (xuddi shu tugma indeksni optimallashtirish jarayonini o'zi boshlaydi):

va jadvalni turli formatlarda saqlang (xuddi shu tugma indekslarni tahlil qilish va optimallashtirish uchun batafsil sozlamalarni ochishga imkon beradi):

Bundan tashqari, kattalashtirish oynasi yonidagi asosiy menyuning chap tomonidagi uchinchi tugmani bosish orqali ma'lumotni yangilashingiz mumkin.

Kattalashtiruvchi oynali tugma ko'rib chiqish uchun kerakli ma'lumotlar bazalarini tanlash imkonini beradi.

Hozirda keng qamrovli yordam tizimi mavjud emas. Shuning uchun, "?" tugmasini bosing oddiygina dasturiy mahsulot haqida asosiy ma'lumotlarni o'z ichiga olgan modal oyna paydo bo'lishiga olib keladi:

Yuqorida tavsiflangan barcha narsalarga qo'shimcha ravishda, asosiy menyuda qidiruv paneli mavjud:

Indeksni optimallashtirish jarayonini boshlashda:

Shuningdek, oynaning pastki qismida bajarilgan harakatlar jurnalini ko'rishingiz mumkin:

Indekslarni tahlil qilish va optimallashtirish uchun batafsil sozlamalar oynasida siz nozikroq variantlarni sozlashingiz mumkin:

Ariza uchun so'rovlar:

1. statistik ma'lumotlarni nafaqat indekslar uchun, balki turli yo'llar bilan (to'liq yoki qisman yangilash) tanlab yangilash imkonini beradi.
2. nafaqat ma'lumotlar bazasini, balki turli xil serverlarni ham tanlashga imkon bering (bu MS SQL Serverning ko'p nusxalari mavjud bo'lganda juda qulaydir)
3. Foydalanishda ko'proq moslashuvchanlik uchun buyruqlarni kutubxonalarga o'rash va ularni PowerShell buyruqlariga chiqarish tavsiya etiladi, masalan, bu erda:
4. dbatools.io/commands
5. butun dastur uchun ham, kerak bo'lsa, MS SQL Serverning har bir nusxasi va har bir ma'lumotlar bazasi uchun shaxsiy sozlamalarni saqlash va o'zgartirish imkonini beradi.
6. 2 va 4-bandlardan kelib chiqadiki, siz ma'lumotlar bazalari bo'yicha guruhlar va MS SQL Server misollari bo'yicha guruhlar yaratmoqchisiz, ular uchun sozlamalar bir xil bo'ladi.
7. ikki nusxadagi indekslarni qidirish (to'liq va to'liq bo'lmagan, ular bir oz farq qiladi yoki faqat kiritilgan ustunlarda farqlanadi)
8. SQLIndexManager faqat MS SQL Server DBMS uchun ishlatilganligi sababli, buni nomda aks ettirish kerak, masalan: MS SQL Server uchun SQLIndexManager
9. Ilovaning barcha GUI bo'lmagan qismlarini alohida modullarga ko'chiring va ularni .NET Core 2.1 da qayta yozing.

Yozish vaqtida istaklarning 6-bandi faol ishlab chiqilmoqda va to'liq va shunga o'xshash dublikatlarni qidirish shaklida allaqachon yordam mavjud:

Axborot manbalari

• adaptiv indeksni optimallashtirish usuli
• SQLIndexManager yordam dasturi
• SQLIndexManager yordam dasturining qisqacha texnik sharhi
• SQLIndexManager yordam dasturi bo'yicha muhokama
• SQL Index Manager - SQL Server va Azure-da indekslarni saqlash uchun bepul GUI vositasi
• SQL Server indekslarini tahlil qilish va optimallashtirish

Manba: www.habr.com