SQL Server Performance Forum – Threads Archive

sql server 2005 64-bit , itanium 2 and SAN Storage

hi friends , we are in the process of migrating our sql server 2000 enterprise ed running on compaq xeon cluster based on 4 32-bit xeon processors ,4 gb ram and win 2000 enterprise to sql server 2005 enterprise ed running on HP integrity cluster server based on 4 64-bit itanium 2 processors ,15 gb RAM , win 2003 enterprise 64-bit and attached to 5 tera HP SAN storage we took a backup of the database running on sql 2000 and resotred it on sql 2005 but we faced the following problems ( all related to performance ) 1: when runing a query against sql 2005 only one processor is utilized ; that is one procesor activity goes up to 100% percent but all the other 3 remains below 15 % 2 : comapring results of the same query from sql 2000 and sql 2005 on the same database return 1 million record sql 2000 : 1m 22 sec sql 2005 : 6m 15 sec ( too strange ) so , we installed the 32-bit sql query analyzer on the sql 2005 machine (64-bit) and run the same query on them return 1 million record query analyzer on the sql 2005 machine : 1m 20 sec management studio on the sql 2005 machine (query plan should be saved) : 4m 40sec we rebuilt all the indexes but still this performance bottleneck i don’t know what is going on ( the new server should be faster 6 times than the old one) the database is resored on the san storage but the storage seems to be very fast when copying from or to i tested the same query on a server with 2 32-bit xeon processors, win 2003 std and sql 2005 and it ran very fast in comparison to the itainum server could any one tell me what is missed i will appreciate this so much thanks

1. the query is probably running with a non-parallel plan, did it run with a parallel plan on another system?
2. was all the tests done on the local system, ie, is this a test of network bandwidth?
if the difference is between Query Analyzer and SQL Management Studio, it would not surprise me if QA is faster than MgmStud.
make sure both are displaying results in grid mode.
if you just want to test SQL Server speed, query the aggregate of 1M rows instead of returning 1M rows. depending on which Xeon and Itanium procs you are on, you could get any result
on an individual proc basis, the Xeon and Opt should be faster than the Itanium,
this is because recent Xeon & Opt are manufactured on 90nm process and the current Itanium is still on 130nm, also, Itanium is probably heavily thermally limited, ie, the 130nm It should probably be running at over 2GHz if it was design with more power efficiency. however, the Itanium 2 proc has excellent scaling on large platforms, ie, over 4 proc,
so it really did not make sense to go from 4 proc Xeon to 4 proc Itanium, unless you needed to go to full 64-bit versus 32-bit.
but if you needed to go to 16 proc, then Itanium probably makes sense

thanks joechang for ur response 1: yes ,it seems that sometimes the query doesnot run in a parallel plan , i found it goes on parallel on other system , do u think forcing a parallel plan is necessary or what ? 2: yes , tests are all locally on sql server 2005 and from network for sql 2000 ( look here sql 2000 should be slower as it is queried remotely ) 3: yes, we got the itanium bcz we wanted all 64-bit configs ( hardware , windows server 2003 64-bit , sql 2005 enterprise 64-bit ) thanks again for ur response , waiting for ur advices

the automatic parallel plan feature in SQL 2000 is really dangerous, benefitting some queries, severely crippling the system for other queries. in SQL 2005, parallel plans seem to be much better with nearly no negatives. i think you will need to understand parallel plans better to understand why it is used and beneficial also understand that the Itanium proc has very different characteristics than Xeon or Opteron,
even though an Opteron may have the best overall SQL Server performance in 2-4 proc systems,
the Xeon excells at table scan ops, so may very easily outpace either the Opteron and Itanium. ie, do not draw too many conclusions from a single test. what specifically did you want to know?

************************************************ 1: yes ,it seems that sometimes the query doesnot run in a parallel plan , i found it goes on parallel on other system , do u think forcing a parallel plan is necessary or what ?
************************************************ How do you force a parallel plan?

http://www.sql-server-performance.com/q&a96.asp
http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_120705.mspx advice. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>