Access path selection

SQL Server 2000 32 bit sp4 / Windows 2003

I have a table that has over 7.5 million rows. I have a unique clustered index (U_ORDR, D_ORDR_YR). There are 5342000 distinct occurances of U_ORDR and 24 distinct occurances of D_ORDR_YR. Database Tuning Advisor showed that there should be a nonclustered index created like (D_ORDR_YR, U_ORDR). I created this index and ran a query with show execution plan. The nonclustered index is chosen even though a bookmark lookup is required. Also the way I understand it the clustered index would be used to get to the C_ORDR column. So why is SQL Server choosing to access the nonclustered index. The plan shows a clustered index scan when I force the clustered index to be used. It also show that it would take longer to use the clustered index (85%) than to use the nonclustered/bookmark access (15%). Here is the query that I am using

declare @.OrderNumber [bigint], @.OrderYear [bigint]

select @.OrderNumber = 1753851, @.OrderYear = 02

SELECT C_ORDR

FROM CIS540T CIS540T --with (index (IX_CIS540T_Ordr_no))

WHERE CIS540T.U_ORDR = @.OrderNumber and CIS540T.D_ORDR_YR = @.OrderYear

If this is the exact query, consider just putting an index to cover:

C_ORDR, U_ORDR, D_ORDR_YR

Then you get the best of both worlds...

|||

Just to expand on Louis' suggestion in that you should specify the following order for the columns in your new index:

U_ORDR, D_ORDR_YR, C_ORDR

It sounds like the U_ORDR column offers the highest selectivity, followed by D_ORDER_YR. C_ORDR is included to form a covering index for your query.

Chris

|||My question are why is SQL Server doing an index scan when I told it that D_ORDR_YR and U_ORDR is unique. Why doesn't it use an index seek?|||

Both U_ORDR and D_ORDR_YR are defined as BIGINT in the table?

|||Both columns are defined as decimal. U_ORDR with a precision of 7 and U_ORDR_YR with a prrcision of 3. I changed the definition of @.U_ORDR and @.D_ORDR_YR to decimal the plan stayed the same. It is still doing an index scan instead of a seek.|||

Hmmm, the conversion can/will cause a scan to substituted for a scan.

try issuing:

DBCC FREEPROCCACHE

GO

first and then rerun.

|||It does seem to have been a column definition problem. I did some more swapping of data types and was able get the plan to show an index seek. Thanks for your help.

Access denied while adding node

I have a SQL Server 2k cluster setup on one node and I'm trying to add it to the second one. I already have the two server clustered with MSDTC, however I cannot get the SQL install to add the second node. Every time I run the setup to add it I receive
the following error: "An error occurred while creating virtual server resources for clustering. Access is Denied". I have tried several methods of the install but with no success. Any help would be greatly appreciated.
Ed
Is the account you are using to run the installation a domain user who is a
local admin on both machines?
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Check KB article 273769 and see if that describes your problem.
|||Unfortunately, no. I chose Per Seat licensing from the beginning.
|||Yes, I am using my domain admin account to install the second node and also have the same username as an administrator on both servers.