در SQL Server، Statistics یک مجموعه اطلاعات آماری در مورد توزیع دادهها در یک ستون یا مجموعه ستونها است. این آمارها به بهینهسازی کوئریها کمک میکنند و به SQL Server اطلاعات لازم را درباره توزیع دادهها و میزان تکرار مقادیر فراهم میکنند.
Statistics در SQL Server به صورت خودکار یا دستی ایجاد میشوند. زمانی که یک جدول ایجاد میشود یا اطلاعات در آن تغییر میکند (به عنوان مثال، با عملیات INSERT، UPDATE، DELETE)، SQL Server ممکن است به صورت خودکار Statistics ایجاد یا بهروزرسانی کند.
Statistics شامل اطلاعاتی نظیر:
- Histogram (هیستوگرام):
– نموداری از توزیع مقادیر در یک ستون، که به بهینهسازی برخی از عملیاتهای جستجو و فیلترینگ در کوئریها کمک میکند.
- Density Vector (بردار تراکم):
– اطلاعاتی درباره تراکم دادهها در ستون.
- اطلاعات تعداد رکوردها و تعداد رکوردهای یکتا:
– تعداد کل رکوردها و تعداد رکوردهای یکتا در ستون.
اطلاعات آماری موجود در Statistics به بهینهسازی برنامههای اجرایی کمک میکنند تا تصمیمات بهتری درباره نحوه اجرای کوئریها بگیرند. درخواستهایی که از اطلاعات آماری بهرهمند هستند، ممکن است سریعتر و با بهترین عملکرد اجرا شوند.
Statistics معمولاً در داخل پایگاه داده در یک سطح جدول (Table) قرار دارند و با دستورات Transact-SQL میتوانید آنها را مشاهده و مدیریت کنید.
Statistics در SQL Server برای بهینهسازی کوئریها و تصمیمات بهتر در مورد اجرای عملیاتهای جستجو، فیلترینگ، و ترتیب دادهها استفاده میشوند. این آمارها اطلاعاتی درباره توزیع دادهها در یک ستون را فراهم میکنند و به بهینهسازی برنامههای اجرایی کمک میکنند. در ادامه، کاربردهای اصلی Statistics در SQL Server را بررسی میکنیم:
1. بهینهسازی جستجو:
– زمانی که یک کوئری جستجوی داده انجام میشود، بهینهساز کوئری ممکن است از Statistics برای تخمین تعداد رکوردها و توزیع دادهها در ستونها استفاده کند. این اطلاعات به بهینهسازی اجرای کوئری و انتخاب بهترین نقطهای برای شروع جستجو کمک میکنند.
2. فیلترینگ بهتر:
– هنگام استفاده از شروط WHERE در کوئریها، بهینهساز از Statistics برای تصمیم بهتر درباره نحوه اعمال شرایط فیلترینگ استفاده میکند. این اطلاعات میزان تکرار مقادیر مختلف را نشان میدهند و این امکان را میدهند که بهینهساز برای اجرای کوئری از راههای موثرتری برای اعمال فیلترینگ استفاده کند.
3. ترتیب دادهها:
– زمانی که در کوئریها از دستور ORDER BY برای ترتیب دادهها استفاده میشود، Statistics به بهینهساز اطلاعاتی ارائه میدهند تا بتواند تصمیم بگیرد کدام متغیر برای ترتیب دادهها مناسبتر است و نقطههای بهینه برای انجام عملیات ترتیببندی را انتخاب کند.
4. بهینهسازی JOIN:
– هنگام استفاده از JOIN در کوئریها، Statistics برای تخمین تعداد رکوردها در جدولهای مختلف استفاده میشوند. این اطلاعات به بهینهساز کمک میکنند تا تصمیمات مناسب برای انجام JOINها و ادغام دادهها را بگیرد.
5. انتخاب مناسبترین نقطه برای اجرای Index Seek یا Index Scan:
– زمانی که یک نوع جستجوی Index (Index Seek یا Index Scan) اجرا میشود، Statistics به بهینهساز اطلاعات در مورد توزیع دادهها کمک میکنند تا نقطهای که اجرای بهینهتری را فراهم میکند را انتخاب کند.
Statistics (آمارها) در مفهوم پایگاه داده به اطلاعات آماری درباره توزیع دادهها در یک ستون یا گروه از ستونها اشاره دارد. در زیر چند تعریف ساده در مورد Statistics در پایگاه داده SQL Server آورده شده است:
- آمارها (Statistics) در SQL Server:
– Statistics به دادههای آماری در مورد توزیع دادهها در یک ستون یا گروه از ستونها اشاره دارند. این اطلاعات به بهینهسازی کوئریها و انتخاب بهترین راه برای اجرای آنها کمک میکنند.
- اطلاعات توزیع مقادیر:
– Statistics اطلاعاتی را فراهم میکنند که نشان دهنده توزیع مقادیر مختلف در یک ستون است. این اطلاعات به بهینهسازی جستجوها و فیلترینگ در کوئریها کمک میکنند.
- استفاده در بهینهسازی کوئریها:
– Statistics در بهینهسازی کوئریها نقش اساسی دارند. اطلاعات جمعآوری شده توسط Statistics به بهینهساز امکان میدهد تا تصمیمات مناسب در مورد نحوه اجرای کوئریها و استفاده از ایندکسها و عملیاتهای دیگر اجرایی گرفته شود.
- تاثیر بر کارایی جستجو:
– Statistics تاثیر مستقیم بر کارایی عملیات جستجو در پایگاه داده دارند. این اطلاعات به بهینهساز کمک میکنند تا تصمیمات موثرتری در مورد نحوه اجرای عملیاتهای جستجو گرفته شود.
- استفاده از Histogram و Density Vector:
– Statistics شامل اطلاعاتی هستند نظیر Histogram (هیستوگرام) که توزیع مقادیر را به صورت نموداری نشان میدهد و Density Vector که اطلاعات درباره تراکم دادهها در ستون را فراهم میکند.
این تعریفات سادهترین نگاه به Statistics در SQL Server هستند. در کل، Statistics اطلاعات مهمی برای بهینهسازی کوئریها و افزایش کارایی پایگاه داده فراهم میکنند.
Statistics در SQL Server ممکن است به صورت خودکار ایجاد شوند یا شما میتوانید آنها را به صورت دستی ایجاد کنید. در اینجا نحوه ایجاد Statistics به صورت دستی و خودکار آورده شده است:
ایجاد Statistics به صورت دستی:
برای ایجاد Statistics به صورت دستی بر روی یک ستون میتوانید از دستور `CREATE STATISTICS` استفاده کنید.
CREATE STATISTICS stats_ColumnName
ON TableName (ColumnName);
– `stats_ColumnName`: نامی که به Statistics میدهید.
– `TableName`: نام جدول مربوطه.
– `ColumnName`: نام ستون بر روی آن میخواهید Statistics ایجاد شود.
ایجاد Statistics به صورت خودکار:
در اکثر مواقع، SQL Server به صورت خودکار Statistics ایجاد میکند. این اتفاق زمانی رخ میدهد که:
1. یک Index بر روی ستونی ایجاد شود.
2. مقدار AUTO_CREATE_STATISTICS در SQL Server فعال باشد.
برای اطمینان از وضعیت این تنظیمات میتوانید از دستور زیر استفاده کنید:
— بررسی وضعیت AUTO_CREATE_STATISTICS
EXEC sp_autostats ‘YourTableName’;
اگر AUTO_CREATE_STATISTICS غیرفعال باشد، میتوانید آن را با دستور زیر فعال کنید:
— فعال کردن AUTO_CREATE_STATISTICS
ALTER DATABASE YourDatabaseName SET AUTO_CREATE_STATISTICS ON;
اگر میخواهید Statistics را برای یک Index خاص ایجاد کنید، میتوانید از دستور `CREATE STATISTICS` با استفاده از نام Index استفاده کنید.
CREATE STATISTICS stats_IndexName
ON TableName
([IndexName]);
– `stats_IndexName`: نامی که به Statistics میدهید.
– `TableName`: نام جدول مربوطه.
– `IndexName`: نام Index بر روی آن میخواهید Statistics ایجاد شود.
توجه داشته باشید که برای اطمینان از بهروز بودن Statistics، ممکن است نیاز به بهروزرسانی دستی یا استفاده از jobهای اتوماتیک باشد.
مشکلات عدم بروز رسانی Statistics ها در SQL Server :
عدم بهروزرسانی Statistics در SQL Server میتواند منجر به مشکلات کارایی در اجرای کوئریها شود. زمانی که Statistics بهروز نشوند، بهینهساز کوئری ممکن است اطلاعات آماری ناکافی یا قدیمی داشته باشد، که موجب انتخاب نادرست ایندکسها و تصمیمات ناصحیح در اجرای کوئریها میشود. در ادامه مشکلات عدم بهروزرسانی Statistics و راهحلهای ممکن برای حل آنها را میبینید:
- مشکل: عدم بهروزرسانی Statistics به صورت خودکار:
– راهحل: مطمئن شوید که تنظیم AUTO_CREATE_STATISTICS و AUTO_UPDATE_STATISTICS در SQL Server فعال باشد. این تنظیمات مسئول ایجاد و بهروزرسانی Statistics به صورت خودکار هستند.
— بررسی وضعیت AUTO_CREATE_STATISTICS
EXEC sp_autostats ‘YourTableName’;
— فعال کردن AUTO_CREATE_STATISTICS
ALTER DATABASE YourDatabaseName SET AUTO_CREATE_STATISTICS ON;
— بررسی وضعیت AUTO_UPDATE_STATISTICS
EXEC sp_autostats ‘YourTableName’;
— فعال کردن AUTO_UPDATE_STATISTICS
ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS ON;
- مشکل: آمارها به دلیل حجم بالای دادهها بهروز نمیشوند:
– راهحل: در صورت که دیتابیس حاوی حجم بالای دادههاست و بهروزرسانی Statistics زمانبر است، ممکن است نیاز باشد به صورت دستی آمارها را بهروز کنید.
— بهروزرسانی Statistics به صورت دستی
UPDATE STATISTICS YourTableName;
- مشکل: تنظیمات AUTO_UPDATE_STATISTICS_ASYNC فعال است:
– راهحل: در صورتی که تنظیم AUTO_UPDATE_STATISTICS_ASYNC فعال است (وظیفه بهروزرسانی آمارها به صورت غیرهمزمان انجام شود)، ممکن است بهینهساز برای بهروزرسانی Statistics به مدت طولانی صبر کند. در این صورت، این تنظیم را غیرفعال کنید.
— غیرفعال کردن AUTO_UPDATE_STATISTICS_ASYNC
ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
- مشکل: تغییرات زیاد در دادهها وجود دارد:
– راهحل: در صورتی که دادهها به طور فزاینده تغییر میکنند، ممکن است نیاز باشد به صورت متداول Statistics را بهروز کنید یا از وظیفههای اتوماتیک SQL Server برای بهروزرسانی مکرر آنها استفاده کنید.
- مشکل: عدم انتخاب بهینهترین ایندکس:
– راهحل: در صورتی که بهینهساز ایندکسها به درستی انجام نمیشود، ممکن است نیاز باشد به صورت دستی Statistics را با استفاده از دستور `UPDATE STATISTICS` بر روی جداول مربوطه بهروز کنید.
— بهروزرسانی Statistics به صورت دستی
UPDATE STATISTICS YourTableName;
با رعایت این راهحلها، مشکلات عدم بهروزرسانی Statistics را میتوانید حل کنید و به بهینهسازی عملکرد پایگاه داده خود بپردازید.
روشهای بهروزرسانی Statistics در sql server
Statistics در SQL Server میتوانند به صورت خودکار یا دستی بهروزرسانی شوند. در زیر، روشهای مختلف برای بهروزرسانی Statistics در SQL Server آورده شدهاند:
1. بهروزرسانی Statistics به صورت خودکار:
1.1. خودکار در زمان اجرای کوئری:
– SQL Server به صورت خودکار Statistics را در زمان اجرای کوئریها بهروز میکند.
1.2. خودکار در زمان اجرای کوئری با استفاده از Jobها:
– میتوانید Jobهای SQL Server را برای بهروزرسانی Statistics ایجاد کنید و این Jobها را با زمانبندی منظم اجرا کنید.
2. بهروزرسانی Statistics به صورت دستی:
2.1. بهروزرسانی همه Statistics یک جدول:
— بهروزرسانی همه Statistics یک جدول
UPDATE STATISTICS YourTableName;
2.2بهروزرسانی یک یا چند ستون خاص در یک جدول
— بهروزرسانی یک یا چند ستون خاص در یک جدول
UPDATE STATISTICS YourTableName (Column1, Column2);
2.3. بهروزرسانی یک یا چند Statistics خاص:
— بهروزرسانی یک یا چند Statistics خاص
UPDATE STATISTICS YourTableName YourStatisticsName;
3. استفاده از Trace Flag 2371:
از Trace Flag 2371 میتوانید برای کنترل رفتار بهروزرسانی Statistics در SQL Server استفاده کنید. این Trace Flag از SQL Server 2016 به بعد به صورت پیشفرض فعال است و نیازی به فعالسازی آن نیست. این Trace Flag ممکن است در برخی موارد کمک کننده باشد تا عملیات بهروزرسانی Statistics بهینهتر انجام شود.
نکته مهم:
– هنگامی که بهروزرسانی Statistics به صورت دستی انجام میشود، باید توجه داشته باشید که این عملیات ممکن است به منظور کنترل کارایی و زمان بندی مناسب اجرا شود. بهطور مثال، اگر جدول دادههای بزرگ دارید، بهروزرسانی Statistics بر روی تمام ستونها ممکن است زمانبر باشد.
با توجه به نیازها و مشخصات محیط شما، میتوانید یک یا ترکیبی از روشهای بالا را برای بهروزرسانی Statistics در SQL Server انتخاب کنید.
دیدگاه ها 2