SQL Server Performance

Sql Server Performanc Dual P4

Discussion in 'Performance Tuning for Hardware Configurations' started by dnrickner, Sep 14, 2005.

  1. dnrickner New Member

    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
    1GB RAM
    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
    4GB RAM
    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.
  2. bertcord New Member

    what are teh two servers disk configuration?

    What have you looked at in performance monitor?

    Bert
  3. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  4. dnrickner New Member

    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)
    as
    begin
    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<=@end
    begin
    insert into @tbl values(@start)
    select @start=dateadd(day,1,@start)
    end
    --insert into @tbl values('1900-01-01')
    --Insert the End-of-Time record
    insert into @tbl values('4712-12-11')
    RETURN
    end


    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.
  5. mmarovic Active Member

    This is disk configuration on your new server I guess. What is configuration on old one? What are tempDB settings on both servers?
  6. dnrickner New Member

    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.
  7. mmarovic Active Member

    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.
  8. dnrickner New Member

    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
    BEGIN
    SELECT @iLoop = @iLoop + 1
    END
    SELECT @iloop
  9. derrickleggett New Member

    It means you have something seriously wrong with this server. Have you opened up tickets with MS or the hardware vendor?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  10. mmarovic Active Member

    Also, while testing make sure there is no other process using significant % of cpu.
  11. dnrickner New Member

    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.
  12. znichter New Member

    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
    http://www.sqljunkies.com/WebLog/odds_and_ends/
  13. biged123456 New Member

    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

Share This Page