خانه SQL Server حل مشکل تخمین Cardinality با استفاده از Filtered Statistics SQL Server نوشته شده توسط: تورج عزیزی ۱۲ تیر ۱۳۹۵ زمان مطالعه: 10 دقیقه ۰ (۰) مقدمه در این مقاله تاثیر Filtered Statistics را بر روی Performance خواهید دید. پیش نیازهای مقاله معرفی Statistics در SQL Server statistic ها در چه شرایطی توسط SQL Server به طور خودکار به روز می شوند از SQL Server 2008 به بعد، Filtered Statistics به عنوان یک قابلیت اضافه شد. با استفاده از Filtered Statistics می توان روی زیر مجموعه ای از دیتای کلی یک جدول شیء آمار ساخت. Filtered Statistics به این دلیل می تواند خیلی موثر واقع شود: قسمت Histogram در Statistics محدودیت ۲۰۰ طبقه ای را دارد و این به خاطر این است که اطلاعات شیء آمار باید در حداکثر یک Page جا شود. اما Filtered Statistics این امکان را فراهم می کند که بتوان روی زیر مجوعه ای از دیتای یک جدول آمار ساخت و البته چون رکوردهای کمتری نسبته به کل جدول داریم آمار دقیق تری خواهیم داشت. و در نتیجه تخمین Cardinality توسط Query Optimizer بهتر انجام می شود به این معنی که پلن بهتری خواهیم داشت و در نهایت سرعت بهتری! مثال CREATE STATISTICS Country_Austria ON Country(ID) WHERE Name = 'Austria' GO و مهمتر اینکه Query Optimizer فقط وقتی همان شرط بالا در یک کوئری بیاید از این شیء آمار و در غیر این صورت از آمار پیش فرض استفاده می کند: SELECT SalesAmount FROM Country INNER JOIN Orders ON Country.ID = Orders.ID WHERE Name = 'Austria' GO آزمایش تاثیر Filtered Statistics بر Performance کد های زیر را برای تولید دیتای آزمایشی اجرا کنید: -- Create a new database CREATE DATABASE FilteredStatistics GO -- Use it USE FilteredStatistics GO -- Create a new table CREATE TABLE Country ( ID INT PRIMARY KEY, Name VARCHAR(100) ) GO -- Create a new table CREATE TABLE Orders ( ID INT, SalesAmount DECIMAL(18, 2) ) GO ساخت ایندکس -- Create a Non-Clustered Index CREATE NONCLUSTERED INDEX idx_Name ON Country(Name) GO -- Create a Clustered Index CREATE CLUSTERED INDEX idx_ID_SalesAmount ON Orders(ID, SalesAmount) GO درج دیتا -- Insert a few records into the Lookup Table INSERT INTO Country VALUES(0, 'Austria') INSERT INTO Country VALUES(1, 'UK') INSERT INTO Country VALUES(2, 'France') GO -- Insert uneven distributed order data INSERT INTO Orders VALUES(0, 0) DECLARE @i INT = 1 WHILE @i <= 1000 BEGIN INSERT INTO Orders VALUES (1, @i) SET @i += 1 END GO حصول اطمینان از بروز بودن آمار -- Update the Statistics on both tables UPDATE STATISTICS Country WITH FULLSCAN UPDATE STATISTICS Orders WITH FULLSCAN GO کوئری تستی SELECT SalesAmount FROM Country INNER JOIN Orders ON Country.ID = Orders.ID WHERE Name = 'UK' OPTION ( RECOMPILE,-- Used to see the Statistics Output QUERYTRACEON 3604,-- Redirects the output to SSMS QUERYTRACEON 9204-- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded") ) GO وقتی به پلن اجرا نگاه می کنیم متوجه یک مشکل بزرگ در تخمین Cardinality می شویم:تخمین SQL Server به تعداد ۵۰۱ ردیف است و در واقعیت ۱۰۰۰ ردیف برگشت داده شده است. SQL Server با مراجعه به Density Vector آمار ایندکس idx_ID_SalesAmount محاسبه را انجام داده: چون فقط ۲ مقدار یونیک داریم پس Density Vector برابر با ۰.۵ می شود و ۱۰۰۱ * ۰.۵ = ۵۰۱. وقتی همان کوئری را به شرط Austria اجرا کنیم، باز هم تخمین برابر ۵۰۱ است و کوئری ۱ رکورد بر می گرداند. این تخمین اشتباه تاثیر بدی در پلن اجرا دارد چون درخواست حافظه برای اجرا توسط هر عملگر در پلن اجرا و انتخاب نوع عملگر مستقیماً با توجه به تعداد ورودی های آن عملگر صورت می گیرد. در چنین سناریوی خاصی می توانید با Filtered Statistics به تخمین کمک کنید: -- Fix the problem by creating Filtered Statistics Objects CREATE STATISTICS Country_UK ON Country(ID) WHERE Name = 'UK' CREATE STATISTICS Country_Austria ON Country(ID) WHERE Name = 'Austria' GO با اجرای مجدد کوئری با ایجاد Filtered Statistics همه چیز تمام نمی شود. باید حواستان به نگهداری از آمار باشد چون SQL Server با تغییر ۲۰% از دیتای شما آمار را بروز می کند. یعنی اگر جدولی با ۱۰۰۰۰ رکورد داشته باشید باید ۲۰۰۰ تغییر در جدول اتفاق بیفتد تا آمار شما اعم از Filtered Statistics یا عادی بروز شود. امیدوارم مفید واقع شده باشد. چه رتبه ای میدهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش برچسب ها # Cardinality# Filtered Statistics# Performance# SQL Server# آموزش SQL Server معرفی نویسنده مقالات 17 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تورج عزیزی پروفایل نویسنده معرفی محصول مسعود طاهری دوره آموزشی Performance Tuning در SQL Server (گروه 15) ثبت نام حضوری8.700.000 تومانثبت نام غیرحضوری5.700.000 تومان مقالات مرتبط ۱۰ اردیبهشت SQL Server استفاده از Credential و Proxy در SQL Server Agent حسن سلیمانی ۰۷ اردیبهشت SQL Server استفاده از Operator ها در SQL Server Agent حسن سلیمانی ۰۵ اردیبهشت SQL Server بررسی نحوه ایجاد Job در SQL Server حسن سلیمانی ۲۹ فروردین SQL Server آشنایی با بخش های مختلف SQL Server Agent حسن سلیمانی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ مصطفي زماني ۲۴ / ۰۴ / ۹۵ - ۰۹:۰۵ سلام با تشکر از مقاله سودمند شما به نظر میرسد بهروز نگه داشتن آمار کار مشکلی است. به ویژه در شرایطی که تعداد رکوردهای جدول بالا باشد و تغییرات پارامتر ورودی نیز متعدد است. پاسخ به دیدگاه مصطفي زماني ۲۴ / ۰۴ / ۹۵ - ۰۹:۰۵ سلام با تشکر از مقاله سودمند شما به نظر میرسد بهروز نگه داشتن آمار کار مشکلی است. به ویژه در شرایطی که تعداد رکوردهای جدول بالا باشد و تغییرات پارامتر ورودی نیز متعدد است. پاسخ به دیدگاه فرشید علی اکبری ۲۱ / ۰۴ / ۹۵ - ۰۱:۴۸ سلام مطلب بسیار مفید وکاربردی خوبی بود. تشکر. پاسخ به دیدگاه فرشید علی اکبری ۲۱ / ۰۴ / ۹۵ - ۰۱:۴۸ سلام مطلب بسیار مفید وکاربردی خوبی بود. تشکر. پاسخ به دیدگاه تورج عزیزی ۱۵ / ۰۴ / ۹۵ - ۰۷:۵۳ سلام توی مقالات پیش نیاز توضیح دادممنظور selectivity اون ستونی که آمار به ازای اون ساخته شده است که به شکل ۱ تقسیم بر تعداد مقادیر یونیک تعریف میشه و Optimizer با این مقدار می تونه بفهمه که چند درصد از رکوردهای جدول یک شرط با مقدار بخصوص (مقداری از مقادیر این ستون) رو ارضا می کنه.Hope this helps. پاسخ به دیدگاه تورج عزیزی ۱۵ / ۰۴ / ۹۵ - ۰۷:۵۳ سلام توی مقالات پیش نیاز توضیح دادممنظور selectivity اون ستونی که آمار به ازای اون ساخته شده است که به شکل ۱ تقسیم بر تعداد مقادیر یونیک تعریف میشه و Optimizer با این مقدار می تونه بفهمه که چند درصد از رکوردهای جدول یک شرط با مقدار بخصوص (مقداری از مقادیر این ستون) رو ارضا می کنه.Hope this helps. پاسخ به دیدگاه seyedmahdi ۱۴ / ۰۴ / ۹۵ - ۱۱:۰۵ با سلام و تشکر از مقاله خوبتونمن منظورتونو از density vector متوجه نشدم میشه کمی بیشتر توضیح بدید؟ پاسخ به دیدگاه seyedmahdi ۱۴ / ۰۴ / ۹۵ - ۱۱:۰۵ با سلام و تشکر از مقاله خوبتونمن منظورتونو از density vector متوجه نشدم میشه کمی بیشتر توضیح بدید؟ پاسخ به دیدگاه m.abbasi.72@gmail.com ۱۳ / ۰۴ / ۹۵ - ۰۱:۳۰ بسیار عالی. دنبال این مقاله میگشتم.مشکل من مرتفع شد پاسخ به دیدگاه m.abbasi.72@gmail.com ۱۳ / ۰۴ / ۹۵ - ۰۱:۳۰ بسیار عالی. دنبال این مقاله میگشتم.مشکل من مرتفع شد پاسخ به دیدگاه