مدير فهرس SQL - أداة مجانية لإلغاء تجزئة الفهارس والمحافظة عليها

لسنوات عديدة العمل كخادم ديسيبل SQL والقيام بإدارة الخادم ، ثم تحسين الأداء. بشكل عام ، أردت أن أفعل شيئًا مفيدًا في وقت فراغي للكون وزملائنا. في النهاية ، حصلنا على أداة صيانة فهرس مفتوح المصدر صغيرة لـ SQL Server و Azure.

فكرة

في بعض الأحيان ، عند العمل وفقًا لأولوياتهم ، قد يشبه الأشخاص بطارية من نوع الإصبع - هناك رسوم تحفيز كافية لفلاش واحد فقط ، ثم هذا هو الحال. وحتى وقت قريب ، لم أكن استثناءًا من هذه الملاحظة للحياة. غالبًا ما زرتها الأفكار لإنشاء شيء خاص بي ، لكن الأولويات تغيرت ولم يتم التوصل إلى شيء.

تم توفير تأثير قوي إلى حد ما على حافزي والتطوير المهني من خلال العمل في شركة خاركوف Devart ، التي كانت تعمل في إنشاء برامج لتطوير وإدارة قواعد بيانات SQL Server و MySQL و Oracle.

قبل أن آتي إليهم ، لم يكن لدي أي فكرة عن تفاصيل إنشاء المنتج الخاص بي ، لكنني اكتسبت بالفعل الكثير من المعرفة حول البنية الداخلية لـ SQL Server في العملية. بعد أن قمت بتحسين طلبات البيانات الوصفية في خطوط الإنتاج الخاصة بها لأكثر من عام ، بدأت تدريجياً في فهم الوظيفة المطلوبة في السوق أكثر من أي وظيفة أخرى.

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

بالفعل عندما كان يعمل في مكان جديد وحاول تنفيذ المشروع من تلقاء نفسه ، كان عليه دائمًا تقديم بعض التنازلات. الفكرة الأولية لجعل منتج كبير مليء بالميزات سرعان ما تلاشت وتحولت تدريجياً إلى اتجاه مختلف - لكسر الوظيفة المخططة إلى أدوات صغيرة منفصلة وتنفيذها بشكل مستقل عن بعضها البعض.

نتيجة لذلك ، تم إنشاء SQL Index Manager - أداة صيانة فهرس مجانية لـ SQL Server و Azure. كانت الفكرة الرئيسية هي أن تتخذ كبدائل تجارية من RedGate و Devart ومحاولة تحسين وظائفها. لتوفير ، لكل من المبتدئين والمستخدمين ذوي الخبرة ، القدرة على تحليل الفهارس والحفاظ عليها بشكل مريح.

تطبيق

بكلمات ، كل شيء يبدو بسيطًا دائمًا ... ألقى نظرة على زوجين من vidosikov المحفزين ، ووقف في رف وبدأ في صنع منتج رائع. ولكن في الممارسة العملية ، ليس كل شيء وردية للغاية ، نظرًا لوجود العديد من المزالق عند العمل مع وظيفة جدول النظام sys.dm_db_index_physical_stats وكذلك في المكان الوحيد الذي يمكنك من خلاله الحصول على المعلومات ذات الصلة حول تجزئة الفهرس.

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

عند تحليل RedGate SQL Index Manager (1.1.9.1378 - 155 دولارًا) ، يمكنك أن ترى أن التطبيق يستخدم طريقة بسيطة للغاية: مع استعلام واحد نحصل على قائمة بجداول المستخدم وطرق العرض ، وبعد الاستعلام الثاني ، يتم إرجاع قائمة بجميع الفهارس ضمن قاعدة البيانات المحددة.

SELECT objects.name AS tableOrViewName , objects.object_id AS tableOrViewId , schemas.name AS schemaName , CAST(ISNULL(lobs.NumLobs, 0) AS BIT) AS ContainsLobs , o.is_memory_optimized FROM sys.objects AS objects JOIN sys.schemas AS schemas ON schemas.schema_id = objects.schema_id LEFT JOIN ( SELECT object_id , COUNT(*) AS NumLobs FROM sys.columns WITH (NOLOCK) WHERE system_type_id IN (34, 35, 99) OR max_length = -1 GROUP BY object_id ) AS lobs ON objects.object_id = lobs.object_id LEFT JOIN sys.tables AS o ON o.object_id = objects.object_id WHERE objects.type = 'U' OR objects.type = 'V' SELECT i.object_id AS tableOrViewId , i.name AS indexName , i.index_id AS indexId , i.allow_page_locks AS allowPageLocks , p.partition_number AS partitionNumber , CAST((c.numPartitions - 1) AS BIT) AS belongsToPartitionedIndex FROM sys.indexes AS i JOIN sys.partitions AS p ON p.index_id = i.index_id AND p.object_id = i.object_id JOIN ( SELECT COUNT(*) AS numPartitions , object_id , index_id FROM sys.partitions GROUP BY object_id , index_id ) AS c ON c.index_id = i.index_id AND c.object_id = i.object_id WHERE i.index_id > 0 -- ignore heaps AND i.is_disabled = 0 AND i.is_hypothetical = 0 

بعد ذلك ، في دورة ، يتم إرسال طلب لكل قسم من الفهرس لتحديد حجم ومستوى التجزئة. في نهاية الفحص ، يتم تجاهل الفهارس التي تزن أقل من عتبة الإدخال على العميل.

 EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 1, @partitionNr = 1 EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 2, @partitionNr = 1 EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 3, @partitionNr = 1 

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

ولكن المشكلة أكثر حدة في جانب آخر: سيكون عدد طلبات الخادم مساويًا تقريبًا لإجمالي عدد الأسطر من sys.partitions. نظرًا لحقيقة أن قواعد البيانات الحقيقية يمكن أن تحتوي على عشرات الآلاف من الأقسام ، يمكن أن تؤدي هذه الفروق الدقيقة إلى عدد كبير من الطلبات المماثلة للخادم. في الحالة التي تكون فيها قاعدة البيانات بعيدة ، سيصبح وقت الفحص أطول بسبب زيادة التأخير في الشبكة لكل طلب ، حتى أبسطها.

على عكس RedGate ، يتلقى منتج مشابه تم تطويره في Devart - dbForge Index Manager لـ SQL Server (1.10.38 - 99 دولارًا) معلومات في استعلام كبير واحد ثم يعرض كل شيء على العميل:

 SELECT SCHEMA_NAME(o.[schema_id]) AS [schema_name] , o.name AS parent_name , o.[type] AS parent_type , i.name , i.type_desc , s.avg_fragmentation_in_percent , s.page_count , p.partition_number , p.[rows] , ISNULL(lob.is_lob_legacy, 0) AS is_lob_legacy , ISNULL(lob.is_lob, 0) AS is_lob , CASE WHEN ds.[type] = 'PS' THEN 1 ELSE 0 END AS is_partitioned FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s JOIN sys.partitions p ON s.[object_id] = p.[object_id] AND s.index_id = p.index_id AND s.partition_number = p.partition_number JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id LEFT JOIN ( SELECT c.[object_id] , index_id = ISNULL(i.index_id, 1) , is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END) , is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END) FROM sys.columns c LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id AND i.index_id > 0 WHERE c.system_type_id IN (34, 35, 99) OR c.max_length = -1 GROUP BY c.[object_id], i.index_id ) lob ON lob.[object_id] = i.[object_id] AND lob.index_id = i.index_id JOIN sys.objects o ON o.[object_id] = i.[object_id] JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id WHERE i.[type] IN (1, 2) AND i.is_disabled = 0 AND i.is_hypothetical = 0 AND s.index_level = 0 AND s.alloc_unit_type_desc = 'IN_ROW_DATA' AND o.[type] IN ('U', 'V') 

لقد تمكنا من التخلص من المشكلة الرئيسية للحجاب من نفس النوع من الاستعلامات في منتج منافس ، ولكن عيوب هذا التطبيق هي أنه لا يتم تمرير معلمات إضافية إلى وظيفة sys.dm_db_index_physical_stats التي يمكن أن تحد من مسح الفهارس غير الضرورية بشكل واضح. في الواقع ، يؤدي هذا إلى الحصول على معلومات حول جميع المؤشرات في النظام وتحميلات إضافية للقرص في مرحلة المسح.

من المهم ملاحظة أن البيانات التي تم الحصول عليها من sys.dm_db_index_physical_stats لم يتم تخزينها مؤقتًا بشكل دائم في تجمع المخزن المؤقت ، وبالتالي فإن تقليل القراءات المادية عند الحصول على معلومات حول تجزئة الفهرس كان أحد المهام ذات الأولوية أثناء التطوير.

بعد عدة تجارب ، اتضح أن الجمع بين كلا النهجين ، وتقسيم المسح إلى جزأين. أولاً ، يحدد أحد الاستعلامات الكبيرة حجم المقاطع ، ويقوم بتصفية ما سبق غير الموجودة في نطاق التصفية:

 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 

بعد ذلك ، نحصل فقط على الأقسام التي تحتوي على بيانات لتجنب عمليات القراءة غير الضرورية من فهارس فارغة.

 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) 

