Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Series of basic index and performance questions Expand / Collapse
Author
Message
Posted Thursday, February 19, 2009 10:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 24, 2013 1:20 PM
Points: 70, 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?

Post #661056
Posted Friday, February 20, 2009 2:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #661133
Posted Friday, February 20, 2009 1:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 24, 2013 1:20 PM
Points: 70, 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
Post #661697
Posted Friday, February 20, 2009 1:44 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #661729
Posted Friday, February 20, 2009 2:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 24, 2013 1:20 PM
Points: 70, 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?
Post #661782
Posted Friday, February 20, 2009 3:20 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #661838
Posted Friday, February 20, 2009 7:44 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
Gila,

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

TIA,
MJ
Post #661929
Posted Friday, February 20, 2009 8:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
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

Post #661937
Posted Friday, February 20, 2009 8:20 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
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.



Post #661938
Posted Saturday, February 21, 2009 2:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #661984
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse