Dear All,

I am trying to calculate the size of a cluster index using the "Estimating

the Size of a Clustered Index" section of Books online. Can some body tell me

what the below formula means

Calculate the number of pages in the index:

Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1

where 1 <= Level <= LevelsShri.DBA wrote:

> Dear All,

> I am trying to calculate the size of a cluster index using the "Estimating

> the Size of a Clustered Index" section of Books online. Can some body tell me

> what the below formula means

> Calculate the number of pages in the index:

> Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1

> where 1 <= Level <= Levels

>

for each value of variable 'level' between 1 and Levels (levels is

variable calculated earlier):

calculate (Index_Rows_Per_Page)^(Level â' 1)

sum all (Index_Rows_Per_Page)^(Level â' 1)

I used ^ to mark exponent

that means:

let's say we have 3 levels, Levels=3:

calculate:

- for level = 1: (Index_Rows_Per_Page)^(1 â' 1) =(Index_Rows_Per_Page)^0=1

- for level = 2: (Index_Rows_Per_Page)^(2 â' 1) =(Index_Rows_Per_Page)^1=Index_Rows_Per_Page

- for level = 3: (Index_Rows_Per_Page)^(3 â' 1) =(Index_Rows_Per_Page)^2=Index_Rows_Per_Page*Index_Rows_Per_Page

now, sum all these: 1 + Index_Rows_Per_Page +

Index_Rows_Per_Page*Index_Rows_Per_Page

OK?|||> Can some body tell me

> what the below formula means

The calculation means that the number of non-leaf node index pages required

for a clustered index is the sum of the number pages need at all levels.

The number of required pages at a given level is Index_Rows_Per_Page to the

power of level - 1. Below is the Transact-SQL equivalent.

DECLARE

@.Num_Index_Pages int,

@.Index_Rows_Per_Page int,

@.Level int

SELECT

@.Num_Index_Pages = 0,

@.Index_Rows_Per_Page = 10,

@.Level = 3 --init at number of levels

WHILE @.Level > 0

BEGIN

SET @.Level = @.Level - 1

SET @.Num_Index_Pages = @.Num_Index_Pages + POWER(@.Index_Rows_Per_Page,

@.Level)

END

SELECT @.Num_Index_Pages AS Num_Index_Pages

--

Hope this helps.

Dan Guzman

SQL Server MVP

"Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message

news:5634D0D1-7B1F-4D3A-8878-DE30960D7255@.microsoft.com...

> Dear All,

> I am trying to calculate the size of a cluster index using the "Estimating

> the Size of a Clustered Index" section of Books online. Can some body tell

> me

> what the below formula means

> Calculate the number of pages in the index:

> Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1

> where 1 <= Level <= Levels

>|||"Zarko Jovanovic" wrote:

> Shri.DBA wrote:

> > Dear All,

> >

> > I am trying to calculate the size of a cluster index using the "Estimating

> > the Size of a Clustered Index" section of Books online. Can some body tell me

> > what the below formula means

> >

> > Calculate the number of pages in the index:

> >

> > Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1

> >

> > where 1 <= Level <= Levels

> >

> >

> for each value of variable 'level' between 1 and Levels (levels is

> variable calculated earlier):

> calculate (Index_Rows_Per_Page)^(Level â' 1)

> sum all (Index_Rows_Per_Page)^(Level â' 1)

> I used ^ to mark exponent

> that means:

> let's say we have 3 levels, Levels=3:

> calculate:

> - for level = 1: (Index_Rows_Per_Page)^(1 â' 1) => (Index_Rows_Per_Page)^0=1

> - for level = 2: (Index_Rows_Per_Page)^(2 â' 1) => (Index_Rows_Per_Page)^1=Index_Rows_Per_Page

> - for level = 3: (Index_Rows_Per_Page)^(3 â' 1) => (Index_Rows_Per_Page)^2=Index_Rows_Per_Page*Index_Rows_Per_Page

> now, sum all these: 1 + Index_Rows_Per_Page +

> Index_Rows_Per_Page*Index_Rows_Per_Page

> OK?

>

----

Some how I am not conveniced with this formula for calculating the number of

index pages.

I have created below table and loaded 1000000 records. When I calculate the

number of index pages it gives me 387507 pages. I don't understand why so

many index pages are required to store clustered index key size of 4 bytes.

Regards

Balaji

USE [MyDB]

GO

/****** Object: Table [dbo].[Log] Script Date: 10/30/2007 17:39:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Log](

[LogID] [int] IDENTITY(1,1) NOT NULL,

[LogMessage] [varchar](50) NOT NULL,

[LogDateTime] [datetime] NOT NULL,

CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED

(

[LogID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF|||Balaji wrote:

> "Zarko Jovanovic" wrote:

>> Shri.DBA wrote:

>> Dear All,

>> I am trying to calculate the size of a cluster index using the "Estimating

>> the Size of a Clustered Index" section of Books online. Can some body tell me

>> what the below formula means

>> Calculate the number of pages in the index:

>> Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1

>> where 1 <= Level <= Levels

>>

>> for each value of variable 'level' between 1 and Levels (levels is

>> variable calculated earlier):

>> calculate (Index_Rows_Per_Page)^(Level â' 1)

>> sum all (Index_Rows_Per_Page)^(Level â' 1)

>> I used ^ to mark exponent

>> that means:

>> let's say we have 3 levels, Levels=3:

>> calculate:

>> - for level = 1: (Index_Rows_Per_Page)^(1 â' 1) =>> (Index_Rows_Per_Page)^0=1

>> - for level = 2: (Index_Rows_Per_Page)^(2 â' 1) =>> (Index_Rows_Per_Page)^1=Index_Rows_Per_Page

>> - for level = 3: (Index_Rows_Per_Page)^(3 â' 1) =>> (Index_Rows_Per_Page)^2=Index_Rows_Per_Page*Index_Rows_Per_Page

>> now, sum all these: 1 + Index_Rows_Per_Page +

>> Index_Rows_Per_Page*Index_Rows_Per_Page

>> OK?

> ----

> Some how I am not conveniced with this formula for calculating the number of

> index pages.

> I have created below table and loaded 1000000 records. When I calculate the

> number of index pages it gives me 387507 pages. I don't understand why so

> many index pages are required to store clustered index key size of 4 bytes.

> Regards

> Balaji

> USE [MyDB]

> GO

> /****** Object: Table [dbo].[Log] Script Date: 10/30/2007 17:39:04 ******/

> SET ANSI_NULLS ON

> GO

> SET QUOTED_IDENTIFIER ON

> GO

> SET ANSI_PADDING ON

> GO

> CREATE TABLE [dbo].[Log](

> [LogID] [int] IDENTITY(1,1) NOT NULL,

> [LogMessage] [varchar](50) NOT NULL,

> [LogDateTime] [datetime] NOT NULL,

> CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED

> (

> [LogID] ASC

> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

> ) ON [PRIMARY]

> GO

> SET ANSI_PADDING OFF

did you read whole article in BOL?|||Hi Dan Guzman,

Thanks very much for your help. I see there is a difference of 10-15% from

the calculated value and original value. Any thoughts.

Regards

Balaji

"Dan Guzman" wrote:

> > Can some body tell me

> > what the below formula means

> The calculation means that the number of non-leaf node index pages required

> for a clustered index is the sum of the number pages need at all levels.

> The number of required pages at a given level is Index_Rows_Per_Page to the

> power of level - 1. Below is the Transact-SQL equivalent.

>

> DECLARE

> @.Num_Index_Pages int,

> @.Index_Rows_Per_Page int,

> @.Level int

> SELECT

> @.Num_Index_Pages = 0,

> @.Index_Rows_Per_Page = 10,

> @.Level = 3 --init at number of levels

> WHILE @.Level > 0

> BEGIN

> SET @.Level = @.Level - 1

> SET @.Num_Index_Pages = @.Num_Index_Pages + POWER(@.Index_Rows_Per_Page,

> @.Level)

> END

> SELECT @.Num_Index_Pages AS Num_Index_Pages

> --

> Hope this helps.

> Dan Guzman

> SQL Server MVP

> "Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message

> news:5634D0D1-7B1F-4D3A-8878-DE30960D7255@.microsoft.com...

> > Dear All,

> >

> > I am trying to calculate the size of a cluster index using the "Estimating

> > the Size of a Clustered Index" section of Books online. Can some body tell

> > me

> > what the below formula means

> >

> > Calculate the number of pages in the index:

> >

> > Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1

> >

> > where 1 <= Level <= Levels

> >

> >

>|||> Thanks very much for your help. I see there is a difference of 10-15% from

> the calculated value and original value. Any thoughts.

What original value are you referring to?

--

Hope this helps.

Dan Guzman

SQL Server MVP

"Balaji" <Balaji@.discussions.microsoft.com> wrote in message

news:AA2312C8-5C72-43F3-A283-E4D7AE2EFE3A@.microsoft.com...

> Hi Dan Guzman,

> Thanks very much for your help. I see there is a difference of 10-15% from

> the calculated value and original value. Any thoughts.

> Regards

> Balaji

> "Dan Guzman" wrote:

>> > Can some body tell me

>> > what the below formula means

>> The calculation means that the number of non-leaf node index pages

>> required

>> for a clustered index is the sum of the number pages need at all levels.

>> The number of required pages at a given level is Index_Rows_Per_Page to

>> the

>> power of level - 1. Below is the Transact-SQL equivalent.

>>

>> DECLARE

>> @.Num_Index_Pages int,

>> @.Index_Rows_Per_Page int,

>> @.Level int

>> SELECT

>> @.Num_Index_Pages = 0,

>> @.Index_Rows_Per_Page = 10,

>> @.Level = 3 --init at number of levels

>> WHILE @.Level > 0

>> BEGIN

>> SET @.Level = @.Level - 1

>> SET @.Num_Index_Pages = @.Num_Index_Pages + POWER(@.Index_Rows_Per_Page,

>> @.Level)

>> END

>> SELECT @.Num_Index_Pages AS Num_Index_Pages

>> --

>> Hope this helps.

>> Dan Guzman

>> SQL Server MVP

>> "Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message

>> news:5634D0D1-7B1F-4D3A-8878-DE30960D7255@.microsoft.com...

>> > Dear All,

>> >

>> > I am trying to calculate the size of a cluster index using the

>> > "Estimating

>> > the Size of a Clustered Index" section of Books online. Can some body

>> > tell

>> > me

>> > what the below formula means

>> >

>> > Calculate the number of pages in the index:

>> >

>> > Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1

>> >

>> > where 1 <= Level <= Levels

>> >

>> >|||Hi Dan,

The original value I am referring to here is the result of sp_spaceused.

Regards

Balaji.T

"Dan Guzman" wrote:

> > Thanks very much for your help. I see there is a difference of 10-15% from

> > the calculated value and original value. Any thoughts.

> What original value are you referring to?

> --

> Hope this helps.

> Dan Guzman

> SQL Server MVP

> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message

> news:AA2312C8-5C72-43F3-A283-E4D7AE2EFE3A@.microsoft.com...

> > Hi Dan Guzman,

> >

> > Thanks very much for your help. I see there is a difference of 10-15% from

> > the calculated value and original value. Any thoughts.

> >

> > Regards

> > Balaji

> >

> > "Dan Guzman" wrote:

> >

> >> > Can some body tell me

> >> > what the below formula means

> >>

> >> The calculation means that the number of non-leaf node index pages

> >> required

> >> for a clustered index is the sum of the number pages need at all levels.

> >> The number of required pages at a given level is Index_Rows_Per_Page to

> >> the

> >> power of level - 1. Below is the Transact-SQL equivalent.

> >>

> >>

> >> DECLARE

> >> @.Num_Index_Pages int,

> >> @.Index_Rows_Per_Page int,

> >> @.Level int

> >> SELECT

> >> @.Num_Index_Pages = 0,

> >> @.Index_Rows_Per_Page = 10,

> >> @.Level = 3 --init at number of levels

> >> WHILE @.Level > 0

> >> BEGIN

> >> SET @.Level = @.Level - 1

> >> SET @.Num_Index_Pages = @.Num_Index_Pages + POWER(@.Index_Rows_Per_Page,

> >> @.Level)

> >> END

> >> SELECT @.Num_Index_Pages AS Num_Index_Pages

> >>

> >> --

> >> Hope this helps.

> >>

> >> Dan Guzman

> >> SQL Server MVP

> >>

> >> "Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message

> >> news:5634D0D1-7B1F-4D3A-8878-DE30960D7255@.microsoft.com...

> >> > Dear All,

> >> >

> >> > I am trying to calculate the size of a cluster index using the

> >> > "Estimating

> >> > the Size of a Clustered Index" section of Books online. Can some body

> >> > tell

> >> > me

> >> > what the below formula means

> >> >

> >> > Calculate the number of pages in the index:

> >> >

> >> > Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1

> >> >

> >> > where 1 <= Level <= Levels

> >> >

> >> >

> >>

>|||> The original value I am referring to here is the result of sp_spaceused.

Perhaps extra space is required due to fragmentation.

--

Hope this helps.

Dan Guzman

SQL Server MVP

"Balaji" <Balaji@.discussions.microsoft.com> wrote in message

news:CE6A90B3-E3D1-4CD1-A67F-B6DA9F78D754@.microsoft.com...

> Hi Dan,

> The original value I am referring to here is the result of sp_spaceused.

> Regards

> Balaji.T

> "Dan Guzman" wrote:

>> > Thanks very much for your help. I see there is a difference of 10-15%

>> > from

>> > the calculated value and original value. Any thoughts.

>> What original value are you referring to?

>> --

>> Hope this helps.

>> Dan Guzman

>> SQL Server MVP

>> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message

>> news:AA2312C8-5C72-43F3-A283-E4D7AE2EFE3A@.microsoft.com...

>> > Hi Dan Guzman,

>> >

>> > Thanks very much for your help. I see there is a difference of 10-15%

>> > from

>> > the calculated value and original value. Any thoughts.

>> >

>> > Regards

>> > Balaji

>> >

>> > "Dan Guzman" wrote:

>> >

>> >> > Can some body tell me

>> >> > what the below formula means

>> >>

>> >> The calculation means that the number of non-leaf node index pages

>> >> required

>> >> for a clustered index is the sum of the number pages need at all

>> >> levels.

>> >> The number of required pages at a given level is Index_Rows_Per_Page

>> >> to

>> >> the

>> >> power of level - 1. Below is the Transact-SQL equivalent.

>> >>

>> >>

>> >> DECLARE

>> >> @.Num_Index_Pages int,

>> >> @.Index_Rows_Per_Page int,

>> >> @.Level int

>> >> SELECT

>> >> @.Num_Index_Pages = 0,

>> >> @.Index_Rows_Per_Page = 10,

>> >> @.Level = 3 --init at number of levels

>> >> WHILE @.Level > 0

>> >> BEGIN

>> >> SET @.Level = @.Level - 1

>> >> SET @.Num_Index_Pages = @.Num_Index_Pages +

>> >> POWER(@.Index_Rows_Per_Page,

>> >> @.Level)

>> >> END

>> >> SELECT @.Num_Index_Pages AS Num_Index_Pages

>> >>

>> >> --

>> >> Hope this helps.

>> >>

>> >> Dan Guzman

>> >> SQL Server MVP

>> >>

>> >> "Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message

>> >> news:5634D0D1-7B1F-4D3A-8878-DE30960D7255@.microsoft.com...

>> >> > Dear All,

>> >> >

>> >> > I am trying to calculate the size of a cluster index using the

>> >> > "Estimating

>> >> > the Size of a Clustered Index" section of Books online. Can some

>> >> > body

>> >> > tell

>> >> > me

>> >> > what the below formula means

>> >> >

>> >> > Calculate the number of pages in the index:

>> >> >

>> >> > Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1

>> >> >

>> >> > where 1 <= Level <= Levels

>> >> >

>> >> >

>> >>|||Ok...thank you very much for the reply.

"Dan Guzman" wrote:

> > The original value I am referring to here is the result of sp_spaceused.

> Perhaps extra space is required due to fragmentation.

> --

> Hope this helps.

> Dan Guzman

> SQL Server MVP

> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message

> news:CE6A90B3-E3D1-4CD1-A67F-B6DA9F78D754@.microsoft.com...

> > Hi Dan,

> >

> > The original value I am referring to here is the result of sp_spaceused.

> >

> > Regards

> > Balaji.T

> >

> > "Dan Guzman" wrote:

> >

> >> > Thanks very much for your help. I see there is a difference of 10-15%

> >> > from

> >> > the calculated value and original value. Any thoughts.

> >>

> >> What original value are you referring to?

> >>

> >> --

> >> Hope this helps.

> >>

> >> Dan Guzman

> >> SQL Server MVP

> >>

> >> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message

> >> news:AA2312C8-5C72-43F3-A283-E4D7AE2EFE3A@.microsoft.com...

> >> > Hi Dan Guzman,

> >> >

> >> > Thanks very much for your help. I see there is a difference of 10-15%

> >> > from

> >> > the calculated value and original value. Any thoughts.

> >> >

> >> > Regards

> >> > Balaji

> >> >

> >> > "Dan Guzman" wrote:

> >> >

> >> >> > Can some body tell me

> >> >> > what the below formula means

> >> >>

> >> >> The calculation means that the number of non-leaf node index pages

> >> >> required

> >> >> for a clustered index is the sum of the number pages need at all

> >> >> levels.

> >> >> The number of required pages at a given level is Index_Rows_Per_Page

> >> >> to

> >> >> the

> >> >> power of level - 1. Below is the Transact-SQL equivalent.

> >> >>

> >> >>

> >> >> DECLARE

> >> >> @.Num_Index_Pages int,

> >> >> @.Index_Rows_Per_Page int,

> >> >> @.Level int

> >> >> SELECT

> >> >> @.Num_Index_Pages = 0,

> >> >> @.Index_Rows_Per_Page = 10,

> >> >> @.Level = 3 --init at number of levels

> >> >> WHILE @.Level > 0

> >> >> BEGIN

> >> >> SET @.Level = @.Level - 1

> >> >> SET @.Num_Index_Pages = @.Num_Index_Pages +

> >> >> POWER(@.Index_Rows_Per_Page,

> >> >> @.Level)

> >> >> END

> >> >> SELECT @.Num_Index_Pages AS Num_Index_Pages

> >> >>

> >> >> --

> >> >> Hope this helps.

> >> >>

> >> >> Dan Guzman

> >> >> SQL Server MVP

> >> >>

> >> >> "Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message

> >> >> news:5634D0D1-7B1F-4D3A-8878-DE30960D7255@.microsoft.com...

> >> >> > Dear All,

> >> >> >

> >> >> > I am trying to calculate the size of a cluster index using the

> >> >> > "Estimating

> >> >> > the Size of a Clustered Index" section of Books online. Can some

> >> >> > body

> >> >> > tell

> >> >> > me

> >> >> > what the below formula means

> >> >> >

> >> >> > Calculate the number of pages in the index:

> >> >> >

> >> >> > Num_Index_Pages = â'Level (Index_Rows_Per_Page)Level â' 1

> >> >> >

> >> >> > where 1 <= Level <= Levels

> >> >> >

> >> >> >

> >> >>

> >>

>

Subscribe to:
Post Comments (Atom)

## No comments:

## Post a Comment