Re: Redundant Indexes?
From: Erland Sommarskog (esquel_at_sommarskog.se)
Date: 12/27/04
- Next message: Derrick Leggett: "Re: dual processor license"
- Previous message: Sean Blaes: "SQL Server Agent hangs and becomes unresponsive"
- In reply to: Jorge Luzarraga Castro: "Redundant Indexes?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 27 Dec 2004 00:14:03 +0000 (UTC)
Jorge Luzarraga Castro (jluzarraga@fidens.cl) writes:
> I´ve got a table called 'prd_movcomision' (I´ve attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
>
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
>
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo],
> [itpo_codigo])
> ON [PRIMARY]
> GO
>
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
> GO
>
>
> Given the previous indexes, [B] is contained within [A] and [C] is
> contained within [B]. The question is if this is really neccessary or
> once I have an index A the other indexes (B and C) are redundant.
There are no redudant indexes in your table. There may be unused indexes,
that I can't tell, because that depends on your queries.
While the columns index B is included in index A, a query like:
SELECT * FROM tbl WHERE poli_coidgo = @val1 AND itpo_codigo = @val2
cannot seek index A to find any rows. It can scan the index, though.
The difference here is that "seek" means that SQL Server finds the
value by walking the fast way through the index tree. "scan" means
that SQL Server searches all leaf pages of the index, which is considerably
slower. In reality, SQL Server will probably scan the entire table instead,
which is even slower. (As a scan. Beside the scanning the index, SQL Server
will also have to access data pages, and with many hits, that can be more
data-pages access than a table scan.)
The same goes for index C, assuming that you mean to have itpo_codigo hear.
So the important issue here, is whether you actually have any queries for
which these indexes are useful.
> I´m having too many problems with the size of the database, it is
> growing too fast.
I don't think dropping indexes is a very good help to address that. In
such case it's better to examine whether there is data that is inserted
multiple times, data which is not removed as it should be etc.
Dropping an index can conserve some space - but it can also kill
performance, if there is a query which needs the index.
-- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
- Next message: Derrick Leggett: "Re: dual processor license"
- Previous message: Sean Blaes: "SQL Server Agent hangs and becomes unresponsive"
- In reply to: Jorge Luzarraga Castro: "Redundant Indexes?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
