In my production environment the application runs query so it hasn't changed with or with out index. and the difference in time for me is 10 ms without name index...

Thanks Chad, for the effort. This has, at least, given me some extra troubleshooting abilities/ideas I did not have before so effort was not futile.

Unfortunately there is not that much of a discrepancy between the counts of the leading columns on the indexes (Lead_A, datetime, 32million and Lead_B, varchar, 27 million). Now five million...

I never bothered to check the uniqueness of the timestamp, and it turns out that there are numerous (200000) values that are the same.

I do both depending on the fragmentation. If its more then 30% I will do a rebuild. the reorganization is quite quick on both tables. auto stats is on.

there is not a clustered index on the datetime column. it is just a time stamp, thus all distinct, of when the row was inserted into the table, the...

Thanks Chad. There appears to be more fragmentation, pages and records in the non-leaf levels of Lead_B index which is taking longer. Could this account for Lead_B index taking approx...

I would not think credentials are cache but that sql server checks active directory for credentials each time the job is run. A simple test (although not practical in a...

I have solved my problem. After further investigation I found that the windows installer setting in the registry were not correct. I was missing keys for sql server 2005 sp2...

The admin share are enabled on both the active and passive node. I did run the installer from the active node. I will attempt to move cluster group to other...

I am trying to install additional components from the sql server 2005 cd locally on the box. I am logged in with a user that has admin right to the...

manu: i do not have autoshrink on

lynn: I have thought of changnig from percentage to fixed size. just haven't got around to it. but in light of the grow by...

heres link about tempdb

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Having tempdb on its own drive (raid 10 preferable) and having same number of tempdb file as you have cores would be optimal configuration.

thanks Michael, converting the 8 to bigint now lets the query run to success.