We have a very strange problem:
We have a stored procedure which returns a set of data. This procedure does a rather complex join and aggregation, so it takes 700 Milliseconds to execute.
When called directly in SQL Studio, the procedure ALWAYS returns correcty and always takes about 700 ms of time.
However, when called via a Client software (C# or Excel), then it works for a while, but then suddenly, the procedure takes 30 seconds (!) and gets a timeout. The number of disk reads is normal, but CPU is going up.
This behaviour goes on for 1-2 hours, and then it is normal again ! During this time, when the procedure show this behaviour, you can still execute the procedure in SQL Studio. Also, other and more simple procedures also get normally executed when called in the client.
I checked the calls with the profiler. When calling via a client, the call is marked as "RPC", when called in SQL Studio, it is "SQL"
So probably the procedure executes normal, but the data transmission via RPC somehow is hanging. But this is only a guess.
Does anyone have a idea, or can point us in some direction? I don't know where to look.
Also the Event log is empty.