Revizyon zouti gratis SQLIndexManager la

Kòm ou konnen, endèks jwe yon wòl enpòtan nan yon DBMS, bay yon rechèch rapid nan dosye yo mande yo. Se poutèt sa li enpòtan pou sèvis yo nan yon fason apwopriye. Byen anpil nan materyèl yo te ekri sou analiz ak optimize, ki gen ladan sou entènèt la. Pou egzanp, sijè sa a te resamman revize nan piblikasyon sa a.

Gen anpil solisyon peye ak gratis pou sa. Pou egzanp, gen yon pare-fè desizyon an, ki baze sou yon metòd optimize endèks adaptasyon.

Apre sa, kite a gade nan sèvis piblik la gratis SQLIndexManager, otè pa AlanDenton.

Diferans prensipal teknik ant SQLIndexManager ak yon kantite lòt analogue se otè a li menm bay isit la и isit la.

Nan atik sa a, nou pral pran yon gade deyò nan pwojè a ak kapasite yo operasyonèl nan solisyon lojisyèl sa a.

Diskite sou sèvis piblik sa a isit la.
Apre yon tan, pi fò nan kòmantè yo ak pinèz yo te korije.

Se konsa, se pou yo kounye a deplase sou sèvis piblik la SQLIndexManager tèt li.

Aplikasyon an ekri nan C# .NET Framework 4.5 nan Visual Studio 2017 epi sèvi ak DevExpress pou fòm:

ak sanble tankou sa a:

Tout demann yo pwodwi nan dosye sa yo:

1. endèks
2. Rechèch
3. QueryEngine
4. ServerInfo

Lè w konekte ak yon baz done epi voye demann nan DBMS, aplikasyon an siyen jan sa a:

ApplicationName=”SQLIndexManager”

Lè ou lanse aplikasyon an, yon fenèt modal ap louvri pou ajoute yon koneksyon:

Isit la, chaje yon lis konplè tout ka MS SQL sèvè aksesib sou rezo lokal yo poko travay.

Ou kapab tou ajoute yon koneksyon lè l sèvi avèk bouton ki pi agoch ​​la nan meni prensipal la:

Apre sa, demann sa yo nan DBMS yo pral lanse:

1. Jwenn enfòmasyon sou DBMS la

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

2. Jwenn yon lis baz done ki disponib ak pwopriyete kout yo

   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
   

Apre w fin egzekite scripts ki anwo yo, yon fenèt ap parèt ki gen enfòmasyon tou kout sou baz done egzanp MS SQL sèvè a chwazi a:

Li se vo sonje ke yo montre enfòmasyon pwolonje ki baze sou dwa. Si la sysadmin, Lè sa a, ou ka chwazi done nan gade nan sys.master_files. Si pa gen dwa sa yo, Lè sa a, mwens done yo tou senpleman retounen pou yo pa ralanti demann lan.

Isit la ou bezwen chwazi baz done ki enterese yo epi klike sou bouton "OK".

Apre sa, yo pral egzekite script sa a pou chak baz done chwazi pou analize eta endèks yo:

Analiz estati endèks

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

Kòm yo ka wè nan demann yo tèt yo, tab tanporè yo itilize byen souvan. Sa a se fè pou pa gen okenn recompilation, ak nan ka a nan yon konplo gwo, plan an ka pwodwi nan paralèl lè mete done, depi mete varyab tab se posib sèlman nan yon sèl fil.

Apre ou fin egzekite script ki anwo a, yon fenèt ak yon tablo endèks ap parèt:

Ou kapab tou montre lòt enfòmasyon detaye isit la, tankou:

1. baz done
2. kantite seksyon
3. dat ak lè dènye apèl la
4. konpresyon
5. filegroup

ak sou sa.
Oratè yo tèt yo ka Customized:

Nan selil yo nan kolòn Ranje, ou ka chwazi ki aksyon yo pral fèt pandan optimize. Epitou, lè eskanè a fini, yo chwazi yon aksyon default ki baze sou anviwònman yo chwazi:

Ou dwe chwazi endis yo vle pou trete.

Sèvi ak meni prensipal la, ou ka sove script la (menm bouton an kòmanse pwosesis optimize endèks la tèt li):

epi sove tab la nan diferan fòma (menm bouton an pèmèt ou louvri anviwònman detaye pou analize ak optimize endèks):

Ou ka mete ajou enfòmasyon an tou lè w klike sou twazyèm bouton ki sou bò gòch la nan meni prensipal la bò kote loup la.

Bouton an ak yon loup pèmèt ou chwazi baz done yo vle pou konsiderasyon.

Kounye a pa gen yon sistèm èd konplè. Se poutèt sa, peze bouton an "?" pral tou senpleman lakòz yon fenèt modal parèt ki gen enfòmasyon debaz sou pwodwi lojisyèl an:

Anplis de sa ki dekri pi wo a, meni prensipal la gen yon ba rechèch:

Lè w kòmanse pwosesis optimize endèks la:

Ou ka wè tou yon boutèy demi lit aksyon ki fèt nan pati anba fenèt la:

Nan fennèt paramèt detaye pou analiz endèks ak optimize, ou ka configured opsyon plis sibtil:

Demann pou aplikasyon an:

1. fè li posib pou oaza aktyalizasyon estatistik pa sèlman pou endèks ak tou nan diferan fason (konplètman aktyalizasyon oswa pasyèlman)
2. fè li posib pa sèlman pou chwazi yon baz done, men tou diferan serveurs (sa a trè pratik lè gen anpil ka MS SQL sèvè)
3. Pou pi gwo fleksibilite nan itilize, li sijere pou vlope kòmandman yo nan bibliyotèk ak pwodiksyon yo nan kòmandman PowerShell, jan sa fèt, pou egzanp, isit la:
4. dbatools.io/commands
5. fè li posib pou konsève pou ak chanje paramèt pèsonèl tou de pou tout aplikasyon an epi, si sa nesesè, pou chak egzanp MS SQL Server ak chak baz done.
6. Soti nan pwen 2 ak 4, li swiv ke ou vle kreye gwoup pa baz done ak gwoup pa egzanp MS SQL Server, pou ki paramèt yo se menm bagay la.
7. rechèch pou endis kopi (konplè ak enkonplè, ki swa yon ti kras diferan oswa diferan sèlman nan kolòn ki enkli yo)
8. Depi SQLIndexManager yo itilize sèlman pou MS SQL sèvè DBMS, li nesesè yo reflete sa a nan non an, pou egzanp, jan sa a: SQLIndexManager pou MS SQL sèvè
9. Deplase tout pati ki pa GUI nan aplikasyon an nan modil separe epi reekri yo nan .NET Core 2.1.

Nan moman ekri a, atik 6 nan volonte yo ap devlope aktivman e gen deja sipò nan fòm rechèch pou kopi konplè ak menm jan an:

Sous

• metòd adaptasyon optimize endèks
• SQLIndexManager sèvis piblik
• kout BECA teknik sou sèvis piblik la SQLIndexManager
• diskisyon sou sèvis piblik SQLIndexManager la
• SQL Index Manadjè - Zouti gratis entèfas pou Antretyen Index sou SQL sèvè ak Azure
• SQL sèvè Endèks analiz ak optimize

Sous: www.habr.com