2

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.

SQL Police
  • 4,127
  • 1
  • 25
  • 54
  • google or bing "parameter sniffing" to see if that is a possible issue. – granadaCoder Oct 11 '13 at 13:56
  • @granadaCoder Interesting article, but do you think this is related to calls via RPC ? The probem only arises when the procedure is called via RPC. If called via SQL (in SQL Studio), then it always works.To us, it looks like there is something happening in the RPC component (like a buffer overflow or faulty queue processing), but we have no clue where to look. – SQL Police Oct 12 '13 at 14:15

2 Answers2

1

Problem solved -- it was a fairly complex function, and compiling the execution plan took a lot of time. I simplified the procedure by removing a lot of conditional branches (if, case) and splitting the procedure into multiple sub-procedures.

SQL Police
  • 4,127
  • 1
  • 25
  • 54
  • I am having a similar issue at this moment. I have a stored procedure which takes about 4 seconds (to remove 80.000 records) when I run it in SQL Server Management Studio. However, when I run it from C# (in Visual Studio, my web service class), it has a timeout. I see no way to simplify my query (stored procedure) or split it (it finds duplicates via a left outer join/min(id)/group statement, pretty big). Any ideas? – Tjab Apr 06 '16 at 12:17
  • Fixed! I needed to add `WITH RECOMPILE` to my procedure. I didn't help for some people, but it works with my problem. Tested with the deletion of 300.000 records, took about 15 seconds (where 80.000 records took 4 seconds in SSMS). http://stackoverflow.com/questions/4831085/sql-server-effects-of-using-with-recompile-in-proc-definition – Tjab Apr 06 '16 at 12:33
  • 1
    @Tjab When you create a stored procedure, SQL Server creates a so-called *query plan*, which is based on statistics over your data. Normally this plan is only created only once, when the procedure is being created. Now it can happen that your data has changed in such a way that the query plan does not fit the data anymore. In this case, the option `WITH RECOMPILE` forces sql server to refresh the query plan every time the stored procedure is executed. – SQL Police Apr 06 '16 at 14:17
  • 1
    @Tjab BTW, when you are deleting masses of data, then the indexes on your table can slow down the deletion. It can help to temporarily disable indexes and re-create. Depends on your data structure, of course. – SQL Police Apr 06 '16 at 14:19
  • I'll have to look into that I guess. I do not understand enough about databases to answer some questions like; I have over 390 million records of data in my database, the index space is at 55 GB. When I disable and re-create the indexes, won't it crash hard (or at least take ages to do so?)? – Tjab Apr 07 '16 at 05:51
  • Well, in that case I would not recommend it. It will probably take longer to rebuild the index as the time you win for deleting the records. But perhaps you should check which of the indexes you really need. You could also use the SQL Server *Database Engine Tuning Advisor* to check if your indexes are properly set. – SQL Police Apr 07 '16 at 06:36
0

.NET applications uses predefined (default) command timeout values. This duration is most probably not enough for some of your commands and queries. You may try to define your custom command timeout in your application.

I think you use a C# application as mentioned. Following definition in a constructor of your database context (EF code-first approach) may solve your issue.

public MyDBContext()
      : base("MyDB")
  {
      ((IObjectContextAdapter)this).ObjectContext.CommandTimeout = 300;
  }

I've been using this definition in my C# based applications together with EF that executes sql server stored procedures.

Good luck!

aog
  • 484
  • 3
  • 14
  • thank you, I was also looking for a method to prolong the timeout. But in our case, we are very sure that we have another problem. As said, the procedure always takes about 1 second. Only sometimes, when called via RPC, some part of the server gets 'stuck', and then it needs forever. This happens only via RPC calls, in SQL Studio it always worked. Also, the Profiler says that numer of reads on disk are normal, only CPU goes up forever. To us, this looks like some queue processing on RPC calls goes into a loop or else, but we have no clue where to look – SQL Police Oct 12 '13 at 13:39