SQL 6.5 to SQL 2000 performance question

[Robert]

We are running SQL Server in 6.5 compatability mode. We have some queries
that are supported in 2000, that is the reason for the 6.5 version. What,
if any, would be gained in performance by switching to 2000.

[Simon Hayes]

"Robert" <st*******@boeing.com> wrote in message
news:Hs********@news.boeing.com...

We are running SQL Server in 6.5 compatability mode. We have some queries
that are supported in 2000, that is the reason for the 6.5 version. What,
if any, would be gained in performance by switching to 2000.

As far as I'm aware, the compatibility level affects functionality, not
performance as such, although since I haven't used it much myself, I'm not
100% sure about that.

In any event, MS recommends using a compatibility level only as a temporary
step during an upgrade, and moving to 2000 level would give you extra
functionality (eg. functions, indexed views) which isn't available in 6.5
mode. So it would probably be a good idea to review and update your code
anyway.

If you're having difficulties doing that, then you can post an example of
the problem code (including DDL and sample data, if appropriate), and the
errors you get in 2000 mode, and someone may be able to suggest an
alternative way to write the queries.

Simon

[Gert-Jan Strik]

You would get all the gains of SQL-Server 7.0 and 2000.

Some relevant gains of SQL-Server 7.0:
- Use of multiple indexes per table
- Hash and merge join strategy
- better handling of large memory
- better storage engine

Some relevant gains of SQL-Server 2000:
- Indexed views possible
- even better storage engine

Gert-Jan

Robert wrote:

We are running SQL Server in 6.5 compatability mode. We have some queries
that are supported in 2000, that is the reason for the 6.5 version. What,
if any, would be gained in performance by switching to 2000.

[Robert]

thanx
"Gert-Jan Strik" <so***@toomuchspamalready.nl> wrote in message
news:40***************@toomuchspamalready.nl...

You would get all the gains of SQL-Server 7.0 and 2000.

Some relevant gains of SQL-Server 7.0:
- Use of multiple indexes per table
- Hash and merge join strategy
- better handling of large memory
- better storage engine

Some relevant gains of SQL-Server 2000:
- Indexed views possible
- even better storage engine

Gert-Jan

Robert wrote:

We are running SQL Server in 6.5 compatability mode. We have some queries that are supported in 2000, that is the reason for the 6.5 version. What, if any, would be gained in performance by switching to 2000.