SQL-DBA-01 (4/19/2015)
Eirikur,Can you suggest how to use sample percent and other hints using sp_updatestats??????????....???????
The table which has billions of records do u suggest running sp_updatestats???????????????????????????????????????
Again reiterating the suggestion of using Ola Hallengren's SQL Server Index and Statistics Maintenance
😎
To answer your question, have a look at sys.dm_db_index_physical_stats and sys.dm_db_index_operational_stats.
USE [MY_DB_NAME];
GO
SET NOCOUNT ON;
SELECT
IPS.database_id
,IPS.object_id
,IPS.index_id
,IPS.partition_number
,IPS.index_type_desc
,IPS.alloc_unit_type_desc
,IPS.index_depth
,IPS.index_level
,IPS.avg_fragmentation_in_percent
,IPS.fragment_count
,IPS.avg_fragment_size_in_pages
,IPS.page_count
,IPS.avg_page_space_used_in_percent
,IPS.record_count
,IPS.ghost_record_count
,IPS.version_ghost_record_count
,IPS.min_record_size_in_bytes
,IPS.max_record_size_in_bytes
,IPS.avg_record_size_in_bytes
,IPS.forwarded_record_count
,IPS.compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'SCHEMA_NAME.TABLE_NAME'),1,NULL,NULL) IPS;
SELECT
IOS.database_id
,IOS.object_id
,IOS.index_id
,IOS.partition_number
,IOS.leaf_insert_count
,IOS.leaf_delete_count
,IOS.leaf_update_count
,IOS.leaf_ghost_count
,IOS.nonleaf_insert_count
,IOS.nonleaf_delete_count
,IOS.nonleaf_update_count
,IOS.leaf_allocation_count
,IOS.nonleaf_allocation_count
,IOS.leaf_page_merge_count
,IOS.nonleaf_page_merge_count
,IOS.range_scan_count
,IOS.singleton_lookup_count
,IOS.forwarded_fetch_count
,IOS.lob_fetch_in_pages
,IOS.lob_fetch_in_bytes
,IOS.lob_orphan_create_count
,IOS.lob_orphan_insert_count
,IOS.row_overflow_fetch_in_pages
,IOS.row_overflow_fetch_in_bytes
,IOS.column_value_push_off_row_count
,IOS.column_value_pull_in_row_count
,IOS.row_lock_count
,IOS.row_lock_wait_count
,IOS.row_lock_wait_in_ms
,IOS.page_lock_count
,IOS.page_lock_wait_count
,IOS.page_lock_wait_in_ms
,IOS.index_lock_promotion_attempt_count
,IOS.index_lock_promotion_count
,IOS.page_latch_wait_count
,IOS.page_latch_wait_in_ms
,IOS.page_io_latch_wait_count
,IOS.page_io_latch_wait_in_ms
,IOS.tree_page_latch_wait_count
,IOS.tree_page_latch_wait_in_ms
,IOS.tree_page_io_latch_wait_count
,IOS.tree_page_io_latch_wait_in_ms
,IOS.page_compression_attempt_count
,IOS.page_compression_success_count
FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID(N'SCHEMA_NAME.TABLE_NAME'),1,NULL) IOS;