Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
.See my reply to your previous post from this morning.
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc6001c43e8b$7d505750$a301280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
> .
>

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE939
VGNCCB_ROLE_JT62389
VGNCCB_GROUP_USER_JT1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
..
See my reply to your previous post from this morning.
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc6001c43e8b$7d505750$a301280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
> .
>

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.

The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)

Any idea what could be the issue here? The server
is not really heavily taxed.

The tables are small. They have very few rows.

VGNCCB_ROLE939
VGNCCB_ROLE_JT62389
VGNCCB_GROUP_USER_JT1364

The problem Query:

select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))

I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.

TIA,
Jack
...[posted and mailed, please reply in news]

Jack A (InformixMail@.yahoo.com) writes:
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)

DBCC PINTABLE is a command that very rarely is useful. If you have a
situation that you have a table that is referred to rearely, but
when it is referred to, you want the answers directly. Then you
have a case. Since these tables are referred to at log in and small,
I would assume that they are in memory anyway.

I could think of a possible rewrites of the query, but since this appears
to come from a third-party app, you don't seem to have any use for
that.

Without having the full information about the tables it is difficult
to say, but if it is correct that VGNCCB_ROLE_JT does not have a
clustered index, I think it is time to add one, and that would be
on (ROLE_ID). That could make the two indexes on USER_NAME and GROUP_ID
covering for the query, and could save you some bookmark lookups.

Another idea is to build an indexed view, and hope that SQL Server
will find the indexed view when looking for a query plan. But I am
not sure this is possible. And in any case, you need to have Enterprise
Edition for this to work.

I would encourage you to post the complete CREATE TABLE and CREATE INDEX
scripts for the tables. That makes it a little easier to guess.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK , Here goes with the table structure. BTW I've run DBCC reindex.

TABLE: VGNCCB_ROLE
PK__VGNCCB_ROLE__1FA39FB9clustered, unique, primary key located on
PRIMARY -- ROLE_ID

TABLE: VGNCCB_ROLE_JT
index_nameindex_descriptionindex_keys
PK__VGNCCB_ROLE_JT__218BE82Bclustered, unique, primary key located on
PRIMARY- ID
VGNCCB_ROLE_JT_INDEX1nonclustered located on PRIMARY- USER_NAME
VGNCCB_ROLE_JT_INDEX2nonclustered located on PRIMARY- GROUP_ID

TABLE: VGNCCB_GROUP_USER_JT
index_nameindex_descriptionindex_keys
PK__VGNCCB_GROUP_USE__1DBB5747clustered, unique, primary key located
on PRIMARY- ID
VGNCCB_GROUP_USER_JT_INDEX1nonclustered located on PRIMARY -GROUP_ID
VGNCCB_GROUP_USER_JT_INDEX2nonclustered located on PRIMARY-
USER_NAME|||Jack A (InformixMail@.yahoo.com) writes:
> OK , Here goes with the table structure. BTW I've run DBCC reindex.

Thanks, but I explicitly asked for CREATE TABLE and CREATE INDEX statements.
That could permit me see if it is possible to build an indexed view.

Also, in VGNCCB_ROLE_JT, I can't even see that there is a ROLE_ID
column.

You can script tables and indexes in Enterprise Manager or Query Analyzer.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
.See my reply to your previous post from this morning.
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc6001c43e8b$7d505750$a301280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
> .
>

Performance mystery -- SP vs script

I have encountred situations like this before, but this one
has me stumped.

I have a pretty simple SP that collects information about
residential properties from a large database. First step is to
query on the basis of address or location, and collect a temp
table of property IDs. Second step is to populate a composite
table of property information by joining the IDs to a table of
characterisitics. Third step is to update some fields by finding
a single value from multiple candidates in large tables -- one
has 275 million, another 325 million rows -- e.g., the price of
the most recent sale for a property.

As an SP, this takes absolutely forever, and it seems it's doing
endless scans of the large tables. So to analyze it, I took the
code and ran it as a script -- turned the parameter definition at
the top into a DECLARE statement, set values for the variables
that are the input parameters, no other changes, and go. Presto!
It runs in no time flat, and the query plans reveal it's using
the indexes just like it's supposed to. But the SP might take
an hour to do the same thing.

Any suggestions about what to look for? I believe both versions
have fresh query plans -- I have recompiled (and dropped and
recreated) the SP, and the plain script should have a fresh plan.
Maybe it's because the parameter values are known when the script
runs, but not when the SP is complied? I would really appreciate
any pointers, and can provide more information as needed.

Thanks,
Jim GeissmanPS -- it's the update statements that are the problem.
Even if the temp table has only one record, four parallel
threads are spawned to search through a large table
hunting for the ten or so records for that property -- even
though thereare indexes aplenty allowing it to go directly
to the appropriate records.|||You might want to try recompiling the stored procedure. It's possible
that when the stored procedure was first run and a query plan was
created, there were different indexes or the tables did not hold the
same data. The stored procedure might still be trying to use that same
query plan even though the data and/or indexes have changed.

HTH,
-Tom.|||That sure sounds reasonable, Tom, however nothing has changed
with the tables for a week or more, and the SP has only existed
for two days. One change I did make that seems to have led to
this was:

I had approximately this formerly:

update t set amt=e.amt, date=e.date,name=n.name
from #temp t join event e on e.propid=t.propid
join name n on n.propid=e.propid and n.eventid=e.eventid
where e.eventtype=... and e.date=(select max(date)
from event e1 where e1.propid=t.propid and...)

I split it into two separate update queries, one for the
date & amount, another for the name, because the two
should each be the most recent (with some conditions),
but don't have to be from the same event. The joins
dropped from three tables to two. That change broke it.|||There's an index that has everything needed to locate the
amount -- propid, date, eventtype, and amount, in that order.
However, it is being scanned on the basis of date and event
type (millions of rows) instead of going directly to the
correct handful of rows by adding propid. Is there a way to
change this behavior?|||On 9 Mar 2005 15:38:30 -0800, jim_geissman@.countrywide.com wrote:

>PS -- it's the update statements that are the problem.
>Even if the temp table has only one record, four parallel
>threads are spawned to search through a large table
>hunting for the ten or so records for that property -- even
>though thereare indexes aplenty allowing it to go directly
>to the appropriate records.

Which version of SQL Server are you using? I've had problems with SQL Server
7 grossly misestimating the time to execute query plans, thinking it has
exceeded the parallelism threshold, and choosing to use parallelism when it
actually hurts performance significantly. Try changing your server's
parallelism threshold to 5x or 10x the default value.|||(jim_geissman@.countrywide.com) writes:
> I have encountred situations like this before, but this one
> has me stumped.
> I have a pretty simple SP that collects information about
> residential properties from a large database. First step is to
> query on the basis of address or location, and collect a temp
> table of property IDs. Second step is to populate a composite
> table of property information by joining the IDs to a table of
> characterisitics. Third step is to update some fields by finding
> a single value from multiple candidates in large tables -- one
> has 275 million, another 325 million rows -- e.g., the price of
> the most recent sale for a property.
> As an SP, this takes absolutely forever, and it seems it's doing
> endless scans of the large tables. So to analyze it, I took the
> code and ran it as a script -- turned the parameter definition at
> the top into a DECLARE statement, set values for the variables
> that are the input parameters, no other changes, and go. Presto!
> It runs in no time flat, and the query plans reveal it's using
> the indexes just like it's supposed to. But the SP might take
> an hour to do the same thing.

Without seeing the code, and not having information about the table etc,
the best I can offer is wild guesses and standard recommendations.

Since replacing the parameters with variables appeared to give effect,
it seems you have a workaround. (Copy the parameters to local variables.)
Then again, there are a couple of more possibilities.

If the issue is indeed parameters vs. variables, then it could be a
case of psrameter sniffing when you don't want it. "Parameter sniffing"
is when SQL Server uses the values of the input parameters on the
first invocation to build the query plan for the stored procedure.
When it comes to variables, SQL Server does not know their values
before and builds the plan from standard assumptions. Usually parameter
sniffing is good, but say that you have:

CREATE PROCEDURE sniff_sp @.a datetime ... AS
...
IF @.a IS NULL SELECT @.a = convert(char(8), @.a, 112)

And on first invocation, you call the procedure with @.a = NULL. That's
a value you don't use, so it will not be good for the plan.

Another alternative is that the stored procedure was created with
any of ANSI_NULLS or QUOTED_IDENTIFIER off, and the procedure involves
access to an indexed view or an indexed computed columns. The two
mentioned settings must be ON for such indexes to be used, and these
two settings are saved with the stored procedure. You can use
the Objectproperty() function to investigate this.

There may be other reasons as well, as the optimizer being to optimistic
about the benefits of parallelism, or too pessimistic about the usefulness
of a non-clustered index. You can use OPTION (MAXDOP 1) to turn of
parallelism. You can also try to force the use of a certain index, but
you should be careful, because a using a non-clustered index when
there are too many hits and be real disaster performancewise.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp