SQL Server Performance

using heaps

Discussion in 'Performance Tuning for DBAs' started by jeroen, Apr 2, 2006.

  1. jeroen New Member

    On this thread I read a discussion about heap tables:

    I have a table that has quite a few inserts and updates, but also quite a few reads. I'm using a guid for primary key. Should I make this a heap table in order to avoid page splits? If so, what penalty do I pay when doing selects?
    In general, if you use a guid for primary key, would you suggest to make the table a heap (unless you have a suitable candidate key)?

    Thanks for any help.
  2. FrankKalis Moderator

    If you're using a GUID for PRIMARY KEY, that column usually is a poor candidate for the clustered index (it might be a better one, if you use some sequential GUID generator). However, depending on the queries run against this table, you might find another set of column(s) to cluster on. But that's impossible to tell with the information given.

    Frank Kalis
    Microsoft SQL Server MVP
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  3. Luis Martin Moderator

    Can you post the table (columns)?

    Luis Martin

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte

    All postings are provided “AS IS” with no warranties for accuracy.

  4. jeroen New Member

    psv_id primary key, guid
    psv_prs_id guid, foreign key
    psv_var_id guid, foreign key
    psv_value nvarchar

    psv_prs_id,psv_var_id is a candidate key. If I make this the clustered index I probably get even more page splits. All reads use this index, which is now a non clustered unique index.
  5. joechang New Member

    guids can cause performance problems, but i would not be overly concerned about page splits unless you know beyond doubt that it is causing a problem

    consider an adequate disk system since you decided to use guids,

    also consider gert's sequential guid (sqldev.net)
  6. FrankKalis Moderator

    Here's what Joe and I had in mind when talking "sequential":<a target="_blank" href=http://www.sqldev.net/xp/xpguid.htm>http://www.sqldev.net/xp/xpguid.htm</a> <br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  7. jeroen New Member

    Thanks for your replies. I read about the sequential guids in the thread I posted above, so I'm thinking about using it.

    The reason I think I have quite a few page splits is the fact that inserting appears to go much slower than in other tables. Probably if there is a page split at the beginning of the table the whole table has to be moved on the disk, which is why it's so slow. I could set the fill factor to a lower value, but I don't know what value would be about right.
  8. satya Moderator

    How about the indx placement on the involved tables?
    How about the defragmentation practices of indexes too?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. jeroen New Member

    There's the primary key, one index on psv_prs_id,psv_var_id and one on psv_var_id.
    Indexes are rebuilt on a weekly basis. I could do it more often, but selects on the table are going ok, it's the inserts that are going slow.

Share This Page