[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

User query hanging a critical application

JustWannaBeAbleToJamAndNotEmbarassMyself

3/27/2007 8:25:00 PM

I have a critical production system.
There is an application.. that runs as a Windows Service that
continually queries a SQL Server 2000 DB, on Windows 2000 every second
or two.

A user went onto the system and executed a complex query, that ended
up causing SQL Server to utilize 100% of the CPU.

The critical application simultaneously went to query the database via
a stored procedure using OLE DB, and just waited for more than 20
minutes, while the user's query was executing.

The question is.. why didn't the critical application time out after
so many seconds?? I thought the default for command execution was 30
seconds??

Is there any way to make sure a OLE DB command times out, no matter
how much CPU usage SQL Server is consuming?? Is there a way to
limit the amount of CPU usage SQL Server uses.. to at least allow
other applications to be responsive?

Whats the easiest way to set up a timeout value that will work for all
OLE DB commands within an application?.. I prefer not to have to set
the timeout value for each individual command.

Please help!
3 Answers

Erland Sommarskog

3/27/2007 10:11:00 PM

0

CSAWannabe (wisertime2006@gmail.com) writes:
> The critical application simultaneously went to query the database via
> a stored procedure using OLE DB, and just waited for more than 20
> minutes, while the user's query was executing.
>
> The question is.. why didn't the critical application time out after
> so many seconds?? I thought the default for command execution was 30
> seconds??

The default timeout is indeed 30 seconds for many APIs. However, if you
use OLE DB directly, without any layer of ADO, the default timeout is 0,
that is no timeout. (Which is the only sensible default value in my
opinion.)

But I think that the timeout is determined to when the first data comes
rolling in. So if the critical application runs a query, and gets some
rows back, and the is blocked by the run-away query, there will be no
timeout.

> Is there any way to make sure a OLE DB command times out, no matter
> how much CPU usage SQL Server is consuming??

The timeout is a client thing, so SQL Server CPU consumption has nothing
to do wih it.

> Is there a way to limit the amount of CPU usage SQL Server uses.. to at
> least allow other applications to be responsive?

No. Well, if it is a multi-CPU machine, you can change the setting
"Max Degrees of Parallelism" to a lower value than the number of
available CPUs. That prevents a single process from monopolising
all CPUs.

> Whats the easiest way to set up a timeout value that will work for all
> OLE DB commands within an application?.. I prefer not to have to set
> the timeout value for each individual command.

It would help to know exactly in what way you are using OLE DB. ADO?
OLE DB Consumer Templates? The naked OLE DB API?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/...

JustWannaBeAbleToJamAndNotEmbarassMyself

3/27/2007 10:56:00 PM

0

Thanks for your excellent answers and prompt response.

In response to your questions.. I am using OLE DB Consumer
Templates. So given that.. whats the easiest way to set a query
timeout for the entire application. I'd rather not have to set the
timeout for each individual command. which calls a stored procedure..
since there are many of them throughout the application. Can I do it
on a connection basis?? or something similar?

I am running this app.. on a multi-processor machine. Although
limiting SQL Server to a particular number of processes.. could take
away a good deal of performance, on a 2 or 4 processor machine. I'm
not sure thats a great option.
And this application is very performance oriented.

So you think that even if SQL Server.. manages to run up the CPU to
100%.. the query timeout would still work?? If its off by a few
seconds that would be tolerable. And you're positive the default
timeout for OLE DB Consumer templates.. is inifinite??

Thanks again. You've been a lifesaver already.




On Mar 27, 6:10 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> CSAWannabe (wisertime2...@gmail.com) writes:
> > The critical application simultaneously went to query the database via
> > a stored procedure using OLE DB, and just waited for more than 20
> > minutes, while the user's query was executing.
>
> > The question is.. why didn't the critical application time out after
> > so many seconds?? I thought the default for command execution was 30
> > seconds??
>
> The default timeout is indeed 30 seconds for many APIs. However, if you
> use OLE DB directly, without any layer of ADO, the default timeout is 0,
> that is no timeout. (Which is the only sensible default value in my
> opinion.)
>
> But I think that the timeout is determined to when the first data comes
> rolling in. So if the critical application runs a query, and gets some
> rows back, and the is blocked by the run-away query, there will be no
> timeout.
>
> > Is there any way to make sure a OLE DB command times out, no matter
> > how much CPU usage SQL Server is consuming??
>
> The timeout is a client thing, so SQL Server CPU consumption has nothing
> to do wih it.
>
> > Is there a way to limit the amount of CPU usage SQL Server uses.. to at
> > least allow other applications to be responsive?
>
> No. Well, if it is a multi-CPU machine, you can change the setting
> "Max Degrees of Parallelism" to a lower value than the number of
> available CPUs. That prevents a single process from monopolising
> all CPUs.
>
> > Whats the easiest way to set up a timeout value that will work for all
> > OLE DB commands within an application?.. I prefer not to have to set
> > the timeout value for each individual command.
>
> It would help to know exactly in what way you are using OLE DB. ADO?
> OLE DB Consumer Templates? The naked OLE DB API?
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downl......
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/...

Erland Sommarskog

3/29/2007 10:33:00 PM

0

CSAWannabe (wisertime2006@gmail.com) writes:
> In response to your questions.. I am using OLE DB Consumer
> Templates. So given that.. whats the easiest way to set a query
> timeout for the entire application. I'd rather not have to set the
> timeout for each individual command. which calls a stored procedure..
> since there are many of them throughout the application. Can I do it
> on a connection basis?? or something similar?

I have never worked with the Consumer Templates, only the bare OLE DB
API. But I have the impression that the Consumer templates is a fairly
thin layer over the API. DBPROP_COMMANDTIMEOUT is a rowset property, so
I would suspect that there is no other option than to set it per rowset.

> I am running this app.. on a multi-processor machine. Although
> limiting SQL Server to a particular number of processes.. could take
> away a good deal of performance, on a 2 or 4 processor machine. I'm
> not sure thats a great option.

So you are running service and SQL Server on the same machine? Hmm, if
it's performance-critical, a hardware investment may be in place. CPU
power is not the only point of competition, memory is another.

> So you think that even if SQL Server.. manages to run up the CPU to
> 100%.. the query timeout would still work?? If its off by a few
> seconds that would be tolerable. And you're positive the default
> timeout for OLE DB Consumer templates.. is inifinite??

Again, I have not used the Consumer Templates, but in the bare API,
the default timeout has the only reasonable value: 0.

Keep in mind that when you use timeouts, you must always submit a
IF @@tracount > 0 ROLLBACK TRANSACTION in response, as there is no
automatic rollback with query cancellations. If you neglect to this,
you will cause a lot of problems. Which is not uncommon to happen to
people who use API with a default timeout of 30 seconds.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/...