Te arotake o te taputapu koreutu SQLIndexManager

E mohio ana koe, he waahi nui nga tohu tohu i roto i te DBMS, he rapu tere ki nga rekoata e hiahiatia ana. Koinei te take he mea nui ki te mahi i a raatau i te wa tika. He maha nga korero kua tuhia mo te tātari me te arotautanga, tae atu ki te Ipurangi. Hei tauira, no tata nei i arotakehia tenei kaupapa i tenei whakaputanga.

He maha nga utu me nga utu kore utu mo tenei. Hei tauira, he mea kua rite решение, i runga i te tikanga arotautanga taupū urutau.

I muri mai, kia titiro tatou ki te whaipainga kore utu SQLIndexManager, na AlanDenton.

Ko te rereketanga hangarau matua i waenga i te SQLIndexManager me te maha o etahi atu taapiri ka tukuna e te kaituhi ake konei и konei.

I roto i tenei tuhinga, ka titiro tatou ki waho ki te kaupapa me nga kaha whakahaere o tenei otinga rorohiko.

Te matapaki i tenei taputapu konei.
I te roanga o te wa, kua whakatikahia te nuinga o nga korero me nga hapa.

Na, me haere tatou ki te whaipainga SQLIndexManager ake.

Kua tuhia te tono ki C# .NET Framework 4.5 i Visual Studio 2017 me te whakamahi i te DevExpress mo nga puka:

penei ana te ahua:

Ko nga tono katoa ka hangaia i roto i nga konae e whai ake nei:

1. taupū
2. Uiui
3. Miihini Uiui
4. Nga korero Tūmau

Ina hono ana ki te papaunga raraunga me te tuku patai ki te DBMS, ka hainatia te tono penei:

ApplicationName=”SQLIndexManager”

Ina whakarewahia e koe te tono, ka tuwhera he matapihi aratau hei taapiri hononga:

I konei, ko te uta i te rarangi katoa o nga tauira MS SQL Server katoa e waatea ana i runga i nga whatunga rohe kaore ano kia mahi.

Ka taea hoki te taapiri hononga ma te paatene maui ki te tahua matua:

I muri mai, ka whakarewahia nga patai e whai ake nei ki te DBMS:

1. Te tiki korero mo te DBMS

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

2. Te tiki rarangi o nga papaa raraunga e waatea ana me o raatau taonga poto

   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
   

I muri i te mahi i nga tuhinga o runga ake nei, ka puta he matapihi kei roto nga korero poto mo nga papaa raraunga o te tauira kua tohua o MS SQL Server:

Me mahara ko nga korero whanui e whakaatuhia ana i runga i nga tika. Mena kei reira sysadmin, ka taea e koe te kowhiri i nga raraunga mai i te tirohanga sys.master_files. Mena karekau he motika penei, ka iti noa nga raraunga ka whakahokia mai kia kore ai e whakaroa te tono.

I konei ka hiahia koe ki te kowhiri i nga putunga korero ka paato i te paatene "OK".

Whai muri, ka mahia te tuhinga e whai ake nei mo ia papaarangi kua tohua hei tātari i te ahua o nga tohu:

Te tātari tūnga taupū

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

Ka kitea mai i nga patai ake, he maha nga wa ka whakamahia nga ripanga rangitahi. Ka mahia tenei kia kore ai he whakahiato, a, mo te kaupapa nui, ka taea te hanga i te mahere ki te whakarara i te wa e whakauru ana i nga raraunga, na te mea ko te whakauru i nga taurangi tepu ka taea anake i roto i te miro kotahi.

I muri i te mahi i te tuhinga i runga ake nei, ka puta he matapihi me te ripanga taupū:

Ka taea hoki e koe te whakaatu i etahi atu korero taipitopito i konei, penei:

1. pātengi raraunga
2. te maha o nga waahanga
3. te ra me te wa o te waeatanga whakamutunga
4. kōpeketanga
5. rōpū kōnae

