SQL Server Statistics-related commands

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