SQL Server Performance

problem with sys.dm_db_index_physical_stats

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by suvresh, Jan 30, 2008.

  1. suvresh New Member

    hi,
    i am using SQL Server 2005. i have a database DBOneshelp, in that Database there is a table - tblSearch. now i want to see the fragmentation status of that table so execute the following Query:
    SELECT PK_SalesId, avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats
    (DB_ID(N'DBOneshelp'), OBJECT_ID(N'tblSearch'), NULL, NULL , 'Detailed')
    go
    but there is a syntax error:
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near '('.
    can any one tell me what is the error i have done in the above sql statement
    thanks in advance
    regards,
    suvresh
  2. satya Moderator

  3. joetig New Member

    try the following:
    SELECT PK_SalesId, avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats
    (DB_ID('DBOneshelp'), OBJECT_ID('tblSearch'), NULL, NULL , 'Detailed')
  4. suvresh New Member

    still giving same error! not working
  5. joetig New Member

    try select *
  6. gpzucker New Member

    remove the single quotes from around 'Detailed'
  7. comeaugilles New Member

    Just take your database out of SQL 80 compatibility mode and put it in to 90.
  8. suvresh New Member

    thanks comeaugilles
    it is now really working, thankx again.
    regards,
    suvresh
  9. anurag.er@gmail.com New Member

    You can use the following with the compatible level 80
    Declare @db_id smallint
    Declare @tab_id int
    set @db_id=db_id('DBOneshelp')
    set @tab_id=object_id( 'tblSearch')
    SELECT PK_SalesId, avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats
    (@db_id,@tab_id, NULL, NULL , 'Detailed')
    go

Share This Page