August 28, 2008 at 4:39 pm
We have discovered a performance problem with our implementaton of 2005 in a clustered environment.
Our current production cluster is runing SLQ 2000 on two 2 processer servers ,2.8 GHz, 3.62 GB Ram with external drives on a netapp for data and logs. Our new 2005 cluster machines are 4 processor- 4 GHz, 16GB ram with the same external drives on the netapp.
In testing an unrealted issure we discovered that the new cluster is returning query results slower than our existing cluster.
Our test is an ugly query which returns over 100,000 rows and sorts on a non-indexed query. Not that we expect to use this query but it brought to light some interesting behavior.
On the existing server the query pegged the local c drive on the cluster where on the new server it pegged the drive where tempdb sits. The server performance monitors indicate an average disk queue length of over 1,700 on the new cluster while it never when past 60 on the old. The query return time is about 6 times slower.
After some reading we understand that SQL 2005 makes use of tempdb more than SQL 2000 did. Tempdb in both cases in on the external netapp but in SQL 2000 it appears to be using the local disk for sorting.
We have made some changes to spread tempdb accross 4 data files initial size of 1000m growth of 10%, still one log file. This did show some improvement but still the bigger faster machine is taking at least twice the time to return the query and disk queue length is still huge. In fact our little develoment box beats the fancy new cluster.... all internal drives.
Has anyone run into this issue and are there any suggestions regarding tembdb in a clustered environment. Is there a way to get SQL to use more memory for sorting rather than disk IO.
August 29, 2008 at 2:28 am
Have you done a full stats update on all tables after the 2005 upgrade? If not, that could result in non-optimal query plans.
Run profiler for a while and capture the following events (under Errors and Warnings) Sort Warning and Hash Warning. They indicate that sorts/hashes couldn't fit into memory and had to bail to disk. It's not a normal occurance ind it's most certainly not desired.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply