Why Sql doesn't use clustered index ?

Question

Why Sql doesn't use clustered index ?

farax_x

Hall of Fame

Points: 3579
More actions
February 21, 2015 at 12:31 pm

#314704

I have a table like this :
CREATE TABLE [dbo].[News_News](
[NewsId] [int] NOT NULL,
[Url] [nvarchar](4000) NOT NULL,
[Title] [nvarchar](4000) NOT NULL,
[Description] [nvarchar](4000) NULL,
[CreationDate] [datetime] NOT NULL,
[AgencyId] [int] NOT NULL,
[SCode] [varchar](6) NULL,
CONSTRAINT [PK_MyTest] PRIMARY KEY CLUSTERED
(
[NewsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [PRIMARY]
) ON [PRIMARY]
and one non clustered index :
CREATE NONCLUSTERED INDEX [News_Agancy_IDX] ON [dbo].[News_News]
(
[NewsId] ASC,
[AgencyId] 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 = 65) ON [PRIMARY]
this query optimizer prefer to use News_Agancy_IDX in order to clustered index. why ?
SELECT n.NewsId
FROM dbo.[News_News] AS n

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

farax_x (2/21/2015)
I have a table like this :
CREATE TABLE [dbo].[News_News](
[NewsId] [int] NOT NULL,
[Url] [nvarchar](4000) NOT NULL,
[Title] [nvarchar](4000) NOT NULL,
[Description] [nvarchar](4000) NULL,
[CreationDate] [datetime] NOT NULL,
[AgencyId] [int] NOT NULL,
[SCode] [varchar](6) NULL,
CONSTRAINT [PK_MyTest] PRIMARY KEY CLUSTERED
(
[NewsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [PRIMARY]
) ON [PRIMARY]
and one non clustered index :
CREATE NONCLUSTERED INDEX [News_Agancy_IDX] ON [dbo].[News_News]
(
[NewsId] ASC,
[AgencyId] 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 = 65) ON [PRIMARY]
this query optimizer prefer to use News_Agancy_IDX in order to clustered index. why ?
SELECT n.NewsId
FROM dbo.[News_News] AS n

It's really simple... the NCI has all of the data that is needed to be returned and it is much more narrow than the clustered index (which contains ALL of the data for each row at the leaf level). Since more rows could be returned using fewer reads (a read is a page) from the NCI (because more rows fit in the NCI for the given key at the leaf level), SQL Server has to do much less work using the NCI than the CI. The correctly recognizes all of that and, quite appropriately, uses the NCI instead of the CI.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

To extend what Jeff said, when SQL Server reads from disk it actually reads an Extent which contains 8 pages. The narrower NCI means more data is also read from disk when the NCI rather than the CI.