Estimating time for creation of clustered index

I asked my SQL Server to create me a clustered index on a 200,000ish
row table. It's taking rather longer than I expected. But, I don't
really know how long I should have expected it to take. How can I
estimate that?
Thanks!
IonDo you have a backup of the database? A pretty reliable way to see how this
will impact your production system is to restore it (on another server,
another instance, or on the same instance with a different database name),
and create the clustered index on the copy. It will be slightly affected by
factors such as different hardware and different activity, but unless the
situation is extreme, it should be within an order of magnitude.
I have not seen anything that resembles a formula for predicting how long a
clustered index will take, without doing any actual work. There are so many
variables involved, I think it will be every difficult to approach anything
even remotely trustworthy.
<ionFreeman@.gmail.com> wrote in message
news:1142528571.960565.264770@.j33g2000cwa.googlegroups.com...
>I asked my SQL Server to create me a clustered index on a 200,000ish
> row table. It's taking rather longer than I expected. But, I don't
> really know how long I should have expected it to take. How can I
> estimate that?
> Thanks!
> Ion
>

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

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicate
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A'--Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?
Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archive/2006/07/07/652668.aspx
..
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?
|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the differences
> between Seek Predicate and Predicate?
sql

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicate
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A' --Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archive/2006/07/07/652668.aspx
.
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the differences
> between Seek Predicate and Predicate?

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicat
e
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A' --Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archi.../07/652668.aspx
.
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predic
ate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the difference
s
> between Seek Predicate and Predicate?

Estimate DB Size: how to calculate clustered and non-clustered ind

I am doing an estimation on db size based on this link:
http://msdn.microsoft.com/library/en...asp?frame=true
How can I check which tables have clustered index and non-clustered index
fastly?
Thank you!
In addition, noting the sentence "Total Table Size = Data Space Used +
Nonclustered Index + Clustered Index + ...n"
What are the other "...n" factors and how significant (can measure to %?)
are these factors affecting the result?
Many thanks!
|||Get the script I posted at the following link.
http://www.sqlservercentral.com/colu...xdetection.asp
"Jackie Chow" wrote:

> I am doing an estimation on db size based on this link:
> http://msdn.microsoft.com/library/en...asp?frame=true
> How can I check which tables have clustered index and non-clustered index
> fastly?
> Thank you!
|||Check for fragmentation using DBCC SHOWCONTIG.
I will send you a document explaining the use and the Excel Document.
Regards
Andre
"Jackie Chow" wrote:

> I am doing an estimation on db size based on this link:
> http://msdn.microsoft.com/library/en...asp?frame=true
> How can I check which tables have clustered index and non-clustered index
> fastly?
> Thank you!