SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Series of basic index and performance questions


Series of basic index and performance questions

Author
Message
xgcmcbain
xgcmcbain
SSC-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 86
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)

Group: General Forum Members
Points: 365430 Visits: 46932
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


xgcmcbain
xgcmcbain
SSC-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 86
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)

Group: General Forum Members
Points: 365430 Visits: 46932
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


xgcmcbain
xgcmcbain
SSC-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 86
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)

Group: General Forum Members
Points: 365430 Visits: 46932
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


MANU-J.
MANU-J.
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11796 Visits: 8766
Gila,

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

TIA,
MJ
maechismo_8514
maechismo_8514
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6612 Visits: 2228
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
maechismo_8514
maechismo_8514
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6612 Visits: 2228
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)

Group: General Forum Members
Points: 365430 Visits: 46932
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search