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.