Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

How to find Index size in Sql 2000 Expand / Collapse
Posted Tuesday, September 28, 2010 6:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 4, 2016 12:23 PM
Points: 57, Visits: 264

I have an Index seek operation going which is scanning 1900000 rows and returning 1800000 records. I want to find if forcing index scan is better than index seek by comparing page reads & index size. Iam trying to find out the size of index by following query

eclare @IndexId int
set @IndexId = (select index_id from sys.indexes where name = 'idx_datecoveringcol')
select * from sys.dm_db_index_physical_stats (DB_ID(),object_Id ('dbo.Metrics_Test_Specimen_Data),@IndexId, null,'detailed')

I get the following error Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.

I found from Microsoft support that it is problem of Sql 2000 pre service pack 3.
Please refer this link

Is there any way where I could find the index size in my sql 2000. I cannot upgrade the service pack coz that is a company wide decision and not in my hands.

Thank you,
Post #994881
Posted Tuesday, September 28, 2010 11:50 PM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
sys.dm_db_index_physical_stats is a SQL 2005 view, it's not available in SQL 2000.

Use DBCC SHOWCONTIG. As one of the output columns it has 'pages scanned'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #994931
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse