Debugging inconsistent results from C# WebMethod in IIS

Question

I have a simple webmethod that looks a bit like this:

[WebMethod]
public static object getData(string id, string type, string[] filters)
{    
    //do some validation of inputs
    //execute a stored procedure and process with LINQ

    return new { Result = "OK", Records = results, TotalRecordCount = unpagedTotal };
}

When I run this in debug mode connected to my database it consistently returns the same data for the same inputs, as you would expect. When this is run in production the Records and TotalRecordCount in my JSON is sometimes different for exactly the same inputs. There are no determinable patterns that dictate when the numbers change - but frequently they do to all different values. The only observation I can make about the values is that TotalRecordCount never exceeds the actual record count - when the value changes it's always to a lower number.

There are no INSERT statements in the stored procedures. This is for reading data only. I have run SQL Server Profiler on the server and confirm that there are no queries running that could alter the count of the records. This is not a transactional system, it's more of a read-only reporting database that gets rebuilt once a day.

I think I have isolated the issue to IIS, because all of the following tests pass:

Rule out SQL Server

1. Run the stored procedure 100 times in a loop and check outputs
2. Repeat above for 2 SSMS login sessions using the same SQL login

Rule out JavaScript / Front-end

1. In Chrome DevTools inspect: Network > Request > Headers. Check the Request Payload is correct.
2. In Chrome DevTools inspect: Network > Response - number of records displayed on page match (the erroneous) number of records in response

Rule out Application Layer / C#

1. Open solution in Visual Studio
2. Connect application to production DB
3. Run in debug mode - unable to replicate the error after many runs. TotalRecordCount is consistent.

Rule out Network

1. Check that the response coming back from server is a well-formed JSON object (stringified)
2. Compare Request Payload against @params in the stored procedure (using SQL Profiler)

What's left? IIS. I don't know much about IIS.

What I want to know is... is there some setting(s) in IIS that could manifest this bizarre behaviour? Have I missed anything? Does anyone have a clue what's going on?

I've just deployed this application and am having a brain meltdown after looking at this for several hours. All help appreciated!

SQL Code

As requested, the stored procedure looks a little like this:

ALTER PROCEDURE [SO].[getData]

 @ID bigint
 @Type string

WITH ENCRYPTION
AS
BEGIN                

DECLARE @resultsTable
as table (   
col1 nvarchar(100)
col2 nvarchar(100)
)

--Add test data
INSERT INTO @resultsTable
EXEC [SO].[someSP1] @ID @Type
INSERT INTO @resultsTable
EXEC [SO].[someSP2] @ID @Type
INSERT INTO @resultsTable
EXEC [SO].[someSP3] @ID @Type
INSERT INTO @resultsTable
EXEC [SO].[someSP4] @ID @Type

-- Returned combined dataset
SELECT * FROM @resultsTable

END

each of the someSP procedures have the same signature as this parent one. As mentioned above this works fine if I run it hundreds of times from 2 instances of SSMS connected using the same SQL Login to SQL server (e.g. emulating an application)

I cannot post someSP as they are huge and sensitive. The options I have are:

WITH ENCRYPTION SET NOCOUNT ON;

Answer 1

For a complete answer I'll add my comment from above: One thing you can test to see if you can reproduce locally is pull up the site in 2 browsers and hit it at the same time. The reason it could be inconsistent in production is because it's a static method and multiple people are hitting it at the same time. I don't know for sure that this is your issue but it sounds like it. Try using a lock to force one request to wait for another.

[WebMethod]
public static object getData(string id, string type, string[] filters)
{    
    //do some validation of inputs
    //execute a stored procedure and process with LINQ

    var _lock = new object();

    lock(_lock)
    {
        return new { Result = "OK", Records = results, TotalRecordCount = unpagedTotal };
    }
}