Query performance problem

I'm having trouble figuring out why a query is having
varying response time. The following query takes anywhere
from a 2 seconds to 20 minutes:
select *
from tbl (nolock)
where ACTIVE = 1
There are 17 rows returned in the table. The largest data
in the text field is 34 KB, with most around 20 KB.
Anybody have any ideas?
Table structure is as follows:
CREATE TABLE [tbl] (
[ID] [int] IDENTITY (40, 1) NOT NULL ,
[ACTIVE] [bit] NOT NULL ,
[NAME] [varchar] (50) COLLATE NULL ,
[FILE] [text] NULL ,
CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
(
[SHEET_ID]
)
)What indexes do you have? And how many rows in the table? What query plan
does the optimizer select? Does it use an index on the Active column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
> I'm having trouble figuring out why a query is having
> varying response time. The following query takes anywhere
> from a 2 seconds to 20 minutes:
> select *
> from tbl (nolock)
> where ACTIVE = 1
> There are 17 rows returned in the table. The largest data
> in the text field is 34 KB, with most around 20 KB.
> Anybody have any ideas?
> Table structure is as follows:
> CREATE TABLE [tbl] (
> [ID] [int] IDENTITY (40, 1) NOT NULL ,
> [ACTIVE] [bit] NOT NULL ,
> [NAME] [varchar] (50) COLLATE NULL ,
> [FILE] [text] NULL ,
> CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
> (
> [SHEET_ID]
> )
> )|||There is only one index, The PK on the field "ID" (see DDL
below).
There are 21 rows in the table, the query returns 17.
Notice that there is a text column in the tbl (20-30KB).
The optimizer does a full tablescan on the table. Also
note, I am running a trace for that table and there is not
any update DML.
Thanks,
Mark

>--Original Message--
>What indexes do you have? And how many rows in the table?
What query plan
>does the optimizer select? Does it use an index on the
Active column?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Mark DeWaard" <anonymous@.discussions.microsoft.com>
wrote in message
>news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
anywhere
data
>
>.
>|||OK. Well, you could try creating a supporting index for the query, but it
seems like the major issue here is actually accessing the BLOB data. Bit
still it is not so much data that I would suspect so varying response times.
And just to be certain, check for blocking (I know you mention you are
running a profiler trace, but just to be certain). I guess you could
investigate where the wait time is, a recent SQL Server Magazine had a nice
article regarding this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <mdewaard@.idtdna.com> wrote in message
news:b74201c40b9f$660907f0$a601280a@.phx.gbl...
> There is only one index, The PK on the field "ID" (see DDL
> below).
> There are 21 rows in the table, the query returns 17.
> Notice that there is a text column in the tbl (20-30KB).
> The optimizer does a full tablescan on the table. Also
> note, I am running a trace for that table and there is not
> any update DML.
> Thanks,
> Mark
>
> What query plan
> Active column?
> wrote in message
> anywhere
> data|||Have you attempted to call just the column names instead of using the "*"?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
> I'm having trouble figuring out why a query is having
> varying response time. The following query takes anywhere
> from a 2 seconds to 20 minutes:
> select *
> from tbl (nolock)
> where ACTIVE = 1
> There are 17 rows returned in the table. The largest data
> in the text field is 34 KB, with most around 20 KB.
> Anybody have any ideas?
> Table structure is as follows:
> CREATE TABLE [tbl] (
> [ID] [int] IDENTITY (40, 1) NOT NULL ,
> [ACTIVE] [bit] NOT NULL ,
> [NAME] [varchar] (50) COLLATE NULL ,
> [FILE] [text] NULL ,
> CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
> (
> [SHEET_ID]
> )
> )|||Another table has the "id" field as a fk. Could it be that
an exclusive lock is placed on the "id" when the other
table is inserted or updated?

>--Original Message--
>OK. Well, you could try creating a supporting index for
the query, but it
>seems like the major issue here is actually accessing the
BLOB data. Bit
>still it is not so much data that I would suspect so
varying response times.
>And just to be certain, check for blocking (I know you
mention you are
>running a profiler trace, but just to be certain). I
guess you could
>investigate where the wait time is, a recent SQL Server
Magazine had a nice
>article regarding this.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Mark DeWaard" <mdewaard@.idtdna.com> wrote in message
>news:b74201c40b9f$660907f0$a601280a@.phx.gbl...
DDL
not
table?
>
>.
>|||Yes, that is possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:c49201c40c6c$0fc53440$a601280a@.phx.gbl...
> Another table has the "id" field as a fk. Could it be that
> an exclusive lock is placed on the "id" when the other
> table is inserted or updated?
>
> the query, but it
> BLOB data. Bit
> varying response times.
> mention you are
> guess you could
> Magazine had a nice
> DDL
> not
> table?