SQL Server 2008 - Stored Procedure Gets Stuck When Called From WPF Service Using a SqlDataAdapter

Question

This has me pulling my hair out. We have a workflow, hosted as a WCF service, which makes a call to another WCF service which then calls a stored procedure. Store procedure calls a merge, then iterates through a cursor that calls another sproc. The cursor count is the same as the source count in the merge. If the source count is high (~120k), the sproc never returns. Disk activity and CPU utilization is nill, and memory is not being taxed. If I then call the sproc from SSMS, it completes in about an hour.

We are using a SQLDataAdapter to make the actual call. Is the SDA somehow receiving updates on every cursor iteration and then failing, causing SQL to stall as it waits? Or is something else going on?

I bring up the SDA because I spent the early part of the week tracking down the cause of the workflow faulting, and it turned out it was a repeated ANSI warning message being returned to the SDA and causing an out of memory exception. Which leads me to wonder if there is something else going on under the cover here, causing the problem.

Answer 1

I seriously doubt the WCF host is the appropriate environment to hold a database call for one hour duration... I recommend you have a look at Asynchronous procedure execution which allows you to make long database calls from and ASP/WCF process in a reliable fashion, and call let the HTTP call return back to the caller.

Answer 2

It's hard to tell what your code is actually doing, but you mention that your stored proc is using cursors and that your stored proc is slow. I write my stored procs to use temporary tables, and I iterate over the temporary tables as the following example indicates. This made my procs run in seconds to minutes when they use to take minutes to hours when I used cursors. If you are using cursors and profiling indicates the cursor code in your stored proc is slow give temporary tables a try.

You'll never got back to cursors.

-- create a temporary table
DECLARE @FrontDeskRows table (
    id int,
    Arrival datetime,
    Departure datetime,
    CheckedIn int,
    OwnerID varchar(50),
    GuestID varchar(50),
    [LName] varchar (256),
    [FName] varchar (256),
    [Address] varchar (256),
    [City] varchar (256),
    [State] varchar (256),
    [Zip] varchar (256),
    [phone] varchar (256),
    [Status] int )

-- load your temporary table
INSERT INTO @FrontDeskRows
  SELECT  id,
      Arrival,
      Departure,
      CheckedIn,
      OwnerID,
      GuestID,
      [LName],
      [FName],
      [Address],
      [City],
      [State],
      [Zip],
      [phone],
      [Status]
  FROM FrontDesk
  ORDER BY Id ASC

DECLARE @arrival as DateTime
DECLARE @departure as DateTime
DECLARE @id as int

-- loop over the temprary table
SELECT @id = (SELECT MIN(id) FROM @FrontDeskRows)
SELECT @arrival = Arrival FROM @FrontDeskRows Where id = @id
SELECT @departure = Departure FROM @FrontDeskRows Where id = @id
  WHILE @id IS NOT NULL
   BEGIN

    -- PROCESS EACH ROW HERE

    -- get the next item in the temporary table
    SELECT @id = (SELECT MIN(id) FROM @FrontDeskRows WHERE id > @id)
    SELECT @arrival = Arrival FROM @FrontDeskRows Where id = @id
    SELECT @departure = Departure FROM @FrontDeskRows Where id = @id

   END

Answer 3

Turns out it was a very bad case of parameter sniffing. Very, very bad case...