Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

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 ?
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of marrowyung
marrowyung

ASKER

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 ?
hi,

can anyone help me on example about what is the correct column order when defining index ?
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).
"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.?
any update ste5an ?
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.
"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 ?
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?
Those about query optimizing.
yeah, so no book is about optimizing existing index, right?
Query optimizing === optimizing existing index.
ok, got it, I will buy anyway.