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 derfloh 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
Joined
May 15, 2007
Messages
3
Location
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