SQL Performance Tuning : بهینه سازی اجرای دستورات

[Babak-Aghili]

SQL Performance Tuning

من که چیزی بلد نیستم . :ناراحت: اون دو سه جمله ای که میدونم را مینویسم. به این امید که استارتی باشه برای اینکه بقیه بروبکس ادامه اش بدهند . :بوس:

1- SELECT Performance Tips

-- سعی شود که کوئری های سرچ ، در صورت امکان با سمت چپ ترین ایندکس مچ شود..

-- ORDER BY و DISTINCT را با گشاده دستی ، خرج نکنید. آنها ممکن است باعث ساخت جداول میانی شوند که باعث کاهش کارایی است.

-- حتی المقدور از UNION ALL بجای UNION استفاده کنید. UNION قبل از برگرداندن نتایج ، بایستی که آنها را سورت یا هش کند که بر کارایی تاثیر گذار است.

-- LOCK_TIMEOUT را روی عددی مثبت قرار دهید ( پیش فرض : -1 )

-- اگر کوئری شما ، شامل دستور IN هست که داخل آن ، بجای select از مقادیر ثابت استفاده شده ، مقادیر را برحسب میزان تناوب حضور در کوئری خارجی ، مرتب کنید. ( ساده ترین انتخاب میتواند این باشد که مقادیر را به صورت حرفی یا عددی ، مرتب کنید.)

-- از بین گزینه های JOIN و Nested Query ، گزینه JOIN را انتخاب کنید.

-- حتی المقدور از Cross Joins اجتناب کنید.

-- استفاده از TOP n در کوئری ها، خوبه !

-- SQL Server بصورت اتوماتیک ، نتایج کوئری ها را کش میکند. پس اگر قصد مقایسه کارایی کوئری هاتان را دارید ، از دستورات DBCC FREEPROCCACHE و DBCC DROPCLEANBUFFERS نیز استفاده کنید.

... ( ادامه داره ... )

[Babak-Aghili]

بهینه سازی ایندکس ها :

:چشمک: در صورتی که از کوئری های Select زیاد استفاده میکنید ؛ ایندکس های Clustered‌میتوانند بهترین انتخاب باشند. ایندکس های غیر کلاسترد ، برای select های تکی و جستجو به دنبای یک رکورد خاص ، مناسبتر هستند.

:چشمک: Index Selectivity را توسط فرمول : Selectivity = # of Unique Keys / # of Rows میتوان بدست آورد. ایندکسهای غیر کلاسترد که با توجه به این فرمول ، از 0.1 کمتر شوند ، مناسب نیستند و optimizer از آنها استفاده نخواهد کرد.

:چشمک: اگر ایندکسها روی چند ستون هستند ، آن ستونهایی که selective تر هستند را در ابتدا قرار دهید ... این امر در هنگام تشکیل درخت و رسیدن سریعتر به برگ و کاهش I/O به sql server کمک میکند.

:چشمک: برای گزارشاتی که به ندرت به تولید آنها نیاز دارید ، ایندکسی ذخیره نکنید . هروقت لازم داشتین ، درستش کنید و بعد هم DROP ش کنید.

:چشمک: مفید هست که پس از BULK INSERT ، مجددا ایندکسها را طراحی کنید و بسازید. چون این اعمال ؛ بویژه هنگامی که کلاینت ها زیاد هستند ، بدون حضور ایندکسها با سرعت بیشتری اجرا میشوند.

:چشمک: اگر optimizer بتواند همه دیتایی که لازم دارد را از یک ایندکس غیر کلاسترد ، بدون نیاز به ارجاع به جدول مربوطه ، بازیابی کند ، قطعا همین کار را خواهد کرد !! .که به آن Index Covering میگویند . اگر افزودن یک یا چند ستون کوشولو به یک ایندکس غیرکلاسترد ، همه دیتایی که کوئری لازم دارد را در اختیارش قرار میدهد ، حتما این کار را انجام دهید ... سرعت کوئری افزایش خواهد یافت. ... به این ستون ها و ایندکس ها covered INdex گفته میشود.

:چشمک: از اطلاعاتی که DBCC SHOWCONTIG در اختیارتان میگذارد ، تصمیم گیری کنید که آیا نیاز به rebuild کردن Clustered Index‌ ها دارید یا نچ ؟

:چشمک: برای rebuild کردن ایندکس ها روی یک جدول ، از DBCC DBREINDEX استفاده کنید.
مثال :
DBCC DBREINDEX ( 'Customers', PK_Customers')
مثال :
DBCC DBREINDEX ('Customers', ' ' , 100)

[بابک زواری]

:تشویق: عالی بود :تشویق:
ادامه بدید

[titbasoft]

از بین گزینه های JOIN و Nested Query ، گزینه JOIN را انتخاب کنیدیادمه یه دفعه یه سوال پسیده بودم (در مورد شبیه سازی IN زمانی که بیش از یک column داریم) که آقای ثباتی با 2 روش فوق مثال زده بودن و اتفاقا توجه بنده رو به exe plan این دو جلب کردن که کاملاً مثل هم بود. با این وجود آیا واقعا بین JOIN و N Q تفاوتی وجود داره؟

[Babak-Aghili]

بهینه سازی دستورات INSERT و UPDATE و DELETE

:لبخندساده SELECT ... INTO از دستورات معمولی INSERT سریعتر است. توجه داشته باشیم که این دستور باعث Lock شدن جدول میشود. بنابراین آنرا با دقت بکار ببریم.

:لبخندساده BULK INSERT برای لود کردن دیتاهای خارجی ، از INSERT سریعتر است چونکه در سطح پایین تری در سرور اجرا میشود. برای لود کردن مقادیر زیاد دیتا به سرور ، از آن استفاده کنید.

:لبخندساده با توجه به اینکه در دستورات UPDATE و DELETE معمولا از یک دستور SELECT استفاده میشود ، فلذا !! موارد ذکر شده برای Select در مورد اینها هم صادق است.

...

[Babak-Aghili]

بهینه سازی اجرای کرسورها :

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

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

:شیطان: تاموقعی که به ویژگیهای خاص کرسورهای static و KeySet نیاز مبرم پیدا نکرده اید ، از آنها استفاده نکنید. چونکه بازکردن این کرسورها ، باعث ایجاد یک جدول موقتی میشود . که نتایجش را در آن کپی کند و رفرنس دهد ...

:شیطان: اگر دیتایی که کرسور برمیگرداند را قصد ندارید که تغییر دهید ، آنرا بفرم READ_ONLY تعریف کنید. این به سرور میفهماند که این رکوردها ، تغییر نخواهند کرد.

:شیطان: در صورتی که هم کرسورهایتان Read Only هست و هم فقط به سمت جلو حرکت میکنید ( forward ONly ) .. آنها را FAST_FORWARD تعریف کنید. ... خاصیتش اینکه که خودش یک سری performance optimization های درون-ساخت داره که با FAST_FORWARD ها اعمال میکنه.

:شیطان: از تغییر دادن تعداد زیادی رکورد توسط یک کرسور که داخل یک حلقه در یک Transaction انداخته اید ، اجتناب کنید ! . چونکه هررکوردی که تغییر میدهید ، ممکن هست که تا پایان ترانساکشن ! بصورت Locked باقی بماند که البته به Transction Isolation Level هم بستگی دارد.

:شیطان: حواستان به آپدیت کردن ستونهای کلید، توسط کرسورهای دینامیک ؛ روی جداولی که کلیدهای کلاسترد ایندکس و غیر unique دارند ، باشد !! ( عجب جمله فلسفی ای شد !!). چونکه میتواند به Haloween Problem :گیج: منجر شود. اسکیوال سرور ، در رابطه با Non Unique Clustered Index Keys :لبخند: بصورت داخلی یک Sequence Number را بعنوان پسوند آنها قرار میدهد تا مجبورشان کند که Unique شوند. حال اگر یکی از این کلید ها را آپدیت کنید ، امکانش هست که باعث تولید مقداری بشوید که از قبل موجود بوده و سرور رامجبور میکنید که پسوندی بدان اضافه کند که اگر کرسور هم روی clustered index مرتب شده باشد ، باعث میشود که بعدا در مجموعه نتایج ، جایش تغییر کند. و چونکه کرسور را بصورت دینامیک استفاده کرده بودیم ، واکشی رکوردها از مابقی مجموعه نتایج باعث واگذاری دوباره رکورد میشود و این فرایند همچنان ادامه پیدا میکند و شرافتمندانه در یک Infinite Loop می افتید.

[Babak-Aghili]

یادمه یه دفعه یه سوال پسیده بودم (در مورد شبیه سازی IN زمانی که بیش از یک column داریم) که آقای ثباتی با 2 روش فوق مثال زده بودن و اتفاقا توجه بنده رو به exe plan این دو جلب کردن که کاملاً مثل هم بود. با این وجود آیا واقعا بین JOIN و N Q تفاوتی وجود داره؟

سوات من تا اینجا قد میده /\ :: ... بقیه اش را عالیجنابان توضیح بدهند .:: /\

آخه خود_ optimizer ، وقتی که nested query میبینه ، در صورتی که تشخیص بدهد که واقعا اینجا به nested query احتیاج نبوده ، آنرا بقول معروف Flat میکند و به join _ عادی تبدیل میکند.
نهایتا :: خیلی بهتر هست که بین جداول از join معمولی استفاده کنیم و تصمیم گیری در مورد نحوه پردازش آنرا بر عهده Sql Server بگذاریم. :چشمک:

[m-khorsandi]

این هم یه فایل توپ در مورد SQL Tuning.
آقای عقیلی عالی بود.

[Babak-Aghili]

همگی لطف دارید.مرسی.
هرجاش که غلط غولوط بود ، خودتون زحمت اصلاحش را بکشید.
جناب خرسندی ، بابت کتاب هم تشکرات.
______________________________________________


BULK COPY PERFORMANCE TIPS

:قهقهه: بجای bcp از BULK INSERT استفاده کنید. باوجود اینکه در پایین ترین سطح ، هردو از یک مکانیزم استفاده میکنند ، ولی دیتایی که از طریق BULK INSERT لود میشود ، از طریق پروتوکل Tabular Data Stream ردوبدل نمیشود بلکه از طریق Open Data Services عبور میکند و مستقیما به فرم یک OLE-DB rowset به Sql Server فرستاده میشود. به همین دلیل ، گاهی تا دوبرابر هم سریعتر از bcp است.

:قهقهه: درصورت امکان ، در هنگام لودکردن دیتا ، جداول را Lock کنید. واسه ی این کار از sp_tableoption و گزینه ی table lock on bulk load استفاده نمایید.

:قهقهه: سعی کنید در هنگام لود کردن ، این چهار معیار را فراهم کنید. برای nonlogged bulk load :
------- جدول ، قابل قفل شدن باشد.
------- در دیتابیس مقصد ، select/into bulk copy ، را چک کنید که on باشد.
------- جدول for replication ، نباشد.
------- اگر جدول ایندکسی دارد ، آنها هم خالی باشند.

:قهقهه: درنظر بگیرید که عملیات BULK INSERT را از طریق پارامتر BATCHSIZE ، به چند batch شکسته باشیم. این کار باعث میشود که لود روی transaction log کاهش پیدا کند چونکه هر batch بصورت جداگانه commit میشود. ولی عیب این کار در این هست که اگر عملیات به هردلیلی abort شود ،جدول مقصد در یک حالت فیمابین باقی میماند : batch ی که موقع پیش آمدن خطا ، درحال اجرا بوده ، Rollback میشود ولی batchهای قبل از آن ، در دیتابیس باقی خواهند ماند. ... پس بهتره که یک ستون کوشولوی LoadNumber در جدول مقصد در نظر بگیریم تا بفهمیم که چه سطرهایی توسط هر bulk load اضافه شده اند.

[بابک زواری]

خدا خیرت بده دستت هم درد نکنه میبینم از روزی که به راه راست هدایت شدی و سراغ محصولات میکروسافت اومدی چقدر تغییر کردی جیگر .

[Babak-Aghili]

مرسی. از خودم ذوق در وکردم.