1

I know that in SO is lot of this kind of topics, but would like to introduce to my concrete example.

When running action from web app (ADO.NET, ASP.NET MVC) getting error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server. The wait operation timed out

But from the second hand when running directly on db raw query (captured by profiler) taking only 2 seconds.

Background:

  1. Issue appear suddenly (week after release) and occurring only in prod server.
  2. Timeout for this specific query is 180 sec, for http requests is 24000 [units] - probably also seconds (per MSDN)
  3. We have no full text indexes
  4. Error occurring only for one specific query when user search by one "fretext" (normal textbox) field what generating the query with additional 10-12 "or like %value%" conditions (the way how it was done from the begin, and have no chance to change it)
  5. What is strange very similar queries (for others "freetext" fields with the same amount of "or like %value%") working without any problems
  6. Transaction isolation level = Serialized
  7. Data binding is done in the same way for each query (controller->service->db->service->view model->html), actually our framework automatically binding fields from "select" query to viewModels
  8. Raw query (captured by profiler) running quickly (2-3 secounds) but from webApp level returning timeout.
  9. I have no direct access to prod server, so im not able to check everything immediately, also i can not share any code/stacktraces.

Advices:

Found lot similar posts, this one looks especially interesting for me: https://stackoverflow.com/a/8603111, and wondering about clearing statistics (exec sp_updatestats).

Question:

Do you have similar experiences, maybe any others advices as suggested above? What can be the reason that raw query running in short time, but from webapp getting timeout?

Community
  • 1
  • 1
user2987281
  • 200
  • 1
  • 14

1 Answers1

2

This is most likely caused by different execution plans cached for different options.

Have a look at these questions:

Stored procedure is executing with different indexes when called via Entity Framework compared to SSMS

and

Why is some sql query much slower when used with SqlCommand?

Community
  • 1
  • 1
ManOnAMission
  • 1,023
  • 1
  • 12
  • 31
  • Very useful links, i have to check! BTW what can be the "quck fix" - have possibility do only some operations in DB (in short perspective), do you think exec sp_updatestats can help? – user2987281 Oct 02 '15 at 07:20
  • @user2987281 Yes, updating the statistics will help. If it's e.g. a stored procedure that you call, a sp_recompile will also help. By the way: You can also use the Profiler to Monitor execution plans, so you can see the bad and the good execution plan. But this slows the db down, so only do it in testing Environments. – ManOnAMission Oct 02 '15 at 07:31
  • Thanks for tip - we dont have stored procedures - everything going via plain text queries via ADO.NET. There is another one thing: wondering how long can take update statistics operation - our db is rather small - have ~1GB size and about 50 tables with rather small number of records (biggest 4 tables have about 130 000 rows, rest tables much less than 100 000). I heard that update statistics can also cause some performance issues so i think i will stop entire application for a while and after that operation will run again. In the local server this operation has taken 8 seconds. – user2987281 Oct 02 '15 at 08:03
  • Problem is solved: I have rebuilded indexes plus did exec sp_updatestats, Dont know which of that things had biggest performance influence. If someone is interested updatestats for our db has take 15 seconds. Thank you very much for your help, and have a good day :) – user2987281 Oct 02 '15 at 15:52