Sama sa imong nahibal-an, ang mga indeks adunay hinungdanon nga papel sa usa ka DBMS, nga naghatag usa ka dali nga pagpangita sa gikinahanglan nga mga rekord. Mao nga hinungdanon kaayo ang pagserbisyo kanila sa tukma nga panahon. Daghang materyal ang gisulat bahin sa pag-analisar ug pag-optimize, lakip ang Internet. Pananglitan, kini nga hilisgutan bag-o lang gisusi sa .
Adunay daghang bayad ug libre nga mga solusyon alang niini. Pananglitan, adunay usa ka andam , base sa usa ka adaptive index optimization nga pamaagi.
Sunod, atong tan-awon ang libre nga utility , gisulat ni .
Ang panguna nga teknikal nga kalainan tali sa SQLIndexManager ug daghang uban pang mga analogue gihatag sa tagsulat mismo ΠΈ .
Niini nga artikulo, atong tan-awon sa gawas ang proyekto ug ang mga kapabilidad sa operasyon niini nga solusyon sa software.
Paghisgot niini nga utility .
Sa paglabay sa panahon, kadaghanan sa mga komento ug mga bug natul-id.
Busa, magpadayon kita sa SQLIndexManager utility mismo.
Ang aplikasyon gisulat sa C# .NET Framework 4.5 sa Visual Studio 2017 ug naggamit sa DevExpress alang sa mga porma:
ug ingon niini:
Ang tanan nga mga hangyo gihimo sa mosunod nga mga file:
- index
- Pangutana
- QueryEngine
- Impormasyon sa Server
Kung magkonektar sa usa ka database ug magpadala mga pangutana sa DBMS, ang aplikasyon gipirmahan ingon sa mosunod:
ApplicationName=βSQLIndexManagerβ
Kung gilansad nimo ang aplikasyon, usa ka modal window ang maablihan aron makadugang usa ka koneksyon:
Dinhi, ang pag-load sa usa ka kompleto nga lista sa tanan nga mga kaso sa MS SQL Server nga ma-access sa mga lokal nga network dili pa molihok.
Mahimo ka usab makadugang usa ka koneksyon gamit ang pinakawala nga buton sa main menu:
Sunod, ang mosunod nga mga pangutana sa DBMS ilunsad:
- Pagkuha og impormasyon bahin sa DBMS
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Pagkuha usa ka lista sa magamit nga mga database nga adunay ilang mubu nga mga kabtangan
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
Human sa pagpatuman sa mga script sa ibabaw, usa ka bintana ang makita nga adunay mubu nga impormasyon mahitungod sa mga database sa pinili nga pananglitan sa MS SQL Server:
Angay nga hinumdoman nga ang gipalapdan nga kasayuran gipakita base sa mga katungod. Kung naa , unya makapili ka ug data gikan sa view . Kung walaβy ingon nga mga katungod, nan gamay ra nga datos ang gibalik aron dili mapahinay ang hangyo.
Dinhi kinahanglan nimo nga pilion ang mga database sa interes ug i-klik ang "OK" nga buton.
Sunod, ang mosunod nga script ipatuman alang sa matag pinili nga database aron pag-analisar sa kahimtang sa mga indeks:
Pagtuki sa kahimtang sa 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)
)
Sama sa makita gikan sa mga pangutana sa ilang kaugalingon, ang mga temporaryo nga lamesa gigamit kanunay. Gihimo kini aron wala'y mga recompilations, ug sa kaso sa usa ka dako nga laraw, ang plano mahimong mamugna nga managsama kung magsal-ot sa datos, tungod kay ang pagsal-ot sa mga variable sa lamesa posible lamang sa usa ka hilo.
Human ipatuman ang script sa ibabaw, usa ka bintana nga adunay index table ang makita:
Mahimo usab nimo ipakita ang ubang detalyado nga kasayuran dinhi, sama sa:
- database
- gidaghanon sa mga seksyon
- petsa ug oras sa katapusang tawag
- kompresiyon
- filegroup
i t. d.
Ang mga mamumulong mismo mahimong ipasadya:
Sa mga selula sa kolum sa Pag-ayo, mahimo nimong pilion kung unsang aksyon ang himuon sa panahon sa pag-optimize. Usab, kung makompleto ang pag-scan, usa ka default nga aksyon ang gipili base sa gipili nga mga setting:
Kinahanglan nimong pilion ang gusto nga mga indeks alang sa pagproseso.
Gamit ang main menu, mahimo nimong i-save ang script (ang parehas nga buton nagsugod sa proseso sa pag-optimize sa index mismo):
ug i-save ang lamesa sa lainlaing mga format (ang parehas nga buton nagtugot kanimo sa pag-abli sa mga detalyado nga setting alang sa pag-analisar ug pag-optimize sa mga indeks):
Mahimo usab nimo nga i-update ang impormasyon pinaagi sa pag-klik sa ikatulo nga buton sa wala sa main menu sunod sa magnifying glass.
Ang buton nga adunay magnifying glass nagtugot kanimo sa pagpili sa gitinguha nga mga database alang sa konsiderasyon.
Sa pagkakaron walay komprehensibong sistema sa pagtabang. Busa, pagpindot sa β?β buton magpahinabo lang nga magpakita ang usa ka modal window nga adunay sukaranan nga kasayuran bahin sa produkto sa software:
Dugang sa tanan nga gihulagway sa ibabaw, ang main menu adunay usa ka search bar:
Sa pagsugod sa proseso sa pag-optimize sa indeks:
Mahimo usab nimo tan-awon ang usa ka log sa gihimo nga mga aksyon sa ilawom sa bintana:
Sa detalyado nga mga setting nga bintana alang sa pagtuki sa indeks ug pag-optimize, mahimo nimong i-configure ang labi ka maliputon nga mga kapilian:
Mga hangyo alang sa aplikasyon:
- himoong posible nga pilion ang pag-update sa mga estadistika dili lamang alang sa mga indeks ug usab sa lain-laing mga paagi (hingpit nga pag-update o partially)
- paghimo nga posible dili lamang sa pagpili sa usa ka database, apan usab sa lain-laing mga server (kini mao ang kaayo sayon ββββsa diha nga adunay daghang mga higayon sa MS SQL Server)
- Para sa mas dako nga pagka-flexible sa paggamit, gisugyot nga ibalot ang mga sugo sa mga librarya ug i-output kini sa mga sugo sa PowerShell, sama sa gibuhat, pananglitan, dinhi:
- himoong posible ang pagtipig ug pagbag-o sa personal nga mga setting alang sa tibuok nga aplikasyon ug, kon gikinahanglan, alang sa matag pananglitan sa MS SQL Server ug sa matag database
- Gikan sa mga punto 2 ug 4, kini nagsunod nga gusto nimo nga maghimo mga grupo pinaagi sa mga database ug mga grupo pinaagi sa MS SQL Server nga mga higayon, diin ang mga setting parehas
- pangitaa ang mga duplicate nga indeks (kompleto ug dili kompleto, nga gamay ra ang kalainan o lahi ra sa gilakip nga mga kolum)
- Tungod kay ang SQLIndexManager gigamit lamang alang sa MS SQL Server DBMS, gikinahanglan nga ipakita kini sa ngalan, pananglitan, sama sa mosunod: SQLIndexManager alang sa MS SQL Server
- Ibalhin ang tanan nga dili GUI nga mga bahin sa aplikasyon ngadto sa lain nga mga module ug isulat kini pag-usab sa .NET Core 2.1
Sa panahon sa pagsulat, ang aytem 6 sa mga pangandoy aktibong gipalambo ug aduna nay suporta sa porma sa pagpangita sa kompleto ug susama nga mga duplicate:
Mga tinubdan
Source: www.habr.com