SQL Server এক্সিকিউশন প্ল্যান মास্টার করুন: একটি প্র্যাক্টিক্যাল গাইড

আপনি এক্সিকিউশন প্ল্যান কীভাবে পড়েন? ডান থেকে বাম, বাম থেকে ডান, অথবা কস্ট চেক করে? নাকি ইনডেক্স স্ক্যান, টেবিল স্ক্যান এবং লুকআপের মতো অবজেক্ট देखकर? এই ব্লগে Microsoft® SQL Server এক্সিকিউশন প্ল্যান পড়ার পদ্ধতি আলোচনা করা হয়েছে।

ভূমিকা

যদিও SQL Server সাধারণত একটি ভালো প্ল্যান তৈরি করে, কিন্তু কখনও কখনও এটি তার প্ল্যানগুলো যাচাই করতে এবং খারাপগুলো ফিক্স করতে যথেষ্ট স্মার্ট নয়।

SQL Server-এ আপনি আনুমানিক এক্সিকিউশন প্ল্যান এবং প্রকৃত গ্রাফিক্যাল এক্সিকিউশন প্ল্যান পেতে পারেন। এই প্ল্যানগুলো SQL Server Management Studio (SSMS)-এ এক্সিকিউট আইকনের ডানে থাকা আইকনগুলো ব্যবহার করে অথবা ctrl M বা ctrl L কমান্ড দিয়ে জেনারেট করতে পারেন। SQL Server-এ অন্যান্য টাইপের প্ল্যানও আছে, তবে এই পোস্টে সেগুলো কভার করা নেই।

আনুমানিক এবং প্রকৃত এক্সিকিউশন প্ল্যান

এক্সিকিউশন প্ল্যানের দুটি ধরন আছে:

1. আনুমানিক এক্সিকিউশন প্ল্যান: আনুমানিক প্ল্যানগুলো SQL Server-এর ডেটা পেতে আশা করা কাজের একটি অনুমান দেয়।

2. প্রকৃত এক্সিকিউশন প্ল্যান: প্রকৃত এক্সিকিউশন প্ল্যান Transact-SQL কোয়ারি বা ব্যাচ এক্সিকিউট হওয়ার পর জেনারেট হয়। এই কারণে প্রকৃত এক্সিকিউশন প্ল্যানে রানটাইম ইনফরমেশন থাকে, যেমন প্রকৃত রিসোর্স ইউজেজ মেট্রিক্স এবং যেকোনো রানটাইম সতর্কতা।

ডেটা প্রসেসিং

আপনি কি একই কোয়ারির জন্য আনুমানিক প্ল্যান এবং প্রকৃত প্ল্যানের মধ্যে পার্থক্য লক্ষ্য করেছেন? অধিকাংশ সময় এটি একই থাকে, তবে এটি ভিন্ন হতে পারে স্ট্যাটিস্টিক্স পরিবর্তন, স্কিমা-সম্পর্কিত পরিবর্তন, বা ডেটা পরিবর্তনের কারণে। ট্রাবলশুটিং করার সময় আপনার সবসময় প্রকৃত এক্সিকিউশন প্ল্যান চেক করা উচিত।

সঠিকভাবে এক্সিকিউশন প্ল্যান পড়ে প্রকৃত পয়েন্ট চিহ্নিত করুন। ডেটা ফ্লো দেখে শুরু করুন, কস্ট নয়। লজিক্যাল বা ফিজিক্যাল রিডস নিয়ে চিন্তা করবেন না। ইনপুট/আউটপুট (I/O) অপারেশনların সংখ্যা কমাতে হবে তা গুরুত্বপূর্ণ। একজন ডেটাবেস অ্যাডমিনিস্ট্রেটর (DBA) হিসেবে আপনি জানেন যে স্টোরেজ অ্যাক্সেস হার্ডওয়্যার রিসোর্সের মধ্যে সবচেয়ে ধীর, তাই আপনাকে সেই কার্যকলাপকে কমিয়ে আনতে হবে। তাহলে আপনি কীভাবে σταтистиクス এক্সপ্লোর করবেন, এবং এক্সিকিউশন প্ল্যান তা দেখায় কি? হ্যাঁ, দেখায়! ডায়рекশন লাইনগুলো মাউস হোভার করে ডানে থেকে বামের ইন্ডিকেটর দেখুন। এটি রেকর্ড সংখ্যা এবং ডেটা সাইজ দেখায়। প্রতিটি লাইন অপারেশন দ্বারা রিটার্ন করা ডেটা ভলিউম অনুযায়ী আঁচড় বা পাতলা হয়, যা নিচের ইলাস্ট্রেশনটিতে দেখানো হয়েছে:

যদি আপনার অনেক অবজেক্ট থাকে, তবে প্রতিটি অপারেশন tarafından প্রসেস করা ডেটার পরিমাণের ওভারভিউ পেতে একটি ভালো উপায় লাগবে। SentryOne Plan Explorer ডাউনলোড করুন এবং এই টুল ব্যবহার করে প্ল্যান দেখুন সহজে ওভারভিউ পाने জন্য।

SentryOne ডাউনলোড ও কনফিগার করার পর, এক্সিকিউশন প্ল্যানটি এই টুলে খুলুন। SentryOne-এ অনেক ভিউ এবং ডেসক্রিপশন আছে, যেগুলো আপনি প্রয়োজন অনুযায়ী ব্যবহার করতে পারেন। ডেটা প্রসেসিং ভিউ পেতে, Data size in MB অপশন সিলেক্ট করে ভিউ পরিবর্তন করুন নিচের ছবির মত। আপনার লক্ষ্য হবে সামগ্রিক ডেটা প্রসেসিং কমাতে সুযোগ খুঁজে বের করা।

আপনি যদি I/O স্ট্রেস কমাতে চান, তবে SET STATISTICS IO ON T-SQL মান দেখতে পারেন কোয়ারির জন্য সামগ্রিক I/O ব্যবহার সম্পর্কে ধারণা পেতে। এটি SSMS-এ কোয়ারি এক্সিকিউশনের আগে সেট করুন রিজাল্ট সোইচ টু মেসেজ ট্যাব দেখতে রিজাল্ট প্যানেলে। এটি নিচের রিজাল্টের মতো দেখাবে:

(356 row(s) affected) Table 'Table3'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

logical reads 5 রেজাল্ট দেখায় যে SQL Server ৪০ KB (৫ × ৮ KB) পেজ মেমরি থেকে ডেটা পেতে পড়ে। কোয়ারি অপ্টিমাইজ করার সময় লজিক্যাল রিডস উপেক্ষা করবেন না কারণ এটি লাইভ সার্ভারেও ফিজিক্যাল এবং লজিক্যাল দুভাবেই হতে পারে। আপনি কখনো জানতে পারবেন না যে আপনি যে পেজগুলো রেফার করছেন সেগুলো মেমরিতে আছে কিনা বা ডিস্কে। লক্ষ্য হওয়া উচিত কুমুলেটিভ রিড অপারেশনların সংখ্যা কমাতে।

আনুমানিক বনাম প্রকৃত প্ল্যান

প্রকৃত প্ল্যান জেনারেট হওয়ার পর, এক্সিকিউশন প্ল্যানের যেকোনো অপারেশন বা লজিক্যাল ইউনিটের ওপর মাউস হোভার করুন। নিচের ছবি রেফার করুন ফিজিক্যাল অপারেশন টাইপ দেখতে, যেমন Clustered Index Seek or Scan রেজাল্ট। এক্সিকিউশন সংখ্যা এবং প্রকৃত ও আনুমানিক সারি সংখ্যা খুঁজুন। অপারেশনগুলোর אינডিভিজুয়াল কস্ট দেখবেন না কারণ এটি পুরনো হার্ডওয়্যারের উপর ভিত্তি করে সেকেন্ডে একটি অনুমান এবং সঠিক বিবরণ দিতে পারে না। নিচের ছবিতে, Clustered Index Seek অপারেটর একবার এক্সিকিউট হয়েছে ১০০ রেকর্ড পেতে, এবং SQL ৩৫৬ রেকর্ড অনুমান করেছে। পার্থক্য হতে পারে পুরনো স্ট্যাটিস্টিক্স বা কোয়ারি পারফরম্যান্সের কারণে।

এক্সিকিউশন ইটেরেশন

নিচের ছবিতে দেখানো হয়েছে আনুমানিক এক্সিকিউশন সংখ্যা ১১৫৪১২১ বার একটি ক্লাস্টার ইনডেক্সে এক রেকর্ড পেতে। এটি গুরুত্বপূর্ণ। যদিও অপারেটর কস্ট কম, এবং আমরা এটি উপেক্ষা করতে পারতাম, অন্য উচ্চ কস্টের অপারেশনগুলো দেখে, এটি একটি ব্যয়বহুল পছন্দ হতে পারে। এমন অপারেশনগুলো ক্লাস্টার ইনডেক্স থাকা সত্ত্বেও কোয়ারি পারফরম্যান্সে হাই ইমপ্যাক্ট डालতে পারে। Row ID (RID) লুকআপস হিপের জন্য একই ধরনের অপারেশন।

টেম্পোরারি ডেটাবেস

আনুমানিক এবং প্রকৃত রেকর্ড সংখ্যার পার্থক্য আরেকটি বিষয় যা আপনাকে বিবেচনা করতে হবে। ভুল অনুমানের ফলে একটি কোয়ারি সীমিত মেমরি পেতে পারে। যখন এটা হয়, তবে এটি কাজ করার জন্য টেম্পোরারি ডেটাবেস (tempdb) ব্যবহার করে। SQL Server-এর দ্বারা ভুল অপারেটর বা প্ল্যানের পছন্দের ফলে কোয়ারিগুলোর স্লো অপারেশন এবং এক্সট্রাকশন হতে পারে, নিচের ছবিতে দেখানো হয়েছে। প্রকৃত রেকর্ড সংখ্যা একই, কিন্তু অনুমানগুলো ভিন্ন। এটি হতে পারে পুরনো বা অনুপস্থিত স্ট্যাটিস্টিক্সের কারণে। মনে রাখবেন যে টেবিল ভেরিয়েবলগুলোর কোনো স্ট্যাটিস্টিক্স নেই, তাই প্ল্যান সবসময় ১ এবং ১K রিটার্ন করে নতুন রিলিজগুলিতে যতক্ষণ না recompile অপশন ব্যবহার করা হয়। এই 이유로 টেবিল ভেরিয়েবল বড় রেকর্ড সংখ্যার জন্য ভালো পছন্দ নয়।

সোর্ট অপারেটর

আপনাকে সোর্টিংয়ের প্রভাব বিবেচনা করতে হবে। সোর্ট অপারেটর মূলত নিচের ফাংশনগুলো জন্য ব্যবহৃত হয়: অ্যাগ্রিগেট, মার্জ জয়েন, অথবা order by ক্লজ। এটি কয়েক রেকর্ডের সাথে কোনো প্রভাব ফেলতে পারে না, কিন্তু প্রতিটি অতিরিক্ত রেকর্ডের সাথে প্রসেসিং স্লো হয়। সোর্টিং এড়িয়ে চলুন অথবা order by ক্লজ ব্যবহার করবেন না। সোর্টিং যদি প্রয়োজন হয়, তবে অ্যাপ্লিকেশন গ্রিড ব্যবহার করুন সোর্ট করার জন্য সোর্ট করা ডেটা অ্যাপ্লিকেশনে পাঠানোর পরিবর্তে।

নিচের ছবিতে সোর্ট কস্ট দেখানো হয়েছে:

স্পুল অপারেটর

আরেকটি কী অপারেটর যা আপনাকে দেখতে হবে সেটা হলো স্পুল। স্পুল একটি স্লো অপারেটর কারণ এটি হিডেন বা টেম্পোরারি অবজেক্ট বা ওয়ার্ক টেবিল tempdb-এ সংরক্ষণ করে। এটি কিছু নির্দিষ্ট অপারেটরের সাথে আরও স্লো হয় যা রিবাইন্ড বা রിവাইন্ড করতে বাধ্য করে। SQL Server-এ অনেক ধরনের স্পুল আছে, যেমন Eager, Lazy, Table/Index, ইত্যাদি। SQL Server স্পুল ব্যবহার করে যখন এটি একটি টেম্প ওয়ার্ক টেবিল রেফার করার জন্য ভালো হয় সোর্স টেবিলে ফিরে যাওয়ার চেয়ে ইন্টারমিডিয়েট রেজাল্ট সেটের জন্য। নিচের ছবিতে একটি উদাহরণ দেখানো হয়েছে:

স্পুলের সাথে, রিবাইন্ড এবং রিওয়াইন্ডের সংখ্যা লক্ষ্য করা গুরুত্বপূর্ণ। রিওয়াইন্ড রিবাইন্ডের চেয়ে ব্যয়বহুল। উদাহরণস্বরূপ, নিচের ছবিতে, অপারেটর ২৬৭৪ রিওয়াইন্ড দেখাচ্ছে, যার মানে কোয়ারি ২৬৭৪ বার পুনরায় এক্সিকিউট হচ্ছে ডেটা পেতে। এটি রিটার্ন করে প্রতিটি অপারেটর টেবিল স্পুল থেকে টেবিল স্ক্যানে যেতে প্রতিটি রেকর্ডের জন্য রিওয়াইন্ডে। রিবাইন্ড মানে এটি স্পুল থেকে ডেটা পেয়েছে এবং টেবিল স্ক্যানের জন্য ফিরে যায়নি।

হ্যাশ এবং নেস্টেড লুপ অপারেটর

যেহেতু ছোট রেকর্ড সেটে এগুলো ভালো পারফর্ম করে, হ্যাশ এবং নেস্টেড লুপেরপরের অপারেটরগুলো যা আপনাকে বিবেচনা করা উচিত। কিন্তু বড় রেকর্ড সেটের জন্য, বা যখন আনুমানিক এবং প্রকৃত প্ল্যানের মধ্যে বড় পার্থক্য থাকে, এই অপারেটরগুলো বিশাল প্রভাব ফেলতে পারে কারণ এগুলো মেমরির পরিবর্তে tempdb ব্যবহার করতে পারে। SQL Server অপারেটর ডিটেইলসে নিচের সতর্কতা পোস্ট করে: "operator used tempdb to spill data during executions"। اگر এটা হয়, তবে স্ট্যাটিস্টিক্সের ওপর মনোযোগ দিন। ভুল অনুমানের সাথে, লুপ মেমরি অ্যালোকেশনে শর্ট ফেলে বা এটি ইটারেট করতে থাকে। এক্সিকিউশনের জন্য অ্যালোকেট করা মেমরি দেখুন। মেমরি অ্যালোকেশন দেখতে, এক্সিকিউশন প্ল্যানের স্টার্টিং পয়েন্ট (বাম থেকে ডান) সিলেক্ট করে প্রপার্টি বক্স খুলুন। যদি কিছু অস্বাভাবিক না থাকে, তবে কোয়ারি টিউনিং আদর্শ পছন্দ।

উপসংহার

একটি কোয়ারি অপ্টিমাইজ করার জন্য আপনার প্রথম লক্ষ্য হওয়া উচিত সামগ্রিক রিড এবং রাইট (অর্থাৎ ডিস্কে I/O) কমাতে। মেমরিতে রিড এবং রাইটের জন্য লজিক্যাল রিডস ভুলবেন না। I/O কমালে অধিকাংশ সমস্যা সমাধান হয় এবং কোয়ারিগুলো অনেক দ্রুত চলে।

এরপর, অন্যান্য অপারেশনগুলো দেখুন যা tempdb-এ কার্যকলাপের কারণে ব্যয়বহুল। মনে রাখবেন, tempdb অনেক অপারেশনের জন্য ব্যবহৃত হয় এবং সবসময় ব্যয়বহুল। রিওয়াইন্ড খুঁজুন - যেকোনো অপারেশনের এক্সিকিউশন সংখ্যা, স্পুল, সোর্টিং এবং লুপ।

tempdb-এর সাথে ব্যবহৃত হলে এগুলো ব্যয়বহুল। প্রতিটি অপারেটরে সতর্কতা রিভিউ করা ভুলবেন না কারণ এগুলো ভালো সংকেত দেয়। যদিও এই পোস্টে missing index অপারেটর আলোচনা করা হয়নি, তার মানে এটি নয় যে আপনি এটা উপেক্ষা করবেন।

এটা রিভিউ করুন, কিন্তু ব্লাইন্ডলি ইনডেক্স তৈরি করবেন না। একই কলামে উপলব্ধ অন্যান্য ইনডেক্স চেক করুন, এবং আপনার ডেটাবেসে চলমান কোয়ারিগুলোর উপর প্রভাব বিবেচনা করুন।

ফিডব্যাক ট্যাব ব্যবহার করে যেকোনো মন্তব্য বা প্রশ্ন করুন। আপনি আমাদের সাথে কথা বলতে পারেন।