SQL Server Performance

Large number of columns

Discussion in 'Performance Tuning for DBAs' started by seasider, Feb 26, 2003.

  1. seasider New Member

    I have been assigned the task of improving performance on one of our older transactional systems. One of the biggest failings of this system is the horizontal size of the tables. This is causing very poor read peformance.

    The five biggest tables in the database have 112,111,100,82 and 81 columns respectively. This is on tables that have 1 million plus rows. Then there are 100+ tables with 20+ columns.

    Does anyone have any tricks or solutions for dealing with tables of this size without going for a full re-design? The system is so functionaly overloaded a re-design would be an horrendous job.

    One of my first recommendations is to opimise the column data-types to reduce the width, e.g. at the moment a lot of them are NUMBER when INT would do. Then I am going to try and get data onto a RAID 5 array instead of the present RAID 1, this should help a little also.

    Any ideas would be appreciated.
  2. sqljunkie New Member

    With tables that wide it sounds like somebody went through and denormalized the table layout...
    I think your first recommendation to reduce the column width is a good one. I've ran across databases before with char(500) columns when there was not a single record longer than 50 characters.
    The only other suggestion I have would be to look at how the queries are accessing the data with Profiler. Perhaps you'll get some ideads how to increase the performance if you know how the users are accessing the data. Plus you'll probably see better results if you focus your efforts on the tables that are getting used the most...
  3. Chappy New Member

    I certainly dont envy your task!

    Whilst splitting the tables out into a more manageable design, dont forget that views are a very powerful tool you could use to minimise the impact of the changes on the software, in the interim. This way you can improve behind the scenes manageability, and then perform incremental improvements in the queries, before removing the views where possible. I like this method because It is far preferable to focus on small bits of redesign at a time, rather than faced with a massive rewrite, potentially breaking all sorts of software components in the process.

    I worked on a very poor database recently, where many text fields were nvarchar when no unicode strings were even stored. In addition, like rortloff, the design had suffered from overestimation (ie, some 'char' fields only needed 20 chars, but the designer had made it 60 just to be 'safe'). Purely by using more sensible field types I reduced the DB from 4G to 170MB, and as youd imagine performance improved massively.

    Finally, when studying the usage of these tables, its very important to check queries do not just SELECT * for the sake of it. Read performance can often be improved by using an explicit field list.
  4. bradmcgehee New Member

    In regards to your question about RAID. If your database is mostly read rather than writes, then moving to a RAID 5 will help, RAID 10 would help even more.

    If you haven't done so already, hopefully you are rebuilding the indexes often and using a relatively high fillfactor in order to reduce the amount of space on each data page. This is normally the opposite advice I would give, but because of your huge table sizes, you want the least amount of wasted space in your indexes.

    I think you are on the correct track, but you do have a large job ahead of yourself.

    Brad M. McGehee
  5. seasider New Member

    Yep it looks a massive task <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />Just got the findings back from a trial run of changing the data-types on the database (NUMBER -&gt; INT, shortening chars, DATETIME -&gt; SMALLDATETIME where possible). We have reduced the database size from 80GB to 50GB! Good start, now all we have to do is find a day or two to take a production box down to implement the change - not so easy!<br /><br />I have ensured that Reindex jobs are being run regular to keep the tables as compact as possible. Only thing is with columns as wide as these they don't fit too easily onto a page so fragementation occurs at a massive rate. De-fragging an 8GB table on SQL 7 isn't too easy in a 24/7 production environment as well.<br /><br />I like the View idea. Going to have to get my hands dirty with the queries and see what I can do.<br /><br />Wish me luck...

Share This Page