👍🏼 ☯️ 💅🏽 SQL सर्वर में विभाजन 🧑🏿‍🤝‍🧑🏽 🚟 🎮

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

जैसा कि एमएसडीएन बताता है: “विभाजित टेबल और इंडेक्स के डेटा को उन ब्लॉकों में विभाजित किया जाता है जिन्हें डेटाबेस में कई फाइलग्रुप में वितरित किया जा सकता है। डेटा को क्षैतिज रूप से विभाजित किया जाता है, इसलिए पंक्ति समूहों को अलग-अलग वर्गों में मैप किया जाता है। एक ही सूचकांक या तालिका के सभी खंड एक ही डेटाबेस में होने चाहिए। डेटा पर प्रश्नों या अपडेट को निष्पादित करते समय एक टेबल या इंडेक्स को एक तार्किक इकाई माना जाता है। "

मुख्य लाभ भी वहाँ सूचीबद्ध हैं:

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

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

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

शेष लाभों को प्राप्त करने के लिए (तुरंत वर्गों को बढ़ाएं; उत्पादकता बढ़ाएं) - आपको विशेष रूप से डेटा संरचना को डिज़ाइन करने और प्रश्नों को लिखने की आवश्यकता है।
मुझे लगता है कि मैंने पहले ही पाठक को काफी शर्मिंदा कर दिया है और अब मैं अभ्यास करने के लिए आगे बढ़ सकता हूं।

सबसे पहले, 4 फ़ाइल समूहों के साथ एक डेटाबेस बनाएँ जिसमें हम प्रयोग करेंगे:

create database [PartitionTest] on primary (name ='PTestPrimary', filename = 'E:\data\partitionTestPrimary.mdf', size = 8092KB, filegrowth = 1024KB) , filegroup [fg1] (name ='PTestFG1', filename = 'E:\data\partitionTestFG1.ndf', size = 8092KB, filegrowth = 1024KB) , filegroup [fg2] (name ='PTestFG2', filename = 'E:\data\partitionTestFG2.ndf', size = 8092KB, filegrowth = 1024KB) , filegroup [fg3] (name ='PTestFG3', filename = 'E:\data\partitionTestFG3.ndf', size = 8092KB, filegrowth = 1024KB) log on (name = 'PTest_Log', filename = 'E:\data\partitionTest_log.ldf', size = 2048KB, filegrowth = 1024KB); go alter database [PartitionTest] set recovery simple; go use partitionTest;

एक तालिका बनाएं जिसे हम पीड़ा देंगे।

 create table ptest (id int identity(1,1), dt datetime, dummy_int int, dummy_char char(6000));

और इसे एक वर्ष के लिए डेटा के साथ भरें:

 ;with nums as ( select 0 n union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 ) insert into ptest(dt, dummy_int, dummy_char) select dateadd(hh, rn-1, '20180101') dt, rn dummy_int, 'dummy char column #' + cast(rn as varchar) from ( select row_number() over(order by (select (null))) rn from nums n1, nums n2, nums n3, nums n4 )t where rn < 8761

अब pTest तालिका में 2018 के प्रत्येक घंटे के लिए एक रिकॉर्ड है।

अब आपको एक विभाजन फ़ंक्शन बनाने की आवश्यकता है जो डेटा को अनुभागों में विभाजित करने के लिए सीमा की स्थिति का वर्णन करता है। SQL सर्वर केवल श्रेणी विभाजन का समर्थन करता है।

हम अपनी तालिका को dt (डेटाटाइम) कॉलम द्वारा विभाजित करेंगे ताकि प्रत्येक अनुभाग में 4 महीने का डेटा हो (यहां मैंने खराब कर दिया है - वास्तव में, पहले खंड में 3 के लिए डेटा होगा, 4 के लिए दूसरा, 5 महीने के लिए तीसरा, लेकिन प्रदर्शन उद्देश्यों के लिए - यह कोई समस्या नहीं है)

 create partition function pfTest (datetime) as range for values ('20180401', '20180801')

सब कुछ सामान्य लग रहा है, लेकिन यहाँ मैंने जानबूझकर एक "गलती" की है। यदि आप msdn में वाक्यविन्यास को देखते हैं, तो आप देखेंगे कि निर्माण के दौरान आप निर्दिष्ट कर सकते हैं कि निर्दिष्ट सीमा किस खंड से - बाईं ओर या दाईं ओर होगी। डिफ़ॉल्ट रूप से, किसी अज्ञात कारण से, निर्दिष्ट बॉर्डर "लेफ्ट" अनुभाग को संदर्भित करता है, इसलिए मेरे मामले के लिए यह विभाजन फ़ंक्शन बनाने के लिए निम्नानुसार होगा:

 create partition function pfTest (datetime) as range right for values ('20180401', '20180801')

जबकि मैं वास्तव में निष्पादित किया गया था:

 create partition function pfTest (datetime) as range left for values ('20180401', '20180801')

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

विभाजन फ़ंक्शन बनाने के बाद, आपको एक विभाजन योजना बनाने की आवश्यकता है। यह स्पष्ट रूप से फाइलग्रुप के वर्गों को बांधता है:

 create partition scheme psTest as partition pfTest to ([FG1], [FG2], [FG3])

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

 create clustered index cix_pTest_id on pTest(id) on psTest(dt)

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

अब देखते हैं कि हमें वर्तमान कॉन्फ़िगरेशन में क्या मिला ( अनुरोध यहां से लिया गया है ):

 SELECT sc.name + N'.' + so.name as [Schema.Table], si.index_id as [Index ID], si.type_desc as [Structure], si.name as [Index], stat.row_count AS [Rows], stat.in_row_reserved_page_count * 8./1024./1024. as [In-Row GB], stat.lob_reserved_page_count * 8./1024./1024. as [LOB GB], p.partition_number AS [Partition #], pf.name as [Partition Function], CASE pf.boundary_value_on_right WHEN 1 then 'Right / Lower' ELSE 'Left / Upper' END as [Boundary Type], prv.value as [Boundary Point], fg.name as [Filegroup] FROM sys.partition_functions AS pf JOIN sys.partition_schemes as ps on ps.function_id=pf.function_id JOIN sys.indexes as si on si.data_space_id=ps.data_space_id JOIN sys.objects as so on si.object_id = so.object_id JOIN sys.schemas as sc on so.schema_id = sc.schema_id JOIN sys.partitions as p on si.object_id=p.object_id and si.index_id=p.index_id LEFT JOIN sys.partition_range_values as prv on prv.function_id=pf.function_id and p.partition_number= CASE pf.boundary_value_on_right WHEN 1 THEN prv.boundary_id + 1 ELSE prv.boundary_id END /* For left-based functions, partition_number = boundary_id, for right-based functions we need to add 1 */ JOIN sys.dm_db_partition_stats as stat on stat.object_id=p.object_id and stat.index_id=p.index_id and stat.index_id=p.index_id and stat.partition_id=p.partition_id and stat.partition_number=p.partition_number JOIN sys.allocation_units as au on au.container_id = p.hobt_id and au.type_desc ='IN_ROW_DATA' /* Avoiding double rows for columnstore indexes. */ /* We can pick up LOB page count from partition_stats */ JOIN sys.filegroups as fg on fg.data_space_id = au.data_space_id ORDER BY [Schema.Table], [Index ID], [Partition Function], [Partition #];

इस प्रकार, हमें तीन नहीं बहुत सफल अनुभाग मिले - पहला भंडार समय की शुरुआत से 04/01/2018 00:00:00 समावेशी, दूसरा - 01/01/2018 से 00:00:01 से 08/01/2018 00:00:00 समावेशी, तक तीसरी दुनिया के अंत तक 08/01/2018 00:00:01 से (मैंने जानबूझकर दूसरे के अंश को याद किया, क्योंकि मुझे याद नहीं है कि एसक्यूएल सर्वर इन अंशों को क्या लिखता है, लेकिन अर्थ सही तरीके से प्रसारित होता है)।
अब एक ही विभाजन योजना के अनुसार डमी_इंट फ़ील्ड पर एक गैर-संकुल सूचकांक बनाएं, "गठबंधन"।

हमें एक संरेखित सूचकांक की आवश्यकता क्यों है?

हमें एक संरेखित सूचकांक की आवश्यकता है ताकि हम एक खंड (स्विच) को स्विच करने का कार्य कर सकें - और यह उन कार्यों में से एक है जिसके लिए, अक्सर, वे विभाजन से परेशान होते हैं। यदि तालिका में कम-से-कम एक अनलिंकड इंडेक्स है, तो आप अनुभाग को स्विच नहीं कर सकते

 create nonclustered index nix_pTest_dummyINT on pTest(dummy_int) on psTest(dt);

और देखते हैं कि मैंने यह क्यों कहा कि सेक्शन के लागू होने के बाद आपके प्रश्न धीमे हो सकते हैं। अनुरोध चलाएँ:

 SET STATISTICS TIME, IO ON; select id from pTest where dummy_int = 54 SET STATISTICS TIME, IO OFF;

और चलो निष्पादन आँकड़े देखें:

 Table 'ptest'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

और कार्यान्वयन योजना:

चूँकि हमारा सूचकांक वर्गों द्वारा "संरेखित" किया गया है, सशर्त रूप से, प्रत्येक अनुभाग का अपना सूचकांक है, जो अन्य वर्गों पर अनुक्रमित के साथ "असंबद्ध" है। हमने उस क्षेत्र पर शर्तों को लागू नहीं किया है जिसके द्वारा सूचकांक का विभाजन किया जाता है, इसलिए SQL सर्वर प्रत्येक अनुभाग में सूचकांक तलाश को निष्पादित करने के लिए मजबूर होता है, वास्तव में, एक के बजाय 3 सूचकांक की तलाश है।

आइए एक सेक्शन को बाहर करने की कोशिश करें:

 SET STATISTICS TIME, IO ON; select id from pTest where dummy_int = 54 and dt < '20180801' SET STATISTICS TIME, IO OFF;

और चलो निष्पादन आँकड़े देखें:

 Table 'ptest'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

हाँ, एक खंड को बाहर रखा गया था और वांछित मूल्य की खोज केवल दो खंडों में की गई थी।
यह कुछ ऐसा है जिसे विभाजन पर निर्णय लेते समय याद रखना चाहिए। यदि आपके पास ऐसे प्रश्न हैं जो उस फ़ील्ड पर प्रतिबंध का उपयोग नहीं करते हैं जिसके द्वारा तालिका का विभाजन किया जाता है, तो आपको समस्या हो सकती है।

हमें अब गैर-संकुल सूचकांक की आवश्यकता नहीं है, इसलिए मैं इसे हटा देता हूं

 drop index nix_pTest_dummyINT on pTest;

और गैर-क्लस्टर सूचकांक की आवश्यकता क्यों थी?

सामान्य तौर पर, मुझे इसकी आवश्यकता नहीं थी, मैं क्लस्टर इंडेक्स के साथ एक ही चीज़ दिखा सकता था, मुझे नहीं पता कि मैंने इसे क्यों बनाया है, लेकिन जब से मैंने इसे बनाया है और स्क्रीनशॉट बनाया है - तब तक अच्छा नहीं खोता

अब निम्नलिखित परिदृश्य पर विचार करें: हम हर 4 महीने में इस तालिका से डेटा संग्रहीत करते हैं - हम पुराने डेटा को हटाते हैं और अगले चार महीनों के लिए एक खंड जोड़ते हैं ("स्लाइडिंग विंडो" का संगठन एमएसडीएन और ब्लॉग्स के ढेर में वर्णित है)।

हम कार्य को छोटे और समझने योग्य उप-भागों में विभाजित करते हैं:

01/01/2019 से 04/01/2019 तक डेटा के लिए एक अनुभाग जोड़ें
एक खाली मंच तालिका बनाएँ
मंच तालिका में 04/01/2018 तक डेटा अनुभाग स्विच करें
खाली खंड से छुटकारा पाएं

आइए जाने:

1. हम घोषणा करते हैं कि नया खंड FG1 फाइलग्रुप में बनाया जाएगा, क्योंकि यह जल्द ही हमसे मुक्त हो जाएगा:

 alter partition scheme psTest next used [FG1];

और एक नई सीमा जोड़कर विभाजन फ़ंक्शन को बदलें:

 SET STATISTICS TIME, IO ON; alter partition function pfTest() split range ('20190101'); SET STATISTICS TIME, IO OFF;

हम आंकड़ों को देखते हैं:

 Table 'ptest'. Scan count 1, logical reads 76171, physical reads 0, read-ahead reads 753, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 1, logical reads 7440, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

सामान्य तौर पर, सब कुछ अपेक्षित था - ऊपरी सीमा के साथ एक नया खंड दिखाई दिया (याद रखें कि हमारे लिए सीमा की स्थिति बाएं खंड से संबंधित है) 01/01/2019 और एक खाली अनुभाग जिसमें एक लंबी तारीख के साथ अन्य डेटा होगा।

सब कुछ ठीक लग रहा है, लेकिन इतने सारे रीडिंग क्यों हैं? हम ऊपर दिए गए आंकड़े को ध्यान से देखते हैं, और देखते हैं कि FG3 में मौजूद तीसरे खंड का डेटा FG1 में समाप्त हो गया है, लेकिन अगला खंड, FG3 में खाली है।

2. एक स्टेज टेबल बनाएं।

एक टेबल पर एक सेक्शन को स्विच (स्विच) करने के लिए और इसके विपरीत, हमें एक खाली टेबल की आवश्यकता होती है जिसमें हमारे विभाजन तालिका के समान सभी प्रतिबंध और सूचकांक बनाए जाते हैं। टेबल उसी फाइलग्रुप में होनी चाहिए, जिस खंड को हम वहां "स्विच" करना चाहते हैं। पहला (संग्रहीत) खंड FG1 में निहित है, इसलिए हम उसी स्थान पर एक तालिका और एक क्लस्टर इंडेक्स बनाते हैं:

 create table stageTest (id int identity(1,1), dt datetime, dummy_int int, dummy_char char(6000)) ; create clustered index cix_stageTest_id on stageTest(id) on [FG1];

आपको इस तालिका को विभाजित करने की आवश्यकता नहीं है।

3. अब हम स्विच करने के लिए तैयार हैं:

 SET STATISTICS TIME, IO ON; alter table pTest switch partition 1 to stageTest SET STATISTICS TIME, IO OFF;

और यहाँ है कि हम क्या मिलता है:

  4947,  16,  1,  59 ALTER TABLE SWITCH statement failed. There is no identical index in source table 'PartitionTest.dbo.pTest' for the index 'cix_stageTest_id' in target table 'PartitionTest.dbo.stageTest' .

मजेदार, आइए देखें कि सूचकांकों में हमारे पास क्या है:

 select o.name tblName, i.name indexName, c.name columnName, ic.is_included_column from sys.indexes i join sys.objects o on i.object_id = o.object_id join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id join sys.columns c on ic.column_id = c.column_id and o.object_id = c.object_id where o.name in ('pTest', 'stageTest')

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

 create clustered index cix_stageTest_id on stageTest(id, dt) with (drop_existing = on) on [FG1];

और अब, एक बार फिर, हम अनुभाग को बदलने की कोशिश करते हैं:

 SET STATISTICS TIME, IO ON; alter table pTest switch partition 1 to stageTest SET STATISTICS TIME, IO OFF;

ता डैम! अनुभाग स्विच किया गया है, देखें कि यह हमारी लागत क्या है:

 SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 3 ms.

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

आइए देखें कि हमारे अनुभागों के साथ क्या है:

और उनके साथ सब कुछ महान है। पहले खंड में, शून्य रिकॉर्ड बचे हैं, वे सुरक्षित रूप से स्टेजटेस्ट टेबल के लिए रवाना हो गए। हम आगे बढ़ सकते हैं

4. हमारे लिए जो कुछ भी बचा है, वह है हमारा खाली पहला खंड हटाना। आइए करते हैं और देखते हैं कि क्या होता है:

 SET STATISTICS TIME, IO ON; alter partition function pfTest() merge range ('20180401'); SET STATISTICS TIME, IO OFF;

और यह भी केवल हमारे मामले में मेटाडेटा पर एक ऑपरेशन है। हम वर्गों को देखते हैं:

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

हम पहले के साथ कुछ भी नहीं कर सकते, लेकिन दूसरे के साथ हम कोशिश करेंगे। आइए एक नया खंड बनाएं जिसमें 01/01/2019 से 04/01/2019 तक का डेटा होगा, और समय समाप्त होने तक नहीं:

 alter partition scheme psTest next used [FG2]; SET STATISTICS TIME, IO ON; alter partition function pfTest() split range ('20190401'); SET STATISTICS TIME, IO OFF;

और हम देखते हैं:

 SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

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

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

डेटा के लिए बाईं ओर एक नया अनुभाग बनाएं
तालिका को इस अनुभाग में स्विच करें

हम कोशिश करते हैं (और उस चरण को याद रखें FG1 में):

 alter partition scheme psTest next used [FG1]; SET STATISTICS TIME, IO ON; alter partition function pfTest() split range ('20180401'); SET STATISTICS TIME, IO OFF;

हम देखते हैं:

 Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'ptest'. Scan count 1, logical reads 2939, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

खैर, बुरा नहीं है, अर्थात्। केवल बाएं खंड को पढ़ें (जिसे हम विभाजित करते हैं) और वह है। ठीक है। विभाजन-रहित गैर-रिक्त तालिका को एक विभाजित तालिका अनुभाग में बदलने के लिए, स्रोत तालिका में प्रतिबंध होने चाहिए ताकि SQL सर्वर को पता चले कि सबकुछ ठीक हो जाएगा और मेटाडेटा पर एक ऑपरेशन के रूप में स्विच किया जा सकता है (बजाय एक पंक्ति में सब कुछ पढ़ने और जाँचने के लिए कि क्या अनुभाग शर्तों से मेल खाता है या नहीं। ):

 alter table stageTest add constraint check_dt check (dt <= '20180401')

स्विच करने की कोशिश कर रहा है:

 SET STATISTICS TIME, IO ON; alter table stageTest switch to pTest partition 1 SET STATISTICS TIME, IO OFF;

आँकड़े:

  SQL Server Execution Times: CPU time = 15 ms, elapsed time = 39 ms.

फिर, ऑपरेशन केवल मेटाडेटा पर है। हम देखते हैं कि हमारे वर्गों के साथ क्या है:

ठीक है। ऐसा लगता है। और अब हम फंक्शन और पार्टीशन स्कीम को फिर से बनाने की कोशिश करेंगे (मैंने विभाजन स्कीम और फंक्शन को डिलीट कर दिया, टेबल को रीक्रिएट और रिफिल किया और नई पार्टीशन स्कीम का उपयोग करके क्लस्टर इंडेक्स बनाया):

 create partition function pfTest (datetime) as range right for values ('20180401', '20180801')

आइए देखें कि अब हमारे पास कौन-कौन से वर्ग हैं:

खैर, अब हमारे पास तीन "तार्किक" खंड हैं - समय की शुरुआत से 04/01/2018 00:00:00 (समावेशी नहीं), 04/01/2018 से 00:00:00 (समावेशी) से 08/01/2018 00:00:00 ( समावेशी नहीं) और तीसरा, सब कुछ जो 01/01/2018 00:00:00 से अधिक या उसके बराबर है।

अब डेटा को संग्रह करने का वही कार्य करने का प्रयास करते हैं जो हमने पिछले विभाजन फ़ंक्शन के साथ किया था।

1. एक नया खंड जोड़ें:

 alter partition scheme psTest next used [FG1]; SET STATISTICS TIME, IO ON; alter partition function pfTest() split range ('20190101'); SET STATISTICS TIME, IO OFF;

हम आंकड़ों को देखते हैं:

 Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'ptest'. Scan count 1, logical reads 3685, physical reads 0, read-ahead reads 4, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

बुरा नहीं है, कम से कम यथोचित - केवल अंतिम अनुभाग पढ़ें। हम देखते हैं कि हमारे पास वर्गों में क्या है:

ध्यान दें कि अब, पूरा तीसरा खंड FG3 में जगह पर बना हुआ है, और FG1 में एक नया खाली खंड बनाया गया है।

2. हम एक मंच तालिका और उस पर कॉर्रक्ट क्लस्टर इंडेक्स बनाते हैं

 create table stageTest (id int identity(1,1), dt datetime, dummy_int int, dummy_char char(6000)) ; create clustered index cix_stageTest_id on stageTest(id, dt) on [FG1];

3. स्विच खंड

 SET STATISTICS TIME, IO ON; alter table pTest switch partition 1 to stageTest SET STATISTICS TIME, IO OFF;

आंकड़े कहते हैं कि मेटाडेटा ऑपरेशन है:

 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 5 ms.

अब, सभी आश्चर्य के बिना।

4. अनावश्यक अनुभाग निकालें

 SET STATISTICS TIME, IO ON; alter partition function pfTest() merge range ('20180401'); SET STATISTICS TIME, IO OFF;

और यहाँ हमारे पास एक आश्चर्य है:

 Table 'ptest'. Scan count 1, logical reads 27057, physical reads 0, read-ahead reads 251, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

हम देखते हैं कि हमारे पास वर्गों के साथ क्या है:

और यहाँ यह स्पष्ट हो जाता है: हमारा खंड # 2 fg2 फ़ाइल समूह से fg1 फ़ाइल समूह में स्थानांतरित हो गया। क्लास। क्या हम इस बारे में कुछ कर सकते हैं?

हो सकता है कि हमें हमेशा एक खाली खंड और "कभी खाली" छोड़ दिया अनुभाग और उस खंड के बीच की सीमा को "नष्ट" करने की आवश्यकता हो जिसे हमने "तालिका" में बदल दिया था।

निष्कर्ष में:

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

आशा है कि सामग्री सहायक थी। शायद यह उखड़ गया, अगर आपको लगता है कि घोषित कुछ का खुलासा नहीं किया गया है, तो लिखिए, मैं इसे खत्म करने की कोशिश करूंगा। आपका ध्यान के लिए धन्यवाद।

SQL सर्वर में विभाजन

More articles: