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

Sort works on one index and doesn't work on the other? Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 10:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 1,274, Visits: 2,937
I have a query which does a order by at the end.

where WFID ='Something'
Order by Rkey asc, NFD desc


And my index is:

CREATE NONCLUSTERED INDEX [temp] ON [dbo].[WrkFS]
(
[WFid] ASC,
[SId] ASC,
[NFTId] ASC,
[AId] ASC,
[RKey] asc,
[NFD] desc
)
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

With above index i still see a sort operator, even though the two columns are added to the index key columns.

With below index i don't see sort operator

CREATE NONCLUSTERED INDEX [temp] ON [dbo].[WrkFS]
(
[WFId] ASC,
[RKey] asc,
[NFD] desc
)
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

Any thoughts on why 2nd one works and 1st one doesn't?
Post #1433904
Posted Thursday, March 21, 2013 10:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:45 PM
Points: 23,081, Visits: 31,620
sqldba_newbie (3/21/2013)
I have a query which does a order by at the end.

where WFID ='Something'
Order by Rkey asc, NFD desc


And my index is:

CREATE NONCLUSTERED INDEX [temp] ON [dbo].[WrkFS]
(
[WFid] ASC,
[SId] ASC,
[NFTId] ASC,
[AId] ASC,
[RKey] asc,
[NFD] desc
)
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

With above index i still see a sort operator, even though the two columns are added to the index key columns.

With below index i don't see sort operator

CREATE NONCLUSTERED INDEX [temp] ON [dbo].[WrkFS]
(
[WFId] ASC,
[RKey] asc,
[NFD] desc
)
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

Any thoughts on why 2nd one works and 1st one doesn't?


Yes, the columns between WFId and RKey. With those columns, the values in RKey and NFD are not sorted as they are in the second index.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1433908
Posted Thursday, March 21, 2013 11:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 1,274, Visits: 2,937
Lynn Pettis (3/21/2013)
sqldba_newbie (3/21/2013)
I have a query which does a order by at the end.

where WFID ='Something'
Order by Rkey asc, NFD desc


And my index is:

CREATE NONCLUSTERED INDEX [temp] ON [dbo].[WrkFS]
(
[WFid] ASC,
[SId] ASC,
[NFTId] ASC,
[AId] ASC,
[RKey] asc,
[NFD] desc
)
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

With above index i still see a sort operator, even though the two columns are added to the index key columns.

With below index i don't see sort operator

CREATE NONCLUSTERED INDEX [temp] ON [dbo].[WrkFS]
(
[WFId] ASC,
[RKey] asc,
[NFD] desc
)
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

Any thoughts on why 2nd one works and 1st one doesn't?


Yes, the columns between WFId and RKey. With those columns, the values in RKey and NFD are not sorted as they are in the second index.


hmmm...so in that case i need specific indices ONLY for those two columns on which sort is done?
Post #1433919
Posted Thursday, March 21, 2013 11:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 42,488, Visits: 35,556
DEpends whether the sort's expensive enough to justify a duplicate index.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1433935
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse