SQL Server Performance Forum – Threads Archive
Old server running faster then new?
Server 1(bought new in 1999) Quad 750mhz Pentium Xeon 512mb Level 2 cache4 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
How do you run Update Statistics, with full scan?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
Use Query Analyser to look at the execution plan, you’ll find the hoild up that way.
The table structure and indexes are the same on both servers. The faster machine has a smaller table. There is no reason that I can see that the 2.5ghz machine should be running so slow. Not to be a pain, but I don’t see the reason to look into how the query is written if the tables and indexes are the same on both machines. If the tables and indexes are the same, then that should prove that it’s something in the setup of the server or installation of SQL server. Sorry it has taken me so long to respond, I have been on an actual vacation the past two weeks. Live to Throw
Throw to Live
Please check for the following: 1. If /PAE switch is enabled in Boot.ini file
2. If AWE memory is enabled in SQL Server Configuration option. Please check the server options using sp_configure. Thanks Thanks N’ Regards
DJ
From what I know, the /PAE switch is not needed in the boot.ini file if you are running Advanced Server. SQL Server sees the 6gig of memory available. I can manually adjust the amount of memory that SQL Server takes. The slide bar goes up to 5375mb. I have assumed that this meant that SQL Server saw the extra memory. I have not manually put the PAE or AWE switch in the boot.ini file. Live to Throw
Throw to Live
Both servers are using the same character set, sort order and accent order. Live to Throw
Throw to Live
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
Here’s the results of the query: cntr_value counter_name
———– ———————
1682256 Target Server Memory (KB)
1682256 Total Server Memory (KB) I assume that this means that SQL server is not seeing the extra memory over 2gb even though it says so in the memory tab of the properties dialog box of the server. Live to Throw
Throw to Live
try to enable the AWE Thanks N’ Regards
DJ
I just ran it a couple times over the last couple of hours and it returned the same results every time. The memory size is not changing. The mornings are the busiest time for that server. Live to Throw
Throw to Live
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
May be suffering with parallelism, so may try restricting the process to one processor and see the results. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Funny you would say that. On Friday we noticed that 21 processes kick off when we run the query. Hadn’t seen that many on one query before. Why would it be SUFFERING from parrallelism? Live to Throw
Throw to Live
May try to use limited processors for a large running query, run SP_CONFIGURE check max degree of parallelism Option value. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Long story about parrallelism.
Some times, queries execute faster using one processor than more.
To test, open EM, properties, Processor, and live only 1 to queries. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
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
]]>