order of index

Dear all,

some expertists said order of index does a lot of matter on the tuning of performance, is that right ?what is the theory of it and an example before and after that ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Nope.

The query plan is compiled based on cost. The optimizer choses the indices which make queries cheaper.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I think what your were asking is if the order of the fields in the index matter when there is more than one field, and that is true.

If the query cannot be expressed in a way that matches the key in the index, than the index would be ignored.

Jim.
0
Scott PletcherSenior DBACommented:
The order of key columns is an index is absolutely critical.

Also, first creating the best clustered index, before creating nonclustered indexes, is also critical for performance.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
ste5an

tks, easy to understand this.


Jim,

"I think what your were asking is if the order of the fields in the index matter when there is more than one field, and that is true."

ok, the order of fields when creating the index does matter, how matter, this should be the correct question then.

"If the query cannot be expressed in a way that matches the key in the index, than the index would be ignored."

key in the index means the column defined in the index, right? so you mean is her the column do not have an index, then SQL server will perform slowly and it will always looks for an index..

ScottPletcher,

"The order of key columns is an index is absolutely critical."

so you are agreeing on the same thing Jim pointed out.

"Also, first creating the best clustered index, before creating nonclustered indexes, is also critical for performance. "

I also way to ask you on this , so for each table I have to:
1) create clustered index(usually primary key, agree?)
2) AND/OR create unique constraint/index ,someone in my other post said just create unique non-clustered index/unique clustered index already prefect enough.
3) then let the stats runs for a long time and see what else non-clustered index the optimizer also looking for.

right?

or 1) and 2 ) has to be done or just either of them ?

Dear all,

please share me how the order of column of index does matter and any example of it on what is bad and good ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

can anyone help me on example about what is the correct column order when defining index ?
0
ste5anSenior DeveloperCommented:
The column order should respect the distribution of values in those columns.

Consider the extrem distribution of column A INT where A is distinct thus each value exists only once. And a column B INT where only two values are used (1,2).

Now we want select by WHERE A=1 AND B=1.

Having an index of (A,B) now leads to exactly on logical read - this means only on page of index data is read - to find our row. Having an index of (B, A) makes currently no difference due to the ascending sort order.

Now we want to select WHERE A=132132312321 AND B = 2.
Now we need more reads. Cause we need an index seek to locate that row. When we have it we don't need further reads due to the fact that A is implicit unique.

Now consider the same with an index ( B, A ). Now we need an seek to find the start of all pages with B=2, they are all in the second part of the index, then we need to seek again to find the A value in that part. You'll see we need to read much more data from this index.

In terms of performance: You need to test how indices are used and you need to look at the execution plan (SSMS, Query, Show Actual Execution Plan) and use the SET STATISTICS IO ON directive before your query to get the numbers of reads in the message output window.

Then you will see that in case 1 we should have one logical read and a index seek. In case it is still a index seek, but some more reads to locate the row in the index.  In case 3 we will still have an index seek (imho) and more reads than in case 2. (Assuming that we a really large test table).
0
marrowyungSenior Technical architecture (Data)Author Commented:
"In terms of performance: You need to test how indices are used and you need to look at the execution plan (SSMS, Query, Show Actual Execution Plan) and use the SET STATISTICS IO ON directive before your query to get the numbers of reads in the message output window."

yeah, showplan_all also important to quickly see what index are using , agree ?

"Having an index of (A,B) now leads to exactly on logical read - this means only on page of index data is read - to find our row"

I think logical read is going to read from RAM/buffer cache, not read only the index, right?

"Having an index of (B, A) makes currently no difference due to the ascending sort order."

I don't see sort in the query, why ascending sort order ? what is your sort about ?

"Now consider the same with an index ( B, A ). Now we need an seek to find the start of all pages with B=2, they are all in the second part of the index, then we need to seek again to find the A value in that part. You'll see we need to read much more data from this index."

this mean, the nature of this type of order, when it is important, is the column with unique value should be on the first read/part of the index?  so if C,A  and D, when compare together is our unique constraint, where B is still the 1,2, then index should be index (C,A,D,B) will be perfect? not index (B,C,A,D), right?

"  In case 3 we will still have an index seek (imho) and more reads than in case 2. (Assuming that we a really large test table). "

you mean this "Now consider the same with an index ( B, A ).", will involve more read than this "Now we want to select WHERE A=132132312321 AND B = 2.?
0
marrowyungSenior Technical architecture (Data)Author Commented:
any update ste5an ?
0
ste5anSenior DeveloperCommented:
Yup, yup, yup, the order in the index and the distribution of data over the pages, no*, yup.

*) you design the index in this way that you reduce the necessary page reads to a minimum. This does not necessarily mean to select the first value when it is unique. Being unique in this case helps, cause we reduce the necessary read by applying our knowledge over that column. This no always possible.

p.s. sorry for the delay and the short answer. No time currently.

p.p.s. for the matter of optimizing: read Itzik Ben-Gan's books.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I think logical read is going to read from RAM/buffer cache, not read only the index, right?"

if you yup about that and why it is about reading index page before? are you talking about that?

can you re-quote what is the yup about ? too short please.

"p.s. sorry for the delay and the short answer. No time currently."

Me too, take care, be well. can you imagine that I need to have a DAILY MEETING on what the freak has been done yesterday, this is my first job need that.

"p.p.s. for the matter of optimizing: read Itzik Ben-Gan's books. "

p.p.s mean please please and please ? good

yeah ,his books on the list. tks for your all helps.

but which one is about index only ? or expert on index ?

can't see why this is belongs to Itzik Ben,  SQL Server MVP Deep Dives (Manning, 2009), Sample Chapter,  Source Code, it don't show his name on it.

"What's inside

•Topics important for SQL Server pros
•Accessible to readers of all levels
•New features of SQL Server 2008
"

this books seems not for SQL 2012/2014 and so on, right?

I have a look on another books: SQL Server MVP Deep Dives, Volume 2, do you know is it from the same author and I should buy that one as that one is newer ? http://www.amazon.com/SQL-Server-MVP-Deep-Dives/dp/1617290475/ref=sr_1_2?ie=UTF8&qid=1443329124&sr=8-2&keywords=SQL+Server+MVP+Deep+Dives

seems comment diff on both books. I am sure volume 2 is  an updated one or totally diff one.

so answer for this:

"this mean, the nature of this type of order, when it is important, is the column with unique value should be on the first read/part of the index?  so if C,A  and D, when compare together is our unique constraint, where B is still the 1,2, then index should be index (C,A,D,B) will be perfect? not index (B,C,A,D), right?"

is

"*) you design the index in this way that you reduce the necessary page reads to a minimum. This does not necessarily mean to select the first value when it is unique. Being unique in this case helps, cause we reduce the necessary read by applying our knowledge over that column. This no always possible."

?

how about:


"Having an index of (B, A) makes currently no difference due to the ascending sort order."

 I don't see sort in the query, why ascending sort order ? what is your sort about ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
ste5an,

"
 p.p.s. for the matter of optimizing: read Itzik Ben-Gan's books. "

I am sorry I have all these books on my Amazon list now, which one is focusing on the order of index?
0
ste5anSenior DeveloperCommented:
Those about query optimizing.
0
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, so no book is about optimizing existing index, right?
0
ste5anSenior DeveloperCommented:
Query optimizing === optimizing existing index.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok, got it, I will buy anyway.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.