1

I have a SQL Server database in production and it has been live for 2 months. A while ago the web application associated with it loading takes too long time. And sometimes it says timeout occurred.

Found a quick fix by running a command 'exec sp_updatestats' will fixed the problem. But I need to be run that one consistently (for every 5 minutes).

So I created a Windows service with timer and started on server. My question is what are the root causes and possible permanent solutions? Anyone?

Here is a Most expensive query from Activity Monitor 

WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-2eea594b-f994-43be-a5ed-d9a47837a391]), TIMEOUT @p2;
Ananth
  • 87
  • 1
  • 13
  • i dont think it is good idea to run this sp every 5 minutes. If this is the right solution for your case, I would run it once per day on asp.net preapplication start event instead, when the app pool recycle. – Houssam Hamdan May 10 '17 at 08:35
  • 1
    `root causes` - lack of proper database design and maintenance? Irrelevant indices, wrongly set up [statistics](https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics)? And unrelated, but there is already the Windows task scheduler and SQL Agent that are designed specifically to run tasks at certain schedule. – GSerg May 10 '17 at 08:36
  • 1
    I also recommend you to see https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html. His maintenance sql scripts were always the right tool to me. – Houssam Hamdan May 10 '17 at 08:38
  • 1
    Updating statistics is not a fix, rather it is a compulsory task need to done on every OLTP databases. It will increase the SQL Server Engine efficiency in choosing the correct execution plan. I recommend you need to do it on daily basis,not for every 5 mins. Use SQL Agent or Windows Task scheduler to do this Job on daily usually at off peak hours of application. – Shakeer Mirza May 10 '17 at 08:43
  • thanks for the ideas . I believe already done proper indexing ... Updated code above – Ananth May 10 '17 at 10:53
  • 2
    It's not an expensive query. It's a process waiting for messages to appear. If there are no messages it's going to keep waiting. Are you judging that it's expensive simply by the time elapsed? – GSerg May 10 '17 at 10:59

2 Answers2

0

To diagnose a poorly performing queries you need to:

  • Identify the poorly performing query, e.g. via application logging, a SQL Profiler trace filtered to show only queries with a longer duration than a certain threshold etc...
  • Get an execution plan for the query

At that point you can start to try to figure out what the performance issue is.

Given that exec sp_updatestats fixes your issue it could be that statistics on certain tables are out of date (this is a pretty common cause of performance issues). If thats the case then you might be able to tweak your statistics or at least rebuild only those statistics that are causing issues.

Its worth noting that updating statistics will also cause cached execution plans to become invalid, and so its plausible that your issue is unrelated to statistics - you need to collect more information about the poorly performing queries before looking at solutions.

Your most expensive query looks like its waiting for a message, i.e. its in your list of long running queries because its designed to run for a long time, not because its expensive.

Community
  • 1
  • 1
Justin
  • 84,773
  • 49
  • 224
  • 367
0

Thanks for everyone i found a solution for my issue . Its quite different I've enabled sql dependency module on my sql server by setting up enable broker on , thats the one causing timeout query so by setting it to false everything is fine working now.

Ananth
  • 87
  • 1
  • 13