> 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?
A. Apologies for the length of this FAQ article, but there is a lot of 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
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
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.
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
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
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!).
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)
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
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 »