|
|
Posted by RodStrongo1 on 09/30/80 12:00
On Jan 14, 5:31 pm, Erland Sommarskog <esq...@sommarskog.se> 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/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.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!
[Back to original message]
|