SQL Server 2014 में इन-मेमोरी ओएलटीपी। भाग II

पहले भाग में, हमने एनालिटिक और ट्रांसेक्शनल और एप्लीकेशन के संबंध में इन-मेमोरी प्रोसेसिंग के लिए SQL सर्वर की मुख्य विशेषताओं की संक्षिप्त रूप से जाँच की, जो बाद वाले पर ध्यान केंद्रित कर रहा है, क्योंकि SQL Server 2014 में इन-मेमोरी OLTP (हेकोटन) सबसे महत्वपूर्ण नवाचार है। इस लेख में, हम समीक्षा जारी रखते हैं। पहले से निर्मित डेटाबेस के उदाहरण पर हेकोटन कार्यक्षमता।



चूँकि हम मेमोरी में ट्रांसेक्शनल प्रोसेसिंग के बारे में बात कर रहे हैं, इसलिए यह परिचित एसीआईडी ​​को याद करने के लिए उपयोगी होगा: परमाणु, अखंडता, अलगाव और अवशिष्टता (प्रभाव), विशेष रूप से, पत्र I। चूंकि यह पूर्ण अलगाव को प्राप्त करने के लिए लाभहीन है, DBMS अलगाव स्तर का समर्थन करता है जो कुछ हद तक समझौता करने की अनुमति देता है। । यह शैली का एक क्लासिक है। जैसे ही यह आदर्श के करीब पहुंचता है, SQL सर्वर समर्थन करता है: गंदे पढ़ने (फॉर्म के उल्लंघन की अनुमति है: पहला लेनदेन डेटा बदलता है, दूसरा परिवर्तन पढ़ता है, पहला रोल वापस आता है, यह पता चलता है कि दूसरा कोई नहीं पढ़ता है), पढ़े हुए प्रतिबद्ध (संभवतः गैर-दोहराए गए रीडिंग: पहला लेनदेन डेटा को बदल सकता है। दूसरे पढ़ने के पैमाने पर, और दूसरी बार दूसरी लेन-देन डेटा को पहले से ही बदले हुए पढ़ेगा), दोहराए जाने योग्य रीड (पहला लेन-देन रिकॉर्ड को दूसरे पैमाने पर नहीं बदल सकता है, लेकिन नए को जोड़ सकता है - प्रेत), सीरियल करने योग्य (सबसे गंभीर - प्रेत नहीं) चारों ओर जाना)। एक बार, जब SQL सर्वर में पेज 2 किलोबाइट थे, केवल पेज लॉक का समर्थन किया गया था, इसलिए अंतिम दो स्तर पर्याय थे। लेखन लॉक के लिए पूर्ण समर्थन 7.0 (1998) में दिखाई दिया। 2005 में, स्नैपशॉट अलगाव को इन स्तरों में जोड़ा गया था, जो कड़ाई से बोल रहा है, एक अलगाव स्तर नहीं है, लेकिन SQL सर्वर को लॉकर से एक संस्करण में बदल देता है। हेकोटन के लिए बस एक स्नैपशॉट मुख्य है, क्योंकि मेमोरी में ऑब्जेक्ट्स के साथ काम करते समय आशावादी संगामिता सबसे प्राकृतिक दृष्टिकोण है। मेरा मतलब है - संक्षेप में, क्योंकि इस मामले में प्रविष्टियों के संस्करण tempdb में संग्रहीत नहीं हैं। प्रत्येक लेखन ऑपरेशन में एक निरंतर बढ़ती हुई लेनदेन संख्या होती है, जिसका उपयोग बाद के रीडिंग के लिए किया जाता है। Uncommitted रिकॉर्ड स्मृति में संग्रहीत किए जाते हैं, लेकिन एक प्रतिबद्ध होने तक दिखाई नहीं देते हैं, इसलिए कोई गंदे रीड नहीं हैं। कचरा संग्रहण प्रक्रिया और मुफ्त मेमोरी के दौरान पुराने रिकॉर्ड हटा दिए जाते हैं।

Hecaton रिपीटेबल रीड का भी समर्थन करता है, जो (डिस्क संस्करण के विपरीत) किसी को ब्लॉक नहीं करता है। जब लेन-देन के अंत में कहीं फिर से रीडिंग होती है, तो यदि रिकॉर्ड बदल गए हैं, तो लेन-देन को त्रुटि के साथ रद्द कर दिया जाता है 41305 "वर्तमान लेनदेन टेबल पर एक बार-बार पढ़ने योग्य सत्यापन विफलता [नाम] के कारण करने में विफल रहा।" Serializable उसी तरह काम करती है - प्रेत के मामले में, लेनदेन रद्द हो जाता है।

अलगाव स्तर देशी संकलित संग्रहीत कार्यविधि (नीचे देखें) के टीओएमआईसी ब्लॉक में या टी-एसक्यूएल क्वेरी के तालिका संकेत में सेट किया गया है। एक विकल्प के रूप में - डेटाबेस विकल्प का उपयोग करते हुए MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, जो स्मृति में तालिकाओं का उपयोग करते समय, स्नैपशॉट के लिए UnCommitted / Committed को उठाता है। अगर यह लिखने का रिवाज है

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN UPDATE dbo.UserSession SET ShoppingCartId=3 WHERE SessionId=4
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

एक त्रुटि होगी Msg 41333, लेवल 16, स्टेट 1, लाइन 3

निम्नलिखित लेन-देन में मेमोरी ऑप्टिमाइज़्ड तालिकाओं का उपयोग करना चाहिए और स्नैपशॉट अलगाव के तहत मूल रूप से संकलित प्रक्रियाओं को फिर से संकलित करना चाहिए: repeatableRead लेन-देन, Serializable लेन-देन, और लेन-देन जो एक्सेस टेबल का उपयोग करते हैं जो कि RepableRead या Serializable अलगाव में अनुकूलित मेमोरी नहीं होती है।



T-SQL स्क्रिप्ट में, अलगाव स्तर संकेत द्वारा निर्धारित किया गया है:

 BEGIN TRAN UPDATE dbo.UserSession WITH (SNAPSHOT) SET ShoppingCartId=3 WHERE SessionId=4 UPDATE dbo.ShoppingCart WITH (SNAPSHOT) SET TotalPrice=100.00 WHERE ShoppingCartId=3 COMMIT (1 row(s) affected) (1 row(s) affected)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

सैद्धांतिक रूप से, मेमोरी अनुकूलित तालिकाओं के लिए प्रतिबद्ध पढ़ें भी समर्थित है, लेकिन केवल ऑटोकॉमिट (एकल स्टेटमेंट) लेनदेन के मामले में।

इसी समय, अवरुद्ध करने की लागत न्यूनतम है, जिसके लिए, वास्तव में, सब कुछ शुरू किया गया था।

 BEGIN TRAN INSERT dbo.ShoppingCart VALUES (5,500,GETUTCDATE(),50.5) SELECT resource_type, resource_description, object_name(resource_associated_entity_id), request_mode, request_type, request_status FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ध्यान दें कि Hecaton टेबल पर केवल स्कीमा स्थिरता को "लटका" देता है। कोई X और IX का उल्लेख नहीं किया गया है:





चित्र 1



तुलना के लिए, आइए याद करें कि पारंपरिक तालिकाओं के साथ क्या होता है। एक समान डिस्क तालिका बनाएं, पंक्तियों के समान सेट को प्रीफिल के रूप में डालें और लेनदेन दोहराएं:

 CREATE TABLE dbo.ShoppingCart_Disk ( ShoppingCartId int not null primary key, UserId int not null, CreatedDate datetime2 not null, TotalPrice money, INDEX ixUserid nonclustered (UserId)) ... BEGIN TRAN INSERT dbo.ShoppingCart_Disk VALUES (5,500,GETUTCDATE(),50.5) SELECT resource_type, resource_description, case resource_type when 'object' then object_name(resource_associated_entity_id) else cast(resource_associated_entity_id as sysname) end, resource_associated_entity_id, request_mode, request_type, request_status FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

क्या कहा जाता है, अंतर महसूस करें:





Fig.2



न्यूनतम अवरोध Hecaton के स्पष्ट लाभों में से एक है। यदि ओएलटीपी शैली में निष्पादित किया जाता है, तो एक एप्लिकेशन मेमोरी से ट्रांसफर से लाभान्वित होगा, अर्थात। कई कनेक्शनों से प्रतिस्पर्धा के एक उच्च प्रतिशत के साथ अपेक्षाकृत कम लेनदेन की विशेषता है, और समस्या यह है कि ताले या भौतिक कुंडी लंबे समय तक लटकाते हैं और संसाधनों को जारी नहीं करते हैं। यदि संग्रहीत प्रक्रियाओं का उपयोग किया जाता है, तो पोर्टिंग आसान हो जाएगा, और प्रदर्शन समस्याओं को तालिकाओं / संग्रहीत प्रक्रियाओं के अपेक्षाकृत छोटे सबसेट में परिवर्तित किया जा सकता है। बेशक, सभी परिदृश्य नहीं जीतते हैं। सीमित करने का मामला एक रिकॉर्ड से एक तालिका है, जिसे हर कोई एक नए मूल्य के साथ अपडेट करता है। हर चीज और हर चीज को स्मृति में धकेलने की इच्छा इस तथ्य को जन्म देगी कि यह मूर्खतापूर्ण रूप से समाप्त हो जाएगी। वैसे, वे अक्सर सवाल पूछते हैं, अगर हेकोटन ने उन्हें आवंटित सभी मेमोरी को नष्ट कर दिया तो क्या होगा? सामान्य तौर पर, यह स्पष्ट है कि कुछ भी अच्छा नहीं है: Msg 701, स्तर 17, राज्य 103। इस क्वेरी को चलाने के लिए संसाधन पूल 'डिफ़ॉल्ट' में अपर्याप्त सिस्टम मेमोरी है। इस मामले में क्या करना है आमतौर पर कुछ भी नहीं है। SQL सर्वर स्मृति को बहुत तेज़ी से साफ़ करता है, और रिलीज़ होने पर मैं अभी भी इसे एक स्तूप में ड्राइव नहीं कर सकता, जिसे केवल पुनरारंभ (CTP के विपरीत) द्वारा व्यवहार किया जाता है। कचरा संग्रह को गैर-अवरुद्ध और सहकारी के रूप में डिज़ाइन किया गया है, जिसका अर्थ है कि इन उद्देश्यों के लिए विशेष प्रवाह के बावजूद, अधिकांश कार्य उपयोगकर्ता प्रक्रियाओं द्वारा किया जाता है, जो समय-समय पर जांच और बाद के विलोपन के लिए रिकॉर्ड चिह्नित करते हैं, और फिर उन्हें हटाते हैं। उपयोगकर्ता गतिविधि बहुत कम होने पर समर्पित स्ट्रीम सक्रिय हो जाती है

काम के दौरान अतिप्रवाह से कैसे बचें? स्पष्ट रूप से, तालिकाओं को मेमोरी में स्थानांतरित करते समय वास्तविकता का मूल्यांकन करना सही है, खासकर जब से किसी ने भी रद्द नहीं किया है।

आप मेमोरी ऑप्टिमाइज़ेशन सलाहकार का उपयोग कर सकते हैं, जो संभावित समस्याओं के लिए स्कीमा, इंडेक्स, आदि का स्थैतिक विश्लेषण करता है और उपयोग की प्रकृति द्वारा गतिशील विश्लेषण के लिए एक अधिक बुद्धिमान एएमआर (विश्लेषण, माइग्रेट और रिपोर्ट) लेता है (आँकड़े लेता है)

