Table Scans and indexes

I've checked the execution of a slow executing query that I run often. It shows a table scan of a large table.

Here is the query:

declare @DateAdded datetime

set @DateAdded = '5/1/2011'

SELECT     tblHero.fldHeroName,players.Alliance, 
    tblHero.fldHeroOwner, players.ActIndex, players.fldPlayerCities, 
    tblHero.fldHeroLevel, tblHero.fldHeroPower, 
    players.fldPlayerPrestige, players.fldPlayerName 
FROM         tblHero INNER JOIN 
(
    SELECT     MAX(fldPlayerCities) AS fldPlayerCities, MAX(fldPlayerAlliance) AS     Alliance, 
            MAX(fldPlayerPrestige) AS fldPlayerPrestige, fldPlayerName, 
            MAX(fldPlayerPrestige) - AVG(fldPlayerPrestige) AS ActIndex 
    FROM tblPlayer WHERE (fldPlayerDateAdded >= DATEADD(dd, - 4, @DateAdded)) GROUP BY     fldPlayerName
 ) players 
 ON tblHero.fldHeroOwner = players.fldPlayerName 
 WHERE     (tblHero.fldHeroIsHistoric = 1 or fldHeroLevel = 100) and 
    fldHeroDateAdded = @DateAdded 
 ORDER BY players.ActIndex, players.fldPlayerCities, players.fldPlayerPrestige

The predicate from the table sc开发者_如何学编程an is:

[dbo].[tblHero].[fldHeroDateAdded]=[@DateAdded] AND ([dbo].[tblHero].[fldHeroIsHistoric]=(1) OR [dbo].[tblHero].[fldHeroLevel]=(100))

The following indices are on the table:

CREATE NONCLUSTERED INDEX IX_tblHero_2 ON dbo.tblHero
    (
    fldHeroDateAdded DESC,
     fldHeroIsHistoric DESC
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO
 CREATE NONCLUSTERED INDEX IX_tblHero_3 ON dbo.tblHero
    (
    fldHeroDateAdded DESC,
    fldHeroLevel DESC
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO

And finally, the question:

What indexes should I add to get rid of the table scan and speed this up?

You need an index on fldPlayerDateAdded on tblPlayer for sure, as it is doing a date range search. You might also try adding a separate index on fldPlayerName. Composite indexes will do the same function, but will order them together, which may be ok for this situation, but is not the right situation for all the different situations you may or may not have. Date range queries are also better suited by clustered indexes, (ranges in general), but you probably have a clustered index already on the PK..

There are a couple of posibilities here...

Is the query stored in a stored-procedure, and were the indexes created after the stored procedure? If so, re-compile to stored-procedure to force SQL Server to generate a new execution plan.

Otherwise, it would appear that it is due to the join on the sub-query. If so, SQL Server is determining that (fldPlayerDateAdded >= DATEADD(dd, - 4, @DateAdded)) is the most important predicate. So it is generating the sub-query results first, then joining to the tblHero table. This gives at least two options...

EDIT - This may be partly due to the ORDER BY clauses all being from the sub-query. Possibly try without the ORDER BY, to see if this has any bearing.

First, if not present, you could add an index on (fldPlayerDateAdded,fldPlayerName) to tblPlayer. This would possibly allow the optimiser to filter the tblHero table first, then join to the sub-query.

Alternatively, to keep the same basic execution plan, but speed it up, include the field fldHeroOwner in your indexes on tblHero.

Note: It may be useful for you to provide the full actual execution plan to better determine what is happening and how to mitigate against it.

You may use OPTION(RECOMPILE) hint at the end of Sql stament, this hint force to recompile the query plan, this option is usefull when the Sql statement is used with differents parameters passed to de sp, just do a test.