SQL Server Performance

after rebuild

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by kumarp, Dec 13, 2007.

  1. kumarp New Member

    hi ,

    i am having some doubts regarding rebuild and reorgainze indexes.
    after run the rebuild and reorganize,
    i run this smt . for avg_page_space_used_in_percent value is null for all indexes and for some tables when i run alter rebuild statements the output is not varying same as previous ones.

    SELECT distinct a.index_id, name, avg_fragmentation_in_percent,avg_fragment_size_in_pages,avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'etrans'),
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

    is it compulsory to run update statitics after running rebuild and reorganize?

    after done rebuild and reorganize also users are facing still the performance is slow for that application.

    pls help someone .

    thanks ,
    kumar p

  2. satya Moderator

    Are you rebuilding all the indexes on the database or only selected (or required)?
    Also are you performing any shrink operations on the database?
    You said performance issues what kind of performance issues, slow running queries or slow response from datab ase?
  3. kumarp New Member

    hi , thanks for reply
    i rebuilled all indexes and i didn't perform any shirnking
    while selecting ,updating the any things in the application the performance is slow
    i don't known excatly slow response or slow running queries
    whn i checked in sql profiler while updataing or selecting long queries it will take some time
  4. satya Moderator

  5. satya Moderator

    Check the 2 blogs above and also perform a thorough health check on the hardware of the server, ensure no cables are loose on the server room and also check network related components.
  6. kumarp New Member

    thanks satya ,
    i'll check ,
    we have so maany application running present , but only this application running slow
  7. techbabu303 New Member

    If you have rebuild the indexes using the DBCC DBREINDEX then update statistics is done automatically.
    Couple of troublehooting paths to application slow down
    1> Did you recently increase number of users ?
    2> Please check if there are blocks when the users are complaining application slow down , you can use "select * from sysprocesses where blocked >0 and spid >51"
    3> Please check out similar posts in forums for application slow down since there are many here that can help you which are elborated well.
  8. kumarp New Member

    hi , thanks for reply babu
    i didn't increase any users. when i run the select stmt no blocking process are there .
    i rebuild the indexes using alter index .............. cmd

Share This Page