Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have recently migrated from a Win 2003/SQL Server 2000 system to Win 2008 64 bit R2, SQL Server 2008 R2.

Our websites are in classic asp, and this can't be changed to another scripting language at this time.

On the old server, if I got stuck in some kind of endless loop, the page would throw an error.

On the new server, I have a page that has some sort of looping problem, that even though the SQL SP is called only once (and runs fine run as a query on the server) it pegs SQL server and therefore locks all of our websites.

I'll get my code figured out, no biggie. But I need to make sure the server times out when this happens. (The page I'm working on runs fine with certain instances of the query, and locks with others using a different query variable. I can't have something like that sneak up on me on a page I haven't touched for three years.)

I can't figure out how an SP that runs once on the server, from an ASP page, is tying up SQL server this way. It's obviously some sort of a timeout issue, but I can't figure out where/which timeout values to change.

I actually have to remote desktop to the server and kill the process in SQL server.

I'm afraid I'm a generalist, and server management is not my thing, even though it's my responsibility, so I am almost certain to have questions about any answer that I receive.

How can I track this down? What settings do I need to change?

More info: It's not SQL Server On our test site, I created an ASP file that just did an endless loop (do while 1=1) and had the same problem - the other websites wouldn't load - without SQL server being involved. So I think the reason the process was hanging is that the page wasn't timing out as it should, and so the connection to SQL was never closed. Killing the process in SQL server would reset the page somehow.

For my intentional endless loop, I had to refresh the app pool to get rid of it. This points more to either IIS or the ASP settings.

The ASP timeouts are set to whatever the default were when the server was first loaded.

I still can't figure out why one file is locking up all websites, though. Again, that didn't happen on the old server.

share|improve this question

2 Answers 2

First, and hear me out, this is what I always tell developers who complain about long-running statements or procedures and want to fiddle with query time outs:

The best thing to do is to fix your code.

Figure out what code is running long (use SQL Profiler, look at query plans or just stare at your code until you grok it) and repair the problem. It could be code, indexing, out-of-date statistics, blocking from something else, slow storage or a bunch of other things.

Ok, that's done.

If you want to change the query timeout to get things to work while you figure out what is broken, you want to look in your code to find where you create ADO query objects (result sets, command objects, etc.) and change the .QueryTimeout and/or .CommandTimeout properties.

IIRC, the default for those properties is 60 seconds. Make it "long enough". AFAIK, there is no global way to set .QueryTimeout and/or .CommandTimeout on an IIS- or application-global way, but I'm not an IIS/ASP guy, I'm a DBA.

Note that increasing this value might mean that the longer-running code will be blocking other users for longer and it could lead to collateral damage. The default value is set to a value that may seem low to help keep your connection from messing up other connections.

There is no simple way to kill queries that run "too long" on SQL 2008.

The "query timeout" stuff that you see in the SQL Server "Properties" dialogs has to do with timeouts to linked servers. IOW, that controls the timeout value for the local server when it acts as a client and connects to a remote server on your behalf. (Practically speaking, it's like altering the QueryTimeout values in your query objects, but it's all inside of SQL Server.) That won't help you.

The old "query governor", when configured, will estimate how long a query will take and it always got it wrong whenever I tried to use it. Everyone I've seen talk about it has found it to be disappointing. The "resource governor", which you may notice, is designed to limit use of RAM and CPU, not time. Therefore, you don't want that at all.

You could possibly write something that would kill queries. BUT:

  • You may step on people's important work. (Including backups, re-indexing, etc.)
  • Those killed queries will be rolled back. Rolling back takes a finite amount of time and work may disappear.
  • Re-submission of those rolled back queries may cause even more load on your server.

In short, that kind of a project is usually harder to get right than fixing the long-running code in the first place.

share|improve this answer
    
Hi, Darin - my code is already fixed, and was 5 minutes after I wrote this. That's not the issue. And it's also, as I said in my added note, not the database that is the issue, it only appeared that way because of something in the way IIS is set up. The problem is that even if I have an endless loop, with no database interaction whatsoever and run it - just three lines - it locks up every website on our server. The old server didn't do that; IIRC, it didn't even stall the website that the problem code was running on. Thanks for the info, though: I'm always working to optimize my web queries. –  Julie Oct 5 '12 at 15:42
1  
Oh. Should the question title be reworded and retagged so that sql-server-2008 is no longer mentioned? Or would it be better to leave that in for future persons searching for similar problems? –  darin strait Oct 8 '12 at 21:19
    
Hmm. That's a good question. It appeared to involve SQL server, because due to the loop not allowing things to close, SQL server hogged all of the processor time. But the problem really must be in IIS. (I have a friend that thinks he knows what the issue is, if we can ever connect.) What's normal in a situation like this? (I wanted to reword the entire question, but didn't, partly because it would have made your answer seem out of context. But at least rewording should happen, I think.) –  Julie Oct 9 '12 at 1:30
    
I'm not 100% on protocol for this, either. I've never edited a question posed by another, but I did just alter the title and tags and "submit the change for peer review". We'll see if I get scolded. –  darin strait Oct 10 '12 at 15:00
up vote 0 down vote accepted

For the next guy.

The solution to this problem comes down to perceptions, when someone - like me - isn't really familiar with server management.

As I stated in the original question, we've just moved from an older server - Windows Server 2003/SQL Server 2000 - to a new one, that uses Windows Server 2008 R2 64-bit and SQL Server 2008 R2. I didn't set up the original server, and paid someone to migrate stuff to the new server, giving him the instructions, "Just set it up the way the old server was set up." Heh.

The old server was 9 years old, a dual Xeon processor machine with 1 GB of RAM. The original server was set up with two websites - our booking system and the company website - both using the same application pool. As websites were added over the years, they were added to the default app pool. With our resource issues, that was the best we could do. And at some point, someone must have set the timeout to be a very short value, probably because the server got a bit clogged at times.

The new server has 32gb of RAM and a 4 core processor. But the consultant I hired did exactly as I asked - almost - and set everything up the way it was on the old server, including putting all of the websites into the same application pool. The one thing he did not do, apparently, was set the timeout to the shorter value that was the old server must have been set to.

On the new server, when I loaded an ASP page that called a stored procedure, SQL server used all the processor allocated to it and just hung there and all websites were stalled, until I killed the process in SQL Server. After a bit of research, I found out the same thing happened - all websites stalled - when running any kind of an endless loop, even if SQL server was not involved, and that recycling the app pool would "fix" it.

It all came down to assumptions/perceptions: The timeout on the old server was short enough, that when debugging something, I never realized the all of the websites were locked, because it was only for 15 or 20 seconds, then the page would timeout, I'd fix it and move on. When the timeout is set to 120 seconds, though, that's a whole different ball game. Since SQL Server was stalled, I thought it was a SQL Server issue, but it was hanging because the record and connection weren't closed because the page was stuck in a loop...so it only appeared that SQL server was part of the problem.

The final solution was to separate most of the websites into their own application pools (duh), and adjust the timeout values for ASP according to what was appropriate for each website.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.