Indexes on SQL Server

27 May 2005 10:23 AM

Stimp

Hi all,

I'm not au fait with indexes as much as I should be...

If a table has a lot of indexes (circa 18) and an insert is carried out
on this table roughly every 5 seconds, this is a bad thing isn't it?

i.e. you should only use this many indexes on tables where SELECT
statements are used, more than INSERTs

Also, when creating indexes for a reports table, is Profiler the best
application to decide on the indexes?

Finally, if I have a statement like:

SELECT * FROM Blah ORDER BY a, b, c

.... should I create an index on (a, b, c) together ?
i.e. Should I use my ORDER BY statements as guidelines on which indexes
to use?

Thanks!
Peter

--

"I hear ma train a comin'
.... hear freedom comin"

27 May 2005 10:56 AM

John Bell

Hi

Unually it is usually not the number of actions taken against the data, but
the performance of those actions that is important. You will compromise
insert performance with a heavily indexed table, but this may be acceptable
if something else is compromised by not having them.

For creating the indexes you can look at the query plan(s) for the commands
you wish to improve. Query Analyser is a good place to look at these. It is
also worth looking at the Index tuning wizard to see if it comes up with any
suggestions.

You may also want to look at
http://www.sql-server-performance.com/lm_index_elimination_english.asp to
remove unused indexes.

Other reading:
http://www.sql-server-performance.com/mr_indexing.asp
http://www.sql-server-performance.com/nb_execution_plan_statistics.asp

John

Show quoteHide quote

"Stimp" wrote:

> Hi all,
>
> I'm not au fait with indexes as much as I should be...
>
> If a table has a lot of indexes (circa 18) and an insert is carried out
> on this table roughly every 5 seconds, this is a bad thing isn't it?
>
> i.e. you should only use this many indexes on tables where SELECT
> statements are used, more than INSERTs
>
> Also, when creating indexes for a reports table, is Profiler the best
> application to decide on the indexes?
>
> Finally, if I have a statement like:
>
> SELECT * FROM Blah ORDER BY a, b, c
>
> .... should I create an index on (a, b, c) together ?
> i.e. Should I use my ORDER BY statements as guidelines on which indexes
> to use?
>
> Thanks!
> Peter
> --
>
> "I hear ma train a comin'
> .... hear freedom comin"
>

27 May 2005 11:19 AM

Stimp

On Fri, 27 May 2005 John Bell <jbellnewspo***@h0tmail.com> wrote:

>
> You may also want to look at
> http://www.sql-server-performance.com/lm_index_elimination_english.asp to
> remove unused indexes.
>
> Other reading:
> http://www.sql-server-performance.com/mr_indexing.asp
> http://www.sql-server-performance.com/nb_execution_plan_statistics.asp

Thanks for the information!

Peter

--

"I hear ma train a comin'
.... hear freedom comin"

27 May 2005 2:01 PM

JT

Every time insert or update changes a column that is included in an index,
that index is updated as well, so that's a performace hit. It is not
necessary to index every column used in the select's where or order clause.
For example, if you go the grocery store and ask the clerk where to find
canned corn, they will simply tell you isle number 3. They probably won't
tell you the shelf or slot number, becuase it would be too much trouble for
them to retain that level of detail in their memory, and besides the
additional information would only marginally decrease the time required for
your search.

Show quoteHide quote

"Stimp" <r**@spumco.com> wrote in message
news:slrnd9dtab.s2m.ren@carbon.redbrick.dcu.ie...
> Hi all,
>
> I'm not au fait with indexes as much as I should be...
>
> If a table has a lot of indexes (circa 18) and an insert is carried out
> on this table roughly every 5 seconds, this is a bad thing isn't it?
>
> i.e. you should only use this many indexes on tables where SELECT
> statements are used, more than INSERTs
>
> Also, when creating indexes for a reports table, is Profiler the best
> application to decide on the indexes?
>
> Finally, if I have a statement like:
>
> SELECT * FROM Blah ORDER BY a, b, c
>
> ... should I create an index on (a, b, c) together ?
> i.e. Should I use my ORDER BY statements as guidelines on which indexes
> to use?
>
> Thanks!
> Peter
> --
>
> "I hear ma train a comin'
> ... hear freedom comin"