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
Showing posts with label timeout. Show all posts
Showing posts with label timeout. Show all posts
Wednesday, March 7, 2012
Query Timeouts--Indexing Questions
I am helping a client with query timeout issues. Their tables have an
identity field used as a primary key, with a clustered index on the primary
key. This key really has no functional significance; it is just a number.
One example is that they search for customers by phone number, with an
additional index on the phone number. When I check the execution plan, it
shows a clustered index scan on the clustered index. Isn't a clustered index
s
the most efficient?
My question is whether I should drop the primary key and clustered index on
the identity column and create a clustered index on something of physical
significance like a phone number to get better performance?
Any ideas?
Larry Menzin
American Techsystems Corp.Place all the non-cluster index in a separate filegroup.
"Larry Menzin" wrote:
> I am helping a client with query timeout issues. Their tables have an
> identity field used as a primary key, with a clustered index on the primar
y
> key. This key really has no functional significance; it is just a number.
> One example is that they search for customers by phone number, with an
> additional index on the phone number. When I check the execution plan, it
> shows a clustered index scan on the clustered index. Isn't a clustered ind
ex
> s the most efficient?
> My question is whether I should drop the primary key and clustered index o
n
> the identity column and create a clustered index on something of physical
> significance like a phone number to get better performance?
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||What indexes are used, and how they are used, depend on the columns returned
by the select statement and the nature of the WHERE condition.
SELECT * FROM [YourTable] WHERE [PhoneNumber] = '916-867-5309'
should result in a s of the nonclustered index on [PhoneNumber] with a
bookmark lookup on the clustered index. But, if you changed it to
SELECT * FROM [YourTable] WHERE [PhoneNumber] LIKE '%916-867-5309%'
you will most likely get a clustered index scan. Why? Because the SELECT is
looking for columns not covered by the non-clustered index.
If your application performs queries like
SELECT [CustomerName] FROM [YourTable]
WHERE [PhoneNumber] LIKE '%916-867-5309%'
consider using a composite non-clustered index using both [CustomerName] and
[PhoneNumber] columns.
"Larry Menzin" wrote:
> I am helping a client with query timeout issues. Their tables have an
> identity field used as a primary key, with a clustered index on the primar
y
> key. This key really has no functional significance; it is just a number.
> One example is that they search for customers by phone number, with an
> additional index on the phone number. When I check the execution plan, it
> shows a clustered index scan on the clustered index. Isn't a clustered ind
ex
> s the most efficient?
> My question is whether I should drop the primary key and clustered index o
n
> the identity column and create a clustered index on something of physical
> significance like a phone number to get better performance?
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||My client is using some wildcards so that indexes are not used properly.
Since there can only be one clustered index per table and my client is using
it on the identity column, I'm stuck using non-clustered indexes.
What strategy is there for sealing with wildcards?
Larry Menzin
American Techsystems Corp.
"Mark Williams" wrote:
> What indexes are used, and how they are used, depend on the columns return
ed
> by the select statement and the nature of the WHERE condition.
> SELECT * FROM [YourTable] WHERE [PhoneNumber] = '916-867-5309'
> should result in a s of the nonclustered index on [PhoneNumber] with a
> bookmark lookup on the clustered index. But, if you changed it to
> SELECT * FROM [YourTable] WHERE [PhoneNumber] LIKE '%916-867-5309%'
> you will most likely get a clustered index scan. Why? Because the SELECT i
s
> looking for columns not covered by the non-clustered index.
> If your application performs queries like
> SELECT [CustomerName] FROM [YourTable]
> WHERE [PhoneNumber] LIKE '%916-867-5309%'
> consider using a composite non-clustered index using both [CustomerName] and
> [PhoneNumber] columns.
> --
>
> "Larry Menzin" wrote:
>|||You won't be able to avoid a scan of an index when using wildcards in the
WHERE condition.
Ideally, you would want an index *s* on a non-clusted index, with a
bookmark lookup to the clustered index. (There are some conditions, believe
it or not, where a scan of the clustered index will be faster, but that's
another story).
Again, your best option would be to create a composite non-clustered index
using the two columns involved in the customer phone - number lookup query.
"Larry Menzin" wrote:
> My client is using some wildcards so that indexes are not used properly.
> Since there can only be one clustered index per table and my client is usi
ng
> it on the identity column, I'm stuck using non-clustered indexes.
> What strategy is there for sealing with wildcards?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Mark Williams" wrote:
>
identity field used as a primary key, with a clustered index on the primary
key. This key really has no functional significance; it is just a number.
One example is that they search for customers by phone number, with an
additional index on the phone number. When I check the execution plan, it
shows a clustered index scan on the clustered index. Isn't a clustered index
s
the most efficient?My question is whether I should drop the primary key and clustered index on
the identity column and create a clustered index on something of physical
significance like a phone number to get better performance?
Any ideas?
Larry Menzin
American Techsystems Corp.Place all the non-cluster index in a separate filegroup.
"Larry Menzin" wrote:
> I am helping a client with query timeout issues. Their tables have an
> identity field used as a primary key, with a clustered index on the primar
y
> key. This key really has no functional significance; it is just a number.
> One example is that they search for customers by phone number, with an
> additional index on the phone number. When I check the execution plan, it
> shows a clustered index scan on the clustered index. Isn't a clustered ind
ex
> s
the most efficient?> My question is whether I should drop the primary key and clustered index o
n
> the identity column and create a clustered index on something of physical
> significance like a phone number to get better performance?
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||What indexes are used, and how they are used, depend on the columns returned
by the select statement and the nature of the WHERE condition.
SELECT * FROM [YourTable] WHERE [PhoneNumber] = '916-867-5309'
should result in a s
of the nonclustered index on [PhoneNumber] with abookmark lookup on the clustered index. But, if you changed it to
SELECT * FROM [YourTable] WHERE [PhoneNumber] LIKE '%916-867-5309%'
you will most likely get a clustered index scan. Why? Because the SELECT is
looking for columns not covered by the non-clustered index.
If your application performs queries like
SELECT [CustomerName] FROM [YourTable]
WHERE [PhoneNumber] LIKE '%916-867-5309%'
consider using a composite non-clustered index using both [CustomerName] and
[PhoneNumber] columns.
"Larry Menzin" wrote:
> I am helping a client with query timeout issues. Their tables have an
> identity field used as a primary key, with a clustered index on the primar
y
> key. This key really has no functional significance; it is just a number.
> One example is that they search for customers by phone number, with an
> additional index on the phone number. When I check the execution plan, it
> shows a clustered index scan on the clustered index. Isn't a clustered ind
ex
> s
the most efficient?> My question is whether I should drop the primary key and clustered index o
n
> the identity column and create a clustered index on something of physical
> significance like a phone number to get better performance?
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||My client is using some wildcards so that indexes are not used properly.
Since there can only be one clustered index per table and my client is using
it on the identity column, I'm stuck using non-clustered indexes.
What strategy is there for sealing with wildcards?
Larry Menzin
American Techsystems Corp.
"Mark Williams" wrote:
> What indexes are used, and how they are used, depend on the columns return
ed
> by the select statement and the nature of the WHERE condition.
> SELECT * FROM [YourTable] WHERE [PhoneNumber] = '916-867-5309'
> should result in a s
of the nonclustered index on [PhoneNumber] with a> bookmark lookup on the clustered index. But, if you changed it to
> SELECT * FROM [YourTable] WHERE [PhoneNumber] LIKE '%916-867-5309%'
> you will most likely get a clustered index scan. Why? Because the SELECT i
s
> looking for columns not covered by the non-clustered index.
> If your application performs queries like
> SELECT [CustomerName] FROM [YourTable]
> WHERE [PhoneNumber] LIKE '%916-867-5309%'
> consider using a composite non-clustered index using both [CustomerName] and
> [PhoneNumber] columns.
> --
>
> "Larry Menzin" wrote:
>|||You won't be able to avoid a scan of an index when using wildcards in the
WHERE condition.
Ideally, you would want an index *s
* on a non-clusted index, with abookmark lookup to the clustered index. (There are some conditions, believe
it or not, where a scan of the clustered index will be faster, but that's
another story).
Again, your best option would be to create a composite non-clustered index
using the two columns involved in the customer phone - number lookup query.
"Larry Menzin" wrote:
> My client is using some wildcards so that indexes are not used properly.
> Since there can only be one clustered index per table and my client is usi
ng
> it on the identity column, I'm stuck using non-clustered indexes.
> What strategy is there for sealing with wildcards?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Mark Williams" wrote:
>
Query time-outs
SQL Server has (in its sp_configure) the option to set "remote query
timeout" and in Query Analyzer it can set the "Query time-out" option in
seconds.
My first question is:
Does the "remote query timeout" option only handle queries from remote
servers or does it work on queries directly conncected to the local SQL
Server? Meaning, does the "remote query timeout" option work like QA's
"Query time-out" on the server?
My next question is:
Depending on the answer from above, is there a way to configure the max
time for a given query?
The last question:
If SQL Server does not have a method for the above question, is there a
third party app that can limit the amount of time a query can take on
the SQL Server?
BillI think you have an option called CommandTimeOut in ADO. Using this, you can
set a limit in terms of the amount of time you want to wait for a query to
execute. If the time has elapsed, execution stops. You also have another
option called Query Governor Cost Limit, which determines uptil what cost a
query can execute. Every query has an execution cost and you can set a limit
on the maximum cost that is allowed for your application.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"William Enloe" <william.enloe@.domail.maricopa.edu> wrote in message
news:400476BF.B971E8F1@.domail.maricopa.edu...
> SQL Server has (in its sp_configure) the option to set "remote query
> timeout" and in Query Analyzer it can set the "Query time-out" option in
> seconds.
> My first question is:
> Does the "remote query timeout" option only handle queries from remote
> servers or does it work on queries directly conncected to the local SQL
> Server? Meaning, does the "remote query timeout" option work like QA's
> "Query time-out" on the server?
> My next question is:
> Depending on the answer from above, is there a way to configure the max
> time for a given query?
> The last question:
> If SQL Server does not have a method for the above question, is there a
> third party app that can limit the amount of time a query can take on
> the SQL Server?
>
> Bill|||That may be true, but I don't have influence on the Development team
because the fault was seen in Crystal. I've checked into the Query
Governor Cost Limit, but the cost (though documented in seconds) fails
to stop a query on a duration of time. In my test I set the Query cost
to 8 (seconds) and ran a simple (poor performance query) "Select * from
sysobjects A1, sysobjects A2". Though it is a cartesian product with a
runtime duration between 2 minutes to 8 minutes, the cost estimation in
query performance shows up as a 7. In my mind this query should never
run and I would never be allowed to govern the time a query takes if the
cost estimation isn't accurate to a closer number of the duration. So,
how is the cost derived and what can be done to limit query duration
from the server side?
Thank you,
Bill
SriSamp wrote:
> I think you have an option called CommandTimeOut in ADO. Using this, you can
> set a limit in terms of the amount of time you want to wait for a query to
> execute. If the time has elapsed, execution stops. You also have another
> option called Query Governor Cost Limit, which determines uptil what cost a
> query can execute. Every query has an execution cost and you can set a limit
> on the maximum cost that is allowed for your application.
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "William Enloe" <william.enloe@.domail.maricopa.edu> wrote in message
> news:400476BF.B971E8F1@.domail.maricopa.edu...
> > SQL Server has (in its sp_configure) the option to set "remote query
> > timeout" and in Query Analyzer it can set the "Query time-out" option in
> > seconds.
> >
> > My first question is:
> > Does the "remote query timeout" option only handle queries from remote
> > servers or does it work on queries directly conncected to the local SQL
> > Server? Meaning, does the "remote query timeout" option work like QA's
> > "Query time-out" on the server?
> >
> > My next question is:
> > Depending on the answer from above, is there a way to configure the max
> > time for a given query?
> >
> > The last question:
> > If SQL Server does not have a method for the above question, is there a
> > third party app that can limit the amount of time a query can take on
> > the SQL Server?
> >
> >
> > Bill
timeout" and in Query Analyzer it can set the "Query time-out" option in
seconds.
My first question is:
Does the "remote query timeout" option only handle queries from remote
servers or does it work on queries directly conncected to the local SQL
Server? Meaning, does the "remote query timeout" option work like QA's
"Query time-out" on the server?
My next question is:
Depending on the answer from above, is there a way to configure the max
time for a given query?
The last question:
If SQL Server does not have a method for the above question, is there a
third party app that can limit the amount of time a query can take on
the SQL Server?
BillI think you have an option called CommandTimeOut in ADO. Using this, you can
set a limit in terms of the amount of time you want to wait for a query to
execute. If the time has elapsed, execution stops. You also have another
option called Query Governor Cost Limit, which determines uptil what cost a
query can execute. Every query has an execution cost and you can set a limit
on the maximum cost that is allowed for your application.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"William Enloe" <william.enloe@.domail.maricopa.edu> wrote in message
news:400476BF.B971E8F1@.domail.maricopa.edu...
> SQL Server has (in its sp_configure) the option to set "remote query
> timeout" and in Query Analyzer it can set the "Query time-out" option in
> seconds.
> My first question is:
> Does the "remote query timeout" option only handle queries from remote
> servers or does it work on queries directly conncected to the local SQL
> Server? Meaning, does the "remote query timeout" option work like QA's
> "Query time-out" on the server?
> My next question is:
> Depending on the answer from above, is there a way to configure the max
> time for a given query?
> The last question:
> If SQL Server does not have a method for the above question, is there a
> third party app that can limit the amount of time a query can take on
> the SQL Server?
>
> Bill|||That may be true, but I don't have influence on the Development team
because the fault was seen in Crystal. I've checked into the Query
Governor Cost Limit, but the cost (though documented in seconds) fails
to stop a query on a duration of time. In my test I set the Query cost
to 8 (seconds) and ran a simple (poor performance query) "Select * from
sysobjects A1, sysobjects A2". Though it is a cartesian product with a
runtime duration between 2 minutes to 8 minutes, the cost estimation in
query performance shows up as a 7. In my mind this query should never
run and I would never be allowed to govern the time a query takes if the
cost estimation isn't accurate to a closer number of the duration. So,
how is the cost derived and what can be done to limit query duration
from the server side?
Thank you,
Bill
SriSamp wrote:
> I think you have an option called CommandTimeOut in ADO. Using this, you can
> set a limit in terms of the amount of time you want to wait for a query to
> execute. If the time has elapsed, execution stops. You also have another
> option called Query Governor Cost Limit, which determines uptil what cost a
> query can execute. Every query has an execution cost and you can set a limit
> on the maximum cost that is allowed for your application.
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "William Enloe" <william.enloe@.domail.maricopa.edu> wrote in message
> news:400476BF.B971E8F1@.domail.maricopa.edu...
> > SQL Server has (in its sp_configure) the option to set "remote query
> > timeout" and in Query Analyzer it can set the "Query time-out" option in
> > seconds.
> >
> > My first question is:
> > Does the "remote query timeout" option only handle queries from remote
> > servers or does it work on queries directly conncected to the local SQL
> > Server? Meaning, does the "remote query timeout" option work like QA's
> > "Query time-out" on the server?
> >
> > My next question is:
> > Depending on the answer from above, is there a way to configure the max
> > time for a given query?
> >
> > The last question:
> > If SQL Server does not have a method for the above question, is there a
> > third party app that can limit the amount of time a query can take on
> > the SQL Server?
> >
> >
> > Bill
Query timeout when rows returned < TOP (n)?
SQLServer 2005, ~7 million records, queries are using a clustered index keyed on the field "date".
Both queries below have the same execution plan, IO Cost, etc but Query 1 takes ~38 seconds whereas Query2 takes ~.3 seconds.
The only difference is in one of the where clauses (point=). It seems to have something to do with the fact that the first query is only returning 66 rows, but I'm at a loss as to why it's so slow. Query 1 is sub 1 second if I do a select top 66, but ~38 seconds with a top 67.
Obviously there's something I'm missing, but I'm completely clueless as to what it is.
Thanks - James
Query 1:
SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName
FROM history
WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')
ORDER BY date DESC
37759 ms
66 rows
IO Cost: 188.225
Returns rows 1-61 < 1 second, 62-66 @. ~38 seconds
Query 2:
SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName
FROM history
WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 1')
ORDER BY date DESC
334 ms
100 rows
IO Cost: 188.225
It really depends on how many records there are of Attenna 1 and Antenna 2.
Clearly out of all your records there are only 66 that match Atenna 2 so it probably had to look through every record taking 38 seconds. however, there seem to be a whole lot more Attenna 1 or they were toward the begging of your records. Because it filled the Top 100 you specified. Once that is filled there is no point for the query to keep executing and it popped back after only 3 seconds.
On top of that your index is no on any of the columns in your where clause. An index is not a magic item. You indexes on your WHERE criteria in order for it to take advantage of it.
|||Query plans are your friend.
Look at the query plan and you will see the difference between both queries.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Yep query plans are the same for both. I've got indices for the other fields also.
The odd thing is with a if I give it a point name that doesn't exist like
SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName
FROM history
WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'xx')
ORDER BY date DESC
It uses the index for point then hits the clustered index, but the same query with a valid point name
SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName
FROM history
WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')
ORDER BY date DESC
it uses only the clustered index. I'm starting to wonder if it might be a problem with Stastics being out of synch for some reason.
Thanks again - James
|||Query 1 required a scan of 100% of the table. Even after looking at the whole table, only 66 records were returned.
In the second query, it found 100 records very quickly so there was no need to continue.
If you were to remove the "Top 100" from these queries, the execution times would be very similar as both queries would be required to scan the whole table (with this caviat: If Query 2 returne 200,000,000 records, it's going to take longer...the table scan won't take longer to locate the records, but actually reading the disk and moveing the bits will take longer).
Query Timeout Setting
SQL Server 2005 is timing out when running loooong queries.
Is there a global place I can extend the timeout for queries? I do not mean in code but just in general use?
Thanks
Craig
Perhaps its better to fix the long running query?
|||
ndinakar:
Perhaps its better to fix the long running query?
I agree completely!
The number of reusable connections is limited. A few users of a single long-running query can bring an entire system down!
Think about driving on a road with ten lanes going your way. Just two slooooow drivers, each in different lanes, can cause traffic jams really fast in busy traffic.
|||
When you have 80 - 160 million records in 2 or more tables we need all options possible, so any answers to the original question?
|||80-160 mill rows is nothing for SQL Server, if your query is properly optimized and have the right indexes. I work with tables over 500 mill rows to as high as 1.2 bill rows. So, fixing the query will be a better solution in the longer run. If you can show us the query that's timing out we can try to help with that.
Check this article on various ways to set timeout limits:http://vyaskn.tripod.com/watch_your_timeouts.htm
|||Both the connection object and the command object have a property that deals with maximum times.
For legitimate, long-running queries that cannot be better tuned or designed, just up the time on those properties.
That way, you force your developers (assuming you are developing with test datasets of the appropriate size) to identify long-running queries before they hit production. It gives you an opportunity to improve the sql before the system goes live.
By searching for those property names, you can identify long-running queries (from the developer's point of view) and route them to your sql specialist for tuning.Query timeout problems
Front End: Access 2000 Project (.adp)
Back End: SQL Server 2000
Back End: SQL Server 2000
I have an application that keeps timing out. I have opened the
DataLink properties in the front end (File, Connection, Advanced tab)
and set the timeout to 999. But the connection still occasionally
times out.
Any ideas? Some of the SQL is pretty horrible (multiple sub-queries
etc.)
TIA
Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk"Edward" <teddysnips@.hotmail.com> wrote in message
news:25080b60.0501310730.3f8e6ed9@.posting.google.c om...
> Front End: Access 2000 Project (.adp)
> Back End: SQL Server 2000
> I have an application that keeps timing out. I have opened the
> DataLink properties in the front end (File, Connection, Advanced tab)
> and set the timeout to 999. But the connection still occasionally
> times out.
> Any ideas? Some of the SQL is pretty horrible (multiple sub-queries
> etc.)
> TIA
> Edward
> --
> The reading group's reading group:
> http://www.bookgroup.org.uk
The timeout you changed is the connection timeout, so it won't affect
timeouts caused by long-running queries. Depending on what connection
library you're using (ODBC, ADO), you should be able to change the general
timeout value, but I have no idea how - perhaps an Access group might give
more help.
In ay event, if the timeouts occur during different queries, or the same
queries with different parameters, you'll probably have to try to identify
the problem queries more exactly - Profiler would be a good place to start.
Simon
Query Timeout Issue with .NET
Hello all and thank you for your time.
I have an application with reports taking over 30 seconds to run. This
is producing the
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding." error. Is there a way to
increase the timeout expired default of 30 seconds until I have
resources to optimize or rewrite the querys? There are over 120
reports and querys!
I've tried:
<httpRuntime executionTimeout=3D"1800"/>
<add key=3D"XYZ"
value=3D" ;Server=3Dserver01;UID=3Dsa;PASSWORD=3Dp
assword;DATABASE=3Ddatabas=
e;Connecti=ADon
Timeout=3D 40000;"/>=20
I'd really appreciate any help.=20
SunshineI don't believe there is a connection string keyword that controls command
timeouts. You need to set the command CommandTimeout property to the
desired value.
Hope this helps.
Dan Guzman
SQL Server MVP
<sunshinevaldes@.yahoo.com> wrote in message
news:1145276070.473701.247630@.i40g2000cwc.googlegroups.com...
Hello all and thank you for your time.
I have an application with reports taking over 30 seconds to run. This
is producing the
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding." error. Is there a way to
increase the timeout expired default of 30 seconds until I have
resources to optimize or rewrite the querys? There are over 120
reports and querys!
I've tried:
<httpRuntime executionTimeout="1800"/>
<add key="XYZ"
value=" ;Server=server01;UID=sa;PASSWORD=passwor
d;DATABASE=database;Connecti_
on
Timeout= 40000;"/>
I'd really appreciate any help.
Sunshine|||Wouldn't I have to do the CommandTimeout for every call to the server?
If so that would be over a hundred so I was hoping there was something
in the web.config I could tweak. I did set the query timeout on the
server to 0 but that didn't help either.
Sunshine|||the connection timeout in your connection string indicates the timeout
for the connection to be formed, not for the actual query to complete.
This means that if for some reason it was having trouble connecting to
the database server, it would wait that long before reporting an error.
As Dan said you will need to set your CommandTimeout property in your
SQL command (I hope you've centralised your data access code or you're
in for a lot of code changes). But you should also make sure you remove
that absurd level of timeout from your connection string.
Cheers
Will|||sunshinevaldes@.yahoo.com wrote:
> Hello all and thank you for your time.
> I have an application with reports taking over 30 seconds to run.
> This
> is producing the
> "Timeout expired. The timeout period elapsed prior to completion of
> the
> operation or the server is not responding." error. Is there a way to
> increase the timeout expired default of 30 seconds until I have
> resources to optimize or rewrite the querys? There are over 120
> reports and querys!
>
> I've tried:
> <httpRuntime executionTimeout="1800"/>
> <add key="XYZ"
> value=" ;Server=server01;UID=sa;PASSWORD=passwor
d;DATABASE=database;Connect
i_on
> Timeout= 40000;"/>
>
> I'd really appreciate any help.
>
This is slightly off-topic here:
Come to this group if you want help in reducing the time it takes for your
queries to run (query optimization).
For help with client applications utilizing your queries, go to the
newsgroup that is focussed on your client application: in this case,
microsoft.public.dotnet.framework.aspnet.
My recommendation is to optimize your queries so that they don't take so
long to run. My suggestion for the interim would be to use IIS Manager to
increase the Script Timeout setting for your application. You can increase
the setting on a per-page basis by using the Server.ScriptTimeout property.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||you would indeed have to set it for every call to the server. Hence my
comment about centralising data access code.
It is not possible to set this in the web.config.|||Unfortunately, the data access code is not centralized. This
application was written by consultants who say they can't fix the timed
out issue. I was hoping there was an easy fix until I can fix their
queryies.
Thanks for all your responses.
Sunshine|||Bob,
I went to microsoft.public.dotnet.framework.aspnet first but after
almost of w with no replies I came here and have had great replies
within the same day.
Can you tell me how the increase the Script Timeout in the IIS Manager?
Sunshine|||(sunshinevaldes@.yahoo.com) writes:
> Unfortunately, the data access code is not centralized. This
> application was written by consultants who say they can't fix the timed
> out issue.
Hm, if they don't enough about data-access programming to address
timeout issues properly, who says that you should be able to fix
their invoices? :-)
> I was hoping there was an easy fix until I can fix their queryies.
On SQL Server level there isn't, because this completely idiotic
default timeout of 30 seconds is set in the client API, and all SQL Server
sees is the cancellation request.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
I have an application with reports taking over 30 seconds to run. This
is producing the
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding." error. Is there a way to
increase the timeout expired default of 30 seconds until I have
resources to optimize or rewrite the querys? There are over 120
reports and querys!
I've tried:
<httpRuntime executionTimeout=3D"1800"/>
<add key=3D"XYZ"
value=3D" ;Server=3Dserver01;UID=3Dsa;PASSWORD=3Dp
assword;DATABASE=3Ddatabas=
e;Connecti=ADon
Timeout=3D 40000;"/>=20
I'd really appreciate any help.=20
SunshineI don't believe there is a connection string keyword that controls command
timeouts. You need to set the command CommandTimeout property to the
desired value.
Hope this helps.
Dan Guzman
SQL Server MVP
<sunshinevaldes@.yahoo.com> wrote in message
news:1145276070.473701.247630@.i40g2000cwc.googlegroups.com...
Hello all and thank you for your time.
I have an application with reports taking over 30 seconds to run. This
is producing the
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding." error. Is there a way to
increase the timeout expired default of 30 seconds until I have
resources to optimize or rewrite the querys? There are over 120
reports and querys!
I've tried:
<httpRuntime executionTimeout="1800"/>
<add key="XYZ"
value=" ;Server=server01;UID=sa;PASSWORD=passwor
d;DATABASE=database;Connecti_
on
Timeout= 40000;"/>
I'd really appreciate any help.
Sunshine|||Wouldn't I have to do the CommandTimeout for every call to the server?
If so that would be over a hundred so I was hoping there was something
in the web.config I could tweak. I did set the query timeout on the
server to 0 but that didn't help either.
Sunshine|||the connection timeout in your connection string indicates the timeout
for the connection to be formed, not for the actual query to complete.
This means that if for some reason it was having trouble connecting to
the database server, it would wait that long before reporting an error.
As Dan said you will need to set your CommandTimeout property in your
SQL command (I hope you've centralised your data access code or you're
in for a lot of code changes). But you should also make sure you remove
that absurd level of timeout from your connection string.
Cheers
Will|||sunshinevaldes@.yahoo.com wrote:
> Hello all and thank you for your time.
> I have an application with reports taking over 30 seconds to run.
> This
> is producing the
> "Timeout expired. The timeout period elapsed prior to completion of
> the
> operation or the server is not responding." error. Is there a way to
> increase the timeout expired default of 30 seconds until I have
> resources to optimize or rewrite the querys? There are over 120
> reports and querys!
>
> I've tried:
> <httpRuntime executionTimeout="1800"/>
> <add key="XYZ"
> value=" ;Server=server01;UID=sa;PASSWORD=passwor
d;DATABASE=database;Connect
i_on
> Timeout= 40000;"/>
>
> I'd really appreciate any help.
>
This is slightly off-topic here:
Come to this group if you want help in reducing the time it takes for your
queries to run (query optimization).
For help with client applications utilizing your queries, go to the
newsgroup that is focussed on your client application: in this case,
microsoft.public.dotnet.framework.aspnet.
My recommendation is to optimize your queries so that they don't take so
long to run. My suggestion for the interim would be to use IIS Manager to
increase the Script Timeout setting for your application. You can increase
the setting on a per-page basis by using the Server.ScriptTimeout property.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||you would indeed have to set it for every call to the server. Hence my
comment about centralising data access code.
It is not possible to set this in the web.config.|||Unfortunately, the data access code is not centralized. This
application was written by consultants who say they can't fix the timed
out issue. I was hoping there was an easy fix until I can fix their
queryies.
Thanks for all your responses.
Sunshine|||Bob,
I went to microsoft.public.dotnet.framework.aspnet first but after
almost of w
with no replies I came here and have had great replieswithin the same day.
Can you tell me how the increase the Script Timeout in the IIS Manager?
Sunshine|||(sunshinevaldes@.yahoo.com) writes:
> Unfortunately, the data access code is not centralized. This
> application was written by consultants who say they can't fix the timed
> out issue.
Hm, if they don't enough about data-access programming to address
timeout issues properly, who says that you should be able to fix
their invoices? :-)
> I was hoping there was an easy fix until I can fix their queryies.
On SQL Server level there isn't, because this completely idiotic
default timeout of 30 seconds is set in the client API, and all SQL Server
sees is the cancellation request.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Query Timeout in SSAS
I am using Sql 2005 SSAS. When building the Data Source Views using "Edit Named Query", the data source is pointed to a Sql 7.0 server. When I tried to execute the query, it terminates after 30 seconds with "Query Timeout Expired".
Try editing the DataSource object for your SQL 7.0.
You should be able to change query timeout. I beleive it is part of the connection string.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Query timeout expired on Analysis 2005
Hello
During deployment process of large db ~200M rows I got the error
Error 1 OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00
The Analysis 2005 "data source view" connected to mssql 2000 that select the 200M rows from view.
Please advice what to do.
Thanks
Amir
If you are using the Enterprise Edition of SSAS2005 have a look at partions. You can divide cubes by using partitions and select different time periods for each partition.
With these you do not have to load all data at once.
Regards
Thomas Ivarsson
query timeout expired
Using VB, I am running a bulk insert query from csv file into a newly created table. It works fine on small test files; but when I try it on the production data, I get a "query timeout expired" message and processing ends. The text files contain several hundred thousand lines.
How can I resolve this problem. I have several hundred of these csv files and more coming.
Here's the code:
Dim sSQL As String
sSQL = "BULK INSERT " & TableName & " "
sSQL = sSQL & "FROM '" & DataPath & "' WITH "
sSQL = sSQL & "(FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2)"
DbConn.Execute sSQL
I commented out the DbConn.Execute sSQL and discovered a different error which is probably causing the timeout. Prior to the bulk insert, I check if the table exists, drop it if it does and then do the bulk insert. So now I'm getting the message:
Invalid object name 'XLDS_ds1'
The XLDS_ds1 is the table name. I was able to create the table originally so how can it be invalid? Here's the SQL code assigned to a string
sSQLExists = "IF OBJECT_ID(N'ImEx.dbo." & TableName & "', N'U') IS NOT NULL"
sSQLExists = sSQLExists & " DROP TABLE ImEx.dbo." & TableName & ";"
Still can't get past the invalid object name. Since the table does not currently exist (apparently it was dropped at some point), I commented out the sSQLExists code and tried executing just the sSQL code in the original post. Still getting query timeout expired.
BTW, I ran the following query in MSSMS and got a success message but the table was NOT dropped. Anybody have a clue as to what is happenning?
IF OBJECT_ID (N'ImEx.dbo.XLDS_ds1', N'U') IS NOT NULL DROP TABLE ImEx.dbo.XLDS_ds1;
Command(s) completed successfully.
query timeout expired
I am performing a update to 2m+ records, when it reaches 200 records it
times out ?
rsobj = db.execute("select * from Somefile where t2 is null;")
do while
db.commantimeout = 0
******** newexp & newfactor are calculated *****
SQLLine = "UPDATE Australia..InProgress SET t2 = '" & NewExp & "',t3
='" & NewFactor & "' where t1 = '" & business & "';"
DBobj.Execute(SQLLine)
Loop
When I monitor the current processors they are all awaitting commandHi
Define timeout value on an application level. Set it to default
"Tlink" <Tlink@.online.nospam> wrote in message
news:%23kBqCteWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>I am performing a update to 2m+ records, when it reaches 200 records it
>times out ?
> rsobj = db.execute("select * from Somefile where t2 is null;")
> do while
> db.commantimeout = 0
> ******** newexp & newfactor are calculated *****
> SQLLine = "UPDATE Australia..InProgress SET t2 = '" & NewExp & "',t3
> ='" & NewFactor & "' where t1 = '" & business & "';"
> DBobj.Execute(SQLLine)
> Loop
> When I monitor the current processors they are all awaitting command
>|||I am unsure as to what this means and how to do it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uV6ceweWGHA.3864@.TK2MSFTNGP04.phx.gbl...
> Hi
> Define timeout value on an application level. Set it to default
>
>
> "Tlink" <Tlink@.online.nospam> wrote in message
> news:%23kBqCteWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>|||Tlink
Set cnAdo = New ADODB.Connection
strConnect = "driver={SQL
Server};uid=...;pwd=...;server=..;database=....;Network=dbmssocn"
cnAdo.Provider = "SQLOLEDB"
cnAdo.ConnectionString = strConnect
cnAdo.CommandTimeout = 0--or what do you have here?
cnAdo.CursorLocation = adUseServer
cnAdo.Mode = adModeRead
cnAdo.Open
"Tlink" <Tlink@.online.nospam> wrote in message
news:uUUdQ4eWGHA.3624@.TK2MSFTNGP04.phx.gbl...
>I am unsure as to what this means and how to do it.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uV6ceweWGHA.3864@.TK2MSFTNGP04.phx.gbl...
>|||try this
SELECT subsnp_id,pop_id,allele_id
FROM AlleleFreqBySsPop as A,
(SELECT Omim_No
FROM av
WHERE Description LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM cs
WHERE CS_Description LIKE '%LIVER%'
OR CS_DATA LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM ti
WHERE Omim_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM ti_alt_title
WHERE Omim_Alt_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM tx
WHERE Omim_Text LIKE '%LIVER%' ) as B
WHERE A.source LIKE '%' + cast(B.Omim_no as varchar) + '%'|||Hi Omnibuzz
I think you are
about this thread. See ,what does the OP ask for ?
:-))))))))))))))))
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4AF72CF7-A02E-423A-BF50-A1A95EF10D38@.microsoft.com...
> try this
> SELECT subsnp_id,pop_id,allele_id
> FROM AlleleFreqBySsPop as A,
> (SELECT Omim_No
> FROM av
> WHERE Description LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM cs
> WHERE CS_Description LIKE '%LIVER%'
> OR CS_DATA LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM ti
> WHERE Omim_Titles LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM ti_alt_title
> WHERE Omim_Alt_Titles LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM tx
> WHERE Omim_Text LIKE '%LIVER%' ) as B
> WHERE A.source LIKE '%' + cast(B.Omim_no as varchar) + '%'
>|||Oops.. sorry wrong number :)
"Uri Dimant" wrote:
> Hi Omnibuzz
> I think you are about this thread. See ,what does the OP ask for
?
> :-))))))))))))))))
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:4AF72CF7-A02E-423A-BF50-A1A95EF10D38@.microsoft.com...
>
>|||Wrong query, too. :) Check out the correct post.
ML
http://milambda.blogspot.com/|||Guess I better take a break for sometime :) Thanks for pointing it out.|||You'll feel better after a peaceful wend. :)
ML
http://milambda.blogspot.com/
times out ?
rsobj = db.execute("select * from Somefile where t2 is null;")
do while
db.commantimeout = 0
******** newexp & newfactor are calculated *****
SQLLine = "UPDATE Australia..InProgress SET t2 = '" & NewExp & "',t3
='" & NewFactor & "' where t1 = '" & business & "';"
DBobj.Execute(SQLLine)
Loop
When I monitor the current processors they are all awaitting commandHi
Define timeout value on an application level. Set it to default
"Tlink" <Tlink@.online.nospam> wrote in message
news:%23kBqCteWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>I am performing a update to 2m+ records, when it reaches 200 records it
>times out ?
> rsobj = db.execute("select * from Somefile where t2 is null;")
> do while
> db.commantimeout = 0
> ******** newexp & newfactor are calculated *****
> SQLLine = "UPDATE Australia..InProgress SET t2 = '" & NewExp & "',t3
> ='" & NewFactor & "' where t1 = '" & business & "';"
> DBobj.Execute(SQLLine)
> Loop
> When I monitor the current processors they are all awaitting command
>|||I am unsure as to what this means and how to do it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uV6ceweWGHA.3864@.TK2MSFTNGP04.phx.gbl...
> Hi
> Define timeout value on an application level. Set it to default
>
>
> "Tlink" <Tlink@.online.nospam> wrote in message
> news:%23kBqCteWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>|||Tlink
Set cnAdo = New ADODB.Connection
strConnect = "driver={SQL
Server};uid=...;pwd=...;server=..;database=....;Network=dbmssocn"
cnAdo.Provider = "SQLOLEDB"
cnAdo.ConnectionString = strConnect
cnAdo.CommandTimeout = 0--or what do you have here?
cnAdo.CursorLocation = adUseServer
cnAdo.Mode = adModeRead
cnAdo.Open
"Tlink" <Tlink@.online.nospam> wrote in message
news:uUUdQ4eWGHA.3624@.TK2MSFTNGP04.phx.gbl...
>I am unsure as to what this means and how to do it.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uV6ceweWGHA.3864@.TK2MSFTNGP04.phx.gbl...
>|||try this
SELECT subsnp_id,pop_id,allele_id
FROM AlleleFreqBySsPop as A,
(SELECT Omim_No
FROM av
WHERE Description LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM cs
WHERE CS_Description LIKE '%LIVER%'
OR CS_DATA LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM ti
WHERE Omim_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM ti_alt_title
WHERE Omim_Alt_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
UNION ALL
SELECT Omim_No
FROM tx
WHERE Omim_Text LIKE '%LIVER%' ) as B
WHERE A.source LIKE '%' + cast(B.Omim_no as varchar) + '%'|||Hi Omnibuzz
I think you are
about this thread. See ,what does the OP ask for ?:-))))))))))))))))
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4AF72CF7-A02E-423A-BF50-A1A95EF10D38@.microsoft.com...
> try this
> SELECT subsnp_id,pop_id,allele_id
> FROM AlleleFreqBySsPop as A,
> (SELECT Omim_No
> FROM av
> WHERE Description LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM cs
> WHERE CS_Description LIKE '%LIVER%'
> OR CS_DATA LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM ti
> WHERE Omim_Titles LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM ti_alt_title
> WHERE Omim_Alt_Titles LIKE '%LIVER%'
> ORDER BY Omim_No ASC
> UNION ALL
> SELECT Omim_No
> FROM tx
> WHERE Omim_Text LIKE '%LIVER%' ) as B
> WHERE A.source LIKE '%' + cast(B.Omim_no as varchar) + '%'
>|||Oops.. sorry wrong number :)
"Uri Dimant" wrote:
> Hi Omnibuzz
> I think you are
about this thread. See ,what does the OP ask for ?
> :-))))))))))))))))
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:4AF72CF7-A02E-423A-BF50-A1A95EF10D38@.microsoft.com...
>
>|||Wrong query, too. :) Check out the correct post.
ML
http://milambda.blogspot.com/|||Guess I better take a break for sometime :) Thanks for pointing it out.|||You'll feel better after a peaceful w
end. :)ML
http://milambda.blogspot.com/
Query timeout
Now that I've got some sort of DB back, I've come up against an old
problem that my new friends in this group might be able to help me with
:-)
I tend to get a lot of
Fatal error: Maximum execution time of 30 seconds exceeded in
c:\inetpub\wwwroot\syspro\daily_status_bud.php on line 117
in my php scripts, and also in enterprise manager when running certain
(simple) queries. I've got a pretty big db to be fair, about 6 gig,
but is this just a lack of resources? Should I just be throwing memory
at this, or could there be a more nefarious reason for time-outs?
This generally happens when the server is busy, so I'm very much
leaning to a resources and processing beef problem but just wondered if
a more experienced MSSQL mind might think otherwise.
Many thanks
Chris Weston
Check for blocking
Are your statistics up to date?
How about fragmentation of your tables
Are your queries Sargable? Are there a lot of Table or Index scans
instead of Index seeks?
Increase the timeout to 1 minute if you need
This is just a start there is a lot more that you can do
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142955662.623106.137670@.g10g2000cwb.googlegr oups.com...
> Check for blocking
Generally I'm not getting many blocking transactions. It does happen
occasionally but I know what to look for.
> Are your statistics up to date?
It's set to auto-update. I guess so, in that case. Is this a big overhead?
Could I do it overnight instead?
> How about fragmentation of your tables
Good question. I've no idea. How would I find out?
> Are your queries Sargable? Are there a lot of Table or Index scans
> instead of Index seeks?
Again, this is something I'm going to have to google. I wouldn't know
whether my queries use the indexes efficiently or not.
> Increase the timeout to 1 minute if you need
That would be nice, but I can't find where to do that.
> This is just a start there is a lot more that you can do
The difference between what I know and what I don't know is pretty enormous.
I can only hope to narrow the gap.
That's already a lot of help, thanks.
Chris Weston
|||Check out query wait option from the sp_configure.
problem that my new friends in this group might be able to help me with
:-)
I tend to get a lot of
Fatal error: Maximum execution time of 30 seconds exceeded in
c:\inetpub\wwwroot\syspro\daily_status_bud.php on line 117
in my php scripts, and also in enterprise manager when running certain
(simple) queries. I've got a pretty big db to be fair, about 6 gig,
but is this just a lack of resources? Should I just be throwing memory
at this, or could there be a more nefarious reason for time-outs?
This generally happens when the server is busy, so I'm very much
leaning to a resources and processing beef problem but just wondered if
a more experienced MSSQL mind might think otherwise.
Many thanks
Chris Weston
Check for blocking
Are your statistics up to date?
How about fragmentation of your tables
Are your queries Sargable? Are there a lot of Table or Index scans
instead of Index seeks?
Increase the timeout to 1 minute if you need
This is just a start there is a lot more that you can do
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142955662.623106.137670@.g10g2000cwb.googlegr oups.com...
> Check for blocking
Generally I'm not getting many blocking transactions. It does happen
occasionally but I know what to look for.
> Are your statistics up to date?
It's set to auto-update. I guess so, in that case. Is this a big overhead?
Could I do it overnight instead?
> How about fragmentation of your tables
Good question. I've no idea. How would I find out?
> Are your queries Sargable? Are there a lot of Table or Index scans
> instead of Index seeks?
Again, this is something I'm going to have to google. I wouldn't know
whether my queries use the indexes efficiently or not.
> Increase the timeout to 1 minute if you need
That would be nice, but I can't find where to do that.
> This is just a start there is a lot more that you can do
The difference between what I know and what I don't know is pretty enormous.
I can only hope to narrow the gap.
That's already a lot of help, thanks.
Chris Weston
|||Check out query wait option from the sp_configure.
Query timeout
Now that I've got some sort of DB back, I've come up against an old
problem that my new friends in this group might be able to help me with
:-)
I tend to get a lot of
Fatal error: Maximum execution time of 30 seconds exceeded in
c:\inetpub\wwwroot\syspro\daily_status_b
ud.php on line 117
in my php scripts, and also in enterprise manager when running certain
(simple) queries. I've got a pretty big db to be fair, about 6 gig,
but is this just a lack of resources? Should I just be throwing memory
at this, or could there be a more nefarious reason for time-outs?
This generally happens when the server is busy, so I'm very much
leaning to a resources and processing beef problem but just wondered if
a more experienced MSSQL mind might think otherwise.
Many thanks
Chris WestonCheck for blocking
Are your statistics up to date?
How about fragmentation of your tables
Are your queries Sargable? Are there a lot of Table or Index scans
instead of Index seeks?
Increase the timeout to 1 minute if you need
This is just a start there is a lot more that you can do
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142955662.623106.137670@.g10g2000cwb.googlegroups.com...
> Check for blocking
Generally I'm not getting many blocking transactions. It does happen
occasionally but I know what to look for.
> Are your statistics up to date?
It's set to auto-update. I guess so, in that case. Is this a big overhead?
Could I do it overnight instead?
> How about fragmentation of your tables
Good question. I've no idea. How would I find out?
> Are your queries Sargable? Are there a lot of Table or Index scans
> instead of Index seeks?
Again, this is something I'm going to have to google. I wouldn't know
whether my queries use the indexes efficiently or not.
> Increase the timeout to 1 minute if you need
That would be nice, but I can't find where to do that.
> This is just a start there is a lot more that you can do
The difference between what I know and what I don't know is pretty enormous.
I can only hope to narrow the gap.
That's already a lot of help, thanks.
Chris Weston|||Check out query wait option from the sp_configure.
problem that my new friends in this group might be able to help me with
:-)
I tend to get a lot of
Fatal error: Maximum execution time of 30 seconds exceeded in
c:\inetpub\wwwroot\syspro\daily_status_b
ud.php on line 117
in my php scripts, and also in enterprise manager when running certain
(simple) queries. I've got a pretty big db to be fair, about 6 gig,
but is this just a lack of resources? Should I just be throwing memory
at this, or could there be a more nefarious reason for time-outs?
This generally happens when the server is busy, so I'm very much
leaning to a resources and processing beef problem but just wondered if
a more experienced MSSQL mind might think otherwise.
Many thanks
Chris WestonCheck for blocking
Are your statistics up to date?
How about fragmentation of your tables
Are your queries Sargable? Are there a lot of Table or Index scans
instead of Index seeks?
Increase the timeout to 1 minute if you need
This is just a start there is a lot more that you can do
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142955662.623106.137670@.g10g2000cwb.googlegroups.com...
> Check for blocking
Generally I'm not getting many blocking transactions. It does happen
occasionally but I know what to look for.
> Are your statistics up to date?
It's set to auto-update. I guess so, in that case. Is this a big overhead?
Could I do it overnight instead?
> How about fragmentation of your tables
Good question. I've no idea. How would I find out?
> Are your queries Sargable? Are there a lot of Table or Index scans
> instead of Index seeks?
Again, this is something I'm going to have to google. I wouldn't know
whether my queries use the indexes efficiently or not.
> Increase the timeout to 1 minute if you need
That would be nice, but I can't find where to do that.
> This is just a start there is a lot more that you can do
The difference between what I know and what I don't know is pretty enormous.
I can only hope to narrow the gap.
That's already a lot of help, thanks.
Chris Weston|||Check out query wait option from the sp_configure.
Query Timeout
I have a rather complicated query statement in a stored proc which all of a
sudden hangs in production. I copy the same database to QA and run the store
d
proc and it executes in less than a second. The explain plans are different
between production and QA. I have recompiled (sp_recompile) the stored proc
in production. Still hangs. I have run sp_updatestats in production (but tha
t
still doesn't explain why QA works fine) and it still hangs. Any ideas?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1Double-check the indexes on the production and QA boxes first to make sure
they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
production if the indexes are the same.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:64ebbf94f87e0@.uwe...
>I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the
> stored
> proc and it executes in less than a second. The explain plans are
> different
> between production and QA. I have recompiled (sp_recompile) the stored
> proc
> in production. Still hangs. I have run sp_updatestats in production (but
> that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>|||cbrichards via droptable.com wrote:
> I have a rather complicated query statement in a stored proc which all of
a
> sudden hangs in production. I copy the same database to QA and run the sto
red
> proc and it executes in less than a second. The explain plans are differen
t
> between production and QA. I have recompiled (sp_recompile) the stored pro
c
> in production. Still hangs. I have run sp_updatestats in production (but t
hat
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>
When the query "hangs", check sysprocesses to see what's blocking it...
My guess is your production server has more activity on it than the QA
server, and something is blocking your query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The indexes are the same as I copied the database to QA. I have another post
on this website that Tracy has been responding to about the DBREindex not
having any affect on my fragmented index. But there has not been a response
on that issue since posting my SHOWCONTIG results.
Mike C# wrote:[vbcol=seagreen]
>Double-check the indexes on the production and QA boxes first to make sure
>they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
>production if the indexes are the same.
>
>[quoted text clipped - 6 lines]
Message posted via http://www.droptable.com|||Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
running has not revealed any blocking. Initially I suspected that too. I am
still stumped over the lack of affect of DBReindex on my indexes another pos
t
you responded too.
Tracy McKibben wrote:
>When the query "hangs", check sysprocesses to see what's blocking it...
> My guess is your production server has more activity on it than the QA
>server, and something is blocking your query.
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> The indexes are the same as I copied the database to QA. I have another po
st
> on this website that Tracy has been responding to about the DBREindex not
> having any affect on my fragmented index. But there has not been a respons
e
> on that issue since posting my SHOWCONTIG results.
>
Actually there was a response from another poster, stating that the
index in question is too small to defragment. I didn't respond because
his answer is correct.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||cbrichards via droptable.com wrote:
> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
> running has not revealed any blocking. Initially I suspected that too. I a
m
> still stumped over the lack of affect of DBReindex on my indexes another p
ost
> you responded too.
>
There must be some clue in sysprocesses. Is there a waittype shown for
the query? Does the execution plan reveal any clues?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||When I run "select * from master.dbo.sysprocesses" while the proc is about 3
0
seconds into running (It should complete in less than a half second), I get
the following from sysprocesses:
blocked = 0
lastwaittype = PAGELATCH_SH
CPU = 261204
When I run it in production (and times out) the explain plain provided in
profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
QA does not show in its explain plan.
Tracy McKibben wrote:
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||At about 20 minutes into the proc execution I ran sysprocesses again:
Blocked = 0 (in fact all the rows for the blocked column from sysprocesses
are zero)
LastWaitType = LCK_M_S
Tracy McKibben wrote:
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> When I run "select * from master.dbo.sysprocesses" while the proc is about
30
> seconds into running (It should complete in less than a half second), I ge
t
> the following from sysprocesses:
> blocked = 0
> lastwaittype = PAGELATCH_SH
> CPU = 261204
> When I run it in production (and times out) the explain plain provided in
> profiler shows a Hash join and two Bookmark lookups that the "exact" copy
on
> QA does not show in its explain plan.
>
For the two bookmark lookups, what index is being used? Are they the
same indexes that the query uses in QA? If not, you need to determine
why it's choosing different indexes. Is the amount of data the same
between QA and prod? Try forcing the index using an index hint, see if
that improves the performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sudden hangs in production. I copy the same database to QA and run the store
d
proc and it executes in less than a second. The explain plans are different
between production and QA. I have recompiled (sp_recompile) the stored proc
in production. Still hangs. I have run sp_updatestats in production (but tha
t
still doesn't explain why QA works fine) and it still hangs. Any ideas?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1Double-check the indexes on the production and QA boxes first to make sure
they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
production if the indexes are the same.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:64ebbf94f87e0@.uwe...
>I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the
> stored
> proc and it executes in less than a second. The explain plans are
> different
> between production and QA. I have recompiled (sp_recompile) the stored
> proc
> in production. Still hangs. I have run sp_updatestats in production (but
> that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>|||cbrichards via droptable.com wrote:
> I have a rather complicated query statement in a stored proc which all of
a
> sudden hangs in production. I copy the same database to QA and run the sto
red
> proc and it executes in less than a second. The explain plans are differen
t
> between production and QA. I have recompiled (sp_recompile) the stored pro
c
> in production. Still hangs. I have run sp_updatestats in production (but t
hat
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>
When the query "hangs", check sysprocesses to see what's blocking it...
My guess is your production server has more activity on it than the QA
server, and something is blocking your query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The indexes are the same as I copied the database to QA. I have another post
on this website that Tracy has been responding to about the DBREindex not
having any affect on my fragmented index. But there has not been a response
on that issue since posting my SHOWCONTIG results.
Mike C# wrote:[vbcol=seagreen]
>Double-check the indexes on the production and QA boxes first to make sure
>they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
>production if the indexes are the same.
>
>[quoted text clipped - 6 lines]
Message posted via http://www.droptable.com|||Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
running has not revealed any blocking. Initially I suspected that too. I am
still stumped over the lack of affect of DBReindex on my indexes another pos
t
you responded too.
Tracy McKibben wrote:
>When the query "hangs", check sysprocesses to see what's blocking it...
> My guess is your production server has more activity on it than the QA
>server, and something is blocking your query.
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> The indexes are the same as I copied the database to QA. I have another po
st
> on this website that Tracy has been responding to about the DBREindex not
> having any affect on my fragmented index. But there has not been a respons
e
> on that issue since posting my SHOWCONTIG results.
>
Actually there was a response from another poster, stating that the
index in question is too small to defragment. I didn't respond because
his answer is correct.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||cbrichards via droptable.com wrote:
> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
> running has not revealed any blocking. Initially I suspected that too. I a
m
> still stumped over the lack of affect of DBReindex on my indexes another p
ost
> you responded too.
>
There must be some clue in sysprocesses. Is there a waittype shown for
the query? Does the execution plan reveal any clues?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||When I run "select * from master.dbo.sysprocesses" while the proc is about 3
0
seconds into running (It should complete in less than a half second), I get
the following from sysprocesses:
blocked = 0
lastwaittype = PAGELATCH_SH
CPU = 261204
When I run it in production (and times out) the explain plain provided in
profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
QA does not show in its explain plan.
Tracy McKibben wrote:
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||At about 20 minutes into the proc execution I ran sysprocesses again:
Blocked = 0 (in fact all the rows for the blocked column from sysprocesses
are zero)
LastWaitType = LCK_M_S
Tracy McKibben wrote:
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> When I run "select * from master.dbo.sysprocesses" while the proc is about
30
> seconds into running (It should complete in less than a half second), I ge
t
> the following from sysprocesses:
> blocked = 0
> lastwaittype = PAGELATCH_SH
> CPU = 261204
> When I run it in production (and times out) the explain plain provided in
> profiler shows a Hash join and two Bookmark lookups that the "exact" copy
on
> QA does not show in its explain plan.
>
For the two bookmark lookups, what index is being used? Are they the
same indexes that the query uses in QA? If not, you need to determine
why it's choosing different indexes. Is the amount of data the same
between QA and prod? Try forcing the index using an index hint, see if
that improves the performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Query timeout
Now that I've got some sort of DB back, I've come up against an old
problem that my new friends in this group might be able to help me with
:-)
I tend to get a lot of
Fatal error: Maximum execution time of 30 seconds exceeded in
c:\inetpub\wwwroot\syspro\daily_status_bud.php on line 117
in my php scripts, and also in enterprise manager when running certain
(simple) queries. I've got a pretty big db to be fair, about 6 gig,
but is this just a lack of resources? Should I just be throwing memory
at this, or could there be a more nefarious reason for time-outs?
This generally happens when the server is busy, so I'm very much
leaning to a resources and processing beef problem but just wondered if
a more experienced MSSQL mind might think otherwise.
Many thanks
Chris WestonCheck for blocking
Are your statistics up to date?
How about fragmentation of your tables
Are your queries Sargable? Are there a lot of Table or Index scans
instead of Index seeks?
Increase the timeout to 1 minute if you need
This is just a start there is a lot more that you can do
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142955662.623106.137670@.g10g2000cwb.googlegroups.com...
> Check for blocking
Generally I'm not getting many blocking transactions. It does happen
occasionally but I know what to look for.
> Are your statistics up to date?
It's set to auto-update. I guess so, in that case. Is this a big overhead?
Could I do it overnight instead?
> How about fragmentation of your tables
Good question. I've no idea. How would I find out?
> Are your queries Sargable? Are there a lot of Table or Index scans
> instead of Index seeks?
Again, this is something I'm going to have to google. I wouldn't know
whether my queries use the indexes efficiently or not.
> Increase the timeout to 1 minute if you need
That would be nice, but I can't find where to do that.
> This is just a start there is a lot more that you can do
The difference between what I know and what I don't know is pretty enormous.
I can only hope to narrow the gap.
That's already a lot of help, thanks.
--
Chris Weston|||Check out query wait option from the sp_configure.
problem that my new friends in this group might be able to help me with
:-)
I tend to get a lot of
Fatal error: Maximum execution time of 30 seconds exceeded in
c:\inetpub\wwwroot\syspro\daily_status_bud.php on line 117
in my php scripts, and also in enterprise manager when running certain
(simple) queries. I've got a pretty big db to be fair, about 6 gig,
but is this just a lack of resources? Should I just be throwing memory
at this, or could there be a more nefarious reason for time-outs?
This generally happens when the server is busy, so I'm very much
leaning to a resources and processing beef problem but just wondered if
a more experienced MSSQL mind might think otherwise.
Many thanks
Chris WestonCheck for blocking
Are your statistics up to date?
How about fragmentation of your tables
Are your queries Sargable? Are there a lot of Table or Index scans
instead of Index seeks?
Increase the timeout to 1 minute if you need
This is just a start there is a lot more that you can do
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142955662.623106.137670@.g10g2000cwb.googlegroups.com...
> Check for blocking
Generally I'm not getting many blocking transactions. It does happen
occasionally but I know what to look for.
> Are your statistics up to date?
It's set to auto-update. I guess so, in that case. Is this a big overhead?
Could I do it overnight instead?
> How about fragmentation of your tables
Good question. I've no idea. How would I find out?
> Are your queries Sargable? Are there a lot of Table or Index scans
> instead of Index seeks?
Again, this is something I'm going to have to google. I wouldn't know
whether my queries use the indexes efficiently or not.
> Increase the timeout to 1 minute if you need
That would be nice, but I can't find where to do that.
> This is just a start there is a lot more that you can do
The difference between what I know and what I don't know is pretty enormous.
I can only hope to narrow the gap.
That's already a lot of help, thanks.
--
Chris Weston|||Check out query wait option from the sp_configure.
Query Timeout
I have a rather complicated query statement in a stored proc which all of a
sudden hangs in production. I copy the same database to QA and run the stored
proc and it executes in less than a second. The explain plans are different
between production and QA. I have recompiled (sp_recompile) the stored proc
in production. Still hangs. I have run sp_updatestats in production (but that
still doesn't explain why QA works fine) and it still hangs. Any ideas?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1Double-check the indexes on the production and QA boxes first to make sure
they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
production if the indexes are the same.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:64ebbf94f87e0@.uwe...
>I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the
> stored
> proc and it executes in less than a second. The explain plans are
> different
> between production and QA. I have recompiled (sp_recompile) the stored
> proc
> in production. Still hangs. I have run sp_updatestats in production (but
> that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||cbrichards via SQLMonster.com wrote:
> I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the stored
> proc and it executes in less than a second. The explain plans are different
> between production and QA. I have recompiled (sp_recompile) the stored proc
> in production. Still hangs. I have run sp_updatestats in production (but that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>
When the query "hangs", check sysprocesses to see what's blocking it...
My guess is your production server has more activity on it than the QA
server, and something is blocking your query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The indexes are the same as I copied the database to QA. I have another post
on this website that Tracy has been responding to about the DBREindex not
having any affect on my fragmented index. But there has not been a response
on that issue since posting my SHOWCONTIG results.
Mike C# wrote:
>Double-check the indexes on the production and QA boxes first to make sure
>they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
>production if the indexes are the same.
>>I have a rather complicated query statement in a stored proc which all of a
>> sudden hangs in production. I copy the same database to QA and run the
>[quoted text clipped - 6 lines]
>> that
>> still doesn't explain why QA works fine) and it still hangs. Any ideas?
--
Message posted via http://www.sqlmonster.com|||Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
running has not revealed any blocking. Initially I suspected that too. I am
still stumped over the lack of affect of DBReindex on my indexes another post
you responded too.
Tracy McKibben wrote:
>> I have a rather complicated query statement in a stored proc which all of a
>> sudden hangs in production. I copy the same database to QA and run the stored
>> proc and it executes in less than a second. The explain plans are different
>> between production and QA. I have recompiled (sp_recompile) the stored proc
>> in production. Still hangs. I have run sp_updatestats in production (but that
>> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>When the query "hangs", check sysprocesses to see what's blocking it...
> My guess is your production server has more activity on it than the QA
>server, and something is blocking your query.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> The indexes are the same as I copied the database to QA. I have another post
> on this website that Tracy has been responding to about the DBREindex not
> having any affect on my fragmented index. But there has not been a response
> on that issue since posting my SHOWCONTIG results.
>
Actually there was a response from another poster, stating that the
index in question is too small to defragment. I didn't respond because
his answer is correct.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||cbrichards via SQLMonster.com wrote:
> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
> running has not revealed any blocking. Initially I suspected that too. I am
> still stumped over the lack of affect of DBReindex on my indexes another post
> you responded too.
>
There must be some clue in sysprocesses. Is there a waittype shown for
the query? Does the execution plan reveal any clues?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||When I run "select * from master.dbo.sysprocesses" while the proc is about 30
seconds into running (It should complete in less than a half second), I get
the following from sysprocesses:
blocked = 0
lastwaittype = PAGELATCH_SH
CPU = 261204
When I run it in production (and times out) the explain plain provided in
profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
QA does not show in its explain plan.
Tracy McKibben wrote:
>> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
>> running has not revealed any blocking. Initially I suspected that too. I am
>> still stumped over the lack of affect of DBReindex on my indexes another post
>> you responded too.
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||At about 20 minutes into the proc execution I ran sysprocesses again:
Blocked = 0 (in fact all the rows for the blocked column from sysprocesses
are zero)
LastWaitType = LCK_M_S
Tracy McKibben wrote:
>> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
>> running has not revealed any blocking. Initially I suspected that too. I am
>> still stumped over the lack of affect of DBReindex on my indexes another post
>> you responded too.
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> When I run "select * from master.dbo.sysprocesses" while the proc is about 30
> seconds into running (It should complete in less than a half second), I get
> the following from sysprocesses:
> blocked = 0
> lastwaittype = PAGELATCH_SH
> CPU = 261204
> When I run it in production (and times out) the explain plain provided in
> profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
> QA does not show in its explain plan.
>
For the two bookmark lookups, what index is being used? Are they the
same indexes that the query uses in QA? If not, you need to determine
why it's choosing different indexes. Is the amount of data the same
between QA and prod? Try forcing the index using an index hint, see if
that improves the performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sudden hangs in production. I copy the same database to QA and run the stored
proc and it executes in less than a second. The explain plans are different
between production and QA. I have recompiled (sp_recompile) the stored proc
in production. Still hangs. I have run sp_updatestats in production (but that
still doesn't explain why QA works fine) and it still hangs. Any ideas?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1Double-check the indexes on the production and QA boxes first to make sure
they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
production if the indexes are the same.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:64ebbf94f87e0@.uwe...
>I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the
> stored
> proc and it executes in less than a second. The explain plans are
> different
> between production and QA. I have recompiled (sp_recompile) the stored
> proc
> in production. Still hangs. I have run sp_updatestats in production (but
> that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||cbrichards via SQLMonster.com wrote:
> I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the stored
> proc and it executes in less than a second. The explain plans are different
> between production and QA. I have recompiled (sp_recompile) the stored proc
> in production. Still hangs. I have run sp_updatestats in production (but that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>
When the query "hangs", check sysprocesses to see what's blocking it...
My guess is your production server has more activity on it than the QA
server, and something is blocking your query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The indexes are the same as I copied the database to QA. I have another post
on this website that Tracy has been responding to about the DBREindex not
having any affect on my fragmented index. But there has not been a response
on that issue since posting my SHOWCONTIG results.
Mike C# wrote:
>Double-check the indexes on the production and QA boxes first to make sure
>they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
>production if the indexes are the same.
>>I have a rather complicated query statement in a stored proc which all of a
>> sudden hangs in production. I copy the same database to QA and run the
>[quoted text clipped - 6 lines]
>> that
>> still doesn't explain why QA works fine) and it still hangs. Any ideas?
--
Message posted via http://www.sqlmonster.com|||Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
running has not revealed any blocking. Initially I suspected that too. I am
still stumped over the lack of affect of DBReindex on my indexes another post
you responded too.
Tracy McKibben wrote:
>> I have a rather complicated query statement in a stored proc which all of a
>> sudden hangs in production. I copy the same database to QA and run the stored
>> proc and it executes in less than a second. The explain plans are different
>> between production and QA. I have recompiled (sp_recompile) the stored proc
>> in production. Still hangs. I have run sp_updatestats in production (but that
>> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>When the query "hangs", check sysprocesses to see what's blocking it...
> My guess is your production server has more activity on it than the QA
>server, and something is blocking your query.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> The indexes are the same as I copied the database to QA. I have another post
> on this website that Tracy has been responding to about the DBREindex not
> having any affect on my fragmented index. But there has not been a response
> on that issue since posting my SHOWCONTIG results.
>
Actually there was a response from another poster, stating that the
index in question is too small to defragment. I didn't respond because
his answer is correct.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||cbrichards via SQLMonster.com wrote:
> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
> running has not revealed any blocking. Initially I suspected that too. I am
> still stumped over the lack of affect of DBReindex on my indexes another post
> you responded too.
>
There must be some clue in sysprocesses. Is there a waittype shown for
the query? Does the execution plan reveal any clues?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||When I run "select * from master.dbo.sysprocesses" while the proc is about 30
seconds into running (It should complete in less than a half second), I get
the following from sysprocesses:
blocked = 0
lastwaittype = PAGELATCH_SH
CPU = 261204
When I run it in production (and times out) the explain plain provided in
profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
QA does not show in its explain plan.
Tracy McKibben wrote:
>> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
>> running has not revealed any blocking. Initially I suspected that too. I am
>> still stumped over the lack of affect of DBReindex on my indexes another post
>> you responded too.
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||At about 20 minutes into the proc execution I ran sysprocesses again:
Blocked = 0 (in fact all the rows for the blocked column from sysprocesses
are zero)
LastWaitType = LCK_M_S
Tracy McKibben wrote:
>> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
>> running has not revealed any blocking. Initially I suspected that too. I am
>> still stumped over the lack of affect of DBReindex on my indexes another post
>> you responded too.
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> When I run "select * from master.dbo.sysprocesses" while the proc is about 30
> seconds into running (It should complete in less than a half second), I get
> the following from sysprocesses:
> blocked = 0
> lastwaittype = PAGELATCH_SH
> CPU = 261204
> When I run it in production (and times out) the explain plain provided in
> profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
> QA does not show in its explain plan.
>
For the two bookmark lookups, what index is being used? Are they the
same indexes that the query uses in QA? If not, you need to determine
why it's choosing different indexes. Is the amount of data the same
between QA and prod? Try forcing the index using an index hint, see if
that improves the performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Query Timeout
Hello,
Our system has experienced time out issues with on of the SQL Severs. We
have an ASP.NET application that calls a stored proc, which returns a result
set. This stored proc all of a sudden started to time out on Friday after
noon. I was able to execute the same procedure in Query Analyzer without any
problems. The timeout for My ASP.NET is 30 seconds and the query was
executing the Query Analyzer in less than 30 seconds.
The solution I found out for this is to update statistics on one of the
databases. The stored proc has a join with a table in different database
sitting on the same server. When I updated the statistics of this database
the ASP.NET app started behave normally. This time I did not run the update
stats, instead I was thinking to do more research this morning. To my
surprise, the time out issue went away and ASP.NET is working absolutely
fine. What I found is that the SQL server might have been rebooted this
weekend.
Now I am scratching my head and trying to figure out what could be the
problem. What is it'
I appreciate any comments.
ThanksIf you have SQL Server configured to auto update stats, it may have done an
update itself which improved the stats and allowed it to create a good query
plan again.
My experience has been that auto update is not reliable enough, so we
schedule update statistics ... with fullscan against all tables across the
course of a week.
--
Scott Nichol
<Srini> wrote in message news:eRWM0U7pDHA.1488@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> Our system has experienced time out issues with on of the SQL Severs. We
> have an ASP.NET application that calls a stored proc, which returns a
result
> set. This stored proc all of a sudden started to time out on Friday after
> noon. I was able to execute the same procedure in Query Analyzer without
any
> problems. The timeout for My ASP.NET is 30 seconds and the query was
> executing the Query Analyzer in less than 30 seconds.
>
> The solution I found out for this is to update statistics on one of the
> databases. The stored proc has a join with a table in different database
> sitting on the same server. When I updated the statistics of this database
> the ASP.NET app started behave normally. This time I did not run the
update
> stats, instead I was thinking to do more research this morning. To my
> surprise, the time out issue went away and ASP.NET is working absolutely
> fine. What I found is that the SQL server might have been rebooted this
> weekend.
>
> Now I am scratching my head and trying to figure out what could be the
> problem. What is it'
> I appreciate any comments.
>
> Thanks
>
Our system has experienced time out issues with on of the SQL Severs. We
have an ASP.NET application that calls a stored proc, which returns a result
set. This stored proc all of a sudden started to time out on Friday after
noon. I was able to execute the same procedure in Query Analyzer without any
problems. The timeout for My ASP.NET is 30 seconds and the query was
executing the Query Analyzer in less than 30 seconds.
The solution I found out for this is to update statistics on one of the
databases. The stored proc has a join with a table in different database
sitting on the same server. When I updated the statistics of this database
the ASP.NET app started behave normally. This time I did not run the update
stats, instead I was thinking to do more research this morning. To my
surprise, the time out issue went away and ASP.NET is working absolutely
fine. What I found is that the SQL server might have been rebooted this
weekend.
Now I am scratching my head and trying to figure out what could be the
problem. What is it'
I appreciate any comments.
ThanksIf you have SQL Server configured to auto update stats, it may have done an
update itself which improved the stats and allowed it to create a good query
plan again.
My experience has been that auto update is not reliable enough, so we
schedule update statistics ... with fullscan against all tables across the
course of a week.
--
Scott Nichol
<Srini> wrote in message news:eRWM0U7pDHA.1488@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> Our system has experienced time out issues with on of the SQL Severs. We
> have an ASP.NET application that calls a stored proc, which returns a
result
> set. This stored proc all of a sudden started to time out on Friday after
> noon. I was able to execute the same procedure in Query Analyzer without
any
> problems. The timeout for My ASP.NET is 30 seconds and the query was
> executing the Query Analyzer in less than 30 seconds.
>
> The solution I found out for this is to update statistics on one of the
> databases. The stored proc has a join with a table in different database
> sitting on the same server. When I updated the statistics of this database
> the ASP.NET app started behave normally. This time I did not run the
update
> stats, instead I was thinking to do more research this morning. To my
> surprise, the time out issue went away and ASP.NET is working absolutely
> fine. What I found is that the SQL server might have been rebooted this
> weekend.
>
> Now I am scratching my head and trying to figure out what could be the
> problem. What is it'
> I appreciate any comments.
>
> Thanks
>
Query Timeout
I have a query I run in MSDE that will give me a timeout error message. It
reads:
"Timeout expired. The time out period elapsed prior to completion of the
operation or the server is not responding."
I know the server is responding, because the query seems to run anyway,
though I'm not sure the results are accurate. Is there any way to extend the
timeout period so this does not happen. The query running is adding
numerical records to one table based on criteria in the query and values in
another table.
What application are you using to query the database? If it's something your
wrote in-house and you are using ADO then set the CommandTimeout property of
the connection to 0 (zero). Some ADO libraries default to 30 seconds for a
timeout.
Jim
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:B8EFC32E-54F0-4CBB-A2F9-36699FA895B6@.microsoft.com...
> I have a query I run in MSDE that will give me a timeout error message.
It
> reads:
> "Timeout expired. The time out period elapsed prior to completion of the
> operation or the server is not responding."
> I know the server is responding, because the query seems to run anyway,
> though I'm not sure the results are accurate. Is there any way to extend
the
> timeout period so this does not happen. The query running is adding
> numerical records to one table based on criteria in the query and values
in
> another table.
reads:
"Timeout expired. The time out period elapsed prior to completion of the
operation or the server is not responding."
I know the server is responding, because the query seems to run anyway,
though I'm not sure the results are accurate. Is there any way to extend the
timeout period so this does not happen. The query running is adding
numerical records to one table based on criteria in the query and values in
another table.
What application are you using to query the database? If it's something your
wrote in-house and you are using ADO then set the CommandTimeout property of
the connection to 0 (zero). Some ADO libraries default to 30 seconds for a
timeout.
Jim
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:B8EFC32E-54F0-4CBB-A2F9-36699FA895B6@.microsoft.com...
> I have a query I run in MSDE that will give me a timeout error message.
It
> reads:
> "Timeout expired. The time out period elapsed prior to completion of the
> operation or the server is not responding."
> I know the server is responding, because the query seems to run anyway,
> though I'm not sure the results are accurate. Is there any way to extend
the
> timeout period so this does not happen. The query running is adding
> numerical records to one table based on criteria in the query and values
in
> another table.
Query Timeout
Hi there,
I have a java application which connects to an sqlServer database and issues
an SQL update statement.
When you attempt to connect to the database if there is no connection then
this is fine, it will retry for x times and timeout at the time you have
set.
The problem is if you have made a valid connection and then disconnect the
database from the network and try to run the sql UPDATE. The update will
only attempt one time and basically hang until the connection is
re-established.
The other way round, if the machine the java application is running on is
disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
times and times out at the interval set.
It only seems to hang if the java application is still connected but the
database is disconnected. Is this a general problem with the jdbc driver?
Regards
Jamie
Jamie wrote:
> Hi there,
> I have a java application which connects to an sqlServer database and issues
> an SQL update statement.
> When you attempt to connect to the database if there is no connection then
> this is fine, it will retry for x times and timeout at the time you have
> set.
> The problem is if you have made a valid connection and then disconnect the
> database from the network and try to run the sql UPDATE. The update will
> only attempt one time and basically hang until the connection is
> re-established.
> The other way round, if the machine the java application is running on is
> disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
> times and times out at the interval set.
> It only seems to hang if the java application is still connected but the
> database is disconnected. Is this a general problem with the jdbc driver?
> Regards
> Jamie
It's at a lower level than that. The failure you cause means the TCP stack will
take minutes before it notifies the driver that the socket is dead. You might
try setting the query timeout on your statement before executing it. Then the
driver may be able to return control to you sooner.
Joe Weinstein at BEA
I have a java application which connects to an sqlServer database and issues
an SQL update statement.
When you attempt to connect to the database if there is no connection then
this is fine, it will retry for x times and timeout at the time you have
set.
The problem is if you have made a valid connection and then disconnect the
database from the network and try to run the sql UPDATE. The update will
only attempt one time and basically hang until the connection is
re-established.
The other way round, if the machine the java application is running on is
disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
times and times out at the interval set.
It only seems to hang if the java application is still connected but the
database is disconnected. Is this a general problem with the jdbc driver?
Regards
Jamie
Jamie wrote:
> Hi there,
> I have a java application which connects to an sqlServer database and issues
> an SQL update statement.
> When you attempt to connect to the database if there is no connection then
> this is fine, it will retry for x times and timeout at the time you have
> set.
> The problem is if you have made a valid connection and then disconnect the
> database from the network and try to run the sql UPDATE. The update will
> only attempt one time and basically hang until the connection is
> re-established.
> The other way round, if the machine the java application is running on is
> disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
> times and times out at the interval set.
> It only seems to hang if the java application is still connected but the
> database is disconnected. Is this a general problem with the jdbc driver?
> Regards
> Jamie
It's at a lower level than that. The failure you cause means the TCP stack will
take minutes before it notifies the driver that the socket is dead. You might
try setting the query timeout on your statement before executing it. Then the
driver may be able to return control to you sooner.
Joe Weinstein at BEA
Query Timeout
Hi,
I want to increase query timeout value but I don't know how to do this.
Please
advise me where to change query timeout value. Is it possible to set this
for a
particular database?
Thanks,
Soura.
Hi Sounder,
I think we can increase the Timeout for a whole server(all DB's in that
Server)
Right click on the server name => Select properties => Choose the
Connections tab => Increase the query timeout value. (The value is in
seconds. Set it to 0 for unlimited time) => Click OK.
Regards,
Herbert
"SouRa" wrote:
> Hi,
> I want to increase query timeout value but I don't know how to do this.
> Please
> advise me where to change query timeout value. Is it possible to set this
> for a
> particular database?
> Thanks,
> Soura.
>
|||Hi Herbert,
Thanks for your reply. There is an option in replication job to change the
number of attempts to connect subscriber with some specified time interval.
We can modify the information. Similarly is there any option to change query
time out (in any replication jobs,subscription properties or any where else)?
Thanks,
Soura.
"Herbert" wrote:
[vbcol=seagreen]
> Hi Sounder,
> I think we can increase the Timeout for a whole server(all DB's in that
> Server)
> Right click on the server name => Select properties => Choose the
> Connections tab => Increase the query timeout value. (The value is in
> seconds. Set it to 0 for unlimited time) => Click OK.
>
> Regards,
> Herbert
>
> "SouRa" wrote:
|||Hi Sounder,
I think u need to increase the timeout of merge agent....
Open the Replication monitor in that choose the Merge agent => Right-click
appropriate publication and select Agent Profiles... => Click the New Profile
button to create the new profile with the appropriate QueryTimeout value =>
Choose the newly created profile.
Hope this will help..
Regards,
Herbert
"SouRa" wrote:
[vbcol=seagreen]
> Hi Herbert,
> Thanks for your reply. There is an option in replication job to change the
> number of attempts to connect subscriber with some specified time interval.
> We can modify the information. Similarly is there any option to change query
> time out (in any replication jobs,subscription properties or any where else)?
> Thanks,
> Soura.
> "Herbert" wrote:
|||Hi Herbert,
Thanks for your reply. Actually I getting time out error in my application
frequently.
So I decided to increase the query time out value. I found a profile High
volume server to server profile (in merge agent profiles), any idea about
this?
Thanks,
Soura.
"Herbert" wrote:
[vbcol=seagreen]
> Hi Sounder,
> I think u need to increase the timeout of merge agent....
> Open the Replication monitor in that choose the Merge agent => Right-click
> appropriate publication and select Agent Profiles... => Click the New Profile
> button to create the new profile with the appropriate QueryTimeout value =>
> Choose the newly created profile.
> Hope this will help..
> Regards,
> Herbert
>
> "SouRa" wrote:
|||Hi Sounder,
Since ur getting timeout in application, i think u have to increase the
query timeout of Server instead of replication agent.
by my previous post step u can set the query timeout of Server.
Regards,
Herbert
"SouRa" wrote:
[vbcol=seagreen]
> Hi Herbert,
> Thanks for your reply. Actually I getting time out error in my application
> frequently.
> So I decided to increase the query time out value. I found a profile High
> volume server to server profile (in merge agent profiles), any idea about
> this?
> Thanks,
> Soura.
> "Herbert" wrote:
|||SouRa, rather than adjusting the query timeout setting, why dont you try to
figure out why the queries are taking so long?
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:14E41307-124F-40A1-A1ED-97E4E9DC7B81@.microsoft.com...[vbcol=seagreen]
> Hi Herbert,
> Thanks for your reply. Actually I getting time out error in my application
> frequently.
> So I decided to increase the query time out value. I found a profile High
> volume server to server profile (in merge agent profiles), any idea about
> this?
> Thanks,
> Soura.
> "Herbert" wrote:
|||Soura,
the querytimeout as you seem to be requesting - for all access to the
server - is really a client-side setting. Herbert is right that
replicationwise we can set this in the agent profile, but as you seem to be
requesting more generally, it'll be a property of the command object if you
are using ADO / ADO.NET. Usually this doesn't need changing but there are
legitimate cases for it. I also concur with Chris - you might want to start
by looking to see if you have blocking issues that can be remedied by using
NOLOCK or a reporting server or better indexes for optimization etc. Not
always appropriate but still worth verifying.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
We have modified the Merge agent profile to "High end server to server.."
After couple of days of this change, we came across a new error message "the
process is running and waiting for a response from one of the backend
connections", after this message, we noticed the normal error message "
...clean up".
Please provide any other areas we may need to review.
We "push" the subscription to the Subscriber. All our foreign keys are not
enabled for replication.
Thanks,
Soura
"Paul Ibison" wrote:
> Soura,
> the querytimeout as you seem to be requesting - for all access to the
> server - is really a client-side setting. Herbert is right that
> replicationwise we can set this in the agent profile, but as you seem to be
> requesting more generally, it'll be a property of the command object if you
> are using ADO / ADO.NET. Usually this doesn't need changing but there are
> legitimate cases for it. I also concur with Chris - you might want to start
> by looking to see if you have blocking issues that can be remedied by using
> NOLOCK or a reporting server or better indexes for optimization etc. Not
> always appropriate but still worth verifying.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||SouRa,
the first error message is not normally anything to be concerned about. I
often get this when initializing. In my case it is the application of the
indexes that causes this message. Have a loog at the processes and use bdcc
inputbuffer to see exactly what is happening during this time. Please could
you post up the complete text of the second error message.
Cheers,
Paul
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:800312A8-DE48-44DA-9B1C-705323B6EA6E@.microsoft.com...[vbcol=seagreen]
> Hi,
> We have modified the Merge agent profile to "High end server to server.."
> After couple of days of this change, we came across a new error message
> "the
> process is running and waiting for a response from one of the backend
> connections", after this message, we noticed the normal error message "
> ..clean up".
> Please provide any other areas we may need to review.
> We "push" the subscription to the Subscriber. All our foreign keys are not
> enabled for replication.
> Thanks,
> Soura
> "Paul Ibison" wrote:
I want to increase query timeout value but I don't know how to do this.
Please
advise me where to change query timeout value. Is it possible to set this
for a
particular database?
Thanks,
Soura.
Hi Sounder,
I think we can increase the Timeout for a whole server(all DB's in that
Server)
Right click on the server name => Select properties => Choose the
Connections tab => Increase the query timeout value. (The value is in
seconds. Set it to 0 for unlimited time) => Click OK.
Regards,
Herbert
"SouRa" wrote:
> Hi,
> I want to increase query timeout value but I don't know how to do this.
> Please
> advise me where to change query timeout value. Is it possible to set this
> for a
> particular database?
> Thanks,
> Soura.
>
|||Hi Herbert,
Thanks for your reply. There is an option in replication job to change the
number of attempts to connect subscriber with some specified time interval.
We can modify the information. Similarly is there any option to change query
time out (in any replication jobs,subscription properties or any where else)?
Thanks,
Soura.
"Herbert" wrote:
[vbcol=seagreen]
> Hi Sounder,
> I think we can increase the Timeout for a whole server(all DB's in that
> Server)
> Right click on the server name => Select properties => Choose the
> Connections tab => Increase the query timeout value. (The value is in
> seconds. Set it to 0 for unlimited time) => Click OK.
>
> Regards,
> Herbert
>
> "SouRa" wrote:
|||Hi Sounder,
I think u need to increase the timeout of merge agent....
Open the Replication monitor in that choose the Merge agent => Right-click
appropriate publication and select Agent Profiles... => Click the New Profile
button to create the new profile with the appropriate QueryTimeout value =>
Choose the newly created profile.
Hope this will help..
Regards,
Herbert
"SouRa" wrote:
[vbcol=seagreen]
> Hi Herbert,
> Thanks for your reply. There is an option in replication job to change the
> number of attempts to connect subscriber with some specified time interval.
> We can modify the information. Similarly is there any option to change query
> time out (in any replication jobs,subscription properties or any where else)?
> Thanks,
> Soura.
> "Herbert" wrote:
|||Hi Herbert,
Thanks for your reply. Actually I getting time out error in my application
frequently.
So I decided to increase the query time out value. I found a profile High
volume server to server profile (in merge agent profiles), any idea about
this?
Thanks,
Soura.
"Herbert" wrote:
[vbcol=seagreen]
> Hi Sounder,
> I think u need to increase the timeout of merge agent....
> Open the Replication monitor in that choose the Merge agent => Right-click
> appropriate publication and select Agent Profiles... => Click the New Profile
> button to create the new profile with the appropriate QueryTimeout value =>
> Choose the newly created profile.
> Hope this will help..
> Regards,
> Herbert
>
> "SouRa" wrote:
|||Hi Sounder,
Since ur getting timeout in application, i think u have to increase the
query timeout of Server instead of replication agent.
by my previous post step u can set the query timeout of Server.
Regards,
Herbert
"SouRa" wrote:
[vbcol=seagreen]
> Hi Herbert,
> Thanks for your reply. Actually I getting time out error in my application
> frequently.
> So I decided to increase the query time out value. I found a profile High
> volume server to server profile (in merge agent profiles), any idea about
> this?
> Thanks,
> Soura.
> "Herbert" wrote:
|||SouRa, rather than adjusting the query timeout setting, why dont you try to
figure out why the queries are taking so long?
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:14E41307-124F-40A1-A1ED-97E4E9DC7B81@.microsoft.com...[vbcol=seagreen]
> Hi Herbert,
> Thanks for your reply. Actually I getting time out error in my application
> frequently.
> So I decided to increase the query time out value. I found a profile High
> volume server to server profile (in merge agent profiles), any idea about
> this?
> Thanks,
> Soura.
> "Herbert" wrote:
|||Soura,
the querytimeout as you seem to be requesting - for all access to the
server - is really a client-side setting. Herbert is right that
replicationwise we can set this in the agent profile, but as you seem to be
requesting more generally, it'll be a property of the command object if you
are using ADO / ADO.NET. Usually this doesn't need changing but there are
legitimate cases for it. I also concur with Chris - you might want to start
by looking to see if you have blocking issues that can be remedied by using
NOLOCK or a reporting server or better indexes for optimization etc. Not
always appropriate but still worth verifying.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
We have modified the Merge agent profile to "High end server to server.."
After couple of days of this change, we came across a new error message "the
process is running and waiting for a response from one of the backend
connections", after this message, we noticed the normal error message "
...clean up".
Please provide any other areas we may need to review.
We "push" the subscription to the Subscriber. All our foreign keys are not
enabled for replication.
Thanks,
Soura
"Paul Ibison" wrote:
> Soura,
> the querytimeout as you seem to be requesting - for all access to the
> server - is really a client-side setting. Herbert is right that
> replicationwise we can set this in the agent profile, but as you seem to be
> requesting more generally, it'll be a property of the command object if you
> are using ADO / ADO.NET. Usually this doesn't need changing but there are
> legitimate cases for it. I also concur with Chris - you might want to start
> by looking to see if you have blocking issues that can be remedied by using
> NOLOCK or a reporting server or better indexes for optimization etc. Not
> always appropriate but still worth verifying.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||SouRa,
the first error message is not normally anything to be concerned about. I
often get this when initializing. In my case it is the application of the
indexes that causes this message. Have a loog at the processes and use bdcc
inputbuffer to see exactly what is happening during this time. Please could
you post up the complete text of the second error message.
Cheers,
Paul
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:800312A8-DE48-44DA-9B1C-705323B6EA6E@.microsoft.com...[vbcol=seagreen]
> Hi,
> We have modified the Merge agent profile to "High end server to server.."
> After couple of days of this change, we came across a new error message
> "the
> process is running and waiting for a response from one of the backend
> connections", after this message, we noticed the normal error message "
> ..clean up".
> Please provide any other areas we may need to review.
> We "push" the subscription to the Subscriber. All our foreign keys are not
> enabled for replication.
> Thanks,
> Soura
> "Paul Ibison" wrote:
Subscribe to:
Posts (Atom)