Tulad ng alam mo, ang mga index ay may mahalagang papel sa isang DBMS, na nagbibigay ng mabilis na paghahanap sa mga kinakailangang tala. Kaya naman napakahalaga na paglingkuran sila sa isang napapanahong paraan. Napakaraming materyal ang naisulat tungkol sa pagsusuri at pag-optimize, kabilang ang sa Internet. Halimbawa, ang paksang ito ay nasuri kamakailan sa .
Maraming bayad at libreng solusyon para dito. Halimbawa, mayroong isang handa na , batay sa paraan ng adaptive index optimization.
Susunod, tingnan natin ang libreng utility , isinulat ni .
Ang pangunahing teknikal na pagkakaiba sa pagitan ng SQLIndexManager at isang bilang ng iba pang mga analogue ay ibinigay ng may-akda mismo ΠΈ .
Sa artikulong ito, titingnan natin sa labas ang proyekto at ang mga kakayahan sa pagpapatakbo ng solusyon sa software na ito.
Tinatalakay ang utility na ito .
Sa paglipas ng panahon, karamihan sa mga komento at mga bug ay naitama.
Kaya, lumipat tayo ngayon sa SQLIndexManager utility mismo.
Ang application ay nakasulat sa C# .NET Framework 4.5 sa Visual Studio 2017 at gumagamit ng DevExpress para sa mga form:
at ganito ang hitsura:
Ang lahat ng mga kahilingan ay nabuo sa mga sumusunod na file:
- Index
- Tanong
- QueryEngine
- ServerInfo
Kapag kumokonekta sa isang database at nagpapadala ng mga query sa DBMS, ang application ay nilagdaan bilang mga sumusunod:
ApplicationName=βSQLIndexManagerβ
Kapag inilunsad mo ang application, magbubukas ang isang modal window upang magdagdag ng koneksyon:
Dito, hindi pa gumagana ang paglo-load ng kumpletong listahan ng lahat ng mga instance ng MS SQL Server na naa-access sa mga lokal na network.
Maaari ka ring magdagdag ng koneksyon gamit ang pinakakaliwang pindutan sa pangunahing menu:
Susunod, ang mga sumusunod na query sa DBMS ay ilulunsad:
- Pagkuha ng impormasyon tungkol sa DBMS
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Pagkuha ng isang listahan ng mga magagamit na database kasama ang kanilang mga maikling katangian
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
Pagkatapos isagawa ang mga script sa itaas, lalabas ang isang window na naglalaman ng maikling impormasyon tungkol sa mga database ng napiling instance ng MS SQL Server:
Ito ay nagkakahalaga ng pagpuna na ang pinalawig na impormasyon ay ipinapakita batay sa mga karapatan. Kung meron , pagkatapos ay maaari kang pumili ng data mula sa view . Kung walang ganoong mga karapatan, kung gayon mas kaunting data ang ibinalik lamang upang hindi mapabagal ang kahilingan.
Dito kailangan mong piliin ang mga database ng interes at mag-click sa pindutang "OK".
Susunod, ang sumusunod na script ay isasagawa para sa bawat napiling database upang pag-aralan ang estado ng mga index:
Pagsusuri ng katayuan ng index
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)
)
Tulad ng makikita mula sa mga query mismo, ang mga pansamantalang talahanayan ay madalas na ginagamit. Ginagawa ito upang walang mga recompilations, at sa kaso ng isang malaking scheme, ang plano ay maaaring mabuo nang magkatulad kapag nagpasok ng data, dahil ang pagpasok ng mga variable ng talahanayan ay posible lamang sa isang thread.
Pagkatapos isagawa ang script sa itaas, lalabas ang isang window na may index table:
Maaari ka ring magpakita ng iba pang detalyadong impormasyon dito, gaya ng:
- database
- bilang ng mga seksyon
- petsa at oras ng huling tawag
- pisilin
- filegroup
at t. d.
Ang mga speaker mismo ay maaaring ipasadya:
Sa mga cell ng column na Ayusin, maaari mong piliin kung anong aksyon ang isasagawa sa panahon ng pag-optimize. Gayundin, kapag nakumpleto ang pag-scan, pipiliin ang isang default na pagkilos batay sa mga napiling setting:
Dapat mong piliin ang nais na mga index para sa pagproseso.
Gamit ang pangunahing menu, maaari mong i-save ang script (ang parehong pindutan ay nagsisimula sa proseso ng pag-optimize ng index mismo):
at i-save ang talahanayan sa iba't ibang mga format (ang parehong pindutan ay nagbibigay-daan sa iyo upang buksan ang mga detalyadong setting para sa pagsusuri at pag-optimize ng mga index):
Maaari mo ring i-update ang impormasyon sa pamamagitan ng pag-click sa ikatlong button sa kaliwa sa pangunahing menu sa tabi ng magnifying glass.
Ang button na may magnifying glass ay nagbibigay-daan sa iyong piliin ang nais na mga database para sa pagsasaalang-alang.
Kasalukuyang walang komprehensibong sistema ng tulong. Samakatuwid, pagpindot sa "?" na buton ay magiging sanhi lamang ng isang modal window na lumitaw na naglalaman ng pangunahing impormasyon tungkol sa produkto ng software:
Bilang karagdagan sa lahat ng inilarawan sa itaas, ang pangunahing menu ay may search bar:
Kapag sinimulan ang proseso ng pag-optimize ng index:
Maaari mo ring tingnan ang isang log ng mga ginawang aksyon sa ibaba ng window:
Sa window ng detalyadong mga setting para sa pagsusuri at pag-optimize ng index, maaari mong i-configure ang mas banayad na mga opsyon:
Mga kahilingan para sa aplikasyon:
- gawing posible na piliing i-update ang mga istatistika hindi lamang para sa mga index at gayundin sa iba't ibang paraan (ganap na i-update o bahagyang)
- gawin itong posible hindi lamang upang pumili ng isang database, kundi pati na rin ang iba't ibang mga server (ito ay napaka-maginhawa kapag mayroong maraming mga pagkakataon ng MS SQL Server)
- Para sa higit na kakayahang umangkop sa paggamit, iminumungkahi na balutin ang mga utos sa mga aklatan at i-output ang mga ito sa mga utos ng PowerShell, tulad ng ginagawa, halimbawa, dito:
- gawing posible na i-save at baguhin ang mga personal na setting para sa buong application at, kung kinakailangan, para sa bawat halimbawa ng MS SQL Server at bawat database
- Mula sa mga punto 2 at 4, sumusunod na gusto mong lumikha ng mga grupo ayon sa mga database at mga grupo ayon sa mga instance ng MS SQL Server, kung saan pareho ang mga setting
- maghanap ng mga duplicate na index (kumpleto at hindi kumpleto, na maaaring bahagyang naiiba o naiiba lamang sa mga kasamang column)
- Dahil ang SQLIndexManager ay ginagamit lamang para sa MS SQL Server DBMS, ito ay kinakailangan upang ipakita ito sa pangalan, halimbawa, tulad ng sumusunod: SQLIndexManager para sa MS SQL Server
- Ilipat ang lahat ng hindi GUI na bahagi ng application sa magkahiwalay na mga module at muling isulat ang mga ito sa .NET Core 2.1
Sa oras ng pagsulat, ang item 6 ng mga kagustuhan ay aktibong binuo at mayroon nang suporta sa anyo ng paghahanap ng kumpleto at katulad na mga duplicate:
pinagmumulan
Pinagmulan: www.habr.com