Microsoft SQL सर्वर अनजाने सुविधाएँ: STATISTICS_ONLY, DBCC AUTOPILOT, और SET AUTOPILOT

जैसा कि आप जानते हैं, SQL सर्वर क्वेरी ऑप्टिमाइज़र एक इष्टतम क्वेरी निष्पादन योजना बनाने के लिए लागत अनुमान का उपयोग करता है। SQL सर्वर कई योजनाओं का निर्माण और मूल्यांकन करता है और उनमें से सबसे कम लागत वाली योजना का चयन करता है।

समस्याओं में से एक जिसका हम समय-समय पर सामना करते हैं, यह समझने के लिए कि नया सूचकांक किसी विशेष क्वेरी के निष्पादन को कैसे प्रभावित करेगा, हमें यह सूचकांक बनाने की आवश्यकता है। कभी-कभी, विशेष रूप से जब तालिका बहुत बड़ी होती है, तो सूचकांक बनाने की प्रक्रिया में इतनी देरी होती है कि यह वास्तविक दुःस्वप्न में बदल जाता है। इसके अलावा, 20 मिनट के इंतजार के बाद, हम अच्छी तरह से यह जान सकते हैं कि क्वेरी को निष्पादित करते समय नए बनाए गए सूचकांक का उपयोग नहीं किया जाता है।

दरअसल, सवाल यह है कि "काल्पनिक" सूचकांक कैसे बनाया जाए? बस यह जांचने के लिए कि क्वेरी निष्पादित करते समय ऐसा कोई इंडेक्स वास्तव में उपयोगी होगा या नहीं।

STATISTICS_ONLY के साथ

एक काल्पनिक सूचकांक बनाने के लिए, हम क्रिएट इंडेक्स कमांड की अनिर्दिष्ट सुविधा का उपयोग कर सकते हैं। उदाहरण के लिए:

USE AdventureWorksDW GO CREATE INDEX ix_FirstName ON DimCustomer(FirstName) WITH STATISTICS_ONLY = -1 GO 

नतीजतन, इस सूचकांक के लिए आंकड़े बनाए जाएंगे (एक हिस्टोग्राम बनाया गया है और गणना की गई घनत्व) और sys.indexes में एक प्रविष्टि दिखाई देगी। आप इसे sp_helpindex और DBCC SHOWSTATISTICS से देख सकते हैं:

 sp_HelpIndex DimCustomer 

 DBCC SHOW_STATISTICS(DimCustomer, ix_FirstName) 

बेंजामिन नेवरेज ने इन काल्पनिक सूचकांकों का वर्णन किया है ।

PS यदि आप STATISTICS_ONLY = 0 का उपयोग करके एक इंडेक्स बनाते हैं, तो SQL सर्वर आंकड़े उत्पन्न नहीं करेगा। केवल एक काल्पनिक सूचकांक।

DBCC AUTOPILOT और SET AUTOPILOT

अब हमारे पास एक काल्पनिक सूचकांक है, हम इसका उपयोग कैसे करते हैं?

आप संकेत का उपयोग करके इसे स्पष्ट रूप से निर्दिष्ट करने का प्रयास कर सकते हैं:

 SELECT * FROM DimCustomer WITH(index=ix_FirstName) WHERE FirstName = N'Eugene' 

और त्रुटि प्राप्त करें:
Msg 308, Level 16, State 1, Line 1
Index 'ix_FirstName' on table 'DimCustomer' (specified in the FROM clause) does not exist.

और यदि आप एक इंडेक्स आईडी निर्दिष्ट करते हैं?

 SELECT * FROM DimCustomer WITH(index=5) WHERE FirstName = N'Eugene' 

वही बात:
Msg 307, Level 16, State 1, Line 1
Index ID 5 on table 'DimCustomer' (specified in the FROM clause) does not exist.

तो हम एक क्वेरी प्लान कैसे बना सकते हैं जो इस इंडेक्स को ध्यान में रखता है?

यहीं से सारी मस्ती शुरू होती है।

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

आइए इस कमांड के सिंटैक्स को देखें:

 SET AUTOPILOT ON|OFF /* DBCC TRACEON (2588) DBCC HELP('AUTOPILOT') */ DBCC AUTOPILOT (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]]) 

परीक्षण

तो, आइए देखें कि यह सब कैसे काम करता है।

 -- Current Cost = 0,762133 -- Clustered Index Scan on pk SELECT * FROM DimCustomer WHERE FirstName = N'Eugene' GO 

 -- creating the index -- DROP INDEX ix_FirstName ON DimCustomer CREATE INDEX ix_FirstName ON DimCustomer(FirstName) WITH STATISTICS_ONLY = -1 GO -- Looking at the info necessary in the DBCC AUTOPILOT comand SELECT name, id, Indid, Dpages, rowcnt FROM sysindexes WHERE id = object_id('DimCustomer') GO 

 DBCC AUTOPILOT (5, 9, 0, 0, 0, 0, 0) -- Starting with the TypeID 5 DBCC AUTOPILOT (6, 9, 37575172, 1, 0, 0, 0) -- Clustered Index with TypeID 6 DBCC AUTOPILOT (0, 9, 37575172, 2, 0, 0, 0) -- All other index with TypeID 0 DBCC AUTOPILOT (0, 9, 37575172, 3, 0, 0, 0) -- All other index with TypeID 0 DBCC AUTOPILOT (0, 9, 37575172, 5, 0, 0, 0) -- All other index with TypeID 0 GO SET AUTOPILOT ON GO -- Query to create the estimated execution plan with the cost = 0,0750712 SELECT * FROM dbo.DimCustomer WHERE FirstName = N'Eugene' OPTION (RECOMPILE) GO SET AUTOPILOT OFF GO 

लगभग। अनुवादक: टाइपिड पैरामीटर क्या है, निश्चित रूप से ज्ञात नहीं है, एक अन्य लेख में, जिसका लिंक नीचे है, वही लेखक लिखता है कि "ऑटोपायलट" मोड में एक विशिष्ट सूचकांक का उपयोग करने के लिए, आपको 0 निर्दिष्ट करने की आवश्यकता है


आप DBCC AUTOPILOT को पेज और RowCount मापदंडों के रूप में मनमाना मान देकर भी अनुकूलक को धोखा दे सकते हैं। यदि आप उन्हें शून्य के बराबर पास करते हैं, तो मान क्लस्टर समूह के लिए समान होंगे।

निष्कर्ष

इन विशेषताओं के वर्णन में पर्याप्त "रिक्त स्थान" हैं, लेकिन मुझे यकीन है कि यह पोस्ट आपके अपने परीक्षणों के लिए एक अच्छा प्रारंभिक बिंदु होगा।

मैं अभी भी इस चीज़ के साथ खेलता हूं, इसलिए आप मुझसे सुरक्षित रूप से सवाल पूछ सकते हैं, या अपनी खोजों को साझा कर सकते हैं।

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

अनुवादक से:
लेखक में शामिल होने के दौरान, मैं आपको याद दिलाता हूं कि काम के माहौल में अनिर्दिष्ट कार्यक्षमता के उपयोग से अवांछनीय परिणाम हो सकते हैं।
इसके अलावा, मैं उसी लेखक के लिए एक और लिंक जोड़ना चाहता हूं: एसक्यूएल सर्वर पर हाइपोथेटिकल इंडेक्स । यह उसी पोस्ट का एक नया, विस्तारित संस्करण है। मैंने उसका अनुवाद क्यों नहीं किया? क्योंकि उनके पास एक ही सार है, मुख्य अंतर यह है कि नए संस्करण में, यह डीबीसीसी ऑटोपायलट के अधिक सरल उपयोग के लिए सीएलआर विधानसभा प्रदान करता है, जो कि, मेरी राय में, आवश्यक नहीं है।
मेरे SQL Server 2005 SP4 में सभी कोड और सभी स्क्रीनशॉट मेरे द्वारा लिए गए थे, इसके लिए न्यूनतम डोपिंग की आवश्यकता थी (उदाहरण के लिए, STATISTICS_ONLY = STATISTICS_ONLY = -1 के साथ का उपयोग करके), इसलिए सब कुछ SQL सर्वर 2005 और पुराने (अर्थात्, 2005 से शुरू) पर काम करेगा। सर्वर, डेटाबेस ट्यूनिंग सलाहकार का उपयोग किया जाता है, जो, संभवतः, इस कार्यक्षमता का उपयोग करता है)।
हमेशा की तरह, अनुवाद और शैली के बारे में किसी भी सुझाव और सुधार का स्वागत है।