Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
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: Yesterday @ 7:46 AM
Points: 54, Visits: 232
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
Posted Tuesday, September 28, 2010 11:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:19 AM
Points: 42,766, Visits: 35,864
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 »

Add to briefcase

Permissions Expand / Collapse