Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Comprehensive index information

Status
Not open for further replies.

coughe

Programmer
May 15, 2007
3
CA
I need to gather all the applicable options and their values that could have been used on an index during creation/alteration.

Does anyone know what system tables may hold the information concerning the following create index options:
1) ONLINE
2) MAXDOP
3) STATISTICS_NORECOMPUTE
4) DROP EXISTING
 
What version of SQL Server are you using?

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
I'm using SQL Server 2000 and 2005 (so the Online and Max degree of parallelism for 2005 only)

-Greg
 
Here are some helpful queries for 2005.
Code:
declare @dbid int
select @dbid = db_id()

select objectname=object_name(s.object_id), indexname=i.name, i.index_id
            , reads=range_scan_count + singleton_lookup_count
            , 'leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count
            , 'leaf_page_splits' = leaf_allocation_count
            , 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
            , 'nonleaf_page_splits' = nonleaf_allocation_count
from sys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) s,
                        sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by reads desc, leaf_writes, nonleaf_writes

SELECT *
FROM sys.dm_db_index_usage_stats

SELECT * 
FROM sys.indexes

select objectname=object_name(s.object_id), indexname=i.name, i.index_id
                        ,reads=user_seeks + user_scans + user_lookups
                        ,writes =  user_updates
from sys.dm_db_index_usage_stats s,
            sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and s.object_id = i.object_id
and i.index_id = s.index_id
and s.database_id = @dbid
order by reads desc

For 2000.
you can just select * from sysindexes and sysindexkeys


- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top