خانه SQL Server قاتلین ایندکسها در SQL Server [بخش سوم] SQL Server افزایش سرعت SQL Server نوشته شده توسط: مهدی شیشه بری تاریخ انتشار: ۱۲ شهریور ۱۳۹۶ آخرین بروزرسانی: ۱۴ مهر ۱۴۰۲ زمان مطالعه: 6 دقیقه ۵ (۲) مقدمه در قسمتهای اول و دوم دیدید که چگونه با استفادهی نامناسب از عملگر LIKE و عملیات Concatenation، زمینههای عدم استفاده از تاثیر مثبت ایندکسها در افزایش کارآیی عملکرد کوئریها فراهم میشود. در این قسمت میخواهم شما را با یکی دیگر از سناریوهایی که موجب خنثی شدن قابلیت ایندکسها میشود، آشنا کنم. نکته مهم: تمامی کوئریهای این مجموعه مقالات، بر روی دیتابیس AdventureWorks2016 و در محیط SQL Server Management Studio 2016 و ۲۰۱۷ اجرا شده است. قاتل شماره ۳ – ستونهای محاسباتی (Computed Columns) همانطور که میدانید در برخی از موارد مقادیر یک یا چندین ستون از جدولی بهصورت عبارت یا Expression تعریف میشوند. چنین مقادیری ممکن است در حین اجرای یک کوئری و برای جلوگیری از محاسباتِ زمانِ اجرا مورد استفاده قرار گیرند. از طرفی چنین ستونهایی میتوانند با ستونهای دیگری وابستگی داشته باشند تا بهمحض هرگونه تغییری در سایر ستونها، تغییرات بر روی آنها نیز اِعمال شود. به چنین ستونهایی Computed Columns گفته میشود. زمانی که شما مجبور هستید تا نتیجه اجرای یک تابع یا محاسبات مبتنی بر چندین ستون را در جدولتان نگهداری کنید، استفاده از ستونهای محاسباتی میتواند برای شما مفید واقع شود. همواره به این نکته مهم توجه داشته باشید که قابلیت استفاده از ایندکسهای موجود بر روی ستونهایی که بر اساس آنها ستونهای محاسباتی شکل گرفته است، امکان پذیر نخواهد بود! برای بررسی این موضوع، ابتدا دو ستون محاسباتی جدید را برای جدول Person درنظر میگیریم. اولین ستون محاسباتی شامل ترکیبی از فیلدهای نام و نامخانوادگی است اما دومین ستون محاسباتی معنای خاصی نداشته و صرفا جهت بررسی یک سناریو تعریف میشود. USE AdventureWorks2016 GO ALTER TABLE Person.Person ADD FirstLastName AS (FirstName + ' ' + LastName) ,CalculateValue AS (BusinessEntityID * EmailPromotion); اکنون کوئریهای زیر را اجرا میکنیم. SET STATISTICS IO ON SELECT BusinessEntityID, FirstName, LastName, FirstLastName FROM Person.Person WHERE FirstLastName = 'Gustavo Achong'; SELECT BusinessEntityID, CalculateValue FROM Person.Person WHERE CalculateValue = 198; با مراجعه به Plan اجرایی این کوئریها خواهیم دید که جهت بازیابی رکوردها از عملیات Scan استفاده شده است. در قسمتهای قبلی این مجموعه مقالات به شما تذکر دادم که این عملیات با افزایش حجم دیتابیس، رکوردهای جدول و تعداد کاربران همزمان میتواند احتمال بروز پدیده Blocking و استفاده از I/O های بیشتر برای درخواستها را فراهم کند. این موضوع در بخش آمار و اطلاعات I/O نیز قابل مشاهده است که چگونه برای بازیابی نتایج موردنظر، تمامی رکوردها مورد ارزیابی قرار گرفته شده است. با توجه به عدم استفاده از ایندکس ستونهای مبداء در ستونهای محاسباتی، اکنون برای این دو ستونِ جدید ایندکسهایی از نوع NONCLUSTERED تعریف میکنیم. CREATE INDEX IX_PersonPerson_FirstLastName ON Person.Person(FirstLastName); CREATE INDEX IX_PersonPerson_CalculateValue ON Person.Person(CalculateValue); با اجرای مجدد کوئریها خواهیم دید که بازیابی رکوردها از طریق عملیات Index Seek انجام شده است. مقایسه آمار و اطلاعات I/O نیز نشان از کاهش چشمگیر تعداد Page های خواندهشده در این عملیات خواهد داشت. حتما این نکته مهم را بهخاطر بسپارید که ایندکسگذاری بر روی ستونهای محاسباتی مستلزم آن است که عبارات موجود در آنها بهصورت قطعی یا Deterministic باشد؛ یعنی در هر زمان از اجرای آن عبارت، نتایج مشابهی حاصل شود. بهعنوان مثال استفاده از تابع GETDATE() موجب عدم استفاده از قابلیت ایندکسگذاری بر روی چنین ستونهایی خواهد شد و SQL Server به شما اجازه تعریف ایندکس را نخواهد داد. در قسمت بعدی با یکی دیگر از قاتلین ایندکسها آشنا خواهید شد. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۲ اولین نفر باش دانلود مقاله قاتلین ایندکسها در SQL Server [بخش سوم] فرمت PDF 5 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان مقالات مرتبط ۱۵ مهر زبان های برنامه نویسی معرفی برترین نرم افزار برنامه نویسی اندروید به همراه معرفی ابزارهای مهم تیم فنی نیک آموز ۱۲ مهر زبان های برنامه نویسی متغیر ها در جاوا تیم فنی نیک آموز ۱۱ مهر SQL Server راهنمای جامع مانیتورینگ بکاپ ها در SQL Server تیم فنی نیک آموز ۰۹ مهر هوش تجاری dbt در ETL و ELT چیست و چه مزایایی دارد؟ نگین فاتحی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ jml.maryam ۲۷ / ۱۰ / ۹۶ - ۰۱:۰۳ با سلام و عرض ارادت ممنونم از مقالتون، مهندس شیشه بری، چنانچه بعد از مدتی مقادیر یکی از جز فیلدهای مشارکت کننده در ستونهای محاسباتی(که ایندکس براش تعریف شده) تغییر کنه، آیا مقدارش در ایندکسش هم اصلاح میشه؟ پاسخ به دیدگاه آرزو محمدزاده ۳۰ / ۱۰ / ۹۶ - ۰۹:۱۱ با سلام و عرض ادب همواره این اصل را بهخاطرداشته باشید که بهازای هر یک از اعمال دستکاری رکوردهای جداول از قبیل UPDATE، INSERT و DELETE همان اتفاق نیز بهازای رکوردهای موجود در ایندکسها خواهد افتاد. برای آنکه این موضوع را بررسی کنید مثلا میتوانید بلافاصله پس از بهروزرسانی مقدار رکوردی که دارای ایندکس است از دستور زیر استفاده کرده و محتوای لاگ فایل را بررسی کنید . SELECT * FROM fn_dblog (NULL,NULL); GO در این حالت خواهید دید که علاوه بر ثبت بهروزرسانی مقدار رکورد در لاگ فایل، بهروزرسانی مقدار ایندکس متناظر نیز لحاظ خواهد شد. پیشنهاد میکنم حتما محصول جنون سرعت نیک آموز را تهیه کنید. این محصول علاوه بر قیمت مناسب دارای اطلاعات بسیار ارزشمندی است. پاسخ به دیدگاه jml.maryam ۲۷ / ۱۰ / ۹۶ - ۰۱:۰۳ با سلام و عرض ارادت ممنونم از مقالتون، مهندس شیشه بری، چنانچه بعد از مدتی مقادیر یکی از جز فیلدهای مشارکت کننده در ستونهای محاسباتی(که ایندکس براش تعریف شده) تغییر کنه، آیا مقدارش در ایندکسش هم اصلاح میشه؟ پاسخ به دیدگاه آرزو محمدزاده ۳۰ / ۱۰ / ۹۶ - ۰۹:۱۱ با سلام و عرض ادب همواره این اصل را بهخاطرداشته باشید که بهازای هر یک از اعمال دستکاری رکوردهای جداول از قبیل UPDATE، INSERT و DELETE همان اتفاق نیز بهازای رکوردهای موجود در ایندکسها خواهد افتاد. برای آنکه این موضوع را بررسی کنید مثلا میتوانید بلافاصله پس از بهروزرسانی مقدار رکوردی که دارای ایندکس است از دستور زیر استفاده کرده و محتوای لاگ فایل را بررسی کنید . SELECT * FROM fn_dblog (NULL,NULL); GO در این حالت خواهید دید که علاوه بر ثبت بهروزرسانی مقدار رکورد در لاگ فایل، بهروزرسانی مقدار ایندکس متناظر نیز لحاظ خواهد شد. پیشنهاد میکنم حتما محصول جنون سرعت نیک آموز را تهیه کنید. این محصول علاوه بر قیمت مناسب دارای اطلاعات بسیار ارزشمندی است. پاسخ به دیدگاه حسن ضرابی ۰۳ / ۰۸ / ۹۶ - ۰۴:۰۰ با سلام و خسته نباشید خدمت جناب آقای مهندس مهدی شیشه بری از این مقاله خوب بسیار ممنونم پاسخ به دیدگاه حسن ضرابی ۰۳ / ۰۸ / ۹۶ - ۰۴:۰۰ با سلام و خسته نباشید خدمت جناب آقای مهندس مهدی شیشه بری از این مقاله خوب بسیار ممنونم پاسخ به دیدگاه