[posted and mailed, please reply in news]
Rob (yung.robert@northropgrumman.ca) writes:
[quoted text, click to view] > I have a very strange problem. I have one sql 7.0 box, and one sql
> 2000 sp3 box. An ASP app that we've been running off of the 7.0 is
> now running about 10x slower on the 2000 box. The 2000 has more ram,
> faster processor, and both are running on WinNTsp6a. I have checked
> all the possible settings between the two servers, and dont see any
> differences that could cause this. The code and tables are exactly
> the same on both databases, and I have no idea where to look now. I
> must also mention that both sql servers also have a novell client on
> it to attach to our netware backbone. I have read alot of articles
> blaming the client for slow connections, but the sql 7.0 server doesnt
> seem to exhibit any problems. When I put my database on a pure 2000
> server on yet another box, I also have no problems. So my question
> is, is there any reason for a 2000 sql server to run slower than a sql
> 7.0 one? How can i go about troubleshooting this? Even if I bypass
> my .asp app and just do a query in query analyzer, the 2000 box takes
> about 10 seconds more to return the same # of rows. I am at a loss...
> please help!!
The most likely reason is that you get a bad query plan on SQL2000 for
one reason or another. This can easily be examained by running the query
in QA on both servers, with Show Execution Plan (Ctrl/K) and then compare
the plans. Most likely they will be different.
This may be happening for several reason. One is that the optimizer in
SQL 2000 is different, and in your particular case makes a poorer choice
of plans. David's suggestion of running UPDATE STATISTICS on the involved
tables, is a good one, and I'll that you should do it WITH FULLSCAN.
Another possible reason is that there is some implicit conversion going
on. Say that your query includes this condition
WHERE indexed_char_col = 4
In SQL 7, the 4 is implicitly converted to char, and the optimizer can
therefore use the index on indexed_char_col. But in SQL2000, the conversion
rules are different and the column will be implicitly converted to integer,
which will make the index useless.
You should also use sp_help on the involved tables to check that all indexes
are in place in the SQL2000 database.
Yet another thing to look for is disabled or non-trusted constraints, by
using this SELECT:
select "table" = object_name(parent_obj), "constraint" = name
from sysobjects
where xtype IN ('F', 'C')
and (objectproperty(id, 'CnstIsDisabled') = 1 OR
objectproperty(id, 'CnstIsNotTrusted') = 1)
order by 1, 2
The optimizer can use constraints to determine a query plan, but only
if the constraint can be fully trusted. Thus not if the constraint is
disabled, or was enabled with WITH NOCHECK (which is the default when
a constraint is reenabled). If you have disabled/untrusted constraints on a
table, you can use this command:
alter table tbl WITH CHECK CHECK CONSTRAINT ALL
(Yes, the syntax is that funny.)
This is hardly an exhaustive list of possibilities, but it should give you
some to start digging.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
