Page Numbers in Excel: increasing

Showing posts with label increasing. Show all posts

Saturday, February 25, 2012

Pagefile and Paging

My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
solution. He believes the pagefile settings should be 1.5 times the amount
of physical RAM which I agree but I seem to find it hard to correlate paging
with pagefile increase.
Also under what conditions would one need ot consider increasing the size of
the pagefile if its not set to 1.5 * Physical RAM ?
We are using SQL 2000/2005
Thank you.
"F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
>
He's 1/2 right. You need more memory. But increasing the pagefile won't
make a difference.
Basically SQL Server can page, or it can ask the OS to page to disk. Both
involve disk I/O.
Get more memory or rewrite your queries.

> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Increasing the page file size generally would not be the solution to hard
paging. I'd first try to determine whether hard paging is from the SQL Server
process (i.e. whether Windows is paging out the workign set of the SQL Server
process). If that's the case, try to find whether there is any other
processes that are consuming memory and caused paging.
It's also possible that you may be running into a SQL Server bug. For
instance, http://support.microsoft.com/kb/884593 or
http://support.microsoft.com/kb/918483 are examples. I'm not saying that they
apply to your case, but want to point this out as a possibility.
Linchi
"F" wrote:

> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
> solution. He believes the pagefile settings should be 1.5 times the amount
> of physical RAM which I agree but I seem to find it hard to correlate paging
> with pagefile increase.
> Also under what conditions would one need ot consider increasing the size of
> the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
>
|||I too agree that increasing the pagefile size will not help at all. Your
DBA's first thought should have been "what is causing the paging" and not
how to get around it. SQL Server is designed to do as much as possible to
avoid paging to begin with. It is likely you have other applications on the
server than SQL Server that require some memory and SQL Server is set to use
most of it. If that is the case you may be able to avoid the paging by
setting the MAX Memory setting in SQL Server to leave room for the other
apps. Adding additional memory may also be an option but you still have to
consider how all the apps play together.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.

Pagefile and Paging

My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
solution. He believes the pagefile settings should be 1.5 times the amount
of physical RAM which I agree but I seem to find it hard to correlate paging
with pagefile increase.
Also under what conditions would one need ot consider increasing the size of
the pagefile if its not set to 1.5 * Physical RAM ?
We are using SQL 2000/2005
Thank you."F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
>
He's 1/2 right. You need more memory. But increasing the pagefile won't
make a difference.
Basically SQL Server can page, or it can ask the OS to page to disk. Both
involve disk I/O.
Get more memory or rewrite your queries.
> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Increasing the page file size generally would not be the solution to hard
paging. I'd first try to determine whether hard paging is from the SQL Server
process (i.e. whether Windows is paging out the workign set of the SQL Server
process). If that's the case, try to find whether there is any other
processes that are consuming memory and caused paging.
It's also possible that you may be running into a SQL Server bug. For
instance, http://support.microsoft.com/kb/884593 or
http://support.microsoft.com/kb/918483 are examples. I'm not saying that they
apply to your case, but want to point this out as a possibility.
Linchi
"F" wrote:
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
> solution. He believes the pagefile settings should be 1.5 times the amount
> of physical RAM which I agree but I seem to find it hard to correlate paging
> with pagefile increase.
> Also under what conditions would one need ot consider increasing the size of
> the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
>|||I too agree that increasing the pagefile size will not help at all. Your
DBA's first thought should have been "what is causing the paging" and not
how to get around it. SQL Server is designed to do as much as possible to
avoid paging to begin with. It is likely you have other applications on the
server than SQL Server that require some memory and SQL Server is set to use
most of it. If that is the case you may be able to avoid the paging by
setting the MAX Memory setting in SQL Server to leave room for the other
apps. Adding additional memory may also be an option but you still have to
consider how all the apps play together.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.

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

Pagefile and Paging

Pagefile and Paging

page splits on a clustered identity column ?

page splits on a clustered identity column ?

Page Numbers in Excel

Blog Archive

About Me