نوشته های نیما جمشیدی

What’s New in SQL Server 2022

نیما جمشیدی — Thu, 23 Dec 2021 20:10:19 +0330

مقدمه ۲ نوامبر ۲۰۲۱، مایکروسافت جلسه ای برای معرفی بخشی از ویژگی های جدید SQL Server 2022 برگزار کرد که پس از سکوت طولانی در مورد نسخه جدید SQL Server بود. این معارفه مربوط به GA release میشد که قراره در سال ۲۰۲۲ منتشر بشه. یکی از قابلیت جدید معرفی شده مربوط میشه به Parameter Sensitive Plan Optimization که قصد دارم درمورد سابقه این مسئله و قابلیت جدیدی که مایکروسافت ارائه کرده صحبت کنم.SQL Server 2022سابقه :در مقاله متغیرهای لوکال و مشکلات Parameter Sniffing به بررسی performance در کوئری پلن های کش شده به ازاء پارامترهای ورودی مختلف پرداختم. به طور خلاصه :زمانی که یک Stored Procedure فراخوانی می‌شه، SQL Server به دنبال Execution Plan مربوط به اون در حافظه Cache می گرده، اگه اونو پیدا کرد بلافاصله اجراش می کنه اما اگه Planی براش پیدا نکرد، باید تمام مراحل ساخت Execution Plan طی بشه.دیتابیس SQL Server با استفاده از پارامترهایی که اولین بار به یک SP فرستاده میشه، یک Plan ایجاد کرده و اونو Cache میکنه. یعنی یکبار پلن رو می‌سازه تا چندین بار ازش استفاده کنه و در زمان صرفه جویی کنه.گاهی پیش میاد که کش کردن پلن کوئری برای یه کوئری با پارامترهای مختلف اشکال ایجاد کنه به این معنی که با برخی پارامتر ها اون پلن کش شده سریع و بهینه باشه اما با پارامترهای متفاوت دیگه اون کوئری پلن مناسب نباشه،به این پدیده، مشکل Parameter Sniffing گفته میشه. این مشکل مدت ها مدیران پایگاه داده SQL Server رو درگیر کرده و تا آخرین نسخه SQL server 2019 و حتی با اضافه شدن Query Store هم حل نشده، راه حل های مثل Recompile در هر بار اجرا کوئری، گزینه های موقتی بودن که میتونست این مسئله رو حل کنه. حالا در SQL Server 2022 مایکروسافت ادعا میکنه که تونسته این مشکل قدیمی رو به نحوی کنترل کنه.مفاهیم پایه :اول نیاز هست مفاهیمی رو که در SQL Server وجود داره و رفته رفته تکمیل تر شدن، باهم مرور کنیم :مفهوم Intelligent query processing (IQP)مفهوم Intelligent query processing به مجموعه ویژگی هایی گفته میشه که به صورت هوشمند سعی دارند هزینه اجرا کوئری در SQL Server رو کاهش بدن، برخی از این قابلیت ها از نسخه 2017 ایجاد شدن و برخی مربوط به نسخه 2019 هستند. در SQL Server 2022 شاهد نسل سوم IQP خواهیم بود.مفهوم Query Store : قابلیت Query Store از نسخه ۲۰۱۶ ایجاد شد که وظیفه ذخیره کردن سابقه کوئری های اجرا شده و کوئری پلن ها رو داره. این اطلاعات کمک میکنه تا مشکلات ناشی از تغییر کوئری پلن و performance به راحتی شناسایی بشن. حتی بعد از ریست شدن SQL بازهم این اطلاعات به خاطر ذخیره شدن تو دیسک در دسترس هستن.مفهوم Cardinality estimator (CS) :در SQL Server ابزار Query Optimizer بر مبنای هزینه عمل میکنه در واقع cost-based هست به این معنی که کوئری پلنی رو انتخاب میکنه که کمترین هزینه اجرا رو داره . Query Optimizer این هزینه رو با دو فاکتور حساب میکنه.تعداد row رکوردی که در هر مرحله از کوئری پردازش میشه.هزینه مدل الگوریتمی که در نهایت باید برای اجرا کوئری پردازش بشه.درواقع مجموع هزینه دیتا پردازش شده و هزینه عملیات های انجام شده رو در نظر میگیره. از اونجا که SQL Server هربار نمیتونه تعداد رکوردها رو بشمره، به کمک Cardinality estimator برآوردی از هزینه اجرا کوئری و تعداد رکورد ها بدست میاره.مفهوم Parameter Sensitive Plan Optimization :به قابلیت جدید ارائه شده در SQL Server 2022 که وظیفه رفع مسئله، کش شدن پلن ها بر اساس پارامترهای مختلف و در نتیجه جلوگیری از مشکلات Performance در دیتابیس رو داره Parameter Sensitive Plan Optimization گفته میشه.بررسی یک مثال :فرض کنید کاربر A میاد سراغ stored procedure ایجاد شده به اسم Myproc و اون رو با مقدار ورودی پارامتر 1 فراخوانی میکنه و کوئری خیلی سریع و بهینه با کوئری پلن زیر اجرا میشه . تو این مثال Index seek بهترین گزینه برای اجرا کوئری بوده.بعد از مدتی کاربر B سراغ همون SP میاد با مقدار متغیر ورودی 10 فرخوانی میکنه. Engine تشخیص میده که Scan راه حل بهتری برای کوئری هست و کوئری پلن قبلی رو کنار میذاره . خب نکته اینه که در یک زمان فقط یه ورژن از کوئری پلن برای یک کوئری میتونه در حافظه وجود داشته باشه.حالا اگر کاربر A دوباره بیاد سراغ کوئری و اون اجرا کنه، با توجه به اینکه کوئری پلن Scan تو حافظه کش شده پس کوئری با سرعت و هزینه نامناسبی اجرا میشه.در SQL Server 2022 ادعا شده که این مشکل حل شده به این شکل که با استفاده از امکان Parameter Sensitive Plan میتونه برای کاربرهای A و B، دو کوئری پلن متفاوت ذخیره کنه . اینجوری هر دو نفر Performance مناسب و پایداری دارن، همچنین نیاز به تغییر کد هم نیست. بریم دمو این تغییر رو تو SQL Server 2022 ببینیم :یه SP داریم که با دو پارامتر 8 و 2 جداگانه و بدون نگه داشتن کش پلن قبلی اجرا میشه. همونطور که میبینیم پلن سمت چپ از ایندکس seek و پلن سمت راست از index Scan استفاده میکنه.حالا بریم compatibility level رو به 16 تغییر بدیم، یعنی بریم روی نسخه SQL Server 2022.این بار اگه هردو کوئری رو بدون پاک کردن پلن کش اجرا کنیم، خواهیم دید که دو کوئری پلن مختف کش میشه.یعنی برای یک کوئری، دو پلن در کش ایجاد میشه.همونطور که تو تصویر مشخصه، برای پلن کش شده نوشته Option PLAN PER VALUE که نشون میده کوئری برای کدوم پارامتر کش شده. همچنین در Query Store هم دقیقا Query_hash یکسانی برای هر دو پلن ذخیره شده.مایکروسافت به همراه این قابلیت چندین قابلیت جدید هم معرفی کرده و احتمالا در آینده با انتشار نسخه عمومی SQL Server 2022 مقالات بیشتری در مورد این ویژگی ها منتشر خواهد شد، سعی میکنم به مرور به قابلیت های جدید بپردازم و در مورد اونها مطالبی رو باهم بررسی کنیم.منبع: Introducing SQL Server 2022 Bob Ward

تعریف block ، lock و deadlock در SQL Server

نیما جمشیدی — Tue, 13 Apr 2021 13:32:46 +0430

اجازه بدین تو شروع بحث یه سوال مطرح کنم، آیا لاک شدن Object ها تو SQL Server اتفاق بدیه؟نه، اینطور نیست. locking ابزار engine SQL برای حفظ خاصیت integrity دیتا در دیتابیس هست، تا تراکنش رو وادار کنه هر چهار خاصیت ACID رو پاس کنه.در مورد این چهار خاصیت و جزئیات مربوط به اون توی مطلب مشکل Concurrency - تئوری و راه حل در SQL Server صحبت کردیم.درواقع SQL Server، برای جلوگیری از مشکلات Concurrency نیاز به لاک کردن در سطوح مختلف دیتا داره. لاک ها ساختار in-memory دارن یعنی تو سطح رم اتفاق میوفتن و نه دیسک. لاک میتونه تو سطوح مختلف اتفاق بیوفته :سطح row : یک یا چند رکورد در سطح دیتابیس لاک می‌شه.سطح key : یک یا چند رکورد در ایندکس لاک می‌شه تا از بهم خوردن کلید ایندکس مورد استفاده جلوگیری کنه.سطح page : کوچکترین واحد نگهداری دیتا در SQL Server یعنی page با اندازه 8 KB لاک میشه.سطح Extent : مجموعه 8 page با هم یه extent تشکیل میدن گاهی یک extent لاک میشه.سطح table : گاهی پیش میاد که تمام جدول لاک بشه.سطح DB : بدترین نوع لاک، لاک شدن کل دیتابیس هست.لاک انواع مختلفی داره :Lock modeلاک شدن Object ها در SQL Server تو سرور Develop به عنوان مشکل بروز نمیکنه چون ما به تنهایی یا به همراه چند برنامه نویس دیگه داریم رو اپلیکیشن کار میکنیم اما به محض انتقال پروژه به سرور production، ممکنه بروز کنه. این همونجایی که تلفن شما زنگ میخوره و بهتون میگن که فلان صفحه لود نمیشه یا خیلی کند لود میشه.مثال lock و block :فرض کنین کاربر A تراکنشی رو شروع کرده و داره با بخشی از دیتا کار میکنه. کاربر B همزمان تراکنش دیگه ای رو شروع میکنه و میاد سراغ همون بخش دیتا که کاربر A هنوز باهاش کار داره. اینجا SQL سرور بنا به تنظیمات انجام شده به کاربر B میگه باید منتظر بمونی تا کاربر A کارش تموم بشه.این میشه کل مفاهیمی که میخوایم در موردش صحبت کنیم. بیایین تعریف بالا رو به زبان SQL Server بگیم.فرض کنین کاربر A تراکنشی رو شروع کرده ______ begin transactionداره با بخشی از دیتا کار میکنه ______ یه لاک تو سطح Object اتفاق افتاده. مثلا سطح رکورد یا جدول کاربر B تراکنش همزمان دیگه ای رو شروع میکنه ______ begin transactionمیاد سراغ همون بخش دیتا که کاربر A هنوز باهاش کار داره ______ Concurrencyاینجا SQL سرور بنا به تنظیمات انجام شده ______ Isolation Level به کاربر B میگه باید منتظر بمونی ______ wait از نوع LCK که مربوط به blocking هست.تا کاربر A کارش تموم بشه ________ Commit یا Rollbackپس فهمیدم که locking وقتی پیش میاد که کاربری بخشی از دیتا رو به طور اختصاصی در اختیار بگیره و کاربر دیگه ای رو که میخواد با اون دیتا کار کنه Block کنه. اما Deadlock کی اتفاق می افته؟مثال Deadlock :فرض کنین کاربر A بخش از دیتا رو لاک کرده، کابر B هم همزمان بخش دیگه ای از دیتا رو لاک کرده. کاربر A تو ادامه تراکنش نیاز به بخش دیتا داره که کاربر B لاکش کرده. کابر B هم تو ادامه کارش به بخش دیتایی نیاز داره که کابر A اول لاکش کرده. هیچ کدوم از تراکنش ها نمیتونه تموم بشه چون هر کدوم wait لاکی هست که توسط اون یکی ایجاد شده. به این حالت Deadlock گفته میشه. در این صورت SQL Server تفنگشو بر میداره و میگیره سمت تراکنشی که هزینه Rollback کمتری داره و اصطلاحا اون رو Kill میکنه. تنها جایی که SQL Server دخالت میکنه و پروسسی رو Kill میکنه توی Deadlock هست و برای wait که بخاطر لاک ایجاد شده هیچ دخالتی نمیکنه. به طور پیش فرض مفهموم lock time out روی SQL Server وجود نداره.برای اپلیکیشنی که مراحل اولیه رو طی کرده و کارایی لازم رو داره کمتر پیش میاد مشکل Deadlock وجود داشته باشه، درضمن چون deadlock همراه با نتیجه fail هست پس این وظیفه برنامه نویس های که این موارد رو تو کد هندل کنن و retry کنن.چطور Blocking رو در SQL Server کنترل کنیم؟دو راه حل وجود داره :اول : داشتن ایندکس مناسب.دوم: استفاده از Isolation Level مناسب در دیتابیس.تو این مطلب میخوایم مثالی برای راه حل اول بزنیم و راهکار ممکن رو بررسی کنیم. از محیط SQL Server 2019 استفاده میکنیم به همراه دیتابیس دمو Stack Overflow . تمام تستی هم که میخواهیم انجام بدیم مربوط به جدول users این دیتابیس هست.اول مطمئن میشیم که جدول users فقط PK داشته باشه و هیچ ایندکسی جز کلاستر براش ساخته نشده باشه.جدول users بدون ایندکس Nonclusteredچک میکنیم که Isolation level دیتابیس به حالت پیش فرض یعنی read commited باشه.SET TRANSACTION ISOLATION LEVEL READ COMMITTEDکوئری اجرا میکنیم تا ببینیم چنتا از کاربرا LastAccessDate مربوط به بازه مورد نظر دارن.select id from dbo.users where LastAccessDate >= '2013/01/10' and LastAccessDate < '2013/05/12' 1583 کاربر با این شرط پیدا شد. حالا یه تراکنش را به شکل زیر اجرا میکنیم.begin tran update Users set Reputation = Reputation + 100 where LastAccessDate >= '2013/01/10' and LastAccessDate < '2013/05/12'یه کوئری آپدیت روی جدول Users نوشتیم که reputation رو برای کاربرانی که LastAccessDate بین بازه مشخص شده رو دارن، 100 تا زیاد کنه. دقت کنین که تراکنش رو Commit نکردیم یعنی تراکنش باز میمونه. یه تب جدید باز میکنیم و کوئری select رو دوباره اجرا میکنیم.select id from dbo.users where LastAccessDate >= '2013/01/10' and LastAccessDate < '2013/05/12' اتفاقی که میوفته اینکه کوئری درحال اجرا باقی می مونه و هیچ وقت نتیجه ای بما برگردونده نمیشه، اما چرا ؟بهتره sp_whoisactive رو دانلود کنید و روی دیتابیستون ایجادش کنین تا بتونیم ادامه مراحل رو باهم پیش ببریم.با اجرا کوئری زیر میتونیم بفهمیم چه کوئری هایی درحال اجرا هستن و چه lock رو ایجاد کردن.sp_whoisactive نتیجه این شکلی میشه:همونطور که میبینیم کوئری select توسط کوئری Update که تراکنش commit نشده هست wait LCK شده. حالا کوئری آپدیت رو Rollback میکنیم و یه ایندکس روی ستون های ID و LastAccessDate تو جدول Users ایجاد میکنیم.CREATE NONCLUSTERED INDEX IX_LastAccessDate_ID ON [dbo].[Users] ( [Id] ASC, [LastAccessDate] ASC )حالا مجددا کوئری آپدیت رو اجرا میکنیم.begin tran update Users set Reputation = Reputation + 100 where LastAccessDate >= '2013/01/10' and LastAccessDate < '2013/05/12'و بعد کوئری Select رو اجرا میکنیم.select id from dbo.users where LastAccessDate >= '2013/01/10' and LastAccessDate < '2013/05/12'اینبار خیلی سریع کوئری select جواب داد، در صورتی که کوئری Update همچنان داخل تراکنش باز هست، مجددا sp_whoisactive رو اجرا میکنیم.اینبار فقط یه رکورد برمیگردونه که مربوط به کوئری Updateهست، یعنی با اینکه کوئری update لاک رو ایجاد کرده اما کوئری Select تونست اجرابشه. ایندکس ها فقط سرعت اجرا کوئری رو افزایش نمیدن به حل مسئله Concurrency هم کمک میکنن. چرا که با ساخت ایندکس Nonclustered ما یه کپی کوچکتری از جدولمون ایجاد کردیم که فقط شامل دو ستون مورد نیازمون هست. شبیه به اینکه یک جدول اصلی داشته باشیم و یه جدول خلاصه شده. وقتی کوئری رو جدول اصلی اجرا شد و اونو لاک کرد، SQL Server تشخیص داد که کپی دیگه ای هم وجود داره که میتونه از اون استفاده کنه و با دونستن این مطلب که کوئری اول تاثیری تو نتیجه کوئری دوم نداره اجازه استفاده از ایندکس Nonclustered رو بهش داد.حالا یه تغییر کوچیک تو کوئری Select میدیم.select id ,DisplayName from dbo.users where LastAccessDate >= '2013/01/10' and LastAccessDate < '2013/05/12'آیا همچنان کوئری اجرا میشه یا block میشه؟ما ستون DispayName رو هم به کوئری اضافه کردیم. میدونیم که این ستون تو ایندکس ساخته شده مون نبود. یعنی کپی از جدول که این دیتا رو داشته باشه وجود ندارد، پس ناچاره بره سراغ دیتا اصلی و این اتفاق میوفته.کوئری select جدیدمون توسط کوئری update که تراکنش جاری هست Block شد.نتیجه گیری :همیشه ایندکس ها رو برای سریعتر کردن اجرا کوئری ها ساختیم. اینجا دیدیم که ایندکس های Nonclustered بخاطر تعریفی که دارن و کپی مستقلی از جدول رو جای دیگه و تو Page های جداگانه نگه میدارن میتونن برای حل مشکل Concurrencyو جلو گیری از Blocking مفید باشن. توصیه ای که وجود داره ساخت تعداد محدودی ایندکس برای هر جدول هست مثلا حداکثر 5 ایندکس که شامل حداکثر 5 فیلد هستن میتونه شروع خوبی باشه. اینکه SQL Server کی تصمیم میگیره لاک رو تو سطح رکورد نگه داره یا کل جدول رو لاک کنه از دست ما خارج هست و نمیتونیم کنترلش کنیم اما میدونیم که هرچقدر تعداد رکورد های درگیر در تراکنش کمتر باشه لاک سطح پایینتری خواهیم داشت، هرچی ایندکس های بهتری داشته باشیم Blocking کمتری و در نهایت عملکرد مناسب تری در کل اپلیکیشن.منبع :www.sqlshack.comwww.brentozar.com

7 توصیه مهم کوئری نویسی در SQL Server

نیما جمشیدی — Sun, 04 Apr 2021 15:56:38 +0430

این مطلب برای متخصصین پایگاه داده و DBA هایی که سالها با موارد مختلف دیتابیس SQL Server کار میکنن نوشته نشده، شامل توصیه های عمومی برای Developer ها و افرادی میشه که بخشی از کار روزانه شون کوئری نویسی در SQL Server هست.مورد اول : بکارگیری تریگر تریگر در SQL Serverهمیشه شنیدیم که گفته شده تا جای ممکن از تریگر استفاده نکنین، تریگر خوب نیست، چرا ؟سناریویی که معمولا پیش میاد و ازتون به عنوان SQL Server Developer یا ادمین پایگاه داده میخوان تریگر بسازین چیه ؟بارها شده خطایی تو اپلیکیشن گزارش بشه و تیم توسعه بعد از بررسی متوجه شده باید تو کد تغییر بده اما تشخیص میده الان فرصت انجام اینکار نیست و استفاده از تریگر رو پیشنهاد میدن. حتی ممکنه چندین اپلیکیشن همزمان وجود داشته باشه مثلا ویندوز ، وب و موبایل که هزینه اصلاح کد تو هر سه مورد زیاده و نمیشه به سرعت تغییر داد پس ترجیح میدن یه تریگر روی جدولی که این اشکال توش وجود داره ساخته بشه و نقص عملیات توی کد رو جبران کنه. آیا این روش درست و استانداریه ؟تریگر چه مشکلی ایجاد میکنه ؟ مهمترین اشکال تریگر اینه که قابل تفکیک از عملیات اصلی نیست، فرض کنین تعداد زیادی Insert روزانه روی جدول انجام می شه و شما روی اون جدول تریگر برای عملیات insert ساختین، از این به بعد زمان اجرا تریگر هم به تک تک insert ها اضافه میشه و ممکنه بعد از مدتی لاگ ها و wait های طولانی ایجاد کنه و در نهایت اپلیکیشن رو با مشکل جدی روبرو کنه. پس اولین اشکال تریگر ایجاد مشکلات جدی Performance هست. تریگر میتونه مشکلات Concurrency هم بوجود بیاره. همیشه یه توصیه وجود داره، اینکه هیچ وقت دیتابیس سرور رو تبدیل به اپلیکیشن سرور نکنید.هر وقت درخواستی به شما رسید که مغایر با این توصیه بود پس درست نیست. یعنی استفاده از تریگر به دلیلی که توضیح دادم درست نیست، برای اصلاح کد و رفع موقتی باگ ها تریگر نسازین !خب پس تریگر به چی دردی میخوره؟ کی میشه ازش استفاده درست کرد؟یه استفاده منطقی برای تریگر وجود داره و اون Auditing هست، زمانی که لازم تغییرات روی دیتابیس رو جایی لاگ کنید حالا به دو دلیل ، اول اینکه لازم دارید رفتار کاربرها رو لاگ کنید و ببینید تغییری که توی جدول داده میشه توسط چه کسی و چه زمانی داده شده. حالت دوم وقتی که لازم رفتار اپلیکیشن رو لاگ کنید مثلا یه تغییر ناخواسته دیتایی تو سیستم رخ میده و شما نمیتونین سند معتبری برای اشکال اپلیکیشن پیدا کنین با اینکار به developer ها کمک میکنین و میتونین جزئیات خوبی از دیتابیس استخراج کنین.از نسخه 2016 به بعد قابلیت Temporal Tables به SQL Server اضافه شد، که میشه برای هر جدول فعالش کرد. در این صورت جدول شامل یه نسخه اصلی و یه جدول history میشه و تمام دیتاهایی رو که ویرایش یا حذف میشن توی جدول history به همراه تاریخ اون تغییر نگه میداره. میشه از قابلیت Temporal Table هم برای Auditing استفاده کرد، اما این امکان رو ندارین که دیتا جدیدی جدا از ستون های جدول اصلی (به جز تاریخ تغییر انجام شده) به جدول history اضافه کنین که با تریگر میشد اینکار رو هم کرد.مورد دوم : استفاده از * در select میدونیم که استفاده از * توی select هزینه اجرا کوئری رو افزایش میده، چون بجای چند ستونی که لازم هست تمام ستون ها رو از دیسک استخراج میکنه و میاره توی رم و گاهی حتی ممکنه تا کلاینت هم این دیتارو منتقل کنه.خب توجیه برنامه نویس ها برای استفاده از * چیه ؟ فرض کنین دیتابیس تازه طراحی شده و تو شروع کار جدولی که قرار روش کوئری نوشته بشه 5 ستون داره، وقتی از برنامه نویس خواسته میشه که از * توی select استفاده نکنه، میگه جدول کلا 5 تا ستون داره منم همشو لازم دارم خب چه کاریه کد شلوغ کنم یه * میزارم بجاش!بله الان جدول 5 تا ستون داره اما چند ساله دیگه ممکنه بشه 20 تا ستون، دیتابیس تازه طراحی شده و ممکنه نیازمندی ها تغییر کنه. همیشه جلوی این توجیه مقاومت کنین.استفاده از * برای Select توی کدی که بارها و بارها قرار توی اپلیکیشن استفاده بشه ممنوعه، در هر شرایطی.مورد سوم : Table variables یه نوع از متغیرهایی که میشه دیتارو بصورت موقتی توش ذخیره کرد table variable ها هستن. نوع Disk-based این متغیرها در TempDB دیتابیس ساخته میشن و کاراییشون شبیه به temp table ها وابسته یه دیسک سرور و میزان ممری کش دیتابیس هست. از نسخه 2014 نوع دیگه ای از این متغیر ها با عنوان memory-optimized tables l معرفی شد که تاخیر کمتر و عملکرد مناسب تری دارن. رفتار SQL Server در مورد Table variable ها سابقا یه اشکال اساسی داشت، یعنی اگه شما از نسخه های قدیمی تر SQL Server استفاده میکنین باید بدونین که برای این نوع متغیرها Statistic مناسبی نداره. اجازه بدین با یه مثال بهتون توضیح بدم. فرض کنین توی یه کوئری 100 تا رکورد رو به عنوان یه Table variable نگهدارین. SQL وقتی میخواد کوئری پلن مناسب رو بسازه، برآوردی از اینکه توی این متغیر چنتا رکورد هست نداره و نمیتونه درست estimate کنه. خب این اشکال سابقا وجود داشت و SQL Server سعی کرد این اشکال رفع کنه اما تقریبا به بدترین شکل ممکن ! بار اول اجرا کوئری تعداد رکورد تو متغیر رو میشمره و کش میکنه. دفعات بعد دیگه آمار داره که تو این متغیر چنتا رکورد هست.اما اشکال کار کجاس؟فرض کنین کوئری ما پارامتر ورودی داره و با تغییر این پارامترا تعداد رکورد ذخیره شده تو متغیر عوض میشه، خب ما کش کردیم که متغییرمون 100 تا رکورد داره اما الان با اجرا کوئری با متغیر جدید شد 1 دونه رکورد یا نه بازه بزرگ تر شد و شد 10000 رکورد . کوئری پلن حدس میزنه که عدد همون 100 تا رکورده و همون قدر مموری به عملیات تخصیص میده و کوئری پلن رو هم براساس همون انتخاب میکنه. کوئری که با بعضی پارامترها خوب کار میکنه با تغییر پارامتر عملکرد فاجعه ای داره، این موضوع به مشکلات Parameter Sniffing مربوط میشه که میتونین در موردش بیشتر بخونین.خب اگه از option recompile استفاده بکنیم چی ؟ هربار کوئری پلن رو دوباره تولید میکنه و از مقدار کش شده دفعه قبلی استفاده نمیکنه. بله درسته، برای استثنا میتونین یک یا دو کوئری رو هر بار recompile کنین . اما اگه اینکار رو برای همه کوئری ها انجام بدین کم کم میبینید که CPU سرور دیتابیس درگیر شد و مشکلات اساسی پیدا کردین. پس استفاده بی رویه از recompile بده چون مصرف CPU رو میبره بالا و این خطرناکه.مورد چهارم : big data typesموضوع big fields با عدم بکارگیری large object (LOB) در جداول دیتابیس متفاوت هست، این که ما بی دلیل یه متغیر از نوع varchar یا nvarchar با طول زیاد تعریف کنیم به این دلیل که اینها طول متغیر هستند،درست نیست. برای مثال فرض کنین ستون آدرس داریم و فقط به این دلیل که احتمال میدیم ممکنه یه روز آدرس با طول زیادی هم در اون ذخیره بشه نوع اون رو nvarchar(2000) در نظر بگیریم، اما اشکال این کار چیه ؟موقع ساخت کوئری پلن، SQL Server آمار کلی از دیتا ها داره و دقیق نمیدونه توی هر فیلد یه ستون چه دیتایی ذخیره شده پس با نگاه کردن به نوع جدول حدس میزنه که چقدر مموری لازم داره دو ستون nvarchar(2000) با nvarchar(200) میتونن باعث رفتار متفاوتی در SQL Server بشن.وقتی کوئری مموری بیشتری رو بگیره یعنی فضای کش کمتری برامون باقی میمونهوقتی دیتا فضای بیشتری رو اشغال کنه یعنی نیاز به IO بیشتری خواهیم داشتو حتی فضای مرتب سازی بیشتری مورد نیاز هست.مورد پنجم : جداول heapمیدونیم که هیپ به جدولی گفته میشه که کلاستر نیست یعنی ایندکس کلاستر نداره. داشتن primary key به طور پیش فرض به معنی داشتن ایندکس کلاستر هست (حتما اینطور نباید باشه اما رفتار پیش فرض SQL Server اینه)، پس همیشه وقتی داریم دیتابیس طراحی میکنیم و جداولمون رو میسازیم باید یادمون باشه که هیچ جدولی heap باقی نمونه . درواقع تمام جداولمون باید PK داشته باشن.فراموش نکنیم که جدول هیپ هم کاربرد خودش رو داره. مثلا تو فرآیند های ETL میتونه مفید باشه، یعنی وقتی که میخوایم دیتا رو به صورت bulk توی یه جدول درج کنی و بعد بخونیم و truncate کنیم مفیده. اما به صورت کلی برای جدول های اپلیکیشن جدول کلاستر مناسبه.نکته ای که باید بهش دقت کنیم اینکه برای انتخاب ستون PK دنبال بیزینس سازمان نباشین به حرف هیچ کس هم گوش نکنین حتی اگه بهتون گفتن خب کد ملی که یونیکه همونو بکن PK، اینکار رو انجام ندین. همیشه ستون PK رو خودتون بسازین یعنی ستون ID بسازین و اون رو PK کنین. استفاده از ستونهایی که دیتا بیزینس توش وجود داره همیشه گیرتون میندازه پس خودتون رو درگیر این ماجرا نکنین. گاهی پیش میاد که نیاز باشه PK رو به صورت NonClustered بسازیم و کلاستر ایندکس رو روی یکی از ستون های بیزینس ایجاد کنیم، این حالت کاربرد performance داره و در حالت عمومی بکار نمیره.مورد ششم : binariesانواع مختلف دیتا وجود دارن که نباید توی جدول دیتابیس ذخیره بشن، یه قانون کلی رو همیشه یادمون باشه:دیتایی حق داره توی دیتابیس ذخیره بشه که میشه بهش join زد، خب به دیتا باینتری میشه join زد ؟ شما میتونین به یه عکس جوین بزنین ؟ معلومه که نمیشه پس این دیتا نباید تو جدول دیتابیس باشه. تو نسخه های مختلف SQL Server راه حل های مختلفی برای ذخیره سازی دیتا باینری ارائه شده مثل FileStream و FileTable سراغ این روش ها برین و این تیپ دیتا ها رو از توی جدول دیتابیس SQL Server بکشین بیرون.مورد هفتم : old index اولین بحث در مورد ایندکسها اینه که چه زمانی ایندکس بسازیم، آیا با طراحی دیتابیس ساخت ایندکس هم باید انجام بشه؟ همونطور که گفتم PK رو باید همون اول ساخت، یعنی ایندکس کلاستر جدول همون اول ساخته میشه اما بقیه ایندکس ها چی ؟ باید گفت نه ، چون راهی برای شناسایی ایندکس های مورد نیاز نداریم. قرار نیست حدس بزنیم که مثلا این جدول person هست پس لابد روی name , lastname , nationalcode ایندکس لازم داره.این روش کاملا غلطه، شما نباید چیزی رو حدس بزنید. پس راه حل درست چیه ؟ بعد از طراحی دیتابیس و شروع کار اپلیکیشن چند هفته دیتابیس رو به حال خودش رها کنین و اجازه بدین ایندکس های مورد نیاز شناسایی بشه. SQL Server خودش اینکار براتون میکنه. با استفاده از Missing index و Unused index میتونین ایندکس های مورد نیاز رو بسازین و غیرضروری هارو حذف کنین.SQL SERVER – Unused Index Script-- Unused Index Script -- Original Author: Pinal Dave SELECT TOP 25 o.name AS ObjectName , i.name AS IndexName , i.index_id AS IndexID , dm_ius.user_seeks AS UserSeek , dm_ius.user_scans AS UserScans , dm_ius.user_lookups AS UserLookups , dm_ius.user_updates AS UserUpdates , p.TableRows , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement' FROM sys.dm_db_index_usage_stats dm_ius INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1 AND dm_ius.database_id = DB_ID() AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC GOSQL SERVER – Missing Index Script-- Missing Index Script -- Original Author: Pinal Dave SELECT TOP 25 dm_mid.database_id AS DatabaseID, dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact, dm_migs.last_user_seek AS Last_User_Seek, OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName], 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL (dm_mid.equality_columns,'') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (dm_mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement FROM sys.dm_db_missing_index_groups dm_mig INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle WHERE dm_mid.database_ID = DB_ID() ORDER BY Avg_Estimated_Impact DESC GOدو کوئری بالا مربوط به سایت blog.sqlauthority.com هست.نکته مهمی که درمورد ایندکس وجود داره اینکه با ساختن اونها باز هم کار ما تموم نمیشه، هر چند وقت یبار مثلا بصورت برنامه ماهانه نیاز هست تا چک بشن. رفتار اپلیکیشن و همینطور رفتار کاربرها ممکنه تغییر کنه. مثلا تو سیسم حقوق و دستمزد رفتار شروع ماه با پایان ماه متفاوته یا آخر سال کلا همه چیز عوض میشه. ما اگه تو یه دوره کوتاه ایندکس ها رو بسازیم و سیستم رو رها کنیم برای دوره های بخصوص مثل موردی که اشاره کردیم ایندکس مناسب نخواهیم داشت و این یعنی یه مشکل جدی توی Performance دیتابیسون.مدیریت ایندکس در SQL Serverدر پایان باید گفت مواردی که اشاره کردیم توصیه های عمومی هستن و ممکنه تو حالت های خاصی نقض بشن، اما به طور کلی مفید و کاربردی هستن.منبع : brentozar.com

یه دیتابیس ادمین واقعا چه کاری انجام می‌ده؟

نیما جمشیدی — Fri, 19 Mar 2021 16:00:27 +0330

یک DBA باید کنترل کنه که دیتابیس‌ها بک آپ دارن، به خوبی کانفیگ شده و بروز هستن، امنیت لازم رو دارن و سرعت عملکردشون مناسبه.من شغلم رو اینجوری برای دوستانم تعریف می‌کنم : " گاهی دیدین سایتها کند هستن یا دیتا شما رو از دست میدن؟ این وظیفه منه که چنین اتفاقی نیوفته. من تنها فرد درگیر تو این ماجرا نیستم چون برنامه نویس‌ها و مهندس‌های دیگه هم هستن، اما من مسئول بخشی هستم که دیتا شما رو نگهداری می‌کنه مثل پروفایل‌تون ، سفارش‌هاتون، تاریخچه مرورگرتون و در واقع تمام دیتایی که شرکت ها در مورد شما جمع آوری کردن!!"وظیفه یه دیتابیس ادمین چیه ؟یه DBA باید کنترل کنه که همه دیتابیس ها به نحوی بک آپ داشته باشن که بیزینس سازمان رو برای Recovery Point Objectives (RPO) پوشش بدن.تست کردن بک آپ های تهیه شده به صورت دوره ای.رفع مشکلات پیش اومده برای دیتابیس بعد از ساعات کاری یا پایان هفته.راه اندازی ابزار مناسب مانیتورینگ برای مانیتور کردن آنلاین خطاهای احتمالی.با توجه به قابلیت‌های جدید فراهم شده تو دیتابیس‌ها، انتخاب اینکه چه زمانی از clustering, log shipping, mirroring, Windows Azure و دیگر تکنولوژی ها استفاده بشه.نصب و راه اندازی سرور های جدید دیتابیس.بروزرسانی سرورهای دیتابیس موجود.اعمال change script ها روی دیتابیس. وقتی که مشکل performance پیش میاد، تشخیص بهترین راه کار موجود برای بهبود کارایی سیستم شامل تغییرات سخت افزاری، تغییرات server configuration، یا ساخت ایندکس و اصلاح کوئری ها به عهده DBA هست.داکیومنت کردن دیتابیس های سازمان و موارد مربوط به اون.کمک به کاهش هزینه سرورهای سازمان با performance tuning، مشاوره های لازم و اصلاح حجم دیتابیس ها.چرا وجود یه دیتابیس ادمین برای سازمان مهمه ؟وقتی دیتابیس دان بشه، تمام بیزینس متوقف می‌شه. اگه شما فروشنده آنلاین هستین و وب سایت شما دان بشه، قطعا یه وضعیت اورژانس پیش میاد. حتی با کند شدن دیتابیس میتونه موقعیت اضطراری برای سازمان ایجاد کنه. حضور DBA ها میتونه در این مواقع موثر باشه.وقتی یه نفر تصادفی دیتا رو delete میکنه، تمام بیزینس میتونه دچار مشکل بشه، متاسفانه تو سازمان ها افراد زیادی هستن که به دیتابیس دسترسی دارن و گاهی بین اونها افراد غیر متخصص هم وجود دارن و حتی متخصص ها هم ممکنه گاهی اشتباه کنن. DBA ها کمک میکنن که از بروز فاجعه جلوگیری بشه.مدیران پایگاه داده کجا کار میکنن؟وظیفه DBA ها حفاظت از دیتا مهم هست، پس هرجا دیتا مهم وجود داره میتونین اونارو پیدا کنین. امروزه دیتا مهم همه جا هست پس DBA ها رو همه جا میتونین پیدا کنین، تو همه سازمان های بزرگ و کوچیک.معمولا شرکت های کوچیک با 1 یا 2 دیتابیس به اندازه لازم کار برای DBA تمام وقت ندارن، در این موارد معمولا فردی در شرکت هست که بخشی از تایمش رو به انجام وظایف DBA مشغوله، به این افراد به اصطلاح “accidental DBAs” گفته میشه. احتمالا خیلی از DBA های فعلی زمانی اینجوری وارد این شغل شدن و گاهی هم هیچ قصدی برای این حوزه ندارن و فقط مسئول بخشی از این وظایف هستن.هرچه شرکت بزرگتر باشه DBA های بیشتری داره، شرکت های بزرگ معمولا تیم از DBA ها دارن.آیا دیتابیس ادمین به صورت ریموت و از خونه میتونه کار کنه؟بله، میتونه و باید این اجازه بهش داده بشه. این روزها همه دارن از خونه کار میکنن اما گاهی مدیرها دوست دارن DBA هاشون رو در محل کار ببینن. حساسیت بی موردی در این زمینه وجود داره چون DBA ها از با ارزش ترین دارایی شرکت حفاظت میکنن، یعنی دیتا شرکت.بخاطر شرایط اضطراری معمولا DBA ها در جلسات موقت حضور دارن و بنا به سطح امنیت این موارد، مدیر دوست داره رو در رو، آدما رو تو جلسه ببینه و تو اتاق گیرشون بندازه.گاها شرکت ها ترجیح میدن به دلایل زیر DBA های ریموت رو استخدام کنن:شرکت تو نقطه دور افتاده ای قرار داره برای مثال، یکی از مشتریان من در شهر کوچیکی هست و براحتی نمیتونه توی اون شهر DBA پیدا کنه. همچنین DBA ها حاضر نمیشن بخاطر این شغل محل زندگیشون تغییر بدن، پس کمپانی ناچاره که از DBA ریموت استفاده کنه.شرکت به دنبال مهارت ویژه ای هست که سخت پیدا میشه برای مثال، یکی از مشتریان من نیاز به راه اندازی Distributed Availability Groups داشت که به ندرت استفاده میشه و تعداد DBA های که این تجربه رو داشته باشن خیلی محدوده، پس ناچار شد این بخش از کار رو به DBA ریموت بسپاره.در مقایسه با تحلیل‌گر داده، وظیفه DBA چیه؟تحلیلگر داده کوئری می‌نویسه تا جواب های مناسب رو از دیتا استخراج کنه، اما DBA اطمینان حاصل میکنه که جواب کوئری با سرعت مناسبی دریافت بشه.تحلیلگر داده زمان زیادی صرف میکنه تا کوئری های جدید در SQL بنویسه، از ابزارهای گزارش استفاده میکنه، اونها معمولا به طور مستقیم با کاربران بیزینس کار میکنن و اطلاع کاملی از بیزینس و جزئیات اون نسبت به DBA ها دارن. دقیقا میدونن هر جدول در دیتابیس چه معنی داره و ارتباط بین جداول رو هم کاملا شناخت دارن و کار تحلیل، نوشتن گزارش و صحبت با کاربران رو انجام میدن تا نیازمندی ها رو مشخص کنن.یه دیتابیس ادمین معمولا اطلاعات کمتری از جزئیات بیزینس داره. اونها بیشتر در مورد سیستمی که دیتا رو نگهداری می‌کنه میدونن مثل Microsoft SQL Server, Postgres, MySQL, Oracle و غیره، DBA میدونه که کدوم کاربر کدوم کوئری رو بیشتر اجرا میکنه و ممکنه به تحلیلگر کمک کنه تا کوئری های بهتری اجرا کنه اما معمولا نویسنده کوئری های جدید نیست.خب یکم پیچیده شد، چون عادت کرده بودیم عنوان DBA رو به همه چیز بدیم . "اگه داره رو دیتا کار میکنه، پس دیتابیس ادمینه دیگه"تفاوت دو سطح junior DBA و senior DBA چیه؟یه junior DBA بخش زیادی از کارش رو داره برای اولین بار انجام میده و برای انجام کارهاش وابسته به google و جستجو در اون هست.یه senior DBA چندین سال رو به عنوان junior DBA سپری کرده و به انجام کارهای معمول مثل configuring backups, troubleshooting outages, managing security, and responding to end user requests ،عادت داره.به این معنی نیست که از گوگل استفاده نکنه یا کاری رو برای اولین بار انجام نده اما حداقل نیمی از کارهاش رو قبلا بارها و بارها انجام داده و اگر هر قرار باشه چیزی رو گوگل کنه برای انجام کار با روش بهینه تر هست مثلا انجام اتوماتیک کارها با استفاده از PowerShell.تعداد سالهای تجربه کار به تنهایی نمیتونه فردی رو از junior به senior ارتقا بده، وجود دارن افرادی که بعد از 10 سال، کارها رو همونجوری انجام میدن که روز اول انجام میدادن و هیچ تجربه ای در مورد انجام کارها به صورت اتوماتیک یا انجام کارهای پرچالش بدست نیاوردن.یه DBA از چه ابزارهایی استفاده می‌کنه؟زمانی که یک سرور SQL رو مدیریت میکنیم، معمولترین ابزار SQL Server Management Studio است. یک ابزار رایگان مایکروسافت که برای ده ها استفاده شده و از همان ابتدا بعنوان کنسول اصلی پایگاه داده نوشته شد. اینکه گاهی DBA ها از ابزار های دیگه برای مدیریت پایگاه داده استفاده میکنن فقط به این دلیل که SSMS قدیمی هست یا جزئیات زیادی داره، خیلی منصفانه نیست. من بیشتر زمانم رو در این ابزار سپری میکنم.زمانی که تعداد زیادی سرور SQL رو مدیریت میکنیم، DBA ها معمولا از PowerShell استفاده میکنن، معمولا به کمک فریم ورک DBAtools.io. استفاده از PowerShell رو میتونین با کتاب Learn PowerShell Scripting in a Month of Lunches شروع کنین.برای نوشتن کد جدید، مایکروسافت از ما میخواد که از ابزار Azure Data Studio استفاده کنیم. چیزی شبیه به Visual Studio Code. یک اپلیکیشن cross-platform که میشه کارای دیتابیس ادمینی هم باهاش انجام داد اما در اصل برای دیتابیس Developer ها مناسب هست. برای مثال من برای کوئری نویسی همزمان در Microsoft SQL Server و Amazon RDS Aurora Postgres از Azure Data Studio استفاده کردم چون با هردو کار میکنه.چه آموزش هایی کمک میکنه که اولین شغل DBA مون رو بدست بیاریم؟شرکت ها تقریباً هرگز DBA بدون تجربه رو استخدام نمی کنن. برای به دست آوردن این تجربه ، دو مسیر شغلی وجود داره.ابتدا به عنوان یک Developer شغل پیدا کنین و زمان زیادی رو برای SQL Server صرف کنین. کم کم با object تو SQL Server مثل جدول ها و ایندکس ها و روش ذخیره سازی دیتا آشنا میشین و کم کم میتونین یه “the database person” بشین تا درنهایت موقعیت ادمین پایگاه داده رو بدست بیارین. یا ابتدا به عنوان sysadmin کار کنین و به عنوان بخشی از وظایف کلی مدیریت سرور، با سرورهای پایگاه داده درگیر بشین. با پیشرفت شغلتون، سرورهای دیتابیس جدید نصب میکنین، سرورهای موجود را وصله می کنین، موقع خراب شدنشون عیب یابی میکنین و کم کم مسیر DBA شدن رو طی میکنین.در هردو حالت نقص هایی دارین، اگه Developer باشین جزئیات نصب و راه اندازی رو نمیدونین و اگه sysadmin باشین دانشتون در مورد کوئری نویسی کم خواهد بود اما در ادامه کار به عنوان DBA فرصت دارین تا ضعف‌هاتون رو برطرف کنین.آیا تازه کارها می توانند به عنوان مدیر پایگاه داده کار کنن؟اگه تازه از دانشگاه فارغ التحصیل شدین بعیده مستقیما DBA بشین. بهتره همونطور که گفتم اول یه Developer یا sysadmin بشین و بعد تمایلتون رو به DBAشدن نشون بدین.اگه شرکتی رو پیدا کردین که تازه کارها رو به عنوان DBA استخدام میکنن، خیلی مراقب باشین چون این شغلها خیلی کم درآمد هستن و درنهایت مجبور میشین بخاطر درآمد کم شرکت رو ترک کنین. به این امید که با تخصصی که تو این شرکت به دست آوردین بتونین شغل DBA مناسبی رو پیدا کنین. اما ناامید خواهید شد چون اون شرکت چیزی رو بشما یاد داده که مورد نیازش بوده ، بخش محدودی از وظایف یه DBA و در دنیای واقعی شما هنوز با یه ادمین پایگاه داده فاصله دارین.داشتن گواهی یه دوره معتبر برای بدست آوردن شغل کافیه؟تو دنیای Microsoft SQL Server، باید گفت نه کافی نیست. متاسفانه حتی دوره های مایکروسافت هم تمام نیاز یه DBA رو پوشش نمیده، چون اونا دوست دارن مشتریا بسمت خدمات ابری مایکروسافت بیان، پس فقط چیزی رو یاد میدن که مایلن ما یاد بگیریم نه چیزی رو که واقعا نیاز هست.متاسفانه دوره ها مسیر خوبی برای بدست آوردن اولین شغل DBA نیستن. به جای اون، روی کار به عنوان Developer یا sysadmin تمرکز کنین و بعد در محل کار خودتون کم کم یه “the database person” بشین.آیا DBA ها همچنان در فضای ابری شغلی دارن؟من این سوال رو زیاد میشنوم که تو فضای ابری، باز هم نیاز به DBA ها هست ؟ مثلا Azure SQL DB رو در نظر بگیرین. بعضی از وظایفی که تا حالا گفتیم تو سیستم عامل های پایگاه داده ابری به صورت خودکار انجام می‌شه، اما خیلی از کارها اینجوری نیست. برای مثال اگه نیاز بشه فایل پشتیبان چند ماه پیش رو نگهدارین. علاوه بر این بحث های Performance و بهینه سازی دیتابیس ها همچنان نیاز به وجود DBA ها داره.مدیر پایگاه داده بودن شغل خوبیه؟اگه حوصله اینو دارین که سالها یه کاری رو انجام بدین تا به تدریج به موقعیت DBA برسید، بله شغل مناسبیه. شما تبدیل میشین به مرکز داده شرکتی که توش کار میکنین، به این معنی که میدونین هرجای شرکت چه خبره، با همه کارمندای IT در ارتباطین، میدونین چه پروژه هایی در جریانه و میتونین به همه کمک کنین. با همه اینا، اشکالتی هم داره :بدست آوردن اولین شغل DBA سخته.تبدیل شدن از Junior به senior بدون تغییر شرکت تقریبا غیرممکنه.بخش اصلی کارهای شما همزمان با ارتباط برقرار کردن با دیگرانه و برخلاف Developer ها این بخش کارتون ممکنه آزار دهنده باشه.گاهی ممکنه مسئولیت زیادی داشته باشین اما قدرت تغییر شرایط از شما گرفته شده باشه و توی یه سیستم خراب گیر بیوفتین.طی سالها وضعیت این شغل ثابت بوده اما بزودی ممکنه همه چیز متحول بشه و شرایط بکلی تغییر کنه پس باید همیشه آماده تغییرات باشین.منبع : www.brentozar.com

مشکل Concurrency - تئوری و راه حل در SQL Server

نیما جمشیدی — Wed, 10 Mar 2021 15:17:39 +0330

این مطلب میتونه برای برنامه نویس‌ها و ادمین های پایگاه داده که دوست دارن در مورد مشکل همزمانی در Isolation level های مختلف دیتابیس SQL Server بدونن، مناسب باشه.Concurrencyمفاهیم :در هر دیتابیس رابطه ای، مفهوم transaction یا تراکنش وجود داره. تراکنش به مجموعه ای از چند عملیات منطقی گفته می‌شه که در یک user session به عنوان یه کار واحد انجام می شن.اجازه بدین ویژگی های یک transaction رو بررسی کنیم: ویژگی Atomicityیه transaction باید اتمیک باشه یعنی نمیشه بخشی از عملیات توش انجام بشه و بخشی نشه، یا تمام عملیات باید کامل انجام شده باشه یا هیچ کدوم انجام نشه.ویژگی Consistencyیه transaction باید consistent باشه، یعنی هر دیتایی که توسط دیتابیس سیستم نوشته میشه باید بر اساس قوانین تعریف شده در دیتابیس معتبر باشه، قوانینی مثل primary key , foreign keyویژگی durabilityوقتی از Consistency مطمعن شدیم، تراکنش به طور دائم در دیسک ذخیره میشه و این ویژگی durability رو برای تراکنش ایجاد میکنه.به مجموع این 3 ویژگی به همراه ویژگی isolation ، خاصیت ACID گفته میشه که لازمه تمامی دیتابیس های رابطه ای هست.تراکنش ها میتونن به طور همزمان در دیتابیس اجرا بشن، یعنی می‌تونیم تراکنش هایی رو پیدا کنیم که در حال خوندن یا نوشتن روی یک data object هستند(row,table,index). این موضوع مجموعه ای از مشکلات رو برای سطوح isolation ایجاد میکنه.تنظیم Isolation level یک تراکنش مشخص می‌کنه که چگونه و چه زمانی دیتابیس تغییرات ایجاد شده توسط هر تراکنس رو در اختیار دیگر کاربران قرار بده. پس برای انتخاب transaction isolation level مناسب ، داشتن درک درست از مشکلات معمول concurrency برامون ضروریه.مشکل Concurrencyقبل از ورود به جزئیات transaction level ، مهمه که با انواع معمول Concurrency و اصطلاحاتش آشنا بشیم .نوع اول : Lost update and dirty writeاین حالت وقتی پیش میاد که دو تراکنش به یک رکورد دسترسی پیدا می‌کنن و هر دو اونو آپدیت میکنن. تصویر پایین خلاصه این وضعیت رو نشون میده.Lost update and dirty writeتو این مثال ما 2 تراکنش داریم که همزمان به رکورد با مقدار 60 دسترسی پیدا می‌کنن و مقدار اونو تغییر میدن . تراکنش اول این رکورد رو میخونه ، پروسس رو انجام میده و در نهایت تراکنش رو commit میکنه.تراکنش دوم رکورد رو میخونه و بعد از آپدیت commit میشه. هر تراکنش رکورد رو به مقدار متفاوتی بروزرسانی میکنه و منجر به از دست رفتن بروز رسانی یکی از تراکنش ها میشه.مفهوم Dirty readزمانی که تراکنش به دیتایی دست پیدا میکنه که قبلا توسط تراکنش دیگه ای ویرایش شده اما این تغییر هنوز نه commit شده و نه roll back در این صورت Dirty read اتفاق میوفته.شکل زیر مفهوم dirty read رو نشون میده. در این مثال رکورد دو ستون داره با مقدار اولیه 60,40 .فرض کنید ما شرطی داریم که میگه مجموع این دو ستون باید همیشه 100 باشه.تراکنش دوم دیتایی رو خونده که مجموع دو مقدار در اون 100 نیست، به این دلیل که بهش اجازه داده شد وسط تراکنش مربوط به session 1 دیتا commit نشده رو بخونه. در واقع dirty read انجام داد.نوع دوم : Non-repeatable read or fuzzy readاین وضعیت وقتی اتفاق میوفته که توی یک تراکنش یک کوئری دو نتیجه مختلف برگردونه ، طبق شکل زیر session 2 توی یک ترکنش دوبار select کرده اما هربار نتیجه متفاوتی برگردونده شده .Non-repeatable read or fuzzy readاین بار هم به Session 2 اجازه داده شد وسط تراکنش 1 ، از همون دیتا بخونه!نوع سوم : Phantom readsشبیه Non-repeatable read در این مورد رخ میده ، به مثال زیر توجه کنینفرض کنید تراکش 1 رو داریم که شامل دو select از جدول T هست یکی وقتی تراکنش شروع میشه و یکی هم درست پیش از اتمام تراکنش. تراکنش 2 رو بعد از اولین select تراکنش 1 شروع میشه و رکورد جدیدی به جدول T اضافه میکنه و قبل از select دوم Commit میکنه .باز هم نتیجه دو select اجرا شده در یک تراکنش متفاوت شد.نوع چهارم : Locking readsاین درواقع مشکل Concurrency نیست و بیشتر مشکل design pattern هست. به طور خلاصه، قرار هست یک رکورد رو بخونیم و با مقداری که تو همون تراکنش به دست اومده بروزرسانی کنیم. با تضمین اینکه session دیگه ای نمی‌تونه مقدار دیتایی رو read شده رو تغییر بده .پیاده سازی در SQL Serverاگه تغییری تو transaction isolation level سطح SQL Server داده نشده به طور پیش فرض به صورت READ COMMITTED هست.حالت اول : Lost updateفرض کنین یک سیستم بانکی داریم و حسابی برای شما با موجودی 1500 افتتاح شده.CREATE TABLE BankAccounts ( AccountId INT IDENTITY(1,1), BalanceAmount INT); insert into BankAccounts (BalanceAmount) SELECT 1500;مدیرتون تصمیم میگیره حقوق 1600 دلاری شما رو پرداخت کنه.-- Session 1: Employer DECLARE @CustomerBalance INT ; DECLARE @BalanceDifference INT ; SET @BalanceDifference = 1600 ; BEGIN TRANSACTION ; -- Getting back current balance value SELECT @CustomerBalance = BalanceAmount FROM BankAccounts WHERE AccountId = 1 ; PRINT 'Read Balance value: ' + CONVERT(VARCHAR(32),@CustomerBalance); -- adding salary amount SET @CustomerBalance = @CustomerBalance + @BalanceDifference ; -- Slowing down transaction to let tester the time -- to run query for other session PRINT 'New Balance value: ' + CONVERT(VARCHAR(32),@CustomerBalance); WAITFOR DELAY '00:00:10.000'; -- updating in table UPDATE BankAccounts SET BalanceAmount = @CustomerBalance WHERE AccountId = 1 ; -- display results for user SELECT BalanceAmount as BalanceAmountSession1 FROM BankAccounts WHERE AccountId = 1 COMMIT ;همزمان دوستتون بدهی 40 دلاری رو بهتون برمی‌گردونه.-- Session 2: Web reseller DECLARE @CustomerBalance INT ; DECLARE @BalanceDifference INT ; SET @BalanceDifference = 40 ; BEGIN TRANSACTION ; -- Getting back current balance value SELECT @CustomerBalance = BalanceAmount FROM BankAccounts WHERE AccountId = 1 ; PRINT 'Read Balance value: ' + CONVERT(VARCHAR(32),@CustomerBalance); -- adding salary amount SET @CustomerBalance = @CustomerBalance + @BalanceDifference ; PRINT 'New Balance value: ' + CONVERT(VARCHAR(32),@CustomerBalance); -- updating in table UPDATE BankAccounts SET BalanceAmount = @CustomerBalance WHERE AccountId = 1 ; -- display results for user SELECT BalanceAmount as BalanceAmountSession2 FROM BankAccounts WHERE AccountId = 1 ; COMMIT ;خب نتیجه دو تراکنش انجام شده این خواهد بود :Session 1Session 2متاسفانه بدهی پرداختی دوستتون رو از دست دادین !حالت دوم : Dirty readبرای تشریح Dirty read ما دیتایی رو تو جدول Person.Person بروزرسانی می‌کنیم.SELECT COUNT(DISTINCT LastName) DistinctLastNameBeforeBeginTran FROM Person.Person WHERE FirstName = 'Aaron'; BEGIN TRANSACTION; UPDATE Person.Person SET LastName = 'Hotchner' WHERE FirstName = 'Aaron' SELECT COUNT(DISTINCT LastName) DistinctLastNameInTransaction FROM Person.Person WHERE FirstName = 'Aaron'; WAITFOR DELAY '00:00:10.000'; ROLLBACK TRANSACTION; SELECT COUNT(DISTINCT LastName) DistinctLastNameAfterRollback FROM Person.Person WHERE FirstName = 'Aaron';تو مدت wait که تو کوئری وجود داره میتونیم تعداد Aaron ها رو بشمریم :SELECT COUNT(DISTINCT LastName) SecondSessionResults FROM Person.Person WHERE FirstName = 'Aaron';با توجه به اینکه تنظیمات روی default isolation level هست ، Session دوم منتظر میمونه تا اولی تموم بشه.خب پس میتونیم بگیم SQL Server به طور پیش فرض ما رو از dirty read محافظت میکنه. اجازه بدین isolation level به READ UNCOMMITTE تغییر بدیم و ببینیم آیا SQL دیتا dirty رو به Session دوم میده یانه ؟SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;و حالا کوئری Session 2 رو اجرا می‌کنیم.Session 1Session 2نتیجه 1 برگردونده شد و wait هم نداشت. پس session 2 تونست وسط تراکنش اول دیتا رو بخونه.حالت سوم : Non-repeatable readsاگه توضیحات non-repeatable reads یادتون باشه اشاره می کرد به اینکه دو کوئری read تو یک تراکنش با توجه به زمان اجرا میتونه نتایج متفاوتی برگردونه.از دو session استفاده می‌کنیم، session اول کوئری select هست که 5 رکورد اول از جدول Person. Person برمی‌گردونه، یه زمانی رو صبر میکنه و مجددا همون کوئری رو اجرا میکنه.کوئری session اول :-- ensure we use SQL Server default isolation level SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRANSACTION; -- Query 1 - first run SELECT TOP 5 FirstName, MiddleName, LastName, Suffix FROM Person.Person ORDER BY LastName -- let some time for session 2 WAITFOR DELAY '00:00:10.000'; -- Query 1 - second run SELECT TOP 5 FirstName,MiddleName,LastName,Suffix FROM Person.Person ORDER BY LastName COMMIT TRANSACTION;زمانی که session اول waiting هست ، session دوم رو اجرا میکنیم تا FirstName و LastNmae رو آپدیت کنه.-- ensure we use SQL Server default isolation level SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRANSACTION; UPDATE Person.Person SET Suffix = 'Clothes' WHERE LastName = 'Abercrombie' AND FirstName = 'Kim'; COMMIT TRANSACTION; نتیجه بدست آمده از Session 1 :می‌تونیم تغییر رو به شکل زیر انجام بدیم:UPDATE Person.Person SET Suffix = NULL WHERE LastName = 'Abercrombie' AND FirstName = 'Kim';اگه مایلیم از رخ داد همچین چیزی جلوگیری کنیم باید در مورد REPEATABLE READ isolation level بدونیم،پس بهتره تستش کنیم. برای session اول فقط لازمه وضعیت رو از SET TRANSACTION ISOLATION LEVEL READ COMMITTED;به SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;تغییر بدیم .درواقع با تغییر ISOLATION LEVEL دیتابیس 2 session رو وادار کردیم تا منتظر تموم شدن session 1 بمونه. نتیجه بدست اومده :session 1session 2در آخر تغییر انجام شده رو به حالت قبلی برمی‌گردونیم تا تست آخر رو انجام بدیم.حالت چهارم Phantom reads :برای این تست یک جدول dbo.Employee درست می‌کنیم و خالی نگهش میداریم.IF (OBJECT_ID('dbo.Employee') IS NOT NULL) BEGIN DROP TABLE [dbo].[Employee]; END; CREATE TABLE [dbo].[Employee] ( EmpId int IDENTITY(1,1) NOT NULL, EmpName nvarchar(32) NOT NULL, CONSTRAINT pk_EmpId PRIMARY KEY CLUSTERED (EmpId) );برای Session 1 یه تراکنش شامل دو کوئری select روی این جدول با فاصله زمانی 10 ثانیه اجرا می‌کنیم.-- ensure we use SQL Server default isolation level SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRANSACTION; -- Query 1 - first run SELECT * FROM dbo.Employee -- let some time for session 2 WAITFOR DELAY '00:00:10.000'; -- Query 1 - second run SELECT * FROM dbo.Employee COMMIT TRANSACTION;تو session 2، تراکنش درج رکورد اجرا می‌کنیم.-- ensure we use SQL Server default isolation level SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRANSACTION; INSERT INTO [dbo].[Employee] ([EmpName]) VALUES ('Oby'); INSERT INTO [dbo].[Employee] ([EmpName]) VALUES ('One'); INSERT INTO [dbo].[Employee] ([EmpName]) VALUES ('Ken'); INSERT INTO [dbo].[Employee] ([EmpName]) VALUES ('Tukee'); COMMIT TRANSACTION;نتیجه به دست آمده از session اول :با توجه به اینکه بین دو select تراکنش اول ، تراکنش دوم توی جدول insert انجام داد ، نتیجه بدست اومده تو یه تراکنش متفاوت شد.خلاصه :همانطور که دیدیم امکان بدست آوردن نتایج مختلف با کوئری یکسان در یک تراکنش با توجه به cuncurrency و isolation level وجود داره و همزمانی تراکنش‌ها میتونه مشکلاتی رو به وجود بیاره. تنظیم درست isolation level با توجه به سناریو تعریف شده می‌تونه از خرابی دیتا جلوگیری کنه.منبع :www.sqlshack.com

پیاده سازی وب اپلیکیشن پایتون (Flask) در IIS ویندوز سرور

نیما جمشیدی — Wed, 27 Jan 2021 23:05:39 +0330

با اینکه ویندوز محیط مناسبی برای نصب پایتون نیست، گاهی مجبور به انجام اینکار هستیم. تو این مطلب تلاش می‌کنم، وب اپلیکیشن پایتون رو با استفاده از IIS تو محیط ویندوز سرور راه اندازی کنم. مسلما راه اندازی این پروژه تو محیط لینوکس با nginx یا apache ساده تره، اما وقتی تمام وب اپلیکیشن های شرکت تو محیط ویندوز سرور هست و شما برای ایجاد یک داشبورد دیتا نیاز به ساخت وب اپلیشن پایتون دارین، منطقی نیست که بخواهین اون رو تو سرور جدا گانه ای پیاده سازی کنین.نصب IIS به همراه FastCGI و پایتونتو محیط ویندوز سرور به مسیر زیر می‌ریم Server Manager -> Manage -> Add Role and Features:گزینه Role-based or feature-based installation انتخاب می‌کنیم سرور رو انتخاب می کنیم گزینه Web Server (IIS) انتخاب می‌کنیمروی Add Features کلیک می کنیمبجز موارد مورد نیاز معمول برای نصب IIS ، ویژگی CGI رو هم انتخاب می‌کنیمانتخاب Next و Install برای شروع فرآیند نصب حالا IIS رو اجرا می‌کنیماگه پیغام زیر نمایش داده شد کلید YES رو بزنین_ در غیر اینصورت نیاز هست که Web Platform Installer رو نصب کنینو حالا Web Platform Installer رو اجرا کنینکلمه WFastCGI رو جستجو کنین و این فیچر رو برای IIS و پایتون نصب کنیننسخه مناسب پایتون رو انتخاب کنین (نسخه 3.4 ) و کلیک Add و Install رو بزنیندر آخر پیغام زیر می‌بینیم نکته خیلی مهم : متاسفانه من با این روش نتونستم WfastCGI رو نصب کنم. بخاطر نبودن اینترنت در سرور عملیاتی و احتمالا تحریم بودن IP ایران تو مراحل نصب خطا داد(با وجود دسترسی به اینترنت) لذا برای رفع این مشکل مجبور شدم فایل رو بصورت آفلاین دانلود و نصب کنم.نصب WFastCGI به صورت آفلاینبرای دانلود WFastCGI به سایت pypi رفته و فایل رو دانلود می‌کنیم https://pypi.org/project/wfastcgi/و اقدام به نصب پکیج می‌کنیم pip install wfastcgiبه جای wfastcgi ، دایکتوری فایل دانلود شده رو به همراه اسم اون رو میزاریم و بعد این قابلیت رو فعال می‌کنیمwfastcgi-enableایجاد سایت جدیدیک سایت جدید در IIS ایجاد می‌کنیم و فایل پروژه فلسک رو که قبلا نوشتیم ،توی پوشه سایت کپی می‌کنیم و بعد به سراغ دایکتوری پایتون میریم و فایل wfastcgi.py رو هم کپی می‌کنیمتوجه داشته باشین که فایل wfastcgi.py ممکنه تو مسیر روت C:\Python34 یا C:\Python34_x86 و یا حتی تو پوشه site-packages باشههمونطور که می‌بینین تو پوشه mysite حتما باید دو فایل وجود داشته باشه. فایل app.py که کد پایتون وب اپلیکیشن فلسک هست و فایل wfastcgi.py مربوط یه فیجر نصب شدهبین ماژول های سایت، روی “Handler Mappings” کلیک می‌کنیم و “Add Module Mapping” رو انتخاب می‌کنیمتنظیمات رو دقیقا مثل تصویر انجام می‌دیم .دقت کنید که ممکنه مسیر پایتون و مسیر سایتمون متفاوت باشه، اما حتما باید Python.exe و wfastcgi.py رو داشته باشیم. برای مثال : C:\Python34\Python.exe|C:\inetpub\wwwroot\mysite\wfastcgi.py روی “Request Restrictions” کلیک کنین و مطمئن بشین که تیک زده نشده تو مرحله بعد Yes رو انتخاب کنینحالا توی IIS به تنظیمات روت میریم و روی FastCGI Settings کلیک می‌کنیم روی “…” جلو Environment Variables بزنین تا بتونیم مقادیر مورد نیاز رو ست کنیماینجا دو مقدار باید ست بشه، اول PYTHONPATH ،که مسیر پوشه سایت ایجاد شده هستو دوم WSGI_HANDLER ، با مقدار app.app (دقت کنین که وقتی اسم اپلیکیشن app.py باشه اسم انتخابی میشه app.app )حالا OK رو کلیک کنین و سایت رو browse کنیندرنهایت باید وب کانفیگ شبیه به زیر برای اپلیکیشن ایجاد شده باشه: کد استفاده شده تو فایل app.py :from flask import Flask app = Flask(__name__) @app.route("/") def hello(): return "Hello from FastCGI via IIS!" if __name__ == "__main__": app.run()اگه تمام مراحل بالا رو درست انجام داده باشین، می‌تونین خروجی سایت رو روی IIS ببینین و با کمک Binding port و URL دلخواهتون رو برای سایت تنظیم کنین.منابع :medium.compypi.org

استفاده از Icecream بجای Print برای Debug در پایتون

نیما جمشیدی — Tue, 19 Jan 2021 13:43:42 +0330

اگه شما هم عادت دارین از دستور Print برای لاگ کردن موقع دیباگ کد پایتون استفاده کنین، از امروز می خواهیم دستور print رو با Icecream جایگزین کنیم.وقتی داریم از print برای دیباگ کردن کد استفاده می کنیم، همیشه گیج کننده است که بین چندین لاین کد برگردیم و تشخیص بدیم کدوم خروجی پرینت شده مال کد خط از کد هست.برای مثال ، با اجرا اسکریپت زیر : num1 = 30 num2 = 40 print(num1) print(num2)این خروجی رو می بینیم :30 40کدوم یک از این عددا مربوط به num1 هست و کدوم num2 ؟ خب چون دوتا خروجی داریم تشخیصش سخت نیست اما اگه تعداد زیادی بود چی ؟ گیج کننده و زمانبر بود تا بفهمیم خروجی برای کدوم پرینت هست.خب میشه پیشنهاد بدیم که اسم متغیر رو هم کنار مقدارش چاپ کنیم :num1 = 30 num2 = 40 print('num1', num1) print('num2', num2)و خروجی زیر رو بدست بیاریم:num1 30 num2 40این خروجی مفهوم تر شد، اما همچنان نوشتن کد اضافی زمانبره، آیا راهی وجود داره که چنین خروجی رو با کد ساده تری تولید کنیم ؟num1 = 30 num2 = 40 ic(num1) ic(num2) و نتیجه :ic| num1: 30 ic| num2: 40اینجاست که دستور Icecream به دردمون میخوره.کتابخانه icecream چیست ؟همونطور که از عنوان مشخصه، icecream یه کتابخانه پایتون هست که کمک میکنه تا دیباگ کردن با دستور پرینت راحتر و کاربردی تر بشه.برای نصب Icecream :pip install icecreamو برای استفاده از اون در کد :from icecream import ic حالا با یه function امتحانش می کنیم:from icecream import ic def plus_five(num): return num + 5 ic(plus_five(4)) ic(plus_five(5))و نتیجه :ic| plus_five(4): 9 ic| plus_five(5): 10با استفاده از ic ، ما نه تنها خروجی رو می بینیم بلکه تابع و پارامتر ورودی اون رو هم خواهیم داشت! گاهی لازم داریم تا بدونیم کدوم بخش از کد اجرا میشه، مثلا برای چک کردن وضعیت شرط هایی که توی کد استفاده کردیم، برای مثال :def hello(user:bool): if user: print("I'm user") else: print("I'm not user") hello(user=True)نتیجه اجرا کد :I'm userبا استفاده از icecream اینکار ساده تر خواهد شد و بدون هیچ کد اضافه ای تنها با دستور ic همین نتیجه رو میشه بدست آورد.from icecream import ic def hello(user:bool): if user: ic() else: ic() hello(user=True)نتیجه اجرا کد :ic| icecream_example.py:4 in hello() at 01:05:20.315نتیجه بدست اومده بما میگه که خط 4 کد اجرا شده که در تابع hello هست و خط 6 اجرا نشده.آیا امکان بدست آوردن اطلاعات بیشتری وجود دارد ؟برای جزئیات بیشتر در ic.configureOutput()مقدار includeContext=True رو ست کنین، در اینصورت علاوه بر اطلاعات قبلی، اطلاعات زیر رو هم خواهیم داشت:ic| icecream_example.py:7 in - plus_five(4):9پس مشخص میشه که توی تابع plus_five در کد icecream_example.py لاین 7 اجرا شده و نتیجه 9 بوده .همه icecream ها رو بعد از تموم شدن debugging حذف کنیدممکنه توی کد دستور Print رو برای هدف دیگه ای جز debug کردن استفاده کرده باشین و تشخیص اینها بعد از نهایی شدن کد سخت خواهد بود. اما با ic براحتی میتونیم بعد از اتمام کار اونها رو سرچ کنین و پاک کنین.حالا کد شما تمیز شده.نتیجه گیری یادگرفتیم چطور از icecream برای دیباگ کردن کد پایتون استفاده کنیم، شما میتونین منو در لینکدین دنبال کنین تا در آینده مطالب مفیدتری در مورد data science در کنارهم بخونیم.منبع :https://towardsdatascience.com/

6 گام ساده برای بهبود سرعت MongoDB

نیما جمشیدی — Wed, 13 Jan 2021 20:41:37 +0330

