3

Every few months a few select pages of a website will start responding with

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I ran SQL Server Profiler to see where the database is getting hung up. It's on a specific stored procedure. So I took the stored procedure call and ran it through Management Studio. Returns a few thousand rows in under a second.

Opening up the procedure, I see it's just a simple select statement. If I take the select statement out and run it in a new query window, Management Studio will hang.

So I start running it in pieces, and find the line that's causing the query to hang

and GetDate() between EffectiveDate and ISNULL(ExpiryDate, @CurrentDate)

GetDate() is supposed to be @CurrentDate anyways, so if I switch it, it'll run fine. Why does this even matter though? As I understand, GetDate should not be an expensive call. I understand IsNull is, but that shouldn't matter either, as it can handle the query fine if I just swap out GetDate with CurrentDate.

Am I correct in assuming getdate is causing this query to hang intermittently? How/why would it do that?

A bit of background, as I said this error has popped up before, on March 23rd, July 27th, October 25th and now November 21st. It always seems to happen near the end of the month. I've been assured nothing is going on during this time (scheduled tasks, maintenance, etc.)

This is on a production server, so I haven't been able to actually take the time to debug the problem. As soon as the site goes down, it has to be brought back up. I know how to fix it, I just need to force a recompile on the stored procedure, but does anyone have any idea what could be causing this?

The SQL Server also has another UAT database, the same procedure will hang on that database as well. But other stored procedures that have that exact same line of code run fine on both databases.

Edit: I had to put the website back up, so I recompiled the stored procedure. The site now runs fine. The query still doesn't run on it's own though.

Brandon
  • 68,708
  • 30
  • 194
  • 223
  • 3
    I would look at the execution plan for both versions. I suspect they are doing two differnt things. MY guess might be that it is having to run row by row to do the comparision to the function and not when using @currentdata. It hangs at the end of the month because there is more data then. – HLGEM Nov 21 '11 at 21:06
  • @HLGEM, thanks for the suggestion for the query plans. The queries themselves would be relatively unaffected by when they're actually run. – Brandon Nov 21 '11 at 21:10
  • @HLGEM, how do I look at the execution plan if the query never completes? – Brandon Nov 21 '11 at 21:35

1 Answers1

0

I was mistaken to the cause of the issue. The query I was running in pieces was basically:

select *
from Item
left outer join category Subcategories on Subcategories.ItemKey = Item.Id,
Categories Category,
Availability
where Item.Id = @ItemId
  and GetDate() between Availability.EffectiveDate 
      and ISNULL(Availability.ExpiryDate, @CurrentDate)

This obviously is not the query being run in production, just an anonymized example.

This query would not run. To get it to run I originally commented out

and GetDate() between EffectiveDate and ISNULL(ExpiryDate, @CurrentDate)

It would also run if I changed GetDate() to @CurrentDate. I was sure GetDate was the problem. However, when I ran the query and commented out the Availablity line it worked fine. Availability is a massive table. I ran a re-index and a statistics update and everything works fine. I guess I owe GetDate an apology.

Edit: This happened again on a completely different environment. Again GetDate appears to have been the culprit. Changing that line and running the procedure got everything to work again. GetDate may not be to blame as the root cause, but it's definitely the first sign that something is going wrong.

Brandon
  • 68,708
  • 30
  • 194
  • 223
  • You have two pretty nasty cross-joins going on with Categories and Availability - and you mention Availability is a massive table. – HardCode Jan 04 '12 at 21:56
  • @HardCode, I know, I've been trying to "fix" these issues. However, the most recent occurrence of this issue was not in a complicated procedure or even against a large table. This was a simple select statement that caused the problem. – Brandon Jan 04 '12 at 22:01