हर दिन सेवा योजना - भाग 1: स्वचालित सूचकांक डीफ़्रैग्मेन्टेशन

डेटाबेस को एक प्रकार की संदर्भ इकाई के रूप में मानना ​​एक गलती है, क्योंकि, समय के साथ, विभिन्न प्रकार की अवांछनीय परिस्थितियां दिखाई दे सकती हैं - प्रदर्शन में गिरावट, खराबी, और इसी तरह।

ऐसी स्थितियों की संभावना को कम करने के लिए, स्थिरता और इष्टतम डेटाबेस प्रदर्शन की गारंटी देने वाले कार्यों से सेवा योजना बनाएं।

इन कार्यों के बीच, निम्नलिखित को प्रतिष्ठित किया जा सकता है:

1. डीफ़्रैग्मेन्ट इंडेक्स
2. अद्यतन आँकड़े
3. बैकअप

इन कार्यों में से प्रत्येक के स्वचालन पर विचार करें।

अद्यतन 2019-06-03:
लेकिन पहले मैं ओपन सोर्स प्रोग्राम का विज्ञापन करना चाहता हूं, जो मैंने इस पोस्ट को लिखने के 5 साल बाद किया था। ऐसा ऐतिहासिक रूप से हुआ कि लंबे समय तक उन्होंने SQL सर्वर की सर्विसिंग के लिए सिस्टम टूल्स के विकास में भाग लिया। इस समय के दौरान, कई विचार जमा हो गए हैं और एक निश्चित स्तर पर मैं अपना खुद का कुछ करना चाहता था।

परिणाम एक ऐसा एप्लिकेशन है जो आपको सुविधाजनक UI के माध्यम से अनुक्रमित करने की अनुमति देता है। रेडगेट और डेवर्ट के पेड एनालॉग्स को मुख्य प्रतियोगियों के लिए लिया गया था।

एसक्यूएल इंडेक्स मैनेजर की मुख्य विशेषताएं:

• खंडित सूचकांक प्राप्त करने के लिए अनुकूलित एल्गोरिदम
• एक समय में कई डेटाबेस की सेवा करने की क्षमता
• चयनित सेटिंग्स के आधार पर अनुक्रमित के लिए स्वचालित कार्रवाई चयन
• अधिक सुविधाजनक विश्लेषिकी के लिए वैश्विक खोज और परिष्कृत फ़िल्टरिंग के लिए समर्थन
• बड़ी संख्या में सेटिंग्स और इंडेक्स के बारे में उपयोगी जानकारी
• सूचकांक रखरखाव लिपियों की स्वचालित पीढ़ी
• हीप और कॉलमस्टोरस्टोर रखरखाव सहायता
• कमांड लाइन का समर्थन
• अनुक्रमणिका संपीड़न और सांख्यिकी अद्यतन को पुन: बनाने के बजाय शामिल करने की क्षमता
• परिणाम निर्यात करने की क्षमता
• इंटरफ़ेस अनुकूलन
• SQL Server 2008+ और Azure SQL डेटाबेस के सभी संस्करणों के लिए समर्थन

अभी और भविष्य में, SQL Index Manager पूरी तरह से मुफ़्त है ।
इस लिंक पर एप्लिकेशन का नवीनतम संस्करण डाउनलोड किया जा सकता है, और सभी स्रोत GitHub पर हैं।





लेकिन अब वापस मूल पोस्ट पर। तो, पहले बिंदु ...

फ़ाइल सिस्टम और लॉग फ़ाइल के विखंडन के अलावा, डेटा फ़ाइलों के अंदर विखंडन का डेटाबेस प्रदर्शन पर महत्वपूर्ण प्रभाव पड़ता है:

1. व्यक्तिगत सूचकांक पृष्ठों के भीतर विखंडन

रिकॉर्ड डालने, अपडेट करने और हटाने के बाद, अनिवार्य रूप से पृष्ठों पर रिक्त स्थान होते हैं। इसमें कुछ भी गलत नहीं है, क्योंकि यह स्थिति बिल्कुल सामान्य है, अगर एक के लिए नहीं, लेकिन ...

स्ट्रिंग की लंबाई बहुत महत्वपूर्ण भूमिका निभाती है। उदाहरण के लिए, यदि किसी पंक्ति में एक आकार है जो आधे से अधिक पृष्ठ पर है, तो इस पृष्ठ के मुक्त आधे हिस्से का उपयोग नहीं किया जाएगा। परिणामस्वरूप, जैसे-जैसे पंक्तियों की संख्या बढ़ती है, डेटाबेस में अप्रयुक्त स्थान में वृद्धि होगी।

इस प्रकार के विखंडन से निपटने के लिए योजना के डिजाइन चरण में है, अर्थात्, उन डेटा प्रकारों का चयन करें जो पृष्ठों पर कॉम्पैक्ट रूप से फिट होते हैं।

2. सूचकांक संरचनाओं के भीतर विखंडन

इस प्रकार के विखंडन का मुख्य कारण पृष्ठ विभाजन ऑपरेशन है। उदाहरण के लिए, प्राथमिक कुंजी की संरचना के अनुसार, एक नई पंक्ति को एक विशिष्ट सूचकांक पृष्ठ में डाला जाना चाहिए, लेकिन इस पृष्ठ में सम्मिलित डेटा को समायोजित करने के लिए पर्याप्त स्थान नहीं है।

इस स्थिति में, एक नया पृष्ठ बनाया जाता है, जिसमें पुराने पृष्ठ से लगभग आधे अभिलेख स्थानांतरित हो जाएंगे। एक नया पृष्ठ अक्सर शारीरिक रूप से पुराने के निकट नहीं होता है और इसलिए, सिस्टम द्वारा खंडित के रूप में चिह्नित किया जाता है।

किसी भी मामले में, विखंडन से जानकारी की समान मात्रा को संग्रहीत करने के लिए पृष्ठों की संख्या में वृद्धि होती है। यह स्वचालित रूप से डेटाबेस आकार में वृद्धि और अप्रयुक्त स्थान में वृद्धि की ओर जाता है।

खंडित अनुक्रमित को संदर्भित करने वाले प्रश्नों का निष्पादन करते समय, अधिक IO परिचालनों की आवश्यकता होती है। इसके अलावा, विखंडन सर्वर की मेमोरी पर अतिरिक्त लागत लगाता है, जिसे कैश में अतिरिक्त पृष्ठों को संग्रहीत करना पड़ता है।

अनुक्रमणिका विखंडन से निपटने के लिए, SQL सर्वर शस्त्रागार में निम्नलिखित कमांड शामिल हैं: ALTER INDEX REBUILD / REORGANIZE ।

एक सूचकांक के पुनर्निर्माण में पुराने को हटाना और सूचकांक का एक नया उदाहरण बनाना शामिल है। यह ऑपरेशन विखंडन को समाप्त करता है, पृष्ठ को संकुचित करके डिस्क स्थान को पुनर्स्थापित करता है, जबकि पृष्ठ पर मुक्त स्थान को संग्रहीत करता है, जिसे फ़िलफ़ेक्टर विकल्प के साथ सेट किया जा सकता है। यह ध्यान रखना महत्वपूर्ण है कि सूचकांक के पुनर्निर्माण का संचालन बहुत महंगा है।

इसलिए, उस मामले में जहां विखंडन नगण्य है, मौजूदा सूचकांक को पुनर्गठित करना बेहतर है। इस ऑपरेशन के लिए सूचकांक को फिर से बनाने की तुलना में कम सिस्टम संसाधनों की आवश्यकता होती है और इसमें पत्ती-स्तरीय पृष्ठों को पुनर्गठित करना शामिल है। इसके अलावा, पुनर्गठन यदि संभव हो तो सूचकांक पृष्ठों को संकुचित करता है।

किसी विशेष इंडेक्स के विखंडन की डिग्री sysinos_db_index_physical_stats डायनेमिक सिस्टम दृश्य में देखी जा सकती है:

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) WHERE avg_fragmentation_in_percent > 0 

इस क्वेरी में, अंतिम पैरामीटर मोड सेट करता है, जिसके मूल्य से यह जल्दी से संभव है, लेकिन सूचकांक विखंडन ( सीमित / पूर्ण मोड) के स्तर को काफी सटीक रूप से निर्धारित नहीं करता है। इसलिए, SAMPLED / DETAILED मोड सेट करने की अनुशंसा की जाती है।

हम जानते हैं कि खंडित सूचियों की सूची कहाँ से प्राप्त करनी है। अब उनमें से प्रत्येक के लिए आवश्यक समान INDEX कमांड उत्पन्न करना आवश्यक है। परंपरागत रूप से, इसके लिए कर्सर का उपयोग किया जाता है:

 DECLARE @SQL NVARCHAR(MAX) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT ' ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' + CASE WHEN s.avg_fragmentation_in_percent > 30 THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON)' ELSE 'REORGANIZE' END + ';' FROM ( SELECT s.[object_id] , s.index_id , avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent) FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s WHERE s.page_count > 128 -- > 1 MB AND s.index_id > 0 -- <> HEAP AND s.avg_fragmentation_in_percent > 5 GROUP BY s.[object_id], s.index_id ) s JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id] OPEN cur FETCH NEXT FROM cur INTO @SQL WHILE @@FETCH_STATUS = 0 BEGIN EXEC sys.sp_executesql @SQL FETCH NEXT FROM cur INTO @SQL END CLOSE cur DEALLOCATE cur 

सूचकांक के पुनर्निर्माण की प्रक्रिया को तेज करने के लिए, SORT_IN_TEMPDB विकल्प को अतिरिक्त रूप से निर्दिष्ट करने की अनुशंसा की जाती है। हमें ONLINE विकल्प का भी अलग से उल्लेख करना होगा - यह इंडेक्स री-क्रिएशन को धीमा कर देता है। लेकिन कभी-कभी यह उपयोगी है। उदाहरण के लिए, एक गुच्छेदार सूचकांक से पढ़ना बहुत महंगा है। हमने एक कवरिंग इंडेक्स बनाया और प्रदर्शन समस्या को हल किया। अगला, हम एक आरईबीयूएल गैर-अनुक्रमित सूचकांक करते हैं। इस बिंदु पर, हमें फिर से क्लस्टर इंडेक्स की ओर मुड़ना होगा - जो प्रदर्शन को कम करता है।

SORT_IN_TEMPDB आपको टेम्पर्ड डेटाबेस में इंडेक्स को फिर से बनाने की अनुमति देता है, जो अपर्याप्त मेमोरी के मामले में बड़े इंडेक्स के लिए विशेष रूप से उपयोगी है, अन्यथा विकल्प की अनदेखी की जाती है। इसके अलावा, अगर tempdb डेटाबेस किसी अन्य डिस्क पर स्थित है, तो यह इंडेक्स बनाने में लगने वाले समय को काफी कम कर देगा। ONLINE आपको उस इंडेक्स को बनाए बिना क्वेरीज़ को ऑब्जेक्ट को ब्लॉक करने की अनुमति देता है जिसके लिए यह इंडेक्स बनाया गया है।

जैसा कि अभ्यास से पता चला है, विखंडन की कम डिग्री के साथ या कम संख्या में पृष्ठों के साथ डीफ़्रैग्मेंटिंग सूचकांक कोई ध्यान देने योग्य सुधार नहीं लाते हैं जो उनके साथ काम करते समय उत्पादकता में वृद्धि में योगदान करते हैं।

इसके अतिरिक्त, कर्सर का उपयोग किए बिना उपरोक्त क्वेरी फिर से लिखी जा सकती है:

 DECLARE @IsDetailedScan BIT = 0 , @IsOnline BIT = 0 DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = ( SELECT ' ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' + CASE WHEN s.avg_fragmentation_in_percent > 30 THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON' -- Enterprise, Developer + CASE WHEN SERVERPROPERTY('EditionID') IN (1804890536, -2117995310) AND @IsOnline = 1 THEN ', ONLINE = ON' ELSE '' END + ')' ELSE 'REORGANIZE' END + '; ' FROM ( SELECT s.[object_id] , s.index_id , avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent) FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, CASE WHEN @IsDetailedScan = 1 THEN 'DETAILED' ELSE 'LIMITED' END) s WHERE s.page_count > 128 -- > 1 MB AND s.index_id > 0 -- <> HEAP AND s.avg_fragmentation_in_percent > 5 GROUP BY s.[object_id], s.index_id ) s JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id JOIN sys.objects o ON o.[object_id] = s.[object_id] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') PRINT @SQL EXEC sys.sp_executesql @SQL 

परिणामस्वरूप, निष्पादित होने पर, दोनों प्रश्न, विकट समस्याग्रस्त सूचकांकों के लिए प्रश्न उत्पन्न करेंगे:

 ALTER INDEX [IX_TransactionHistory_ProductID] ON [Production].[TransactionHistory] REORGANIZE; ALTER INDEX [IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID] ON [Production].[TransactionHistory] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON); ALTER INDEX [IX_TransactionHistoryArchive_ProductID] ON [Production].[TransactionHistoryArchive] REORGANIZE; 

दरअसल, यह डेटाबेस के लिए रखरखाव योजना बनाने का पहला हिस्सा है। अगले भाग में, हम आंकड़ों को स्वचालित रूप से अपडेट करने के लिए एक क्वेरी लिखेंगे।

यदि आप इस लेख को अंग्रेजी बोलने वाले दर्शकों के साथ साझा करना चाहते हैं:
SQL सर्वर विशिष्ट रखरखाव योजनाएँ: स्वचालित अनुक्रमणिका डीफ़्रैग्मेन्टेशन

अद्यतन 2016-04-22: अलग-अलग वर्गों को डीफ़्रैग्मेन्ट करने की क्षमता को जोड़ा और कुछ बग्स को ठीक किया

 USE ... DECLARE @PageCount INT = 128 , @RebuildPercent INT = 30 , @ReorganizePercent INT = 10 , @IsOnlineRebuild BIT = 0 , @IsVersion2012Plus BIT = CASE WHEN CAST(SERVERPROPERTY('productversion') AS CHAR(2)) NOT IN ('8.', '9.', '10') THEN 1 ELSE 0 END , @IsEntEdition BIT = CASE WHEN SERVERPROPERTY('EditionID') IN (1804890536, -2117995310) THEN 1 ELSE 0 END , @SQL NVARCHAR(MAX) SELECT @SQL = ( SELECT ' ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s2.name) + '.' + QUOTENAME(o.name) + ' ' + CASE WHEN s.avg_fragmentation_in_percent >= @RebuildPercent THEN 'REBUILD' ELSE 'REORGANIZE' END + ' PARTITION = ' + CASE WHEN ds.[type] != 'PS' THEN 'ALL' ELSE CAST(s.partition_number AS NVARCHAR(10)) END + ' WITH (' + CASE WHEN s.avg_fragmentation_in_percent >= @RebuildPercent THEN 'SORT_IN_TEMPDB = ON' + CASE WHEN @IsEntEdition = 1 AND @IsOnlineRebuild = 1 AND ISNULL(lob.is_lob_legacy, 0) = 0 AND ( ISNULL(lob.is_lob, 0) = 0 OR (lob.is_lob = 1 AND @IsVersion2012Plus = 1) ) THEN ', ONLINE = ON' ELSE '' END ELSE 'LOB_COMPACTION = ON' END + ')' FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s 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.schemas s2 ON o.[schema_id] = s2.[schema_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.page_count > @PageCount AND s.alloc_unit_type_desc = 'IN_ROW_DATA' AND o.[type] IN ('U', 'V') AND s.avg_fragmentation_in_percent > @ReorganizePercent FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') PRINT @SQL --EXEC sys.sp_executesql @SQL