SQL Server Performance

this is related to tuning of a query

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by srikanthamk, Feb 4, 2010.

  1. srikanthamk New Member

    hi,
    i have a question related to tuning of a query.. yesterday the query was running good , but today it was running very slow wat could be the reason? and what is the permanent solution for that not to repeat this issue again in future?
    thanks and regards
    srikanth
  2. preethi Member

    Hi Sri Kanth,
    There could be many reasons for this situation:
    • There could be some other query/queries running in the environment which is blocking the resources your query needs
    • The parameters you have passed could be different and based on the parameters the amount of data to be processed could be different.
    • A high percentage of data got uploaded / modified between these two incidents and they change the query plan, or statistics became outdated suddenly
    Actually there is no solution until you identify the root cause of the problem :)

  3. satya Moderator

    Sri
    You have just expressed an open question without having further details about that query, database environment.
    To start with a sample, the query optimizer uses statistics to create query plans that improve query performance. For most queries, the query optimizer already generates the necessary statistics for a high quality query plan; in a few cases, you need to create additional statistics or modify the query design for best results.
    Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan. For example, the query optimizer could use cardinality estimates to choose the index seek operator instead of the more resource-intensive index scan operator, and in doing so improve query performance.
    This is just a starter to know..... there is much more to it, so you get to use details for more leads to solve the issue.
  4. rohit2900 Member

    Sri,The temp work around to fix this issue is to try updating the statistics of the under laying tables and if doesn't improve then you might need to try defrag or rebuild all the indexes for these tables based on the current status of the indexes but for a permanent fix, It will be good idea if you post the query along with the table structures & index definition.

Share This Page