بهبود سرعت و کارایی برنامه های قدیمی مبتنی بر SqlServer
برنامه نویسان قدیمی ما در سیستم های قدیمی اکثرا سبک کوئری نویسی خاص خود را در آن زمان داشتند ، بسیاری از این دوستان کوئری های Ad-Hoc و یا غیر پارامتری می نوشتند که بسیار بسیار فشار کاری زیادی بر روی SQL Server وارد میکرد . زمانی که کوئری های Ad-Hoc زیادی بر روی حافظه وجود داشته باشد به علت محدود بودن کش PLan های Sql Server به مرور حافظه درگیر کش Plan های بی مصرف شده و باعث عدم کارایی سیستم می شود .
با Enable کردن ویژگی Optimize for Ad hoc Workloads انجین SQL Server به صورت اتوماتیک کش Plan های کوئری های مذکور که بسیار کم استفاده بودند را حذف کرده و باعث آزاد شدن حافظه برای کش کردن کوئری هایی که بیشتر مورد استفاده قرار می گیرند می شود .
یک نمونه کوئری غیر پارامتری را مشاهده می کنید :
SELECT * FROM Student WHERE SubmitDate ='25 Apr 2022'
SET @Query= 'SELECT * FROM Course WHERE OrderDate = ' + '''' +@par + ''''
EXEC (@Query)
جهت فعال سازی این گزینه می توان از اسکریپت زیر استفاده کرد :
exec sp_configure 'optimize for ad hoc workloads',1;
RECONFIGURE;
Go
-- Optimizing for Ad hoc Workloads
exec sp_configure 'show advanced options',1;
RECONFIGURE;
go
برای پیدا کردن کوئری هایی که فقط یک بار از کش Plan استفاده کرده اند می توان از اسکریپت زیر استفاده کرد :
SELECT objtype, cacheobjtype,
AVG(usecounts) AS Avg_UseCount,
SUM(refcounts) AS AllRefObjects,
SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS Size_MB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1
GROUP BY objtype, cacheobjtype;
**نکته : البته با بازنویسی برنامه های قدیمی و استفاده بیشتر از ORM ها به مرور این دست مشکلات کاهش یافت و نحوه ارسال کوئری ها به سمت دیتابیس به شکل بهتری در آخر انجام می شود .
مشکل کوئری های غیر پارامتری چیست ؟
وقتی که از این دست کوئری های غیر پارامتری به صورت عمده استفاده می شود مشکلی به اسم Cache Bloat بوجود می آید . در شرح این مشکل می توان گفت که Sql Server برای اجرای بهینه کوئری ها برای هر کدام Plan های خاصی می سازد ، مشکل زمانی بروز پیدا میکند که یک سری کوئری ها از دید Developer یکسان تشخیص داده می شود ولی از دید Sql Server خیر ، علت آن هم عدم استفاده از پارامتر ها در کوئری بوده که باعث می شود انجین Sql برای هر کدام Plan جدایی را محاسبه کرده و کش کند . مشکل زمانی حاد تر شده که حجم این کوئری ها بسیار بالا باشد که باعث مصرف حافظه ی بسیار بالا و Cpu Usage بالایی می شود .
بهترین راه حلی که از بروز این مشکلات جلوگیری می کند بازنویسی دوباره کوئری ها و پارامتریک کردن آنهاست که باعث می شود حتی با تغییر در پارامتر ها Sql همه ی این کوئری ها را یکسان در نظر بگیرد و تلاش برای محاسبه Plan های جدید نکند.