Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

INDEXING Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 5:36 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 21, 2014 2:15 PM
Points: 175, Visits: 515
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
Post #1450371
Posted Tuesday, May 7, 2013 6:44 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
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/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1450381
Posted Tuesday, May 7, 2013 11:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:32 AM
Points: 34, Visits: 70
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.
Post #1450403
Posted Wednesday, May 8, 2013 12:16 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 21, 2014 2:15 PM
Points: 175, Visits: 515
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 ..
Post #1450414
Posted Wednesday, May 8, 2013 12:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:32 AM
Points: 34, Visits: 70
If u post select scripts too with index details that will be more helpful to see what happened and why.
Post #1450417
Posted Wednesday, May 8, 2013 12:56 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 21, 2014 2:15 PM
Points: 175, Visits: 515
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
Post #1450426
Posted Wednesday, May 8, 2013 6:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:27 PM
Points: 13,776, Visits: 28,178
tony28 (5/8/2013)
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


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 Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1450528
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse