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