SQL Transaction Performance Issue

Posted on 2011-10-31
Last Modified: 2012-05-12

I am currently facing SQL transaction performance issue (on execution) and unable to trace what causing it.

In the SQL Profiler, I can see that one of the SQL Stored Proc take about 7-8 minutes to complete (which is not good).

Can someone please direct me on how I can/should dig deeper to find what causing the bottleneck? What metrics/parameters should i look at?

    LVL 5

    Accepted Solution

    can you use table that have  huge number of records in selection or joins or used in function that is called in your query?
    One more issue can there, check is there any self join on table that have too many records?


    LVL 50

    Assisted Solution

    ok you've identified a problem procedure...

    now use profiler to see which statements within the procedure actually account for the bulk of the time...
    LVL 6

    Author Comment

    by:Senior IT System Engineer
    how to do that from the SSMS ?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now