0

I have a query that never finishes (takes more that 2 hours) when I run it at 11am, and takes 1 min when I run it at 7pm. So obviously the execution plan changes during the day. What can cause that?

The data does not change. Auto stats are turned off, the stats and indexes get rebuild over night.

I run it in the client's environment, so I can't really tell what else uses the same SQL server. So this is my main concern, I reckon that the server gets so loaded by something else that it can't cope with my query.

At what parameters of the server shall I look to find the cause of the problem?

Where else should I look for a problem? What other factors shall I consider?

Any help will be highly appreciated.

UPDATE: Forgot to mention that I have a whole bunch of other big queries running at the same time (11am) and they all run fine. And actually I have 7 queries that run fine in the evening and don't finish in the morning. They all use the same view which joins quite large tables and that is the only difference between the queries that fail and the queries that don't. So I wonder if SQL server does not have enough memory or something to save the intermediate results of the view execution, and that is why the query never finishes.

So what parameters of the server shall I monitor to find the problem?

UPDATE And unfortunately I don't have the execution plan of the morning run due to the permissions

UPDATE I don't think the the table locks is the cause as I know what's using my database on the server and I can run the same query at say 12pm when nothing else is running from my side (i.e. should be no locks and uncommitted transactions on my tables) and the query takes the same awful amount of time.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
IgorShch
  • 149
  • 1
  • 4
  • 21
  • Do a sp_who2 to check for slowdowns. – Mihai Feb 25 '14 at 11:47
  • 2
    `obviously the execution plan changes` you should verify this by pulling those at least 2 different execution plans, maybe your database is simply `REALLY BUSY` at 11am – DrCopyPaste Feb 25 '14 at 11:48
  • "So obviously the execution plan changes during the day.". No? What could change is the server load (but it´s unlikely that this will cause such a difference), or there are some crappy programs which are misusing locks. – deviantfan Feb 25 '14 at 11:48
  • If you are talking about a query (and _not_ a stored procedure) you cannot foresee if the execution plan is same or different, it will depend on many factors, including the available resources. That said, I would suggest you investigate the existing locks when the query stalls. – Gerardo Lima Feb 25 '14 at 11:51
  • @drCopyPaste Can't pull out the morning execution plan due to the lack of permissions and the fact that the query never finsishes. How can I determine that the server is "REALLY BUSY" Thank you – IgorShch Feb 25 '14 at 12:00
  • Can you get access to TaskManager on the machine? Check the basics of CPU and RAM first - if at 11AM your machine is using 100% of the CPU, that's the obvious bottleneck. – Neville Kuyt Feb 25 '14 at 12:01
  • @IgorShch well can you then start that query manually again and tell your environment to include the actual execution plan taken? If you do not have permissions to do that either I'd ask the responsible dba to give them to you or look into it himself. – DrCopyPaste Feb 25 '14 at 12:01
  • @IgorShch - If you can't get at the execution plan `due to the permissions`, I assume that you don't have any DBA type privileges? You can't run SQL-Profiler, etc, etc? If so, contact a DBA for support; what you're asking about is specifically a DBA activity, and if you don't have those privileges, you need a DBA to support you. – MatBailie Feb 25 '14 at 12:02
  • @IgorShch try running your query with LEVEL READ UNCOMMITTED, so we can exclude table locks – Rodion Feb 25 '14 at 12:03
  • @MatBailie Thank you for the reply. I already did get the DBA to look at the problem, but unfortunately the problem is not a priority for them and I'm just trying to help. – IgorShch Feb 25 '14 at 12:05
  • @IgorShch - To paraphrase then... `I'm not a DBA, I don't have DBA permissions, and the DBA's are unable to support me. How can I fullfil the DBA role without DBA permissions?` You're going to find that very hard indeed. – MatBailie Feb 25 '14 at 12:06
  • @IgorShch Easy fix: put it aside until it becomes a priority :D (maybe you should tell that to them, too, but just maybe) – DrCopyPaste Feb 25 '14 at 12:06
  • @MatBailie Fully appreaciate your point about permissions. I just hoped somebody can give me an advice on the server parameters and other things to check.. – IgorShch Feb 25 '14 at 12:17
  • You mention that each night it updates stats and rebuilds indexes, why would you rebuild indexes every night. Also, for queries using views... Sometimes queries not using an underlying view can be better optimized without using a view. Maybe you should post other questions based on the failing query and its underlying view. – DRapp Feb 26 '14 at 15:37

1 Answers1

0

Lots of things can impact this. Is this really a query or a stored procedure call? I query is re compiled each call a stored proc uses a cached plan. If the parameters for the stored procedure can provide wildly varying results then you can use the WITH RECOMPILE hint

If you can live with dirty reads I would put:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

at the top of your code. This will allow your query to access data being held in locks by other processes. The risk is that if the data changes your answer may not be 100% correct. If your data is always additive or a point in time value is acceptable then this works really well.

I'd go through the execution plan of the query in the evening and optimise that query even if 1 min is acceptable it may be uploading vast amounts of data which you have capacity for in the evening but are contending for in the morning.

user3573909
  • 1