find no.of pages and levels deep

Take the authors table as an example in the pubs database...
How can I find how many levels within the B-Tree from the root to the level
that contains the data rows (clustered index) ? Also how many pages at each
level within that B-Tree ?
Will i have 2 B-Trees now, one for the clustered index and one for the
non-clustered ? Using SQL 2000Hi,
Have a look into DBCC SHOWCONTIG command in books online.
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the
level
> that contains the data rows (clustered index) ? Also how many pages at
each
> level within that B-Tree ?
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
>|||Can your provide an example ? I did look at it but cant find any
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Have a look into DBCC SHOWCONTIG command in books online.
> Thanks
> Hari
> MCDBA
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > Take the authors table as an example in the pubs database...
> >
> > How can I find how many levels within the B-Tree from the root to the
> level
> > that contains the data rows (clustered index) ? Also how many pages at
> each
> > level within that B-Tree ?
> >
> > Will i have 2 B-Trees now, one for the clustered index and one for the
> > non-clustered ? Using SQL 2000
> >
> >
>|||Hi,
DBCC SHOWCONTIG will not give the exact infrmation you require. But it shows
information about the data and Index pages for a table.
THis gives you pages, extends for a table/index.
Infor from books online:-
Statistic Description
ObjectName Name of the table or view processed.
ObjectId ID of the object name.
IndexName Name of the index processed. IndexName is NULL for a heap.
IndexId ID of the index. IndexId is 0 for a heap.
Level Level of the index. Level 0 is the leaf (or data) level of the
index. The level number increases moving up the tree toward the index root.
Level is 0 for a heap.
Pages Number of pages comprising that level of the index or entire
heap.
Rows Number of data or index records at that level of the index. For a
heap, this is the number of data records in the entire heap.
MinimumRecordSize Minimum record size in that level of the index or
entire heap.
MaximumRecordSize Maximum record size in that level of the index or
entire heap.
AverageRecordSize Average record size in that level of the index or
entire heap.
ForwardedRecords Number of forwarded records in that level of the
index or entire heap.
Extents Number of extents in that level of the index or entire heap.
Usage :
DBCC SHOWCONTIG(tablene,[index_name])
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#2Pk8g7bEHA.2660@.tk2msftngp13.phx.gbl...
> Can your provide an example ? I did look at it but cant find any
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Have a look into DBCC SHOWCONTIG command in books online.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > > Take the authors table as an example in the pubs database...
> > >
> > > How can I find how many levels within the B-Tree from the root to the
> > level
> > > that contains the data rows (clustered index) ? Also how many pages at
> > each
> > > level within that B-Tree ?
> > >
> > > Will i have 2 B-Trees now, one for the clustered index and one for the
> > > non-clustered ? Using SQL 2000
> > >
> > >
> >
> >
>|||Hassan wrote:
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the level
> that contains the data rows (clustered index) ? Also how many pages at each
> level within that B-Tree ?
With the INDEXPROPERTY function. The data rows are always at the leaf
level of the clustered index.
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
Yes, each index has its own B-Tree.
Gert-Jan
--
(Please reply only to the newsgroup)