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"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.c...ion_english.asp to
remove unused indexes.
Other reading:
http://www.sql-server-performance.com/mr_indexing.asp
http://www.sql-server-performance.c..._statistics.asp
John
"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"
>|||On Fri, 27 May 2005 John Bell <jbellnewsposts@.h0tmail.com> wrote:
> You may also want to look at
> http://www.sql-server-performance.c...ion_english.asp to
> remove unused indexes.
> Other reading:
> http://www.sql-server-performance.com/mr_indexing.asp
> http://www.sql-server-performance.c..._statistics.asp
Thanks for the information!
Peter
--
"I hear ma train a comin'
... hear freedom comin"|||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.
"Stimp" <ren@.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"
MarilynJ wrote: