Thank you Jason. There are no clustered indexes... (there could be... I just don't know about clustered indexes with the partitions so I have not ventured down that road yet).

You are correct on the seek to scan (I checked the query plan for my "first" speedy version, and saw the index was being used, but never looked back after adding columns to see if it was still being used...turns out ... it's not).

My table is very wide... If seeing the layout of the table adds insight after this, I can post that as well. Based on your tip... here is what I see.

The fast version reports the following:

select sh.dl_load_date, member_nbr, share_nbr, open_date from share sh

where sh.dl_load_date = '2014-05-21 00:00:00'

and closed = 0

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Index Seek(OBJECT:([VCUWarehouse].[dbo].[SHARE].[idx_Share_Mbr] AS [sh]), SEEK:([sh].[DL_LOAD_DATE]='2014-05-21 00:00:00.000'), WHERE:([VCUWarehouse].[dbo].[SHARE].[CLOSED] as [sh].[CLOSED]=(0)) ORDERED FORWARD PARTITION ID:((180)))

When I add a column to the select that is not present in the index... we end up doing a table scan... which I now know is on Tier 3 of the compellant (10K drives) for 92 million rows... no wonder it takes 3 minutes. The million dollar question I have now is... why does adding a non indexed column to the select statement cause the index plan to not use the index on the first 2 keys in one of the indexes (the one it used before)...

StmtText

-------------------------------------------------------------------------------------------------------------------------------------------------------

select sh.dl_load_date, member_nbr, share_nbr, open_date, share_type

from share sh

where sh.dl_load_date = '2014-05-21 00:00:00'

and closed = 0

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Table Scan(OBJECT:([VCUWarehouse].[dbo].[SHARE] AS [sh]), WHERE:([VCUWarehouse].[dbo].[SHARE].[DL_LOAD_DATE] as [sh].[DL_LOAD_DATE]='2014-05-21 00:00:00.000' AND [VCUWarehouse].[dbo].[SHARE].[CLOSED] as [sh].[CLOSED]=(0)) PARTITION ID:((180)))

Modifying the index to include Share_Type does cause the index to get used again...and things are all good. Then I add balance, and blamo, back to table scan.