SQL Server Performance

Help with Storage on Single User SQL Server

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by SmallBiz462, Jun 3, 2007.

  1. SmallBiz462 New Member

    I am seeking some help in reconfiguring a SQL Server. Currently, the server is used by one person for analyzing data sets. The main database is about 15 GB including indexes. A typical T-SQL program will take about 30-60 minutes to run. The T-SQL program coding has been optimized and the indexes/stats have all been built using the recommendations from the Database Engine Tuning Advisor.

    Since this is a single-user system, I am not sure which recommendations would best apply. In addition, this computer (box) is simultaneously used for low-intensive VMWare and ‘Office#%92 applications.

    The current configuration is below:

    1) RAID 1 Array (2 WD Raptors): OS, Programs, & Page File
    2) RAID 1 Array (2 WD Raptors): SQL Data Files (incl tempdb) & Indexes
    3) RAID 1 Array (2 WD Raptors): SQL Log Files (incl tempdb) & VM Machines

    The computer has 4 cores, running Win XP Pro x64, has 4 GB RAM and all of the RAID 1 Arrays are on the PCIe bus.

    When a program is running, typically the 2nd Array is being heavily used, while the other arrays are quiet.

    I am considering the following options:

    1) Create a secondary filegroup for the indexes and place it on Array 3
    2) Add a 2nd data file for each Database and place it on Array 3
    3) Add two data files for each Database and place them on Array 1 and Array 3.

    Furthermore, I am only interested in reconfiguring this system if there will be a significance performance gain. (i.e. a 60 minute run will take less than 30 minutes) Otherwise, the additional maintenance may outweigh the performance gain.

    Finally, how exactly do I distribute the tables across multiple data files (Options 2 & 3). I figured that I should just copy the tables from the old database (with one data file) to the new database (with multiple data files).

    I hope that I have framed this question in a useful manner. Thanks in advance for any help.

    SB
  2. SmallBiz462 New Member

    Are partition functions/schemes one of the preferred ways to 'stripe' tables across physical disks?

    Are their other 'simple' ways to accomplish this?

    Thanks.

    SB
  3. bradmcgehee New Member

    Based on the information, it is hard to make a specific recommendation. If I were in your shoes, the first thing I would do is to dedicate this server only to SQL Server. SQL Server does not play well with other applications when it comes to performance. Second, ensure that your buffer hit cache ratio exceeds 99%. If not, you need to add RAM. Third, double check that your queries are fully optimized. Notice, I haven't answered your question about your I/O subsystem. This is because making any of the changes you have discussed won't gain you the performance you are wanting. It will take several steps to boost your performance, like the ones I have discussed.

    --------------------------------------------------------------
    Brad M. McGehee, SQL Server MVP
    Technical Editor/Moderator www.SQL-Server-Performance.Com
    Director of DBA Education for www.Red-Gate.Com
    www.sqlbrad.com
    www.sqlHawaii.com
  4. SmallBiz462 New Member

    quote:Originally posted by bradmcgehee

    Based on the information, it is hard to make a specific recommendation. If I were in your shoes, the first thing I would do is to dedicate this server only to SQL Server. SQL Server does not play well with other applications when it comes to performance. Second, ensure that your buffer hit cache ratio exceeds 99%. If not, you need to add RAM. Third, double check that your queries are fully optimized. Notice, I haven't answered your question about your I/O subsystem. This is because making any of the changes you have discussed won't gain you the performance you are wanting. It will take several steps to boost your performance, like the ones I have discussed.
    Hi Brad.

    I appreciate your helpful response.

    I understand that it would be helpful to dedicate the server for SQL. In that case the hardware would only be 30% utilized. However, if the workflow increases, we will definitely keep this in mind.

    Thanks for your tip on checking the buffer hit cache ratio. For most of the time, it is indeed exceeding 99%. This is probably due to the individual tables being fairly small and the use of many, many, many indexes/stats.

    I appreciate your candor about the difficulty in achieving a significant performance gain. I probably have been unfairly focused on the IO subsystem.

    In exploring these options, there may be an opportunity for a performance increase by vertically partitioning the big tables. Currently, we are mixing several large varchar fields that are barely being accessed with many tiny fields in the same data row. We should be able to reduce the total storage of main tables by over 50% if we move these large varchar fields off. However, since we are making major use of indexes/stats this may not result in any performance gain. Any thoughts?

    Thanks.

    SB

Share This Page