@Gail,

There's already a job in place on the server that updates the statistics for that index every 2 hours. Is rebuilding the index any different to that?

@dave-3,

Plan for the first one is identical. Running the second one, it does an index scan on the primary key, and still ignores the index!

@dave-3,

Yep, that would be equivalent.

@Gail,

Execution plan of that version attached. Definitely different. Here's the performance statistics from the original:

SQL Server parse and compile time:

CPU...

And here's the results. Query ran faster with the index added. Still doing an Index Scan of the users table, but the join is now quicker.

Statistics are being updated daily at the moment, so I wouldn't have thought they'd be too far out of date. I can certainly try creating a covering index for those...

It's just below the table definition. Scroll down the code block a couple of posts above and you should see them.

I don't understand the logic behind its selection of IX_UsersDisplayName when it does the join into the Users table. Perhaps I'm just totally misunderstanding how indexes work, but I'm really...

Here's the DDL for the Users table:

CREATE TABLE [dbo].[Users]

(

[UserID] [int] NOT NULL IDENTITY(1, 1),

[SingleSignonMemberID] [int] NOT NULL,

[EmailAddress] [nvarchar] (250) COLLATE Latin1_General_CI_AS NOT NULL,

[DisplayName] [nvarchar] (100) COLLATE Latin1_General_CI_AS NOT NULL,

[CompanyID] [int]...

Well, at least it's saved me time in posting execution plans! 🙂

As I said, the nice thing would be to write an online execution plan display. That would be a...

Firefox's handling of MIME types has always been somewhat different to IEs. The MIME type that InstantForums is applying is application/octet-stream. Not quite sure why IE is getting upset at...

You should just be able to right-click and save as for the sqlplan. I'll attach a zipped version to this post as well, just to be on the safe side....

Ok, I've run a modified version of Jeff's suggestion:

IF @PointsCategory IS NULL

BEGIN

WITH

cteTotal AS

(--==== Create the required sums for each UserID. Additional user will NOT show up if

-- no...

Chris,

When I tried both options, there wasn't actually much difference in the performance of the two.

Sure!

The current query is:

CREATE TABLE #TopScores

(

UserID INT,

RecentPoints INT,

AllPoints INT,

RowNumber INT

)

CREATE CLUSTERED INDEX idx_TopScores_RowNumber ON #TopScores (RowNumber)

CREATE INDEX idx_TopScores_UserID ON #TopScores (UserID)

INSERT INTO #TopScores (

UserID,

RecentPoints,

AllPoints,

RowNumber

)

SELECT UserPoints.UserID,

SUM(CASE WHEN [Date] >= '2009-06-13'...

Just tried Jeff's suggestion, restructured the query to do a pre-aggregation with a CTE, and that slowed the performance down (more CPU time).