प्रदर्शन डेटा वेयरहाउस)। आप संसाधन नियंत्रक का उपयोग करके हेक्टन को लगातार सीमित कर सकते हैं:

 CREATE RESOURCE POOL mem_optpool WITH (MAX_MEMORY_PERCENT =   ) EXEC sp_xtp_bind_db_resource_pool 'mydatabase', 'mem_optpool'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

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

 select type, name, memory_node_id, pages_kb/1024 as pages_MB from sys.dm_os_memory_clerks where type like '%xtp%'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

पहला कॉमरेड सिस्टम द्वारा आवंटित मेमोरी होगा, अंतिम (मेमोरी_नोड = 64) आवंटित प्रशासनिक कनेक्शन है, और उनके बीच MEMORY_OPTIMIZED_DATA के साथ उपयोगकर्ता डेटाबेस हैं।



जैसा कि पहले भाग में बताया गया है, यह पूरे आधार को मेमोरी में बंद करने के लिए पूरी तरह से वैकल्पिक है। स्मृति और परिचित तालिकाओं के बीच संयुक्त क्वेरी संभव है, उदाहरण के लिए,

 select m1.ShoppingCartId, m2.UserId, d.CreatedDate, d.TotalPrice from ShoppingCart m1 join ShoppingCart_Disk d on m1.ShoppingCartId = d.ShoppingCartId join UserSession m2 on d.ShoppingCartId = m2.ShoppingCartId delete from ShoppingCart insert ShoppingCart select * from ShoppingCart_Disk update d set TotalPrice = m.TotalPrice from ShoppingCart_Disk d join ShoppingCart m on d.ShoppingCartId = m.ShoppingCartId where m.UserID <= 100
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

लिपि १



तालिकाओं के अलावा, संग्रहीत कार्यविधियाँ मेमोरी में संग्रहीत की जा सकती हैं। ऐसी प्रक्रियाएं केवल इन-मेमोरी टेबल के साथ काम कर सकती हैं। एक प्रक्रिया आवश्यक रूप से SCHEMABINDING विकल्प के साथ बनाई गई है, जिसका अर्थ है कि प्रक्रिया द्वारा संदर्भित तालिकाओं को संशोधित या उससे पहले हटाया नहीं जा सकता है। पारंपरिक डिफ़ॉल्ट प्रक्रियाओं को EXECUTE AS CALLER विकल्प के साथ निष्पादित किया जाता है। स्मृति में प्रक्रियाओं के लिए, यह समर्थित नहीं है। विकल्प इस प्रकार हैं: OWNER के रूप में, 'उपयोगकर्ता_नाम' के रूप में जाँचें या SELF (निर्माता) के रूप में जाँचें। अन्य सीमाएँ हैं - BOL , “मूल प्रक्रिया में संकलित प्रक्रियाएँ” देखें।

प्रक्रिया का मुख्य भाग ANSI SQL मानक से BEGIN ATOMIC ब्लॉक है, और फिलहाल यह T-SQL में इसका एकमात्र अनुप्रयोग है। यह BEGIN TRAN से भिन्न है कि यह त्रुटि होने पर स्वचालित रूप से वापस आ जाता है, जबकि लेनदेन में TRY / CATCH और ROLLBACK का उपयोग करना आवश्यक होता है, क्योंकि SET XACT_ABORT ON के बारे में बारीकियाँ हैं। डिजाइन विकल्प परिवहन अलगाव स्तर = स्नैपशॉट हैं REPEATABLEREAD | अनुक्रमिक और भाषा, जो sys.languages ​​से किसी भी भाषा का उपयोग कर सकते हैं। यह दिनांक | समय प्रारूप और सिस्टम संदेश भाषा को परिभाषित करता है।

 CREATE PROCEDURE dbo.usp_InsertSampleCarts @StartId int, @InsertCount int WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') IF @StartId is null SELECT @StartId = isnull(MAX(ShoppingCartId), 0) + 1 FROM dbo.ShoppingCart DECLARE @i int = 0 WHILE @i < @InsertCount BEGIN INSERT INTO dbo.ShoppingCart VALUES (@StartId + @i, RAND() * 1000, GETUTCDATE(), NULL) SET @i += 1 END END
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

लिपि २



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



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

 CREATE PROCEDURE dbo.usp_InsertSampleCarts_Disk @StartId int = null, @InsertCount int AS BEGIN IF @StartId is null SELECT @StartId = isnull(MAX(ShoppingCartId), 0) + 1 FROM dbo.ShoppingCart DECLARE @i int = 0 WHILE @i < @InsertCount BEGIN INSERT INTO dbo.ShoppingCart_Disk VALUES (@StartId + @i, RAND() * 1000, GETUTCDATE(), NULL) SET @i += 1 END END
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

लिपि ३



और एक लाख प्रविष्टियाँ डालें:

 SET NOCOUNT ON; DBCC FREEPROCCACHE WITH NO_INFOMSGS; DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS exec dbo.usp_InsertSampleCarts null, 1000000 exec dbo.usp_InsertSampleCarts_Disk @InsertCount = 1000000
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

जैसा कि कहा जाता है, अंतर स्पष्ट है:





3 चित्र



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



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

 declare @lsn nvarchar(46) = (select max([Current LSN]) from sys.fn_dblog(null, null)) exec dbo.usp_InsertSampleCarts_Disk @InsertCount = 100 select * from sys.fn_dblog(null, NULL) where [Current LSN] > @lsn order by [Current LSN] desc select count(1), sum([Log Record Length]) from sys.fn_dblog(null, NULL) where [Current LSN] > @lsn set @lsn = (select max([Current LSN]) from sys.fn_dblog(null, null)) exec dbo.usp_InsertSampleCarts null, 100 select * from sys.fn_dblog(null, NULL) where [Current LSN] > @lsn order by [Current LSN] desc select count(1), sum([Log Record Length]) from sys.fn_dblog(null, NULL) where [Current LSN] > @lsn
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

चित्र 4



और हम देखते हैं कि इन-मेमोरी टेबल के मामले में 100 प्रविष्टियों का सम्मिलन LOP_HK प्रकार (लॉग ऑपरेशन हेकोटन) के लॉग में सिर्फ दो प्रविष्टियों के रूप में लॉग किया गया है। जैसा कि कहा गया है, इन प्रविष्टियों को समेकित किया जाता है। आप देख सकते हैं कि वे वास्तव में नए अविवादित फ़ंक्शन का उपयोग करके क्या प्रकट करते हैं, जो मैंने केलेन डेलाने से उधार लिया था। वर्तमान एलएसएन के रूप में, हम अंजीर में सेट किए गए तीसरे परिणाम के संबंधित कॉलम से मान सेट करते हैं। 4।

 SELECT [current lsn], [transaction id], operation, operation_desc, tx_end_timestamp, total_size, object_name(table_id) AS TableName FROM sys.fn_dblog_xtp(null, null) WHERE [Current LSN] = '00000027:000001dd:0002' SELECT [current lsn], [transaction id], operation, operation_desc, tx_end_timestamp, total_size, object_name(table_id) AS TableName FROM sys.fn_dblog_xtp(null, null) WHERE [Current LSN] = '00000027:000001dd:0001'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

चित्रा 5



पहला रिकॉर्ड, जैसा कि हम देखते हैं, 102 रिकॉर्ड होते हैं: टीएक्स शुरू करें, 100 आवेषण, कमिट। इसके बावजूद, उनकी कुल मात्रा (चित्र 4 का दूसरा और चौथा परिणाम देखें) डिस्क तालिका की तुलना में> 10 गुना अधिक कॉम्पैक्ट है। यदि हम एक गैर-टिकाऊ तालिका (DURABILITY = SCHEMA_ONLY) लेते हैं, तो कोई जर्नलिंग नहीं होगी।