ProHoster > بلوق > إدارة > مراجعة الأداة المجانية SQLIndexManager

مراجعة الأداة المجانية SQLIndexManager

كما تعلم، تلعب الفهارس دورًا مهمًا في نظام إدارة قواعد البيانات (DBMS)، حيث توفر بحثًا سريعًا في السجلات المطلوبة. ولهذا السبب من المهم جدًا خدمتهم في الوقت المناسب. تمت كتابة الكثير من المواد حول التحليل والتحسين، بما في ذلك على الإنترنت. على سبيل المثال، تمت مراجعة هذا الموضوع مؤخرًا في هذا المنشور.

هناك العديد من الحلول المدفوعة والمجانية لذلك. على سبيل المثال، هناك الجاهزة قرار، بناءً على طريقة تحسين الفهرس التكيفي.

بعد ذلك، دعونا نلقي نظرة على الأداة المساعدة المجانية SQLIndexManager، تأليف آلان دينتون.

الفرق الفني الرئيسي بين SQLIndexManager وعدد من نظائرها الأخرى قدمه المؤلف نفسه هنا и هنا.

في هذه المقالة، سنلقي نظرة خارجية على المشروع والقدرات التشغيلية لهذا الحل البرمجي.

مناقشة هذه الأداة المساعدة هنا.
مع مرور الوقت، تم تصحيح معظم التعليقات والأخطاء.

لذا، دعنا ننتقل الآن إلى الأداة المساعدة SQLIndexManager نفسها.

التطبيق مكتوب بلغة C# .NET Framework 4.5 في Visual Studio 2017 ويستخدم DevExpress للنماذج:

مراجعة الأداة المجانية SQLIndexManager

ويبدو مثل هذا:

مراجعة الأداة المجانية SQLIndexManager

يتم إنشاء جميع الطلبات في الملفات التالية:

  1. فهرس
  2. سؤال
  3. QueryEngine
  4. معلومات الخادم

مراجعة الأداة المجانية SQLIndexManager

عند الاتصال بقاعدة بيانات وإرسال الاستعلامات إلى نظام إدارة قواعد البيانات، يتم توقيع التطبيق على النحو التالي:

ApplicationName=”SQLIndexManager”

عند تشغيل التطبيق، سيتم فتح نافذة مشروطة لإضافة اتصال:
مراجعة الأداة المجانية SQLIndexManager

هنا، لا يعمل تحميل قائمة كاملة بجميع مثيلات MS SQL Server التي يمكن الوصول إليها عبر الشبكات المحلية حتى الآن.

يمكنك أيضًا إضافة اتصال باستخدام الزر الموجود في أقصى اليسار في القائمة الرئيسية:

مراجعة الأداة المجانية SQLIndexManager

بعد ذلك، سيتم إطلاق الاستعلامات التالية لنظام إدارة قواعد البيانات:

  1. الحصول على معلومات حول نظام إدارة قواعد البيانات (DBMS). 
    SELECT ProductLevel  = SERVERPROPERTY('ProductLevel')
     , Edition       = SERVERPROPERTY('Edition')
     , ServerVersion = SERVERPROPERTY('ProductVersion')
     , IsSysAdmin    = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT)

  2. الحصول على قائمة بقواعد البيانات المتوفرة مع خصائصها المختصرة 
    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:

مراجعة الأداة المجانية SQLIndexManager

ومن الجدير بالذكر أن المعلومات الموسعة تظهر على أساس الحقوق. إن كان هناك مسؤول النظام، ثم يمكنك تحديد البيانات من العرض 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)
)

كما يتبين من الاستعلامات نفسها، يتم استخدام الجداول المؤقتة في كثير من الأحيان. يتم ذلك بحيث لا توجد عمليات إعادة تجميع، وفي حالة وجود مخطط كبير، يمكن إنشاء الخطة بالتوازي عند إدراج البيانات، حيث أن إدراج متغيرات الجدول ممكن فقط في مؤشر ترابط واحد.

بعد تنفيذ البرنامج النصي أعلاه، ستظهر نافذة بها جدول فهرس:

مراجعة الأداة المجانية SQLIndexManager

يمكنك أيضًا عرض معلومات تفصيلية أخرى هنا، مثل:

  1. قاعدة بيانات
  2. عدد الأقسام
  3. تاريخ ووقت آخر مكالمة
  4. ضغط
  5. filegroup

إلخ
يمكن تخصيص مكبرات الصوت نفسها:

مراجعة الأداة المجانية SQLIndexManager

في خلايا عمود الإصلاح، يمكنك تحديد الإجراء الذي سيتم تنفيذه أثناء التحسين. وأيضًا، عند اكتمال الفحص، يتم تحديد الإجراء الافتراضي بناءً على الإعدادات المحددة:

مراجعة الأداة المجانية SQLIndexManager

يجب عليك تحديد الفهارس المطلوبة للمعالجة.

باستخدام القائمة الرئيسية، يمكنك حفظ البرنامج النصي (يبدأ نفس الزر عملية تحسين الفهرس نفسها):

مراجعة الأداة المجانية SQLIndexManager

وحفظ الجدول بتنسيقات مختلفة (يسمح لك نفس الزر بفتح الإعدادات التفصيلية لتحليل الفهارس وتحسينها):

مراجعة الأداة المجانية SQLIndexManager

يمكنك أيضًا تحديث المعلومات بالضغط على الزر الثالث الموجود على اليسار في القائمة الرئيسية بجوار العدسة المكبرة.

يتيح لك الزر ذو العدسة المكبرة تحديد قواعد البيانات المطلوبة للنظر فيها.

لا يوجد حاليًا نظام مساعدة شامل. ولذلك، الضغط على الزر "؟" سيؤدي ببساطة إلى ظهور نافذة مشروطة تحتوي على معلومات أساسية حول منتج البرنامج:

مراجعة الأداة المجانية SQLIndexManager

بالإضافة إلى كل ما هو موضح أعلاه، تحتوي القائمة الرئيسية على شريط بحث:

مراجعة الأداة المجانية SQLIndexManager

عند بدء عملية تحسين الفهرس:

مراجعة الأداة المجانية SQLIndexManager

يمكنك أيضًا عرض سجل الإجراءات التي تم تنفيذها في أسفل النافذة:

مراجعة الأداة المجانية SQLIndexManager

في نافذة الإعدادات التفصيلية لتحليل الفهرس وتحسينه، يمكنك تكوين خيارات أكثر دقة:

مراجعة الأداة المجانية SQLIndexManager

طلبات التقديم:

  1. جعل من الممكن تحديث الإحصائيات بشكل انتقائي ليس فقط للفهارس ولكن أيضًا بطرق مختلفة (تحديث كامل أو جزئي)
  2. جعل من الممكن ليس فقط تحديد قاعدة بيانات، ولكن أيضًا خوادم مختلفة (وهذا مناسب جدًا عندما يكون هناك العديد من مثيلات MS SQL Server)
  3. ولمزيد من المرونة في الاستخدام، يقترح تغليف الأوامر في المكتبات وإخراجها إلى أوامر PowerShell، كما يتم على سبيل المثال هنا:
  4. dbatools.io/commands
  5. جعل من الممكن حفظ وتغيير الإعدادات الشخصية لكل من التطبيق بأكمله، وإذا لزم الأمر، لكل مثيل من MS SQL Server وكل قاعدة بيانات
  6. من النقطتين 2 و4، يترتب على ذلك أنك تريد إنشاء مجموعات حسب قواعد البيانات ومجموعات حسب مثيلات MS SQL Server، والتي تكون إعداداتها هي نفسها
  7. البحث عن الفهارس المكررة (الكاملة وغير المكتملة، والتي تكون إما مختلفة قليلاً أو تختلف فقط في الأعمدة المضمنة)
  8. نظرًا لأن SQLIndexManager يُستخدم فقط لـ MS SQL Server DBMS، فمن الضروري أن يعكس ذلك في الاسم، على سبيل المثال، على النحو التالي: SQLIndexManager لـ MS SQL Server
  9. انقل جميع أجزاء التطبيق غير التابعة لواجهة المستخدم الرسومية إلى وحدات منفصلة وأعد كتابتها في .NET Core 2.1

في وقت كتابة هذا التقرير، كان البند 6 من الرغبات قيد التطوير بشكل نشط ويوجد بالفعل دعم في شكل البحث عن التكرارات الكاملة والمماثلة:

مراجعة الأداة المجانية SQLIndexManager

مصادر

المصدر: www.habr.com

إضافة تعليق