Old server running faster than old?
-
Server 1(bought new in 1999)
Quad 750mhz Pentium Xeon 512mb Level 2 cache
4 gig memory
Win NT 4.0
SQL Server 7.0
10k rpm drives
Tempdb size - about 2gb
Wide table with 10,000,000
Table size - about 19gb
query ran for 1 minute, pulled down 180,000 rows
Server 2(bought new in 2003)
Quad 2.5ghz Pentium Xeon 1gb Level 2 cache
6 gig memory
Win 2000 Server Advanced Server
SQL Server 2000 Enterprise
10k rpm drives
Tempdb size - about 10gb
Same table with about 600,000 rows
Table size - about 2gb
Same indexing
Same query ran for 1 minute, pulled down 65,000 rows
Both DBCC UPDATEUSAGE and UPDATE STATISTICS have been ran on the table on Server 2. Ran DBCC CHECKTABLE with no problems reported on either server.
Queryplan shows minor difference in percentages on different steps, but basically the same queryplan. Changed query to just pull COUNT(*) and the rowcounts were the same on both servers. This test has been ran at different points during the day to eliminate the possibility of network traffic causing the problem.
Server 2 should wipe the floor with Server1! What is going on?
Live to Throw
Throw to Live
Will Summers -
Sorry about that - title should read - 'faster than new', not 'faster than old'
Live to Throw
Throw to Live
Will Summers -
Both servers are using the same character set, sort order and accent order.
Live to Throw
Throw to Live
Will Summers -
I have ran the queries multiple times at many points during the day - same response times.
The queries are returned into text, not a grid.
Servers are in the same room, both are running 10/100 cards. They both hit the same switch/router.
Active Directory - no
I can manually adjust the amount of memory that SQL Server takes on the new machine. The slide bar goes up to 5375mb. Does this mean that SQL Server sees the extra memory?
I have not manually put the PAE or AWE switch in the boot.ini file.
Yes, top 50,000 would probably be a better way to do it. I run the query on one server and stop the query when it hits one minute.
The traffic on the new server is frequent small bursts and the traffic on the old server is less frequent longer bursts. I have ran the queries on each server at multiple points during the day and the response times vary very little.
Both servers have not slowed down or sped up in the last couple of years.
Live to Throw
Throw to Live
Will Summers -
Keep it up... you'll find it eventually (still have no clue)
.Just for luck, can you post the execution plan for both servers?
-
Both servers have the same database except for the smaller row counts on the new server.
Ok, here's what I did yesterday:
Defragged all drives - (Diskeeper - boottime and runtime)
Added /3GB switch to boot.ini (Already had /PAE switch)
Downloaded all updates (nothing major, just root certificates)
Turned on Load Balancing on network card - dual channel)
Removed Named Pipes from protocol list in SQL Server
Rebooted numerous times
Based on tests my boss ran, we are seeing a 8-9% increase in response time. Still not beating the old server, but I am thinking the bottleneck is the network card. It's running at 10mps. The old server is running at the same network speed. That still doesn't explain why the old server is beating the new server. More research is needed.
Any ideas?
Live to Throw
Throw to Live
Will Summers -
I've ran queries against the same server with standard 100MB and then with my PC switch port put down to 10MB. Same query, same row count took over 3 times longer.
What if you run some queries on the servers themselves?
Use perfmon to track CPU , disk, RAM, network etc.
When I upgraded a server once (new one is as above), I saw the spike in CPU, RAM etc and this shortened on the new server, but the network card traffic still peaked for the same amount of time...
-
Turned off autonegotiate and set it to:
100mps full - network card couldn't communicate
100mps half - network card couldn't communicate
10mps full - network card ran ok
10mps half - network card ran ok
Looks like 10mps is the best that I can do sitting on this network.
I also ran the same query on both servers. About the same number of rows on each machine. The rows started to come back almost instantly on both machines. Looks like the bottleneck at the server is disk access. Both machines are running 10k drives.
Live to Throw
Throw to Live
Will Summers -
As you metioned there is usage difference on both the servers. 2 servers can never be the same as they might have different activites going at any given point. Most of the tuning points have been mentioned. One last step could be to run trace on both the servers and compare the stats on I/O just to see what the differences are.
Cheers,
Babu.
-
I have ran the tests at multiple points during the day with varying loads on each servers. We always get the same results within a couple hundread rows returned.
Live to Throw
Throw to Live
Will Summers
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply