1. DBCC show_statistics Display the current query optimization statistics for a table or indexed view
Use adventureworks2008r2; GO DBCC show_statistics ("Person.Address", ak_address_rowguid); DBCC with histogram; GO
2. Stats_date returns the date of the latest update to the statistics on the table or indexed view
The ID of the stats_id statistic object.
Use adventureworks2008r2; GO SELECT as Stats_name, stats_date (object_id as statistics_update_datefromWHERE object_id=object_id('person.address'); GO
3. sp_autostats displays or changes the automatic statistics update option auto_update_statistics for indexes, statistics objects, tables, or indexed views.
UseAdventureWorks2012;GO--Displays the status of all statistics for the Product tableEXECSp_autostats'production.product';--Enable all statistics Auto_update_statistics options on the product tableEXECSp_autostats'production.product',' on';--the AK_Product_Name index on the Product table disables the Auto_update_statistics option. EXECSp_autostats'production.product','OFF', AK_Product_Name;GO
4. Update statistical information
Use AdventureWorks2012; GO -- UPDATE STATISTICS for all tables in the library EXEC sp_updatestats Go -- UPDATE STATISTICS for a table UPDATE STATISTICS Sales.SalesOrderDetail; GO -- UPDATE STATISTICS for an index UPDATE STATISTICS Sales.SalesOrderDetail Ak_salesorderdetail_rowguid;
5. View database Statistics settings
SELECT Case whenDATABASEPROPERTYEX ('Dbmonitor','Isautocreatestatistics')= 1 Then 'Yes' ELSE 'No' END as 'Isautocreatestatistics' , Case whenDATABASEPROPERTYEX ('Dbmonitor','IsAutoUpdateStatistics')= 1 Then 'Yes' ELSE 'No' END as 'IsAutoUpdateStatistics' , Case whenDATABASEPROPERTYEX ('Dbmonitor','is_auto_update_stats_async_on')= 1 Then 'Yes' ELSE 'No' END as 'Isautoupdatestatsaayncon' GOSELECTname, is_auto_create_stats_on, is_auto_update_stats_async_on, is_auto_close_on fromsys.databases;
6, see some of the better statistics posts
Http://www.cnblogs.com/CareySon/archive/2012/05/14/HowStatisticImpactQuery.html
Http://www.cnblogs.com/kerrycode/p/3337817.html
SQL Server Statistics-related commands