me te pera.
Ko nga kaikorero ake ka taea te whakarite:

I roto i nga pūtau o te pou Whakatika, ka taea e koe te kowhiri he aha te mahi ka mahia i te wa e arotau ana. Ano, ka oti te karapa, ka tohua he mahi taunoa i runga i nga tautuhinga kua tohua:

Me whiriwhiri e koe nga tohu e hiahiatia ana mo te tukatuka.

Ma te whakamahi i te tahua matua, ka taea e koe te tiaki i te tuhinga (ko te paatene ano ka tiimata te tukanga arotautanga taurangi ake):

ka tiaki i te ripanga ki nga momo whakatakotoranga (ma te paatene kotahi ka taea e koe te whakatuwhera i nga tautuhinga taipitopito mo te tātari me te arotau i nga tohu tohu):

Ka taea hoki te whakahou i nga korero ma te panui i te paatene tuatoru i te taha maui i te tahua matua i te taha o te karaihe whakanui.

Ko te paatene me te karaihe whakanui ka taea e koe te kowhiri i nga papaa raraunga e hiahiatia ana hei whakaaro.

I tenei wa kaore he punaha awhina matawhānui. Na reira, ka pehi i te paatene "?" ka puta noa he matapihi aratau kei roto nga korero taketake mo te hua rorohiko:

I tua atu i nga mea katoa i whakaahuahia i runga ake nei, kei te tahua matua he pae rapu:

Ina timata te tukanga arotautanga taupū:

Ka taea hoki e koe te tiro i te raarangi o nga mahi i mahia i raro o te matapihi:

I roto i te matapihi tautuhinga taipitopito mo te tātaritanga taupū me te arotautanga, ka taea e koe te whirihora i nga whiringa ngawari ake:

Nga tono mo te tono:

1. kia taea te whakahou i nga tatauranga ehara i te mea mo nga taurangi anake me nga huarahi rereke (whakahou katoa, waahanga ranei)
2. kaua e taea te kowhiri i te paataka raraunga anake, engari ano hoki nga kaitoro rereke (he tino watea tenei ina he maha nga waahanga o te MS SQL Server)
3. Mo te ngawari ake o te whakamahi, e whakaarohia ana kia takai nga whakahau ki roto i nga whare pukapuka ka whakaputa ki nga whakahau PowerShell, pera me te mahi, hei tauira, i konei:
4. dbatools.io/commands
5. kia taea te tiaki me te whakarereke i nga tautuhinga whaiaro mo te tono katoa, me te mea e tika ana, mo ia tauira o te MS SQL Server me ia putunga raraunga
6. Mai i nga tohu 2 me te 4, ka hiahia koe ki te hanga roopu ma nga papaa raraunga me nga roopu ma nga tauira MS SQL Server, he rite nga tautuhinga
7. rapua nga taurangi taarua (kua oti, kaore ano kia oti, he paku rereke, he rereke ranei i roto i nga pou kua whakauruhia)
8. I te mea ko te SQLIndexManager e whakamahia ana mo te MS SQL Server DBMS anake, me whakaatu tenei ki te ingoa, hei tauira, penei: SQLIndexManager mo MS SQL Server
9. Nekehia nga waahanga kore-GUI katoa o te tono ki roto i nga waahanga motuhake ka tuhi ano ki te .NET Core 2.1

I te wa e tuhi ana, kei te kaha te whakawhanake i te take 6 o nga hiahia, a, kua tautokohia te ahua o te rapu i nga taarua katoa me te rite:

Rauemi

• tikanga arotautanga taupū urutau
• SQLIndexManager whaipainga
• he tirohanga hangarau poto mo te whaipainga SQLIndexManager
• matapaki mo te whaipainga SQLIndexManager
• Kaiwhakahaere Taurangi SQL - Utauta GUI koreutu mo te Tiaki Taurangi i runga i te SQL Server me te Azure
• SQL Server Index Analysis me te Arotautanga

Source: will.com