1

Topic: The strange choice of an index

Good afternoon!
There is a table:

CREATE TABLE [dbo]. [RashodL_CurDate] (
[ralID] [bigint] NOT NULL;
[ralCurDate] [datetime] NOT NULL CONSTRAINT [DF_RashodL_CurDate_ralInDate] DEFAULT (getdate ());
[usID] [smallint] NOT NULL;
CONSTRAINT [PK_RashodL_CurDate] PRIMARY KEY CLUSTERED
([ralID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_RashodL_CurDate_usID] ON [dbo]. [RashodL_CurDate]
([usID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
ALTER TABLE [dbo]. [RashodL_CurDate] WITH CHECK ADD CONSTRAINT [FK_RashodL_CurDate_RashodL] FOREIGN KEY ([ralID]) REFERENCES [dbo]. [RashodL] ([ID])
ON DELETE CASCADE
ALTER TABLE [dbo]. [RashodL_CurDate] CHECK CONSTRAINT [FK_RashodL_CurDate_RashodL]
ALTER TABLE [dbo]. [RashodL_CurDate] WITH CHECK ADD CONSTRAINT [FK_RashodL_CurDate_Users] FOREIGN KEY ([usID]) REFERENCES [dbo]. [Users] ([ID])
ALTER TABLE [dbo]. [RashodL_CurDate] CHECK CONSTRAINT [FK_RashodL_CurDate_Users]

And request:

DECLARE @shID tinyint = 1;
SELECT RashodL.ID As ralID, ralNomer, otAPB, otName, RashodL.usID, ISNULL (RashodL.usIDPlat, 0) AS usIDk
FROM RashodL WITH (noLock)
INNER JOIN RashodL_CurDate ON RashodL.ID = RashodL_CurDate.ralID
INNER JOIN Otdel WITH (noLock) ON RashodL.otID = Otdel. ID
WHERE RashodL.shID = @shID;

At creation of the execution plan of request review of index IX_RashodL_CurDate_usID is used.
Why search in a cluster principal key is not used?

2

Re: The strange choice of an index

Add  in request usage cluster as you want and look what there will be on it estimations on IO and CPU and compare to estimations for not cluster which server selects.
Where it is less?

3

Re: The strange choice of an index

saszay;
At you table RashodL_CurDate small and the optimizer selects more preferably scanning, or at you the statistics which has become outdated on it.

4

Re: The strange choice of an index

Yes, the table small. It is no more 100 records

5

Re: The strange choice of an index

From table RashodL_CurDate the field ralID is necessary only;
So what for to climb in cluster, in which all fields;
If is IX_RashodL_CurDate_usID in which too is ralID (is the key cluster);
And it is less than fields, only 2: usID + ralID
On this field any not search, connection is simple.
Its orderliness is not necessary.
It is banal the minimum index containing this field is selected

6

Re: The strange choice of an index

Yasha123;
Clearly, thanks