Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Old server running faster then new?

Author  Topic 

ws5926
Yak Posting Veteran

88 Posts
Posted - 2005-08-12 : 12:45:56
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

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-08-12 : 14:02:42
Could you post the query and DDL for the tables involved?

Tara
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-08-12 : 15:40:57
Clean the cache before testing.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Run the statements with
SET SHOWPLAN_ALL ON

You can also try some of the other statistics:
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET SHOWPLAN TEXT ON

rockmoose
Go to Top of Page

ws5926
Yak Posting Veteran

88 Posts
Posted - 2005-08-29 : 12:51:51
tduggan - the query is pretty big, about 50 columns returned and all the table names and column names are long also. It's basically just a three table join with two columns per table linking them together. Four join conditions total. What do you mean by a DDL?

rockmoose - How do I clean the cache without restarting SQL Server?

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
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2005-08-29 : 13:21:45
quote:
rockmoose - How do I clean the cache without restarting SQL Server?
He posted the necessary commands.
quote:
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.
You said earler that the table is smaller on one machine than the other. You're also comparing two different versions of SQL Server, and you did not indicate that you updated stats or flushed the caches on the older server. Until you completely level the playing field regarding the software and data, you can't make a reasonable hardware performance comparison.
Go to Top of Page

ws5926
Yak Posting Veteran

88 Posts
Posted - 2005-08-29 : 14:24:06
I have ran the queries two or three times in a row on both servers to get the times that I posted earlier in the thread. That should eliminate any cached queries.

Also, the cache is bigger on the newer system, which should help on the second run of the query.

The newer server runs faster on smaller queries than the old machine, but not on the larger queries.

I will work on posting a the queries and showplans on both systems. I will shorten the table and column names to make it more readable.

Thanks for the quick response.



Live to Throw
Throw to Live
Go to Top of Page

ws5926
Yak Posting Veteran

88 Posts
Posted - 2005-08-29 : 15:28:10
Both servers are using the same character set, sort order and accent order.

Live to Throw
Throw to Live
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2005-08-29 : 15:40:08
quote:
Originally posted by ws5926

I have ran the queries two or three times in a row on both servers to get the times that I posted earlier in the thread. That should eliminate any cached queries.

Also, the cache is bigger on the newer system, which should help on the second run of the query.

The newer server runs faster on smaller queries than the old machine, but not on the larger queries.

I will work on posting a the queries and showplans on both systems. I will shorten the table and column names to make it more readable.

Thanks for the quick response.



Live to Throw
Throw to Live



Where are you running these "1 minute queries"? Are you running a "select * from tablex" with no where clause and then cancelling the query? I would sooner do a top 50000 than the way I just described. Keep in mind that many things affect the amount of rows returned. If you're running the results into SQLQA as a grid in one and text in the other this could be a problem. If your getting the results in SQLQA on your desktop it has to place all those records into RAM and you may have a performance hit after you pull back the query from the old server if you leave those results open. Also if there is any traffic on the new server you could have a hit there. The traffic could lock tables or take up a good amount of network bandwidth. Are both servers gigabit? Or are they at least running at the same port speed? Maybe you have the new server hooked into a 10/100 switch instead of gigabit? Was the server installed back in 2003 when you bought it? Was it faster when it was installed? Are you running AWE for your 6gb RAM? Also as Rob point out you are comparing sql7 with sql 2000 but you must also keep in mind that you are running a different O/S as well. Maybe something like active directory, IIS, or exchange server installed on that box might be what's slowing you down. Is anything else installed on there? Lots of questions.... hehe

Daniel
SQL Server DBA
www.dallasteam.com
Go to Top of Page

ws5926
Yak Posting Veteran

88 Posts
Posted - 2005-08-29 : 16:02:17
I have tried to answer all of your questions below:

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
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2005-08-29 : 16:59:39
quote:
Originally posted by ws5926

I have tried to answer all of your questions below:

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 what I would do.

1) Run the top 50k query from your workstation, not each server, against the old server.
2) Use Grid not text.
3) Review time results.
4) Run "select @@version" to clear up the results pane and clear up the memory used on your workstation. I've run into problems before where I've returned large sets of data via a cartesian product in one window and then had trouble displaying data in another window. SQL QA will actually display a warning that all your memory has been used. lol.
4) Run the same query against the new server and document the time.
5) Compare the time results.
6) Run queries again but instead insert the data into a temp table. This will keep it all server side.
7) Review time results.

I'd check that out just as a very quick test. I'd also do as the others say and look at the other variables by using the showplan and other tips.

You may also want to check the server by doing this:
1) create a 250mb text file on the desktop of your workstation.
2) copy the file to the old server and document how long it took.
3) copy the file to the new server and document how long it took.
4) compare times.

The file copy is a great test because if they both copy in the same amount of time then you can probably rule out a difference in the network hardware.

I dont know much about the AWE and SQL Server using more than 4gb of RAM but if the AWE PAE is not in your boot.ini then I might think that you are not actually addressing all the memory. Maybe someone who has more knowledge about that could give better advice.


Daniel
SQL Server DBA
www.dallasteam.com
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2005-08-29 : 20:27:52
As Daniel indicated, you need to set the AWE switch in boot.ini to ensure your SQL Server will actually address more than 2 GB of RAM, and also make sure to set the "awe enabled" server configuration option:

EXEC sp_configure 'awe enabled', 1

You can also change your max server memory too.
quote:
I have ran the queries two or three times in a row on both servers to get the times that I posted earlier in the thread. That should eliminate any cached queries.
If anything, that will guarantee that the data caches are full. If that occurs the buffer manager may or may not try to flush pages for any new queries; it's certainly a variable that will affect a comparison of the two servers. It will also guarantee that the plan is cached too. The only way to be sure that each server is starting clean is to empty the data and proc caches with DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before (and every time) you run a query.
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2005-08-29 : 20:40:35
a lot has been posted in this thread, and it is all good information. However, I would first make sure that your hardware is not an issue. I would check the following:

1. Make sure that your NICs are running 100 mbit FULL duplex (if you are connected to a 100MBit switch). Do not leave it set to autonegotiate. Also, make sure you are running current NIC drivers.

2. Check your RAID controller making sure that you are not running degraded on your RAID. btw, are the two systems using the same RAID levels or are the disk subsystems substantially different?

3. Are these CPUs hyperthread capable? Is hyperthreading turned on? Are you running a current BIOS revision? If HT is enabled, try disabling it to see if performance is increased.



-ec
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-08-30 : 02:50:30
Might physical fragmentation of the MDF/LDF files have anything to do with it? (Naive quesiton, but I'd be interested to know what folk think - we've been taking to doing occassional single file de-fragementation of databases during maintenance windows).

Kristen
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2005-08-30 : 07:11:30
That's a possibility too, it's certainly another variable in the process.
Go to Top of Page

ws5926
Yak Posting Veteran

88 Posts
Posted - 2005-08-30 : 09:07:23
How do I see how much memory SQL server is using right now? In task manager is shows that sqlservr.exe is using 1,737,124k. Right now activity is a bunch of short bursts. The hyperthreading of the new server shows 8 processors and my server is sitting at about 5% average CPU usage.

When I run my query the CPU usage hangs around 40% until it starts to return rows, then it drops back to 5%. The memory usage of sqlservr.exe in the task manager didn't change.

Is there a way to check through TSQL or Enterprise Manger to see how much memory is allocated to SQL server, or does Task manager show the correct amount?

The old machine has 1,699,736k of memory allocated to sqlservr.exe in task manager.

Am I to assume that my new machine is not using the extra memory available to the machine? Why would the memory tab of the properties dialog box show that 5375mb was available for SQL server?

Live to Throw
Throw to Live
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-08-30 : 09:28:55
Doesn't really answer your question, but the following may help you track the amount SQL Server wants, and how much is available


SELECT	cntr_value, counter_name
FROM	master..sysperfinfo PageSplitsSec (nolock)
WHERE	   (object_name = 'SQLServer:Memory Manager' 
		AND counter_name = 'Total Server Memory (KB)' 
		AND instance_name = '')
	OR (object_name = 'SQLServer:Memory Manager' 
		AND counter_name = 'Target Server Memory(KB)' 
		AND instance_name = '')

Kristen
Go to Top of Page

ws5926
Yak Posting Veteran

88 Posts
Posted - 2005-08-30 : 10:12:59
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
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-08-30 : 10:21:01
Thats a kind of "not necessarily" because I believe it doesn't acquire memory until it needs it - so maybe it hasn't needed it yet.

But if the server has been up and running for a while, and working hard <g> then I reckon you're right.

Kisten
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2005-08-30 : 10:33:10
quote:
Originally posted by ws5926

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


Your target memory is not any higher than your total server memory. This tells me that your server has enough memory, it is not wanting to allocate any more. If the target value was higher than total, then you don't have enough RAM.



-ec
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-08-30 : 10:38:50
Does the "target" move around? I mean: right now its happy, but it might not be in 10 minutes time?

You could capture the values using a scheduled task - every minute or so - or run PerfMon to record them over a period of time

Kristen
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2005-08-30 : 10:40:10
quote:
Originally posted by ws5926
Am I to assume that my new machine is not using the extra memory available to the machine? Why would the memory tab of the properties dialog box show that 5375mb was available for SQL server?


SQL server normally will only address 2GB of RAM. If you are running the advanced server edition of windows and the enterprise edition of sql server (looks like you are) you can use the /3GB or /PAE switches to increase the amount of memory SQL Server can address.

For a 4GB system the /3GB switch is recommended. This is simple to implement, just add the /3GB switch to the boot.ini. There is information on how to use this switch on microsoft's site also. THis change will allow SQL Server to allocate up to 3GB RAM, so your buffer cache can potentially get about 1GB larger.

This probably won't fix the issue you posted about, but you should use this switch anyway so you can actually use the RAM in your system.



-ec
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site