با اینکه دیتابیس MongoDB یکی از سریع ترین دیتابیس های NOSQL هست اما این ویژگی به تنهایی کافی نبوده و یه کوئری پیچیده میتونه کد شما رو با مشکل روبرو کنه. همه ما روزانه با این سوال مواجه هستیم که وقتی اپلیکیشن به صورت ناپایدار کار میکنه کجا و به دنبال چه چیزی باید باشیم؟ امیدوارم این چند کار ساده بتونه از دردسرهای روزانه کار با دیتابیس مونگو کم کنه.گام اول : لاگ دیتابیس MongoDB رو چک کنینبه صورت پیش فرض، مونگو تمام کوئری های با زمان بالای 100 میلی ثانیه رو لاگ میکنه، محل این لاگ تو تنظیمات Configuration مشخص می شه که به صورت نرمال تو مسیر زیر قرار داره. /var/log/mongodb/mongod.log برای تغییر مسیر لاگ به فایل mongod.conf مراجعه کنین و مسیر نگهداری فایل رو تغییر بدین. برای تنظیمات بیشتر لاگ log level verbosity و تنظیم مواردی که نیاز به پروفایل دارین به profiling parameters مراجعه کنید.احتمال داره لاگ فایل بزرگ شده باشه پس شما میتونید قبل از پروفایل کردن اون رو Clear کنین.با دستور زیر می تونین فایل لاگ رو خالی کنین:db.runCommand({ logRotate : 1 }); با این روش فایل لاگ جدیدی ایجاد میشه و دیتا لاگ قبلی با تاریخ Backup ذخیره میشه.می تونیم فایل بک آپ رو پاک کنیم یا به مسیر دیگه ای منتقل کنیم. با دستور زیر میتونیم پروفایلر مونگو رو تنظیم کنیم که کوئری رو مثلا با زمان اجرا بیش از 10 ثانیه لاگ کنه :db.setProfilingLevel(1, { slowms: 10000 })برای بررسی لاگ و پیدا کردن کوئری های دردسرساز کلمه 'COMMAND' رو جستجو کنین تا کوئری اجرا شده به همراه زمان در واحد میلی ثانیه رو در انتهای کوئری پیدا کنین. برای مثال :2016-02-12T11:05:08.161+0000 I COMMAND [conn563] command project.$cmd command: count { count: "test", query: { published: { $ne: false }, country: "uk" } } planSummary: IXSCAN { country: 1 } keyUpdates:0 writeConflicts:0 numYields:31 reslen:44 locks: { Global: { acquireCount: { r: 64 } }, MMAPV1Journal: { acquireCount: { r: 32 } }, Database: { acquireCount: { r: 32 } }, Collection: { acquireCount: { R: 32 } } } 403msاین میتونه بهتون کمک کنه تا کوئری های کند رو که باعث کاهش performance دیتابیس شدن رو پیدا کنین.گام دوم : کوئری ها رو آنالیز کنینمثل بقیه دیتابیس ها مونگو هم امکان explain facility برای کوئری رو فراهم میکنه تا بفهمیم دیتابیس چطور عمل میکنه. با اضافه کردن دستور زیر به انتهای کوئری میتونین نحوه اجرا اون رو ببینید.explain('executionStats') برای مثال :db.user.find( { country: 'AU', city: 'Melbourne' } ).explain('executionStats');یا به صورت :db.user.explain('executionStats').find( { country: 'AU', city: 'Melbourne' } );این کوئری یک json طولانی رو برمیگردونه اما میتونین با دستورای زیر بخش کلیدی رو استخراج کنین:executionStats.nReturnedتعداد داکیومنت ها برگردانده میشهexecutionStats.totalDocsExaminedتعداد داکیومنت هایی که اسکن شدن تا نتیجه بدست بیاد. اگه تعداد داکیومنت های بررسی شده خیلی بزرگتر از تعداد برگردونده شده باشه، پس کوئری بهینه نیست. در بدترین حالت، مونگو مجبور خواهد بود تمام داکیومنت های داخل collection رو بررسی کنه . همچنین کوئری باید از ایندکس هم استفاده کرده باشه.برای اطلاعات بیشتر به همراه مثال به لینک های Analyze Query Performance و db.collection.explain() در MongoDB manual مراجعه کنید.گام سوم : افزودن ایندکس مناسبدیتابیس های NoSQL هم به ایندکس نیاز دارن مثل دیتابیس های رابطه ای . یک ایندکس از مجموعه یک یا چند فیلد درست میشه تا سرعت اجرا کوئری رو زیاد کنه. برای مثال ، شما میتونین فیلد country در کالکشن user رو ایندکس کنین. موقعی که کوئری بدنبال 'AU' میگرده، مونگو میتونه سراغ ایندکسش بره و نتایج مناسب رو برگردونه بدون نیاز به اسکن کردن کالکشن user.ایندکس با دستور CreateIndex در مونگو ساخته میشه ، برای مثال برای کالکشن user :db.user.createIndex({ country: 1 });همچنین میتونیم ایندکس با ترکیب چندین فیلد درست کنیم :db.user.createIndex({ country: 1, city: 1 });چندین امکان مختلف هنگام ساخت ایندکس وجود داره که با مراجعه به راهنمای Index Introduction MongoDB میتونین مطالعه بیشتری روش داشته باشین .گام چهارم : با احتیاط از Sorting استفاده کنیدمعمولا دوست داریم نتایج کوئری رو به صورت مرتب ببینیم، برای مثال برای مشاهده تمام کاربران براساس کشور اونها :db.user.find().sort({ country: 1 });مرتب سازی موقعی خوب عمل میکنه که ایندکس مورد نیاز برروی اون فیلد وجود داشته باشه .اگر ایندکسی روی اون فیلد نساخته باشین مونگو مجبوره خودش لیست رو مرتب کنه و این موقعی که لیست بزرگی از داکیومنت ها در حال بررسی هست مشکل ساز میشه. دیتابیس حداکثر 32MB مموری برای اینکار اختصاص میده. ( 32MB memory limit on sorting operations ) و اگر فرآیند مرتب سازی بیش از این رم لازم داشته باشه، هیچ نتیجه ای برگردونده نمیشه و معمولا 1000 داکیومنت نسبتا کوچیک هم برای رسیدن به این سقف کافی خواهد بود. حالا فرض کنین ایندکس مورد نیاز رو روی Country ایجاد کرده باشیم.db.user.createIndex({ country: 1 });اما کوئری اجرا شده شامل sort برروی فیلدهای City , Country باشه.db.user.find().sort({ country: 1, city: 1 });درحالی که ایندکس مورد نیاز برای country روی دیتابیس هست، اما مونگو مجبوره فیلد City رو خودش مرتب کنه .این فرآیند کنده و ممکنه به 32MB سقف مموری Sorting برسه. پس لازمه که ایندکس ترکیبی از دو فیلد رو برای این کوئری ایجاد کنیم .db.user.createIndex({ country: 1, city: 1 });حالا ایندکس مناسبی برای کوئری وجود داره و میتونه با سرعت مناسبی اجرا بشه. همچنین میتونه کوئری با ترتیب برعکس رو هم به خوبی اجرا کنه، چون از انتهای ایندکس شروع خواهد کرد .db.user.find().sort({ country: -1, city: -1 });اما اگه بخواهین فقط یک فیلد رو با ترتیب برعکس انتخاب کنین به مشکل میخوره و نیاز به ساخت ایندکس مناسب برای همین کوئری خواهد بود .db.user.find().sort({ country: -1, city: 1 });ایندکس مورد نیاز :db.user.createIndex({ country: -1, city: 1 });گام پنجم : Maximum Execution Times را تنظیم کنیدکوئری های مونگو تا زمانی که لازم باشه اجرا می شن (البته در بعضی IDE ها سقف پیش فرض وجود دارد) و در صورتی که کوئری کند و نامناسب باشه میتوانه سرور رو دچار مشکل کنه. شما می تونین برای کوئری که به مونگو میفرستین این محدودیت رو بزارین تا بعد از زمان تعیین شده timeout بده و بیش از این سرور رو درگیر نکنه. برای مثال می تونین سقف 100 milliseconds رو برای کوئری اعمال کنید .db.user.find({ city: /^A.+/i }).maxTimeMS(100);در این کوئری فیلد City بررسی می شه تا تمام مواردی رو که شامل حرف A هست انتخاب کنه. زمانی که برای حداکثر تایم انتخاب می کنیم باید معقول باشه. متاسفانه مونگو اجازه نمیده این مقدار رو به صورت سراسری تنطیم کنیم و باید برای هر کوئری جداگانه ست بشه.گام ششم : ایندکس ها رو بازسازی کنیداگر احساس کردین با وجود ایندکس مناسب کوئری کند اجرا می شه نیاز هست که rebuilding indexes رو برای هر کالکشن اجرا کنید. برای مثال برای کالکشن User دستور زیر رو اجرا می کنیم.db.user.reIndex();در مواقعی ممکنه فرآیند Fail بشه که در اینصورت نیاز هست database repair رو انجام بدین و همیشه توصیه میشه که با استفاده از mongodump فایل پشتیبانی از دیتابیس خودتون تهیه کنید.منبع :https://www.sitepoint.com/

مقدمه‌ای بر SQL در Python

نیما جمشیدی — Tue, 17 Nov 2020 17:12:46 +0330

راهنمای بکارگیری SQL در محیط پایتونتو این مطلب در مورد بکارگیری زبان SQL در محیط پایتون صحبت می‌کنیم و شرح می‌دیم که چطور SQL Engine تو پایتون شبیه سازی میشه تا امکان کوئری زدن به دیتابیس رو فراهم کنه.به عنوان یک مهندس داده که با پایتون کار میکنه، شما معمولا نیاز دارین که دیتا رو از یک دیتابیس رابطه ای که توی سرور لوکال یا Cloud نگهداری میشه استخراج کنین. راه های زیادی برای انجام اینکار وجود داره، برای مثال شما می‌تونید کوئری رو توی دیتابیس SQL بزنین و نتیجه رو به عنوان فایل csv. ذخیره کنین تا اون رو در پایتون وارد کنین. البته بهترین کار استفاده مستقیم از SQL تو پایتون هست. استفاده همزمان Panda و SQL میتونه براتون امکان کوئری زدن ، پردازش و استفاده از دیتا تو پایتون رو فراهم کنه.پرسش مهم : SQL چیست ؟کلمه SQL تشکیل شده از Structured Query Language، یه زبان برنامه نویسی که برای مدیریت یا کوئری زدن برروی دیتایی که در یک دیتابیس رابطه ای ذخیره شده، استفاده میشه . RDBMS - relational database management system زبان SQL، زبان غالب برای کار با دیتاهای ساخت یافته که در موجودیت های دیتابیس ذخیره شده اند ( مثل جدول پایگاه داده) هست. زبان های دیگه ای هم برای انجام اینکار وجود داره اما SQL بسیار شناخته شده است و به طور گسترده بکار برده میشه.تلفظ درست SQL چیست ؟اوایل دهه 1970 توسعه SQL در IBM انجام شد که در ابتدا SEQUEL گفته میشد ، به معنی Structured English Query Language، ولی بعدها به دلایل تجاری اسم اون به SQL به معنی Structured Query Language تغییر کرد. تلفظ /see-qu-el" - /ˈsiːkwəl" بخاطر اشاره به کلمه English گفته می شه. این تلفظ رایج توسط آموزش دهنده ها است. در نهایت اینکه برخی "S-Q-L" و عده ای "sequel" تلفظ میکنن که هیچ کدون نادرست نیست. دیتابیس رابطه ای چیست ؟یکی از بهترین تعریف‌های دیتابیس رابطه ای رو Amazon Web Services ارائه کرده :"یک دیتابیس رابطه ای مجموعه ای از آیتم های دیتا هست با رابطه های از پیش تعریف شده میانشان. این آیتم ها به عنوان مجموعه جداول شامل ستون ها و سطرها سازمان یافته اند. جداول برای نگهداری اطلاعات در مورد Objectهایی که باید در پایگاه داده بیان شوند استفاده میشود. هر ستون در جدول نوع خاصی از دیتا و هر فیلد Value مربوط به attribute را نگهداری میکند. سطر در جدول بیان کننده ی مجموعه ای از مقادیر بهم وابسته مربوط به یک Object یا entity است. هر سطر در جدول میتواند با یک کلید شناسایی انحصاری به اسم primary key مشخص شده و سطرها در بین جداول مختلف می توانند توسط foreign keyها بهم مربوط شوند. این دیتا به روش های مختلف میتواند در دسترس قرار بگیره بدون اینکه نیاز به سازماندهی مجدد دیتابیس باشد ".دیتابیس ها میتونن ساختار خیلی پیچیده ای داشته باشن ، با چندین جدول، چندین ستون و سطر برای هر جدول. اگه روابط بین جدول‌ها نامشخص باشن کار کردن و کوئری زدن در دیتابیس سخت و حتی غیرممکن میشه. ERD - Entity Relationship Diagram برای ویژوال کردن این روابط و نشون دادن موجودیت (ستون)های هر جدول و نوع اونها بکار میره . ادمین پایگاه داده سازمان شما باید بتونه این دیاگرام دیتابیس رو در اختیارتون قرار بده.استفاده از SQL در Python کتابخانه های زیادی برای استفاده از SQL در پایتون وجود داره. چندین کتابخانه برای این هدف توسعه داده شده مثل SQLite ، MySQL pyodbc ، MYSQLdb .تو این مطلب از کتابخانه Panda به همراه کتابخانه sqlalchemy برای اتصال به دیتابیس SQL تو پایتون استفاده می کنیم. دستورات این کتابخانه مشابه کتابخونه pyodbc هست و با دنبال کردن چند مرحله ساده میشه به دیتابیس SQL وصل شذ.ساخت SQL Engineخب برای شروع، اول باید کتابخونه مورد نیاز رو نصب و import کنیم. در نهایت میخواهیم قابلیت create_engine کتابخونه رو استفاده کنیم. اول نیاز هست که کتابخانه های مورد استفاده رو نصب کنیم، برای اینکار از pip میشه استفاده کرد اما اگر از PyCharm استفاده میکنین امکان نصب در اون وجود خواهد داشت.pip install sqlalchemyیادر ادامه کتابخانه های مورد استفاده رو import می‌کنیم.import pandas as pdfrom sqlalchemy import create_engineimport pandas as pd ایجاد کانکشن به دیتابیسوقتی کتابخانه import شد، به کمک دستور زیر SQL engine رو میسازیم. در واقع کلاس جدید Engine رو ایجاد می کنیم.نیاز به یک درایور مخصوص دیتابیس مورد استفاده هست ، مثلا برای اتصال به دیتابیس SQL Server از pyodbc استفاده میشه، برای دیتابیس های متفاوت درایور های مخصوص مثل psycopg2 cx_oracle وجود داره .اطلاعات بیشتر درمورد ساخت این کانکشن رو میتونین از www.sqlalchemy.org ببینین.engine = sa.create_engine('mssql+pyodbc://user:password@server/database')نیاز به تنظیم server و database با مقدار درست داریم.server : اسم سرور میزبان دیتابیسdatabase : اسم دیتابیس مورد نظر# establishing the connection to the databse using engine as an interfaceconn = engine.connect()نمایش داده ها با استفاده از دستورات SQLخب تا اینجا به دیتابیس وصل شدیم حالا امکان کوئری زدن برروی جداول ، بروزرسانی اونها و ساخت جدول جدید رو داریم. برای استفاده از sqlalchemy نیاز هست دستورات SQL رو در یک container برای دیتابیس ارسال کنید و پس از دریافت نتیجه اون رو روی دیتافریم panda قرار بدین. دو فاکتوری که در هر کوئری اولیه باید وجود داشته باشه select , from هست .دستور SELECT امکان انتخاب مجموعه ای از ستون ها یا همه اونها رو از جدول میده.دستور FROM مشخص میکنه کدوم جدول برای انتخاب ستون ها مورد نظر هست.نمایش نام جداول موجود در دیتابیس# printing names of the tables present in the databaseprint(engine.table_names())استخراج رکورد های موجود در جدول# checking whether the connection was actually established by selecting and displaying contents of table from the databaseresult = engine.execute(“select * from tablename”)for row in result:print (row)result.close()میتونیم جداول رو در دیتافریم Panda ذخیره کنیم و بعدا با دیتا ذخیره شده کار کنیم.خواندن و ذخیره جدول در دیتافریم Panda# reading a SQL query using pandassql_query = pd.read_sql_query(‘SELECT * FROM database_name.dbo.tablename’, engine)# saving SQL table in a pandas data framedf = pd.DataFrame(sql_query, columns = [‘column1’,‘column2’,…..])# printing the dataframe dfهمچنین میتونیم یک اکسترنال فایل مثل CSV, excel رو بخونیم و توی جدول SQL ذخیره کنیم.df = pd.read_csv(‘tablename’)# create a new table and append data frame values to this tabledf.to_sql(‘tablename’, con=engine, if_exists=’append’,index=False,chunksize=1000)و در نهایت بستن کانکشن :conn.close()منابع :towardsdatascience.commedium.comمنبع :towardsdatascience.com

مسیر شغلی SQL Server

نیما جمشیدی — Mon, 02 Nov 2020 15:13:58 +0330

پرسش چه مسیر شغلی برای یک متخصص پایگاه داده SQL Server وجود داره و در چه زمینه هایی میتونه فعالیت کنه ؟پاسخ قصد داریم در مورد انواع مهارت هایی که در زمینه SQL Server میشه کسب کرد صحبت کنیم.براساس آمار keycdn, DB-Engines و StackOverflow ، پایگاه داده SQL Server مقام سوم رو به عنوان یکی از دیتابیس های شناخته شده داره. میلیون ها نسخه نصب شده در سرورهای مستقل SQL و Cloud ها مانند ( Amazon Web Services, Azure, etc ) در سراسر جهان وجود دارد و بیشمار متخصص تکنولوژی در بیزینس های روزانه با SQL Server کار میکنند.لیستی از برخی مشاغل مربوطه :شماره 1 : Database Administrator -DBAمدیر پایگاه داده کسی است که پایگاه داده SQL Server رو مدیریت و نگهداری میکنه. یک DBA میتونه طراحی، پیاده سازی و نگهداری دیتابیس رو انجام بده. در SQL Server شما میتونید تسک‌ها رو با استفاده از PowerShell, SSIS, T-SQL و حتی Python اتوماتیک کنید. DBA باید ایندکس ها،Statistic ها رو نگهداری کنه و استراتژی بازگردانی در مواقع بحرانی داشته باشه، تسلط به روش های Replication در کنار security performance و high availability لازمه کار یک DBA هست.شماره 2 : Database Developer \ Database Engineerیک دیتابیس Developer باید توانایی طراحی و پیاده سازی دیتابس برای اپلیکیشن های مختلف رو داشته باشه همچنین باید دانش عمیقی از T-SQL و گاهی اوقات ETL داشته باشه.مهندس دیتابیس شبیه توسعه دهنده دیتابیس هست و با توجه به نیازهای سازمانش باید توانمندی های لازم رو داشته باشه.شماره 3 : Data Scientistشغل Data Scientist نسبتا جدید بوده به Machine Learning, Data Mining, Databases و Big Data مربوط میشه. یک دانشمند داده برای آنالیز، پیش بینی ، جمع آوری دیتا و درنهایت پیشرفت بیزینس مسئول هست. آنالیز داده میتونه در زمینه های پزشکی، مخابرات و سیاست مفید باشه.یک دانشمند داده در زمینه علوم کامپیوتر، آمار، جمع آوری داده، ریاضیات و علوم اطلاعات تخصص داره.شماره 4: ETL Developerمفهوم ETL شامل سه مفهوم، استخراج (Extract) ، تحلیل(Transformation) و بارگزاری (Load) داده است. در SQL Server، ابزار SQL Server Integration Services معمولا برای همین منظور استفاده میشه. اطلاعات سازمان استخراج و در جداول Data Warehouse بارگزاری میشه. فرآیند استخراج ، تحلیل و بارگزاری دیتا گاها بسیار پیچیده و زمانبر خواهد بود. ابزارهای دیگه ETL هم مانند Pentaho, Oracle Data Integrator, Talend Data Integrator, Alteryx Analytics وجود داره.شماره 5 : Developerاگه شما به زبان های PHP, .Net , Java یا دیگر زیانهای برنامه نویسی کد میزنین، احتمالا نیاز به اتصال به SQL Server برای برخی پروژه‌هاتون خواهید داشت. هرچی شناخت بهتری از دیتابیس داشته باشید، می تونید Performance بهتری از کوئری هاتون بگیرید. احتمالا SQL Server تکنولوژی اصلی شما نخواهد بود اما به عنوان Developer استفاده از SQL Server میتونی برای مواقعی که تسک های ادمین انجام میدیم مهم باشه. یادگیری ایندکس ونکات کارایی T_SQL برای بهبود تجربه کاربری مفید خواهد بود .شماره 6 : BI Developer \ BI Administratorتوسعه دهنده Business intelligence یه شغل محبوب مربوط به مفاهیم Data Warehousing , Reporting و data transformations هست. اگر شما از SQL Server استفاده میکنید، میتونید با ابزارهای SSIS, SSAS, SSRS, Power BI و SharePoint کار کنید و همچنین نیاز خواهد بود با دیگر زبانهای کوئری نویسی مثل MDX و DAX برای cube آشنا باشید.شماره 7 : Writer/Speaker/Microsoft Certified Trainer/Trainerشرکت هایی مثل MSSQLTips, SQLServerCentral, SimpleTalk وجود دارن که شما میتونید مقاله‌هاتون رو در این وب سایت‎‌ها منتشر کنین و درآمد کسب کنید. شما میتونید تو خونه کارکنین و از زندگی لذت ببرین، زمان خودتون رو مدیریت کنین بدون فشارهای کار کارمندی.مربی های دوره دیده ماکروسافت میتونن در مورد SQL Server تدریس کنن. تنوع زیادی از certification وجود داره که میتونین در اون زمینه ها آموزش بدین البته اگر مهارت لازم رو کسب کرده باشین.شماره 8:Azure/AWS Expertدو سرویس cloud شناخته شده Amazon Web Services و Azure وجود دارند، شما میتوانید SQL Server را در هر یک از آنها داشته باشید. برای اینکار نیاز به داشتن مهارت لازم برای کار با آنها هست.شماره 9:Database Migration Engineerمهندس انتقال دیتابیس وظیفه کانورت دیتا از انواع منابع مختلف مانند Excel, XML, HTML, CSV و همچنین انتقال دیتا میان انواع دیتابیس های SQL Server, Oracle, DB2, MySQL رو داره.شماره 10:Data Analystتحلیل گر داده باید توانایی آنالیز دیتا، تهیه گزارش ها و شناخت KPI ها در محیط SQL رو داشته باشه. او میتونه از ابزارهای Power BI, Reporting Services, Excel استفاده کنه و نیاز هست دانش لازم در مورد تکنولوژی های BI مانند SSAS در multidimensional و یا مدل های Tabular داشته باشد. گاهی اطلاعات تکمیلی درمورد SSAS Data Mining و R مورد نیاز خواهد بود.شماره 11:Reporting Engineerیک Reporting Engineer باید توان تولید، اتومات کردن گزارش های مختلف با استفاده از Power BI, Reporting Services, Excel یا دیگر تکنولوژی ها رو داشته باشد. او باید توانمندی بالایی در مهارت های بهبود کارایی کوئری برای کار با حجم بزرگ دیتا رو داشته باشه.شماره 12:BI Solution Architectیک BI Solution Architect باید در انواع تکنولوژی های BI متخصص باشه و برای مشتریان بهترین راهکار رو بر اساس نیازهاشون با توجه به سخت افزار ، نرم افزار و معماری موجود فراهم کنه. شماره 13:NoSQL DBAاطلاعات زیادی در IoT, Social Network, Telecoms وجود داره که مدیریت کردن این نوع دیتاها با دیتابیس های رابطه ای مشکل خواهد بود. یک DBA باید توانایی کار با NoSQL ها رو هم داشته باشه.این نوع اطلاعات در دیتابیس های سنتی ذخیره نمیشود و نیاز به دیتابیس های NoSQL هست. این شغل میتونه به دیتابیس ObjectDB, ODABA, Perst, Bigtable, Mnesia, Apache River و MongoDB مروبط باشه.شماره 14:Big Data Architect / Big Data Expertاین شغل نیاز به دانستن تکنولوژی های مختلف Big Data و تصمیم گیری در مورد بهترین راه حل برای کمپانی است. در این شغل، فرد متخصص وظیفه داره سخت افزار و نرم افزاری مورد نیاز راهکارهای Big Data را توصیه کنه تا در نهایت معماری سیستم مناسبی برای Big Data طراحی بشه.منبع :www.mssqltips.com

