locked
Performance Tuning/Testing RRS feed

  • Question

  • I have a list of MS SQL Server Stored Procedures. I need to do Performance Tuning/Testing on this ie track the performance issue and get the automated solution, then generate a report. How to do performance tuning/testing against this List of SQL Server Stored Procedures ? I need to generate a report of each Stored Procedure, where the report shows the Execution Plan and also methods for improvising the performance which is retrieved automatically. I have never done this before
    Tuesday, February 21, 2012 5:27 AM

Answers

  • It will be more or less manual process. There are many methods /tools which you can use like DTA.

    You need to do a code review, identify the potential code issue,

    do a index analysis including Clustered index/fillfactor etc identify a optimized index structure.

    Get a proper workload from production and use DTA to tune that workload.

    Update the statististics with proper sampling.


    MCITP, MCTS, MCDBA,MCP

    • Proposed as answer by Warwick Rudd Tuesday, February 21, 2012 7:45 PM
    • Marked as answer by Iric Wen Tuesday, February 28, 2012 8:19 AM
    Tuesday, February 21, 2012 3:45 PM

All replies

  • You can make use of DMVs to get the details of execution statistics and the plan. Refer and pick relevant ones from here and here. Prepare load testing environment, and execute the procedures as per the business logic with different test cases and then retrieve data from the Dynamic Management views. Once you get the execution statistics, you can take a call on how to tune the procedures. Let me know if you need any further help on this.

    Regards,


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

    Tuesday, February 21, 2012 12:44 PM
  • It will be more or less manual process. There are many methods /tools which you can use like DTA.

    You need to do a code review, identify the potential code issue,

    do a index analysis including Clustered index/fillfactor etc identify a optimized index structure.

    Get a proper workload from production and use DTA to tune that workload.

    Update the statististics with proper sampling.


    MCITP, MCTS, MCDBA,MCP

    • Proposed as answer by Warwick Rudd Tuesday, February 21, 2012 7:45 PM
    • Marked as answer by Iric Wen Tuesday, February 28, 2012 8:19 AM
    Tuesday, February 21, 2012 3:45 PM