Migration from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Serv

We are migration few database from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Server)

Problem :

- The performance difference observer was not much (Just 15-18%)

- The CPU Utilization goes to 100% immediately, when the software login screen appears and it sustains in between 90% to 100%

- Multi-User Access (3 or more concurrent users), the sql performance is tremendously slow.

Steps followed for Migration:

- We have used the backup and restore method to migrate the data from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Server).

- We ran the DBCC DBREINDEX for each affected tables

- We executed the sp_updatestats procedure for all the tables

- Upgraded the DB compatibility version to 90

- Installed the SQL 2005 Enterprise Server (64 bit-Itanium Server) Service pack 2.

One thing that we observed, was the memory usage, on SQL 2000 the sqlservr.exe uses around 1.5 GB where as on the SQL 2005 its just 120 MB.

A normal select query on 2000 Server took 11-12 sec, having 10 lac records, and the same query on 2005 took 1 min (3-5 sec CPU process taken by sqlservr and rest by the Sever Management Studio.

The only differance noted was that on 2000 the entire data was displayed at one go where as in 2005 the data was displayed at the 1st sec, and was filling the rest in the buffer, and to finally display entire data it took 1 min.

Also

when we run this query

select * from sys.dm_os_sys_info

one column os_priority_class shows a value = 32 ? what does this means.

In the MSSQLSERVER SERVICES, properties, there is a parameter displaying value

Advanced > Running Under 64 bit OS = No ? what does this means.

Could anyone pls suggest as to how to increase the sqlservr.exe memory area, and also how the enhance the performance.

Hi Ashok,

Running Under 64 bit OS =No means that Operating System is not 64 bit version, you are running 64 bit Version of SQL Server on Operating System which is 32 bit.

Regarding SQL Server Memory it is Dynamically Allocated and De Allocated unless you have specified using sp_configure or via SSMS, another thing you need to refer is have you or some one change the values for network packet size / Min memory / maximum memory value

have you set any option for max degree of parallelism Or max worker threads

If you do have more then 2 GB memory you need to use AWE OR PAE switch in order to use memory more then 2 GB , also refer Estimated and Actual Exucution Plan in both the server to get idea for taking too much time in new server.

Refer http://www.sql-server-performance.com/statistics_io_time.asp

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17451

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17732

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18192

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17107

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=16988

HTH
Hemantgiri S. Goswami