SQL सर्वर ऑप्टिमाइज़ेशन प्रक्रिया का प्रदर्शन

Question

हम SQL सर्वर ऑप्टिमाइज़र द्वारा क्वेरी ऑप्टिमाइज़ेशन के दौरान माना गया क्वेरी प्लान के सभी वेरिएंट को देखना चाहेंगे। SQL सर्वर querytraceonविकल्पों का उपयोग करते हुए काफी विस्तृत जानकारी प्रदान करता है । उदाहरण के लिए QUERYTRACEON 3604, QUERYTRACEON 8615हमें MEMO संरचना को QUERYTRACEON 3604, QUERYTRACEON 8619प्रिंट करने और अनुकूलन प्रक्रिया के दौरान लागू किए गए परिवर्तन नियमों की सूची का प्रिंट आउट करने की अनुमति देता है । हालांकि, यह बहुत अच्छा है, हमारे पास ट्रेस आउटपुट के साथ कई समस्याएं हैं:

1. ऐसा लगता है कि MEMO संरचना में क्वेरी प्लान या वेरिएंट के केवल अंतिम रूप होते हैं जिन्हें बाद में अंतिम रूप में लिखा गया था। क्या "असफल / असफल" क्वेरी योजनाओं को खोजने का एक तरीका है?
2. MEMO के संचालकों में SQL भागों का संदर्भ नहीं होता है। उदाहरण के लिए, LogOp_Get ऑपरेटर में किसी विशिष्ट तालिका का संदर्भ नहीं है।
3. परिवर्तन नियमों में MEMO ऑपरेटरों का सटीक संदर्भ नहीं है, इसलिए, हम यह सुनिश्चित नहीं कर सकते हैं कि कौन से ऑपरेटर परिवर्तन नियम द्वारा परिवर्तित किए गए थे।

मुझे इसे अधिक विस्तृत उदाहरण पर दिखाने दें। मुझे दो कृत्रिम टेबल Aऔर B:

WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n + 10000 * tenthousands.n + 100000 * hundredthousands.n as id  
        FROM x ones, x tens, x hundreds, x thousands, x tenthousands, x hundredthousands
    )
SELECT
    CAST(id AS INT) id, 
    CAST(id % 9173 AS int) fkb, 
    CAST(id % 911 AS int) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO A
FROM t1;


WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n AS id  
        FROM x ones, x tens, x hundreds, x thousands       
    )
SELECT
    CAST(id AS INT) id,
    CAST(id % 901 AS INT) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO B
FROM t1;

अभी, मैं एक सरल क्वेरी चलाता हूं

SELECT a1.id, a1.fkb, a1.search, a1.padding
FROM A a1 JOIN A a2 ON a1.fkb = a2.id
WHERE a1.search = 497 AND a2.search = 1
OPTION(RECOMPILE, 
    MAXDOP 1,
    QUERYTRACEON 3604,
    QUERYTRACEON 8615)
    

मुझे काफी जटिल आउटपुट मिलते हैं जो 15 समूहों वाले मेमो संरचना (आप खुद से कोशिश कर सकते हैं) का वर्णन करते हैं। यहां चित्र है, जो एक पेड़ का उपयोग करके मेमो संरचना की कल्पना करता है।

पेड़ से कोई यह देख सकता है कि अंतिम क्वेरी योजना के लिए अनुकूलक के मिलने से पहले कुछ नियम लागू थे। उदाहरण के लिए join commute( JoinCommute), join to hash join( JNtoHS), या Enforce sort( EnforceSort)। जैसा कि उल्लेख किया गया है कि QUERYTRACEON 3604, QUERYTRACEON 8619विकल्पों का उपयोग करके अनुकूलक द्वारा लागू किए गए पुनर्लेखन नियमों के पूरे सेट को प्रिंट करना संभव है । समस्याये:

1. हम मिल सकता है JNtoSM( Join to sort merge8619 सूची में) को फिर से लिखने नियम, तथापि, तरह-मर्ज ऑपरेटर नहीं ज्ञापन संरचना में है। मैं समझता हूं कि सॉर्ट-मर्ज शायद अधिक महंगा था, लेकिन यह एमएमओ में क्यों नहीं है?
2. कैसे पता करें कि LogOp_GetMEMO में ऑपरेटर तालिका A या तालिका B के संदर्भ में है?
3. यदि मैं GetToIdxScan - Get -> IdxScan8619 सूची में नियम देखता हूं , तो इसे मेमो ऑपरेटरों को कैसे मैप करना है?

इस बारे में सीमित संख्या में संसाधन हैं। मैंने परिवर्तन नियमों और MEMO के बारे में पॉल व्हाइट ब्लॉग पोस्टों में से कई को पढ़ा है, हालांकि, उपरोक्त प्रश्न अनुत्तरित हैं। किसी भी मदद के लिए धन्यवाद।

Answer 1

मैं आपके सवालों का जवाब देने की कोशिश करूंगा:

1. ऐसा लगता है कि MEMO संरचना में क्वेरी प्लान या वेरिएंट के केवल अंतिम रूप होते हैं जिन्हें बाद में अंतिम एक में लिखा गया था। क्या "असफल / असफल" क्वेरी योजनाओं को खोजने का एक तरीका है?

नहीं, दुख की बात है कि ऐसा करने का कोई तरीका नहीं है। @ रोनाल्डो ने टिप्पणी में एक अच्छा लिंक चिपकाया। मेरा सुझाव का उपयोग करने के लिए हैInclude Live Query Statistics

और यह पता लगाने की कोशिश करें कि क्या आप अलग-अलग क्वेरी प्लान देखते हैं। उपयोग top 10, top 1000या, *और आप देखेंगे कि विभिन्न क्वेरी प्लान प्रस्तावित किए जाएंगे। आप query hintअपनी क्वेरी योजना को एक अलग पैटर्न में भी उपयोग और बाध्य कर सकते हैं । मूल रूप से "अपनी खुद की त्याग की गई क्वेरी योजना"

2. MEMO में ऑपरेटर SQL भागों का संदर्भ नहीं रखते हैं। उदाहरण के लिए, LogOp_Get ऑपरेटर में किसी विशिष्ट तालिका का संदर्भ नहीं है।

उपयोग करें QUERYTRACEON 8605, मैं तालिका का संदर्भ देख सकता हूं:

3. परिवर्तन नियमों में MEMO ऑपरेटरों का सटीक संदर्भ शामिल नहीं है, इसलिए, हम यह सुनिश्चित नहीं कर सकते हैं कि कौन से ऑपरेटर परिवर्तन नियम द्वारा बदल दिए गए थे

मुझे GetToIdxScan - Get -> IdxScanआपके द्वारा प्रदान की गई क्वेरी में कोई भी नहीं दिखाई देता है। मेरा सुझाव उपयोग का उपयोग करना है QUERYTRACEON 8605, या QUERYTRACEON 8606, वहां एक संदर्भ होना चाहिए।

संपादित करें:

इसलिए "... क्या SQL सर्वर में उम्मीदवार योजनाओं के बारे में अधिक जानकारी देखना संभव है।"

उत्तर नहीं है , क्योंकि कोई अन्य उम्मीदवार क्वेरी योजना नहीं है। वास्तव में, एक आम गलत धारणा है कि SQL सर्वर आपको सर्वश्रेष्ठ क्वेरी योजना लौटाता है । SQL सर्वर बस आपके लिए सभी संभावित समाधानों की गणना नहीं कर सकता है: जो कि ले जाएगा ... मुझे नहीं पता ... मिनट ...? घंटे...? हर एक समाधान की गणना करना संभव नहीं है।

लेकिन अगर आप जांच करना चाहते हैं कि आपकी क्वेरी योजना उस पैटर्न का चयन क्यों करती है जिसका आप उपयोग कर सकते हैं:

• SET SHOWPLAN_ALL ON : और SQL सर्वर आपको आपकी क्वेरी योजना के हर एक गणना के तर्क का एक पेड़ लौटाएगा

• DBCC SHOW_STATISTICS('A', 'PK_A'): जो आपको एक लक्ष्य तालिका और बाधा के बारे में आंकड़े दिखाएंगे। मैंने आपको परिणाम दिखाने के लिए एक कुंजी बनाई, स्वाभाविक रूप से आप अधिक जानकारी देखेंगे यदि आपकी तालिका को अधिक बार क्वियर किया जाता है

• USE HINT('force_legacy_cardinality_estimation') : आप पूर्व कार्डिनैलिटी आकलन का उपयोग करने की अनुमति देंगे, ताकि आप यह जांच सकें कि क्या आप क्वेरी प्लान लीगेसी कार्डिनैलिटी आकलन के साथ तेज हो सकते हैं।