January 27, 2004 at 8:56 pm
Hi All,
We have just upgraded a server from SQL 7 to SQL 2000 and the performance seems to have slowed somewhat on the server. Prior to the upgrade CPU usage was almost always below 10%. However, now the usage jumps up to 100% frequently and is almost never below 50%.
The application using the database on this server submits and gets its results in the form of stored procedures. Many of the stored procedures are dynamic sql ie the query is built based on what parameters you pass to the stored procedure.
I have followed the section in the KB article 224587 relating to SQL Profiler and cannot find anything that goes against the suggestions in this article.
If anyone out there has had experience upgrading from sql 7 to sql 2000 I would love to hear from you on things I can look out for.
Thanks,
Terry
January 27, 2004 at 10:05 pm
Hi there
Well this is strange. I would consider:
a) update db statistics for starters
b) check default db options and auto create stats is enabled
c) attempt a dbreindex and updateusage
Profile off selected stored procs; run them via query analyser and check the plans generated; are you assigning incoming parameters to local variables and using them?? try this as well if sp's are not coming back (and previously where in quick time), you may get a rude supprise...
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
January 27, 2004 at 11:06 pm
Also, any adverse blocking for whatever reason??
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
January 29, 2004 at 8:35 am
Did you update statistics (exec sp_updatestats) after migrating the database?
January 29, 2004 at 10:28 am
When you did the upgrade, was it an upgrade or did you install a new instance of SQLServer 2000 and still have the old one running on the same machine.
If it did move to another machine, are the CPUs the same speed?
Is the instance configured to use the same amount of resources?
There was nothing that I could find about performance degration after an upgrde in the MS site, but you may want to put a post out there too.
Good Luck
February 2, 2004 at 7:41 am
If you run sp_helpdb does the compatibility_level always read 80?
February 2, 2004 at 6:53 pm
A significant change in performance is usually the result of a 'different' query plan.
(1) run a trace to find which queries are slower then before
(2) Look for the use of 'implicit' typecasts in the where clause. SQL 2000 will NOT perform implicit downcasts. This may lead to a table scan in cases where SQL 7 used the index.
Here is an example:
create table demo( name varchar(100) not null primary key );
select *
from demo
where name = N'John'
The literal value N'John' is of type nvarchar (N prefix). The name column is of type varchar. varchar is a less encompasing datatype then nvarchar so casting from nvarchar to varchar is called a 'downcast'. Since information could potentially be lost when 'downcasting' SQL 2000 won't do it without your help. Instead it 'upcasts' the name column to nvarchar. Because it has to 'upcast' the name column the index on name can't help narrow down the rows to be processed. Instead every single name value in the table is processed!
The Query Optimizer in SQL 2000 is ALLOT more sophisticated then it was in SQL 7. A consequence of this is that it is also ALLOT more picky about doing things that just might be illegal. SQL 7 would happily perform implicit downcasts (nvarchar to varchar, int to smallint, etc). SQL 2000 will NEVER perform an implicit downcast.
Its good programing practice to not rely on implicit type-casting.
February 2, 2004 at 10:44 pm
Terry
Any results/action taken from these suggestions?? good to clear up this posting with the action plan you have taken for others in the future.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
February 3, 2004 at 9:35 pm
Hi All,
Many thanks to all suggestions. I checked and performed all the obvious such as updating indexes, and setting of db options.
However, I found that the database 'sorted' itself out over a couple of days. A suggestion by Ron was that some tuning occurs after the upgrade. My only thoughts are that perhaps the execution plan for the stored procs are regenerating? If anyone can point me to a Microsoft document that confirms this that would be wonderful.
For any would be upgraders out there....my only suggestion is to make sure you practice and test until you are comfortable. That includes testing the different components of any application used to access the database.
Once again, many thanks!
Regards,
Terry Pino
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply