May 7, 2013 at 5:36 pm
Hello,
i have maybe stupid question
why sometimes the SELECT use Noncluster index instead cluster index
I thought that still use PK like primary, if there is value what I searched, but if I created new one just with the searched value is faster.
Does someone any good explain about background indexing and using it?
CREATE NONCLUSTERED INDEX [IX_TB_INSPECTION_DETAIL] ON [dbo].[TB_INSPECTION_DETAIL]
(
[ORDER_DATE] ASC,
[COMMIT_NO] 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) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TB_INSPECTION_DETAIL] ADD CONSTRAINT [PK_TB_INSPECTION_DETAIL] PRIMARY KEY CLUSTERED
(
[LINE_CODE] ASC,
[ORDER_DATE] ASC,
[COMMIT_NO] ASC,
[BODY_NO] ASC,
[ITEM_CODE] ASC,
[DEFECT_CODE] ASC,
[CREATE_DTTM] ASC,
[POSITION] ASC,
[POSITION_CODE] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
thx for response
May 7, 2013 at 6:44 pm
From my understanding, it is better to create your clustered index on primary keys, while leaving the non-clustered indexes to everything else you might search on. If you have a clustered index that has as many columns as yours, it might be more efficiently run for SQL to use the non-clustered index (which only has two columns to find the data.
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
May 7, 2013 at 11:35 pm
tony28 (5/7/2013)
Hello,i have maybe stupid question
why sometimes the SELECT use Noncluster index instead cluster index
I thought that still use PK like primary, if there is value what I searched, but if I created new one just with the searched value is faster.
thx for response
Hey tony,
Its depend on SQL Server query optimizer to select a clustered or nonclustered index for any script(But in general optimizer select index on the basis of columns used in where clause).
If u r not using those columns which are in PK or clustered index then it not worth to use such clustered index instead of a nonclustered index which have those column which are used in where clause in your scripts.
May 8, 2013 at 12:16 am
hi, thx for reply
I didnt write all about it ..heh
these columns are in PK
when I executed query, in tab of execution plan was the label - MISSING INDEX and etc...
so according to this I created this index, and the query was faster ...
and I think that it will be like Calibear wrote
from my view now , optimizer will use smaller index ..
May 8, 2013 at 12:28 am
If u post select scripts too with index details that will be more helpful to see what happened and why.
May 8, 2013 at 12:56 am
here is simple select, I found in little difficult, but here you can see also
select ORDER_DATE from TB_INSPECTION_DETAIL WHERE ORDER_DATE='20130507' and COMMIT_NO='0085'
subtree cost
IX 0,0032838 - rows 1,62897
PK 0,0464984 - rows 1,62897
May 8, 2013 at 6:48 am
tony28 (5/8/2013)
here is simple select, I found in little difficult, but here you can see alsoselect ORDER_DATE from TB_INSPECTION_DETAIL WHERE ORDER_DATE='20130507' and COMMIT_NO='0085'
subtree cost
IX 0,0032838 - rows 1,62897
PK 0,0464984 - rows 1,62897
The query you are showing uses columns that are all within the nonclustered index you defined above. This makes the index into what is known as a covering index. In effect, for this query, it's a clustered index since all the information it needs is there. The optimizer is smart enough to recognize that the nonclustered index is going to be a better selection for this query because it's going to filter off the leading edge of that index whereas with the clustered index, the necessary columns, while in the key (that's a VERY large key by the way and may lead to other issues), would require more filtering.
As to those costs, they're useful for comparing operations to each other within a plan, but they're not very useful outside the plan because they are estimated costs, even on an actual execution plan.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply