Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I look to at my query plan, I see plenty of clustered index seeks (yay!) and scans (not quite so yay!) but in some of them ordered = False. I will mention that we have parallelism involved, though it does not appear to have any relation to these. The indexes were set up with ordering.

What does this mean? That the query plan is ignoring this order? Or that the query is choosing not to order? Or... heck, I have no idea what I'm looking at...!

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

It just means that the query optimiser doesn't require an explicit order guarantee either for some later operator in the plan (e.g. merge join or stream aggregate) or to avoid a sort because you have explicitly requested an order by.

When ordered = false you might in some circumstances get an allocation ordered scan rather than a scan that follows the linked list of the leaf pages in index key order for example.

share|improve this answer
    
Since the scan type: index order or allocation order does not appear in the execution plan or elsewhere, what to tell people with questions such as this one? Basically, one can trade scan speed for data safety, no? I guess I would say, maybe consider forcing allocation order scans when doing big scans on tables, which are not changing at the time of the scan...thinking DWs. Thoughts? –  ooutwire Mar 13 '12 at 14:36
    
@ooutwire - In a DW if the table is on a read only file group wonder if you might get an allocation ordered scan considered automatically without having to use nolock or tablock. Would make sense but haven't tested this. –  Martin Smith Mar 13 '12 at 14:40
    
I agree, but the DWs I use are never read-only (unless we are mucking with them surreptitiously). I suppose if little logical fragmentation exists in the first place and nothing is going on to cause page splits, then one may see little gain from forcing the allocation scan. Guess it'll make a good blog post. I have some TB-sized DWs I can attack for fun. –  ooutwire Mar 13 '12 at 14:55
    
Makes good sense, Martin. Excellent link you shared as well. Thanks much! –  JHFB Mar 13 '12 at 16:19
add comment

I believe the Ordered portion of an index scan is just indicative of whether or not your query is ordered by the index key column(s).

Say you have something like this:

-- Table Definition for reference
CREATE TABLE [HumanResources].[Employee](
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [NationalIDNumber] [nvarchar](15) NOT NULL,
    [ContactID] [int] NOT NULL,
    [LoginID] [nvarchar](256) NOT NULL,
    [ManagerID] [int] NULL,
    [Title] [nvarchar](50) NOT NULL,
    [BirthDate] [datetime] NOT NULL,
    [MaritalStatus] [nchar](1) NOT NULL,
    [Gender] [nchar](1) NOT NULL,
    [HireDate] [datetime] NOT NULL,
    [SalariedFlag] [dbo].[Flag] NOT NULL,
    [VacationHours] [smallint] NOT NULL,
    [SickLeaveHours] [smallint] NOT NULL,
    [CurrentFlag] [dbo].[Flag] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED 
(
    [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



use AdventureWorks
go

select *
from HumanResources.Employee

This would be a clustered index scan with Ordered = False. But modify it to look like this:

use AdventureWorks
go

select *
from HumanResources.Employee
order by EmployeeID

You will then have Ordered = True because you are ordering by the index key column.

But if you have something like this (ORDER BY on a non-key column):

use AdventureWorks
go

select *
from HumanResources.Employee
order by ContactID

Then Ordered = False because the sorting isn't based on the index key column(s).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.