Overlapping Indexes

In examining one of our DBs there is a sales table with the following fields:

WDate: SMALLDATETIME PK
StoreID INT PK
ItemID: INT PK
UnitsSold: SMALLINT
TotalSales: REAL
SalesCode: CHAR(1)

There are also other attributes that do not pertain to the question.

The following Indexes are in the Sales table:

PK_Sales: (Clustered) Composit Primary Key
WDate, StoreID, ItemID

IX_Sales_SalesCode: (Non-Clustered)
SalesCode

IX_Sales_Cover: (Non-Clustered)
WDate, StoreID, ItemID, UnitsSold, TotalSales

Typical queries include:
Querying by the 3 field PK
Querying by the 3 field PK + Sales Code and summing UnitsSold and Total Sales
Querying by the 3 field PK and summing UnitsSold and Total Sales

We have several versions of this same DB with different Data in it for different Store Chains. Since we must keep 2 years of history in the DB, the sales table can be quite large. One of our versions of the DB has nearly 1 billion records and the indexing seems to run quite efficiently. In other words no huge amount of time spend querying (Keep in mind there are nearly a billion records.)

My question is:
Do these indexes look correct? Obviously the PK index is fine but are the other 2 indexes ever being hit? In other words, are the 2 indexes other that the PK index worth keeping or are they just taking up disk space and degradding performance?

Thanks in advance for any help on this.
mcatet

You have multiple ways to find out
(1) you can look at the query plan generated for each query and see what indexes are being used
(2) you can use DMV sys.dm_db_index_usage_stats to find out the usage statistics of each of the indexes since the SQL server started. Please refer to BOL for more details. There are other index related DMVs that you may find useful

Without knowing the text of your queries (we need to know what columns are being selected and what predicated are being used) and the selectivity of each predicate, it is hard to know what indexes will be used.

Some observations:
Query-1: If you are using range predicates and the select clause has columns not covered by indexx-3, then this is definitely useful.
Query-3: since it is covered by index-3 keys, it may be useful as it will avoid accessing the datapage.

|||Besides the advices from Sunil you can consider also using Index Tuning Wizard (in SQL Server 2000) or Database Tuning Advisor (in SQL Server 2005). The best is to provide representative sample of the workload to the tool.