SQL Server Performance

How to Know If TEMPDB is Cause Of Slow Performance

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by jbates99, Sep 13, 2008.

  1. jbates99 Member

    hi experts,
    2005.

    This 2005 db server has 6 user dbs. User dbs and tempdb are on same raid array made up of 5 physical disks. TEMPDB has 1 data file.

    1. Is there a way to determine whether the tempdb database is causing slow performance or having contention?

    2. Would creating multiple data files for tempdb, say 2 or 4 or 8, but keeping them on the same above array - provide any benefit? (server has 2 dual-core processors)

    Thanks much. John

  2. Luis Martin Moderator

    Welcome to the forum!1) You can create a big tempdb said, 2GB to ovoid resize all the time.2) Definitely no.Now, you have to run Profiler to analyze the application. I think the performance problem is there (bad code, indexes, etc.).
  3. jbates99 Member

    Thank you, Louis. Just a quick follow-up question please.
    I have a Database Maintenance Plan that runs every Sunday.
    First, it performs reindexing on all tables.
    The next task reorganizes each table, with a 90 % fill-factor.
    Am I doing these steps in the correct order, or should the reindexing come AFTER the reorganize?

    Thanks, John
  4. Luis Martin Moderator

    If I understand, you are doing the same twice.When you reindex tables (indexes, if fact), the function keep the same original fill factor. Except you change that.So, if any index was created with fill-factor 90, then when you run DBCC REINDEX, 90 is default.My suggestion is: find in this forum some script to run REINDEX only when is necessary according fragmentation.With this script you can run it not only on Sunday, also 2 working days in windows time, to keep performance.
  5. gurucb New Member

  6. Mahmoud_H New Member

    for heavily insert tables , let fill factor <90 , say 60-70 to avoide defragementation.
  7. Saurabh Srivastava New Member

    Capture performance monitor counters for TEMPDB and post it. Because u r using 5 spindles it is important to know if you have loacted tempdb files on seperately or shared with user databses.
  8. Saurabh Srivastava New Member

    Also create as many tempdb file as cpu cores, make sure all of them are equal in size.
  9. jbates99 Member

    Thanks everyone.

    The TEMPDB is on the same logical drvive (5 physical disks) as the user databases.

    2 dual-core processors. So is that 4 processors, meaning I need 4 tempdb files all of same size?

    Thanks John
  10. Saurabh Srivastava New Member

    1) If you have determined that tempdb is suffering from performance. I would recommend placeing tempdb on seperate set of drives so it won't affect your user database performance. This whitepaper http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx explains Tempdb improvements in SQL Server 2005,best practices and monitoring space requirement. I hope this will help you in troubleshooting
    2) Yes. You are correct. 2 Dual cores means 4 logical processors for Windows. FYI- Windows doesn't know about physical CPU sockets so even if you have 2 processors HYPERTHREADED it shows 4 procs in task manager. Dual cores are better performer than Hyperthreaded procs.
    3) Yes, 4 tempdb, all equal sizes (to make them follow proportional fill algorithm in SQL Server, otherwise largest file will be used most).
    Thanks
    Saurabh
  11. madhuottapalam New Member

Share This Page