Post reply

Performance problem?

  • June 11, 2005 at 8:12 pm

    #110898

    Hello all,

    I'm new dba, at my work they have one application is very slow in afternoon time, i ran Perfmon and find out there is couple big table causing problem,

    Q1. My question is how can i find which stored procedure causing the problem and if i find out which stored procedure then how can i resolve that issue.

     

    Q2. When i use system stored procedure to find out about locks there is some table has TABLE,PAGE and Trasaction lock type , which one is dangerous for performance issue and how can i solve than problem.

    Thanks

     

    Andy

     

  • June 12, 2005 at 9:20 am

    #564899

    Andy there is no straight answer to your question. SP: Recomple event tells u if the stored proc is compiled more than once, which causes performance problems. SP should always be called with the owner name e.g. dbo.sp_dosomething

    U will have to look at the SQL query plans to see how u can tune them - table scans should be minimal and table seeks are better. How many pages are read into memory - there are lots of things to consider in perf tuning.

     

  • June 12, 2005 at 9:21 am

    #564900

    And about locks - they are normal. Its the time for which the locks are held determines if system performs good or bad. Read BOL

  • June 13, 2005 at 7:16 am

    #564961

    Can you send us the execution plan so that we may give more pointers?

  • June 13, 2005 at 8:40 pm

    #565160

    other than PerMon, you can create a profiler and let it run for 2 hours (during peak hour) to check what sp is actually running. however, this may slow down your system in that period.

    I think it is ok to suffer for 2 hours to collect all neccessary data rather than you spend time on other stuffs, which won't give you any info.

    after that, you should be able to identify which sp is causing problem. so, you can move to next stpe, which is fine tune that sp based on execution plan.

    hope this may help.

    Leo Leong 

  • June 13, 2005 at 11:11 pm

    #565168

    Maybe I should start another thread... but is there a way to start a trace with the profiler, and save the execution plans at the same time?

  • June 13, 2005 at 11:59 pm

    #565171

    you can include execution plan under category [Performance].

    of course, here are other features as well.

  • June 14, 2005 at 6:50 am

    #565262

    Thanx, how about the input parameters??

    Sorry to keep asking questions. My thaughts process is a little slow atm (starting to imagine how many new ways to solve problems this can give me ).

  • June 15, 2005 at 2:07 am

    #565571

    Profiler don't need to pass in parameter.

    Just open Enterprise Manager --> Tools --> SQL Profiler

    Then, create a new trace.

    I would suggest you to use the template trace there.

  • June 15, 2005 at 9:30 am

    #565745

    I was wondering if it was possible to see the values of the input parameters of the RPC in the trace.

  • June 15, 2005 at 8:12 pm

    #565959

    Would you like to include SQL:BatchComplete?

    May be there is other option for you to check that. : )

    Leo

  • June 16, 2005 at 7:29 am

    #566071

    Thanx for the info... Had a little fun by starting a trace with pretty much everything except the security and locks audit .

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply