Personally, I think the vendor is grasping at straws - Compatibility Level
70 means that a database is to be treated as if it were running under SQL 7
instead of 2000.
When you originally installed SQL 2000, the tempdb, master, msdb, and model
would ALL be set to Level 80, in other words SQL Version 8. Even if you
upgraded from SQL 7, these 4 databases would have been changed to 80. Simply
installing the Service Pack would not have changed the compatibility level
to 80 because it would have already been there.
In terms of your own database, it may have been set to 70 but that would not
explain the performance increase. Setting it back to 70 would only POSSIBLY
change the Collation (sorting sequence) which would affect your clustered
indexes (meaning the way the data is physically stored in the files) and
POSSIBLY the order in which SELECT statements return data without an ORDER
BY clause. It would also mean that any User Defined Functions would no
longer work as they were not available in SQL 7.
I would look at running some traces on the database and then using the
results of that trace to optimize your indexes with the tuning wizard.
Trevor
-----Original Message-----
From: SIDNEY D IVES
[mailto:mssqldba-ezmlmshield-x27048190.[Email address protected]
Sent: Wednesday, August 30, 2006 3:42 PM
To: LazyDBA Discussion
Subject: RE: SQL Compatibility Level
If you think the svc pak may be the cause then back it out if you can.
If not research how the sp may have effected I/O, or memory depending
upon the problem area.
I would change it to 70 and continue researching the reason as to why
the load has doubled. When you say "doubled" I assume you mean about
the same volume of data but increased load time?
Determine if the bottle neck is I/O related or Processor related (
Perfmon will reveal which one), then drill down on the appropriate
area.
Sidney Ives
Database Administrator
Sentara Healthcare
Ofc.: (757) 965-0117
Fax: (757) 228-1956
[Email address protected]
>>> "Eric Emrich "
<mssqldba-ezmlmshield-x15468393.[Email address protected] 8/30/2006
2:18 PM >>>
Yes, it's going down.
I forgot to mention that we're running SQL 2000, Enterprise.
One of our loads has doubled (and in some cases, tripled) in load
times. We upgraded SQL 2000 to SP4 and that changed our compatibility
level to 80 from 70. This caused the vendor's application to stop
working. Changing it back to 70 resolved the issue and we were able to
continue working. Well, during that same SP install, it changed all the
databases to 80 (including the tempdb). They are asking that we change
it back. I'm hesistant to do this since there are other databases on
that instance.
We are trying to investigate as to why the load times have increased
dramatically. They may just be grasping at straws.
-----Original Message-----
From: SIDNEY D IVES
[mailto:mssqldba-ezmlmshield-x27481903.[Email address protected]
Sent: Wednesday, August 30, 2006 1:40 PM
To: LazyDBA Discussion
Subject: Re: SQL Compatibility Level
I would be concerned if there were multiple db's on the server used by
different applications but if the server is dedicated to one product
then give it a shot. It's not a difficult change to make and can
easily
be reversed. I've never heard of doing this to solve performance
problems but the vendor may have inside knowledge of some obscure
feature within the application.
If you feel the vendor is grasping at straws then ask for the support
rep. to confer with a supervisor. Otherwise, I would say make the
change and see if it improves the performance. If nothing else, it
would eliminate it as a possible cause of performance issues. If
performance doesn't improve then return it back to it's original
value.
I've changed compatibility before but most of the time going up not
down (I assume down in your case).
Sidney Ives
Database Administrator
Sentara Healthcare
Ofc.: (757) 965-0117
Fax: (757) 228-1956
[Email address protected]
>>> "Eric Emrich "
<mssqldba-ezmlmshield-x69938614.[Email address protected] 8/30/2006
12:09 PM >>>
In an effort to track down performance problems, our vendor has
suggested that we change the Compatibility Level to 70 for the tempdb.
Is this advisable? Has anyone else done this with no repercussions?
TIA
________________________________________________________________________
_
This message and any attachments may be confidential or privileged and
are for the exclusive use of the intended recipient. If you are not
the
addressee or intended recipient, please do not read, copy, use, or
disclose this communication to others. If you have received this in
error, please notify the sender by replying to this message, and then
delete it entirely from your system. Delivery of this message and any
attachments to any person other than the intended recipient(s) is not
intended in any way to waive confidentiality or a privilege. All
personal messages express views only of the sender, which are not to
be
attributed to Finish Line and/or any of its subsidiaries or
affiliates,
and may not be copied or distributed without this statement.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com ( http://jobs.lazydba.com/ )
( http://jobs.lazydba.com/
)
To subscribe : http://www.LazyDBA.com ( http://www.lazydba.com/ ) (
http://www.lazydba.com/ )
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com ( http://jobs.lazydba.com/
)
To subscribe : http://www.LazyDBA.com ( http://www.lazydba.com/ )
To unsubscribe: http://www.lazydba.com/unsubscribe.html
_________________________________________________________________________
This message and any attachments may be confidential or privileged and
are for the exclusive use of the intended recipient. If you are not the
addressee or intended recipient, please do not read, copy, use, or
disclose this communication to others. If you have received this in
error, please notify the sender by replying to this message, and then
delete it entirely from your system. Delivery of this message and any
attachments to any person other than the intended recipient(s) is not
intended in any way to waive confidentiality or a privilege. All
personal messages express views only of the sender, which are not to be
attributed to Finish Line and/or any of its subsidiaries or affiliates,
and may not be copied or distributed without this statement.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com ( http://jobs.lazydba.com/
)
To subscribe : http://www.LazyDBA.com ( http://www.lazydba.com/ )
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page