SQL Server Performance

Slow Stored Procedure Execution Through Management Studio

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

  1. jonesri New Member

    Hi All,
    I am attempting to tune a stored procedure that is very slow when executed through an application. The problem is that when I attempt to run the stored procedure through management studio it does not return at all (or at least I have not been patient enough to see it return - it has been running for at least 20 mins). Has anyone seen this before?
    I have used SQL Profiler to catch the stored procedure call from the application so I know I am running the same stored procedure, with the same parameters in both cases.
    Checking activity monitor it appears that the stored procedure call through the application uses parallelism (or at least I see several waits indicating it is using parallelism) but when I use management studio I do not get the same waits etc.
    Any ideas?
    Thanks
    Rich
  2. ranjitjain New Member

    Hi,
    Check the estimated/actual execution plan if scans are present and which part of the query consuming most of the cpu time. SQL server uses parallelism when it's feasible but you can supress it for individual query using query hint MAXDOP 1 which will enforce server to execute task serially. You can also run the load in db tuning advisor to get suggestions over indexes.
  3. jonesri New Member

    Thanks for that. I have no problem with SQL using parallelism to execute this stored procedure. My problem is that it seems to use parallelism when executing the stored procedure through the website, but not through management studio.
    I have got the estimated execution plan, but am struggling to get the actual execution plan as I can't get the stored procedure to complete through management studio.
    I have also tried using the tuning advisor without success.
    Rich

Share This Page