So, I understand an index with a column in the wrong sort order will still be used? Please correct me if I'm wrong, I really want to understand this.

If it's...

So that means if column C is sorted in the wrong order, it won't be used by that one query?

Scott:

I did not, but would that make a difference? I didn't think the missing indexes dmv...

The indexes were identical.. I did create the index as recommended by the script, then I ran a duplicate indexes script, and yes, here they were, both old and new....

That's probably the only possible explanation.. I am surprised, I've used it extensilvely and it never happened before. Thanks!

Thank you!

I have already asked for more disk space last week...

There are also several unused tables that I discovered, and I am fighting now to have them removed. Might not happen.

I...

So you're saying since I can't use tempdb for sorting because I only have 81GB available, I should take care of that first..

Well, this is a large table, 99% of...

Well, it's not full, sorry, it just doesn't have enough room available (81 out of 84GB). It is not set to autogrow, it takes up 99% of a disk partition,...

TempDB seems to be always full, maybe I'll reboot first.. thanks!

never mind, teh log size is separat...

select

name,

cast((size/128.0) as int) as TotalSpaceInMB,

cast((cast(fileproperty(name, 'SpaceUsed') as int)/128.0) as int) as UsedSpaceInMB,

...

Smaller table on the test server? A build or rebuild of an index needs the full size of that index as free space, more if sort in tempDB is not...

For some reason, I thought you need the full space of the table only with ONLINE=ON. It didn't use that much on the test system.

Looks like I will have to...

That's what I did, implement datetime as the clustered index and deal with fragmentation maybe on a daily (nightly) basis. Goes along with what Craig said as well, since I...

This is great post by Adam Machanic, I guess that's what I'll do, change settings and test...

http://sqlblog.com/blogs/adam_machanic/archive/2010/05/28/sql-university-parallelism-week-part-3-settings-and-options.aspx