Performance problem: SQL Server 7.0 vs SQL Server 2000

Performance problem: SQL Server 7.0 vs SQL Server 2000

Post by Zoe » Sat, 13 Oct 2001 17:47:12



It took only 3 minutes to complete a process on NT4 + SQL
Server 7 environment, but more than an hour when upgraded
to a Window 2000 + SQL Server 2000 environment, anyone
know why ?

Something I can do with the SQL Server 2000 configuration ?

 
 
 

Performance problem: SQL Server 7.0 vs SQL Server 2000

Post by Narayana Vyas Kondredd » Sat, 13 Oct 2001 18:56:00


Did you update statistics after upgrading to SQL Server 2000?
--
HTH,
Vyas
SQL Server FAQ, articles, code samples,

http://vyaskn.tripod.com/


Quote:> It took only 3 minutes to complete a process on NT4 + SQL
> Server 7 environment, but more than an hour when upgraded
> to a Window 2000 + SQL Server 2000 environment, anyone
> know why ?

> Something I can do with the SQL Server 2000 configuration ?


 
 
 

Performance problem: SQL Server 7.0 vs SQL Server 2000

Post by Zoe » Sun, 14 Oct 2001 13:37:20


I've tried update statistics, reindex, free procedure
cache, update usage & shrink database, it still take ages
to complete the process.

>-----Original Message-----
>Did you update statistics after upgrading to SQL Server
2000?
>--
>HTH,
>Vyas
>SQL Server FAQ, articles, code samples,

>http://vyaskn.tripod.com/



>> It took only 3 minutes to complete a process on NT4 +
SQL
>> Server 7 environment, but more than an hour when
upgraded
>> to a Window 2000 + SQL Server 2000 environment, anyone
>> know why ?

>> Something I can do with the SQL Server 2000
configuration ?

>.

 
 
 

Performance problem: SQL Server 7.0 vs SQL Server 2000

Post by Keith Wo » Wed, 17 Oct 2001 01:31:56


I would be interested in seeing what the query plans look like. Plus check the SQL Errorlog for any error messages, such as an exception access violation or assertion.

Some additional questions:
   - Is this a single query? If more than one query involved, use SQL Profiler to help identify what query (or queries) are taking longer in SQL 2000
   - Is this the same server where the SQL 7.0 was on?
   - Have you used Index Tuning Wizard to analyze the query (or queries)?
   - Recommend do some troubleshooting with SQL Profiler - look at Exception, Recompile and Warnings (sort,hash,missing column stats) Events for clues.

We have come across one issue where the SQL 2K optimizer was choosing a poor plan for query containing substring function and a comparison operator. In 7.0, it was choosing an optimal plan.Fix should be
available in SP2 when that is released (no ETA on this as yet).
-Keith

=========================
*  This posting is provided "AS IS" with no warranties, and confers no rights.                
*  You assume all risk for your use. ? 2001 Microsoft Corporation. All rights reserved
*                                                                                      
*  Please do not send email directly to this alias.                                    
=========================
--------------------
|>Content-Class: urn:content-classes:message



|>Subject: Re: Performance problem: SQL Server 7.0 vs SQL Server 2000
|>Date: Fri, 12 Oct 2001 21:37:20 -0700
|>
|>I've tried update statistics, reindex, free procedure
|>cache, update usage & shrink database, it still take ages
|>to complete the process.
|>
|>

|>>-----Original Message-----
|>>Did you update statistics after upgrading to SQL Server
|>2000?
|>>--
|>>HTH,
|>>Vyas
|>>SQL Server FAQ, articles, code samples,

|>>http://vyaskn.tripod.com/
|>>
|>>
|>>


|>>> It took only 3 minutes to complete a process on NT4 +
|>SQL
|>>> Server 7 environment, but more than an hour when
|>upgraded
|>>> to a Window 2000 + SQL Server 2000 environment, anyone
|>>> know why ?
|>>>
|>>> Something I can do with the SQL Server 2000
|>configuration ?
|>>>
|>>
|>>
|>>.
|>>
|>

 
 
 

1. SQL Server 7.0 vs SQL Server 2000: invalid column width

The following code
select '' into tmp
produces a table of the following layout in
SQL Server 7.0:
a varchar(30) null

However the same code in in SQL Server 2000 fails with the following error:
Server: Msg 2732, Level 16, State 1, Line 1
Column 'a' has invalid width: 0.

So my problem is as follows:
We are going to upgrade a Server that runs hundreds of different production scripts, written by dozens of programmers. And I know at least some of them have employed this type of code. Avoiding the failure of these scripts is of paramount importance to me and my remaining at my current employer ;-)
Any input on this help would be greatly appreciated.
The fact that SQL Serer just defaults to a column width of 30 makes me think that there could be something like a default column width, which could be set in SQL Server 2000. Did anyone ever come across a solution for this?

Thanks in advance,
Dorian

**********************************************************************

Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

2. Quality of Database products

3. SQL Server 7.0 Vs Sql Server 2000

4. Filesystem block size??

5. Upgrading SQL Server 7.0 to SQL Server 2000 - Meta Data Repository Database Version Problem

6. Sequence Number (Identity Won't Work) And Locking

7. SQL Server 2000 vs. SQL Server 2000 Developer

8. Know of a good SQL book?

9. Update NT4.0 and SQL Server 7.0 to Windows 2000 and SQL Server 2000

10. SQL Server 7.0 vs SQL Server 7.0 Enterprise

11. Trusted SQL Server connection problems 2000 vs. 7.0

12. SQL Server 2000 license overriding SQL Server 7.0 license on separate server

13. Installing SQL server 7.0 on a server with SQL server 2000