Page Numbers in Excel: splits

Showing posts with label splits. Show all posts

Saturday, February 25, 2012

page splits/ fragmentation

Hi,
I am inserting data into a table in what I believe is the correct way. The
table is clustered on the date and I am inserting data that 90% of the time
comes in the correct order, ie today I am inserting 2006/04/27 but 10% may b
e
from 2005 or 2004. I think I need a clustered index on the date cause I am
comparing series of dates together.
From the below...
DBCC SHOWCONTIG scanning 'staging_daily_edf_history' table...
Table: 'staging_daily_edf_history' (199671759); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 96488
- Extents Scanned.......................: 12157
- Extent Switches.......................: 12707
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 94.91% [12061:12708]
- Logical Scan Fragmentation ..............: 1.87%
- Extent Scan Fragmentation ...............: 1.49%
- Avg. Bytes Free per Page................: 134.5
- Avg. Page Density (full)................: 98.34%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I believe that my table is not really fragmented. Does anyone else think the
opposite. It worries me though that the insertion become slower and slower a
s
the table icreases in size maybe because of page splits. Any ideas what's th
e
best strategy? should I have my table non clustered instead? Thanks.
Panos.The results show that it is not very fragmented at all and I doubt that page
splits on the clustered index are the issue here. You can also try setting
your fill factor to 90% to remove most of any page splits you may get. How
many non-clustered indexes on this table do you have? Can you give more
details on how you are inserting and how you determine they get slower?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:18D591DA-BCA2-44D8-984A-DE89A4CD5374@.microsoft.com...
> Hi,
> I am inserting data into a table in what I believe is the correct way. The
> table is clustered on the date and I am inserting data that 90% of the
> time
> comes in the correct order, ie today I am inserting 2006/04/27 but 10% may
> be
> from 2005 or 2004. I think I need a clustered index on the date cause I am
> comparing series of dates together.
> From the below...
> DBCC SHOWCONTIG scanning 'staging_daily_edf_history' table...
> Table: 'staging_daily_edf_history' (199671759); index ID: 1, database ID:
> 5
> TABLE level scan performed.
> - Pages Scanned........................: 96488
> - Extents Scanned.......................: 12157
> - Extent Switches.......................: 12707
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 94.91% [12061:12708]
> - Logical Scan Fragmentation ..............: 1.87%
> - Extent Scan Fragmentation ...............: 1.49%
> - Avg. Bytes Free per Page................: 134.5
> - Avg. Page Density (full)................: 98.34%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I believe that my table is not really fragmented. Does anyone else think
> the
> opposite. It worries me though that the insertion become slower and slower
> as
> the table icreases in size maybe because of page splits. Any ideas what's
> the
> best strategy? should I have my table non clustered instead? Thanks.
> Panos.|||Hi,
Well there are 2 more indexes. I guess that must be the problem updating the
non clustered indexes. The way I can see it's slow is because I have a recor
d
on the time the file was processed (insertion via iiss) and can see that it
just takes longer and longer. I guess this is what I should expect when I
have 8m rows and 3 indexes.
Cheers,
Panos.
"Andrew J. Kelly" wrote:

> The results show that it is not very fragmented at all and I doubt that pa
ge
> splits on the clustered index are the issue here. You can also try settin
g
> your fill factor to 90% to remove most of any page splits you may get. Ho
w
> many non-clustered indexes on this table do you have? Can you give more
> details on how you are inserting and how you determine they get slower?
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:18D591DA-BCA2-44D8-984A-DE89A4CD5374@.microsoft.com...
>
>|||8M rows and 3 indexes should not slow down the inserts to a degree that
should be easily detectable. Maybe it is the way in which you are doing the
inserts. Can you post the actual code used for the inserts?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:1BA642CC-5545-4B7F-B44A-056CEEB17004@.microsoft.com...
> Hi,
> Well there are 2 more indexes. I guess that must be the problem updating
> the
> non clustered indexes. The way I can see it's slow is because I have a
> record
> on the time the file was processed (insertion via iiss) and can see that
> it
> just takes longer and longer. I guess this is what I should expect when I
> have 8m rows and 3 indexes.
> Cheers,
> Panos.
>
> "Andrew J. Kelly" wrote:
>|||it is a straight forward insert into table, no conditions or anything, can't
be simpler. It doesn't slow down too much but you can still notice. Well the
actual stats is avg insertion 10secs for the whole process at the beginning
and then it goes to avg 26 secs in the end.
Thanks.
"Andrew J. Kelly" wrote:

> 8M rows and 3 indexes should not slow down the inserts to a degree that
> should be easily detectable. Maybe it is the way in which you are doing th
e
> inserts. Can you post the actual code used for the inserts?
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:1BA642CC-5545-4B7F-B44A-056CEEB17004@.microsoft.com...
>
>|||10 Seconds for an insert? So this isn't a simple insert then, there must be
more to it. Something you might not think is important may in fact be so
the more details you provide the better chance you will get a proper answer.
Are you sure there are no triggers on the table?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:4F99553F-EEEA-4D19-88E0-CC34B121176B@.microsoft.com...
> it is a straight forward insert into table, no conditions or anything,
> can't
> be simpler. It doesn't slow down too much but you can still notice. Well
> the
> actual stats is avg insertion 10secs for the whole process at the
> beginning
> and then it goes to avg 26 secs in the end.
> Thanks.
> "Andrew J. Kelly" wrote:
>|||I am talking about 20-30,000 rows insertions. Although now it seems to take
longer, more than 2 min. I think it's probably fine. There are no triggers b
y
the way on the table. You think that few odd rows which don't come in order
will not cause a massive problem. Ill also change the fill factor and see if
that's better. Appreciate your help.
Panos.
"Andrew J. Kelly" wrote:

> 10 Seconds for an insert? So this isn't a simple insert then, there must
be
> more to it. Something you might not think is important may in fact be so
> the more details you provide the better chance you will get a proper answe
r.
> Are you sure there are no triggers on the table?
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:4F99553F-EEEA-4D19-88E0-CC34B121176B@.microsoft.com...
>
>|||You still are not providing any real details as to exactly how you do these
inserts and that makes it very hard to give any solid advice. In my opinion
that is still too long for just 20 or 30K rows. Have you checked for
blocking? What about your disk queues? Is your log file on a separate Raid
1 from any of the data files?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:CE4272A5-1F5B-4CB4-89A0-38CA5BDCC3F0@.microsoft.com...
>I am talking about 20-30,000 rows insertions. Although now it seems to take
> longer, more than 2 min. I think it's probably fine. There are no triggers
> by
> the way on the table. You think that few odd rows which don't come in
> order
> will not cause a massive problem. Ill also change the fill factor and see
> if
> that's better. Appreciate your help.
> Panos.
> "Andrew J. Kelly" wrote:
>

Page splits/ Dirty pages/ Checkpoint

We have few tables in our application. Basically they are used as temporary
tables. Each day they start with no records in it(empty) and as the day goes
they are filled with the data and at the end of the day they will be
truncated to get ready for the next day. We have a high transaction rate
about 4000/sec. When I noticed the page splits /sec counter it is showing
about 130-160 per second. This is driving the checkpoint to take longer time
.
How Can I reduce this high page splits.
Another question is, we have a char(15) column in those tables and that
column is indexed. It is an Id column but is not unique. Each record has an
unique number Id(generated by our app). But we need to seacrh on the CHAR Id
column so indexed on it. This index is creating/making lot of dirty pages.
This also is a contributing reason for the checkpoint to take longer. How ca
n
I make changes to the index so that it would not create/make many pages
dirty? I tried to change it to VARCHAR and there is not much difference.
The check point is taking about 10-15 seconds and it repeats every 60 second
s.
Your suggestion is greatly appreciated.
Thanks.
Thanks.Just give a try with the following info
1. Check the recovery interval option on the system.
2. make sure that the temporary tables have fixed size by using char
rather than
varchar therefore you can reduce the page spilts.
HTH
Regards
Rajesh Peddireddy.
"Srini" wrote:

> We have few tables in our application. Basically they are used as temporar
y
> tables. Each day they start with no records in it(empty) and as the day go
es
> they are filled with the data and at the end of the day they will be
> truncated to get ready for the next day. We have a high transaction rate
> about 4000/sec. When I noticed the page splits /sec counter it is showing
> about 130-160 per second. This is driving the checkpoint to take longer ti
me.
> How Can I reduce this high page splits.
> Another question is, we have a char(15) column in those tables and that
> column is indexed. It is an Id column but is not unique. Each record has a
n
> unique number Id(generated by our app). But we need to seacrh on the CHAR
Id
> column so indexed on it. This index is creating/making lot of dirty pages.
> This also is a contributing reason for the checkpoint to take longer. How
can
> I make changes to the index so that it would not create/make many pages
> dirty? I tried to change it to VARCHAR and there is not much difference.
> The check point is taking about 10-15 seconds and it repeats every 60 seco
nds.
> Your suggestion is greatly appreciated.
> Thanks.
> Thanks.|||It would really help to show the entire DDL for the table including the
indexes. It sounds like your disk subsystem isn't up to the task. If you
are going to have that many transactions you need a fast disk I/O subsystem,
especially for the transaction logs. Is the log file on it's own RAID 1 or
RAID 10 and is the data on a RAID 10?
Andrew J. Kelly SQL MVP
"Srini" <Srini@.discussions.microsoft.com> wrote in message
news:1F6FF490-C19A-4DFE-BDBC-3AD397CC9D5A@.microsoft.com...
> We have few tables in our application. Basically they are used as
> temporary
> tables. Each day they start with no records in it(empty) and as the day
> goes
> they are filled with the data and at the end of the day they will be
> truncated to get ready for the next day. We have a high transaction rate
> about 4000/sec. When I noticed the page splits /sec counter it is showing
> about 130-160 per second. This is driving the checkpoint to take longer
> time.
> How Can I reduce this high page splits.
> Another question is, we have a char(15) column in those tables and that
> column is indexed. It is an Id column but is not unique. Each record has
> an
> unique number Id(generated by our app). But we need to seacrh on the CHAR
> Id
> column so indexed on it. This index is creating/making lot of dirty pages.
> This also is a contributing reason for the checkpoint to take longer. How
> can
> I make changes to the index so that it would not create/make many pages
> dirty? I tried to change it to VARCHAR and there is not much difference.
> The check point is taking about 10-15 seconds and it repeats every 60
> seconds.
> Your suggestion is greatly appreciated.
> Thanks.
> Thanks.|||We have SAN disk system. Probably the HW is good enough, just trying to see
if I can rearrange some things on the database front to make some improvemen
t.
Coming to the DDL, the tables are not temporary tables the data is temporary
in the sense that the data is kept only for the current day and at the end o
f
the day they are truncated. Each table has about 20 columns. some are decima
l
fields some are datatime columns and others are integer and char type which
includes many char(1)'s and two/three columns char(15 to 20)). One of the
char(15) is indexed which is a kind of Id but is not unique there are no
relationships between these tables and other tables. No triggers no views an
d
anything as such. The data comes into to the system gets inserted to these
standalone tables using some stored procedures. And these tables are queried
using some other stored procedures. The major problem to me looks like is
because of the page splits that it is generating and the dirty pages that it
is generating(about 10000 dirty pages). Is there any thing that can be done
on the table or anything else to make things perform better?
Thanks in advance for your suggestion.
"Andrew J. Kelly" wrote:

> It would really help to show the entire DDL for the table including the
> indexes. It sounds like your disk subsystem isn't up to the task. If you
> are going to have that many transactions you need a fast disk I/O subsyste
m,
> especially for the transaction logs. Is the log file on it's own RAID 1 o
r
> RAID 10 and is the data on a RAID 10?
> --
> Andrew J. Kelly SQL MVP
>
> "Srini" <Srini@.discussions.microsoft.com> wrote in message
> news:1F6FF490-C19A-4DFE-BDBC-3AD397CC9D5A@.microsoft.com...
>
>|||Srini wrote:
> We have SAN disk system. Probably the HW is good enough, just trying
> to see if I can rearrange some things on the database front to make
> some improvement.
> Coming to the DDL, the tables are not temporary tables the data is
> temporary in the sense that the data is kept only for the current day
> and at the end of the day they are truncated. Each table has about 20
> columns. some are decimal fields some are datatime columns and others
> are integer and char type which includes many char(1)'s and two/three
> columns char(15 to 20)). One of the char(15) is indexed which is a
> kind of Id but is not unique there are no relationships between these
> tables and other tables. No triggers no views and anything as such.
> The data comes into to the system gets inserted to these standalone
> tables using some stored procedures. And these tables are queried
> using some other stored procedures. The major problem to me looks
> like is because of the page splits that it is generating and the
> dirty pages that it is generating(about 10000 dirty pages). Is there
> any thing that can be done on the table or anything else to make
> things perform better?
>
The reason it would help to see the DDL is because page splits are a
result of a clustered index and inserts that are not in clustered index
order. You can eliminate the page splitting by chaning the clustered
index to non-clustered or inserting the data in clustered index key
order (if that's possible).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Just because it is a SAN does not mean it is sufficient or configured
properly for your application. I run across more issues SAN related simply
because people tend to ignore the configuration in thinking it can handle
what ever they need. The DDL was to see what we are dealing with and leave
nothing to imagination. It only takes a second to script the table and
indexes but it goes a long way towards letting us see what is actually
there. Not just what you may think is relevant. This is especially true for
the indexes.
Andrew J. Kelly SQL MVP
"Srini" <Srini@.discussions.microsoft.com> wrote in message
news:623BAACD-7754-4BF4-B9ED-5AA2EDC16F8B@.microsoft.com...
> We have SAN disk system. Probably the HW is good enough, just trying to
> see
> if I can rearrange some things on the database front to make some
> improvement.
> Coming to the DDL, the tables are not temporary tables the data is
> temporary
> in the sense that the data is kept only for the current day and at the end
> of
> the day they are truncated. Each table has about 20 columns. some are
> decimal
> fields some are datatime columns and others are integer and char type
> which
> includes many char(1)'s and two/three columns char(15 to 20)). One of the
> char(15) is indexed which is a kind of Id but is not unique there are no
> relationships between these tables and other tables. No triggers no views
> and
> anything as such. The data comes into to the system gets inserted to these
> standalone tables using some stored procedures. And these tables are
> queried
> using some other stored procedures. The major problem to me looks like is
> because of the page splits that it is generating and the dirty pages that
> it
> is generating(about 10000 dirty pages). Is there any thing that can be
> done
> on the table or anything else to make things perform better?
> Thanks in advance for your suggestion.
> "Andrew J. Kelly" wrote:
>|||David is correct but I just want to caution that changing the clustered
index to a nonclustered will not remove page splits. It may reduce them but
a nonclustered index is implemented just like a clustered index and can page
split as well.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eC3cUzslFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Srini wrote:
> The reason it would help to see the DDL is because page splits are a
> result of a clustered index and inserts that are not in clustered index
> order. You can eliminate the page splitting by chaning the clustered index
> to non-clustered or inserting the data in clustered index key order (if
> that's possible).
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||If we try to insert the data in clustered index order, which sounds to me
like a monotonically increasing clustered index, would n't it create hot
spots on the disk there by reducing the throughput? Currently we can't
control the data insert order. But I think I can change it so that I can
create clustered index on the serial number which is sequential and that is
generated by me so the data insertions will be in the clustered index order.
How about the dirty pages created by the other non clustered indexes? When I
run DBCC MEMUSAGE it is showing lot of dirty pages on the pages related to
the non-clustered indexes. How can I reduce the dirty pages on those? I
understand FILLFACTOR will not help here as that option is useful when there
is some data in the table and we are creating indexes on that table.
Thanks.
"David Gugick" wrote:

> Srini wrote:
> The reason it would help to see the DDL is because page splits are a
> result of a clustered index and inserts that are not in clustered index
> order. You can eliminate the page splitting by chaning the clustered
> index to non-clustered or inserting the data in clustered index key
> order (if that's possible).
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Is there any limit on the number of repplies that one can post in a time
frame... This thing is not letting me post mine. Trying to POST again...
I agree SAN may have some issues we are trying to check on that. But by just
looking at the SQL server front 10000 dirty pages per checkpoint, using
default recovery interval(which is 60 seconds), looks like something can be
done there to reduce that huge number of dirty pages.
DDL looks like this:
SET ANSI_PADDING ON
GO
CREATE TABLE
[dbo].[DAILY_DATA1](
[Event_id] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
--This is unique identifier for the data, currently clustered index is
created on this field
[Category_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Session_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Serial_nb] [int] NOT NULL , App generated serial number, which I can use to
create clustered index
[Order_ts] [datetime] NOT NULL ,
[Data_Id_tx] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
--This is the column that we have index on
.
.
.
[Description_tx] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
.
.
.
[Receipt_ts] [datetime] NOT NULL
CONSTRAINT [PK_DD_1_Evt_ID] PRIMARY KEY CLUSTERED
(
[Event_id]
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
CREATE INDEX [IDX_DD_1_Data_Id_tx] ON [dbo].[Daily_Data1]([Data_Id_tx])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
Thanks.
"Andrew J. Kelly" wrote:

> Just because it is a SAN does not mean it is sufficient or configured
> properly for your application. I run across more issues SAN related simply
> because people tend to ignore the configuration in thinking it can handle
> what ever they need. The DDL was to see what we are dealing with and leav
e
> nothing to imagination. It only takes a second to script the table and
> indexes but it goes a long way towards letting us see what is actually
> there. Not just what you may think is relevant. This is especially true f
or
> the indexes.
> --
> Andrew J. Kelly SQL MVP
>
> "Srini" <Srini@.discussions.microsoft.com> wrote in message
> news:623BAACD-7754-4BF4-B9ED-5AA2EDC16F8B@.microsoft.com...
>
>|||I repplied to thsi in the morning but it did not get posted...
Interval option is set to default - not changed. Default is 60 seconds. If I
increase it, it is taking way long on the checkpoint. If I decrease it
CHCKPOINT occurs too frequently. Both are problematic.
The tables are not temporary but the data is. Data gets inserted as part of
the daily operations and will be truncated in the evening. All the columns
are set to fixed length CHAR fields(to their maximum possible lenghts). Ther
e
are only two/thress columns with CHAR(15), CHAR(18) and CHAR(10) all other
columns are integer, decimal, datetime, CHAR(1) type.
I need to find a way to reduce the number of dirty pages and the number of
page splits. How can I do that?
Thanks.
"Rajesh" wrote:
> Just give a try with the following info
> 1. Check the recovery interval option on the system.
> 2. make sure that the temporary tables have fixed size by using char
> rather than
> varchar therefore you can reduce the page spilts.
> HTH
> Regards
> Rajesh Peddireddy.
> "Srini" wrote:
>

page splits on a clustered identity column ?

Hi all,
we can read in many articles that page splits don't occur with a cluster on
a monotone increasing column (like an identity column) .
with this script, we don't see that : with a clustered idendity column,
the performance monitor shows page splits ( "SQLServer:AccessMethods" ; "Page
Splits/sec") , and their level stays quite stable if the index is rebuilt
with lower fillfactor for new insertions.
with a clustered varchar column, the page splits is higher at the beginning
but decrease with lower fillfactor (page splits are avoided when fillfactor = 40) : normal behavior.
how to explain the page splits with the clustered identity column ?
how to measure the number of page splits (an not a ratio per second)
occuring during an execution ?
thanks for reading my poor english and your replays,
R.Fauchatre
PS : tests on SQL Server 2000 SP4 Developer Edition (one user only)
==================================== Script
==================================== --
============================================================================= -- database creatio
--=============================================================================
USE master
IF (SELECT name FROM sysdatabases WHERE name='TEST_INDEX' ) IS NULL CREATE
DATABASE TEST_INDEX
go
USE TEST_INDEX
-- ======================================================== -- fill procedures
-- ========================================================
-- ----
-- string generation
-- ----
print 'procÃ©dures creation'
if OBJECT_ID('generate_string') is not null DROP PROC generate_string
go
CREATE PROCEDURE generate_string
@.string varchar(20) OUTPUT
AS
BEGIN
DECLARE @.limit int
DECLARE @.curr_iteration int
SELECT @.limit = round((rand() * 20) + 3, 0)
SELECT @.curr_iteration = 0
SELECT @.string = ''
WHILE @.curr_iteration < @.limit
BEGIN
SELECT @.string = @.string + char(round((rand() * 25) + 1, 0) + 64)
SELECT @.curr_iteration = @.curr_iteration + 1
END
IF SUBSTRING(@.string,1,1) = ' '
BEGIN
SELECT @.string = SUBSTRING(@.string,2,16)
END
END
go
-- ----
-- filling the table (10000 rows)
-- ----
if OBJECT_ID('FillTable') is not null DROP PROC FillTable
go
CREATE PROC FillTable (@.Chaine bit = 0)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.string varchar(20)
DECLARE @.Compteur int
SET @.compteur = 0
WHILE @.compteur < 10000
BEGIN
SET @.compteur = @.compteur + 1
IF @.chaine = 1
BEGIN
EXEC generate_string @.string output
SET @.string = @.string + '_' + CAST(@.compteur as varchar(5))
INSERT dbo.DemoCluster (col2) values (@.string)
END
ELSE
BEGIN
INSERT dbo.DemoCluster DEFAULT VALUES
END
END
END
go
-- ======================================================== -- Test1 : clustering on an identity coumn
-- ========================================================
print 'table creation : test with clustered identity column'
IF OBJECTPROPERTY(object_id('dbo.DemoCluster'), 'IsUserTable') = 1
DROP TABLE dbo.DemoCluster
go
CREATE TABLE dbo.DemoCluster
(
col1 int identity CONSTRAINT PK_DemoClusterCol1 PRIMARY KEY ,
col2 varchar(20) CONSTRAINT DemoClusterCol2Default
DEFAULT current_timestamp,
col3 datetime CONSTRAINT DemoClusterCol3Default
DEFAULT getdate(),
col4 char(30) CONSTRAINT DemoClusterCol4Default
DEFAULT suser_name(),
col5 char(30) CONSTRAINT DemoClusterCol5Default
DEFAULT user_name(),
col6 char(100) CONSTRAINT DemoClusterCol6Default
DEFAULT 'valeur longue longue longue longue longue longue longue
longue longue longue longue longue longue ',
col7 varchar(200) CONSTRAINT DemoClusterCol7Default
DEFAULT 'valeur compacte'
)
go
EXEC sp_helpindex 'dbo.DemoCluster'
dbcc dropcleanbuffers
go
-- first filling
print 'first filling'
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- index recreated with FillFactor = 100 and PAD_INDEX
print 'fillfactor = 100'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 100,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name = 'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- index recreated with FillFactor = 80 and PAD_INDEX
print 'fillfactor = 80'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 80,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name = 'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- index recreated with FillFactor = 60 and PAD_INDEX
print 'fillfactor = 60'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 60,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name = 'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- index recreated with FillFactor = 40 and PAD_INDEX
print 'fillfactor = 40'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 40,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name = 'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- index recreated with FillFactor = 20 and PAD_INDEX
print 'fillfactor = 20'
CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
FILLFACTOR = 20,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name = 'PK_DemoClustercol1'
go
EXEC FillTable
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- ======================================================== -- Test2 : clustering on a varchar column
-- ========================================================
print 'table creation : test with clustered varchar column'
IF OBJECTPROPERTY(object_id('dbo.DemoCluster'), 'IsUserTable') = 1
DROP TABLE dbo.DemoCluster
go
CREATE TABLE dbo.DemoCluster
(
col1 int identity CONSTRAINT PK_DemoClusterCol1 PRIMARY KEY NONCLUSTERED,
col2 varchar(20) CONSTRAINT UK_DemoClusterCol2 UNIQUE CLUSTERED,
col3 datetime CONSTRAINT DemoClusterCol3Default
DEFAULT getdate(),
col4 char(30) CONSTRAINT DemoClusterCol4Default
DEFAULT suser_name(),
col5 char(30) CONSTRAINT DemoClusterCol5Default
DEFAULT user_name(),
col6 char(100) CONSTRAINT DemoClusterCol6Default
DEFAULT 'valeur longue longue longue longue longue longue longue
longue longue longue longue longue longue ',
col7 varchar(200) CONSTRAINT DemoClusterCol7Default
DEFAULT 'valeur compacte'
)
go
EXEC sp_helpindex 'dbo.DemoCluster'
dbcc dropcleanbuffers
go
-- first filling
print 'first filling'
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- index recreated with FillFactor = 100 and PAD_INDEX
print 'fillfactor = 100'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 100,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name = 'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- index recreated with FillFactor = 80 and PAD_INDEX
print 'fillfactor = 80'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 80,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name = 'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- index recreated with FillFactor = 60 and PAD_INDEX
print 'fillfactor = 60'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 60,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name = 'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- index recreated with FillFactor = 40 and PAD_INDEX
print 'fillfactor = 40'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 40,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name = 'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
-- index recreated with FillFactor = 20 and PAD_INDEX
print 'fillfactor = 20'
CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
FILLFACTOR = 20,PAD_INDEX, DROP_EXISTING
select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name = 'UK_DemoClusterCol2'
go
EXEC FillTable 1
EXEC sp_spaceused 'dbo.DemoCluster', true
go
--
============================================================================= -- drop the database
--
============================================================================= USE master
IF (SELECT name FROM sysdatabases WHERE name='TEST_INDEX' ) IS NOT NULL DROP
DATABASE TEST_INDEX
go> how to explain the page splits with the clustered identity column ?
With an increasing column value based on IDENTITY or GETDATE(), a clustered
index page split occurs during inserts only when the last page in the table
becomes full. The number of page splits during each insert test is
approximately equal to the number new pages.
The number of new pages is constant because FILLFACTOR only applies when the
index is created. SQL Server does not maintain the specified percentage
afterward. The FILLFACTOR will waste space in this situation unless you
later increase row length with an UPDATE.
> how to measure the number of page splits (an not a ratio per second)
> occuring during an execution ?
This is the difference between the number of pages before/after each test.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"r.fauchatre" <rfauchatre@.discussions.microsoft.com> wrote in message
news:D690ADCC-9D6E-4570-BB8B-E6948F2E8FCA@.microsoft.com...
> Hi all,
> we can read in many articles that page splits don't occur with a cluster
> on
> a monotone increasing column (like an identity column) .
> with this script, we don't see that : with a clustered idendity column,
> the performance monitor shows page splits ( "SQLServer:AccessMethods" ;
> "Page
> Splits/sec") , and their level stays quite stable if the index is rebuilt
> with lower fillfactor for new insertions.
> with a clustered varchar column, the page splits is higher at the
> beginning
> but decrease with lower fillfactor (page splits are avoided when
> fillfactor => 40) : normal behavior.
> how to explain the page splits with the clustered identity column ?
> how to measure the number of page splits (an not a ratio per second)
> occuring during an execution ?
> thanks for reading my poor english and your replays,
> R.Fauchatre
> PS : tests on SQL Server 2000 SP4 Developer Edition (one user only)
> ====================================> Script
> ====================================> --
> =============================================================================> -- database creation
> --=============================================================================> USE master
> IF (SELECT name FROM sysdatabases WHERE name='TEST_INDEX' ) IS NULL CREATE
> DATABASE TEST_INDEX
> go
> USE TEST_INDEX
> -- ========================================================> -- fill procedures
> -- ========================================================> -- ----
> -- string generation
> -- ----
> print 'procédures creation'
> if OBJECT_ID('generate_string') is not null DROP PROC generate_string
> go
> CREATE PROCEDURE generate_string
> @.string varchar(20) OUTPUT
> AS
> BEGIN
> DECLARE @.limit int
> DECLARE @.curr_iteration int
> SELECT @.limit = round((rand() * 20) + 3, 0)
> SELECT @.curr_iteration = 0
> SELECT @.string = ''
> WHILE @.curr_iteration < @.limit
> BEGIN
> SELECT @.string = @.string + char(round((rand() * 25) + 1, 0) + 64)
> SELECT @.curr_iteration = @.curr_iteration + 1
> END
> IF SUBSTRING(@.string,1,1) = ' '
> BEGIN
> SELECT @.string = SUBSTRING(@.string,2,16)
> END
> END
> go
> -- ----
> -- filling the table (10000 rows)
> -- ----
> if OBJECT_ID('FillTable') is not null DROP PROC FillTable
> go
> CREATE PROC FillTable (@.Chaine bit = 0)
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.string varchar(20)
> DECLARE @.Compteur int
> SET @.compteur = 0
> WHILE @.compteur < 10000
> BEGIN
> SET @.compteur = @.compteur + 1
> IF @.chaine = 1
> BEGIN
> EXEC generate_string @.string output
> SET @.string = @.string + '_' + CAST(@.compteur as varchar(5))
> INSERT dbo.DemoCluster (col2) values (@.string)
> END
> ELSE
> BEGIN
> INSERT dbo.DemoCluster DEFAULT VALUES
> END
> END
> END
> go
> -- ========================================================> -- Test1 : clustering on an identity coumn
> -- ========================================================> print 'table creation : test with clustered identity column'
> IF OBJECTPROPERTY(object_id('dbo.DemoCluster'), 'IsUserTable') = 1
> DROP TABLE dbo.DemoCluster
> go
> CREATE TABLE dbo.DemoCluster
> (
> col1 int identity CONSTRAINT PK_DemoClusterCol1 PRIMARY KEY ,
> col2 varchar(20) CONSTRAINT DemoClusterCol2Default
> DEFAULT current_timestamp,
> col3 datetime CONSTRAINT DemoClusterCol3Default
> DEFAULT getdate(),
> col4 char(30) CONSTRAINT DemoClusterCol4Default
> DEFAULT suser_name(),
> col5 char(30) CONSTRAINT DemoClusterCol5Default
> DEFAULT user_name(),
> col6 char(100) CONSTRAINT DemoClusterCol6Default
> DEFAULT 'valeur longue longue longue longue longue longue longue
> longue longue longue longue longue longue ',
> col7 varchar(200) CONSTRAINT DemoClusterCol7Default
> DEFAULT 'valeur compacte'
> )
> go
> EXEC sp_helpindex 'dbo.DemoCluster'
> dbcc dropcleanbuffers
> go
> -- first filling
> print 'first filling'
> EXEC FillTable
> EXEC sp_spaceused 'dbo.DemoCluster', true
> go
> -- index recreated with FillFactor = 100 and PAD_INDEX
> print 'fillfactor = 100'
> CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
> FILLFACTOR = 100,PAD_INDEX, DROP_EXISTING
> select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => 'PK_DemoClustercol1'
> go
> EXEC FillTable
> EXEC sp_spaceused 'dbo.DemoCluster', true
> go
> -- index recreated with FillFactor = 80 and PAD_INDEX
> print 'fillfactor = 80'
> CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
> FILLFACTOR = 80,PAD_INDEX, DROP_EXISTING
> select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => 'PK_DemoClustercol1'
> go
> EXEC FillTable
> EXEC sp_spaceused 'dbo.DemoCluster', true
> go
> -- index recreated with FillFactor = 60 and PAD_INDEX
> print 'fillfactor = 60'
> CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
> FILLFACTOR = 60,PAD_INDEX, DROP_EXISTING
> select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => 'PK_DemoClustercol1'
> go
> EXEC FillTable
> EXEC sp_spaceused 'dbo.DemoCluster', true
> go
> -- index recreated with FillFactor = 40 and PAD_INDEX
> print 'fillfactor = 40'
> CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
> FILLFACTOR = 40,PAD_INDEX, DROP_EXISTING
> select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => 'PK_DemoClustercol1'
> go
> EXEC FillTable
> EXEC sp_spaceused 'dbo.DemoCluster', true
> go
> -- index recreated with FillFactor = 20 and PAD_INDEX
> print 'fillfactor = 20'
> CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
> FILLFACTOR = 20,PAD_INDEX, DROP_EXISTING
> select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => 'PK_DemoClustercol1'
> go
> EXEC FillTable
> EXEC sp_spaceused 'dbo.DemoCluster', true
> go
> -- ========================================================> -- Test2 : clustering on a varchar column
> -- ========================================================> print 'table creation : test with clustered varchar column'
> IF OBJECTPROPERTY(object_id('dbo.DemoCluster'), 'IsUserTable') = 1
> DROP TABLE dbo.DemoCluster
> go
> CREATE TABLE dbo.DemoCluster
> (
> col1 int identity CONSTRAINT PK_DemoClusterCol1 PRIMARY KEY
> NONCLUSTERED,
> col2 varchar(20) CONSTRAINT UK_DemoClusterCol2 UNIQUE CLUSTERED,
> col3 datetime CONSTRAINT DemoClusterCol3Default
> DEFAULT getdate(),
> col4 char(30) CONSTRAINT DemoClusterCol4Default
> DEFAULT suser_name(),
> col5 char(30) CONSTRAINT DemoClusterCol5Default
> DEFAULT user_name(),
> col6 char(100) CONSTRAINT DemoClusterCol6Default
> DEFAULT 'valeur longue longue longue longue longue longue longue
> longue longue longue longue longue longue ',
> col7 varchar(200) CONSTRAINT DemoClusterCol7Default
> DEFAULT 'valeur compacte'
> )
> go
> EXEC sp_helpindex 'dbo.DemoCluster'
> dbcc dropcleanbuffers
> go
> -- first filling
> print 'first filling'
> EXEC FillTable 1
> EXEC sp_spaceused 'dbo.DemoCluster', true
> go
> -- index recreated with FillFactor = 100 and PAD_INDEX
> print 'fillfactor = 100'
> CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
> FILLFACTOR = 100,PAD_INDEX, DROP_EXISTING
> select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => 'UK_DemoClusterCol2'
> go
> EXEC FillTable 1
> EXEC sp_spaceused 'dbo.DemoCluster', true
> go
> -- index recreated with FillFactor = 80 and PAD_INDEX
> print 'fillfactor = 80'
> CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
> FILLFACTOR = 80,PAD_INDEX, DROP_EXISTING
> select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => 'UK_DemoClusterCol2'
> go
> EXEC FillTable 1
> EXEC sp_spaceused 'dbo.DemoCluster', true
> go
> -- index recreated with FillFactor = 60 and PAD_INDEX
> print 'fillfactor = 60'
> CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
> FILLFACTOR = 60,PAD_INDEX, DROP_EXISTING
> select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => 'UK_DemoClusterCol2'
> go
> EXEC FillTable 1
> EXEC sp_spaceused 'dbo.DemoCluster', true
> go
> -- index recreated with FillFactor = 40 and PAD_INDEX
> print 'fillfactor = 40'
> CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
> FILLFACTOR = 40,PAD_INDEX, DROP_EXISTING
> select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => 'UK_DemoClusterCol2'
> go
> EXEC FillTable 1
> EXEC sp_spaceused 'dbo.DemoCluster', true
> go
> -- index recreated with FillFactor = 20 and PAD_INDEX
> print 'fillfactor = 20'
> CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
> FILLFACTOR = 20,PAD_INDEX, DROP_EXISTING
> select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => 'UK_DemoClusterCol2'
> go
> EXEC FillTable 1
> EXEC sp_spaceused 'dbo.DemoCluster', true
> go
> --
> =============================================================================> -- drop the database
> --
> =============================================================================> USE master
> IF (SELECT name FROM sysdatabases WHERE name='TEST_INDEX' ) IS NOT NULL
> DROP
> DATABASE TEST_INDEX
> go
>|||Thanks a lot Dan,
In this situation, is the page split a "normal" page split (with half data
of the full page moving on the new page : it's unusefull in this case ?) or
a particular page split to allocate only the new page (and reference it in
the superior level) without moving data of the full page?
R. Fauchatre
"Dan Guzman" wrote:
> > how to explain the page splits with the clustered identity column ?
> With an increasing column value based on IDENTITY or GETDATE(), a clustered
> index page split occurs during inserts only when the last page in the table
> becomes full. The number of page splits during each insert test is
> approximately equal to the number new pages.
> The number of new pages is constant because FILLFACTOR only applies when the
> index is created. SQL Server does not maintain the specified percentage
> afterward. The FILLFACTOR will waste space in this situation unless you
> later increase row length with an UPDATE.
> > how to measure the number of page splits (an not a ratio per second)
> > occuring during an execution ?
> This is the difference between the number of pages before/after each test.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.fauchatre" <rfauchatre@.discussions.microsoft.com> wrote in message
> news:D690ADCC-9D6E-4570-BB8B-E6948F2E8FCA@.microsoft.com...
> > Hi all,
> > we can read in many articles that page splits don't occur with a cluster
> > on
> > a monotone increasing column (like an identity column) .
> >
> > with this script, we don't see that : with a clustered idendity column,
> > the performance monitor shows page splits ( "SQLServer:AccessMethods" ;
> > "Page
> > Splits/sec") , and their level stays quite stable if the index is rebuilt
> > with lower fillfactor for new insertions.
> >
> > with a clustered varchar column, the page splits is higher at the
> > beginning
> > but decrease with lower fillfactor (page splits are avoided when
> > fillfactor => > 40) : normal behavior.
> >
> > how to explain the page splits with the clustered identity column ?
> >
> > how to measure the number of page splits (an not a ratio per second)
> > occuring during an execution ?
> >
> > thanks for reading my poor english and your replays,
> >
> > R.Fauchatre
> >
> > PS : tests on SQL Server 2000 SP4 Developer Edition (one user only)
> >
> > ====================================> > Script
> > ====================================> > --
> > =============================================================================> > -- database creation
> > --=============================================================================> >
> > USE master
> > IF (SELECT name FROM sysdatabases WHERE name='TEST_INDEX' ) IS NULL CREATE
> > DATABASE TEST_INDEX
> > go
> >
> > USE TEST_INDEX
> >
> > -- ========================================================> > -- fill procedures
> > -- ========================================================> >
> > -- ----
> > -- string generation
> > -- ----
> > print 'procÃ©dures creation'
> >
> > if OBJECT_ID('generate_string') is not null DROP PROC generate_string
> > go
> > CREATE PROCEDURE generate_string
> > @.string varchar(20) OUTPUT
> > AS
> > BEGIN
> > DECLARE @.limit int
> > DECLARE @.curr_iteration int
> > SELECT @.limit = round((rand() * 20) + 3, 0)
> > SELECT @.curr_iteration = 0
> > SELECT @.string = ''
> > WHILE @.curr_iteration < @.limit
> > BEGIN
> > SELECT @.string = @.string + char(round((rand() * 25) + 1, 0) + 64)
> > SELECT @.curr_iteration = @.curr_iteration + 1
> > END
> > IF SUBSTRING(@.string,1,1) = ' '
> > BEGIN
> > SELECT @.string = SUBSTRING(@.string,2,16)
> > END
> > END
> > go
> > -- ----
> > -- filling the table (10000 rows)
> > -- ----
> >
> > if OBJECT_ID('FillTable') is not null DROP PROC FillTable
> > go
> >
> > CREATE PROC FillTable (@.Chaine bit = 0)
> > AS
> > BEGIN
> > SET NOCOUNT ON
> > DECLARE @.string varchar(20)
> > DECLARE @.Compteur int
> > SET @.compteur = 0
> > WHILE @.compteur < 10000
> > BEGIN
> > SET @.compteur = @.compteur + 1
> > IF @.chaine = 1
> > BEGIN
> > EXEC generate_string @.string output
> > SET @.string = @.string + '_' + CAST(@.compteur as varchar(5))
> > INSERT dbo.DemoCluster (col2) values (@.string)
> > END
> > ELSE
> > BEGIN
> > INSERT dbo.DemoCluster DEFAULT VALUES
> > END
> > END
> > END
> > go
> > -- ========================================================> > -- Test1 : clustering on an identity coumn
> > -- ========================================================> >
> > print 'table creation : test with clustered identity column'
> >
> > IF OBJECTPROPERTY(object_id('dbo.DemoCluster'), 'IsUserTable') = 1
> > DROP TABLE dbo.DemoCluster
> > go
> >
> > CREATE TABLE dbo.DemoCluster
> > (
> > col1 int identity CONSTRAINT PK_DemoClusterCol1 PRIMARY KEY ,
> > col2 varchar(20) CONSTRAINT DemoClusterCol2Default
> > DEFAULT current_timestamp,
> > col3 datetime CONSTRAINT DemoClusterCol3Default
> > DEFAULT getdate(),
> > col4 char(30) CONSTRAINT DemoClusterCol4Default
> > DEFAULT suser_name(),
> > col5 char(30) CONSTRAINT DemoClusterCol5Default
> > DEFAULT user_name(),
> > col6 char(100) CONSTRAINT DemoClusterCol6Default
> > DEFAULT 'valeur longue longue longue longue longue longue longue
> > longue longue longue longue longue longue ',
> > col7 varchar(200) CONSTRAINT DemoClusterCol7Default
> > DEFAULT 'valeur compacte'
> > )
> > go
> > EXEC sp_helpindex 'dbo.DemoCluster'
> >
> > dbcc dropcleanbuffers
> > go
> > -- first filling
> > print 'first filling'
> > EXEC FillTable
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> >
> > -- index recreated with FillFactor = 100 and PAD_INDEX
> > print 'fillfactor = 100'
> > CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
> > FILLFACTOR = 100,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'PK_DemoClustercol1'
> > go
> > EXEC FillTable
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 80 and PAD_INDEX
> > print 'fillfactor = 80'
> > CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
> > FILLFACTOR = 80,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'PK_DemoClustercol1'
> > go
> > EXEC FillTable
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 60 and PAD_INDEX
> > print 'fillfactor = 60'
> > CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
> > FILLFACTOR = 60,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'PK_DemoClustercol1'
> > go
> > EXEC FillTable
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 40 and PAD_INDEX
> > print 'fillfactor = 40'
> > CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
> > FILLFACTOR = 40,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'PK_DemoClustercol1'
> > go
> > EXEC FillTable
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 20 and PAD_INDEX
> > print 'fillfactor = 20'
> > CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1) WITH
> > FILLFACTOR = 20,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'PK_DemoClustercol1'
> > go
> > EXEC FillTable
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> >
> > -- ========================================================> > -- Test2 : clustering on a varchar column
> > -- ========================================================> >
> > print 'table creation : test with clustered varchar column'
> >
> > IF OBJECTPROPERTY(object_id('dbo.DemoCluster'), 'IsUserTable') = 1
> > DROP TABLE dbo.DemoCluster
> > go
> >
> > CREATE TABLE dbo.DemoCluster
> > (
> > col1 int identity CONSTRAINT PK_DemoClusterCol1 PRIMARY KEY
> > NONCLUSTERED,
> > col2 varchar(20) CONSTRAINT UK_DemoClusterCol2 UNIQUE CLUSTERED,
> > col3 datetime CONSTRAINT DemoClusterCol3Default
> > DEFAULT getdate(),
> > col4 char(30) CONSTRAINT DemoClusterCol4Default
> > DEFAULT suser_name(),
> > col5 char(30) CONSTRAINT DemoClusterCol5Default
> > DEFAULT user_name(),
> > col6 char(100) CONSTRAINT DemoClusterCol6Default
> > DEFAULT 'valeur longue longue longue longue longue longue longue
> > longue longue longue longue longue longue ',
> > col7 varchar(200) CONSTRAINT DemoClusterCol7Default
> > DEFAULT 'valeur compacte'
> > )
> > go
> > EXEC sp_helpindex 'dbo.DemoCluster'
> >
> > dbcc dropcleanbuffers
> > go
> > -- first filling
> > print 'first filling'
> > EXEC FillTable 1
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> >
> > -- index recreated with FillFactor = 100 and PAD_INDEX
> > print 'fillfactor = 100'
> > CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
> > FILLFACTOR = 100,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'UK_DemoClusterCol2'
> > go
> > EXEC FillTable 1
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 80 and PAD_INDEX
> > print 'fillfactor = 80'
> > CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
> > FILLFACTOR = 80,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'UK_DemoClusterCol2'
> > go
> > EXEC FillTable 1
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 60 and PAD_INDEX
> > print 'fillfactor = 60'
> > CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
> > FILLFACTOR = 60,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'UK_DemoClusterCol2'
> > go
> > EXEC FillTable 1
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 40 and PAD_INDEX
> > print 'fillfactor = 40'
> > CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
> > FILLFACTOR = 40,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'UK_DemoClusterCol2'
> > go
> > EXEC FillTable 1
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 20 and PAD_INDEX
> > print 'fillfactor = 20'
> > CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2) WITH
> > FILLFACTOR = 20,PAD_INDEX, DROP_EXISTING|||Official word is that they will consider it for the next release. Feel free
to cast your vote:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126148
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"r.fauchatre" <rfauchatre@.discussions.microsoft.com> wrote in message
news:BE11EA63-33B7-441D-9176-73DCEF73EF89@.microsoft.com...
Thanks a lot Dan,
In this situation, is the page split a "normal" page split (with half data
of the full page moving on the new page : it's unusefull in this case ?)
or
a particular page split to allocate only the new page (and reference it in
the superior level) without moving data of the full page?
R. Fauchatre
"Dan Guzman" wrote:
> > how to explain the page splits with the clustered identity column ?
> With an increasing column value based on IDENTITY or GETDATE(), a
> clustered
> index page split occurs during inserts only when the last page in the
> table
> becomes full. The number of page splits during each insert test is
> approximately equal to the number new pages.
> The number of new pages is constant because FILLFACTOR only applies when
> the
> index is created. SQL Server does not maintain the specified percentage
> afterward. The FILLFACTOR will waste space in this situation unless you
> later increase row length with an UPDATE.
> > how to measure the number of page splits (an not a ratio per second)
> > occuring during an execution ?
> This is the difference between the number of pages before/after each test.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.fauchatre" <rfauchatre@.discussions.microsoft.com> wrote in message
> news:D690ADCC-9D6E-4570-BB8B-E6948F2E8FCA@.microsoft.com...
> > Hi all,
> > we can read in many articles that page splits don't occur with a cluster
> > on
> > a monotone increasing column (like an identity column) .
> >
> > with this script, we don't see that : with a clustered idendity column,
> > the performance monitor shows page splits ( "SQLServer:AccessMethods" ;
> > "Page
> > Splits/sec") , and their level stays quite stable if the index is
> > rebuilt
> > with lower fillfactor for new insertions.
> >
> > with a clustered varchar column, the page splits is higher at the
> > beginning
> > but decrease with lower fillfactor (page splits are avoided when
> > fillfactor => > 40) : normal behavior.
> >
> > how to explain the page splits with the clustered identity column ?
> >
> > how to measure the number of page splits (an not a ratio per second)
> > occuring during an execution ?
> >
> > thanks for reading my poor english and your replays,
> >
> > R.Fauchatre
> >
> > PS : tests on SQL Server 2000 SP4 Developer Edition (one user only)
> >
> > ====================================> > Script
> > ====================================> > --
> > =============================================================================> > -- database creation
> > --=============================================================================> >
> > USE master
> > IF (SELECT name FROM sysdatabases WHERE name='TEST_INDEX' ) IS NULL
> > CREATE
> > DATABASE TEST_INDEX
> > go
> >
> > USE TEST_INDEX
> >
> > -- ========================================================> > -- fill procedures
> > -- ========================================================> >
> > -- ----
> > -- string generation
> > -- ----
> > print 'procÃ©dures creation'
> >
> > if OBJECT_ID('generate_string') is not null DROP PROC generate_string
> > go
> > CREATE PROCEDURE generate_string
> > @.string varchar(20) OUTPUT
> > AS
> > BEGIN
> > DECLARE @.limit int
> > DECLARE @.curr_iteration int
> > SELECT @.limit = round((rand() * 20) + 3, 0)
> > SELECT @.curr_iteration = 0
> > SELECT @.string = ''
> > WHILE @.curr_iteration < @.limit
> > BEGIN
> > SELECT @.string = @.string + char(round((rand() * 25) + 1, 0) + 64)
> > SELECT @.curr_iteration = @.curr_iteration + 1
> > END
> > IF SUBSTRING(@.string,1,1) = ' '
> > BEGIN
> > SELECT @.string = SUBSTRING(@.string,2,16)
> > END
> > END
> > go
> > -- ----
> > -- filling the table (10000 rows)
> > -- ----
> >
> > if OBJECT_ID('FillTable') is not null DROP PROC FillTable
> > go
> >
> > CREATE PROC FillTable (@.Chaine bit = 0)
> > AS
> > BEGIN
> > SET NOCOUNT ON
> > DECLARE @.string varchar(20)
> > DECLARE @.Compteur int
> > SET @.compteur = 0
> > WHILE @.compteur < 10000
> > BEGIN
> > SET @.compteur = @.compteur + 1
> > IF @.chaine = 1
> > BEGIN
> > EXEC generate_string @.string output
> > SET @.string = @.string + '_' + CAST(@.compteur as varchar(5))
> > INSERT dbo.DemoCluster (col2) values (@.string)
> > END
> > ELSE
> > BEGIN
> > INSERT dbo.DemoCluster DEFAULT VALUES
> > END
> > END
> > END
> > go
> > -- ========================================================> > -- Test1 : clustering on an identity coumn
> > -- ========================================================> >
> > print 'table creation : test with clustered identity column'
> >
> > IF OBJECTPROPERTY(object_id('dbo.DemoCluster'), 'IsUserTable') = 1
> > DROP TABLE dbo.DemoCluster
> > go
> >
> > CREATE TABLE dbo.DemoCluster
> > (
> > col1 int identity CONSTRAINT PK_DemoClusterCol1 PRIMARY KEY ,
> > col2 varchar(20) CONSTRAINT DemoClusterCol2Default
> > DEFAULT current_timestamp,
> > col3 datetime CONSTRAINT DemoClusterCol3Default
> > DEFAULT getdate(),
> > col4 char(30) CONSTRAINT DemoClusterCol4Default
> > DEFAULT suser_name(),
> > col5 char(30) CONSTRAINT DemoClusterCol5Default
> > DEFAULT user_name(),
> > col6 char(100) CONSTRAINT DemoClusterCol6Default
> > DEFAULT 'valeur longue longue longue longue longue longue longue
> > longue longue longue longue longue longue ',
> > col7 varchar(200) CONSTRAINT DemoClusterCol7Default
> > DEFAULT 'valeur compacte'
> > )
> > go
> > EXEC sp_helpindex 'dbo.DemoCluster'
> >
> > dbcc dropcleanbuffers
> > go
> > -- first filling
> > print 'first filling'
> > EXEC FillTable
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> >
> > -- index recreated with FillFactor = 100 and PAD_INDEX
> > print 'fillfactor = 100'
> > CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1)
> > WITH
> > FILLFACTOR = 100,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'PK_DemoClustercol1'
> > go
> > EXEC FillTable
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 80 and PAD_INDEX
> > print 'fillfactor = 80'
> > CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1)
> > WITH
> > FILLFACTOR = 80,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'PK_DemoClustercol1'
> > go
> > EXEC FillTable
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 60 and PAD_INDEX
> > print 'fillfactor = 60'
> > CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1)
> > WITH
> > FILLFACTOR = 60,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'PK_DemoClustercol1'
> > go
> > EXEC FillTable
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 40 and PAD_INDEX
> > print 'fillfactor = 40'
> > CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1)
> > WITH
> > FILLFACTOR = 40,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'PK_DemoClustercol1'
> > go
> > EXEC FillTable
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 20 and PAD_INDEX
> > print 'fillfactor = 20'
> > CREATE UNIQUE CLUSTERED Index PK_DemoCLusterCol1 ON DemoCLuster(Col1)
> > WITH
> > FILLFACTOR = 20,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'PK_DemoClustercol1'
> > go
> > EXEC FillTable
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> >
> > -- ========================================================> > -- Test2 : clustering on a varchar column
> > -- ========================================================> >
> > print 'table creation : test with clustered varchar column'
> >
> > IF OBJECTPROPERTY(object_id('dbo.DemoCluster'), 'IsUserTable') = 1
> > DROP TABLE dbo.DemoCluster
> > go
> >
> > CREATE TABLE dbo.DemoCluster
> > (
> > col1 int identity CONSTRAINT PK_DemoClusterCol1 PRIMARY KEY
> > NONCLUSTERED,
> > col2 varchar(20) CONSTRAINT UK_DemoClusterCol2 UNIQUE CLUSTERED,
> > col3 datetime CONSTRAINT DemoClusterCol3Default
> > DEFAULT getdate(),
> > col4 char(30) CONSTRAINT DemoClusterCol4Default
> > DEFAULT suser_name(),
> > col5 char(30) CONSTRAINT DemoClusterCol5Default
> > DEFAULT user_name(),
> > col6 char(100) CONSTRAINT DemoClusterCol6Default
> > DEFAULT 'valeur longue longue longue longue longue longue longue
> > longue longue longue longue longue longue ',
> > col7 varchar(200) CONSTRAINT DemoClusterCol7Default
> > DEFAULT 'valeur compacte'
> > )
> > go
> > EXEC sp_helpindex 'dbo.DemoCluster'
> >
> > dbcc dropcleanbuffers
> > go
> > -- first filling
> > print 'first filling'
> > EXEC FillTable 1
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> >
> > -- index recreated with FillFactor = 100 and PAD_INDEX
> > print 'fillfactor = 100'
> > CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2)
> > WITH
> > FILLFACTOR = 100,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'UK_DemoClusterCol2'
> > go
> > EXEC FillTable 1
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 80 and PAD_INDEX
> > print 'fillfactor = 80'
> > CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2)
> > WITH
> > FILLFACTOR = 80,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'UK_DemoClusterCol2'
> > go
> > EXEC FillTable 1
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 60 and PAD_INDEX
> > print 'fillfactor = 60'
> > CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2)
> > WITH
> > FILLFACTOR = 60,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'UK_DemoClusterCol2'
> > go
> > EXEC FillTable 1
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 40 and PAD_INDEX
> > print 'fillfactor = 40'
> > CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2)
> > WITH
> > FILLFACTOR = 40,PAD_INDEX, DROP_EXISTING
> > select indid, OrigFillFactor, rowcnt, used from sysindexes WHERE name => > 'UK_DemoClusterCol2'
> > go
> > EXEC FillTable 1
> > EXEC sp_spaceused 'dbo.DemoCluster', true
> > go
> > -- index recreated with FillFactor = 20 and PAD_INDEX
> > print 'fillfactor = 20'
> > CREATE UNIQUE CLUSTERED Index UK_DemoClusterCol2 ON DemoCLuster(Col2)
> > WITH
> > FILLFACTOR = 20,PAD_INDEX, DROP_EXISTING

Saturday, February 25, 2012

page splits/ fragmentation

Page splits/ Dirty pages/ Checkpoint

page splits on a clustered identity column ?

page splits on a clustered identity column ?

Page Numbers in Excel

Blog Archive

About Me