SQL Server Performance

How to Improve this Server

Discussion in 'Performance Tuning for Hardware Configurations' started by mcosy, Jul 9, 2007.

  1. mcosy New Member

    Hi All,

    I'm a sysadmin and one Of the ERP System guys set this SQL 2000 Std Server in following ways and we having performace issues. With their reviews, they found that the Intergration (eFrontOffice to eBackOffice locking the users and take 10-15 mints locks.)
    cause the issues.


    Now we want to seperate this eFrontoffice and eBackoffice on to 2 Servers.

    I got the recomendation from JO for this New server. But is that possible to improve this Server to make this System better befor we spend $$$ for new Server?

    I got ML570 G2 Quad Processor X 2.Ghz Server with following
    1. 4GB RAM
    2. 1 x Smart Array 6400 Contoller
    3. 3 x RAID 5 Arrays
    4. Array Config as follows

    Array A: Logical drive1 68GB ( c: OS & d: page File)
    Array B: Logical drive2 135GB ( E: SQL Data & Tempdb )
    Array C: Logical drive3 280GB ( G: Logs & Backup)

    5. DB Sizes

    DB1: 45GB
    DB2: 35GB
    TempDB: 13GB
    DB4-DB10 : between 1MB- 10GB

    6. And also Noticed tempdb Data and Logs are same name like this

    E:MSQLDATA empdb empdb.mdf
    G:MSQLLOGS empdb.mdf
    check the DB properties too??? is this work like abobe? lol

    7. We doing snapshot data replication to our web server and repldata in C: Drive too
  2. satya Moderator

  3. mcosy New Member

    HI,

    Thanks, Can someone give me your thoughts about Q 6?

    6. And also Noticed tempdb Data and Logs are same name like this

    E:MSQLDATA empdb empdb.mdf
    G:MSQLLOGS empdb.mdf
    check the DB properties too??? is this work like above?

    I got the answer from the developer like this for the 13 GB Size of this tempdb.

    "The database would be set to dynamically GROW – however, it will not automatically SHRINK (Microsoft advises against automatic shrinking for online databases).

    So, theoretically, the tempdb will always be as big as it has needed to grow at any given point in time. It would be possible to manually shrink ‘tempdb#%92 to reduce the size"


  4. satya Moderator

    Even though the naming convention is suggested to have .MDF for data and .LDF for log, while managing you could rename the .LDF file as .MDF too.

    TEMPDB will not shrink which is a default behaviour, also when you attempt to restart the SQL services then it will come back to default size. Look at these link to take care in this case@:
    http://www.sqlteam.com/article/what-to-do-when-tempdb-is-full
    http://sqlserver2000.databases.aspf...nd-how-can-i-prevent-this-from-happening.html

    http://www.sql-server-performance.com/tempdb.asp - to fine tune tempdb.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. mcosy New Member

    HI SKJ,

    Thanks for your help.I had a look on all the links. but still got few questions so please tell me

    1. When i check the DB size, this tell me. do we need to restart this server ?


    database_name database_size unallocated space
    ----------------------------------------------------------------------------------------
    tempdb 13987.31 MB 13440.35 MB


    reserved data index_size unused
    ------------------ ------------------ ------------------ ------------------
    32728 KB 13552 KB 9816 KB 9360 KB

    2. Do i really need to change this log file name to ldf or is that easy to rename or safe?
  6. satya Moderator

    1. no need to restart the server unless you need to, you can run SP_SPACEUSED UPDATEUSAGE='TRUE' against TEMPDB to get optimum values. If you do not have any disk space issues then leave the settings as is.

    2. I suggest if you do not have any problem at the moment leave it as it as, as the files are notused with a hard-coded name any way.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. mcosy New Member

    HI,

    Sorry, may be i didn't ask you properley. We got few issues with our system DB locks and freezing etc..etc. I have noticed that most of the issues releate to Sales Orders and can see the tempdb using lots of these tabels.

    And also what i understand this tempdb is delete and re create new one each time the sql start?

    The Size of the DB is 13.4 GB but used space is 54MB got 13 GB free. And i got HDD space too

  8. mcosy New Member

    Hi,

    Just restart my server last night and found tempdb size is 40MB and this morning 429MB
    So do we need to restart this server weekly or is there any way to maintain this tempdb?
  9. satya Moderator

    You haven't looked at the links above andhttp://www.sql-server-performance.com/tempdb.asp talks about how to tune and keepup the tempdb size too, and that ASPFAQ article too in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page