وفقًا للإعدادات ، يتم الحصول على أنواع الفهارس التي يريد المستخدم تحليلها فقط (يتم دعم العمل مع أكوام الفهارس وفهارس الكتلة / غير الكتلة ومؤشرات الأعمدة).

 INSERT INTO #Indexes SELECT ObjectID = i.[object_id] , IndexID = i.index_id , IndexName = i.[name] , PagesCount = a.ReservedPages , UnusedPagesCount = a.ReservedPages - a.UsedPages , 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 

بعد ذلك ، يبدأ السحر قليلاً: بالنسبة لجميع المؤشرات الصغيرة ، نحدد مستوى التفتت عن طريق استدعاء الدالة sys.dm_db_index_physical_stats مرارًا وتكرارًا مع إشارة كاملة لجميع المعلمات.

 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) 

بعد ذلك ، نرجع جميع المعلومات الممكنة إلى العميل ، مع تصفية البيانات الزائدة:

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

بعد ذلك ، تحدد استعلامات النقاط مستوى تجزئة المؤشرات الكبيرة.

 EXEC sp_executesql N' DECLARE @DBID INT = DB_ID() SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'') WHERE [index_level] = 0 AND [alloc_unit_type_desc] = ''IN_ROW_DATA''' , N'@ObjectID int,@IndexID int,@PartitionNumber int' , @ObjectId = 1044198770, @IndexId = 1, @PartitionNumber = 1 EXEC sp_executesql N' DECLARE @DBID INT = DB_ID() SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'') WHERE [index_level] = 0 AND [alloc_unit_type_desc] = ''IN_ROW_DATA''' , N'@ObjectID int,@IndexID int,@PartitionNumber int' , @ObjectId = 1552724584, @IndexId = 0, @PartitionNumber = 1 

نظرًا لهذا النهج ، عند إنشاء استعلامات ، كان من الممكن حل مشكلات أداء المسح التي تمت مواجهتها في تطبيقات المنافسين. يمكن استكمال ذلك ، ولكن في عملية التطوير ، ظهرت أفكارًا جديدة تدريجية سمحت بتوسيع نطاق تطبيق منتجك.

في البداية ، تم تنفيذ دعم العمل مع WAIT_AT_LOW_PRIORITY ، ثم أصبح من الممكن استخدام DATA_COMPRESSION و FILL_FACTOR لإعادة بناء المؤشرات.



كان التطبيق متضخمًا قليلاً مع وظائف غير مخططة مسبقًا ، مثل خدمة الأعمدة:

 SELECT * FROM ( SELECT IndexID = [index_id] , PartitionNumber = [partition_number] , PagesCount = SUM([size_in_bytes]) / 8192 , UnusedPagesCount = ISNULL(SUM(CASE WHEN [state] = 1 THEN [size_in_bytes] END), 0) / 8192 , Fragmentation = CAST(ISNULL(SUM(CASE WHEN [state] = 1 THEN [size_in_bytes] END), 0) * 100. / SUM([size_in_bytes]) AS FLOAT) FROM sys.fn_column_store_row_groups(@ObjectID) GROUP BY [index_id] , [partition_number] ) t WHERE Fragmentation >= @Fragmentation AND PagesCount BETWEEN @MinIndexSize AND @MaxIndexSize 

أو القدرة على إنشاء فهارس غير عنقودية بناءً على معلومات من dm_db_missing_index:

 SELECT ObjectID = d.[object_id] , UserImpact = gs.[avg_user_impact] , TotalReads = gs.[user_seeks] + gs.[user_scans] , TotalSeeks = gs.[user_seeks] , TotalScans = gs.[user_scans] , LastUsage = ISNULL(gs.[last_user_scan], gs.[last_user_seek]) , IndexColumns = CASE WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NOT NULL THEN d.[equality_columns] + ', ' + d.[inequality_columns] WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NULL THEN d.[equality_columns] ELSE d.[inequality_columns] END , IncludedColumns = d.[included_columns] FROM sys.dm_db_missing_index_groups g WITH(NOLOCK) JOIN sys.dm_db_missing_index_group_stats gs WITH(NOLOCK) ON gs.[group_handle] = g.[index_group_handle] JOIN sys.dm_db_missing_index_details d WITH(NOLOCK) ON g.[index_handle] = d.[index_handle] WHERE d.[database_id] = DB_ID() 

النتائج

بعد ستة أشهر من مرحلة التطوير النشط ، يسعدني أن الخطط لا تنتهي عند هذا الحد ، لأنني أرغب في مواصلة تطوير هذا المنتج. تتمثل الخطوة التالية في إضافة وظيفة للبحث عن فهارس مكررة أو غير مستخدمة ، بالإضافة إلى تطبيق دعم كامل لخدمة الإحصائيات داخل SQL Server.

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

يمكن تنزيل الإصدار الحالي من التطبيق على GitHub . المصادر في نفس المكان.