Migration From SQL 2000 Enterprise Server To The SQL 2005 Enterprise Server (64 Bit-Itanium Server)

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.