SQL Server Performance

New SQL Server Hardware

Discussion in 'SQL Server 2005 General DBA Questions' started by koenwuyts, Jul 8, 2008.

  1. koenwuyts New Member

    Hi All,
    You probably see this question alot, but I need some advise about new hardware for our SQL Server which is getting slower and slower.
    Our Current configuration:
    • DELL 2950 with VMWare Infrastructure 3
    • 4 core's (1 CPU)
    • 3,6 GB RAM (our current VMware limitation)
    • SQL Server 2000 std (so only 2GB in use)
    • system drive RAID 1 - 2xHDD
    • data drive RAID 5 - 7xHDD
    • log drive and some indexes RAID 1 - 2xHDD
    • FB-HDD are in a EMC SAN setup.
    Current PerfMon logs (average between 10am and 8pm):
    • CPU: 25% usage
    • RAM pages per sec < 20
    • Physical Disk usage
    • system drive 1%
    • data drive 50%
    • log/index drive 10%
    • Buffer Cache Hit 99%
    • 30 Transaction/sec
    There are alot of big queries running on this machine, and the main goal is to speed them up alot. And in general improve system performance. What will give me the most benifit? RAM, HDD?
    So I was thinking 2 new dedicated server (failover clustering) for SQL Server which would contain this:
    • 2xQuad Core Xeon
    • Windows Server 2003 Enterprise x64
    • SQL Server 2005 x64
    • 32GB Ram
    • 2xSAS HDD Raid 1 (system drive)
    • 9xSAS HDD Raid 5 (data drive)
    • 4xSAS HDD Raid 10 (log/index drive)
    • ...
    Any other good idea's? More drive array's? Different config?
  2. satya Moderator

    On what areas the current SQL Server performance is hampered?
    Is it compulsory for you to replace the current hardware and SQL Serve?
  3. koenwuyts New Member

    Current performance issues is the increased time it takes to run queries.
    The problem is that it is a mixed production system with lots of user databases, but there are also alot of big reports which are run frequently over the tables. An OLAP server could take care of some of the reports but not all.
    Mainly 2 table on the entire SQL server cause the biggest problem, 1 table is 40million records = 36GB, and the other 3,5million records 7,5GB. These are tables on which small SPs run, but also big reports, I've squized out as much performance I can out of these tables using index's.
    Second problem is that I'm out of space (which can be fixed with some additional drives)
    The current setup doesn't need to be replaced, if anyone has some good performance tips. I've exausted all of my idea's.
    But a fact which I need to take into account is that within 6 months the amount of data on the entire system will increase by 40%, which is alot, this means a 60GB table and a 10GB table. And I'm afraid that our current problems will only get wors.
  4. satya Moderator

    Ok what is the schedule of optimization jobs on the tables that will have frequent inserts & updates. Also you need to check on the index strategy in finding out about new indexes based on the data you have selected.
    There are many areas of performance tuning in SQL Server: database design, application design, hardware/software configuration, and many more. But none are as important as indexing, becuase they are used when the data is read or accessing, so having a base strategy of the "right indexes" is the most important thing you can do for performance and scalability.
    Having an index is not sufficient when you are not reindexing or optimizing to address the fragmentaiton, so http://sqlblogcasts.com/blogs/ssqan...r-2005-index-optimization-best-practices.aspx is the best one to adopt.
  5. koenwuyts New Member

    Optimization jobs run every sunday, but do they rebuild indexes? (standard SQL server maintenance jobs, which fail from time to time due to lack of space)
    One test I ran:
    DBCC SHOWCONTIG scanning 'WOPOH1P' table...
    Table: 'WOPOH1P' (165575628); index ID: 1, database ID: 25
    TABLE level scan performed.
    - Pages Scanned................................: 9076
    - Extents Scanned..............................: 1147
    - Extent Switches..............................: 2411
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 47.06% [1135:2412]
    - Logical Scan Fragmentation ..................: 4.24%
    - Extent Scan Fragmentation ...................: 69.75%
    - Avg. Bytes Free per Page.....................: 1054.7
    - Avg. Page Density (full).....................: 86.97%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC SHOWCONTIG scanning 'WOPOH1P' table...
    Table: 'WOPOH1P' (165575628); index ID: 1, database ID: 25
    TABLE level scan performed.
    - Pages Scanned................................: 8789
    - Extents Scanned..............................: 1105
    - Extent Switches..............................: 1104
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 99.46% [1099:1105]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 0.27%
    - Avg. Bytes Free per Page.....................: 824.7
    - Avg. Page Density (full).....................: 89.81%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    I better rebuild all my indexes and see what performance improvements I get.
    But RAM will always be a big limitation I think, currently I only have 2GB of usable RAM and I have +150GB of data. that can't be good for performance.
  6. satya Moderator

    What is the edition of SQL Server used here and its service pack level/
  7. moh_hassan20 New Member

    The counter performance parameter are perfect , and no bottleneck except adding extra storage as needed.

    As you use VMWare Infrastructure 3 , you can follow other upgrade path :
    - add one extra processor to the current hardware.
    - add extra storage as needed.
    - upgrade to Infrastructure 3.5 (Overcoming Memory Limitation)
    - install Windows Server 2003 x64 , sql server 2005 x64

  8. koenwuyts New Member

    We are running SQL Server 2000 Standard SP4 (8.00.2039)
    VMWare Infrastructure 3 has some limitation, I can only add 4 cpu's. Since 2xquad cores are viewed as 8 CPU's, not 2 I'm limited to 1 CPU.
    A new version of VM3 support up to 32GB or 64GB of RAM, but since I'm currently running SQL2000 std I can only use 2GB.
  9. moh_hassan20 New Member

    .. so upgrade sql 2000 standard to next sql 2005 ENTERPRISE , is the first priority,
    especially to benefit the performance enhancement.

  10. koenwuyts New Member

    SQL Enterprise is a bit to expensive. For that price I can buy a new server and SQL server 2005 standard.
  11. satya Moderator

    Don't waste money on SQL 2000 anymore as it is out of mainstream support, you are right to go with SQL 2005 standard in this case that will have more improvement in terms of scalability on the hardware aspects you have defined.
  12. moh_hassan20 New Member

    review the Comparison Between SQL Server 2005 Standard and Enterprise Editions , to be sure that no feature loss is needed.

    AS feature needed is included in standard - go ahead.

  13. Balasundaram New Member

    I believe you are using SQL 2000 and 32bit server. There is option called AWE which allows you to use more RAM if you have. Please check out enabling AWE option in 32bit server.
  14. satya Moderator

    No use if you have SQL 2000 Standard edition as it is limited to 2GB only.
  15. koenwuyts New Member

    I've created a maintenance jobs that manually runs DBREINDEX against all the tables. This has helped a bit.
    To further speed up the process I broke the query down into some sub queries which dump their data in a table. The always seems to go alot faster then building one big query.
  16. melvinlusk Member

    I have to ask, why do you need VMWare? Is there something else running on this box besides the SQL Instance? You're bound to get better performance by dumping VMWare and going with a native Windows install. Also, by dumping the VMWare license you could take the $$$ you save and devote it towards a SQLL 2005 upgrade.
  17. satya Moderator

    Consider Windows Virtual Server (latest version) if the database is not that transactional and better for READONLY basis of apps.
  18. satya Moderator

    [;)] but still you need to ensure the database optimization practices are followed.
  19. koenwuyts New Member

    I'm the DBA but I'm not responsible for the server hardware/software setup at our company, and it was decided (against my advice) to use VMWare since we use it for all our server.
    Now the good thing about this is that I can blame someone else for the slow of the system.[:p]

Share This Page