SQL Server Performance

URGENT HELP

Discussion in 'T-SQL Performance Tuning for Developers' started by pattu1, Aug 21, 2003.

  1. pattu1 New Member

    Hello Gurus
    I have a problem with my production server.
    I have a sql query is running on development box it is taking 19 sec , same query is taking 6min on PRODUCTION box .
    I did UPDATE STATS and DBCC DBREINDEX .SAME INDEXs IN DEVELOPMENT and production.
    Thanks
    Srini




  2. satya Moderator

    There may be difference in the user base, network dependancy between development and production.
    Check whether any issues with network on production, PROFILER may help you some extent to resolve the slow execution.

    And posting the sample code will help to assess.
    (more or less this relates to Development, moved to Developer forum)


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. gaurav_bindlish New Member

    Did you check the execution plan of the query in production and test? Are thet the same?

    Have you eliminated blocking as one of the issues for performance problem?

    What is the difference in the data being handled in this case? Are you running the query on the same kind of data. If not restore the backup from production to one of your test servers and check if the query still executes in les time.

    I agree with Satya on the use of Profiler to get a feel of what;s happeneing at production. What are the hardware differences in production and test boxes? You can use system monitor ro observe the overall activity as well.

    Is there a difference in service pack for the two servers?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  4. pattu1 New Member

    quote:Originally posted by gaurav_bindlish

    Did you check the execution plan of the query in production and test? Are thet the same?

    Have you eliminated blocking as one of the issues for performance problem?

    What is the difference in the data being handled in this case? Are you running the query on the same kind of data. If not restore the backup from production to one of your test servers and check if the query still executes in les time.

    I agree with Satya on the use of Profiler to get a feel of what;s happeneing at production. What are the hardware differences in production and test boxes? You can use system monitor ro observe the overall activity as well.

    Is there a difference in service pack for the two servers?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard



    Actually this report was running fine until yestarday suddenly it is behaving very badly..

    There is nothing going on server from Task manger output. This is very highend server dual processer with 1gb ram.
    Any other suggestion
  5. bambola New Member

    I can only second what Satya and Gaurav already suggested. Check your execution plan running the query from QA, and open a trace. It should give you a better idea of what is happening. If after that you still cannot figure out the problem, come back with what you have found there and we might be able to help more.

    Bambola.
  6. gaurav_bindlish New Member

    Look for parallel execution of query in the execution plan. If you find one, try running the query using MAXDOP option to 1 thereby limiting the query to run on a single processor and see if performance improves.

    Did you see slow down in all the queries or just this query? If all, check SQL Server Log and NT event log for any harware / database related issues.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  7. pattu1 New Member

    quote:Originally posted by gaurav_bindlish

    Look for parallel execution of query in the execution plan. If you find one, try running the query using MAXDOP option to 1 thereby limiting the query to run on a single processor and see if performance improves.

    Did you see slow down in all the queries or just this query? If all, check SQL Server Log and NT event log for any harware / database related issues.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    I ran the query with execution plan . Both exec plans looks "different".
    I tried thru profiler SQL statement is working for 6min then SQL batch completed.


  8. pattu1 New Member

    quote:Originally posted by gaurav_bindlish

    Look for parallel execution of query in the execution plan. If you find one, try running the query using MAXDOP option to 1 thereby limiting the query to run on a single processor and see if performance improves.

    Did you see slow down in all the queries or just this query? If all, check SQL Server Log and NT event log for any harware / database related issues.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard


    Hi Gaurav,
    It's really workedout hunderds of thanks....
    I took out one processor from the sql server and it worked.
    Can you explain me in detail?
  9. gaurav_bindlish New Member

    So now we know that the execution plans have changed.

    Check the point where execution plan looks different and update the statistics again. Recompile the stored procedure (Just alter it once and it will recompile next time it is executed) and see if the old execution plan is restored.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  10. gaurav_bindlish New Member

    Wait a minute did you take out a processor from the query or the SQL Server? I hope its query but if its the other was you can use the MAXDOP hint in your query to limit running the same on single processor and let other processes and queries take advantage of the multi processing power.

    So, so its a typical problem of query behaving weirdly when the query is processed on multiple processors. This happens when the actually the cost of executing a query on single processor is cheaper than the cost of running the same on multiple processors but the query optimizer thinks the other way.

    This may happen due to a couple of reasons. I think the most important being change in the workload. If the execution plan of the query was compiled when there was very less load on the system, the plan may decide to run the query on multiple processors. But when the load on the system increases, running the query on multiple processors may not be the optimal solution. The only solution that comes to my mind which can avoid this problem is to create the procedure using WITH RECOMPILE option. This way every time the query is executed, new plan will be generated and so the dependency on previous plan will be eliminated.

    So I won't recommend putting this singlr processor solution permanently on the server. This way u'll lose the performance advantage of running the query on multiple processors. Hoever if you are happy with the performance, that's fine.

    Cheers,

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  11. satya Moderator

  12. pattu1 New Member

Share This Page