SQL Server Performance

Query performance

Discussion in 'Performance Tuning for DBAs' started by murali_3000, Dec 1, 2006.

  1. murali_3000 New Member

    There is a query in Producution which was running fine for past 3 years.
    But for the past 2 days it is taking more than 10 minutes. earlier it used to take 2 seconds.
    Copied the production data to Development env. Same problem occured in development env.
    Did a dbcc reindex on the major tables which the query was using. In development env query performance came to 2 seconds
    based upon this analysis ran the dbcc reindex in production , but the still the query takes 10 minutes. Scan density in major tables in production is fine.
    we have weekly reindex jobs on these tables to. The table has more than 5 million records.
    What is the problem going on production ,Can anyone please help on this ASAP?

  2. MohammedU New Member

    Production table is being used by other users all the time and data might be inserting continuously...

    Run the update statistics against the tables involved in the query...
    Run the DBCC showcontig against all tables involved in the query and check the fragmentation.

    Run the query in in QA with SHOW EXECUTION PLAN on and SET STATSIS I/O ON and SET STATISTICS TIME ON to check the what is going on...

    Mohammed U.

Share This Page