MS SQL Server ODBC - Timeout Expired - Same query working for months

Author

Timeout Expired - Same query working for months

mkschultz

2005-06-07, 8:23 pm

We are trying to troubleshoot a complex issue with SQL server 2000. We have
been using an *off the shelf* application for the last 6 months that is used
for portfolio management. The application is written in ASP and the database
is SQL 2000.

One of the screens in the application displays a list of projects that meet
certain criteria; all projects that they have rights to and are still open.
A few days agao, we started receiving the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired

I was able to capture the query being executed in SQL by logging the rs
source to a file before it executes. I can run this query in Query Analyzer
and it will run taking about 40 seconds. The timeout on the connection
object is 30 seconds, which (I would think) explains the timeout error. The
strange thing is, before we experienced these timeout issues, the query never
took longer than 5 seconds to execute and display the results in IE.

As a test, I restored a copy of the DB that was about 4 days old. I was
able view the same page in under 5 seconds. When I use query analyzer to run
the query (on the 4 day old DB) it takes roughly 30 seconds. I'm not sure
why there is such a descrepency in the execution time between ADO and QA.

I compared the number of rows on each of the table referenced in the query
and todays DB have no more than 40 rows than the one that is 4 days old. Not
enough (in my opinion) to be causing the query to take 10+ seconds longer in
QA. Here are the DB hardware specs below:

Model: HP ProLiant DL580 G2
Memory: 2GB
CPU: 4 X Intel Xeon 2.8 GHz
Storage: 60 GB

I have gone as far as extracting the data from each of the tables referenced
in the query, dropping the table, recreating the tables, and repopulating the
tables only to see the same results.

Does anyone have any other ideas on what may be causing this or how I can go
about troubleshooting the error?

Thank you!

-- Mark

Uwa Agbonile [MSFT]

2005-06-10, 8:23 pm

You might want to try rebuilding your indexes using DBCC REINDEX

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Uwa Agbonile[MSFT]

"mkschultz" < mkschultz@discussion
s.microsoft.com> wrote in message
news:56205E51-BBE0-4517-B1AB- EAE94EA09C6B@microso
ft.com...
> We are trying to troubleshoot a complex issue with SQL server 2000. We
have
> been using an *off the shelf* application for the last 6 months that is
used
> for portfolio management. The application is written in ASP and the
database
> is SQL 2000.
>
> One of the screens in the application displays a list of projects that
meet
> certain criteria; all projects that they have rights to and are still
open.
> A few days agao, we started receiving the following error:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
> [Microsoft][ODBC SQL Server Driver]Timeout expired
>
> I was able to capture the query being executed in SQL by logging the rs
> source to a file before it executes. I can run this query in Query
Analyzer
> and it will run taking about 40 seconds. The timeout on the connection
> object is 30 seconds, which (I would think) explains the timeout error.
The
> strange thing is, before we experienced these timeout issues, the query
never

> took longer than 5 seconds to execute and display the results in IE.
>
> As a test, I restored a copy of the DB that was about 4 days old. I was
> able view the same page in under 5 seconds. When I use query analyzer to
run
> the query (on the 4 day old DB) it takes roughly 30 seconds. I'm not sure
> why there is such a descrepency in the execution time between ADO and QA.
>
> I compared the number of rows on each of the table referenced in the query
> and todays DB have no more than 40 rows than the one that is 4 days old.
Not
> enough (in my opinion) to be causing the query to take 10+ seconds longer
in
> QA. Here are the DB hardware specs below:
>
> Model: HP ProLiant DL580 G2
> Memory: 2GB
> CPU: 4 X Intel Xeon 2.8 GHz
> Storage: 60 GB
>
> I have gone as far as extracting the data from each of the tables
referenced
> in the query, dropping the table, recreating the tables, and repopulating
the
> tables only to see the same results.
>
> Does anyone have any other ideas on what may be causing this or how I can
go
> about troubleshooting the error?
>
> Thank you!
>
> -- Mark

Sponsored Links