SQL Server Performance

Disk array picking confusion for increasing sql server 2005 performance

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by bharatp80, Jun 25, 2008.

  1. bharatp80 New Member

    Hi,
    Currently we are running SQL server 2005 on DELL PE 2950 server with MD 1000 disk array(ALL 15k SAS drive).We configured RAID 10, RAID1 for tempdb, actual data and transaction log file. We have four different database each of 20GB size and recently we are facing problem of SQL timed out. so we have decided to upgrade hardware for this( We do not want to check and go by SQL query at present so please just consider only option for us is to hardware upgrade)
    I have checked with dell and found new PE2950 server with quad xeon 3.0 GB processor with 16GB of RAM and all drives in server are SAS 15K.also got quote on AX 4-5 4GB Fiber channel DELL Smart Array with 12 SAS 15K Drive 146GB.
    We also found MD 3000 and MD 1000 disk array with 15k SAS drive.
    We are confused to either go with one AX 4-5 Disk array (12 Drive)or go with MD 3000 and MD 1000 (30 drive).
    Which configuration can give us better performance?
    one with AX 4-5 fiber channel smart array with 2GB catch or one with more hard drive with (MD3000+MD 1000) and catch of 512MB.
    please suggest me your views on this.
    Thanks,
    Bharat
  2. moh_hassan20 New Member

    Befor starting upgrade , you must be sure where is the bottleneck of performance: CPU , Memory , Storage,network,....
    It is strange to give time out. Can we know:
    - the current configuration of the server , RAID array, network 100M or gigabit speed.
    - max concurrent users.
    - do you run SQL Standard edition or Enterprise . (I assume you are using windows 2003 , in 32 bit.)
    - review error log of sql server , event viewer to find something strange.
    - Is the server dedicated for sql server , or there are other application run in the box.
    - can you create performance counters on the server to get measure to identify the problem.
  3. bharatp80 New Member

    Hi Mohammad,
    Thanks a lot for your time and quicker reply.
    Here are the answers to your questions.
    - the current configuration of the server , RAID array, network 100M or gigabit speed.
    DELL PE 2950 server with Intel xeon 3.2 Ghz processor, 4GB RAM, 4 SAS 73GB hard drive 15k speed. one MD 1000 disk array running in split mode.channel 0( disk 0 to 6) channel 1 ( Disk 7 to 14)
    OS -RAID 1( 2 disk)
    D- Raid 0(1 disk) , E-Raid 0( 1 disk)
    MD1000( All database files are stored on MD 1000 disk array disk)
    Raid 1- 5( TempDb, transaction log and few smaller actual data files)
    Raid10--1(Actual Data for one database)
    Raid0-1(for Back up)
    - max concurrent users.
    250
    - do you run SQL Standard edition or Enterprise . (I assume you are using windows 2003 , in 32 bit.)
    standard edition for both
    - review error log of sql server , event viewer to find something strange.
    one thing i have always observed in event log about sa login failed message error.it occurs every 10 seconds or so.it looks like someone is trying to access our sql server and he fails to logon.how to remove it...
    - Is the server dedicated for sql server , or there are other application run in the box.
    yes it is dedicated server only.
    - can you create performance counters on the server to get measure to identify the problem.
    what counters you want me to add on.please let me know
  4. moh_hassan20 New Member

    i find the configuration of the server and RAID storage is good enough to run the load.
    So, we must try to discover the reasons that fire the timeout error:
    - Timeout due to db locks (more probably).
    - Login timeout expired , more info: http://msdn.microsoft.com/en-us/library/ms190181.aspx
    - how the application is connected to the server. it it is best to use one account for every user.
    - may be bottleneck on the network. check the traffic of the server using network management tool (available on windows 2003).

    Check locks using the valuable built-in reports that you can run from management studio.
    you can use the profiler tool that is shipped with sql server 2005 to discover these reasons. you will find many options that you can filter to concentrate on time out errors.
    Also
    You can reconfigure the timeout setting of the server as follows:
    Set the remote login timeout to more seconds (default 20 sec, exercise a reasonable value):
    sp_configure 'remote login timeout', 30
    go
    reconfigure with override
    go
    The more important counters you can watch are:
    • Network Interface Object: Bytes Total/Sec
    • SQL Server Locks Object: Average Wait Time
    • System Object: Processor Queue Length
    • SQL Server Access Methods Object: Full Scans/Sec
    • SQL Server General Statistics Object: User Connections
    • Process Object : % Processor Time
    • Memory Object: Pages/sec , Available MBytes
    • PhysicalDisk Object: Avg. Disk Queue Length, % Idle Time
    • SQL Server Buffer Manager Object: Buffer Cache Hit Ratio
    • SQL Server Database Object: Transactions/Sec
    I hope that post is helpful.
    Let me know your result investigation .


  5. bharatp80 New Member

    Hi mohammad,
    Thanks for valuable comments.I will check and will let you know the results of it.
    on friday i setup few counters on SQL server and i found some very stange results which are as below.can you conllude from the results where is the problem is....
    I have set up few basic counters on Dedicated SQL server 2005.
    Hardware...DELL PE 2950 Server with MD 1000 diak array.
    OS windows server 2003 standard edition.
    Ram : 4GM
    Processor : Intel xeon dual core 3.2 GHz(Two processor)
    MD 1000 disk array coonected in split mode.(Two seprate channel)
    channel 0- drive 0 to 6
    channel 1 drive 7 to 15
    I have configured 1 RAID10, and 1 RAID0 and 5 RAID 1 configuration for database,tempdb and transaction log file.
    We are experiencing SQL timed out error. so today i have configured following counters in performance log to see their values but i could not make any guess where exactly the problem is.
    Here are the counters and thier values.
    Pages/sec-----------58.992
    Avg Disk Length-----35.73(MAX 151.801) ( total 4(In server) + 15(MD 1000) = 19 drives in system)
    %Processor Time----33.509
    Available MByte----591
    %Disk Time--------517.648(Max-1384.247)
    -----------------------------------------------------------
    Besides above counter i just checked for one RAID1 array drive H and another RAID 1 Drive I counters ...
    Drive H(Data) Drive I ( Transaction log file)
    Disk Queue Length 15.58(54 MAX) 0(0.002 Max)
    Disk Read Q length 13.72(53) 0(0.002 Max)
    Disk write Q Length 0.003(0.048 Max) 0(0.002 Max)
    % Disk Time 1500 0.002
    I am confused where exactly problem is.
    Can you please make your suggestion to resolve this issue.
    Thanks,
    Bharat
  6. moh_hassan20 New Member

    Can you post the complete text of the error message.
    Can i know the result of investigation of my above post.
  7. satya Moderator

    We have deployed MD3000 without any issues, but it always depends on the database to database and how your application is optimized for performance. Also it is better to keep TEMPDB on RAID 10 as you may be aware SQL 2005 uses it a lot, as referred above giving further details will help to get you correct lead.
  8. moh_hassan20 New Member

    Other reasons that may be the reason of time out:
    check: PRB: A Timeout Occurs When a Database Is Automatically Expanding
    http://support.microsoft.com/kb/305635

    Other article: http://support.microsoft.com/kb/315512/EN-US/ :
    "If the growth increment islarge or there is some other factor that causes it to take a long time, thequery in which you open the transaction might fail because of a timeout error.The same sort of issue can result from an autogrow of the data portion of yourdatabase."
    please, can you review the sql server log event , you may find info for autogrow, and match that time against timeout occurs.

Share This Page