Query timout problem

We are seing -2147217871 Timeout expired on queries. Any idea of possible
cause? Please I need help.
Thanks
EgbonEgbon,
Can happen due to many reasons.Some of the main things to look for are
blocks/deadlocks in the server.Also, use the execution plan on the query to
check for any missing statistics, index usage etc.Use sql profiler to
capture the sql statements and the corresponding statistics.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Egbon" <Vnjowusi@.gosps.com> wrote in message
news:eOyY24kSDHA.632@.TK2MSFTNGP12.phx.gbl...
> We are seing -2147217871 Timeout expired on queries. Any idea of possible
> cause? Please I need help.
> Thanks
> Egbon
>|||I will do that.
Thanks.
Egbon
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:#YIY$BlSDHA.2252@.TK2MSFTNGP12.phx.gbl...
> Egbon,
> Can happen due to many reasons.Some of the main things to look for are
> blocks/deadlocks in the server.Also, use the execution plan on the query
to
> check for any missing statistics, index usage etc.Use sql profiler to
> capture the sql statements and the corresponding statistics.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Egbon" <Vnjowusi@.gosps.com> wrote in message
> news:eOyY24kSDHA.632@.TK2MSFTNGP12.phx.gbl...
> > We are seing -2147217871 Timeout expired on queries. Any idea of
possible
> > cause? Please I need help.
> >
> > Thanks
> >
> > Egbon
> >
> >
>|||Egbon (Vnjowusi@.gosps.com) writes:
> We are seing -2147217871 Timeout expired on queries. Any idea of possible
> cause? Please I need help.
What is your problem? That the query takes long time, or that they are
interupted by this message?
All client libraries from Microsoft, save DB-Library, have a default
command tiemout of 30 seconds, but this is settable. If you are using
ADO, you set the .CommandTimeout property on the .Connection and
.Command objects. A good value is 0 = no timeout.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||The problem was no more there after truncating the log and restarting the
server.
THANKS.
Egbon.
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93B9507CE5B6Yazorman@.127.0.0.1...
> Egbon (Vnjowusi@.gosps.com) writes:
> > We are seing -2147217871 Timeout expired on queries. Any idea of
possible
> > cause? Please I need help.
> What is your problem? That the query takes long time, or that they are
> interupted by this message?
> All client libraries from Microsoft, save DB-Library, have a default
> command tiemout of 30 seconds, but this is settable. If you are using
> ADO, you set the .CommandTimeout property on the .Connection and
> .Command objects. A good value is 0 = no timeout.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Query Timout

Hi All,
I am fairly new to SQLServer, although I have used other RDBMS software
extensively.
Is there a simple way to restrict the amount of time a query runs for?
I have inherited a system that allows users to run very large queries and
it is killing performance. I want to time the query out after a given
number of second.
TIA
PeterPeter,
serverside you could look at setting the query governor cost limit using
sp_configure (see BOL). It will disallow 'costly' queries from starting so
isn't exactly what you are referring to but you might still find it useful.
On the client side, the sqlcommand object (or its equivalent in your
programming language) should have a query timeout property.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in news:ODiQnZ$fGHA.5104
@.TK2MSFTNGP04.phx.gbl:
>
Thanks Paul,
I'll use this as a work around. We do not have the source for the
application unfortunately.
Peter

Query Timout

Hi All,
I am fairly new to SQLServer, although I have used other RDBMS software
extensively.
Is there a simple way to restrict the amount of time a query runs for?
I have inherited a system that allows users to run very large queries and
it is killing performance. I want to time the query out after a given
number of second.
TIA
PeterPeter,
serverside you could look at setting the query governor cost limit using
sp_configure (see BOL). It will disallow 'costly' queries from starting so
isn't exactly what you are referring to but you might still find it useful.
On the client side, the sqlcommand object (or its equivalent in your
programming language) should have a query timeout property.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in news:ODiQnZ$fGHA.5104
@.TK2MSFTNGP04.phx.gbl:

>
Thanks Paul,
I'll use this as a work around. We do not have the source for the
application unfortunately.
Peter

Query Timout

OK...i have a query that looks something like what is below and is generated on the fly:

SELECT DISTINCT c.*
FROM SOMETHING c
WHERE ( X IN
 (SELECT Y
 FROM Z
 WHERE ZXY = '1') AND
 ( X IN
 (SELECT Y
 FROM Z
 WHERE ZXY = '4')OR
 ( X IN
 (SELECT Y
 FROM Z
 WHERE ZXY = '9')AND
 ( X IN
 (SELECT Y
 FROM Z
 WHERE ZXY = '7'))

as you can tell something like this will take a long time to process.....what is the best way to handle a query like such?...is cursor the way to go.....it's driving me nuts!

Treyanyone...anyone?|||OK...if nobody can help...can someone point me in the right direction?

Trey|||I possibly would create an index on your ZXY column and create a stored procedure like this to enhance performance:

CREATE PROCEDURE proc_DoSomething
AS
SELECT DISTINCT c.* FROM SOMETHING c
WHERE ( X IN (SELECT Y FROM Z WHERE ZXY IN ('1', '4', '7') OR ZXY = '9'))

Note that I've combined your subqueries involving AND , OR operators into one. I guess this should work well for you.|||I would use a stored procedure but...they query is created on the fly...and i don't know how many variables will be passed to the stored procedure...could be 1 or could be 20.

Trey|||Yeah, this is likely more an index issue. There is nothing in this query that seems to me that it should toake all that long, unless you have millions and millions of rows in your table. If you add some indices to the table, you should be fine...|||ok...then something like this should be written how...?

SELECT DISTINCT c.*
FROM dbo.def c
WHERE (id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4790') AND (transaction.type_id = '4706') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) OR
(id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4753') AND (transaction.type_id = '4706') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) OR
(id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4767') AND (transaction.type_id = '4706') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) OR
(id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4777') AND (transaction.type_id = '4707') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) AND (id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4769') AND (transaction.type_id = '4707') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) AND (id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4767') AND (transaction.type_id = '4707') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) AND (id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4773') AND (transaction.type_id = '4707') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL)))