I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
Showing posts with label articles. Show all posts
Showing posts with label articles. Show all posts
Tuesday, March 20, 2012
Paging, Performance and ADODB
I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
Paging, Performance and ADODB
I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?
Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.
|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?
Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.
|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
Friday, March 9, 2012
Paging at the end of a large data set
I want to do paging with my VB.NET app using SQL Server. I have a
large table with over 5 million records. I've read numerous articles
on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
examples that they have provided. Performance is fine if you are
paging the "top" part of the data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best time that I can get in returning the page is 10+
seconds for each page.
How can I do this efficiently?Did you look at the methods offered at http://www.aspfaq.com/2120
?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||BTW, are you narrowing down your result set before you allow the user to
page through them, or is every paging operation performed on all 5 million
rows every time?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||> Did you look at the methods offered athttp://www.aspfaq.com/2120
Thanks for the response Aaron. I had not seen that article. However,
I do not believe these SQL Server examples help me much. For example,
in the author's SPs, he has the following code:
SELECT
@.rows = COUNT(*),
@.pages = COUNT(*) / @.perpage
FROM
SampleCDs WITH (NOLOCK)
That alone takes 9 seconds to run over my 5+ million records.
However, I had done some testing with ADODB and recordsets yesterday
and the performance was really good. So this article may help me with
that. I need to look at it some more.
I didn't want to use an ADODB solution. So I'm still looking for an
adequate SQL Server solution. Do you, or anyone else, know of any
others?|||> That alone takes 9 seconds to run over my 5+ million records.
What is the DDL for the table? Is there a clustered index?|||On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> BTW, are you narrowing down your result set before you allow the user to
> page through them, or is every paging operation performed on all 5 million
> rows every time?
Mike, thanks for your response. My goal is to do what you are
saying. I do not want to return all 5 million records. That takes
minutes. I want to create a SQL statement that returns a "page" of
records (page = 10 or 500). I can successfully do that. But like I
said, when I return records 5,000,001 through 5,000,010 it takes over
10 seconds. That is bad performance.
FYI, I am running in SQL Server 2005 and am ordering the records over
the Primary Key.|||> What is the DDL for the table? Is there a clustered index?
I apologize. I'm not a SQL Server expert and do not know what a DDL
is. Also, my table does have a clustered index. It is on the Primary
Key which is an Identity Field. There are other non-clustered indeces
also.|||Why can't you just do something like this to get to the end or bottom of the
dataset?
Select top 10 percent * from tblMyTable order by tblMyTable.MyColumn DESC?
Maybe you don't have a column that puts them in any order, but if you
didn't how would you know the bottom was always the bottom?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
Hi Paul,
What I was getting at is on the server side are your users paging through
all 5,000,000 rows or do you have some way to narrow it down beforehand.
For instance, if I wanted to page through a list of books, I might just want
the ones with titles that begin with "B". That would go a long way to
narrowing down my results from 5,000,000 from the start.
Also you said you are ordering these rows by PK. Is the PK the clustered
index as well?
BTW what type of data is it that you're paging through? Names, products,
...?
Thanks|||P.S. - SQL 2000 or 2005? With 2005 you could use ROW_NUMBER and to get a
better response time. Your best response though (I know I keep saying this)
would be if you could narrow the result set down before you start paging.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
>> BTW, are you narrowing down your result set before you allow the user to
>> page through them, or is every paging operation performed on all 5
>> million
>> rows every time?
>
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
>|||Thanks to all who responded! I appreciate it.
First, I could use the ORDER BY DESC *IF* I only wanted to get the
last 10. But I will need to page backwards from the last page. To
get the second to last page I need to do something else. So this
solution is not acceptable.
Second, we will provide for the ability to filter. However, I want to
be able to figure this out. Microsoft Access can do it. If I open
the same table with the 5 million records, I can move to the bottom of
the data set and page backward. There is a lag going to the bottom,
but once there it pages with virtually no delay.
Third, I've used the ROW_NUMBER and results are not much different. I
tried every example that I could find. Nothing beats using the ADODB
recordset object. This doesn't make sense to me. I have to believe
that SQL Server can do this.
Ultimately what I am trying to do is create my own DataGridView and
use it in Virtual mode to be able to display tables with this many
records. So far I have not found anything that comes even close to
the performance Microsoft Access provides.|||One more thing...
Another option that I'm considering is the use of threading. Like MS
Access there would be a delay in getting the bottom 3-5 pages.
However as the person is paging through, a new thread is generated to
capture additional pages. I was trying to see if SQL Server could
work with better performance without having to use threading.
FYI...I have pasted below the SQL code that I used with the
ROW_NUMBER:
WITH A AS ( SELECT TrustID, ClientID, [DATE], Amount, ROW_NUMBER()
OVER (order by TrustID) AS RowNumber FROM tbl_TrustData )
SELECT *
FROM A
WHERE RowNumber between 5000001 and 5000010
TrustID is the PK and is a clustered index.|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171986970.993291.134530@.k78g2000cwa.googlegroups.com...
> Thanks to all who responded! I appreciate it.
> First, I could use the ORDER BY DESC *IF* I only wanted to get the
> last 10. But I will need to page backwards from the last page. To
> get the second to last page I need to do something else. So this
> solution is not acceptable.
I think the idea there was to retrieve pages from the bottom up. For
instance, any pages in the back half (last 50% of the rows in your table)
might get some benefit from retrieving them from the back - although you'd
have to play with it to see if it helps your situation.
> Second, we will provide for the ability to filter. However, I want to
> be able to figure this out. Microsoft Access can do it. If I open
> the same table with the 5 million records, I can move to the bottom of
> the data set and page backward. There is a lag going to the bottom,
> but once there it pages with virtually no delay.
Microsoft Access is opening a cursor, iterating every single row (hence the
lag time you feel), and caching every single row in memory. If that's what
you want to replicate, then just retrieve every single row from SQL Server
into your client application and page client-side...
> Third, I've used the ROW_NUMBER and results are not much different. I
> tried every example that I could find. Nothing beats using the ADODB
> recordset object. This doesn't make sense to me. I have to believe
> that SQL Server can do this.
> Ultimately what I am trying to do is create my own DataGridView and
> use it in Virtual mode to be able to display tables with this many
> records. So far I have not found anything that comes even close to
> the performance Microsoft Access provides.
That "performance" comes from caching the entire dataset in memory
client-side, which you can easily replicate yourself if you really want to
read the entire dataset into memory client-side just to page it...|||Access cannot be loading every record into memory. When I view the
Process from the Task Manager, Access never goes over 50K (under the
"Mem Usage" column). Also, I've tried loading every record in memory
just to see what would happen and get an "Out of Memory" exception.
Is Access loading every record to some temporary file? I would not
think that would improve performance. I would guess that would make
it worse.
It sounds like I cannot get the performance that I want from SQL
Server retrieving any page, whether top or bottom, from a large
DataSet.|||It's using a fast-forward cursor to rip through the dataset (one of the
methods described on Aaron Bertrand's article on the subject at ASPFAQ). I
was exaggerating Access' caching mechanism (for effect), but Jet does cache
a lot of data client-side which, while it is one way to try to improve
performance, introduces a lot of complexity since you need to keep track of
who's updating what and all that good crap. If you want to try to emulate
Access, use a cursor.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1172098426.310438.17840@.j27g2000cwj.googlegroups.com...
> Access cannot be loading every record into memory. When I view the
> Process from the Task Manager, Access never goes over 50K (under the
> "Mem Usage" column). Also, I've tried loading every record in memory
> just to see what would happen and get an "Out of Memory" exception.
> Is Access loading every record to some temporary file? I would not
> think that would improve performance. I would guess that would make
> it worse.
> It sounds like I cannot get the performance that I want from SQL
> Server retrieving any page, whether top or bottom, from a large
> DataSet.
>|||Thanks Mike. That's the information that I was looking for. The
ASPFAQ article does not mention cursors. Is the Recordset object
using the cursor?|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1172253270.739022.146740@.8g2000cwh.googlegroups.com...
> Thanks Mike. That's the information that I was looking for. The
> ASPFAQ article does not mention cursors. Is the Recordset object
> using the cursor?
I must have been drinking that night. I thought ASPFAQ had an article
comparing performance of different paging techniques; apparently not. I'll
keep looking for that article (maybe someone else remembers seeing it and
can provide a link?)
Depending on which technique you use, you may get a client-side cursor
automatically, and it may even be backed up by a server-side cursor. (See
SqlDataReader). Old ADO was famous for its use of cursors to get at the
data, although its been a while so I'd have to look up the Recordset object
specs to find out for sure in that code. I would suspect that it is using a
client-side cursor, at least.
Here's a couple more links for you:
http://www.4guysfromrolla.com/webtech/042606-1.shtml
http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
Google up some "SQL Server paging speed" or some such... This problem has
been attacked by a lot of people all over the place through the years, so
there's a lot of good info. out there on it.|||Thanks Mike! You've been very helpful. I will look through these.|||> I must have been drinking that night. I thought ASPFAQ had an article
> comparing performance of different paging techniques;
It does;
http://www.aspfaq.com/2120
A|||Not sure if this is relevant but I did a blog post a while ago about paging
that had some references:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/20/580353.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike C#" <xyz@.xyz.com> wrote in message
news:O04M$i3VHHA.488@.TK2MSFTNGP06.phx.gbl...
> "Paul" <pwh777@.hotmail.com> wrote in message
> news:1172253270.739022.146740@.8g2000cwh.googlegroups.com...
>> Thanks Mike. That's the information that I was looking for. The
>> ASPFAQ article does not mention cursors. Is the Recordset object
>> using the cursor?
> I must have been drinking that night. I thought ASPFAQ had an article
> comparing performance of different paging techniques; apparently not.
> I'll keep looking for that article (maybe someone else remembers seeing it
> and can provide a link?)
> Depending on which technique you use, you may get a client-side cursor
> automatically, and it may even be backed up by a server-side cursor. (See
> SqlDataReader). Old ADO was famous for its use of cursors to get at the
> data, although its been a while so I'd have to look up the Recordset
> object specs to find out for sure in that code. I would suspect that it
> is using a client-side cursor, at least.
> Here's a couple more links for you:
> http://www.4guysfromrolla.com/webtech/042606-1.shtml
> http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
> Google up some "SQL Server paging speed" or some such... This problem has
> been attacked by a lot of people all over the place through the years, so
> there's a lot of good info. out there on it.
>|||For some reason, the data at the end of the article (including the important
summary of performance comparisons) shows up blank. You can get it if you
download the PDF of all articles from http://www.aspfaq.com/downloads.asp
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eTQ%23493VHHA.5060@.TK2MSFTNGP06.phx.gbl...
>> I must have been drinking that night. I thought ASPFAQ had an article
>> comparing performance of different paging techniques;
> It does;
> http://www.aspfaq.com/2120
> A
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eTQ%23493VHHA.5060@.TK2MSFTNGP06.phx.gbl...
>> I must have been drinking that night. I thought ASPFAQ had an article
>> comparing performance of different paging techniques;
> It does;
> http://www.aspfaq.com/2120
>
I thought your article also showed performance differences between
server-side paging techniques like using dynamic SQL, cursors, temp tables,
etc. in a side-by-side format. I'm probably confusing your article with
another one I've seen somewhere, but I'll be darned if I'm able to find it
now. Or it might have just been a heavy night of drinking :)|||>> It does;
>> http://www.aspfaq.com/2120
> I thought your article also showed performance differences between
> server-side paging techniques like using dynamic SQL, cursors, temp
> tables, etc. in a side-by-side format.
It did (see my follow-up).
I talked to the site owners and they were supposed to have fixed it today,
but haven't yet.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OlVBKX9VHHA.392@.TK2MSFTNGP06.phx.gbl...
> It did (see my follow-up).
> I talked to the site owners and they were supposed to have fixed it today,
> but haven't yet.
I thought you owned it? What's going on 'round here? :)|||>> I talked to the site owners and they were supposed to have fixed it
>> today, but haven't yet.
> I thought you owned it? What's going on 'round here? :)
No, I handed it over to new owners last year.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23gwTXeOWHHA.3568@.TK2MSFTNGP06.phx.gbl...
> No, I handed it over to new owners last year.
Well that's a kick in the pants! Hope you made a killin' :)|||>> No, I handed it over to new owners last year.
> Well that's a kick in the pants! Hope you made a killin' :)
Sure, but I'll be paying dearly for it on April 15th. :-)|||>> No, I handed it over to new owners last year.
> Well that's a kick in the pants! Hope you made a killin' :)
Sure, but I'll be paying dearly for it on April 15th. :-)|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23A7bClqWHHA.3332@.TK2MSFTNGP04.phx.gbl...
> Sure, but I'll be paying dearly for it on April 15th. :-)
Sounds like it's time for a trip to Vegas... They'll help you if you have
too much money :)
large table with over 5 million records. I've read numerous articles
on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
examples that they have provided. Performance is fine if you are
paging the "top" part of the data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best time that I can get in returning the page is 10+
seconds for each page.
How can I do this efficiently?Did you look at the methods offered at http://www.aspfaq.com/2120
?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||BTW, are you narrowing down your result set before you allow the user to
page through them, or is every paging operation performed on all 5 million
rows every time?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||> Did you look at the methods offered athttp://www.aspfaq.com/2120
Thanks for the response Aaron. I had not seen that article. However,
I do not believe these SQL Server examples help me much. For example,
in the author's SPs, he has the following code:
SELECT
@.rows = COUNT(*),
@.pages = COUNT(*) / @.perpage
FROM
SampleCDs WITH (NOLOCK)
That alone takes 9 seconds to run over my 5+ million records.
However, I had done some testing with ADODB and recordsets yesterday
and the performance was really good. So this article may help me with
that. I need to look at it some more.
I didn't want to use an ADODB solution. So I'm still looking for an
adequate SQL Server solution. Do you, or anyone else, know of any
others?|||> That alone takes 9 seconds to run over my 5+ million records.
What is the DDL for the table? Is there a clustered index?|||On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> BTW, are you narrowing down your result set before you allow the user to
> page through them, or is every paging operation performed on all 5 million
> rows every time?
Mike, thanks for your response. My goal is to do what you are
saying. I do not want to return all 5 million records. That takes
minutes. I want to create a SQL statement that returns a "page" of
records (page = 10 or 500). I can successfully do that. But like I
said, when I return records 5,000,001 through 5,000,010 it takes over
10 seconds. That is bad performance.
FYI, I am running in SQL Server 2005 and am ordering the records over
the Primary Key.|||> What is the DDL for the table? Is there a clustered index?
I apologize. I'm not a SQL Server expert and do not know what a DDL
is. Also, my table does have a clustered index. It is on the Primary
Key which is an Identity Field. There are other non-clustered indeces
also.|||Why can't you just do something like this to get to the end or bottom of the
dataset?
Select top 10 percent * from tblMyTable order by tblMyTable.MyColumn DESC?
Maybe you don't have a column that puts them in any order, but if you
didn't how would you know the bottom was always the bottom?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
Hi Paul,
What I was getting at is on the server side are your users paging through
all 5,000,000 rows or do you have some way to narrow it down beforehand.
For instance, if I wanted to page through a list of books, I might just want
the ones with titles that begin with "B". That would go a long way to
narrowing down my results from 5,000,000 from the start.
Also you said you are ordering these rows by PK. Is the PK the clustered
index as well?
BTW what type of data is it that you're paging through? Names, products,
...?
Thanks|||P.S. - SQL 2000 or 2005? With 2005 you could use ROW_NUMBER and to get a
better response time. Your best response though (I know I keep saying this)
would be if you could narrow the result set down before you start paging.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
>> BTW, are you narrowing down your result set before you allow the user to
>> page through them, or is every paging operation performed on all 5
>> million
>> rows every time?
>
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
>|||Thanks to all who responded! I appreciate it.
First, I could use the ORDER BY DESC *IF* I only wanted to get the
last 10. But I will need to page backwards from the last page. To
get the second to last page I need to do something else. So this
solution is not acceptable.
Second, we will provide for the ability to filter. However, I want to
be able to figure this out. Microsoft Access can do it. If I open
the same table with the 5 million records, I can move to the bottom of
the data set and page backward. There is a lag going to the bottom,
but once there it pages with virtually no delay.
Third, I've used the ROW_NUMBER and results are not much different. I
tried every example that I could find. Nothing beats using the ADODB
recordset object. This doesn't make sense to me. I have to believe
that SQL Server can do this.
Ultimately what I am trying to do is create my own DataGridView and
use it in Virtual mode to be able to display tables with this many
records. So far I have not found anything that comes even close to
the performance Microsoft Access provides.|||One more thing...
Another option that I'm considering is the use of threading. Like MS
Access there would be a delay in getting the bottom 3-5 pages.
However as the person is paging through, a new thread is generated to
capture additional pages. I was trying to see if SQL Server could
work with better performance without having to use threading.
FYI...I have pasted below the SQL code that I used with the
ROW_NUMBER:
WITH A AS ( SELECT TrustID, ClientID, [DATE], Amount, ROW_NUMBER()
OVER (order by TrustID) AS RowNumber FROM tbl_TrustData )
SELECT *
FROM A
WHERE RowNumber between 5000001 and 5000010
TrustID is the PK and is a clustered index.|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171986970.993291.134530@.k78g2000cwa.googlegroups.com...
> Thanks to all who responded! I appreciate it.
> First, I could use the ORDER BY DESC *IF* I only wanted to get the
> last 10. But I will need to page backwards from the last page. To
> get the second to last page I need to do something else. So this
> solution is not acceptable.
I think the idea there was to retrieve pages from the bottom up. For
instance, any pages in the back half (last 50% of the rows in your table)
might get some benefit from retrieving them from the back - although you'd
have to play with it to see if it helps your situation.
> Second, we will provide for the ability to filter. However, I want to
> be able to figure this out. Microsoft Access can do it. If I open
> the same table with the 5 million records, I can move to the bottom of
> the data set and page backward. There is a lag going to the bottom,
> but once there it pages with virtually no delay.
Microsoft Access is opening a cursor, iterating every single row (hence the
lag time you feel), and caching every single row in memory. If that's what
you want to replicate, then just retrieve every single row from SQL Server
into your client application and page client-side...
> Third, I've used the ROW_NUMBER and results are not much different. I
> tried every example that I could find. Nothing beats using the ADODB
> recordset object. This doesn't make sense to me. I have to believe
> that SQL Server can do this.
> Ultimately what I am trying to do is create my own DataGridView and
> use it in Virtual mode to be able to display tables with this many
> records. So far I have not found anything that comes even close to
> the performance Microsoft Access provides.
That "performance" comes from caching the entire dataset in memory
client-side, which you can easily replicate yourself if you really want to
read the entire dataset into memory client-side just to page it...|||Access cannot be loading every record into memory. When I view the
Process from the Task Manager, Access never goes over 50K (under the
"Mem Usage" column). Also, I've tried loading every record in memory
just to see what would happen and get an "Out of Memory" exception.
Is Access loading every record to some temporary file? I would not
think that would improve performance. I would guess that would make
it worse.
It sounds like I cannot get the performance that I want from SQL
Server retrieving any page, whether top or bottom, from a large
DataSet.|||It's using a fast-forward cursor to rip through the dataset (one of the
methods described on Aaron Bertrand's article on the subject at ASPFAQ). I
was exaggerating Access' caching mechanism (for effect), but Jet does cache
a lot of data client-side which, while it is one way to try to improve
performance, introduces a lot of complexity since you need to keep track of
who's updating what and all that good crap. If you want to try to emulate
Access, use a cursor.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1172098426.310438.17840@.j27g2000cwj.googlegroups.com...
> Access cannot be loading every record into memory. When I view the
> Process from the Task Manager, Access never goes over 50K (under the
> "Mem Usage" column). Also, I've tried loading every record in memory
> just to see what would happen and get an "Out of Memory" exception.
> Is Access loading every record to some temporary file? I would not
> think that would improve performance. I would guess that would make
> it worse.
> It sounds like I cannot get the performance that I want from SQL
> Server retrieving any page, whether top or bottom, from a large
> DataSet.
>|||Thanks Mike. That's the information that I was looking for. The
ASPFAQ article does not mention cursors. Is the Recordset object
using the cursor?|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1172253270.739022.146740@.8g2000cwh.googlegroups.com...
> Thanks Mike. That's the information that I was looking for. The
> ASPFAQ article does not mention cursors. Is the Recordset object
> using the cursor?
I must have been drinking that night. I thought ASPFAQ had an article
comparing performance of different paging techniques; apparently not. I'll
keep looking for that article (maybe someone else remembers seeing it and
can provide a link?)
Depending on which technique you use, you may get a client-side cursor
automatically, and it may even be backed up by a server-side cursor. (See
SqlDataReader). Old ADO was famous for its use of cursors to get at the
data, although its been a while so I'd have to look up the Recordset object
specs to find out for sure in that code. I would suspect that it is using a
client-side cursor, at least.
Here's a couple more links for you:
http://www.4guysfromrolla.com/webtech/042606-1.shtml
http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
Google up some "SQL Server paging speed" or some such... This problem has
been attacked by a lot of people all over the place through the years, so
there's a lot of good info. out there on it.|||Thanks Mike! You've been very helpful. I will look through these.|||> I must have been drinking that night. I thought ASPFAQ had an article
> comparing performance of different paging techniques;
It does;
http://www.aspfaq.com/2120
A|||Not sure if this is relevant but I did a blog post a while ago about paging
that had some references:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/20/580353.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike C#" <xyz@.xyz.com> wrote in message
news:O04M$i3VHHA.488@.TK2MSFTNGP06.phx.gbl...
> "Paul" <pwh777@.hotmail.com> wrote in message
> news:1172253270.739022.146740@.8g2000cwh.googlegroups.com...
>> Thanks Mike. That's the information that I was looking for. The
>> ASPFAQ article does not mention cursors. Is the Recordset object
>> using the cursor?
> I must have been drinking that night. I thought ASPFAQ had an article
> comparing performance of different paging techniques; apparently not.
> I'll keep looking for that article (maybe someone else remembers seeing it
> and can provide a link?)
> Depending on which technique you use, you may get a client-side cursor
> automatically, and it may even be backed up by a server-side cursor. (See
> SqlDataReader). Old ADO was famous for its use of cursors to get at the
> data, although its been a while so I'd have to look up the Recordset
> object specs to find out for sure in that code. I would suspect that it
> is using a client-side cursor, at least.
> Here's a couple more links for you:
> http://www.4guysfromrolla.com/webtech/042606-1.shtml
> http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
> Google up some "SQL Server paging speed" or some such... This problem has
> been attacked by a lot of people all over the place through the years, so
> there's a lot of good info. out there on it.
>|||For some reason, the data at the end of the article (including the important
summary of performance comparisons) shows up blank. You can get it if you
download the PDF of all articles from http://www.aspfaq.com/downloads.asp
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eTQ%23493VHHA.5060@.TK2MSFTNGP06.phx.gbl...
>> I must have been drinking that night. I thought ASPFAQ had an article
>> comparing performance of different paging techniques;
> It does;
> http://www.aspfaq.com/2120
> A
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eTQ%23493VHHA.5060@.TK2MSFTNGP06.phx.gbl...
>> I must have been drinking that night. I thought ASPFAQ had an article
>> comparing performance of different paging techniques;
> It does;
> http://www.aspfaq.com/2120
>
I thought your article also showed performance differences between
server-side paging techniques like using dynamic SQL, cursors, temp tables,
etc. in a side-by-side format. I'm probably confusing your article with
another one I've seen somewhere, but I'll be darned if I'm able to find it
now. Or it might have just been a heavy night of drinking :)|||>> It does;
>> http://www.aspfaq.com/2120
> I thought your article also showed performance differences between
> server-side paging techniques like using dynamic SQL, cursors, temp
> tables, etc. in a side-by-side format.
It did (see my follow-up).
I talked to the site owners and they were supposed to have fixed it today,
but haven't yet.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OlVBKX9VHHA.392@.TK2MSFTNGP06.phx.gbl...
> It did (see my follow-up).
> I talked to the site owners and they were supposed to have fixed it today,
> but haven't yet.
I thought you owned it? What's going on 'round here? :)|||>> I talked to the site owners and they were supposed to have fixed it
>> today, but haven't yet.
> I thought you owned it? What's going on 'round here? :)
No, I handed it over to new owners last year.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23gwTXeOWHHA.3568@.TK2MSFTNGP06.phx.gbl...
> No, I handed it over to new owners last year.
Well that's a kick in the pants! Hope you made a killin' :)|||>> No, I handed it over to new owners last year.
> Well that's a kick in the pants! Hope you made a killin' :)
Sure, but I'll be paying dearly for it on April 15th. :-)|||>> No, I handed it over to new owners last year.
> Well that's a kick in the pants! Hope you made a killin' :)
Sure, but I'll be paying dearly for it on April 15th. :-)|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23A7bClqWHHA.3332@.TK2MSFTNGP04.phx.gbl...
> Sure, but I'll be paying dearly for it on April 15th. :-)
Sounds like it's time for a trip to Vegas... They'll help you if you have
too much money :)
Paging Advice Needed
Dear Group
A while ago I've asked how paging is possible and have read the articles on
aspfaq.com. Great work, esp. the speed comparison of all the different
techniques. Thank you to everyone who has responded to my post. Somewhow
Google didn't let me reply to the thread!
All of the methods described work perfectly fine until I'm trying to
implement a WHERE or ORDER BY DESC on a particular field. E.g. Create all
pages but sort on a date field DESC doesn't start with the lowest date on
the first page and the highest date on the last page but all dates mixed up.
As a workaround I'm at first using a cursor to populate a temporary table
e.g. SELECT * FROM MyTable WHERE MyField1 = Condition ORDER BY MyField2
which only contains the data I would like to use to create the pages. I'm
using a cursor since I read that a normal SELECT to poulate the temporary
table doesn't always guarantee that rows are inserted in the correct order.
Next I'm creating the individual pages with the Count and Page.Rank method
off the temporary table.
This method is not the best in performance and I'm sure there must be
another
way to perform paging with filtering and sorting. I'm grateful for any tipps
you have.
Thanks for your time & efforts!
MartinHi!
Just letting you know that I've found an answer to my problem at
http://weblogs.asp.net/pwilson/arch...0/10/31456.aspx
titled 'Sorting and Paging in SQL Server' (including Filtering).
It's easy to use and pretty fast!
Martin
"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:c5rqu3$efu$1@.titan.btinternet.com...
> Dear Group
> A while ago I've asked how paging is possible and have read the articles
on
> aspfaq.com. Great work, esp. the speed comparison of all the different
> techniques. Thank you to everyone who has responded to my post. Somewhow
> Google didn't let me reply to the thread!
> All of the methods described work perfectly fine until I'm trying to
> implement a WHERE or ORDER BY DESC on a particular field. E.g. Create all
> pages but sort on a date field DESC doesn't start with the lowest date on
> the first page and the highest date on the last page but all dates mixed
up.
> As a workaround I'm at first using a cursor to populate a temporary table
> e.g. SELECT * FROM MyTable WHERE MyField1 = Condition ORDER BY MyField2
> which only contains the data I would like to use to create the pages. I'm
> using a cursor since I read that a normal SELECT to poulate the temporary
> table doesn't always guarantee that rows are inserted in the correct
order.
> Next I'm creating the individual pages with the Count and Page.Rank method
> off the temporary table.
> This method is not the best in performance and I'm sure there must be
> another
> way to perform paging with filtering and sorting. I'm grateful for any
tipps
> you have.
> Thanks for your time & efforts!
> Martin
Paging (Performance)
Hello, I have incorporated a paging query in my software. I got the query from here:
http://rosca.net/writing/articles/serverside_paging.asp
My web software ususlly responded in .005 - .02 seconds with about a 100 rows of data. When I put simulated data on my database I added about 2 million rows. when I did this -- every page that did not execute the paging query responded lightning fast. But the webpages that executed the paging query took over 5 seconds. I dont understand why this paging query brought my web application to its knees.
Does anyone know of a more efficient way to do paging. I have SQL server 2000. If it may be easier I can upgrade to SQL 2005. PLZ Let me know. Thankshave a look at these two blog posts (focus on the more recent post):
http://weblogs.sqlteam.com/jeffs/category/162.aspx
theres a lot of info in the blog comments as well, and also links to other methods (for example, http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html)
http://rosca.net/writing/articles/serverside_paging.asp
My web software ususlly responded in .005 - .02 seconds with about a 100 rows of data. When I put simulated data on my database I added about 2 million rows. when I did this -- every page that did not execute the paging query responded lightning fast. But the webpages that executed the paging query took over 5 seconds. I dont understand why this paging query brought my web application to its knees.
Does anyone know of a more efficient way to do paging. I have SQL server 2000. If it may be easier I can upgrade to SQL 2005. PLZ Let me know. Thankshave a look at these two blog posts (focus on the more recent post):
http://weblogs.sqlteam.com/jeffs/category/162.aspx
theres a lot of info in the blog comments as well, and also links to other methods (for example, http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html)
Saturday, February 25, 2012
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" ; "Pag
e
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> 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 'procdures 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 ?) o
r
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:
[vbcol=seagreen]
> With an increasing column value based on IDENTITY or GETDATE(), a clustere
d
> index page split occurs during inserts only when the last page in the tabl
e
> 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 t
he
> 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.
>
> 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...|||Official word is that they will consider it for the next release. Feel free
to cast your vote:
https://connect.microsoft.com/SQLSe...=1261
48
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:
[vbcol=seagreen]
> 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.
>
> 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...
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" ; "Pag
e
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> 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 'procdures 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 ?) o
r
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:
[vbcol=seagreen]
> With an increasing column value based on IDENTITY or GETDATE(), a clustere
d
> index page split occurs during inserts only when the last page in the tabl
e
> 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 t
he
> 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.
>
> 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...|||Official word is that they will consider it for the next release. Feel free
to cast your vote:
https://connect.microsoft.com/SQLSe...=1261
48
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:
[vbcol=seagreen]
> 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.
>
> 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...
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
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
Subscribe to:
Posts (Atom)