خانه 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 به شما اجازه تعریف ایندکس را نخواهد داد. در قسمت بعدی با یکی دیگر از قاتلین ایندکسها آشنا خواهید شد. چه رتبه ای میدهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش برچسب ها # create index sql server# Index در SQL Server# آموزش SQL Server# افزایش سرعت در Sql Server# انواع ایندکس در پایگاه داده# مفهوم Index در SQL Server معرفی نویسنده معرفی محصول مهدی شیشه بری دوره آموزش کوئری نویسی در SQL Server 1.000.000 تومان مقالات مرتبط ۰۳ خرداد مهندسی نرم افزار چالش های مدرن سازی معماری نرم افزار و راهکارهای آن علیرضا ارومند ۲۴ اردیبهشت مهندسی نرم افزار مدرن سازی معماری نرم افزار علیرضا ارومند ۱۹ اردیبهشت هوش تجاری بهبود عملکرد Tabular Model در SQL Server با سه تکنیک کاربردی مسعود طاهری ۱۰ اردیبهشت SQL Server استفاده از Credential و Proxy در SQL Server Agent حسن سلیمانی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ jml.maryam ۲۷ / ۱۰ / ۹۶ - ۰۱:۰۳ با سلام و عرض ارادت ممنونم از مقالتون، مهندس شیشه بری، چنانچه بعد از مدتی مقادیر یکی از جز فیلدهای مشارکت کننده در ستونهای محاسباتی(که ایندکس براش تعریف شده) تغییر کنه، آیا مقدارش در ایندکسش هم اصلاح میشه؟ پاسخ به دیدگاه آرزو محمدزاده ۳۰ / ۱۰ / ۹۶ - ۰۹:۱۱ با سلام و عرض ادب همواره این اصل را بهخاطرداشته باشید که بهازای هر یک از اعمال دستکاری رکوردهای جداول از قبیل UPDATE، INSERT و DELETE همان اتفاق نیز بهازای رکوردهای موجود در ایندکسها خواهد افتاد. برای آنکه این موضوع را بررسی کنید مثلا میتوانید بلافاصله پس از بهروزرسانی مقدار رکوردی که دارای ایندکس است از دستور زیر استفاده کرده و محتوای لاگ فایل را بررسی کنید . SELECT * FROM fn_dblog (NULL,NULL); GO در این حالت خواهید دید که علاوه بر ثبت بهروزرسانی مقدار رکورد در لاگ فایل، بهروزرسانی مقدار ایندکس متناظر نیز لحاظ خواهد شد. پیشنهاد میکنم حتما محصول جنون سرعت نیک آموز را تهیه کنید. این محصول علاوه بر قیمت مناسب دارای اطلاعات بسیار ارزشمندی است. پاسخ به دیدگاه jml.maryam ۲۷ / ۱۰ / ۹۶ - ۰۱:۰۳ با سلام و عرض ارادت ممنونم از مقالتون، مهندس شیشه بری، چنانچه بعد از مدتی مقادیر یکی از جز فیلدهای مشارکت کننده در ستونهای محاسباتی(که ایندکس براش تعریف شده) تغییر کنه، آیا مقدارش در ایندکسش هم اصلاح میشه؟ پاسخ به دیدگاه آرزو محمدزاده ۳۰ / ۱۰ / ۹۶ - ۰۹:۱۱ با سلام و عرض ادب همواره این اصل را بهخاطرداشته باشید که بهازای هر یک از اعمال دستکاری رکوردهای جداول از قبیل UPDATE، INSERT و DELETE همان اتفاق نیز بهازای رکوردهای موجود در ایندکسها خواهد افتاد. برای آنکه این موضوع را بررسی کنید مثلا میتوانید بلافاصله پس از بهروزرسانی مقدار رکوردی که دارای ایندکس است از دستور زیر استفاده کرده و محتوای لاگ فایل را بررسی کنید . SELECT * FROM fn_dblog (NULL,NULL); GO در این حالت خواهید دید که علاوه بر ثبت بهروزرسانی مقدار رکورد در لاگ فایل، بهروزرسانی مقدار ایندکس متناظر نیز لحاظ خواهد شد. پیشنهاد میکنم حتما محصول جنون سرعت نیک آموز را تهیه کنید. این محصول علاوه بر قیمت مناسب دارای اطلاعات بسیار ارزشمندی است. پاسخ به دیدگاه حسن ضرابی ۰۳ / ۰۸ / ۹۶ - ۰۴:۰۰ با سلام و خسته نباشید خدمت جناب آقای مهندس مهدی شیشه بری از این مقاله خوب بسیار ممنونم پاسخ به دیدگاه حسن ضرابی ۰۳ / ۰۸ / ۹۶ - ۰۴:۰۰ با سلام و خسته نباشید خدمت جناب آقای مهندس مهدی شیشه بری از این مقاله خوب بسیار ممنونم پاسخ به دیدگاه