May 24, 2010 at 12:54 pm
Paul White NZ (5/24/2010)
Some people find Internals Viewer (an SSMS plug-in) easier for some tasks:
Oh no... I knew it 🙂
The Add-in 'InternalsViewer.SSMSAddIn.Connect' failed to load or caused an exception.
...
Error Message: The system cannot find the file specified.
Error number: 80070002
Happily, the plug-in is written on C#, so I can dig into its source code :Whistling:
May 24, 2010 at 1:01 pm
Might be the same issue as http://internalsviewer.codeplex.com/WorkItem/View.aspx?WorkItemId=5882. Make sure you download the correct version - for either SSMS 2005 or 2008.
Download choice page:http://internalsviewer.codeplex.com/releases/view/21139
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 24, 2010 at 1:59 pm
Paul White NZ (5/24/2010)
Might be the same issue as http://internalsviewer.codeplex.com/WorkItem/View.aspx?WorkItemId=5882. Make sure you download the correct version - for either SSMS 2005 or 2008.Download choice page:http://internalsviewer.codeplex.com/releases/view/21139
Thanks, this helped.
Nice plug-in. It's a little bit buggy ('Index was outside the bounds of the array' all the time, mess-up with the index types), it doesn't provide the functionality of 'DBCC PAGE (..., 3)', but it draws nice and easy-to-understand diagrams 🙂
May 24, 2010 at 2:24 pm
vk-kirov (5/24/2010)
Thanks, this helped.Nice plug-in. It's a little bit buggy ('Index was outside the bounds of the array' all the time, mess-up with the index types), it doesn't provide the functionality of 'DBCC PAGE (..., 3)', but it draws nice and easy-to-understand diagrams 🙂
It's *very* buggy 😀
Some people like it. I think it's better than nothing, and there's clearly a lot of work gone into the C# code.
It's open source, so feel free to improve it!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 27, 2010 at 10:28 am
A quick search on Microsft's website:
http://msdn.microsoft.com/en-us/library/ms175049.aspx
This clearly gives 8, which wasbn't even an option!
Not my favourite question, although it did make me think a bit more about indexes I suppose...
May 27, 2010 at 10:32 am
jts_2003 (5/27/2010)
A quick search on Microsft's website:http://msdn.microsoft.com/en-us/library/ms175049.aspx
This clearly gives 8, which wasbn't even an option!
Not my favourite question, although it did make me think a bit more about indexes I suppose...
That lists the different 'types' of indexes. The question was about the structures. Several of these index types use the same structure. So it was a bit tricky.
May 27, 2010 at 10:33 am
jts_2003 (5/27/2010)
A quick search on Microsft's website:http://msdn.microsoft.com/en-us/library/ms175049.aspx
This clearly gives 8, which wasbn't even an option!
Not my favourite question, although it did make me think a bit more about indexes I suppose...
If only the question had been about index types!
As it was, it was about types of index structures.
Several people made the same error.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 27, 2010 at 12:18 pm
Paul White NZ (5/27/2010)
jts_2003 (5/27/2010)
A quick search on Microsft's website:http://msdn.microsoft.com/en-us/library/ms175049.aspx
This clearly gives 8, which wasbn't even an option!
Not my favourite question, although it did make me think a bit more about indexes I suppose...
If only the question had been about index types!
As it was, it was about types of index structures.
Several people made the same error.
One of the things that I'm "loving" is how MS changes their online documentation. For instance, that referenced link is for SQL 2008 R2. If you look at the SQL 2008 version, there's an additional type of index in this list. Since this link shows 9, that is why I had an option for 9 in the QotD (R2 wasn't released yet when I researched and wrote this.) The SQL 2005 link shows 7 (though the QotD did specifically mention SQL 2008!)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 27, 2010 at 12:21 pm
Paul White NZ (5/27/2010)
jts_2003 (5/27/2010)
A quick search on Microsft's website:http://msdn.microsoft.com/en-us/library/ms175049.aspx
This clearly gives 8, which wasbn't even an option!
Not my favourite question, although it did make me think a bit more about indexes I suppose...
If only the question had been about index types!
As it was, it was about types of index structures.
Several people made the same error.
Okay, I'm curious. What could I have done better to make it clearer that this question was about the different types of index structures, and not the different types of indexes? Should I have specifically said (NOT index TYPES, but types of index STRUCTURES)?
I did not want this to be a confusing question, and I didn't think it was, yet many people missed this and have complained about it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 27, 2010 at 1:03 pm
Paul White NZ (5/24/2010)A full-text index is the same, storage-wise, as any other non-clustered index. The parent object has an entry in sys.internal_tables, and the DBCC commands can be used to verify that it is a B-plus tree, with the same structure as any other non-clustered index. Ok, so a good fraction of the data stored within the index structure might be opaque to everything except the full-text engine, but that does not mean that the index structure is different - just how it is used.
Reference: Microsoft SQL Server 2008 Internals - K.Delaney et al.
Paul
Question for you Paul:
It is stated in BOL:
Instead of constructing a B-tree structure based on a value stored in a particular row, the Full-Text Engine builds an inverted, stacked, compressed index structure based on individual tokens from the text being indexed.
Now, I'm not about to question Kalen, but where MS is stating so specifically that it's not a B-tree, what gives here?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 27, 2010 at 3:26 pm
At the last PASS summit, I had a chance to talk for about an hour at breakfast with a man from Microsoft who is part of the BOL team. According to him, so many new things are coming out so fast that just keeping the documentation up to date is a real challenge.
I thought that the question was a good one, and the discussion even better. (I got it wrong because of my interpretation, but so what?) A lot of information about index structures and index types came out in the discussion.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 27, 2010 at 3:38 pm
The Dixie Flatline (5/27/2010)
At the last PASS summit, I had a chance to talk for about an hour at breakfast with a man from Microsoft who is part of the BOL team. According to him, so many new things are coming out so fast that just keeping the documentation up to date is a real challenge.I thought that the question was a good one, and the discussion even better. (I got it wrong because of my interpretation, but so what?) A lot of information about index structures and index types came out in the discussion.
Thanks.
One thing that I've noticed is that MS doesn't annotate when the pages change. So, in the case of this QotD, several references have changed since I originally submitted the question. I've also noticed that BOL doesn't have links to previous versions like they used to anymore.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 27, 2010 at 8:46 pm
WayneS (5/27/2010)
Question for you Paul:It is stated in BOL:
Instead of constructing a B-tree structure based on a value stored in a particular row, the Full-Text Engine builds an inverted, stacked, compressed index structure based on individual tokens from the text being indexed.
Now, I'm not about to question Kalen, but where MS is stating so specifically that it's not a B-tree, what gives here?
Hey Wayne,
This is just wording again - and it is confusing. My guess is that the intention of the writer was to show that full-text indexes have a different internal arrangement from 'ordinary' indexes. The distinction is between a B+ tree based on a value stored in a particular row, and a B+ tree built on an inverted, stacked, compressed structure build on parsed tokens.
If you have Kalen's 2008 Internals book, the relevant sections are on pages 345-346. A key part (talking about FT indexes) is "...their space usage is tracked in exactly the same way (using IAM pages) and their structures are the same as regular indexes."
As I keep saying, though, whether you consider full-text indexes and 'regular' indexes to have the same structure or not depends a lot on what level of structure we are talking about.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 1, 2010 at 8:40 pm
Great questions thanks!
I got it correct, but not for the right reason. 🙂
June 11, 2010 at 9:59 pm
Another question with an arbitrary, not-well explained answer. The actual index structures are exactly the same for all index types except full-text indexes. So how is 4 the correct answer? And I know index structures inside and out...
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply