Old server running faster than new??

[Will192]

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?

 

[NoCoolHandle]

Disk i/o ?
If the bottle neck is your disks then (same speed) then that will be a part of it.

How is fragmentation?
dbcc showcontig

 

[Will192]

The table on 2 is about 1/9 the size of the table on server 1. That should be the query faster by that fact alone.

Could disk defragmentation only cause this slowdown?

 

[NoCoolHandle]

I suspect index fragmentation could be a huge issue.

When was the last time you rebuilt indexes?

 

[mrdenny]

How is your memory config on the new server? If you check task manager how much memory does SQL show as in use?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Will192]

The table was originally on server 1. We moved it over and then put the index on it. There has hardly been any inserts or updates to the table since then. I wouldn't expect that to be the problem, but I will run a DBREINDEX on the table on server 2. I will post results after it finishes.

 

[NoCoolHandle]

What about database compatibilbiy mode?

 

[NoCoolHandle]

What do you see when you look at the execution plan when you run the query?

Is one using an index and the other not?

Just by chance you don't have a wide (multi col) clustered index do you?

Rob

 

[Will192]

Compatitibility level mode is 8 on server 2.

 

[Will192]

The execution plans are very similar. Both queries are using the same indexes.

The widest clustered index is 3 columns on only one table. The other two are clustered indexes are 2 wide.

The table that each server is quering is actually a view that combines three tables.

I am working on posting a simple version of the query. The table and column names are really long, and the columns returned is around 50.

 

[NoCoolHandle]

Are both db's using the same character set? It the new server running any different processes that could be impacting SQL?

"bizzare" is the word that keeps poping into my mind.

 

[Will192]

Both servers are using the same character set, sort order and accent order.

 

[Will192]

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.

 

[NoCoolHandle]

This is a real stretch, but in the days of SQL 6.5 we used to do alot of manual tuning of memory.

If you gave sQL server too much memory then often perofrmace went backwards. This was because it didn't diferentiate between datacache in ram vrs disk.

For the heck of it, try reducing the amount of ram it can use and see what happens.


Rob

 

[mrdenny]

If you haven't added the memory switches to the boot.ini file or enabled AWE via sp_configure then SQL won't be able to use any memory above 2 GB.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Will192]

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?

 

[NoCoolHandle]

(A bunch of thoughts\questions in no specific order)
------------------------------------------------------
Are the log and datafiles on different disks of the same disk(s)? They should be on different disks.

------------------------------------------------------
You can easily see what the current thruput is via Performance monitor. Bytes Total/sec (Network Interface section)

Then do two tests.
1. that is just a big nasty query that does not thinking and just puts out data.
2. Copy some big nasty files on the filesystem, compare results from both systems.

That should give you and idea as to if it is the network card or sqlserver. If the number never changes it must be the card. Add a second card..or just change the card.

-----------------------------------------------------

If you are running the query from the server, the network card can be eliminated from the issue. I.E. if you are sitting at the terminal for the sqlserver system and you are running query analyser there, it is a pure sql / os / concurrent activitly or disk issues.
-----------------------------------------------------------
Run Perfmon and look at process/cpu usage by process (remove idle and system from the list)
-----------------------------------------------------------

Uninstall and reinstall sql.. (Depending on how many databases and logins you have this might not take too long. You can get the datatransfer wiz to build login scripts just by specifying you want to copy a database. It leaves the script file in a subdirectory of the server you are running the task from. - Kind of like reinstalling the OS. It just doesn't seem to hurt and I have seen it fix some wierd issues.)

Bttom line is that you could build a script to add the databases and logins. Then the uninstall/reinstall shouldn't take too long. (less than 30 min)
--------------------------------------------------------