How to find Index size in Sql 2000

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » How to find Index size in Sql 2000

How to find Index size in Sql 2000

Rate Topic

Display Mode

Topic Options

Author

Message

lallu_jaya

Posted Tuesday, September 28, 2010 6:39 PM

Valued Member

Group: General Forum Members
Last Login: Friday, December 28, 2012 7:54 AM
Points: 51, Visits: 165

Hi,

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 http://support.microsoft.com/kb/894257

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,
Jaya

Post #994881

GilaMonster

Posted Tuesday, September 28, 2010 11:50 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:30 AM
Points: 37,734, Visits: 30,000

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 2008, MVP
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 »

Permissions