افزایش سرعت دیتابیس در SQL Server و کشف کوئری های کند

[vc_row][vc_column][vc_column_text]

مقدمه

هر برنامه یا وب سایت باید برای حفظ کاربران خود، باید عملکرد سریعی داشته باشد. در مواقع حساس، چند میلی ثانیه کاهش سرعت دریافت اطلاعات ممکن است مشکلات بزرگی ایجاد ‌کند. بنابراین برای اطمینان از اجرای صحیح تمام فعالیت ‌ها، باید سرور پایگاه داده خود را برای عملکرد بهتر و بهینه تر تنظیم کنیم.
در این مقاله سعی داریم تا یک روش گام ‌به ‌گام برای افزایش سرعت دیتابیس در SQL serverو کشف کوئری‌ های کند را بررسی کنیم.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

پیدا کردن مجرمان

مانند هر نرم ‌افزار دیگری، باید بدانیم که SQL Server یک نرم افزار ساخت و مدیریت پایگاه داده پیچیده است. اگر شاهد کندی سرعت در این نرم افزار هستیم، باید دریابیم که چرا آنطور که انتظار داریم کار نمی‌کند. در SQL Server ما باید داده‌ها را با سرعت و دقت هرچه بیشتر در سیستم ثبت کنیم. در صورت هر گونه مشکلی، باید موارد ذیل را بررسی کنیم:

•  ممکن است که سخت ‌افزار و تنظیمات نصب سیستم شما دچار مشکل شده باشد.
• ممکن است که برای پیاده‌ سازی SQL Server نیاز باشد تا کد مناسب T-SQL را ارائه کنیم.

اگرچه SQL Server نرم افزاری اختصاصی است، اما مایکروسافت راه‌ های زیادی برای درک و استفاده موثر از آن ارائه کرده است. اگر سخت‌ افزار خوب است و نصب به درستی انجام شده است، اما SQL Server هنوز به کندی اجرا می ‌شود، ابتدا باید بررسی کنیم که آیا خطاهای مربوط به نرم ‌افزار در سیستم وجود دارد. SQL Server برای هر درخواست کاربر از نخ استفاده می ‌کند و منظور از نخ، برنامه دیگری در داخل برنامه پیچیده ما به نام SQL Server است. لازم به ذکر است که این موضوع مربوط به سیستم عاملی که SQL Server روی آن نصب شده است، نیست. این موضوع مربوط به SQLOS است، که یک شبه سیستم عامل برای SQL Server به شمار می رود.
برای درک بهتر وضعیت فعلی عملکرد سیستم می‌ توان با استفاده از Management View (DMV)، آمار را محاسبه کرد.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

اسکریپ مناسب برای بررسی عملکرد سرعت دیتابیس در SQL Server

در حال حاضر اسکریپت‌ های زیادی وجود دارند که به صورت آنلاین عملکرد سرعت دیتابیس در SQL Server را بررسی می ‌کنند. . ولی ما به شما استفاده از اسکریپت پل رندال را توصیه می ‌کنیم، زیرا در وهله اول درک آن آسان است و همچنین تمام پارامترهای مهم برای بررسی آمار مورد نیاز شما در آن وجود دارد.
وقتی این اسکریپت را اجرا می ‌کنیم، باید روی سطرهای بالای نتیجه تمرکز کنیم زیرا آن ‌ها ابتدا تنظیم شده‌ اند و حداکثر نوع انتظار را نشان می‌ دهند.
فراموش نکنید که برای افزایش سرعت دیتابیس در SQL Server ، ما باید انواع انتظار را بشناسیم تا بتوانیم تصمیمات درستی بگیریم. برای آشنایی با انواع مختلف انتظار، می‌ توانیم به منابع آموزشی شرکت مایکروسافت مراجعه کنیم. درک درست از عملکرد سیستم بسیار مهم است، زیرا به ما کمک می‌ کند تا مشکلاتی نظیر ضعف سیستم فرعی ورودی یا خروجی یا عدم وجود حافظه کافی را درک کنیم و برای افزایش سرعت دیتابیس در SQL Server راهکارهای مناسبی پیدا کنیم.
مدیر پایگاه داده باید قبل از هر اقدامی در مورد آن به طور کامل تحقیق کند. در بیشتر مواقع، یافتن کوئری‌ های کند و T-SQL مشکل ساز و تنظیم صحیح آن‌ ها باعث می ‌شود تا ۶۰ تا ۷۰ درصد مشکلات به وجود آمده حل شوند.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

یافتن کوئری‌ های کند

ما می‌ توانیم برای پیدا کردن بدترین عملکرد ۲۰SQL T-از کد ذیل استفاده کنیم.

۲۰ SELECT TOP
total_worker_time/execution_count AS Avg_CPU_Time
، Execution_count
، total_elapsed_time/execution_count as AVG_Run_Time
، total_elapsed_time
، (SELECT
SUBSTRING(text, statement_start_offset/2+1, statement_end_offset) FROM sys. dm_exec_sql_text (sql_handle) 
) AS Query_Text
FROM sys. dm_exec_query_stats
ORDER BY Avg_CPU_Time DESC

[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]البته در استفاده از کدهای مختلف باید مراقب نتایج باشیم، اگر زمان استفاده از یک کد برای یافتن کوئری ‌های کند دارای حداکثر متوسط زمان اجرا است و فقط یک بار اجرا می ‌شود، در مقایسه با استفاده از کدی که در زمان متوسط اجرا می‌ شود و می ‌توان بارها در روز آن را اجرا کرد، دارای تاثیر کمتری است.

پیدا کردن تنظیم دقیق سرور برای افزایش سرعت دیتابیس در SQL Server

یافتن تنظیم دقیق T-SQL یک مفهوم مهم است. نکته اساسی که باید درک شود این است که چقدر می‌ توانیم Query یا پرس و جوهای T-SQL را بنویسیم و نمایه ‌ها را پیاده‌ سازی کنیم. این روند باید به نحوی باشد که بهینه ساز SQL بتواند یک برنامه بهینه شده برای انجام آنچه ما می‌ خواستیم پیدا کند. با هر نسخه جدید SQL Server، ما یک بهینه ساز پیچیده ‌تر دریافت می ‌کنیم که اشتباهات ما در نوشتن درخواست‌ های SQL بهینه نشده را پوشش می ‌دهد و همچنین اشکالات مربوط به بهینه ساز قبلی را برطرف می ‌کند. اما، هر چقدر هم که بهینه ساز هوشمند باشد، اگر نتوانیم آنچه را که می‌ خواهیم به آن بگوییم، بهینه ساز SQL نمی ‌تواند کار خود را انجام دهد.

SQL Server از الگوریتم ‌های جستجوی پیشرفته و مرتب ‌سازی استفاده می ‌کند. اگر در جستجو و مرتب‌ سازی الگوریتم ‌ها مهارت داریم، در اکثر مواقع می ‌توان حدس زد که چرا SQL Server اقدام خاصی را انجام می ‌دهد. همچنین وقتی پرس و جوهایی را که نیاز به تنظیم دقیق دارند بررسی می ‌کنیم، باید از برنامه اجرایی این پرس و جوها استفاده کنیم تا بتوانیم دریابیم که چگونه SQL سرور، آن ‌ها را تفسیر می ‌کند.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

باید بفهمیم که کدام اپراتورها بیشتر هزینه پرس و جو را بر عهده می ‌گیرند.

اگر اپراتور هزینه زیادی را متحمل می‌ شود، باید دلیل آن را بفهمیم. در بیشتر مواقع هزینه اسکن ‌ها بیشتر از جستجوهای دیگر است. ما باید بررسی کنیم که چرا یک اسکن خاص (اسکن جدول یا اسکن شاخص) به جای جستجوی نمایه اتفاق می ‌افتد. ما می ‌توانیم این مشکل را با پیاده‌ سازی نمایه‌ های مناسب در ستون‌ های جدول حل کنیم، اما مانند هر برنامه پیچیده، هیچ راه حل ثابتی وجود ندارد. به عنوان مثال، اگر جدول کوچک است، اسکن ‌ها سریعتر از موارد جستجو است.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

استفاده مجدد از برنامه‌ های اجرا شده برای افزایش سرعت دیتابیس در SQL Server

حتی اگر نمایه ‌های مناسب را روی جداول پیاده کنیم و کد T-SQL خوب بنویسیم، اگر از برنامه اجرایی مجدد استفاده نشود، با افزایش سرعت دیتابیس در SQL Server رو‌ به ‌رو نخواهیم بود. پس از تنظیم دقیق پرس و جوها، باید مطمئن شویم که ممکن است در صورت لزوم از برنامه اجرایی مجدداً استفاده شود. در صورت استفاده مجدد از برنامه، بیشتر زمان CPU صرف محاسبه برنامه اجرایی می ‌شود که قابل حذف است.

بهترین راه برای استفاده مجدد از برنامه اجرایی، اجرای روش‌ های ذخیره شده با پارامترهای مختلف است. هنگامی که ما در موقعیتی برای اجرای رویه‌ های ذخیره شده نیستیم، می‌ توانیم از sp_executesql آن استفاده کنیم، در صورتی که تنها تغییر در دستورات SQL مقادیر پارامترها باشد، می ‌توان از آن ‌ها برای اجرای دستورات T-SQL نیز استفاده کرد. در چنین شرایطی به احتمال زیاد SQL Server از برنامه اجرایی که در اولین اجرا ایجاد کرده است، استفاده مجدد خواهد کرد.
باید تکرار کنیم که مانند هر برنامه کامپیوتری پیچیده، هیچ راه حل ثابتی وجود ندارد. گاهی اوقات بهتر است دوباره برنامه را تدوین کنید.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

نحوه انجام استفاده مجدد از برنامه ‌ها

فرض می‌ کنیم که یک شاخص غیر خوشه ‌ای مربوط به اسم ستون ‌ها را داریم و نیمی از جدول دارای ارزش sri چند سطر pal در ستون name یا نام است. برای اولین پرس و جو، SQL Server از اسکن جدول استفاده می ‌کند زیرا نیمی از جدول دارای مقادیر یکسان است. اما برای پرس و جو دوم، بهتر است از اسکن فهرست استفاده کنید زیرا فقط چند ردیف دارای palارزش هستند.
حتی اگر درخواست ‌ها مشابه باشند، ممکن است برنامه اجرایی یکسانی راه حل مناسبی نباشد. در بیشتر اوقات نیز این موارد با یکدیگر متفاوت هستند. بنابراین قبل از تصمیم گیری باید همه چیز را با دقت تجزیه و تحلیل کنیم. اگر نمی ‌خواهیم از برنامه اجرایی مجدداً استفاده کنیم، همیشه می ‌توانیم از گزینه Recompile در رویه ‌های ذخیره شده استفاده کنیم.
به خاطر داشته باشید که حتی پس از استفاده از رویه ‌های ذخیره شده یا sp_executesql مواردی وجود دارد که برنامه اجرا مجدداً مورد استفاده قرار نمی ‌گیرد. از جمله مواردی که می ‌تواند باعث غیر قابل استفاده شدن اجرا مجدد برنامه شوند می ‌توان به موارد ذیل اشاره کرد:

•  هنگامی که نمایه ‌های مورد استفاده در پرس و جو تغییر می‌ کند یا حذف می ‌شود
• هنگامی که آمار، ساختار یا طرح جدول مورد استفاده در پرس و جو تغییر می ‌کند
•  اگر از گزینه Recompile استفاده کرده باشیم.
•  هنگامی که تعداد زیادی داده برای درج وجود، بروزرسانی یا حذف وجود داشته باشد.
•  وقتی DDL و DML را در یک پرس و جو ترکیب می‌ کنیم

[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

حذف فهرست ‌های غیر ضروری برای افزایش سرعت دیتابیس در SQL Server

برای افزایش سرعت دیتابیس در SQL Server باید پس از تنظیم دقیق پرس و جوها، نحوه استفاده از نمایه‌ ها را بررسی کنیم. اصلاح و نگهداری هر شاخص نیاز به CPU و ورودی یا خروجی ‌های زیادی دارد. هر بار که داده‌ ها را در پایگاه داده وارد می‌ کنیم، SQL Server نیز باید نمایه‌ ها را به ‌روز کند، بنابراین در صورت‌ عدم استفاده بهتر است آن ‌ها را حذف کنیم.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

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

هنگام تنظیم پایگاه داده، باید داده‌ ها را ذخیره کرده و پرونده ‌ها را جداگانه ثبت کنیم. دلیل اصلی این امر این است که نوشتن و دسترسی به پرونده ‌های داده متوالی نیست، در حالی که نوشتن و دسترسی به پرونده‌ های log به ترتیب است. اگر آن ‌ها را بر روی درایو یکسان قرار دهیم نمی ‌توانیم از آن‌ ها به روشی بهینه استفاده کنیم.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

مدیریت SQL Server

وظیفه اصلی هر مدیر پایگاه داده این است که اطمینان حاصل کند که سرور به طور روان کار می ‌کند و تا آنجا که ممکن است به مشتریان خدمات ‌رسانی می ‌کند. برای تحقق این امر، ما باید پایگاه‌ های داده جداگانه (در صورت امکان، در دستگاه ‌های جداگانه) برای محیط های زیر نگهداری کنیم:

•  تولید
•  توسعه
•  آزمایش کردن
•  تحلیلی

برای یک پایگاه داده تولیدی به یک پایگاه داده با حالت بازیابی کامل نیاز داریم و برای پایگاه‌ های داده دیگر، یک حالت بازیابی ساده کافی است.
آزمایش بر روی پایگاه داده تولید بار زیادی را روی گزارش تراکنش‌ ها، نمایه ‌ها، CPU و I/O وارد می ‌کند. به همین دلیل ما باید از پایگاه‌ های داده جداگانه برای تولید، توسعه، آزمایش و تجزیه و تحلیل استفاده کنیم. در صورت امکان، برای هر پایگاه داده از ماشین‌ های جداگانه استفاده کنید، زیرا بار CPU و ورودی/خروجی را کاهش می‌ دهد.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row]