SQL Server Performance

tempdb in ram?

Discussion in 'Performance Tuning for Hardware Configurations' started by Slart, Dec 1, 2003.

  1. Slart New Member

    Hi,

    I have a server that makes very heavy use of the tempdb. In particular, according to fn_virtualfilestats it does a lot of writes to templog and tempdev. I have moved these two files to different separate disks and to the two least used disks on the system, but even so I wonder how much time it's taking to write to this disk.

    I have considered table variables but they are not an option because the tempdb tables are created as a result of select..into statements.

    I'm wondering if making a ram disk would be a viable option? This server has 8gb running w2k and the tempdb and templog are 100MB each.

    Thanks!

    Steve
  2. Luis Martin Moderator

    Before think in this option, I suggest to run Profiler, find Querys using #tables, copy a paste into SQL Analyzer and run index tunning to find optimization.

    Check:
    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1624

    for more details.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. joechang New Member

    unless your disk drives are completely overloaded, the ram drive won't do much.
    there is a moderately highly cpu load in writing to disks.
    this does not go away, it might even increase with ram disks.
    hence you have not done anything to reduce the cpu load in write operations.
    i would first try to tune the query as much as possible, minimize the number of rows inserted, or later modified. if you have a loop inserting single rows, enclose the loop with a BEGIN/COMMIT TRAN

Share This Page