Joalokaha u tseba, li-index li phetha karolo ea bohlokoa ho DBMS, ho fana ka lipatlisiso tse potlakileng ho lirekoto tse hlokahalang. Ke ka lebaka leo ho leng bohlokoa haholo ho ba sebeletsa ka nako e loketseng. Ho ngotsoe litaba tse ngata mabapi le tlhahlobo le ntlafatso, ho kenyeletsoa le Marang-rang. Ka mohlala, taba ena e sa tsoa hlahlojoa ho .
Ho na le litharollo tse ngata tse lefuoang le tsa mahala bakeng sa sena. Ka mohlala, ho na le e itokiselitse entsoeng , e ipapisitseng le mokhoa oa ho ntlafatsa indexing.
Ka mor'a moo, a re shebeng tšebeliso ea mahala , e ngotsoe ke .
Phapang e kholo ea tekheniki lipakeng tsa SQLIndexManager le li-analogue tse ling tse 'maloa e fanoa ke mongoli ka boeena и .
Sehloohong sena, re tla sheba ka ntle morero le bokhoni ba ts'ebetso ea tharollo ea software ena.
Ho buisana ka sesebelisoa sena .
Ha nako e ntse e ea, boholo ba maikutlo le liphoso li ile tsa lokisoa.
Kahoo, ha re feteleng joale ho ts'ebeliso ea SQLIndexManager ka boeona.
Kopo e ngotsoe ho C# .NET Framework 4.5 ho Visual Studio 2017 mme e sebelisa DevExpress bakeng sa liforomo:
'me e shebahala tjena:
Likopo tsohle li hlahisoa ka lifaele tse latelang:
- Index
- Potso
- QueryEngine
- SevaInfo
Ha o hokela ho database le ho romella lipotso ho DBMS, kopo e saenoa ka tsela e latelang:
ApplicationName=”SQLIndexManager”
Ha o qala ts'ebeliso, fensetere ea modal e tla buleha ho eketsa khokahano:
Mona, ho kenya lenane le felletseng la maemo ohle a MS SQL Server a fumanehang marang-rang a lehae ha ho so sebetse.
U ka boela ua kenya khokahano u sebelisa konopo e ka ho le letšehali ho menu e kholo:
Ka mor'a moo, lipotso tse latelang ho DBMS li tla hlahisoa:
- Ho fumana lintlha ka DBMS
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Ho fumana lethathamo la li-database tse fumanehang ka thepa ea tsona e khuts'oane
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
Ka mor'a hore u kenye mangolo a ka holimo, fensetere e tla hlaha e nang le boitsebiso bo bokhutšoaane mabapi le li-database tsa mohlala o khethiloeng oa MS SQL Server:
Ke habohlokoa ho hlokomela hore boitsebiso bo atolositsoeng bo bontšoa ho latela litokelo. Haeba moo , joale o ka khetha data ho tsoa ponong . Haeba ho se na litokelo tse joalo, joale data e fokolang e khutlisetsoa feela e le hore e se ke ea fokotsa kopo.
Mona o hloka ho khetha li-database tseo o li ratang ebe o tobetsa konopo ea "OK".
Ka mor'a moo, mongolo o latelang o tla etsoa bakeng sa database e 'ngoe le e' ngoe e khethiloeng ho hlahloba boemo ba li-index:
Tlhahlobo ea boemo ba 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)
)
Joalokaha ho ka bonoa ho tsoa lipotsong ka botsona, litafole tsa nakoana li sebelisoa hangata. Sena se etsoa e le hore ho se ke ha e-ba le li-recompilations, 'me tabeng ea morero o moholo, moralo o ka hlahisoa ka mokhoa o ts'oanang ha o kenya data, kaha ho kenya mefuta e fapaneng ea tafole ho khoneha feela ka khoele e le' ngoe.
Kamora ho kenya mongolo o kaholimo, fensetere e nang le tafole ea index e tla hlaha:
U ka boela ua hlahisa lintlha tse ling mona, tse kang:
- database
- palo ea likarolo
- letsatsi le nako ea mohala oa ho qetela
- khatello
- sehlopha sa lifaele
joalo-joalo.
Libui ka botsona li ka hlophisoa:
Liseleng tsa kholomo ea Fix, u ka khetha hore na ke ketso efe e tla etsoa nakong ea ntlafatso. Hape, ha scan ninete e phethela, ho khethoa ketso ea kamehla ho latela litlhophiso tse khethiloeng:
U tlameha ho khetha li-index tse lakatsehang bakeng sa ho sebetsa.
U sebelisa menyu ea mantlha, u ka boloka sengoloa (konopo e ts'oanang e qala ts'ebetso ea optimization ea index ka boeona):
'me u boloke tafole ka liforomo tse fapaneng (konopo e tšoanang e u lumella ho bula litlhophiso tse qaqileng bakeng sa ho sekaseka le ho ntlafatsa li-index):
U ka boela ua ntlafatsa tlhahisoleseding ka ho tobetsa konopo ea boraro ka ho le letšehali ho menu e kholo e haufi le khalase e hōlisang lintho.
Konopo e nang le khalase e holisang e u lumella ho khetha li-database tse lakatsehang bakeng sa ho nahaneloa.
Hajoale ha ho na sistimi ea thuso e felletseng. Ka hona, tobetsa konopo ea "?" e tla etsa hore ho hlahe fensetere ea modal e nang le tlhaiso-leseling ea mantlha mabapi le sehlahisoa sa software:
Ntle le ntho e 'ngoe le e' ngoe e hlalositsoeng ka holimo, menu e kholo e na le sebaka sa ho batla:
Ha o qala ts'ebetso ea optimization ea index:
U ka boela ua sheba lethathamo la liketso tse entsoeng ka tlase ho fensetere:
Ka fensetere e qaqileng ea litlhophiso bakeng sa tlhahlobo ea index le optimization, o ka hlophisa likhetho tse poteletseng haholoanyane:
Likopo tsa kopo:
- etsa hore ho khonehe ho nchafatsa lipalo-palo eseng feela bakeng sa li-index le ka mekhoa e fapaneng (ntlafatsa ka botlalo kapa ka mokhoa o sa fellang)
- etsa hore ho khonehe eseng feela ho khetha database, empa le li-server tse fapaneng (sena se bonolo haholo ha ho na le maemo a mangata a MS SQL Server)
- Bakeng sa maemo a bonolo a ts'ebeliso, ho khothaletsoa ho phuthela litaelo lilaeboraring le ho li hlahisa ho litaelo tsa PowerShell, joalo ka ha ho etsoa, mohlala, mona:
- etsa hore ho khonehe ho boloka le ho fetola litlhophiso tsa hau bakeng sa ts'ebeliso eohle, 'me, ha ho hlokahala, bakeng sa mohlala o mong le o mong oa MS SQL Server le database ka 'ngoe.
- Ho tloha lirapeng tsa 2 le 4 ho latela hore u batla ho theha lihlopha ka li-database le lihlopha ka maemo a MS SQL Server, tseo litlhophiso tsa tsona li tšoanang.
- batla li-index tse qotsitsoeng (tse felletseng le tse sa fellang, tse fapaneng hanyane kapa tse fapaneng feela likholomong tse kenyellelitsoeng)
- Kaha SQLIndexManager e sebelisoa feela bakeng sa MS SQL Server DBMS, hoa hlokahala ho bonahatsa sena ka lebitso, mohlala, ka tsela e latelang: SQLIndexManager bakeng sa MS SQL Server.
- Tsamaisa likarolo tsohle tseo e seng tsa GUI tsa ts'ebeliso ho li-module tse arohaneng 'me u li ngole hape ho .NET Core 2.1
Nakong ea ho ngola, ntlha ea 6 ea litakatso e ntse e ntlafatsoa ka mafolofolo 'me ho se ho ntse ho e-na le tšehetso ka mokhoa oa ho batla likopi tse feletseng le tse tšoanang:
Mohloli
Source: www.habr.com