نصب و راه اندازی سرور پایگاه داده SQL Server

نیما جمشیدی — Sat, 31 Oct 2020 13:21:43 +0330

پرسش:شما یک SQL Server Database Administrator هستید و سازمان شما قصد داره پروژه جدیدی رو شروع کنه، از شما به عنوان DBA درخواست میشه تا دیتابیس سرور جدید رو راه اندازی کنید در عین حال بهتون اعلام میشه که سرور جدید به همراه SQL Server نصب شده وجود داره. وقتی شما این موضوع رو می شنوید، قطعا باید مخالفت کنید و انتظار داشته باشید SQL Server توسط شما که متخصص اینکار هستید نصب بشه. در این مطلب چند نکته درمورد اینکه چرا باید راه اندازی سرور SQL Server توسط یک متخصص انجام بشه بیان می‌کنیم.راه حل :در طی سالها، SQL Server از یک پایگاه داده رابطه ای ساده به یک پلتفرم جامع دیتا تبدیل شده. 10 ، 12 سال پیش وجود سازمان هایی که بدون داشتن DBA از پایگاه داده SQL Server استفاده می کردند کاملا عادی بود. این روزها من از برنامه نویس ها و مدیران سیستم میشنوم که میگن، ما اگر بخواهیم از سیستم با کارایی بالا استفاده کنیم میریم سراغ اوراکل و SQL Server رو برای سیستم های سادمون استفاده میکنیم. بله این حرف در زمان SQL Server 7 , اوراکل 8 درست بود چون اوراکل مزیت هایی داشت که البته بکارگیریش هم مشکلات خاص خودش رو داشت. اما امروزه هر دو سیستم قابلیت های یکسانی ارائه میدن با هزینه های متفاوت. تو دنیای آزاد با قوانین کپی رایت اگر شما بخواهید قابلیت های ارائه شده در SQL Server رو در اوراکل استفاده کنید باید هزینه بیشتری پرداخت کنید.ما به عنوان DBA میدونیم که SQL Server چقدر تغییر کرده ولی همچنان تو دنیای IT تصور میشه که SQL Server تو سطح پایین تری از اوراکل قرار داره، به ویژه از دید کسانی که به لینوکس نزدیکتر هستند و همین این باعث شد که SQL Server نسخه لینوکسی خودش رو هم معرفی کنه.نصب SQL Serverاگر شما تجربه نصب SQL Server 2000 رو داشته باشین، میدونین که فرآیند نصب تو نسخه های جدید رفته رفته پیچیده تر شده. اما به همراه این پیچیدگی ماکروسافت سادگی فرآیند نصب رو برای شما فراهم کرده، منظورم اینکه هنوز هم میشه با چندتا کلیک Next پایگاه داده رو نصب و راه اندازی کنید چیزی که در اوراکل تقریبا غیر ممکنه. اما یادمون باشه نصب پایگاه داده SQL Server چیزی بیش از زدن چند کلیک و بالا آوردن Engine SQL Server هست.تنظیم Storage و فضای ذخیره سازی دیتایکی از بخش های مهم که تاثیر مستقیم تو کارایی و عملکرد پایگاه داده داره Storage هست. جدا از مساله فضای مورد نیاز برای نگهداری فایل های دیتابیس، فرد که مسئول نصب راه اندازی دیتابیس هست باید در مورد تعداد دیسک ها و نحوه صحیح کانفیگ کردن اونها تصمیم بگیره ، برای درک کامل این موضوع مطلب Hard Drive Configurations for SQL Server رو بخونین.بعد از انتخاب درست دیسک ها، DBA باید دیسک ها رو با اندازه درست فرمت کنه - allocation unit size، این بستگی داره که اون دیسک قرار هست برای Data file ها استفاده بشه یا Log file ها . بحث کامل تر در این مورد رو از Format drives with correct allocation and offset for maximum SQL Server performance بخونین.بعد از تمام مراحل بالا حالا وظیفه دارین که تست های Response time رو روی دیسک ها انجام بدین. برای اینکار ابزاری های مثل SQLIO وجود داره که میتونین با این لینک باهاش آشنا بشین : Benchmarking SQL Server IO with SQLIO.آخرین مرحله برای تنظیم Storage، فعال کردن تنظیم Instant File Initialization هست، این قابلیت از نسخه های قبل هم در SQL وجود داشت اما در مراحل نصب اخیرا اضافه شده، اگه این قابلیت رو فعال نکنین وقتی که دیتابیس نیاز پیدا میکنه سایز فایل هاش رو افزایش بده زمان بیشتری صرف میکنه تا فضاهای خالی دیسک رو شناسایی کنه. درواقع SQL Server قبل از گرفتن فضا دیسک مورد نیازش، اول اون فضا رو با 0 پر میکنه. برای مثال وقتی نیاز به دیسک برای creating/restoring دیتابیس یا growing/allocating فایل های دیتا و لاگ داره. تنظیم این قابلیت از انجام اینکار جلوگیری میکنه و معمولا باعث بهبود زمان میشه. برای درک بهتر این موضوع مطالب Effect of Instant File Initialization within SQL Server و Configuring Windows Instant File Initialization for SQL Server 2005 رو بخونین.انتخاب Default Instance یا Named Instanceهمیشه این فکر رو کردیم که این بخش اصلا مهم نیست، اما اشتباهه. به ویژه برای سیستم ادمین هایی که دانش عمیقی از SQL Server ندارن. گاهی لازمه اینستنسی که نصب میکنید اسم مخصوص خودش رو داشته باشه، اگه گزینه پیش فرض رو انتخاب کنید در واقع اینستس SQL رو همنام با نام سرور قراردادین و این موقع راه اندازی اپلیکشن ها شمارو دچار مشکل میکنه. در اون صورت راهی وجود نداره جز نصب مجددا SQL Server. پس موقع نصب فقط کلید Next رو فشار ندید و اسم مناسبی برای اینستنسی که نصب میکنین انتخاب کنین.انتخاب Feature ها موقع نصب پایگاه داده کدوم یک از ما ندیدیم سازمانی رو که فقط از Database engine استفاده میکنه اما قابلیت های مثل Reporting Service ، Integration Service و حتی Analysis Service رو نصب کرده ؟ حتی وقتی یه متخصص SQL Server هم نصب رو انجام داده این اتفاق میوفته!خب مشکل نصب سرویس هایی که هرگز ازشون استفاده نمیکنیم چیه ؟ این سرویس ها معمولا به صورت اتوماتیک اجرا میشن و منابع سیستم رو اشغال میکنن. پس توجه کنین وقتی دارین یه Production Server راه اندازی میکنین فقط باید سرویس های مورد نیاز رو نصب کنین که در اکثر مواقع فقط Database Engine مورد نیاز خواهد بود.انتخاب Service Accountوقتی SQL Server رو با تنظیمات پیش فرض نصب میکنین، باعث میشه نصب با Service account انجام بشه، تو خیلی از سازمان های کوچیک و بزرگ از اکتیودارکتوری یا ابزاری شبیه به اون برای مدیریت اکانت های کاربری استفاده میشه، اگه SQL رو با اکانت لوکال نصب کنین در ادامه راه اندازی امکان شناسایی دامین اکانت برای اون وجود نخواهد داشت چون SQL نمیتونه SPN دامین رو شناسایی کنه. از طرف دیگه، افرادی هم وجود دارن که ادمین دامین رو به عنوان سرویس اکانت استفاده میکنن تا خیالشون بابت داشتن دسترسی های لازم راحت باشه، اما برای مثال اگه xp_cmdshell فعال باشه یک کاربر براحتی میتونه سرویس رو به طور کامل shut down کنه.انتخاب Authentication Modeپایگاه داده SQL دو روش authentication رو فراهم میکنه، Windows authentication که به شما امکان لاگین به SQL رو بصورت مستقیم با اکانت ویندوزتون میده و Mixed mode که امکان استفاده از SQL Server لاگین رو هم ایجاد میکنه.به طور پیش فرض SQL Server از مدل اول استفاده میکنه، اما استفاده از این حالت قالبا با استفاده از کابر دامینی ویندوز همراهه که همونطور که بالا گفتیم نیمتونه امن باشه. اگه انتخاب کنید که از Mixed mode استفاده کنید، باید از نکات امنیت اون آگاه باشین، برای درک بهتر مطلب Best Practices to Secure the SQL Server sa Account and Different ways to secure the SQL Server SA Login رو بخونین.مشخص کردن کاربر ادمین پایگاه دادهنصب SQL Server از شما میخواد که کاربر یا گروه کاربری رو به عنوان ادمین اینستنس معرفی کنید، یعنی قرار تعیین کنید چه کسی دسترسی نامحدود به پایگاه داده داره، کسانی که اطلاعات درستی از امنیت SQL Server ندارن یک گروه ادمین تعریف میکنن و حتی بدتر یک گروه دامینی ادمین تعریف میکنن، برای اینکه بدونین چرا این اشتباهه مطلب Security Issues with the SQL Server BUILTIN Administrators Group رو بخونین.انتخاب Data Directory ها این مرحله جایی که اگه رهاش کنین و درست تنظیمش نکنین تمام کارهای قبلیتون بی فایده میشه، هدف از انتخاب دیسک جدا، فرمت دیسک و تمام کارهایی که تاحالا کردیم، تنظیم محل قرارگیری فایل های mdf , ldf بود ، پس این بخش رو مطابق تقسیم دیسکی که انجام دادین پیش ببرین و محل قرارگیری لاگ فایل ها و دیتا فایل ها تفکیک کنین.تنظیم TempDBانتخاب محل درست قرارگیری TempDB برای SQL Server خیلی حیاتی هست.تا نسخه SQL 2016 ، شما فقط میتونستین یه دیتا فایل برای TempDB ایجاد کنید، اما الان میتونین هر تعداد فایل مورد نیاز رو اضافه کنید. اینکه گفته میشه برای هر cpu core باید یه TempDB ایجاد بشه تصور غلطیه ، برای درک بهتر این موضوع SQL Server tempdb one or multiple data files و Tempdb Configuration Best Practices in SQL Server رو بخونین. موضوع کمی پیچیده تر از انتخاب یک فایل برای هر هسته CPU هست .انتخاب Collation درست گفتیم که انتخاب نادرست درایوها تمام زحماتمون رو به باد میده، انتخاب درست Collation هم به همین میزان مهمه، چون داریم به Engine SQL قوانین sort کردن و همینطور Case و Accent Sensitivity رو میگیم. این خیلی به زبان مورد استفاده در پایگاه داده و رفتار اپلیکشن بستگی داره ، آیا کوچیک بزرگ بودن کارکترها مهمه ، املاء اونها مهمه ؟ و جدی تر از همه ترتیب مرتب کردن حروف اون زبان هست. مثل مشکل ی و ک که ما توی زبان فارسی داریم. جزئیات بیشتر در این مورد Changing SQL Server Collation After Installation and How to change server level collation for a SQL Server Instance منبع : www.mssqltips.com

بهینه سازی اتوماتیک در SQL Server

نیما جمشیدی — Wed, 21 Oct 2020 23:54:19 +0330

در این مقاله میخوانیم :چرا بهینه سازی اتوماتیک؟اصلاح کوئری پلن به صورت اتوماتیکاین مقاله به نسخه SQL Server 2017 و بالاتر مربوط می‌شه.بهینه سازی خودکار قابلیتی در دیتابیس هست که مشکلات Performance کوئری ها رو مورد بررسی قرار داده و راه حل هایی پیشنهاد میده و همچنین میتونه به صورت خودکار این مشکلات را برطرف کنه.قابلیت Automatic tuning در SQL Server 2017 جهت شناسایی مشکلات Performance در دیتابیس و فراهم کردن راهکارهای مناسب و همچنین فراهم کردن امکان رفع خودکار این مشکلات توسط Database Engine ارائه شد. Automatic Tuning امکان برطرف کردن مشکل performance کوئری ها رو که به دلیل انتخاب Query execution plan نامناسب بوده رو ایجاد می‌کنه. در Azure SQL Database قابلیت Automatic index management هم وجود داره که امکان ساخت ایندکس های ضروری و حذف ایندکس های ناکارآمد رو فراهم میکنه.برای آشنایی با مفهوم Execution plan در SQL Server به مقاله نوشته شده مراجعه کنید.در SQL Server دیتابیس engine وظیفه مانیتور کردن کوئری های درحال اجرا رو داره تا به صورت اتوماتیک کارایی را بهبود بده. دیتابیس Engine یک مکانیسم داخلی هوشمند داره که میتونه به صورت اتوماتیک با آداپته کردن دیتابیس بر اساس workload ، اون رو رو بهینه کنه. دو قابلیت بهینه سازی اتوماتیک در حال حاضر وجود داره :Automatic plan correction :وظیفه شناسایی مشکلات پلن اجرا کوئری رو به عهده داره، مانند Parameter sensitivity و parameter sniffing. برطرف کردن مشکلات Performance مربوط به کوئری پلن با فورس کردن آخرین کوئری پلن مناسب اجرا شده قبل از بروز اشکال. این قابلین از نسخه 2017 در SQL Server افزوده شد.Automatic index management :شناسایی ایندکس هایی که نیاز هست به دیتابیس شما افزوده یا حذف شوند، این قابلیت فقط در Azure SQL Database وجود دارد.چرا بهینه سازی اتوماتیک ؟سه وظیفه کلاسیک دیتابیس ادمین ها شامل ، مانیتورینگ workload دیتابیس ها، شناسایی کوئری های T_SQL مشکل ساز و شناسایی ایندکس هایی که باید اضافه یا حذف شوند تا کارایی دیتابیس بهبود یابد. SQL Server Engine جزئیات مورد نیاز کوئری های اجرا شده را جهت مانیتورینگ برای شما فراهم میکند، گرچه مانیتور کردن دیتابیس وظیفه سخت و خسته کننده ایه، به ویژه وقتی که با چندین دیتابیس کار میکنید. لذا بجای مدیریت و مانیتوریگ به صورت دستی بهتره مانیتورینگ و بهینه سازی رو به Database Engine بسپاریم تا از قابلیت Automatic tuning استفاده کنه.بهینه سازی اتوماتیک چطور عمل میکند؟بهینه سازی اتوماتیک یه چرخه از مانیتورینگ و آنالیز پردازش ها انجام میده تا یادگیری پیوسته درباره مشخصات Workload دیتابیس بدست آورده و اشکالات احتمالی را شناسایی و بهبود بده.چرخه بهینه سازی اتوماتیکاین فرآیند دیتابیس رو قادر میکنه به صورت اتوماتیک خود را با workload ورودی آداپته کنه و با پیدا کردن ایندکس ها و کوئری پلن های مناسب باعث بهبود Performance بشه.مراحل انجام :بر اساس این یافته ها، بهینه سازی اتوماتیک tuning مناسب برای workload دیتابیس رو اعمال میکنه و در ادامه automatic tuning کارایی دیتابیس را پس از اعمال هر تغییری مانیتور میکنه تا از موثر بودن تغییر اعمال شده اطمینان حاصل کنه لذا هر تغییری که موثر نباشه برگردانده میشه. این قابلیت ویژگی کلیدی هست که کمک میکنه تا ناخواسته باعث افت کارایی دیتابیس نشه.اصلاح اتوماتیک کوئری پلنویژگی Automatic plan correction بخشی از بهینه سازی اتوماتیک هست که مشکل در کوئری پلن رو شناسایی میکنه و به صورت اتوماتیک با فورس کردن آخرین پلن مناسب اجرا شده اون رو برطرف میکنه.برای اطلاعات بیشتر در مورد کوئری پلن میتونید مقاله موجود رو مطالعه کنید.چرا کوئری پلن نادرست انتخاب میشود ؟پایگاه داده SQL Server میتونه انتخاب های مختلفی برای پلن اجرا کوئری داشته باشه. این کوئری پلن بسته به Statistic ها ، ایندکس ها و فاکتورهای دیگه میتونه انتخاب بشه. پلن بهینه انتخابی که قراره کوئری T_SQL رو اجرا کنه ممکنه طی زمان و با تغییر هر کدوم از فاکتورها تغییر کنه. در برخی موارد ، پلن جدید ممکنه بهتر از پلن قبلی نباشه و باعث مشکل Performance بشه مثل Parameter sensitivity و parameter sniffing .انتخاب پلن نامناسبهمانطور که تو عکس میبینید انتخاب نادرست پلن اتفاق افتاده، شما باید کوئری پلن قبلی مناسب رو پیدا کنین و اون رو جای پلن فعلی استفاده کنین. این کار میتونه با استفاده از sp_query_store_force_plan انجام بشه. Database engine از نسخه 2017 به بعد اطلاعات مربوط به این نوع خرابکاری های پلن رو نگهداری میکنه و پلن درست رو بهتون پیشنهاد میده. همچنین ، engine دیتابیس شما رو قادر میسازه کل این فرآیند رو اتوماتیک کنید و اجازه بدین که خود Engine این تصمیم تغییر پلن رو بگیره.انتخاب اتوماتیک کوئری پلندر صورت به وجود اومدن کوئری پلن نامناسب، Engine دیتابیس میتونه به صورت اتوماتیک آخرین پلن مناسب اجرا شده رو برگردونه.فورس کردن آخرین پلن مناسبدر مرحله اول تغییر پلن نامناسب رو تشخیص میده و پلن قبلی مناسبی رو که باید به جای پلن جدید نامناسب جایگزین کنه رو فورس میکنه ، در مرحله دوم مجددا مانیتور میکنه تا ببینه پلن برگردونده شده بهینه تر عمل میکنه یا نه ؟ اگر پلن فورس شده از پلن جدید نامناسب هم کارایی کمتری داشته باشه (به دلیل تغییرات دیتابیس ممکنه پلن خوب قبلی هم دیگه کارایی نداشته باشه) این پلن هم از حالت فورس خارج میشه و Database Engine پلن جدیدی رو کامپایل میکنه. اما در صورتی که پلن فورس شده بهتر از پلن نامناسب ایجاد شده باشه، فورس بودن پلن تثبیت میشه، و تا وقتی که یک recompile رخ نده باقی می مونه (مثلا تغییر اسکیما یا Statistic ها )نکته : اگر SQL Server قبل از تثبیت شدن پلن فورس شده ریست بشه، فورس پلن برداشته میشه در غیر اینصورت تثبیت شده باقی میمونه.فعال کردن انتخاب پلن اتوماتیکبه ازاء هر دیتابیس میتونین این قابلیت فعال کنین تا دیتابیس خودش بتونه در صورت نیاز آخرین پلن مناسب اجرا شده رو فورس کنه.ALTER DATABASE SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );در صورت عدم استفاده از انتخاب اتوماتیک ، ادمین دیتابیس باید به صورت دوره ای سیستم رو مانیتور کنه و کوئری پلن های نامناسب رو تشخیص بده و میتونه با استفاده از sp_query_store_force_plan پلن های قبلی مناسب اون کوئری رو پیدا و به صورت دستی فورس کنه . برای غیر فعال کردن پلن فورس شده هم میتونه از sp_query_store_unforce_plan استفاده کنه.منبع :docs.microsoft.com

متغیرهای لوکال و مشکلات Parameter Sniffing

نیما جمشیدی — Mon, 12 Oct 2020 12:41:30 +0330

زمانی که یک Stored Procedure فراخوانی می‌شه، SQL Server به دنبال Execution Plan مربوط به اون در حافظه Cache می گرده، اگه اونو پیدا کرد بلافاصله اجراش می کنه اما اگه Planی براش پیدا نکرد، باید تمام مراحل ساخت Execution Plan طی بشه.دیتابیس SQL Server با استفاده از پارامترهایی که اولین بار به یک SP فرستاده میشه، یک Plan ایجاد کرده و اونو Cache میکنه، یعنی یکبار پلن رو می‌سازه تا چندین بار ازش استفاده کنه و در زمان صرفه جویی کنه.به این پدیده Parameter Sniffing گفته میشه.در اکثر موافق Cache شدن Plan خوبه اما بعضی وقتا باعث خرابکاری میشه.نخست SP زیر رو در نظر بگیرید:حالا SP رو با دو مقدار مختلف فراخوانی کرده و Execution Plan ش رو بررسی می کنیم.نمونه اول با Customerid = 1060نمونه اول با Customerid = 90همون طور که می بینیم برای هر دو مقدار، کوئری پلن یکسان مورد استفاده قرار گرفته اما Estimated Row کوئری دوم با مقادیر نادرست نمایش داده میشه.شاید زمان اجرای هر دو کوئری مناسب باشه اما مشکل زمانی حادتر میشه که کوئری پلن اولی که Cache شده، برای پارمترهای بعدی، یک انتخاب بد باشه و باعث کندی محسوس در دریافت خروجی بشه.اگر Properties عملگر Select کوئری پلن دوم رو ببینیم متوجه میشیم که مقدار Compiled Value برابر با مقدار اولین Parameter ی هست که پلن باهاش Cache شده و این مقدار با Runtime Value متفاوته.برای اینکه جلوی این اتفاق گرفته بشه می تونیم SPرو با Local Variable بازنویسی کنیم، یعنی به شکل:حالا مجددا کوئری ها رو اجرا می کنیم:همون طور که مشاهده می کنید Estimated Row برای هر دو کوئری پلن به درستی نمایش داده شده اما در هر صورت دیگه Plan ی وجود نداره که بهش مراجعه و در زمان صرفه جویی شه. استفاده از Local Variable بر مشکل Parameter Sniffing غلبه میکنه اما هیچ تضمینی وجود نداره که بهترین پلن انتخاب شه.منبع : blog.sqlauthority.com

برگزاری آنلاین کنفرانس SQLBits 2020

نیما جمشیدی — Wed, 30 Sep 2020 14:30:49 +0330

این روزها گردهمایی سالانه SQLBits در حال برگزاری هست. امسال تیم برگزار کننده تصمیم گرفت کنفرانس رو به صورت آنلاین برگزار کنه، این کنفرانس از دیروز یعنی 29 سپتامبر شروع شده و به مدت 5 روز ادامه خواهد داشت.کنفرانس سالانه SQLBitsمعرفی SQLBits :گروه SQL Bits در سال 2007 توسط افراد علاقه‌مند به مجموعه محصولات SQL Server با هدف ارائه آموزشهای مورد نیاز برای عموم آغاز به کار کرد. از آن زمان تاکنون به بزرگترین کنفرانس Data Platform در جهان تبدیل شده . برخلاف سایر کنفرانس ها، بیش از 95٪ از مطالبی که ارائه میکنند غیر بازاریابی است و دید واقعی در مورد برنامه‌ریزی موفقیت آمیز ، ساخت و بهره برداری از یک سیستم عامل مدرن را فراهم می کند.تیم SQLBits همه متخصصان داده هستند که در صنعت کار می کنند و به همین دلیل میشه اطمینان داشت که محتوای ارائه شده همون چیزی است که برای کار لازم داریم. این تجربه شامل سیستم داده های معاملاتی از طریق داده های بزرگ ، تجزیه و تحلیل هوش مصنوعی و یادگیری ماشین است.باتوجه به اینکه فعلا ویدئو ارائه‌های کنفرانس جاری در دسترسی نخواهد بود، لذا جدول زمانبدی ارائه شده رو به همراه لینک توضیحات اینجا مینویسم.سه شنبه 29 سپتامبر (آموزش)A Day Full of Azure Data Factory Paul AndrewBuilding a Modern Data Warehouse in Azure Simon D'MoriasSQL Server Cloud Fundamentals Allan HirtA to Z Azure Cosmos DB Hasan SavranGetting started with Azure Machine Learning Nico JacobsMastering Index Tuning Brent OzarSQL Server Performance Tuning Real-World Scenarios Pinal DaveFurther your Power BI development skills Ásgeir Gunnarssonچهارشنبه 30 سپتامبر (آموزش)From Zero to Azure Data Factory Andy LeonardOpen Hack: From ingestion to consumption Stijn WynantsMigrating a Legacy SQL Database to Azure Ike EllisSucceed with DevOps as a DBA Grant FritcheyMachine Learning In Production: Azure, Docker, Kubernetes Terry McCannEnterprise analytics with SQL Server and in Azure Tomaz KastrunAdvanced T-SQL Querying and Query Tuning Itzik Ben-GanPerformance Tuning with Query Store in SQL Server and Azure Erin StellatoStatistics4Performance: Internals, Analysis, Problem Solving Kimberly L. TrippImprove performance with DAX and Power BI Marco RussoSQL Server data security from the bottom up Stuart Mooreپنجشنبه 1 اکتبر : (برگزاری کنفرانس‌ها)https://sqlbits.com/information/event20/schedule?20201001جمعه 2 اکتبر : (برگزاری کنفرانس‌ها)https://sqlbits.com/information/event20/schedule?20201002شنبه 3 اکتبر : (برگزاری کنفرانس‌ها)https://sqlbits.com/information/event20/schedule?20201003

استفاده از SQL Server PolyBase برای اتصال به مونگو

نیما جمشیدی — Tue, 29 Sep 2020 20:39:23 +0330

امروزه استفاده از دیتابیس مونگو MongoDB در سازمان‌ها مرسوم شده و معمولا پیش میاد که اپلیکیشن‌ها و سرویس‌ها به طور همزمان، به دیتابیس مونگو و دیتابیس SQL Server متصل بشن. خب تا اینجای کار مشکلی وجود نداره و هر کدوم از دیتابیس‌ها به صورت مستقل کارخودشون رو انجام میدن، اما زمانی که نیاز به تحلیل داده و تهیه گزارش میشه، کار سخت DBA ها شروع میشه. چند راه برای استخراج دیتا به طور مشترک بین دیتابیس‌ها با انواع مختلف وجود داره :استخراج دیتا از دیتابیس مونگو و درج اون در دیتابیس SQL Server و سپس تهیه گزارشاینکار زمان‌بر هست و باید به صورت آفلاین انجام بشه، لذا نمی‌تونه گزارش آنلاینی رو برای شما فراهم کنه.استفاده از قابلیت PolyBase در SQL Server 2019 PolyBase در SQl Serverقابلیت PolyBase چیست ؟با استفاده از PolyBase امکان اجرا کوئری T-SQL بر روی منبع دیتا خارجی ( external data source) در SQL Server فراهم میشه. از نسخه 2016 SQL Server امکان اتصال به Hadoop و Azure Blob Storage فراهم شد و با انتشار SQL Server 2019 قابلیت اتصال به external data از نوع Oracle ، Teradata و همچنین MongoDB افزوده شد.نکته مهم و کاربردی این قابلیت: همان کوئری که به external data دسترسی داره می‌تونه روی دیتابیس اصلی SQL Server هم اجرا بشه. برای مثال ما می‌تونیم یک کوئری Join بین دیتا داخلی و دیتا خارجی که مثلا در دیتابیس مونگو هست اجرا کنیم و به صورت آنلاین گزارش دلخواه رو تولید کنیم.چطور با PolyBase به دیتابیس مونگو متصل بشیم ؟برای اینکار یک اینستنس SQL Server 2019 لازم داریم که ویژگی PolyBase در اون نصب شده باشه .SQL Server PolyBaseدر صورتی که قبلا این قابلیت رو نصب نکردین، به سراغ فایل نصب SQL Server رفته و این feature رو به اینستنس نصب شده، اضافه می‌کنیم.PolyBase نصببا اجرای SQL Server Management Studio تصویر زیر رو خواهیم داشت .خب حالا نیاز داریم یک دیتابیس مونگو داشته باشیم، من قبلا MongoDB رو به همراه IDE مناسب Robo 3T نصب کردم.فایل های نصب مونگو در ویندوزبا اجرای Robo 3T به دیتابیس مونگو وصل شده و Collection موجود در اون رو باز می‌کنیم.در این مثال، ما یک دیتابیس با نام ForSQLServer داریم که شامل یک Collection بانام Table1 هست. مفاهیم در مونگو کمی با SQL Server متفاوت هستند، مفهوم Collection میتونه معادل Table در SQL Server در نظر گرفته بشه.در داخل Table1 فعلا هیج دیتایی وجود نداره. درج دیتا به صورت Document در مونگو انجام می‌شه. به این معنی که هر رکوردی که در Collection درج کنیم، درواقع یک داکیومنت در اون ایجاد کرده‌ایم .درج داکیومنت در مونگوخب در اینجا ما یک رکورد یا همون داکیومنت در Collection مونگو درج می‌کنیم.مشاهده داکیومنت در مونگوو با اسکریپ اجرا شده، می‌تونیم نتیجه درج داکیومنت رو در مونگو ببینیم.اتصال PolyBase به مونگو :در این مرحله به سراغ دیتابیس SQL Server می‌ریم تا تنظیمات لازم رو در اون انجام بدیم.این تنظیمات شامل 4 مرحله زیر هست :ساخت Master key برای دیتابیسCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@123';ساخت SCOPED CREDENTIAL برای دیتابیس، دقت کنید که این همان کاربری است که به دیتابیس مونگو وصل میشه، پس لزوما باید دسترسی لازم سمت دیتابیس مونگو رو داشته باشه. اسم این ceredential رو MongoDB گذاشتیم تا بعدا ازش استفاده کنیم.CREATE DATABASE SCOPED CREDENTIAL MongoDBWITH IDENTITY = 'username', Secret = 'password';یک EXTERNAL DATA SOURCE با نام MongoDBSource می‌سازیم. دقت کنید که Location دقیقا باید آدرس دسترسی به دیتابیس مونگو باشه، در اینجا به صورت لوکال و با پورت پیش فرض 27017 است.CREATE EXTERNAL DATA SOURCE MongoDBSource WITH (LOCATION = 'mongodb://localhost:27017',CREDENTIAL = MongoDB, --specify credential created in step 2 CONNECTION_OPTIONS = 'ssl=false;' )در نهایت ایجاد EXTERNAL TABLE : دقیقا مطابق ساختار داکیومنت درج شده در مونگو، جدول مشابهی در SQL Server می‌سازیم. در انتهای اسکریپت، Location نشون‌دهنده دیتابیس و کالکشن سمت مونگو هست.CREATE EXTERNAL TABLE Table1(Name Nvarchar(50) NULL, lastname Nvarchar(50) NULL,location Nvarchar(50) NULL,website Nvarchar(100) NULL ) WITH (LOCATION='ForSQLServer.Table1',DATA_SOURCE= MongoDBSource);اگر تمام مراحل چهارگانه بالا رو به درستی انجام داده باشیم، حالا یه جدول از نوع External table در دیتابیس SQL Server خواهیم داشت.external table در sqlهمانند جداول داخلی دیتابیس، می‌تونیم روی اون کوئری بزنیم و دیتا مربوط به مونگو رو به صورت آنلاین استخراج کنیم.مپ نوع دیتا از مونگو به SQL Server :MongoDB Type Mappingچند نمونه کوئری SQL در مونگو :MongoDB for SQL developersخطاهای احتمالی :در صورت مشاهده خطا در مرحله EXTERNAL DATA SOURCE با عنوان :PolyBase Error Msg 46530 – External Data Sources Are Not Supported With Type GENERICدو دلیل ممکنه وجود داشته باشه.1- سرویس polybase بعد از نصب اجرا نشده و فعال نیست. با این اسکریپت میتونین چک کنین :SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled2- در صورتی که سرویس فعال بود نیاز هست تا این قابلیت رو روی اینستنس فعال کنید :exec sp_configure @configname = 'polybase enabled', @configvalue = 1RECONFIGUREاگر با خطاTCP Provider: No connection could be made because the target machine actively refused it.مواجه شدید معنیش اینکه سروریس PolyBase استاپ شده به SQL Server Configuration Manager مراجعه کنید و سرویس رو استارت کنید.

مغایرت Collation در دیتابیس SQL Server

نیما جمشیدی — Tue, 29 Sep 2020 13:29:03 +0330

مغایرت Collation در سطح دیتابیس یا سرور، SQL Server به ما این امکان رو میده تا Collation های متنوعی برای دیتابیس هامون انتخاب کنیم. ما میتونیم اون ها رو در سطح عمومی سرور یا به ازای هر دیتابیس تنظیم کنیم. اما وقتی نیاز باشه بین دو دیتابیس Join بزنیم این ماجرا ممکنه کمی مشکل ساز بشه و یا حتی وقتی temp table درست کنیم و نیاز باشه دیتا اون رو با جداول ایجاد شده مقایسه کنیم هم ممکنه توی Collation های مختلف مشکل پیش بیاد.چگونه مغایرت Collation در دیتابیس را برطرف کنیمدر مرحله اول میتونیم Collation سطح دیتابیس رو به کمک T-SQL با SSMS تغییر بدیم، اما این تغییر روی تمام Object های داخل دیتابیس اعمال نمیشه و ممکنه همچنان مشکل شما باقی بمونه، متاسفانه تغییر Collation تمام دیتا کمی دردسر سازه.تا اینجا مشخص شد Collation چند سطح داره :1- سطح سرورموقع نصب Instance تعیین میشه.2- سطح دیتابیسموقع ایجاد یا بازگردانی دیتابیس مشخص میشه و میتونه ارث بری کنه از Collation سطح سرور.3- سطح Objectموقع ایجاد Object از Collation سطح دیتابیس ارث بری میکنه اما در صورت تغییر Collation DB تغییر نمیکنه و نیاز هست به صورت مجزا تنظیم بشه.دقت کنید که Collation در سطح Table برای SQL Server بی معنی هست ، یعنی از سطح دیتابیس مستقیم میره سراغ Object های موجود توی Table ، مثلا برای هر Column تعریف شده در جدول میتونه معنی داشته باشه.اما راه حل های موجود برای رفع مغایرت یا عدم همخوانی Collationفرض کنیم قرار هست دو جدول از دو دیتابیس مختلف با هم Union بشه، با اجرا کوئری خطا Msg 468 که مربوط به Collation conflict هست به وجود خواهد اومد.Msg 468, Level 16, State 9, Line 2 Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS” and “Arabic_CI_AS” in the UNION operation. اولین قدم اینکه بریم سراغ یک از دیتابیس ها و به کمک T-SQL یا SSMS اقدام به تغییر Collation اون کنیم.کد T-SQL :USE masterGO ALTER DATABASE MyOptionsTest COLLATE French_CI_AS GOو با این کد از تغییر Collation مطمئن بشیم :–Verify the collation setting.SELECT name, collation_name FROM sys.databasesWHERE name = N‘MyOptionsTest’GOحالا اقدام به اجرا Query می کنیم. مشاهده خواهید کرد که همچنان Msg 468 وجود داره، چرا ؟همونطور که گفته شد تغییر Collation تو سطح دیتابیس نمیتونه تمام Collation های Object های اون دیتابیس رو تغییر بده و خودتون باید برای این کار اقدام کنیم. برای این کار کوئری های نوشته شده که به سراغ تک تک Object ها میره و اونها رو تغییر میده.نمونه کوئری :DECLARE @collation NVARCHAR(64)SET @collation = 'Latin1_General_CI_AS'SELECT 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ' + 'ALTER COLUMN [' + COLUMN_NAME + '] ' + DATA_TYPE + '(' + CASE CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN 'MAX'ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END + ') ' + 'COLLATE ' + @collation + ' ' + CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END FROM INFORMATION_SCHEMA.columnsWHERE COLLATION_NAME IS NOT NULL AND TABLE_NAME IN( SELECT TABLE_NAME from information_schema.tables WHERE table_type = 'BASE TABLE' ) AND COLLATION_NAME <> @collationنتیجه اجرا کوئری :و در نهایت نیاز خواهد بود کوئری های ALTER ایجاد شده رو بر روی دیتابیس مورد نظر اجرا کنید تا Collation مربوط به تمام Object ها تغییر کنه .

قابلیت Batch Mode in RowStore در SQL Server

نیما جمشیدی — Tue, 29 Sep 2020 13:18:26 +0330

از موضوعاتی که توی مطلب Intelligent query processing in SQL databases بهش اشاره کردیم و قرار بود بیشتر در موردش صحبت کنیم مفهوم Batch Mode on Rowstore هست. چون قراره به فیلم آموزشی مربوط به کنفرانس سال 2019 گروه SQLBits اشاره کنم، به معرفی کوتاهی درباره این گروه و کنفرانس سالانه SQL Bits می پردازیم. در پایان مثال کاربردی از تاثیر عملکرد قابلیت پردازش Batch Mode در بهبود کارایی و کاهش زمان اجرا کوئری میاریم.گروه SQLBits به صورت سالانه بزرگترین کنفرانس اروپایی SQL Server رو در لندن برگزار میکنه و هر دوره افراد برجسته این رشته برای سخنرانی و ارائه آموزش ها شرکت میکنن.کنفرانس SQLBits 2020 قرار هست 31 March تا 4 April توی لندن برگزار بشه و تا اینجا اسامی تعدادی از سخنران ها و سرفصل های مورد بحث اعلام شده، سعی میکنیم تو مطلب جداگونه ای بهش بپردازیم.اما چیزی که به این مطلب مربوطه SQLBits 2019 هست که توی یکی از ارائه ها Niko Neugebauer مفهوم Batch mode رو در نسخه SQL Server 2019 شرح داده.خوشبختانه تیم SQLBits ویدئو ارائه های سالهای قبل رو تو سایت خودش منتشر میکنه، برای درک کامل مفهوم Batch Mode on Rowstore بهتون پیشنهاد میکنم که حتما حوصله کنین و این ارائه رو ببینید.Batch_Execution_Mode_on_the_Rowstoreاما اگه نخواستین این فیلم رو ببینین، تعریف ارائه شده در مورد Batch Mode رو از سایت blog.sqlauthority.com در ادامه میارم. درضمن Pinal Dave، نویسنده sqlauthority جز افراد ارائه دهنده در سالهای گذشته و همچنین در SQLBits 2020 خواهد بود که امسال با عنوان ارائه SQL Server Performance Tuning Real-World Scenarios در این کنفرانس شرکت میکنه.لیست ارائه های Pinal Dave مربوط به سال های گذشته:sqlbits.com/speakers/Pinal_Daveقرار بود توی این مطلب در مورد Batch Mode on Rowstore بنویسیم اما بیشتر به کنفرانس SQL Bits پرداخته شد، به نظرم لازمه به Brent Ozar هم اشاره کنم، Brent Ozar در کنفرانس SQLBits 2020 حضور داره و قرار هست Deadlocks: Lets Do One, Understand It, and Fix It رو ارائه بده.کم کم سعی میکنیم به سال های گذشته این کنفرانس بپردازیم و کنفرانس 2020 رو هم دنبال کنیم .Simple Example of Batch Mode in RowStore:(مطلب از زبان نویسنده اصلی، Pinal Dave بیان شده)یکی از دلایلی که من عاشق شغلم هستم، اینکه هر روز با آخرین تکنولوژی کار میکنم و باعث افزایش سرعت کامپیوتر مشتری ها میشم.اخیرا یکی از مشتری ها سیستم خودش رو به آخرین نسخه Sql Server یعنی 2019 ارتقا داد، بعد از Upgrade اونها انتظار بهبود کارایی چشم گیری رو داشتن، اما به طور کلی هیچ تغییر محسوسی ندیدن.در نهایت با من تماس گرفتن و ما فرایند Comprehensive Database Performance Health Check رو شروع کردیم. خوشبختانه هنگام کار، من تونستم مثال خوبی از کاربرد Batch Mode in RowStore به مشتری نشون بدم و به این طریق بهبود کارایی در نسخه SQL Server 2019 براشون قابل درک شد.در نسخه Sql server ،2019 امکان Batch mode رو درحالت Row store پشتیبانی میکنه، چیزی که قبلا فقط به ازاء به کارگیری column store index امکان پذیر بود. مهمترین تاثیری که به کارگیری Batch mode در مقایسه با مدل سنتی Row mode داره، بهبود کارایی در عملیات کوئری های دارای aggregation هست.مدل سنتی Row mode پردازش رو بر پایه row-by-row انجام میده اما مدل Batch execution mode پردازش رو بر روی دسته های دیتا ( batches ) انجام میده، برای مثال یک batch میتونه شامل چند صد Row باشه. تعداد Row ها در هر Batch بین 64 تا 912 هست. این عدد میتونه توی Update ها یا نسخه های بعدی Sql server تغییر کنه.مثال ساده :یه جدول با دیتا آزمایشی ایجاد میکنیم، توجه داشته باشین که برای این تست حتما به دیتا زیادی نیاز هست.توی این مثال از دیتابیس AdventureWorks استفاده میکنیم.USE AdventureWorks2017 GO -- Create New Table CREATE TABLE [dbo].MySalesOrderDetail]( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL,[OrderQty] [smallint] NOT NULL,[ProductID] [int] NOT NULL,[SpecialOfferID] [int] NOT NULL,[UnitPrice] [money] NOT NULL,[UnitPriceDiscount] [money] NOT NULL,[LineTotal] [numeric](38, 6) NOT NULL,[rowguid] [uniqueidentifier] NOT NULL,[ModifiedDate] [datetime] NOT NULL) ON [PRIMARY] GO -- Create clustered index CREATE CLUSTERED INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail] ( [SalesOrderDetailID]) GO -- Create Sample Data Table -- WARNING: This Query may run upto 2-10 minutes based on your systems resources INSERT INTO [dbo].[MySalesOrderDetail] SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount],[LineTotal],[rowguid],[ModifiedDate] FROM Sales.SalesOrderDetail S1 GO 50برای انجام تست اولمون Campatibility level رو به 14 تغییر میدیم که مربوط به sql server 2017 میشه و Select رو اجرا میکنیم:-- 2017 ALTER DATABASE [AdventureWorks2017] SET COMPATIBILITY_LEVEL = 140 GO -- Comparing Regular Index with ColumnStore Index USE AdventureWorks2017 GO SET STATISTICS IO, TIME ON GO -- Select Table with regular Index SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice, SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty FROM [dbo].[MySalesOrderDetail] GROUP BY ProductID ORDER BY ProductID GOهمونطور که در کوئری پلن ایجاد شده می بینیم Actual Execution Mode به صورت Row هست.حالا Campatibility level رو به 15 برمیگردونیم تا از قابلیت Batch mode مربوط به SQL server 2019 استفاده کنیم و مجددا Select رو اجرا میکنیم:-- 2019 ALTER DATABASE [AdventureWorks2017] SET COMPATIBILITY_LEVEL = 150 GO -- Comparing Regular Index with ColumnStore Index USE AdventureWorks2017 GO SET STATISTICS IO, TIME ON GO -- Select Table with regular Index SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice, SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty FROM [dbo].[MySalesOrderDetail] GROUP BY ProductID ORDER BY ProductID GOکوئری پلن مربوط به این حالت، Actual Execution Mode رو به صورت Batch انجام داده.با مقایسه همزمان این دو حالت ،میبینیم که کوئری پلن ها مشابه و با Logical read نزدیک بهم اجرا شدن اما CPU time مربوط به اجرا کوئری در حالت Batch mode کمتر بوده .در واقع این نشون میده، پردازش داده ها به صورت دسته ای باعث صرف هزینه کمتری در مقایسه با پردازش تک به تک شده و زمان اجرا کوئری رو بهبود میده .من این تست رو روی کوئری های مختلفی ازمایش کردم و به طور کلی میشه گفت در صورتی از حالت Batch mode در کوئری استفاده بشه بین 20 تا 30 درصد بهبود کارایی خواهیم داشت.

مفهوم Intelligent query processing در SQL Server

نیما جمشیدی — Tue, 29 Sep 2020 12:59:05 +0330

مفهوم IQP (Intelligent query processing) به مجموعه ویژگی هایی گفته میشه که به صورت هوشمند سعی دارند هزینه اجرا کوئری در SQL Server رو کاهش بدن، برخی از این قابلیت ها از نسخه 2017 ایجاد شدن و برخی مربوط به نسخه 2019 هستند، در این مطلب سعی میکنیم تمام این قابلیت ها رو عنوان کنیم و کم کم به شرح اونها بپردازیم .لیست قابلیت ها :Adaptive Joinsبا ایجاد یک کوئری پلن امکان استفاده از Hash Join یا Nested Loop Join را درون کوئری پلن به وجود میاره. یعنی با توجه به پارامترهای ورودی کوئری ، به صورت هوشمند تصمیم میگیره که این کوئری پلن رو با Hash Join اجرا کنه یا Nested Loop .Approximate Count Distinctدر حجم بالای دیتا گاهی نیاز به گرفتن Count با سرعت بالا وجود داره، حتی اگه عدد به دست اومده تقریبی باشه. این قابلیت دستور APPROX_COUNT_DISTINCT رو جایگزین COUNT(DISTINCT()) کرده. مثلا برای جدولی با 10 میلیارد رکورد ، در زمان کمتری نتیجه رو محاسبه میکنه.Batch Mode on Rowstoreمفهوم Batch Mode Processing از نسخه 2012 و همراه با کاربرد ایندکس های Column Store مطرح شد. در کل دو روش پردازش دیتا در SQL وجود داره Row Mode Execution و Batch Mode Execution . مدل Row mode همون مدل سنتی دیتابیس های RDBMS هست که سراغ تک تک رکورد ها میره و اونا رو استخراج و پردازش میکنن اما مدل Batch Mode گروهی از رکوردها رو باهم پردازش میکنه و فقط سراغ یک رکورد نمیره. (در Column stored index گروهی از رکوردها که مربوط به یک Column هستند )، قابلیت اضافه شده اخیر همچنین این امکان رو (پردازش فله ای) برای رکورد های ذخیره شده به صورت سنتی فراهم کرده .Scalar UDF Inliningیکی از مسائل بهینه سازی که همیشه در کوئری نویسی وجود داره، بکارگیری function داخل کوئریه. در صورتی که قرار باشه کوئری به طور مثال 1 میلیون رکورد رو استخراج کنه ، در شرایطی ممکنه 1 میلیون بار function فراخوانی بشه، که Performance پایینی خواهد داشت. ویژگی جدیدی که در نسخه 2019 افزوده شده، در شرایطی میتونه اینfunction ها رو به صورت inline درنظر بگیره. درواقع هنگام اجرا کوئری فرض میکنه کد نوشته شده داخل Function ، داخل کوئری اصلی وجود داره و فقط یکبار به function مراجعه میکنه. در ضمن به جدول سیستمی sys.sql_modulesستون is_inlineable افزوده شده که نشون میده کدوم یک از function ها قابلیت اجرا به صورت inline دارد.Interleaved Executionدر مواقعی اجرای بخشی از کوئری به صورت جداگانه و اجرا ادامه کوئری براساس نتیجه به دست اومده باعث بهبود کارایی و کاهش هزینه می شه. این روش در مقابل اجرا تمام کوئری به صورت یکپارچه میتونه کارایی بالاتری داشته باشه. این ویژگی در شرایطی به SQL Server قابلیت تفکیک کوئری به بخش های مجزا رو میده .Memory Grant Feedbackاجرا ء کوئری در صورتی که شامل عملیات های sort و hash join باشه نیاز به مموری جهت نگهداری رکوردها داره که SQL Server میزان مموری لازم برای انجام این عملیات رو حدس میزنه و بهمون میزان مموری allocate میکنه ( Granted Memory ) . گاهی پیش میاد که میزان مموری allocate شده اشتباه باشه، در صورت کم بودن مموری، کوئری برای اجرا سراغ tempdb میره و از دیسک کمک می گیره که کارایی پایینی داره. در بخش MemoryGrantInfo مربوط به جزئیاتQuery plan. علاوه بر Granted Memory پارامتر جدیدی با عنوان IsMemoryGrantFeedbackAjusted اضافه شده که میتونه مقادیر : No : First time , Yes: adjusting , Yes:Stable رو داشته باشه .قابلیت افزوده شده این امکان رو میده تا درصورتی که در دفعات بعدی اجرا کوئری، شرایط اجرا عوض شد و میزان مموری مورد نیاز کوئری تغییر شدیدی کرد (کاهش یا افزایش )، SQL Server به صورت خودکار این رو تشخیص بده و برای دفعات بعد مموری allocate شده رو اصلاح کنه. به این معنی خواهد بود که بار اول اجرا کوئری با شرایط جدید، همچنان کندی خواهیم داشت اما با تشخیص No : First time اقدام به اصلاح مموری کرده و دفعات بعد مشکل کندی حل میشه .Table Variable Deferred Compilationاین قابلیت با بهبود اجرا کوئری های شامل Table Variable ، امکان عملکرد بهتر در حدس زدن وضعیت دیتاهای موجود در متغیر و در نتیجه افزایش کارایی رو فراهم میکنه .منبع :docs.microsoft.com

قابلیت Scalar UDF Inlining در SQL Server

نیما جمشیدی — Tue, 29 Sep 2020 12:55:28 +0330

یکی از مسائل بهینه سازی که همیشه در کوئری نویسی وجود داره، بکارگیری function داخل کوئریه. در صورتی که قرار باشه کوئری به طور مثال 1 میلیون رکورد رو استخراج کنه ، در شرایطی ممکنه 1 میلیون بار function فراخوانی بشه، که Performance پایینی خواهد داشت. قابلیت Scalar UDF Inlining در SQL 2019 این امکان رو فراهم میکنه تا کوئری به صورت بهینه تری اجرا بشه .قابلیت Scalar UDF Inliningویژگی جدیدی که در نسخه SQL Server 2019 افزوده شده و در سری قابلیت های intelligent-query-processing قرار میگیره، در شرایطی میتونه این function ها رو به صورت inline درنظر بگیره، درواقع هنگام اجرا کوئری فرض میکنه کد نوشته شده داخل Function، داخل کوئری اصلی وجود داره و فقط یکبار به function مراجعه میکنه و جالب تر اینکه به جدول سیستمی sys.sql_modules ستون is_inlineable افزوده شده که نشون میده کدام یک از function ها قابلیت اجرا به صورت inline دارد.منبع :docs.microsoft.com

تاثیر Order by در نتیجه کوئری SQL Server

نیما جمشیدی — Tue, 29 Sep 2020 12:52:37 +0330

پیش تر در مطلب مربوط به Table Scan اشاره مختصری کردیم به اینکه چطور دو کوئری یکسان در یک سرور بدون تغییر هیچ یک از عوامل موثر میتونه نتایج متفاوتی داشته باشه. در این مطلب سعی میکنیم عامل دیگه که باعث تغییر نحوه مرتب سازی دیتا کوئری میشه رو بررسی کنیم. همه ما میدونیم که بکارگیری Order by تضمینی هست برای اینکه همیشه با ترتیب یکسانی result مورد نیاز رو دریافت کنیم، اما چه عاملی باعث میشه که در صورت عدم استفاده از Order by در اجرا کوئری های SQL Server ، نتایج متفاوتی بگیریم، در ادامه سعی میکنیم با استفاده از مثال این عامل رو شرح بدیم .کوئری پایین رو روی دیتابیس Stack Overflow اجرا کنید:وقتی کوئری پلن رو بررسی می کنیم، متوجه می شیم که برای نمایش خروجی از ایندکس کلاستر جدول استفاده شده، در نتیجه خروجی براساس ایندکس کلاستر یا همون Id مرتب و نمایش داده میشهحالا روی ستون DisplayName یه ایندکس می سازیم و دوباره کوئری رو اجرا می کنیم :حالا مجددا کوئری پلن رو می بینیم :همونطور که می بینید به جای استفاده از ایندکس Cluster از ایندکس جدیدی که ساختیم، استفاده شد. چرا؟به این خاطر که Engine اس کیو ال برای پاسخ دادن به کوئری ما، دنبال کوچکترین کپی از دیتای جدول User میگرده تا بتونه اون چیزی که ما میخوایم بهمون نشون بدهاگه وضعیت ایندکس های جدول User رو ببینیم متوجه می شیم که سایز ایندکس Cluster حدود 58 مگابایت و سایز ایندکس NonClustered حدود 9 مگابایته، در نتیجه استفاده از ایندکس NonClustered بهینه تره.نتیجه اینکه SQL Server ممکنه از ایندکسی استفاده کنه که ما انتظار نداریم در نتیجه یک کوئری ممکنه در اجراهای مختلف، خروجی هایی با Order های متفاوتی تولید کنه.یه مثال دیگه :اگه به همون کوئری یه Where اضافه کنیم و شرط Where یعنی ستون reputation رو Include ایندکس کنیم چه اتفاقی میفته؟با مشاهده کوئری پلن میبینیم که:ایندکسی که ساختیم براساس DisplayName مرتب شده در نتیجه برای اینکه SQL Server رکوردهایی رو پیدا کنه که دارای Reputation=1 هستند، باید ایندکس رو Scan کنه. بعد از انجام اسکن، خروجی به صورت اتوماتیک براساس DisplayName مرتب شده.حالا یه ایندکس بهتر میسازیم و کوئری رو دوباره اجرا می کنیم:این ایندکس براساس Reputation مرتب شده اما هیچ مرتب سازی روی DisplayName نداره.اینبار دیگه نیازی به Scan نیست چون ایندکس جدید براساس Reputation مرتب شده و شاهد Index Seek هستیم.همون طور که دیدید استفاده از ایندکس های مختلف توسط SQL Server باعث میشه که نتونیم Order خروجی کوئری رو کنترل کنیم. در نتیجه تنها راهی که همیشه Order یکسان بهمون میده استفاده صریح از Order By در کوئریه.منبع :www.brentozar.com