January 7, 2011 at 2:09 am
AD7 (1/7/2011)
Thanks Gail. Brilliant article. Can't wait for part 2 and 3.
You don't have to wait for them, they're linked right there at the end of this one! 🙂
January 7, 2011 at 2:17 am
AD7 (1/7/2011)
Thanks Gail. Brilliant article. Can't wait for part 2 and 3.
They're available, see the links at the end of the article. These three were originally published Nov 2009. Steve's reposting them this month
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
January 7, 2011 at 2:56 am
My bad. 🙁
Sorry - I did not see the follow-up article links earlier . . . were a bit a sleep - just shows how good the article is that I've been able to learn from it while still a bit asleep ... 🙂
January 7, 2011 at 3:36 am
When an index scan is done by the query processor, it is always a full read of all of the leaf pages in the index, regardless of whether all of the rows are returned. It is never a partial scan.
Except in these cases, yes?
SELECT TOP 1 * FROM dbo.table
SELECT MAX(MyCIColumn) FROM dbo.table
January 7, 2011 at 3:52 am
Try them out with statistics IO on and compare the logical reads with the number of pages in the table.
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
January 7, 2011 at 4:07 am
GilaMonster (1/7/2011)
Try them out with statistics IO on and compare the logical reads with the number of pages in the table.
I just did that test and I got 2 reads for one and 3 for the other, which is certainly a lot less than the 108 in the table I tested against!
January 7, 2011 at 4:30 am
paul.knibbs (1/7/2011)
GilaMonster (1/7/2011)
Try them out with statistics IO on and compare the logical reads with the number of pages in the table.I just did that test and I got 2 reads for one and 3 for the other, which is certainly a lot less than the 108 in the table I tested against!
Ditto. Although I didn't really need to since they returned instantly from a multi-billion row table. The server is good - but not that good!
January 7, 2011 at 4:52 am
Ok, so that statement needs to be amended to 'In normal cases ...' I wasn't thinking about TOP when I wrote that. Do note though, max on a column that's not indexed will have to read everything, cause it can't just read to the last page in the 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
January 7, 2011 at 5:09 am
GilaMonster (1/7/2011)
Do note though, max on a column that's not indexed will have to read everything, cause it can't just read to the last page in the index.
Which is interesting in itself. It would make sense, and the logical IO suggests, that the physical op is actually a seek yet the plan shows a scan.
The scan for MIN and MAX is ordered and returns 4 reads for me. The TOP 1 is unordered (so used the IAM) and has an IO of 1. A seek on the CI also has an IO of 4.
The direction of scan is backward for MAX. So it traverses the B-Tree to find the last page and then conducts a single page scan. This is considered a scan and not a seek. About right?
January 7, 2011 at 5:39 am
It's not a seek because there's no predicate. A seek occurs when there's some defined value to search for. It's a backward scan that aborts after 1 page because it has what's needed
Top 1, if you have no order by and no where clause can be satisfied by reading any page from the index, so the quickest way is to use the IAM and find a page. Any page. Usually the first page in the index chain, but no guarantees of that.
Note that Ordered=false just means that the IAM can be used. Doesn't mean it was. Means that the plan does not need the data ordered by the clustered index key.
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
January 7, 2011 at 8:11 am
Very good info.
As a side note, the biggest problem I have had with index/query slowdowns is with extent fragmenation. While I've read that extent frag might not be all that important because sometimes it is not an accurate read depending on your disk setup, I have found it is critical for me. Even if logical frag, and can density are near perfect, extent frag is a killer. In that case, I need to do an index rebuild on the table...but I need to have about 40% free space in the database in order for this to work else the extent frag won't get lower than 35-40%...I've found with a 14GB table with 16GB indexes, anything less than 40% free space does not allow proper reordering of extents.
January 7, 2011 at 10:17 am
Gail,
Excellent article, I'm glad it was reprinted. 🙂
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
January 7, 2011 at 11:49 am
Question Guy (1/7/2011)
Very good info.As a side note, the biggest problem I have had with index/query slowdowns is with extent fragmenation. While I've read that extent frag might not be all that important because sometimes it is not an accurate read depending on your disk setup, I have found it is critical for me. Even if logical frag, and can density are near perfect, extent frag is a killer. In that case, I need to do an index rebuild on the table...but I need to have about 40% free space in the database in order for this to work else the extent frag won't get lower than 35-40%...I've found with a 14GB table with 16GB indexes, anything less than 40% free space does not allow proper reordering of extents.
have you thought about changing the clustered index on the tables? in the last few months i've set up a process to email daily reports about missing and unused indexes in our databases. on some of our older and larger tables we have over 10 indexes with a lot of those being composite with included columns and i have missing index reports telling me to create more. instead i went to QA and dropped all the indexes on those tables and started from scratch to see what is needed. and tried rebuilding the CI depending on what the missing index DMV's reported. in a few cases i'm even going against the book and trying to create CI's on non-unique columns because otherwise i have a query spiking the CPU by 10%
even had one table where we dropped a bunch of indexes earlier in the week. some big data change took place and a query suddenly started to use another index and ran a lot slower
January 7, 2011 at 12:21 pm
Excellent article Gail. The diagrams were very simple and easy to eyeball.
I have one question about the leaf level of non-clustered indexes. Are they a double linked list? I see from the diagrams that they link forward. Do they link backwards also?
Todd Fifield
January 7, 2011 at 12:39 pm
Thank you for the article Gail. Your technical explanations are outstanding - very straightforward. It amazes me when someone so advanced in a field can also get down to the more basic levels and still convey ideas so well. :smooooth:
p.s., Didn't realize until just now that this was a reprint. :blush: Oh well, thanks just the same. 🙂
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Viewing 15 posts - 76 through 90 (of 124 total)
You must be logged in to reply to this topic. Login to reply