SQL Server Performance

I/O Performance issue

Discussion in 'Performance Tuning for Hardware Configurations' started by marcouchi, May 29, 2007.

  1. marcouchi New Member

    Hello,

    Business is actually accessing a SQL Server (SQL Server 2000 SP4) but I encounter a performance issue.

    I found that the array where are stored the mdf file is overloaded. When I check the Avg. read disk queue length I have an average of 100 (we have 5 disks) so 100/5 = 20 whereas the maximum should be 2 !!!!!!!

    Tempdb and the production database are on this array and I would like to know if it should comes from that point (BO makes a lot of order by and group by) ?? How could I check which database is the most accessed ?

    I'm going to get more information about the array, so I will let you know.

    Regards,
    Marc
  2. satya Moderator

    Do you have tempdb and other user database data files on same disk?
    In SQL 2000 you have only PROFILER or server side trace to see which is most accessed database.

    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.
  3. marcouchi New Member

    yes I have database data files and tempdb on same disk...

    Do you think I should put tempdb (data file and log file) on a specific disk ?
  4. satya Moderator

    I believe you have to seperate the TEMPDB from the user databases data files as the TEMPDB is used extensively for all sorts of computations & processes from the user databases.

    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.

Share This Page