Sql Server Performanc Dual P4 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Sql Server Performanc Dual P4

Hello, We have just upgraded to a new machine and are having strange performance issues. We are getting worse performance on the new machine than the old one. 1 example is a small T-SQL function that builds a table of 60507 records. On the old server it runs in 5 seconds, but on the new one it takes 50 seconds. When we do this we get about 2-3% CPU usage. Also within DTS, the Data Driven Query Task runs slow. It is only slightly faster than the old server. Here we get 10% CPU usage. Any ideas? Below are the specs for the 2 machines: Old Server
Dual PIII 1.3 GHz
Windows 2000 SP3
SQL Server 2000 Standard Ed SP4
5 instances of SQL server currently running New Server
Dual Xeon P4 3.4 GHz w/ hyperthread enabled
Windows Server 2003 Standard Ed SP1
SQL Server 2000 Standard Ed SP4
1 instanc of SQL server is running, 3 are installed. I have played around with boosting SQL Server priority within windows and disabling all non-essential windows services only to get a very small performance gain.
what are teh two servers disk configuration? What have you looked at in performance monitor? Bert
You need to look at performance monitor and profiler both to solve this quickly and efficiently. What is running slowly in profiler? Are you seeing a lot of recompiles, deadlocks, heavy reads or writes? Since you switched servers, have you reindexed and updated statistics? If not, you need to do so immediately. You might also want to recompile all your stored procs just for fun. In performance monitor, what do your physical read and write queue lengths look like? What about your processor %, SQL Server buffer cache hit ratio, SQL Server cache hit ratio, page life expectancy, and memory stats (wait times, etc)? What settings do you have in the boot.ini file if any? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
I have reindexed and updated statistics already. Currently I am the only one using the server (it’s not in production yet) so there are no deadlocks or heavy read/writes. As far as the disk configuration goes we are using 4 72GB 15K RPM U320 SCSI drives in a RAID 3 configuration, which is doing striping over 3 of the disks and using the 4th for parity. I can get a sustained throughput rate of 75MB/Sec, so I don’t think this is where the bottle neck is. Just for fun I’m putting the code of the function below so you can see it: create function fn_build_date_range()
returns @tbl TABLE (sqldate datetime)
declare @start datetime
declare @end datetime
select @start= min(stvterm_start_date) from stvterm where stvterm_code!=’000000′–’01-01-1941′
select @end=max(stvterm_start_date) from stvterm where stvterm_code!=’999999′–’06-02-1941′ –Set the start-date
if @start > convert(datetime,’01-01-1850′)
select @start = ’01-01-1850′ while @start<[email protected]
insert into @tbl values(@start)
select @start=dateadd(day,1,@start)
–insert into @tbl values(‘1900-01-01’)
–Insert the End-of-Time record
insert into @tbl values(‘4712-12-11’)
The table stvterm that it uses (we are a Banner school) has a whopping 223 rows in it with the earliest record being 9/01/1941.
This is disk configuration on your new server I guess. What is configuration on old one? What are tempDB settings on both servers?
The previously stated disk configuration is for the new server, and I left out about the disk configuration on both machines. Sorry for not being more clear. Below is the complete disk configuration for both servers:<br /><br /><u>Old Server</u><br />OS drive (C<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />: 2 18GB 10K RPM U-320 SCSI RAID mirroring (forget which level number it is)<br />Data Drive (E<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />: 4 36GB 10K RPM U-320 SCSI RAID 5<br />SQL Server is installed on C, with the databases on E<br /><br /><u>New Server</u><br />OS drive (C<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />: 2 18GB 15K RPM U-320 SCSI RAID mirroring<br />Data Drive (E<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />: 4 36GB 15K RPM U-320 SCSI RAID 3 as stated earlier<br />SQL Server is installed on C, with the databases on E<br /><br /><br />The tempDb settings are the same for both machines, and is residing on E-drive. Using all default options which is:<br /><br />8MB initial size<br />auto grow by 10%<br />simple recovery model<br />auto update statistics<br />auto create statistics<br />compatibility level 80<br /><br />We have a scheduled job that runs to shrink the tempDb on a regular basis.
While I still don’t know why is new server slower then old one, I have two suggestions:
1. Don’t shrink tempDB
2. Set initial size of tempDB to be equal to max size it ever reached. Btw, your disk configurations look the same. * Maybe you measured query performance during or after tempdb was shrinked on new server.
Thanks for the suggestion about tempDb. When I was doing my testing tempDb was not being shrunk or anything. I checked that already. One thing that may interest you is that a friend sent me a T-SQL statement to run. It runs for 1 minute and counts (starting from 1) to see how high it can get in that minute. The old server counts to 6,845,586 while the new server gets to 6,554,227. I’m not really sure what this means if anything. SQL: DECLARE @endtime datetime
DECLARE @iLoop numeric(8,0) SELECT @endtime = dateadd("n",1,getdate()),
@iLoop = 0
WHILE getdate() < @endtime
SELECT @iLoop = @iLoop + 1
SELECT @iloop
It means you have something seriously wrong with this server. Have you opened up tickets with MS or the hardware vendor? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Also, while testing make sure there is no other process using significant % of cpu.
We have opened a contact with HP about this. And no, there is not any other process eating CPU on the machine. We are going to be doing some mo-board testing today, so we’ll see what results that gives.
Just curious why you are using RAID 3 on your E drive? I have seen RAID 3 or is it 4 recently only because that is the only supported RAID type for a NepApp SAN/NAS solution. Is this supported by your RAID controller or is this a software RAID? RAID 5 though is a much better option for a lot of reasons in my opinion. Would you mind also giving the sp_configure on both servers? Zach
As Derrick and Bert noted, you should definitely try to see where your bottleneck is using performance counters. Seehttp://www.sql-server-performance.com/sg_sql_server_performance_article.asp for additional details. Some other things to try
1 – Disable Hyperthreading and see if performance improves. SQL could be queuing requests on the virtual CPUs and just complicating the issue. You would likely see CPU bottlenecks, so I would always recommend checking perf. counters before making changes.
2 – Ensure SQL is set to use adequate amounts of memory. I would set it to use a maximum of 1G of memory and see if your testing results change. With a Standard 2003 OS (assuming no boot.ini changes) you have 2G available for all apps. If SQL and other apps (including system backups, monitoring, etc) are fighting over resources, your performance could suffer. Again, this would be something you could see in your performance log, so gather statistics first. By the way, I would never recommend using Priority Boost. I am guessing that you do not have other high priority apps stealing SQL resources, so let the OS manage the app space and don’t introduce potential boost setting problems. Matt