डीफ़्रेग्मेंट इंडेक्स आँकड़ों के संग्रह के साथ MS SQL 2008 R2

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

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

लेकिन चलो एक सरल के साथ शुरू करते हैं, इस बहुत डेटा को संग्रहीत करने के लिए एक तालिका बनाते हैं (मैंने एक अलग डेटाबेस बनाया, जहां मैं उन तालिकाओं को रखता हूं जो मैं सर्वर डेटाबेस को सर्विस करते समय उपयोग करता हूं):

स्तंभ	टाइप	टिप्पणी
proc_id	पूर्णांक	पहचान के लिए प्रक्रिया की अनुक्रम संख्या
start_time	datetime	ALTER INDEX क्वेरी शुरू करना
end_time	datetime	ALTER INDEX अनुरोध पूरा करना
database_id	SMALLINT	डीबी आईडी
object_id	इंट	टेबल आई.डी.
TABLE_NAME	वर्चर (50)	टेबल का नाम
index_id	इंट	सूचकांक आईडी
INDEX_NAME	वर्चर (50)	सूचकांक का नाम
avg_frag_percent_before	नाव	ALTER INDEX से पहले सूचकांक विखंडन प्रतिशत
fragment_count_before	bigint	डीफ़्रैग्मेन्टेशन से पहले टुकड़ों की संख्या
pages_count_before	bigint	डीफ़्रैग्मेन्टेशन से पहले सूचकांक पृष्ठों की संख्या
fill_factor	tinyint	सूचकांक पृष्ठ भरण स्तर
partition_num	पूर्णांक	अनुभाग संख्या
avg_frag_percent_after	नाव	सूचकांक सूचकांक के प्रदर्शन के बाद सूचकांक विखंडन प्रतिशत
fragment_count_after	bigint	डीफ़्रैग्मेन्टेशन के बाद टुकड़ों की संख्या
pages_count_after	bigint	डीफ़्रैग्मेन्टेशन के बाद सूचकांक पृष्ठों की संख्या
कार्य	वर्चर (10)	कार्रवाई की गई

संपूर्ण डीफ़्रैग्मेन्टेशन प्रक्रिया इस तालिका से डेटा लेगी, जिसका अर्थ है कि आपको इसे भरने की आवश्यकता है:

DECLARE @currentProcID INT –-    –-  ,     SELECT @currentProcID = ISNULL(MAX(proc_id), 0) + 1 FROM dba_tasks.dbo.index_defrag_statistic --       INSERT INTO dba_tasks.dbo.index_defrag_statistic ( proc_id, database_id, [object_id], table_name, index_id, index_name, avg_frag_percent_before, fragment_count_before, pages_count_before, fill_factor, partition_num) SELECT @currentProcID, dm.database_id, dm.[object_id], tbl.name, dm.index_id, idx.name, dm.avg_fragmentation_in_percent, dm.fragment_count, dm.page_count, idx.fill_factor, dm.partition_number FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm INNER JOIN sys.tables tbl ON dm.object_id = tbl.object_id INNER JOIN sys.indexes idx ON dm.object_id = idx.object_id AND dm.index_id = idx.index_id WHERE page_count > 8 AND avg_fragmentation_in_percent > 10 AND dm.index_id > 0 

नमूनाकरण की स्थिति:
page_count> 8 - मेरा मानना ​​है कि कम संख्या में पृष्ठों के साथ अनुक्रमणिका के पुनर्निर्माण का कोई मतलब नहीं है, क्योंकि यह बेहतर नहीं हुआ, और प्रक्रिया पर खर्च किया गया समय बहुत मूल्यवान है, खासकर अगर आधार घड़ी के आसपास काम करता है और लगातार उच्च भार के अधीन है। (टिप्पणी के बाद अनफिल्टर्ड बार को 8 पेज तक बढ़ा दिया गया)
avg_fragmentation_in_percent> 10 - यह भी एक बहुत ही व्यक्तिपरक आंकड़ा है, व्यावहारिक रूप से सभी प्रलेखन में वे सूचकांक को छूने का सुझाव नहीं देते हैं यदि इसका विखंडन 10 प्रतिशत या उससे कम है, जो मैं सहमत हूं, यदि आपकी स्थिति अलग है, तो हम इसे बदलते हैं।
dm.index_id> 0 - 0 एक गुच्छा है

तालिका भर जाने के बाद, हम जानते हैं कि किन अनुक्रमों की सेवा करने की आवश्यकता है।
चलो सौदा करते हैं:

 --   DECLARE @partitioncount INT --  DECLARE @action VARCHAR(10) --,       DECLARE @start_time DATETIME --   ALTER INDEX DECLARE @end_time DATETIME --   ALTER INDEX --   DECLARE @object_id INT DECLARE @index_id INT DECLARE @tableName VARCHAR(250) DECLARE @indexName VARCHAR(250) DECLARE @defrag FLOAT DECLARE @partition_num INT DECLARE @fill_factor INT -- ,    ,   MAX,      ,      ,    . DECLARE @sql NVARCHAR(MAX) --   DECLARE defragCur CURSOR FOR SELECT [object_id], index_id, table_name, index_name, avg_frag_percent_before, fill_factor, partition_num FROM dba_tasks.dbo.index_defrag_statistic WHERE proc_id = @currentProcID ORDER BY [object_id], index_id DESC --    OPEN defragCur FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num WHILE @@FETCH_STATUS=0 BEGIN SET @sql = N'ALTER INDEX ' + @indexName + ' ON ' + @tableName SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @object_id AND index_id = @index_id; --  ,       , ,       ,           IF (@fill_factor != 80) BEGIN @sql = @sql + N' REBUILD WITH (FILLFACTOR = 80)' SET @action = 'rebuild80' END ELSE BEGIN --  ,    MS IF (@defrag > 30) --   30%,  REBUILD BEGIN SET @sql = @sql + N' REBUILD' SET @action = 'rebuild' END ELSE --   REORGINIZE BEGIN SET @sql = @sql + N' REORGANIZE' SET @action = 'reorginize' END END --    IF @partitioncount > 1 SET @sql = @sql + N' PARTITION=' + CAST(@partition_num AS nvarchar(5)) print @sql --   --   SET @start_time = GETDATE() EXEC sp_executesql @sql --   SET @end_time = GETDATE() --    UPDATE dba_tasks.dbo.index_defrag_statistic SET start_time = @start_time, end_time = @end_time, [action] = @action WHERE proc_id = @currentProcID AND [object_id] = @object_id AND index_id = @index_id FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num END CLOSE defragCur DEALLOCATE defragCur 

और अंत में, हम डीफ़्रैग्मेन्टेशन प्रक्रिया के बाद सूचकांकों के बारे में जानकारी एकत्र करते हैं:

 UPDATE dba SET dba.avg_frag_percent_after = dm.avg_fragmentation_in_percent, dba.fragment_count_after = dm.fragment_count, dba.pages_count_after = dm.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm INNER JOIN dba_tasks.dbo.index_defrag_statistic dba ON dm.[object_id] = dba.[object_id] AND dm.index_id = dba.index_id WHERE dba.proc_id = @currentProcID AND dm.index_id > 0 

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

मैं यह भी नोट करना चाहता हूं कि इंटरनेट पर विस्तृत स्क्रिप्ट हैं, लेकिन सावधान रहें, उनमें से कई पुरानी कमांड का उपयोग करते हैं।

मेरे द्वारा उपयोग किए जाने वाले सिस्टम अभ्यावेदन के बारे में अधिक जानकारी msdn में मिल सकती है:

sys.sysindexes
sys.tables
sys.dm_db_index_physical_stats