SQL query timeout

I am looking for some help troubleshooting my SQL issue.
I have taken over someone else's work, and am now playing catch up.

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.


sql = "select arrDist_id_physical from country_codes where
intCountry_code='"&sqlencode(intCountry_Code)&"'"
response.write(debugSql(sql))
......

sql = ""
sql = sql&"select "
sql = sql& "LEFT(distributors_detail.cust_no, CHARINDEX('-',
distributors_detail.cust_no) - 1) AS distributorNumber, "
sql = sql& "* "
sql = sql&"FROM "
sql = sql& "distributors_detail "
sql = sql&"where "
sql = sql& "right(distributors_detail.cust_no,
len(distributors_detail.cust_no) - (charindex('-',
distributors_detail.cust_no) - 1)) in('-0','-1','-X','-A', '-W') AND "
sql = sql& "isNull(cust_code_3,'') in('', 'CH') AND "
sql = sql & " or LEFT(cust_no, CHARINDEX('-', cust_no) - 1) =
'"& trim(arrDist_id_physical(iIntern)) &"'"
sql = sql& "LEFT(distributors_detail.cust_no, CHARINDEX('-',
distributors_detail.cust_no) - 1) IN ("
for iIntern = 0 to ubound(arrDist_id_physical)
if iIntern = 0 then
sql = sql&"'"&trim(arrDist_id_physical(iIntern))&"'"
else
sql = sql&",'"&trim(arrDist_id_physical(iIntern))&"'"
end if
next
sql=sql&") AND "
sql = sql& "isNull(cust_code_2,'') = '' "
sql = sql&"ORDER BY "
sql = sql& "cust_no ASC"


The web/SQL server is a 2000 server, running dual xeons.

Any guidance in this issue would be greatly appreciated. If I am
omitting pertinent info, please just inform.

THANK YOU!

Re: SQL query timeout

(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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL query timeout

On Jan 14, 5:31 pm, Erland Sommarskog wrote:
> (RodStron...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Thanks for your help!

The two queries are pertaining to domestic vs intl. It does a lookup
for a country code, and case 1 of domestic, case 2 of international.
The query I copied in here is the intl. Both queries run from the
same table, but searching the domestic entries runs smoothly.

Running the sp_spaceused returns:
name rows reserved
data index_size unused
distributors_detail 973 440 KB 368 KB 56 KB 16 KB

Running the sp_who2 produces this:
1 BACKGROUND sa . . LAZY
WRITER 0 0 01/02
09:34:44 1
2 sleeping sa . .
LOG WRITER 0 0 01/02
09:34:44 2
3 BACKGROUND sa . . master SIGNAL
HANDLER 0 0 01/02
09:34:44 3
4 BACKGROUND sa . . LOCK
MONITOR 0 0 01/02
09:34:44 4
5 BACKGROUND sa . . master TASK
MANAGER 0 289 01/02
09:34:44 5
6 BACKGROUND sa . . master TASK
MANAGER 0 0 01/02
09:34:44 6
7 sleeping sa . .
CHECKPOINT SLEEP 0 1042 01/02
09:34:44 7
8 BACKGROUND sa . . master TASK
MANAGER 0 0 01/02
09:34:44 8
9 BACKGROUND sa . . master TASK
MANAGER 0 190 01/02
09:34:44 9
10 BACKGROUND sa . . master TASK
MANAGER 0 0 01/02
09:34:44 10
11 BACKGROUND sa . . master TASK
MANAGER 0 0 01/02
09:34:44 11
12 BACKGROUND sa . . master TASK
MANAGER 0 0 01/02
09:34:44 12
13 BACKGROUND sa . . master TASK
MANAGER 0 318 01/02
09:34:44 13
15 BACKGROUND sa . . master TASK
MANAGER 0 222 01/02
09:34:44 15
51 sleeping labortime LABORTIME100 .
labortime2003 AWAITING COMMAND 93 0 01/15 08:56:43
DataProj
52 sleeping labortime LABORTIME250 .
labortime2003 AWAITING COMMAND 47 0 01/15 08:56:33
DataProj
53 sleeping labortime 0150-CELL1 .
labortime2003 AWAITING COMMAND 328 0 01/15 08:56:36
DataProj
54 sleeping labortime LABORTIME100 .
labortime2003 AWAITING COMMAND 171 0 01/15 08:56:39
DataProj
55 sleeping labortime LABORTIME500-2 .
labortime2003 AWAITING COMMAND 31 0 01/14 16:06:41
DataProj
56 sleeping labortime LABORTIME500-2 .
labortime2003 AWAITING COMMAND 719 0 01/15 08:28:54
DataProj
57 sleeping labortime 0015-1144 .
labortime2003 AWAITING COMMAND 312 0 01/15 07:39:33
Labortime2005
58 sleeping labortime 0950-1183B .
blueslip AWAITING COMMAND 63 0 01/15 06:37:22
Blueslip
59 sleeping labortime 0016-2122 .
cylinders AWAITING COMMAND 0 0 01/15 08:38:28
DataProj
60 sleeping labortime 0015-1144 .
labortime2003 AWAITING COMMAND 548 0 01/15 07:39:46
Labortime2005
61 sleeping labortime 0015-1144 .
labortime2003 AWAITING COMMAND 63 0 01/15 07:39:36
Labortime2005
62 sleeping labortime 0016-2122 .
cylinderparts AWAITING COMMAND 172 0 01/15 08:38:38
DataProj
63 sleeping labortime 0015-1143B .
blueslip AWAITING COMMAND 0 0 01/15 07:52:40
Blueslip
64 sleeping clippard ELK .
cpd AWAITING COMMAND 11219 0 01/15 08:56:55 MS Windows
2000
65 sleeping labortime 0015-1145 .
blueslip AWAITING COMMAND 0 0 01/15 08:35:14
Blueslip
66 sleeping labortime 0100-1166B .
labortime2003 AWAITING COMMAND 374 0 01/14 08:16:26
DataProj
67 RUNNABLE sa 0055-1123BB .
cpd SELECT INTO 109 5 01/15 08:55:54 MS SQLEM - Data
Tools
68 sleeping labortime 0016-2122 .
cylinders AWAITING COMMAND 0 0 01/15 08:18:00
DataProj
69 sleeping labortime 0016-2122 .
cylinders AWAITING COMMAND 0 0 01/15 08:38:28
DataProj
70 sleeping sa 0055-1123BB .
msdb AWAITING COMMAND 234 453 01/14 15:54:23 MS Windows
OS
71 sleeping labortime 0016-2122 .
configure AWAITING COMMAND 0 0 01/15 08:18:37
DataProj
72 sleeping labortime 0016-2122 .
cylinderparts AWAITING COMMAND 0 0 01/15 08:18:37
DataProj
73 sleeping labortime LABORTIME700-2 .
labortime2003 AWAITING COMMAND 1704 0 01/15 08:56:44
DataProj
74 sleeping labortime 0065-1131 .
labortime2003 AWAITING COMMAND 15 0 01/15 08:55:58
Labortime2005
75 sleeping sa 0055-1123BB .
cpd AWAITING COMMAND 3555 1487 01/15 08:48:32 MS
SQLEM
76 sleeping labortime LABORTIME700 .
labortime2003 AWAITING COMMAND 15 0 01/15 08:32:11
Labortime2005
77 sleeping labortime 0065-1131 .
labortime2003 AWAITING COMMAND 15 5 01/15 08:55:58
Labortime2005

So obviously this server hosts a number of databases. It is running
on Elk, so SPID 64, 67, and 75 are pertinent, and 67 and 75 are
connections from my machine.

The BlkBy column is empty all the way down, so that does not indicate
a clear culprit (to me).

Thanks very much for the help!

Re: SQL query timeout

(RodStrongo1@gmail.com) writes:
> The two queries are pertaining to domestic vs intl. It does a lookup
> for a country code, and case 1 of domestic, case 2 of international.
> The query I copied in here is the intl. Both queries run from the
> same table, but searching the domestic entries runs smoothly.
>
> Running the sp_spaceused returns:
> name rows reserved
> data index_size unused
> distributors_detail 973 440 KB 368 KB 56 KB > 16 KB

Less than a megabyte. The poor search conditions should not be an issue.

But when I reviewed your original post, I notice that the SQL that
is generated is not correct. There is this:

> sql = sql& "isNull(cust_code_3,'') in('', 'CH') AND "
> sql = sql & " or LEFT(cust_no, CHARINDEX('-', cust_no) - 1) = '"& trim(arrDist_id_physical(iIntern)) &"'"
> sql = sql& "LEFT(distributors_detail.cust_no, CHARINDEX('-', distributors_detail.cust_no) - 1) IN ("

The line in the middle does not fit in. AND can be followed by OR
and a string literal cannot be followed by a call to a system function.

Mind you, reading SQL code which is so entwined with client code is
difficult. And difficult to maintain.

In any case, it does not seem that you have posted the actual query
you have problem with. Or at least, I would expect a completely
different error than a timeout error for a query that does not
compile. So that leaves me a bit in the dark.




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

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

Re: SQL query timeout

On Jan 15, 5:43 pm, Erland Sommarskog wrote:
> (RodStron...@gmail.com) writes:
> > The two queries are pertaining to domestic vs intl. It does a lookup
> > for a country code, and case 1 of domestic, case 2 of international.
> > The query I copied in here is the intl. Both queries run from the
> > same table, but searching the domestic entries runs smoothly.
>
> > Running the sp_spaceused returns:
> > name rows reserved
> > data index_size unused
> > distributors_detail 973 440 KB 368 KB 56 KB > 16 KB
>
> Less than a megabyte. The poor search conditions should not be an issue.
>
> But when I reviewed your original post, I notice that the SQL that
> is generated is not correct. There is this:
>
> > sql = sql& "isNull(cust_code_3,'') in('', 'CH') AND "
> > sql = sql & " or LEFT(cust_no, CHARINDEX('-', cust_no) - 1) = '"& trim(arrDist_id_physical(iIntern)) &"'"
> > sql = sql& "LEFT(distributors_detail.cust_no, CHARINDEX('-', distributors_detail.cust_no) - 1) IN ("
>
> The line in the middle does not fit in. AND can be followed by OR
> and a string literal cannot be followed by a call to a system function.
>
> Mind you, reading SQL code which is so entwined with client code is
> difficult. And difficult to maintain.
>
> In any case, it does not seem that you have posted the actual query
> you have problem with. Or at least, I would expect a completely
> different error than a timeout error for a query that does not
> compile. So that leaves me a bit in the dark.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx


Thank you for your help Erland,

Sorry for the confusion in the last posting, I accidentally included a
line that is commented out. As I said, I kind of got tossed into this
one, and I'm trying to wade through and learn how everything works
here.

The actual statement that gets executed is:

select LEFT(distributors_detail.cust_no, CHARINDEX('-',
distributors_detail.cust_no) - 1) AS distributorNumber, * FROM
distributors_detail where right(distributors_detail.cust_no,
len(distributors_detail.cust_no) - (charindex('-',
distributors_detail.cust_no) - 1)) in('-0','-1','-X','-A', '-W') AND
isNull(cust_code_3,'') in('', 'CH') AND
LEFT(distributors_detail.cust_no, CHARINDEX('-',
distributors_detail.cust_no) - 1) IN ('001273','001075') AND
isNull(cust_code_2,'') = '' ORDER BY cust_no ASC


Having any of the '-0, -1, -X, -W' appended on the customer number is
used to indicate what type of location this is for the distributor
(office, warehouse, etc) .

I have a development version of this table, and things work fine in
there, with the same statement, so it leads me to believe there is
some inconsistency in the data,
as the number of records is not huge (~1000, ~300 international
records) and there appears to be no blocking going on.

Is there a way for me to examine the data or the progress of the query
to indicate where it is getting hung up?

Re: SQL query timeout

(RodStrongo1@gmail.com) writes:
> Sorry for the confusion in the last posting, I accidentally included a
> line that is commented out. As I said, I kind of got tossed into this
> one, and I'm trying to wade through and learn how everything works
> here.

OK. I had a vain hope that something more substantial was hidden for me.

> I have a development version of this table, and things work fine in
> there, with the same statement, so it leads me to believe there is
> some inconsistency in the data,
> as the number of records is not huge (~1000, ~300 international
> records) and there appears to be no blocking going on.

I will have to admit that I'm out of ideas. The only thing I can think
of is that there is some corruption. You could run DBCC CHECKTABLE on
the table, or DBCC CHECKDB on the entire database. I would not really
expect anything to come out of this, but at least we could tick it off
the list.

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

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

Re: SQL query timeout

On Jan 17, 5:21 pm, Erland Sommarskog wrote:
> (RodStron...@gmail.com) writes:
> > Sorry for the confusion in the last posting, I accidentally included a
> > line that is commented out. As I said, I kind of got tossed into this
> > one, and I'm trying to wade through and learn how everything works
> > here.
>
> OK. I had a vain hope that something more substantial was hidden for me.
>
> > I have a development version of this table, and things work fine in
> > there, with the same statement, so it leads me to believe there is
> > some inconsistency in the data,
> > as the number of records is not huge (~1000, ~300 international
> > records) and there appears to be no blocking going on.
>
> I will have to admit that I'm out of ideas. The only thing I can think
> of is that there is some corruption. You could run DBCC CHECKTABLE on
> the table, or DBCC CHECKDB on the entire database. I would not really
> expect anything to come out of this, but at least we could tick it off
> the list.
>
> --
> Erland Sommarskog,SQLServer MVP, esq...@sommarskog.se
>
> Books Online forSQLServer 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online forSQLServer 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Both the dbcc checks ran fine.

One thing that strikes me odd:
If I run the query through enterprise mgr, it completes very
quickly.

I'm looking at joining my live table(970 records) and my development
table(700 table), but I'm worried that the problem might just follow
the move.

Thanks so very much for your advice.