SQL Server Performance

Debug long running queries on production boxes

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sqlNovice, Aug 15, 2008.

  1. sqlNovice New Member

    Hi All,
    Is there any best practice/ appproach for debugging the qureries which are taking long time to execute in Production enviroment, I know that we can use Profile but it will have an effect on SQL server performance right ?
    Appreciate any pointers on this.
    Regards
  2. preethi Member

    This is what I would suggest:
    1. test the following in the development environment
    2. Make sure it is not adhoc query. It should be a stored procedure.
    3. Determine the frequency of call. Understand it does not make too many round trips nor use of too many resources. You need to stike a balance.
    4. Static Code analysis:The code does not use any worst performance offending operations. (UNION (instead of UNION ALL), DISTINCT, ORDER BY, SELECT * are some of them)
    5. Get the execution plan. Check whether the plan is using expensive operations. Instead of graphical execution plan go for text mode (or XML mode). I will go for text mode as it is easy to understand and gives the information I need. Check for no of executes, physical operations, sub tree cost and hierarchy. If you can't eliminate the expensive costly operations, try to move to a different node (by re-writing the query) so that the total sub tree cost will be less.
    6. In production, take the reads, writes, duration and total no of execution of the stored procedure and identify the worst offending stored procedures. Now filter them and get the execution plan for them only
  3. venkatesanj@hcl.in New Member

    Hi Friend,
    Its advisable to use profiler to trace the sql server execution. Even if it affects the performance.. thats the only right way to check your production server.
    During peak hours, take the trace for half an hour and you can analyse the exact problem.
    Regards,
    Venkatesan Prabu .J

Share This Page