🥇סקירה של הכלי החינמי SQLIndexManager

כפי שאתה יודע, אינדקסים ממלאים תפקיד חשוב ב-DBMS, ומספקים חיפוש מהיר לרשומות הנדרשות. לכן זה כל כך חשוב לתת להם שירות בזמן. לא מעט חומר נכתב על ניתוח ואופטימיזציה, כולל באינטרנט. לדוגמה, נושא זה נבדק לאחרונה ב פרסום זה.

יש הרבה פתרונות בתשלום וחינמיים לכך. לדוגמה, יש מוכן החלטה, מבוסס על שיטת אופטימיזציה של אינדקס אדפטיבי.

לאחר מכן, בואו נסתכל על כלי השירות החינמי SQLIndexManager, חובר על ידי אלנדנטון.

ההבדל הטכני העיקרי בין SQLIndexManager למספר אנלוגים אחרים ניתן על ידי המחבר עצמו כאן и כאן.

במאמר זה, נסקור את הפרויקט ואת היכולות התפעוליות של פתרון תוכנה זה.

דיון על כלי השירות הזה כאן.
עם הזמן, רוב ההערות והבאגים תוקנו.

אז בואו נעבור כעת לכלי השירות SQLIndexManager עצמו.

האפליקציה כתובה ב-C# .NET Framework 4.5 ב-Visual Studio 2017 ומשתמשת ב-DevExpress עבור טפסים:

ונראה כך:

כל הבקשות נוצרות בקבצים הבאים:

מדד
שאלה
QueryEngine
ServerInfo

בעת התחברות למסד נתונים ושליחת שאילתות ל-DBMS, היישום נחתם באופן הבא:

ApplicationName=”SQLIndexManager”

כאשר אתה מפעיל את היישום, ייפתח חלון מודאלי להוספת חיבור:

כאן, טעינת רשימה מלאה של כל מופעי MS SQL Server הנגישים ברשתות מקומיות עדיין לא עובדת.

אתה יכול גם להוסיף חיבור באמצעות הכפתור השמאלי ביותר בתפריט הראשי:

לאחר מכן, השאילתות הבאות ל-DBMS יופעלו:

השגת מידע על ה-DBMS

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

קבלת רשימה של מסדי נתונים זמינים עם המאפיינים הקצרים שלהם

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

לאחר ביצוע הסקריפטים לעיל, יופיע חלון המכיל מידע קצר על מסדי הנתונים של המופע הנבחר של MS SQL Server:

ראוי לציין שמידע מורחב מוצג על סמך זכויות. אם יש סיסדמין, לאחר מכן תוכל לבחור נתונים מהתצוגה sys.master_files. אם אין זכויות כאלה, אז פחות נתונים פשוט מוחזרים כדי לא להאט את הבקשה.

כאן אתה צריך לבחור את מסדי הנתונים המעניינים וללחוץ על כפתור "אישור".

לאחר מכן, הסקריפט הבא יבוצע עבור כל מסד נתונים שנבחר כדי לנתח את מצב האינדקסים:

ניתוח מצב אינדקס

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

כפי שניתן לראות מהשאילתות עצמן, נעשה שימוש בטבלאות זמניות לעתים קרובות למדי. זה נעשה כדי שלא יהיו הידור מחדש, ובמקרה של סכמה גדולה, ניתן להפיק את התוכנית במקביל בהכנסת נתונים, שכן הכנסת משתני טבלה אפשרית רק בשרשור אחד.

לאחר ביצוע הסקריפט לעיל, יופיע חלון עם טבלת אינדקס:

אתה יכול גם להציג מידע מפורט אחר כאן, כגון:

מאגר מידע
מספר קטעים
תאריך ושעה של השיחה האחרונה
סחיטה
קבוצת קבצים

ושנאים. ד.
ניתן להתאים את הרמקולים עצמם:

בתאים של העמודה Fix, תוכל לבחור איזו פעולה תתבצע במהלך האופטימיזציה. כמו כן, כאשר הסריקה מסתיימת, פעולת ברירת מחדל נבחרה על סמך ההגדרות שנבחרו:

עליך לבחור את האינדקסים הרצויים לעיבוד.

באמצעות התפריט הראשי, תוכלו לשמור את הסקריפט (אותו כפתור מתחיל את תהליך אופטימיזציית האינדקס עצמו):

ושמור את הטבלה בפורמטים שונים (אותו כפתור מאפשר לך לפתוח הגדרות מפורטות לניתוח ואופטימיזציה של אינדקסים):

ניתן גם לעדכן את המידע בלחיצה על הכפתור השלישי משמאל בתפריט הראשי שליד זכוכית המגדלת.

הכפתור עם זכוכית מגדלת מאפשר לבחור את מסדי הנתונים הרצויים לשיקול.

כרגע אין מערכת עזרה מקיפה. לכן, לחיצה על כפתור "?" פשוט יגרום להופעת חלון מודאלי המכיל מידע בסיסי על מוצר התוכנה:

בנוסף לכל המתואר לעיל, התפריט הראשי כולל שורת חיפוש:

כאשר מתחילים בתהליך אופטימיזציית האינדקס:

אתה יכול גם להציג יומן של פעולות שבוצעו בתחתית החלון:

בחלון ההגדרות המפורט לניתוח אינדקס ואופטימיזציה, תוכל להגדיר אפשרויות עדינות יותר:

בקשות לבקשה:

מאפשרים לעדכן סטטיסטיקה באופן סלקטיבי לא רק עבור אינדקסים וגם בדרכים שונות (עדכון מלא או חלקי)
מאפשרים לא רק לבחור מסד נתונים, אלא גם שרתים שונים (זה מאוד נוח כשיש הרבה מקרים של MS SQL Server)
לגמישות רבה יותר בשימוש, מומלץ לעטוף את הפקודות בספריות ולהוציא אותן לפקודות PowerShell, כפי שנעשה, למשל, כאן:
dbatools.io/commands
מאפשרים לשמור ולשנות הגדרות אישיות הן עבור האפליקציה כולה ובמידת הצורך עבור כל מופע של MS SQL Server וכל מסד נתונים
מנקודות 2 ו-4, יוצא שאתה רוצה ליצור קבוצות לפי מסדי נתונים וקבוצות לפי מופעי MS SQL Server, שעבורם ההגדרות זהות
חפש אינדקסים כפולים (שלמים ולא שלמים, שהם מעט שונים או שונים רק בעמודות הכלולים)
מכיוון ש- SQLIndexManager משמש רק עבור MS SQL Server DBMS, יש צורך לשקף זאת בשם, למשל, באופן הבא: SQLIndexManager עבור MS SQL Server
העבר את כל החלקים שאינם GUI של היישום לתוך מודולים נפרדים וכתוב אותם מחדש ב-.NET Core 2.1

בזמן כתיבת שורות אלה, פריט 6 של המשאלות נמצא בפיתוח אקטיבי וכבר יש תמיכה בצורת חיפוש כפילויות שלמות ודומות:

מקורות

מקור: www.habr.com

סקירה של הכלי החינמי SQLIndexManager

מקורות

הוספת תגובה ביטול תגובה