SQL Server Performance

SQL performance

Discussion in 'Getting Started' started by Siddu, Aug 13, 2007.

  1. Siddu New Member

    Hi All,
    This is my first post in this forum. I am facing a problem regarding performance of SQL server 2000.
    From application (front end as PERL), I am trying to generate reports with selected fields. Because of this kind of functionality, query is becoming huge with so many join statements. Once I run this big query, the entire application slows down.
    I tried indexes. In index tuning wizard the performance improvance is coming as 0%.
    Kindly help me in any of the following:
    1) performane of query should become more that in less time results should appear.
    or
    2) when the big query is runnning for remaining queries, it should not affect the performance.
    Thanks in advance.
  2. Luis Martin Moderator

    Using QA run:
    set statistics io on
    your query
    set statistics io off
    Post the results please.
    Also, are you using NOLOCK in this query?
  3. Siddu New Member

    Hi Luis Martin,
    I have done in the suiggested way following are the results:
    Table 'Worktable'. Scan count 15570, logical reads 31139, physical reads 0, read-ahead reads 0.
    Table 'project_status'. Scan count 1, logical reads 366, physical reads 0, read-ahead reads 161.
    Table 'Worktable'. Scan count 15570, logical reads 31139, physical reads 0, read-ahead reads 0.
    Table 'bc_summary'. Scan count 1, logical reads 391, physical reads 0, read-ahead reads 59.
    Table 'Worktable'. Scan count 12357, logical reads 24719, physical reads 0, read-ahead reads 0.
    Table 'action_log'. Scan count 2, logical reads 8568, physical reads 0, read-ahead reads 1838.
    Table 'Worktable'. Scan count 15567, logical reads 31136, physical reads 0, read-ahead reads 0.
    Table 'project_update'. Scan count 2, logical reads 7574, physical reads 200, read-ahead reads 7214.
    Table 'project_state'. Scan count 7954, logical reads 17675, physical reads 0, read-ahead reads 0.
    Table 'project'. Scan count 2, logical reads 9372, physical reads 1044, read-ahead reads 1232.
    Table 'milestone'. Scan count 102584, logical reads 205170, physical reads 0, read-ahead reads 0.
    Table 'project_milestone'. Scan count 23855, logical reads 75541, physical reads 0, read-ahead reads 0.
    Table 'phase'. Scan count 7951, logical reads 7951, physical reads 0, read-ahead reads 0.
    Table 'state'. Scan count 7951, logical reads 15902, physical reads 0, read-ahead reads 0.
    Table 'work_standard'. Scan count 1, logical reads 317, physical reads 0, read-ahead reads 123.
    Table 'opo_user'. Scan count 1, logical reads 929, physical reads 2, read-ahead reads 12.
    Table 'project_member'. Scan count 1, logical reads 599, physical reads 0, read-ahead reads 0.
    Table 'role'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 3.
    Table 'project_custbill_paymnt'. Scan count 1, logical reads 428, physical reads 0, read-ahead reads 420.
    Table 'benefit_type'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.
    Table 'complete'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.
    Table 'itn_category'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 2.

    I am not using any NOLOCK in the query
    Thanks in advance.
  4. satya Moderator

    Welcome to the forum!
    Are you performing the queries from the client's application?
    WHy not take help of stored procedures that will be in database and use the same from application, so that process can be handled on server end that will reduce pingback the results for each and every process during this operation.
    Also it will bebetter to check the execution plan of those SQL queries on SQL Server side with a profiler trace, then use INDEX TUNING WIZARD for a recommendation on the indexes.
  5. Siddu New Member

    The query which we are going to execute in the application varies based on the user's selection.
    IN a worst case scenario, the current query leads to maximum of 67 LEFT OUTER JOIN statements
    1) If I run this kind of SQL query inside stored procedure, will it be helping me in any way?
    2) Which of the following do you all feel will be a better approach?
    a. splitting the queries and using the concept of temporary table or directly running a single query containing JOIN statements in stored procedure.
    Thanks in advance.
  6. sunilmadan New Member

    Seems to be one of the reporting queries with joins depending on user selection. I have few suggestions for you:
    • Try to eliminate any function calls / expressions from left hand side of the where clause expression and transfer to right side instead
    • Try to create a view for a non-dynamic part of the query
    • In where clause, use more selective clause first
    You may post the query here for more in-depth analysis and suggestions.
  7. satya Moderator

    Though running with SPs will have better performance, but you have to look at corresponding indexes too with these joins, though you are saying taking help of TEMPDB is fine. But you have to monitor how tempdb is coping with these processes, TEMPDBDMVs fyi in this regard.
  8. Siddu New Member

    Hi Satya,
    Thank you very much for the idea of executing query in stored procedure.
    This has reduced the processing time to 1/3rd.
    Query which was taking 6 minutes earlies is now taking around 2 minutes. Thank you so much.
    Could you please help me to reduce the processing time still more.
    Thanks in advance.
  9. martins New Member

    Hi,
    Using the "with (nolock)" hint in your queries will definitely help as well. Give it a try and see if it improves performance.
  10. Siddu New Member

    Hi Luis Martin,
    I have tried executing query using with nolock. I didn't find any difference in the execution time.
    Thanks in advance.
  11. Luis Martin Moderator

    To me you have index problems in:
    'action_log', 'project_update' and 'project'.
    Try to find better indexes.
  12. satya Moderator

    If you are using that hint within a stored procedure, have you recompiled the plan for that SP?
  13. satya Moderator

    Probably you can look at the execution plan to see what indexes they are using, also keepup the statistics & indexes upto date for optimum performance.

Share This Page