Posted by Erland Sommarskog on 01/14/08 22:31

(RodStrongo1@gmail.com) writes:
> I have an MS SQL db holding distributor information for my client,
> consisting of domestic and intl distributors.
> From an asp page, the user is prompted to input their country.
> When a domestic location is chosen, things work smoothly.
> When an international location is chosen, the asp eventually times out
> and returns an ASP 0113 error.
>
> For several months, the international side worked fine. Using my
> development db, things work fine.
> This makes me think something inconsistent could have entered the
> (prod) data and is causing it to spin.
>
> This query should return a list of 1 or more distributors matching a
> locale. It now returns one match, and subsequent entries are replaced
> with the ASP 0113 error.

There are two queries. The first one returns, the second times out?
(By the way, the timeout is entirely a client-side thing. SQL Server
does mind if you wait forever.)

How big is the distributors_detail table? To determine this run:

exec sp_spaceused distributors_detail, TRUE

I can see two possibilities: one is that the table is very big, and
takes a long time to scan. Because that much is clear: the way
the query, there is no index that can be used efficiently. But
if the table is small, that should not be an issue.

The other possibility is blocking. You can determine this with sp_who2.
Keep an eye on the Blk column. If there is a value, it means that the
spid in the Blk column blocks the spid on that row. If that is your
web request, you have the culprit. Probably you should kill the blocker,
but you should probably try to find out what it is.



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

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

[Back to original message]