Estimating the Size of a Clustered Index

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
> >> >> >
> >> >> >
> >> >>
> >>
>

Estimating hardware requirements for a SQL Server 2005 installation

Is there any good online documentation describing rules of thumb for estimating and planning a SQL Server 2005 deployment in support of a Windows rich client/WCF services architecture?

TIA,

Ian
Well you do not want to hear that answer, but it depends.It′s like asking, How much gas will I need when I travel from NYC to Seattle. Well that depends on how much data / luggage will be transported, which transport device you use, how much data will be retrieved, how often you will query the database, how many users will access the database, etc. There are many parameters to consider.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql