Mar a tha fios agad, tha àite cudromach aig clàran-amais ann an DBMS, a’ toirt seachad sgrùdadh sgiobalta air na clàran a tha a dhìth. Sin as coireach gu bheil e cho cudromach seirbheis a thoirt dhaibh ann an deagh àm. Chaidh tòrr stuth a sgrìobhadh mu mhion-sgrùdadh agus optimization, a’ toirt a-steach air an eadar-lìn. Mar eisimpleir, chaidh an cuspair seo ath-sgrùdadh o chionn ghoirid ann an .
Tha mòran fhuasglaidhean pàighte agus an-asgaidh airson seo. Mar eisimpleir, tha deiseil deiseil , stèidhichte air modh optimization clàr-amais atharrachail.
An ath rud, leig dhuinn sùil a thoirt air an goireas an-asgaidh , air a sgrìobhadh le .
Tha am prìomh eadar-dhealachadh teicnigeach eadar SQLIndexManager agus grunn analogues eile air a thoirt seachad leis an ùghdar fhèin и .
San artaigil seo, bheir sinn sùil bhon taobh a-muigh air a’ phròiseact agus comasan obrachaidh an fhuasglaidh bathar-bog seo.
A 'bruidhinn air a' ghoireas seo .
Thar ùine, chaidh a’ mhòr-chuid de na beachdan agus na mialan a cheartachadh.
Mar sin, gluaisidh sinn air adhart gu goireas SQLIndexManager fhèin.
Tha an tagradh sgrìobhte ann an C # .NET Framework 4.5 ann an Visual Studio 2017 agus a’ cleachdadh DevExpress airson foirmean:
agus tha coltas mar seo:
Tha a h-uile iarrtas air a chruthachadh anns na faidhlichean a leanas:
- Clàr-innse
- Ceist
- Inneal-ceiste
- Fiosrachadh frithealaiche
Nuair a bhios tu a’ ceangal ri stòr-dàta agus a’ cur cheistean chun DBMS, tha an t-iarrtas air a shoidhnigeadh mar a leanas:
ApplicationName=”SQLIndexManager”
Nuair a chuireas tu an tagradh air bhog, fosglaidh uinneag modal gus ceangal a chuir ris:
An seo, chan obraich fhathast liosta iomlan de na h-eisimpleirean MS SQL Server a tha ruigsinneach thairis air lìonraidhean ionadail.
Faodaidh tu cuideachd ceangal a chuir ris a’ cleachdadh a’ phutan air an taobh chlì air a’ phrìomh chlàr:
An ath rud, thèid na ceistean a leanas chun DBMS a chuir air bhog:
- A’ faighinn fiosrachadh mun DBMS
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - A’ faighinn liosta de stòran-dàta a tha rim faighinn leis na feartan goirid aca
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
Às deidh na sgriobtaichean gu h-àrd a chuir an gnìomh, nochdaidh uinneag anns a bheil fiosrachadh goirid mu na stòran-dàta den eisimpleir taghte de MS SQL Server:
Is fhiach a bhith mothachail gu bheil fiosrachadh leudaichte air a thaisbeanadh stèidhichte air còraichean. Ma tha , an uairsin faodaidh tu dàta a thaghadh bhon t-sealladh . Mura h-eil còraichean mar sin ann, thèid nas lugha de dhàta a thilleadh dìreach gus nach cuir iad maill air an iarrtas.
An seo feumaidh tu na stòran-dàta inntinneach a thaghadh agus cliog air a’ phutan “OK”.
An uairsin, thèid an sgriobt a leanas a chuir gu bàs airson gach stòr-dàta taghte gus sgrùdadh a dhèanamh air staid nan clàran-amais:
Mion-sgrùdadh inbhe clàr-amais
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)
)
Mar a chithear bho na ceistean fhèin, bidh bùird sealach air an cleachdadh gu math tric. Tha seo air a dhèanamh gus nach bi ath-chuairteachadh ann, agus a thaobh sgeama mòr, faodar am plana a chruthachadh aig an aon àm nuair a chuireas tu a-steach dàta, leis gu bheil e comasach caochladairean bùird a chuir a-steach ach ann an aon snàithlean.
Às deidh an sgriobt gu h-àrd a chuir an gnìomh, nochdaidh uinneag le clàr clàr-amais:
Faodaidh tu cuideachd fiosrachadh mionaideach eile a thaisbeanadh an seo, leithid:
- база данных
- àireamh earrannan
- ceann-latha agus uair a’ ghairm mu dheireadh
- fàileadh
- buidheann fhaidhlichean
i. d.
Faodar na luchd-labhairt fhèin a ghnàthachadh:
Ann an ceallan a’ cholbh Fix, faodaidh tu taghadh dè an gnìomh a thèid a dhèanamh rè optimization. Cuideachd, nuair a bhios an scan deiseil, thèid gnìomh bunaiteach a thaghadh stèidhichte air na roghainnean taghte:
Feumaidh tu na clàran-amais a tha thu ag iarraidh a thaghadh airson a ghiullachd.
A’ cleachdadh a’ phrìomh chlàr, faodaidh tu an sgriobt a shàbhaladh (bidh an aon phutan a’ tòiseachadh a’ phròiseas optimization clàr-amais fhèin):
agus sàbhail am bòrd ann an diofar chruthan (Leigidh an aon phutan leat roghainnean mionaideach fhosgladh airson clàran-amais a mhion-sgrùdadh agus a bharrachadh):
Faodaidh tu cuideachd am fiosrachadh ùrachadh le bhith a’ briogadh air an treas putan air an taobh chlì sa phrìomh chlàr ri taobh a’ ghlainne-mheudachaidh.
Leigidh am putan le glainne meudachaidh dhut na stòran-dàta a tha thu ag iarraidh a thaghadh airson beachdachadh.
Chan eil siostam cuideachaidh coileanta ann an-dràsta. Mar sin, brùth air a’ phutan “?” bheir e dìreach air uinneag modal nochdadh anns a bheil fiosrachadh bunaiteach mun toradh bathar-bog:
A bharrachd air a h-uile dad a chaidh a mhìneachadh gu h-àrd, tha bàr sgrùdaidh aig a’ phrìomh chlàr:
Nuair a thòisicheas tu air a’ phròiseas optimization clàr-amais:
Chì thu cuideachd log de ghnìomhan dèante aig bonn na h-uinneige:
Anns an uinneag roghainnean mionaideach airson mion-sgrùdadh clàr-amais agus optimization, faodaidh tu roghainnean nas seòlta a rèiteachadh:
Iarrtasan airson an tagraidh:
- ga dhèanamh comasach staitistig ùrachadh gu roghnach chan ann a-mhàin airson clàran-amais agus cuideachd ann an diofar dhòighean (ùrachadh gu h-iomlan no ann am pàirt)
- dèan e comasach chan ann a-mhàin stòr-dàta a thaghadh, ach cuideachd frithealaichean eadar-dhealaichte (tha seo gu math goireasach nuair a tha mòran eisimpleirean ann de MS SQL Server)
- Airson barrachd sùbailteachd ann an cleachdadh, thathas a’ moladh na h-òrdughan a phasgadh ann an leabharlannan agus an cur a-mach gu òrdughan PowerShell, mar a tha air a dhèanamh, mar eisimpleir, an seo:
- dèan e comasach roghainnean pearsanta a shàbhaladh agus atharrachadh an dà chuid airson an aplacaid gu lèir agus, ma tha sin riatanach, airson gach eisimpleir de MS SQL Server agus gach stòr-dàta
- Bho phuingean 2 agus 4, tha e a’ leantainn gu bheil thu airson buidhnean a chruthachadh a rèir stòran-dàta agus buidhnean a rèir eisimpleirean MS SQL Server, far a bheil na roghainnean mar an ceudna
- lorg clàran-amais dùblaichte (coileanta agus neo-choileanta, a tha beagan eadar-dhealaichte no eadar-dhealaichte a-mhàin anns na colbhan a tha air an toirt a-steach)
- Leis gu bheil SQLIndexManager air a chleachdadh airson MS SQL Server DBMS a-mhàin, feumar seo a nochdadh san ainm, mar eisimpleir, mar a leanas: SQLIndexManager airson MS SQL Server
- Gluais a h-uile pàirt neo-GUI den tagradh gu modalan fa leth agus ath-sgrìobh iad ann an .NET Core 2.1
Aig àm sgrìobhaidh, thathas a’ leasachadh cuspair 6 de na miannan gu gnìomhach agus tha taic ann mar-thà ann a bhith a’ lorg dùblaidhean coileanta agus coltach ris:
Stòran
Source: www.habr.com