به روز رسانی Statistic Data در SQL Server

[supporter]

با سلام،
SQL Server بر چه اساسی Statistic Data مربوط به یک Index و یا Column رو Update می‌کنه و اصولا نیازی به بررسی آخرین تاریخ Upadte شدن این اطلاعات و در صورت نیاز Update کردن دستی اونها هست یا نه؟

[AminSobati]

دوست عزیزم،
هر دیتابیس یک Option داره به اسم Auto Update Statistics و به طور پیش فرض True هستش. یعنی با هر ویرایش شما، Statisticهای مربوطه به روز میشن. چنانچه این Option رو False کردین، باید خودتون بوسیله مثلا Job به روز رسانی رو انجام بدین. زمان به روز رسانی بستگی به میزان حجم ویرایش اطلاعات داره.

[supporter]

جناب ثباتی مشکل من هم دقیقا همینه که ویرایش چه حجمی از اطلاعات و تحت چه شرایطی موجب ویرایش Statistic Data مربوط به جدول مربوطه می‌شه
مشکل اینجاست که در تستی که انجام دادیم به نظر می‌رسه حجم اطلاعات ویرایش شده تنها عامل موثر نیست
به عنوان مثال رکوردهای جدولی با 10000 رکورد 5 بار update کردیم (100 رکورد در هر Upadte) و SQL Server تنها در Update پنجم اطلاعات مربوط به Statistic Data را Update کرد.

و یا در جدولی با تعداد رکورد چند ده میلیونی با وجود آن‌که Auto pdate satatistics هم از ابتدا Set بود تاریخ آخرین به روز رسانی Statistic Data یک Index خاص مربوط به 7 ماه پیش بود و این در حالی بود که روزانه چندین هزار رکورد در این Table، درج و یا ... می شد.

[AminSobati]

در حقیقت چیزی که تصمیم میگیره یک Statistic چه موقع عمل Update شدنش آغاز بشه، Query Optimizer هست. یکی از زمانهایی که Update میتونه آغاز بشه، وقتی هست که Query شما برای جستجو قصد داره از یک Statistic استفاده کنه. اگر از زمان آخرین Update تا لحظه Query، تقریبا 20 درصد از اطلاعات تغییر کرده باشند، Query Optimizer این رو تشخیص میده و دستور Update رو صادر میکنه. این Query ناچاره صبر کنه تا Update تموم بشه. در SQL Server 2005 امکان Asynchronous Statistics Updates وجود داره به این معنی که دستور Update در نوبت قرار میگیره و Query هم به شکل موازی اجرا میشه. در این مثال، بین دستور Update و Select حداقل چند ثانیه فاصله بندازید:


use northwind

select * into od from [order details] -- Make a copy of table

select * from od where orderid=10249 -- Statistic will be created here

update od set orderid=1 -- Statistic will not be updated yet

select * from od where orderid=10249 -- Update occures here

[supporter]

جناب ثباتی ضمن تشکر

من کد شما را تست کردم ولی Statistic Data به روز نشد.

[supporter]

اصولا این سوال هم به هنگام اجرای Query ای شبیه Query زیر برامون پیش اومد




SELECT * FROM Table1
WHERE F1 = 100
AND F2 = 200


در این حالت علی رغم وجود Composite Index روی ستون های F1, F2 باز هم Clustered Index Scan می شد و اجرای Query چند دقیقه طول می کشید در حالی که Query زیر در کمتر از یک ثانیه اجرا می‌شد:



SELECT * FROM Table1 WITH(INDEX(IX_Table1))
WHERE F1= 100
And F2 = 200


بنابراین این سوال پیش اومد که چرا Query Optimazer از این Index استفاده نمی‌کنه؟
بعد متوجه شدیم که تاریخ آخرین به روز رسانی Statistiscs Data این Index مربوط به 7 ماه پیشه. این بود که این سوال برامون پیش اومد که چرا Statistics Data این Index به روز نشده؟
و این که در چنین مواقعی لازمه خودمون این به روز رسانی رو انجام بدیم یا نه ؟
یادآور میشم که این Table چند ده میلیون رکورد داره و روزانه چند هزار تراکنش روی اون انجام میشه و Auto update statistics دیتابیس هم از ابتدا Set بوده.

[AminSobati]

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

1) از کدوم نسخه SQL Server استفاده میکنید؟

2) اگر 2005 استفاده میکنید، آیا دیتابیس رو از 2000 به 2005 ارتقاء دادین؟ (روش ارتقاء Attach بوده یا Restore؟)

3) آیا آخرین Service Pack نصب شده؟

4) وقتی Statistic رو خودتون Update کردین، آیا ایندکس بصورت اتوماتیک مورد استفاده قرار گرفت؟

5) کدوم یک از دو فیلد F1 و F2 به یونیک بودن نزدیک تر هستند؟ منظور این که ایندکس F1,F2 و همچنین F2,F1 (بلعکس) کدومیک در حال حاضر ساخته شده اند.

[supporter]

جناب ثباتی با عرض پوزش
در مورد استفاده نشدن Index مشکل از Query ما بود چون Query اصلی به شکل زیر بود:




SELECT * FROM AccDocDetail
WHERE Branch_Code = 200
AND AccMasterCode = 601
AND AccDetailCode IS NULL
AND Year = 1386



که منجر به ‍Clustered Index Scan می‌شد و اجرای Query چند دقیقه طول می‌کشید. :خجالت:


در هر صورت Query در حالتی که Index مربوط (روی تمام ستون های Where Clause و به همین ترتیبی که قید شده)در بخش From قید می‌شه در کمتر از یک ثانیه اجرا می‌شه.


در این حالت هم این سوال پیش می‌آد که Optimizer نباید این حالت رو تشخیص بده و از Index مورد نظر استفاده کنه؟(حتی اگر آز IS NULL در Where Clause استفاده شده باشه، با توجه به این که سرعت اجرای Query در حالتی که از این Index استفاده می‌کنه قابل مقایسه با حالتی که Clustered Index Scan می‌کنه نیست)
در ضمن پس از این که Statistic این Index رو که مربوط به چند ماه پیش بود (و البته نمودنم چرا Update نشده بود؟) Update کردیم تغییری ایجاد نشد.
در ضمن ما از نسخه 2000 و Service pack 3 استفاده می‌کنیم (اگر چه من کد شما رو روی سیستم خودم با Service pack 4 تست کرده بودم)


در ضمن از اونجایی که من با SQL Server 2005 زیاد کار نکردم خیلی دوست داشتم بدونم جواب سوال‌های دوم و پنجمتون چه تاثیری تو این حالت داره؟

[AminSobati]

جواب اینکه چرا Scan انتخاب میشه برای کسی در موقعیت من کار راحتی نیست! چون پاسخ شما نیاز به بررسی چند موضوع روی این جدول داره (از جمله Exec Plan) که دسترسی بهش ندارم. ولی احتمال میدم Optimizer رکوردهای زیادی در تخمینش بدست میاره و ترجیح میده به جای Bookmark Lookup که برای تعداد رکوردهای کم مناسبه، از Scan استفاده کنه که در بعضی از حالتها سریعتر از Bookmark Lookup عمل میکنه.
این نکته رو به یاد داشته باشید: ایندکسی که همه فیلدهای مورد نیاز یک Query رو تامین میکنه (چه در Where Clasue چه در Select) بسیار مفیده و میتونه جلوی Bookmark Lookup رو بگیره

در خصوص سوال دوم، در مواردی دیده شده که وقتی دیتابیس به روش Attach به SQL Server 2005 منتقل میشه، بعضی از Statisticها به نوعی غیر فعال میشن و باید مجددا Rebuild بشن.
سوال پنجم، ترتیب فیلدها در ایندکس نقش بسیار کلیدی دارند. فیلدی که یونیک تر هستش بهتره اول بیاد، به شرط اینکه این فیلد در Where Clause قید شده باشه.