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

این مطلب برای متخصصین پایگاه داده و DBA هایی که سالها با موارد مختلف دیتابیس SQL Server کار میکنن نوشته نشده، شامل توصیه های عمومی برای Developer ها و افرادی میشه که بخشی از کار روزانه شون کوئری نویسی در 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
GO

SQL 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 دیتابیسون.

در پایان باید گفت مواردی که اشاره کردیم توصیه های عمومی هستن و ممکنه تو حالت های خاصی نقض بشن، اما به طور کلی مفید و کاربردی هستن.

منبع :

brentozar.com