September 28, 2010 at 6:39 pm
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
September 28, 2010 at 11:50 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply