Series of basic index and performance questions

  • I know alot of these questions really depend on the schema, how the database is queried from end users and such so the answers might be different, or more the one answer to the question, but please try to answer if possible.

    1) When creating a primary key on a column, is it still necessary to create an index on that column? or would that index serve good another purpose?

    2) if you create a primary key on two fields combined, is it still necessary to create index's on them individually? or would that index serve good for another purpose?

    3) does creating foreign keys (relationships) have any cons? how much do they increase performance?

    4) at what point do u have to rebuild indexes?

    5) are there any advantages or disadvantages to creating indexes as constraints instead make a difference or have pros and cons?

  • xgcmcbain (2/19/2009)


    1) When creating a primary key on a column, is it still necessary to create an index on that column? or would that index serve good another purpose?

    No. Primary keys are enforced by an index

    2) if you create a primary key on two fields combined, is it still necessary to create index's on them individually? or would that index serve good for another purpose?

    Not usually. Certainly not on the leading column of the primary key. Maybe on the second, but it depends on queries

    3) does creating foreign keys (relationships) have any cons? how much do they increase performance?

    Foreign keys are for data integrity and usually don't have an affect on performance

    4) at what point do u have to rebuild indexes?

    Rule of thumb is 30% fragmentation. In some cases that may be high, in some cases low.

    5) are there any advantages or disadvantages to creating indexes as constraints instead make a difference or have pros and cons?

    If your index is not unique, it can't be a constraint. Constraints can't have include columns, indexes can.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (2/20/2009)


    xgcmcbain (2/19/2009)


    1) When creating a primary key on a column, is it still necessary to create an index on that column? or would that index serve good another purpose?

    No. Primary keys are enforced by an index

    Thats exactly why i asked this question, i always assumed it had to be doing such. But its order (DESC, ASC) doesnt seem to be an option though?

    4) at what point do u have to rebuild indexes?

    Rule of thumb is 30% fragmentation. In some cases that may be high, in some cases low.

    does sql server do this manually? how do u check this?

    5) are there any advantages or disadvantages to creating indexes as constraints instead make a difference or have pros and cons?

    If your index is not unique, it can't be a constraint. Constraints can't have include columns, indexes can.

    That really that wasnt my question but i would have assumed so. But did not know you can include columns was more concerted of pros and cons of each in proformance

  • xgcmcbain (2/20/2009)


    does sql server do this manually? how do u check this?

    sys.dm_db_index_physical_stats

    But did not know you can include columns was more concerted of pros and cons of each in proformance

    Unique index and unique constraint should be the same.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • as for reubilding indexes, sql server does not do this automatically at some point?

    What are the reasons for having to rebuilding indexes? inserts, deletes?

  • xgcmcbain (2/20/2009)


    as for reubilding indexes, sql server does not do this automatically at some point?

    No

    What are the reasons for having to rebuilding indexes? inserts, deletes?

    Inserts mostly, updates a bit, deletes less.

    Google for Fragmentation (or look up the relevent sections on Books Online)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gila,

    Why you said that "no index on leading field" for a primary key having two columns?

    TIA,

    MJ

  • MANU (2/20/2009)


    Gila,

    Why you said that "no index on leading field" for a primary key having two columns?

    TIA,

    MJ

    Because in SQL server, the first column is enforced by an index

  • xgcmcbain (2/20/2009)


    as for reubilding indexes, sql server does not do this automatically at some point?

    Nope it does not you have to manually do it or create a job using ALTER statement.

    What are the reasons for having to rebuilding indexes? inserts, deletes?

    Mainly Inserts, Transactional processing- could be deletes as well. The data gets fragmented and its not in an organised format, so you would have to rebuild indexes. You can also defrag them but depends on how much they are fragmented by examining avg_fragmentation_in_percent in sys.dm_db_index_physical_stats DMV.

    As Gail mentions Ideally,

    <30% - Defrag indexes

    >30%- Rebuild indexes

    In some cases where fragmentation is greater than 80% it is better to drop and recreate them at off hours of business.

  • Krishna (2/20/2009)


    Because in SQL server, the first column is enforced by an index

    No, the entire primary key is enforced by an index, not just the first column.

    A second index on the 1st column would be redundant. An index on the second (third, forth, etc) column of a primary key may be useful, depending on the types of queries that run.

    I wrote a bit recently about index column order - http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

    Mainly Inserts, Transactional processing- could be deletes as well.

    Updates can cause fragmentation as well, if the size of the row increases and the page is full.

    A delete can't cause fragmentation, though it can leave pages partially empty.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply