Given an example table of:
customer, order, product, dollar, note, yadda1, yadda2, yadda3

Given an index of
customer+order+product

Isn't it completely redundant to have an index of:
customer
or an index of
customer+order

Where querying based on customer+order (or customer alone), wouldn't SQL
Server use the single customer+order+product index equally as well as if it
also had a customer+order index?

[quoted text, click to view]


Thanks for the response...now in the case of a field in an index that gets
updated, the writes across indexes probably far outweigh the benefits of
having an extra index for faster reads, correct?

For arguments sakes, say in this example of having an index of
customer+order+product and an index of customer+order to speed up the
customer+order read queries: What if the order number is updated
frequently? I'm assuming that if it were "highly unlikely" that you'd have
the two indexes for the reads, I imagine it would "very highly unlikely"
that you'd have the two indexes if you are writing fields contained within
them, correct?

I didn't think of the lower memory for reads as a vote for multiple
indexes...just trying to fish out what else I haven't thought about. :)

[quoted text, click to view]

But the execution plans changes over time, correct? (Based on data growth,
usage, etc.) Is the only way to truly keep on top of things is to test
every query all the time? I'm looking for general practices here. For
high-value queries then perhaps it would be wise to test different types of
indexes to see what worked best.

That leads to the follow-up question of: is there a way to tell if SQL
Server isn't utilizing an index? (Say there is an index in place on
customer+order+product and another index in place that I would consider to
be a totally redundant on customer+order. It would be nice to track that
customer+order hardly ever gets utilized.so the extra cost of having the
index would outweigh any benefits of having the index in the first place...
(See what I mean?) Is there a tool that spits out this kind of index bloat?
Or is the tool labeled "DBA"? :)

There are some 'redundant' indexes currently in place (that I didn't put
there) and I don't want to simply remove them without debating on the
consequences first. I'm obviously not a DBA and they weren't placed there
by a DBA either. I will start testing queries and manually test to see if
SQL is choosing an executation plan that includes the redundant index but
wouldn't it be difficult to know exactly if the extra cost of having the
redundant index is worth it? If I remove the redundant index, then SQL
would simply (assumedly) choose a plan based on the bigger index ... and if
that is the case, and if the read costs are about the same for the server,
then I'm assuming that it would be general practice to remove the redundant
index. I guess it simply takes experience to know if the read costs are
'about the same' = keep the index or drop the index?

[quoted text, click to view]



Unfortunately, it depends. Although the engine may be able to satisfy
the query on Customer, Order using the Customer, Order, Product index,
Product will make the index larger. This means that less rows will be able
to fit in memory per index page and as a result the server may have to do
more work to satisfy the query.

Generally, though, if I were to have an index like the one you mention
(Customer, Order, Product), it would be highly unlikely that I'd create
another index with just Customer, Order.


--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

[quoted text, click to view]

I can't answer that generally. You'll have to stress test your system
to see if it's going to be a problem for you.


--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

[quoted text, click to view]


Try this article:

http://www.sql-server-performance.com/lm_index_elimination_english.asp

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

[quoted text, click to view]


Your disks might care, if they're full.

And your inserts might care, if you have too many indexes and they're
slow.

Personally, I dislike bloat and don't like to keep around a lot of
unused objects in my database, be they indexes, columns, tables, stored
procedures, disabled constraints, or anything else. It keeps the data
dictionary smaller and users of the database need to take less time
scratching their head wondering if something should be there or not.

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

[quoted text, click to view]


[quoted text, click to view]

No. SQL Server is free to use any single index or combination of indexes or
no index at all. The only way to find out what was used is by looking at the
execution plan.

/ Fredrik

[quoted text, click to view]

Yes

[quoted text, click to view]

No. You only need to test your queries when you feel that the response time
is to slow. You only need to optimize until you reach a state wher
everything is good enough. Optimisations beyond this is a waste of time and
money. This is not only general practice, it is best practice.

[quoted text, click to view]

Hmmm. You may make many different kind of queries Some of them will use an
index and some of them won't. I remember you asked "What if I update order
frequently?". I would suggest a single index on order rather than
customer+order

There's another problem. Isn't it reasonable to assume that you have more
orders than customers? The "orders" column" is more selective than
customers. Hopefully, you have more customers than products. The most
selective column should come first. In other words

order+customer+product is better than
customer+order+product

You don't need to change the table, only the index

[quoted text, click to view]

Like a customer+products index?

[quoted text, click to view]

Why? Why do you want to know whether an index is used or not??? Some queries
do and some queries don't. What you should test is how your database
performs. That's the only thing that truly matters. Who cares whether an
index is used or not? Your customers certainly don't.


If I remove the redundant index, then SQL
[quoted text, click to view]

[quoted text, click to view]

Disks may care but they come both big and cheap these days. Inserts might
care but that falls under performance. You may want to remove such indexes
if you do a batch update and restore them later.

I completely agree with the rest. I too dislike bloat. Unused objects should
be removed. Not only do they occupy space, they lso make it harder to
maintain and understand the database. Imagine if someone spends days to
understand a stored procedure only to find out that it is never used.
Objects should be kept at a minimum.

/ Fredrik