June 8, 2011 at 1:27 am
Hi All,
Will clustered index help us to reduce logical IO cost? Since the data's are physically sorted, I know it should reduce the physical IO cost. Actually in our environment the server is installed with 12GB Ram. so the frequently used queries will be placed in Data cache. Right? Here, i did a small R&D on this and see some difference in the logical IO too. Yes, clustered index reduced the logical IO cost. Although I would like to hear some experts suggestions/inputs on this topic.
If there is any article which explain the above scenario, Please share it with me.
karthik
June 8, 2011 at 2:12 am
The clustered index does not force physical storage order of data.
All indexes, if they can be used, reduce the IO cost for queries. That's the whole point of an index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 8, 2011 at 2:40 am
GilaMonster (6/8/2011)
The clustered index does not force physical storage order of data.All indexes, if they can be used, reduce the IO cost for queries. That's the whole point of an index.
I know that I'm setting myself up here, but I thought that clustered indexes did force physical storage order. Quoting from BOL:
Before you create clustered indexes, understand how your data will be accessed. Consider using a clustered index for queries that do the following:
Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.
After the row with the first value is found by using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent.
--
Doesn't the 'physically adjacent' bit imply that the physical storage order is forced?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 8, 2011 at 2:53 am
Phil Parkin (6/8/2011)
GilaMonster (6/8/2011)
The clustered index does not force physical storage order of data.All indexes, if they can be used, reduce the IO cost for queries. That's the whole point of an index.
I know that I'm setting myself up here, but I thought that clustered indexes did force physical storage order. Quoting from BOL:
Before you create clustered indexes, understand how your data will be accessed. Consider using a clustered index for queries that do the following:
Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.
After the row with the first value is found by using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent.
--
Doesn't the 'physically adjacent' bit imply that the physical storage order is forced?
Books online is wrong on this point. Don't get me started, I HATE that entry. Need to go and speak with the doc people to get it corrected. (I touched on exactly that entry in a blog post from a few months ago: http://sqlinthewild.co.za/index.php/2011/02/01/is-a-clustered-index-best-for-range-queries/)
Indexes (all indexes) enforce the logical sort order of their leaf level (which for the cluster is the actual data pages), not the physical. When the index is built or rebuilt, SQL will try to put it into the file in physical order. It is in no way guaranteed to remain that way. If a page split occurs (insert into the middle of the index or a row increases in size), SQL needs to assign a new page and link it into the index structure. Unless there just happens to be a free page right next to the one splitting (unlikely), the new page will be allocated elsewhere and the page pointers on the pages updated to link it into the index chain. SQL does not reorganise the entire index just to put the new page physically adjacent to the old one. That would be incredibly inefficient.
Let's put it this way.
Logical fragmentation is defined as the % of pages where the logical order does not match the physical order. Hence, if the clustered index enforced the physical order of pages, a clustered index could never be fragmented. I think we can all agree that is not the case.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 8, 2011 at 3:23 am
Thanks for the blog link - I learnt something useful today.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 8, 2011 at 3:32 am
I am not still clear with logical IO part. could you explain it little bit more?
karthik
June 8, 2011 at 3:39 am
The entire point of an index is to reduce the amount of data that needs to be read to locate rows. Hence any suitable index will reduce the logical (and physical IO if the pages aren't in cache) for the query.
If an index doesn't reduce the logical IO then either it's a useless index or the query isn't using it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 8, 2011 at 5:56 am
The key to reducing I/O through an index, which Gail has detailed already, are designed explicitly to reduce I/O, is to build the right indexes. Simply putting indexes on the system is not enough. Those indexes have to be used. That means you need to examine the table strutures, your data and your queries to determine what the right indexes are for your system. References that can help you in this include SQL Server 2008 Internals by Kalen Delaney. I'd also suggest you check out my query tuning book, link below in the signature.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2011 at 2:00 pm
So Gail, to sum up:
(When and only when) we rebuild/reorganize the clustered index, we re-arrange the physical data pages on the disk. Correct?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply