SQL 6.5 Performance vs SQL 7.0 Performance

SQL 6.5 Performance vs SQL 7.0 Performance

Post by Seh Eing, Li » Thu, 19 Aug 1999 04:00:00



hello,

Has anyone encountered performance problem when porting 6.5 stored procedure
to 7.0?

The performance degrades almost 100%. Something which took 1min to run in
6.5 runs 2min in 7.0.

My procedure has lots of tmp table creation and use of cursors. Are these
the reason for the performance loss? I am really puzzled coz 7.0 is supposed
to outperform 6.5 many times over. If those were the reasons, what is the
alternative to using tmp tables to manipulate data?

Anyone has any clues? This is a big stumbling block to my company upgrading
to SQL 7.0

regards,

 
 
 

SQL 6.5 Performance vs SQL 7.0 Performance

Post by Christoph Muthman » Thu, 19 Aug 1999 04:00:00


Hi Lim,
this is what I found last week in the newsgroup, maybe it can help you.
Please let us know, if you have any success.

---> copy from the newsgroup
 I know that servaral people here had problems with this issue. Here
 what we found, when we had the problem.

 Problem:
 You create a stored procedure that runs just fine using Query Analyzer.
 (In our case 19 seconds.) The stored procedure is likely to contain
 cursor or loop operations.

 You execute the exact same stored procedure as a scheduled job using T-
 SQL. It now takes a very long time to run (in our case 20 minutes).

 This is a known issue to Microsoft.

 Workaround 1:
 (What Microsoft told me to do)
 Create a CMDExec task and run this step of the scheduled job as osql.
 This works. You get the same performance as you got in Query Analyzer.

 Workaround 2:
 (Found that in an other discussion group)
 In the beginning of your stored procedure include "SET NOCOUNT ON".
 This supresses messages like "X rows affected". You tend to get a lot
 of those messages in cursor or loop operations.
 Effect: Your stored procedure is likely to run faster in Query Analyzer
 (in our case from 19 to 4 seconds)
 Your stored procedure takes up the same time in a scheduled job (using
 T-SQL) as in Query Analyzer (4 seconds instead of 20 minutes)
<--- end of copy from the newsgroup

Have a nice day,
Christop
Seh Eing, Lim schrieb in Nachricht ...

>hello,

>Has anyone encountered performance problem when porting 6.5 stored
procedure
>to 7.0?

>The performance degrades almost 100%. Something which took 1min to run in
>6.5 runs 2min in 7.0.

>My procedure has lots of tmp table creation and use of cursors. Are these
>the reason for the performance loss? I am really puzzled coz 7.0 is
supposed
>to outperform 6.5 many times over. If those were the reasons, what is the
>alternative to using tmp tables to manipulate data?

>Anyone has any clues? This is a big stumbling block to my company upgrading
>to SQL 7.0

>regards,



 
 
 

SQL 6.5 Performance vs SQL 7.0 Performance

Post by Tibor Karasz » Thu, 19 Aug 1999 04:00:00


I don't know how much this could do, but might be worth a try:

Move the creation to the beginning of the stored procedure. This minimizes
recompilation of the proc.

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.



> hello,

> Has anyone encountered performance problem when porting 6.5 stored
procedure
> to 7.0?

> The performance degrades almost 100%. Something which took 1min to run in
> 6.5 runs 2min in 7.0.

> My procedure has lots of tmp table creation and use of cursors. Are these
> the reason for the performance loss? I am really puzzled coz 7.0 is
supposed
> to outperform 6.5 many times over. If those were the reasons, what is the
> alternative to using tmp tables to manipulate data?

> Anyone has any clues? This is a big stumbling block to my company
upgrading
> to SQL 7.0

> regards,


 
 
 

SQL 6.5 Performance vs SQL 7.0 Performance

Post by Neil Pik » Thu, 19 Aug 1999 04:00:00


Seh,

> Has anyone encountered performance problem when porting 6.5 stored procedure
> to 7.0?

Q.  Should I upgrade to SQL 7.0?  Why?  Why not? What are the known bugs?  What
differences are there?
(v2.02 1999.08.16)

A.  Apologies for the length of this FAQ article, but there is a lot of stuff
to cover.

General Stuff
-------------

SQL 7.0 generally has a much more stable and better performing kernel than SQL
6.5 SP5.  For every bug you find with the basic kernel of SQL 7.0 there will be
20 known bugs with SQL 6.5 SP5, and an order of magnitude more of unknown ones.  
However saying that there are going to be a small percentage of queries that
will run slower or fail under SQL 7 than under 6.5.  As these are reported they
will be improved/fixed in future service-packs, but the re-writes to make 99%
of queries run faster is going to have a detrimental effect somewhere.  Some
specific points are noted later.

SQL 7.0 shouldn't be treated as a version 1 release - even though most of the
code has been re-written, it has had far more testing (both internal and
external to MS) than any previous version of SQL Server - though as always, if
in doubt, wait for a service pack or two to come out and check the SQL
newsgroups/mail lists to see the problems that early adopters are experiencing
(if any).  Note that SP1 is released.

A concerted effort was made to test and fix all reported bugs with 6.5 on the
7.0 code-base.  In addition an automated testing tool that generated and
validated hundreds of complex SQL queries every minute was written and run
against SQL 7.0.

Most of the original bugs/issues were with the new features such as text
indexing, DTS gui and the MMC tools - many of these are cleared up in SP1.  SP1
itself underwent a significant beta test.

Where you do get errors with the kernel these could well be to do with SQL 7's
more strict checking of SQL/ANSI rules.  Though many of these are picked up and
even simulated (yes MS deliberately reproduced broken code in some instances)
under the 6.5 compatibility mode, you may find extra things "break" when you go
to full SQL 7.0 mode.  (see sp_dbcmptlevel in the docs).  You need to go to SQL
7.0 mode for new syntax and stuff so it is recommended that you do this as soon
as possible.

For any new release you are advised to apply and test on non-production systems
first, and if at all possible do a stress-test so that any problems that only
occur under stress are found before rolling out to a production system.

Some known bugs/issues with SQL 7 are discussed below - for the official
current list search the MS knowledgebase for sql70 and kbbug.

The rest of this article is entitled "SQL 7.0 - the Good, the Bad and the
Ugly".  Stuff that isn't in the documentation - or doesn't jump out at you from
the docs.  Also things to think about and check out before/during a SQL 7
migration.

Install
-------

1.  Some things to check before a SQL 7 upgrade :-

   All db's are checkdb'd, newalloc'd and checkcatalog'ed
   Free space in master for new 6.5 system procs - 5Mb or so should do
   At least 10Mb tempdb
   At least 6.0 SP3 or 6.5 SP3 is required on the source system
   Syscomments source is available for all sp's, triggers etc.

Good
----

1.  Speed.  Speed is overall much better than with 6.5 - there are exceptions,
noted in the bad/ugly sections where known.  As an example, the speed of a
large batch processing suite was improved by 300% by going to SQL 7 from 6.5 -
a 200% increase was achieved just by migrating with no changes and the extra
improvements were made by optimiser fixes in SP1, plus changing bcp to BULK
INSERT, parallel index creates and some re-writes of queries that were manually
optimised in the past for SQL 6.5.  The batch run consists of about 30 separate
jobs each with 5-10 steps, consisting of bcp in of data, index creation, lots
of select into's on criteria, lots of group by's/aggregation etc.  Time taken
has reduced from 10 hours to 3hrs 20 with no change in hardware/memory etc.

2.  The optimiser in SQL 7.0 is far more powerful than in 6.5, utilising many
more types of join, multiple indexes etc.  It is worth looking at old SQL that
may have been split into multiple steps in SQL 6.5 or earlier because the
optimiser couldn't handle things like > 4 way joins or multiple aggregations.  
This SQL could be re-written back into one large query for better performance.  
The downside to all these optimiser improvements is that it can take
significantly longer to work out the best query plan compared with 6.5 and can
result in increased cpu usage.

3.  Reliability.  You should get far fewer AV's with SQL 7.0 than with 6.5.  
Also fewer database corruptions - to date, 6 months from SQL 7's RTM, there has
not been a single instance of database corruption with SQL 7.0 that was not
caused by hardware failure (info provided by MS PSS).

N.B.  I've found SQL 6.5 SP4 and above to be pretty resilient to corruption.  
Make sure all 6.5 systems are on at least SP4 - I've seen about a dozen systems
needlessly fall victim to a pre-SP4 bug with LRU corruption when buffers get
stressed on SMP systems.

4.  Create indexes concurrently.  Under 6.5 this would cause locks on system
tables and the indexes would build serially.  Under 7.0 if you kick off creates
of non-clustered indices on the same table at the same time, from separate
connections, they will all create concurrently.  The re-use of the read-ahead
buffers from the main table gives near linear performance.  Especially useful
for data-warehouse/MI type systems with lots of NC indexes on large tables.

5.  Max Async IO.  Under 6.5 if you allocated max async io "too high" then
performance would nose-dive.  With 7.0 this doesn't seem to be the case - the
law of diminishing returns still applies, but max async io can be set much
higher - even to the maximum of 255 - without worrying too much.

6.  DBCC performance.  On complex/fragmented databases I have seen DBCC
performance for a full checkdb/checkcatalog of 60 times faster than the
equivalent checkdb/newalloc/checkcatalog on a 6.5 system.  For non-fragmented,
simple databases where not so much disk head movement was needed anyway under
6.5 I still see dbcc times that are twice as fast.

7.  Query parallelism.  Really does work.  Seen large queries greatly increase
in performance by running on an otherwise identical 4-way box compared to a
2-way.

8.  Database size.  Due to the new 8K page size and improvements in the storage
of text/index data most databases will shrink in size when migrated to SQL 7.  
This isn't guaranteed and there are lots of factors involved (see NC index
stuff in the ugly section), but reports of 50Gb SAP systems shrinking to < 30Gb
are common.

9.  Under 6.5 the realistic maximum size systems that you could put on SQL
Server was around the 300Gb mark due to backups/healthchecks/recovery taking
too long.  With 7.0 this rises to around the 1Tb mark and quite possibly higher
given appropriate hardware.  (SQL 7 has been backed up at 600Gb per hour using
a stripe of 24 AIT tape drives - and more importantly restored at 500Gb/hour!).

Bad
---

1.  BCP slower.  Under many circumstances a BCP in to a file with few indices
will be slower under SQL 7.0 than 6.5 - this is probably due to the overhead of
using OLE-DB internally, however it should mean an end to problems caused by
using fast-bcp and it's bypassing of certain SQL checks.  With many NC indexes
it is quicker than 6.5 though.  Using the new BULK INSERT command returns it to
as fast or faster than BCP due to the removal of context switches.  Setting
table-lock on really helps due to the extra overhead of row-locks.  I've seen
jobs reduce from 80 mins to 50 mins with this change.

2.  MMC.  The MMC is dog-slow regardless of the power of the machine it is
being run on.  Service Pack 1 noticeably improves the speed (and reliability)
of MMC.

3.  If you repeatedly open server-side cursors with small resultsets then you
will see significantly slower performance from SQL Server.  This because the
query is being optimised each time and this takes time with SQL 7.  This is
fixed in SP1 (there was a trace flag needed for the original hotfix but the
default behaviour is changed in SP1).  After applying SP1 the plans generated
for cursors are kept like they are for stored-procedures.  See Q197800 for more
details.

4.  Large queries monopolising system.  This was an issue with SQL 6.5, but if
anything it is "worse" with 7.0 - the queries generally complete a lot quicker
with 7.0 though.  If you have a very large query running and it grabs all the
processors to run it will make the system very slow/unresponsive to other users
and queries.  SQL dynamically decides how many processors to use for a query
when it kicks off, and if there is no other activity at the time it will be
given all the processors by default.  This is not necessarily what you want.  
The max number of processors a query can use can be set per query (MAXDOP hint)
or via sp_configure for a server wide setting - the default is all cpu's.  Once
a query has started and has grabbed all the cpu's it can't give them back.

5.  Long stored-procedures that create lots of temporary tables as they go can
take a lot longer to run under SQL 7.0 - this is due to SQL 7.0 re-evaluating
the sp every time a new temporary table is created.  The idea behind this is
that it can then choose the best access plan, but re-evaluating is an overhead.  
Create all temp tables at the start of the stored-proc if possible.

6.  From another FAQ entry.  Q.    If I lose my data file (MDF) in SQL 7 I
can't backup the log to recover to a point in time - why not?

A. A small oversight on MS's part - which will be fixed in the next major (not
service-pack) release of SQL.

Under 6.5 you would do BACKUP LOG <dbname> TO <device> WITH NO_TRUNCATE.

Under SQL 7 this gets ...

read more »

 
 
 

SQL 6.5 Performance vs SQL 7.0 Performance

Post by Chris Woo » Thu, 19 Aug 1999 04:00:00


Christoph,

Did you try switching the statistics off on termpdb? I believe that this
will make a difference too!

Chris Wood
Alberta Department of Resource Development
CANADA


> Hi Lim,
> this is what I found last week in the newsgroup, maybe it can help you.
> Please let us know, if you have any success.

> ---> copy from the newsgroup
>  I know that servaral people here had problems with this issue. Here
>  what we found, when we had the problem.

>  Problem:
>  You create a stored procedure that runs just fine using Query Analyzer.
>  (In our case 19 seconds.) The stored procedure is likely to contain
>  cursor or loop operations.

>  You execute the exact same stored procedure as a scheduled job using T-
>  SQL. It now takes a very long time to run (in our case 20 minutes).

>  This is a known issue to Microsoft.

>  Workaround 1:
>  (What Microsoft told me to do)
>  Create a CMDExec task and run this step of the scheduled job as osql.
>  This works. You get the same performance as you got in Query Analyzer.

>  Workaround 2:
>  (Found that in an other discussion group)
>  In the beginning of your stored procedure include "SET NOCOUNT ON".
>  This supresses messages like "X rows affected". You tend to get a lot
>  of those messages in cursor or loop operations.
>  Effect: Your stored procedure is likely to run faster in Query Analyzer
>  (in our case from 19 to 4 seconds)
>  Your stored procedure takes up the same time in a scheduled job (using
>  T-SQL) as in Query Analyzer (4 seconds instead of 20 minutes)
> <--- end of copy from the newsgroup

> Have a nice day,
> Christop
> Seh Eing, Lim schrieb in Nachricht ...
> >hello,

> >Has anyone encountered performance problem when porting 6.5 stored
> procedure
> >to 7.0?

> >The performance degrades almost 100%. Something which took 1min to run in
> >6.5 runs 2min in 7.0.

> >My procedure has lots of tmp table creation and use of cursors. Are these
> >the reason for the performance loss? I am really puzzled coz 7.0 is
> supposed
> >to outperform 6.5 many times over. If those were the reasons, what is the
> >alternative to using tmp tables to manipulate data?

> >Anyone has any clues? This is a big stumbling block to my company
upgrading
> >to SQL 7.0

> >regards,


 
 
 

SQL 6.5 Performance vs SQL 7.0 Performance

Post by Christoph Muthman » Sat, 21 Aug 1999 04:00:00


Hi Chris,
we are still testing the migration to SQLServer 7.0, so I will keep this in
mind.

Greetings to one of my favourite countries,
Christoph

Chris Wood schrieb in Nachricht ...

>Christoph,

>Did you try switching the statistics off on termpdb? I believe that this
>will make a difference too!

>Chris Wood
>Alberta Department of Resource Development
>CANADA



>> Hi Lim,
>> this is what I found last week in the newsgroup, maybe it can help you.
>> Please let us know, if you have any success.

>> ---> copy from the newsgroup
>>  I know that servaral people here had problems with this issue. Here
>>  what we found, when we had the problem.

>>  Problem:
>>  You create a stored procedure that runs just fine using Query Analyzer.
>>  (In our case 19 seconds.) The stored procedure is likely to contain
>>  cursor or loop operations.

>>  You execute the exact same stored procedure as a scheduled job using T-
>>  SQL. It now takes a very long time to run (in our case 20 minutes).

>>  This is a known issue to Microsoft.

>>  Workaround 1:
>>  (What Microsoft told me to do)
>>  Create a CMDExec task and run this step of the scheduled job as osql.
>>  This works. You get the same performance as you got in Query Analyzer.

>>  Workaround 2:
>>  (Found that in an other discussion group)
>>  In the beginning of your stored procedure include "SET NOCOUNT ON".
>>  This supresses messages like "X rows affected". You tend to get a lot
>>  of those messages in cursor or loop operations.
>>  Effect: Your stored procedure is likely to run faster in Query Analyzer
>>  (in our case from 19 to 4 seconds)
>>  Your stored procedure takes up the same time in a scheduled job (using
>>  T-SQL) as in Query Analyzer (4 seconds instead of 20 minutes)
>> <--- end of copy from the newsgroup

>> Have a nice day,
>> Christop
>> Seh Eing, Lim schrieb in Nachricht ...
>> >hello,

>> >Has anyone encountered performance problem when porting 6.5 stored
>> procedure
>> >to 7.0?

>> >The performance degrades almost 100%. Something which took 1min to run
in
>> >6.5 runs 2min in 7.0.

>> >My procedure has lots of tmp table creation and use of cursors. Are
these
>> >the reason for the performance loss? I am really puzzled coz 7.0 is
>> supposed
>> >to outperform 6.5 many times over. If those were the reasons, what is
the
>> >alternative to using tmp tables to manipulate data?

>> >Anyone has any clues? This is a big stumbling block to my company
>upgrading
>> >to SQL 7.0

>> >regards,


 
 
 

1. performance of SQL 7.0 vs. SQL 6.5

Wojtek,

 What about it?

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see www.ntfaq.com/sql.html
 and http://www.swynk.com/faq/sql/sqlserverfaq.asp
 and GO MSSQL Lib 1 on Compuserve

2. Multiple databases or schemas

3. SQL 6.5 & SQL 7.0 Replication Performance

4. Help with blobs and word (saving, retrieving and editing)

5. SQL 2000 vs SQL 7.0 Performance

6. Portals...about to give up.

7. sql 7.0 vs sql 2000 restore performance

8. Performance problem: SQL Server 7.0 vs SQL Server 2000

9. 6.5 7.0 Upgrade SQL Server Performance Issues Stored Procedure

10. SQL 7.0 Performance Problem after 6.5 Upgrade

11. SQL 7.0 Performance slower than 6.5

12. VB4 RDO Performance: SQL Server 6.0 vs 6.5