SQL And Statistics

最新推荐文章于 2024-04-19 10:23:56 发布

envykok

最新推荐文章于 2024-04-19 10:23:56 发布

阅读量136

点赞数

分类专栏： SQL Basic Knowledge 文章标签： statistics sql sql server database object table

本文链接：https://blog.csdn.net/envykok/article/details/6072132

版权

SQL Basic Knowledge 专栏收录该内容

46 篇文章 0 订阅

订阅专栏

/****** Object: Index [LISTINGFEED_ID_LISTING_IDUN]    Script Date: 12/10/2010 22:22:40 ******/
statistic
helping the optimizer
cost base
    -    sys.stats
    -    DBCC SHOW_STATISTICS(table_name,statsname)

Database auto-generate statistics will be single column only
Database Tuning Advisor(DTA) will provide multiple - columns

Diable statistics will make plan stablility
-    consistent plan is more important than a better plan

Update statistics - > UPDATE STATISTICS WITH [FULLSCAN][RESAMPLE]
                    ->sp_updatestats[RESAMPLE]
                    ->rebuild - > fullscan


index fragementation/statistics...

1.If there is no statistics, then SQL optimizer will guess a number
2.Execution plan
    - estimated subtree cost :
3.DATABASE LEVEL
-    AUTO CREATE STATISTICS
    - NAME : _WA_Sys_*
-    AUTO UPDATE

If estimated subtree cost >5 then sql server will pick up parallelism

sys.dm_exec_cached_plans