Indexing Question

I have a SQL 2005 database with 2 main tables both of which are very big and
a bunch of smaller tables.
These tables are getting 100s of records inserts a minute, they have a
forigen key relationship with one another and are also referenced with
forigen keys in other tables.
In order to do the inserts and in order to run reports I need to create
indexes on the tables.
Is it better to create a number of multi column indecis that have some
parallelism or I should just create a single colum index for every column
that I was considering to include in more than one multi colum index?
Thanks
SagiHi, Sagi,
I understand that you would like to create indexes on the tables which are
getting hundreds of records inserts per minute; but you would like to know
which way is better, creating a number of multi column indexes or creating
a single column index for every column.
If I have misunderstood, please let me know.
I do not recommend that you directly create indexes on your operating table
since it has a heavy inserts on it.
Once an index is created, the insert operation will spend more time to
finish. I recommend that you timely synchronize your database to another
SQL Server which is used for report. You can create indexes on the database
for report. For creating single column index or multi-column index, this
depends on your requirements. A multi-column index cannot be utilized if
you query your database on only one of the columns; but if your query is
based on the multiple columns which are in the multi-column index, you can
get better performance. Generally I recommend that you create single column
index for critical columns (such as datetime, name columns etc) and create
multiple column index for the non-critical columns.
Also, for getting better performance, you may also consider to create
partition views or partition tables for the database.
For more information about index in SQL Server, please refer to:
CREATE INDEX (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||This is a bad response which might mislead the person who posted the
question.
Multi-column indexes CAN be used by queries that only access a single
column, as long as that column is the left-most column in the index.
Creating many single column indexes can actually degrade query performance
by an order of magnitude as the query processor might need to seek between
multiple indexes to resolve some queries. It is usually far better to design
indexes that cover the requirements of performance sensitive queries by
including at least the columns that provide filtering (those used in the
WHERE + JOIN clauses) & possibly including columns only named in the SELECT
list if many rows are being returned.
Regards,
Greg Linwood
SQL Server MVP
http://www.SQLBenchmarkPro.com
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:ODFE3IVhHHA.5272@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Sagi,
> I understand that you would like to create indexes on the tables which are
> getting hundreds of records inserts per minute; but you would like to know
> which way is better, creating a number of multi column indexes or creating
> a single column index for every column.
> If I have misunderstood, please let me know.
> I do not recommend that you directly create indexes on your operating
> table
> since it has a heavy inserts on it.
> Once an index is created, the insert operation will spend more time to
> finish. I recommend that you timely synchronize your database to another
> SQL Server which is used for report. You can create indexes on the
> database
> for report. For creating single column index or multi-column index, this
> depends on your requirements. A multi-column index cannot be utilized if
> you query your database on only one of the columns; but if your query is
> based on the multiple columns which are in the multi-column index, you can
> get better performance. Generally I recommend that you create single
> column
> index for critical columns (such as datetime, name columns etc) and create
> multiple column index for the non-critical columns.
> Also, for getting better performance, you may also consider to create
> partition views or partition tables for the database.
> For more information about index in SQL Server, please refer to:
> CREATE INDEX (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188783.aspx
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Best regards,
> Charles Wang
> Microsoft Online Partner Support
> PLEASE NOTE: The partner managed newsgroups are provided
> to assist with break/fix issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader:
> microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:ODFE3IVhHHA.5272@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Sagi,
> I understand that you would like to create indexes on the tables which are
> getting hundreds of records inserts per minute; but you would like to know
> which way is better, creating a number of multi column indexes or creating
> a single column index for every column.
> If I have misunderstood, please let me know.
> I do not recommend that you directly create indexes on your operating
> table
> since it has a heavy inserts on it.
> Once an index is created, the insert operation will spend more time to
> finish. I recommend that you timely synchronize your database to another
> SQL Server which is used for report.
This is probably the best idea.
> You can create indexes on the database
> for report. For creating single column index or multi-column index, this
> depends on your requirements. A multi-column index cannot be utilized if
> you query your database on only one of the columns;
Umm, I have to disagree with this Charles. A multi-column index can
certainly be used if the first column is part of the query. And in certain
cases additional fields can be used if they appear in the query.
> but if your query is
> based on the multiple columns which are in the multi-column index, you can
> get better performance. Generally I recommend that you create single
> column
> index for critical columns (such as datetime, name columns etc) and create
> multiple column index for the non-critical columns.
I'm not entirely sure I'd agree with this. I think it really depends on
what the queries are.
I owuld agree however, that he probably wants to keep the total number of
indices to a bare minimum.
> Also, for getting better performance, you may also consider to create
> partition views or partition tables for the database.
Another option that I've used is to put the indexes in their own file group.
(Obviously don't put the clustered index in the same one, won't gain you
anything. :-)
> For more information about index in SQL Server, please refer to:
> CREATE INDEX (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188783.aspx
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Best regards,
> Charles Wang
> Microsoft Online Partner Support
> PLEASE NOTE: The partner managed newsgroups are provided
> to assist with break/fix issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader:
> microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, Greg,
Thanks for your pointing out. I am awfully sorry for that.
It seemed that I misuderstood the multi-column index before. Yes, you are
right. The multi-column index was useful for the left-most column.
Actually, there is a phase of description in SQL Server BOL:
==========================================Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (>), less than (<), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should
be ordered based on their level of distinctness, that is, from the most
distinct to the least distinct.
For example, if the index is defined as LastName, FirstName the index will
be useful when the search criterion is WHERE LastName = 'Smith' or WHERE
LastName = Smith AND FirstName LIKE 'J%'. However, the query optimizer
would not use the index for a query that searched only on FirstName (WHERE
FirstName = 'Jane').
============================================(ref: General Index Design Guidelines
http://msdn2.microsoft.com/en-us/library/ms191195.aspx )
I also do not recommend creating many single column indexes. I mean that it
is better to create single indexes for those critical columns which are
mostly common used as conditions for queries. Actually sometimes even if
you create an index on some column (which has many duplicated values), the
index may not be used when you perform a query on that column. SQL Server
optimizer will make a decision to choose a less cost solution between a
table full scan and the index query.
Thanks again!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thank you Greg,
It is my fault that I misunderstood it before.
Please refer to my reply to Greg Linwood.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================