Mapitio ya zana ya bure ya SQLIndexManager

Kama unavyojua, faharisi zina jukumu muhimu katika DBMS, kutoa utaftaji wa haraka kwa rekodi zinazohitajika. Ndiyo maana ni muhimu sana kuwahudumia kwa wakati unaofaa. Nyenzo nyingi zimeandikwa juu ya uchambuzi na uboreshaji, pamoja na kwenye mtandao. Kwa mfano, mada hii ilipitiwa hivi karibuni uchapishaji huu.

Kuna suluhisho nyingi za kulipwa na za bure kwa hili. Kwa mfano, kuna tayari-kufanywa uamuzi, kulingana na mbinu ya utoshelezaji ya faharasa inayobadilika.

Ifuatayo, hebu tuangalie matumizi ya bure Meneja wa SQLIndex, iliyoandikwa na AlanDenton.

Tofauti kuu ya kiufundi kati ya SQLIndexManager na idadi ya analogues zingine hutolewa na mwandishi mwenyewe hapa и hapa.

Katika makala hii, tutaangalia nje mradi na uwezo wa uendeshaji wa ufumbuzi wa programu hii.

Kujadili matumizi haya hapa.
Baada ya muda, maoni na hitilafu nyingi zilirekebishwa.

Kwa hivyo, wacha sasa tuendelee kwenye matumizi ya SQLIndexManager yenyewe.

Programu imeandikwa katika C# .NET Framework 4.5 katika Visual Studio 2017 na hutumia DevExpress kwa fomu:

na inaonekana kama hii:

Maombi yote yanatolewa katika faili zifuatazo:

1. index
2. Query
3. QueryEngine
4. Maelezo ya seva

Wakati wa kuunganisha kwenye hifadhidata na kutuma maswali kwa DBMS, maombi hutiwa saini kama ifuatavyo:

ApplicationName=”SQLIndexManager”

Unapozindua programu, dirisha la modal litafunguliwa ili kuongeza muunganisho:

Hapa, kupakia orodha kamili ya matukio yote ya Seva ya SQL ya MS inayopatikana kupitia mitandao ya ndani bado haifanyi kazi.

Unaweza pia kuongeza muunganisho kwa kutumia kitufe cha kushoto kabisa kwenye menyu kuu:

Ifuatayo, maswali yafuatayo kwa DBMS yatazinduliwa:

1. Kupata habari kuhusu DBMS

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

2. Kupata orodha ya hifadhidata zinazopatikana na mali zao fupi

   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
   

Baada ya kutekeleza hati zilizo hapo juu, dirisha litaonekana likiwa na habari fupi kuhusu hifadhidata za mfano uliochaguliwa wa Seva ya MS SQL:

Ni vyema kutambua kwamba maelezo yaliyopanuliwa yanaonyeshwa kulingana na haki. Ikiwa huko sysadmin, basi unaweza kuchagua data kutoka kwa mtazamo sys.master_files. Ikiwa hakuna haki kama hizo, basi data ndogo inarudishwa tu ili usipunguze ombi.

Hapa unahitaji kuchagua hifadhidata za kupendeza na bonyeza kitufe cha "OK".

Ifuatayo, hati ifuatayo itatekelezwa kwa kila hifadhidata iliyochaguliwa ili kuchanganua hali ya faharasa:

Uchambuzi wa hali ya fahirisi

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

Kama inavyoonekana kutoka kwa maswali yenyewe, meza za muda hutumiwa mara nyingi. Hii imefanywa ili hakuna recompilations, na katika kesi ya mpango mkubwa, mpango unaweza kuzalishwa kwa sambamba wakati wa kuingiza data, kwani kuingiza vigezo vya meza kunawezekana tu kwenye thread moja.

Baada ya kutekeleza hati iliyo hapo juu, dirisha iliyo na jedwali la faharisi itaonekana:

Unaweza pia kuonyesha maelezo mengine ya kina hapa, kama vile:

1. database
2. idadi ya sehemu
3. tarehe na saa ya simu ya mwisho
4. itapunguza
5. kikundi cha faili

na kadhalika.
Spika zenyewe zinaweza kubinafsishwa:

Katika seli za safu wima ya Kurekebisha, unaweza kuchagua ni hatua gani itafanywa wakati wa uboreshaji. Pia, uchanganuzi unapokamilika, kitendo chaguo-msingi huchaguliwa kulingana na mipangilio iliyochaguliwa:

Lazima uchague faharisi zinazohitajika kwa usindikaji.

Kutumia menyu kuu, unaweza kuhifadhi hati (kitufe sawa huanza mchakato wa uboreshaji wa faharisi yenyewe):

na uhifadhi jedwali katika fomati tofauti (kitufe kimoja hukuruhusu kufungua mipangilio ya kina ya kuchambua na kuongeza faharisi):

Unaweza pia kusasisha habari kwa kubofya kitufe cha tatu upande wa kushoto kwenye menyu kuu karibu na glasi ya kukuza.

Kitufe kilicho na kioo cha kukuza kinakuwezesha kuchagua hifadhidata zinazohitajika kwa kuzingatia.

Kwa sasa hakuna mfumo wa usaidizi wa kina. Kwa hivyo, bonyeza kitufe cha "?" itasababisha tu dirisha la modal kuonekana lililo na habari ya msingi kuhusu bidhaa ya programu:

Mbali na kila kitu kilichoelezwa hapo juu, orodha kuu ina bar ya utafutaji:

Wakati wa kuanza mchakato wa uboreshaji wa index:

Unaweza pia kutazama logi ya vitendo vilivyofanywa chini ya dirisha:

Katika kidirisha cha kina cha mipangilio ya uchanganuzi na uboreshaji wa faharisi, unaweza kusanidi chaguo hila zaidi:

Maombi ya maombi:

1. kuwezesha kusasisha takwimu kwa hiari sio tu kwa faharisi na pia kwa njia tofauti (sasisha kikamilifu au kidogo)
2. kufanya hivyo inawezekana si tu kuchagua database, lakini pia seva tofauti (hii ni rahisi sana wakati kuna matukio mengi ya MS SQL Server)
3. Kwa kubadilika zaidi katika utumiaji, inashauriwa kufunga amri kwenye maktaba na kuzitoa kwa amri za PowerShell, kama inavyofanywa, kwa mfano, hapa:
4. dbatools.io/commands
5. kuwezesha kuhifadhi na kubadilisha mipangilio ya kibinafsi kwa programu nzima na, ikiwa ni lazima, kwa kila mfano wa Seva ya MS SQL na kila hifadhidata.
6. Kutoka kwa pointi 2 na 4, inafuata kwamba unataka kuunda vikundi kwa hifadhidata na vikundi kwa hali ya Seva ya MS SQL, ambayo mipangilio yake ni sawa.
7. tafuta nakala rudufu (kamili na haijakamilika, ambazo ni tofauti kidogo au hutofautiana tu katika safu wima zilizojumuishwa)
8. Kwa kuwa SQLIndexManager inatumika tu kwa MS SQL Server DBMS, ni muhimu kuakisi hii katika jina, kwa mfano, kama ifuatavyo: SQLIndexManager kwa MS SQL Server.
9. Sogeza sehemu zote zisizo za GUI za programu katika moduli tofauti na uziandike upya katika .NET Core 2.1

Wakati wa kuandika, kipengee cha 6 cha matakwa kinatengenezwa kikamilifu na tayari kuna msaada katika mfumo wa kutafuta nakala kamili na sawa:

Vyanzo

• njia ya uboreshaji ya faharisi inayobadilika
• SQLIndexManager shirika
• muhtasari mfupi wa kiufundi wa matumizi ya SQLIndexManager
• majadiliano juu ya matumizi ya SQLIndexManager
• Kidhibiti cha Fahirisi cha SQL - Zana ya Bure ya GUI ya Matengenezo ya Fahirisi kwenye Seva ya SQL na Azure
• Uchambuzi na Uboreshaji wa Kielelezo cha Seva ya SQL

Chanzo: mapenzi.com