Slow query execution, SQL Server 2000

[M Wells]

Hi All,

I have a table that currently contains approx. 8 million records.

I'm running a SELECT query against this table that in some
circumstances is either very quick (ie results returned in Query
Analyzer almost instantaneously), or very slow (ie 30 to 40 seconds to
return results), and I'm trying to work out how I improve performance.

Essentially the query I'm running is nothing more complex than:

SELECT TOP 1 * FROM Table1 WHERE tier=n ORDER BY member_id

[tier] is a smallint column with a non-clustered, non-unique index on
it. [member_id] is a numeric column with a clustered, unique index on
it.

When I supply a [tier] value of 1, it returns results instantaneously.
I have no idea if this is meaningful, but the tier = 1 records were
loaded first into the table, and comprise approximately 5 million
records.

When I supply a [tier] value of 2, the results take 30 to 40 seconds.
tier =2 records were loaded second, and comprise approximately 3
million records.

I've tried running an execution plan, and while I'm no expert, it
appears to me that the index on tier isn't being used, even if I use:

tier = CAST(2 as SMALLINT)

I'm wondering if anyone can give me ANY advice on how to get any
better performance out of this SELECT statement?

Also, out of curiosity, can a disk defragment have a positive impact
on SELECT query performance?

Any help very much appreciated!

Much warmth,

Murray

[M Wells]

On Mon, 23 Feb 2004 16:30:39 GMT, M Wells
<pl**********@planetthoughtful.org> wrote:

I'm wondering if anyone can give me ANY advice on how to get any
better performance out of this SELECT statement?

Also, out of curiosity, can a disk defragment have a positive impact
on SELECT query performance?

Also, additional to the above post, have there been any SELECT
performance improvements realised with the Service Packs? I've just
noticed that this server is returning a SELECT @@VERSION of 8.00.194,
which, as I understand it, means that it currently has no service
packs installed...

I'm wondering if updating to Service Pack 3a (which I know is a good
idea anyway), might improve the performance of this query?

Much warmth,

Murray

[Erland Sommarskog]

M Wells (pl**********@planetthoughtful.org) writes:

Essentially the query I'm running is nothing more complex than:

SELECT TOP 1 * FROM Table1 WHERE tier=n ORDER BY member_id

[tier] is a smallint column with a non-clustered, non-unique index on
it. [member_id] is a numeric column with a clustered, unique index on
it.

When I supply a [tier] value of 1, it returns results instantaneously.
I have no idea if this is meaningful, but the tier = 1 records were
loaded first into the table, and comprise approximately 5 million
records.

When I supply a [tier] value of 2, the results take 30 to 40 seconds.
tier =2 records were loaded second, and comprise approximately 3
million records.

I've tried running an execution plan, and while I'm no expert, it
appears to me that the index on tier isn't being used, even if I use:

This is a little funny. In most situations that index on tier would
be meaningless, but the TOP 1 should change everything.

The reason the index is meaningless is because for each hit in tier, SQL
Server needs to access the data page, in what is called a bookmark lookup,
and many bookmark lookups can easily be more expensive than scanning
the table.

However, since SQL Server users the clustered key as the row pointer in
a nonclustered index, SQL Server should be able to find that single row
by seeking the index on tier and then perform a single lookup.

So, indeed, upgrade to SP3, and see if it resolves the issue. No
guarantees that it will.

If it doesn't - it would be interesting to have a repro to give to
Microsoft, but producing a repro with eight million rows could mean
some problems.

This could be an alternative way of writing the query:

SELECT t.*
FROM tbl t
WHERE t.memberid = (SELECT MIN(t2.memberid)
FROM tbl t2
WHERE t2.tier = n)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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

[Greg D. Moore \(Strider\)]

"M Wells" <pl**********@planetthoughtful.org> wrote in message
news:j0********************************@4ax.com...

On Mon, 23 Feb 2004 16:30:39 GMT, M Wells
<pl**********@planetthoughtful.org> wrote:

I'm wondering if anyone can give me ANY advice on how to get any
better performance out of this SELECT statement?

Also, out of curiosity, can a disk defragment have a positive impact
on SELECT query performance?
Also, additional to the above post, have there been any SELECT
performance improvements realised with the Service Packs? I've just
noticed that this server is returning a SELECT @@VERSION of 8.00.194,
which, as I understand it, means that it currently has no service
packs installed...

The optimizer does get updated from time to time, so it's possible.


I'm wondering if updating to Service Pack 3a (which I know is a good
idea anyway), might improve the performance of this query?

Regardless, there are enough security issues and the like fixed with the
latest SP, that you should update to 3a.

Much warmth,

Murray

[Gang He [MSFT]]

The nonclustered index on [tier] internally store the clustered key
[member_id]as part of index key, meaning with the same [tier] value the rows
are sorted on [member_id] already in the index. Thus the plan should have
been just a simple scan on the nonclustered index. I tried to create
table/index with the same schema and verified on SP3 that is the case.

The fact that different [tier] values caused such a big performance
difference indicate there might be some physical locality issue with part of
the index. You can try to defrag the index to see whether that helps to
make the behavior consistent.

Last, you can use the following workaround to force the plan to use the
nonclustered index, assuming the index id for the nonclustered index is 2:

SELECT TOP 1 * FROM Table1 with (index=2) WHERE tier=n ORDER BY member_id

--
Gang He
SQL Server Storage Engine Development

This posting is provided "AS IS" with no warranties, and confers no rights.
"M Wells" <pl**********@planetthoughtful.org> wrote in message
news:dm********************************@4ax.com...

Hi All,

I have a table that currently contains approx. 8 million records.

I'm running a SELECT query against this table that in some
circumstances is either very quick (ie results returned in Query
Analyzer almost instantaneously), or very slow (ie 30 to 40 seconds to
return results), and I'm trying to work out how I improve performance.

Essentially the query I'm running is nothing more complex than:

SELECT TOP 1 * FROM Table1 WHERE tier=n ORDER BY member_id

[tier] is a smallint column with a non-clustered, non-unique index on
it. [member_id] is a numeric column with a clustered, unique index on
it.

When I supply a [tier] value of 1, it returns results instantaneously.
I have no idea if this is meaningful, but the tier = 1 records were
loaded first into the table, and comprise approximately 5 million
records.

When I supply a [tier] value of 2, the results take 30 to 40 seconds.
tier =2 records were loaded second, and comprise approximately 3
million records.

I've tried running an execution plan, and while I'm no expert, it
appears to me that the index on tier isn't being used, even if I use:

tier = CAST(2 as SMALLINT)

I'm wondering if anyone can give me ANY advice on how to get any
better performance out of this SELECT statement?

Also, out of curiosity, can a disk defragment have a positive impact
on SELECT query performance?

Any help very much appreciated!

Much warmth,

Murray