Hi, When I look at the script generated from the ITW it often doesn't
have all the recomendations I see on the recommendations page. Is
this normal? The doc on the wiz seems a little thin...Johnny,
Check out:
http://www.microsoft.com/technet/pr...in/tunesql.mspx
and
http://www.sql-server-performance.c...wizard_tips.asp
and
http://msdn.microsoft.com/library/d...>
_05_0cbo.asp
HTH
Jerry
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1128634228.482567.300780@.z14g2000cwz.googlegroups.com...
> Hi, When I look at the script generated from the ITW it often doesn't
> have all the recomendations I see on the recommendations page. Is
> this normal? The doc on the wiz seems a little thin...
>|||Quite Frankly the 'brain' behind the ITW is quite this too.
I recommend you do your own analysis, set a benchmark, evaluate indices and
speed, factor in processors, competing resources, SQL cache etc etc.
You're more likely to get a better efficiency should you tackle the design
yourself (albeit you could use the ITW as a guide but it's scope is very
limited) and a better understanding of your data and query requirements.sql
Showing posts with label page. Show all posts
Showing posts with label page. Show all posts
Friday, March 30, 2012
Friday, March 9, 2012
Index Fragementing Rapidly
I'm a little confused over something that keeps happinging to my database.
I have a web app that insert a row into a SS2K table every time a page is viewed. It's an intranet site, and I maybe average one page a second, no big deal. THe table uses an Indentity field, and has a clustered index on that Indenty Field alone.
Most of the time this works fine, absolutely no problems, no slow down, etc. However, from time to time the insert into this table will "lock up" - take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
When I look at the tables, using DBCC SHOWCONTIG, the logical fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that generally takes care of the problem. I've setup a job to run nightly that runs the following sql command: DBCC DBREINDEX (
'Activity','',70).
Here's my question: This will happen suddenly (i.e. one page request, no problem, the next, lock up). Why does my index fragment so rapidly? I would expect a "build up". Could something else be going on that I'm missing?
Hi,
Info from Books online:-
Table fragmentation occurs through the process of data modifications
(INSERT, UPDATE, and DELETE statements) made against the table.
Because these modifications are not usually distributed equally among the
rows of the table, the fullness of each page can vary over
time causing fragments.For queries that scan part or all of a table, such
table fragmentation can cause additional page reads,
which hinders parallel scanning of data.
Thanks
Hari
MCDBA
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> I'm a little confused over something that keeps happinging to my database.
> I have a web app that insert a row into a SS2K table every time a page is
viewed. It's an intranet site, and I maybe average one page a second, no
big deal. THe table uses an Indentity field, and has a clustered index on
that Indenty Field alone.
> Most of the time this works fine, absolutely no problems, no slow down,
etc. However, from time to time the insert into this table will "lock up" -
take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> When I look at the tables, using DBCC SHOWCONTIG, the logical
fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
generally takes care of the problem. I've setup a job to run nightly that
runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> Here's my question: This will happen suddenly (i.e. one page request, no
problem, the next, lock up). Why does my index fragment so rapidly? I
would expect a "build up". Could something else be going on that I'm
missing?
|||Not sure if that is his problem. He says that he has an identity column,
which is clustered. This means that each insert will be added to the end of
the table. Maybe the hang is occurring when the database needs to grow.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u2mp%23t$bEHA.212@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi,
> Info from Books online:-
> Table fragmentation occurs through the process of data modifications
> (INSERT, UPDATE, and DELETE statements) made against the table.
> Because these modifications are not usually distributed equally among the
> rows of the table, the fullness of each page can vary over
> time causing fragments.For queries that scan part or all of a table, such
> table fragmentation can cause additional page reads,
> which hinders parallel scanning of data.
> Thanks
> Hari
> MCDBA
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
database.[vbcol=seagreen]
is
> viewed. It's an intranet site, and I maybe average one page a second, no
> big deal. THe table uses an Indentity field, and has a clustered index on
> that Indenty Field alone.
> etc. However, from time to time the insert into this table will "lock
up" -[vbcol=seagreen]
> take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
> generally takes care of the problem. I've setup a job to run nightly that
> runs the following sql command: DBCC DBREINDEX ('Activity','',70).
no
> problem, the next, lock up). Why does my index fragment so rapidly? I
> would expect a "build up". Could something else be going on that I'm
> missing?
>
|||But why does my table fragment so quickly? I have approx. 400,000 records in the table, with, on average, about 1 row being added a second and it appears to go from being < 1% fragment to > 90% in a matter of seconds (all after running fine for hours / d
ays without reindexing).
THe problem is that all of the sudden the website goes from working fine to completely shut down in a matter of seconds.
"Hari Prasad" wrote:
> Hi,
> Info from Books online:-
> Table fragmentation occurs through the process of data modifications
> (INSERT, UPDATE, and DELETE statements) made against the table.
> Because these modifications are not usually distributed equally among the
> rows of the table, the fullness of each page can vary over
> time causing fragments.For queries that scan part or all of a table, such
> table fragmentation can cause additional page reads,
> which hinders parallel scanning of data.
> Thanks
> Hari
> MCDBA
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> viewed. It's an intranet site, and I maybe average one page a second, no
> big deal. THe table uses an Indentity field, and has a clustered index on
> that Indenty Field alone.
> etc. However, from time to time the insert into this table will "lock up" -
> take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
> generally takes care of the problem. I've setup a job to run nightly that
> runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> problem, the next, lock up). Why does my index fragment so rapidly? I
> would expect a "build up". Could something else be going on that I'm
> missing?
>
>
|||looks like your fill factor is set to 70% ? knock it down to say 50% and see
if that helps.
Greg Jackson
PDX, Oregon
|||Perhaps it has less to do with the fragmentation than when
your pad index/fill factor gets to 100%. When you hit
100% one add'l row will cause a cascade, won't it?
>--Original Message--
>But why does my table fragment so quickly? I have
approx. 400,000 records in the table, with, on average,
about 1 row being added a second and it appears to go from
being < 1% fragment to > 90% in a matter of seconds (all
after running fine for hours / days without reindexing).
>THe problem is that all of the sudden the website goes
from working fine to completely shut down in a matter of
seconds.[vbcol=seagreen]
>"Hari Prasad" wrote:
modifications[vbcol=seagreen]
the table.[vbcol=seagreen]
equally among the[vbcol=seagreen]
over[vbcol=seagreen]
all of a table, such[vbcol=seagreen]
<KarlPierburg@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
3BD40FE07664@.microsoft.com...[vbcol=seagreen]
happinging to my database.[vbcol=seagreen]
every time a page is[vbcol=seagreen]
page a second, no[vbcol=seagreen]
clustered index on[vbcol=seagreen]
problems, no slow down,[vbcol=seagreen]
table will "lock up" -[vbcol=seagreen]
shuts down my website.[vbcol=seagreen]
logical[vbcol=seagreen]
INDEXDEFRAG, and that[vbcol=seagreen]
to run nightly that[vbcol=seagreen]
('Activity','',70).[vbcol=seagreen]
one page request, no[vbcol=seagreen]
fragment so rapidly? I[vbcol=seagreen]
going on that I'm
>.
>
|||Karl,
Are you sure there isn't a shrink operation going on? DO you have a Job
scheduled to do a shrink or worse yet is AutoShrink turned on? A clustered
index on an Identity column will not cause splits or fragmentation with just
inserts. Do you update these rows after they are inserted?
Andrew J. Kelly SQL MVP
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:1671C010-B598-4EC4-8225-0FBAE90CB611@.microsoft.com...
> But why does my table fragment so quickly? I have approx. 400,000 records
in the table, with, on average, about 1 row being added a second and it
appears to go from being < 1% fragment to > 90% in a matter of seconds (all
after running fine for hours / days without reindexing).
> THe problem is that all of the sudden the website goes from working fine
to completely shut down in a matter of seconds.[vbcol=seagreen]
> "Hari Prasad" wrote:
the[vbcol=seagreen]
such[vbcol=seagreen]
message[vbcol=seagreen]
database.[vbcol=seagreen]
is[vbcol=seagreen]
no[vbcol=seagreen]
on[vbcol=seagreen]
down,[vbcol=seagreen]
up" -[vbcol=seagreen]
website.[vbcol=seagreen]
that[vbcol=seagreen]
no[vbcol=seagreen]
|||Decreasing fill factor will not help at all, since all new inserts are going
to the last page...
Do as Andew says, a shrink will definitely frag up a table...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> I'm a little confused over something that keeps happinging to my database.
> I have a web app that insert a row into a SS2K table every time a page is
viewed. It's an intranet site, and I maybe average one page a second, no
big deal. THe table uses an Indentity field, and has a clustered index on
that Indenty Field alone.
> Most of the time this works fine, absolutely no problems, no slow down,
etc. However, from time to time the insert into this table will "lock up" -
take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> When I look at the tables, using DBCC SHOWCONTIG, the logical
fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
generally takes care of the problem. I've setup a job to run nightly that
runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> Here's my question: This will happen suddenly (i.e. one page request, no
problem, the next, lock up). Why does my index fragment so rapidly? I
would expect a "build up". Could something else be going on that I'm
missing?
|||Andrew-
Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly processing. I just read a post that you were involved with that basically said "don't do that".
I will take it out. It's just a weird problem that happens with no regularity.
These rows are NEVER updated or deleted. Some basic read-only reporting is all that takes place.
Could you comment on 2 things:
1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDENTITY fields. It seems to me that I would want to speficy a fill factor of 100%, since I'll never do any inserts earlier in the page / extent?
"Andrew J. Kelly" wrote:
> Karl,
> Are you sure there isn't a shrink operation going on? DO you have a Job
> scheduled to do a shrink or worse yet is AutoShrink turned on? A clustered
> index on an Identity column will not cause splits or fragmentation with just
> inserts. Do you update these rows after they are inserted?
> --
> Andrew J. Kelly SQL MVP
>
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:1671C010-B598-4EC4-8225-0FBAE90CB611@.microsoft.com...
> in the table, with, on average, about 1 row being added a second and it
> appears to go from being < 1% fragment to > 90% in a matter of seconds (all
> after running fine for hours / days without reindexing).
> to completely shut down in a matter of seconds.
> the
> such
> message
> database.
> is
> no
> on
> down,
> up" -
> website.
> that
> no
>
>
|||> 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
To shrink the file it must move any pages at the end of the physical file to
someplace near the beginning since the shrink happens from the end inward.
This for one is a very expensive operation in terms of resources and
logging. But chances are after the move the data that was so nicely
defragged and contiguous earlier (by the reindexing) is now spread all over
the file where ever SQL Server had a place to put the extents. This is
usually mixed in amongst all the other extents and causes extent
fragmentation. But then later that night you reindex the tables again and
this forces the database to grow and starts the whole process all over
again. Put lots of free space in the data files and leave it there.
> 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on
INDENTITY fields. It seems to me that I would want to specify a fill factor
of 100%, since I'll never do any inserts earlier in the page / extent?
Yes in your case you probably do want 100%. The new rows will be appended
and will never grow. This is an ideal situation for keeping the
fragmentation and reads to a minimum. I have to believe Autoshrink was
kicking in and not only freezing your database (so it seemed) but
fragmenting the tables as well.
Andrew J. Kelly SQL MVP
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:852300A1-859C-41DC-A047-F12C87F61D11@.microsoft.com...
> Andrew-
> Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly
processing. I just read a post that you were involved with that basically
said "don't do that".
> I will take it out. It's just a weird problem that happens with no
regularity.
> These rows are NEVER updated or deleted. Some basic read-only reporting
is all that takes place.
> Could you comment on 2 things:
> 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
> 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDEN
TITY fields. It seems to me that I would want to speficy a fill factor of
100%, since I'll never do any inserts earlier in the page / extent?[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
clustered[vbcol=seagreen]
just[vbcol=seagreen]
message[vbcol=seagreen]
records[vbcol=seagreen]
(all[vbcol=seagreen]
fine[vbcol=seagreen]
among[vbcol=seagreen]
page[vbcol=seagreen]
second,[vbcol=seagreen]
index[vbcol=seagreen]
"lock[vbcol=seagreen]
that[vbcol=seagreen]
nightly[vbcol=seagreen]
request,[vbcol=seagreen]
rapidly? I[vbcol=seagreen]
I'm[vbcol=seagreen]
I have a web app that insert a row into a SS2K table every time a page is viewed. It's an intranet site, and I maybe average one page a second, no big deal. THe table uses an Indentity field, and has a clustered index on that Indenty Field alone.
Most of the time this works fine, absolutely no problems, no slow down, etc. However, from time to time the insert into this table will "lock up" - take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
When I look at the tables, using DBCC SHOWCONTIG, the logical fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that generally takes care of the problem. I've setup a job to run nightly that runs the following sql command: DBCC DBREINDEX (
'Activity','',70).
Here's my question: This will happen suddenly (i.e. one page request, no problem, the next, lock up). Why does my index fragment so rapidly? I would expect a "build up". Could something else be going on that I'm missing?
Hi,
Info from Books online:-
Table fragmentation occurs through the process of data modifications
(INSERT, UPDATE, and DELETE statements) made against the table.
Because these modifications are not usually distributed equally among the
rows of the table, the fullness of each page can vary over
time causing fragments.For queries that scan part or all of a table, such
table fragmentation can cause additional page reads,
which hinders parallel scanning of data.
Thanks
Hari
MCDBA
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> I'm a little confused over something that keeps happinging to my database.
> I have a web app that insert a row into a SS2K table every time a page is
viewed. It's an intranet site, and I maybe average one page a second, no
big deal. THe table uses an Indentity field, and has a clustered index on
that Indenty Field alone.
> Most of the time this works fine, absolutely no problems, no slow down,
etc. However, from time to time the insert into this table will "lock up" -
take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> When I look at the tables, using DBCC SHOWCONTIG, the logical
fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
generally takes care of the problem. I've setup a job to run nightly that
runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> Here's my question: This will happen suddenly (i.e. one page request, no
problem, the next, lock up). Why does my index fragment so rapidly? I
would expect a "build up". Could something else be going on that I'm
missing?
|||Not sure if that is his problem. He says that he has an identity column,
which is clustered. This means that each insert will be added to the end of
the table. Maybe the hang is occurring when the database needs to grow.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u2mp%23t$bEHA.212@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi,
> Info from Books online:-
> Table fragmentation occurs through the process of data modifications
> (INSERT, UPDATE, and DELETE statements) made against the table.
> Because these modifications are not usually distributed equally among the
> rows of the table, the fullness of each page can vary over
> time causing fragments.For queries that scan part or all of a table, such
> table fragmentation can cause additional page reads,
> which hinders parallel scanning of data.
> Thanks
> Hari
> MCDBA
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
database.[vbcol=seagreen]
is
> viewed. It's an intranet site, and I maybe average one page a second, no
> big deal. THe table uses an Indentity field, and has a clustered index on
> that Indenty Field alone.
> etc. However, from time to time the insert into this table will "lock
up" -[vbcol=seagreen]
> take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
> generally takes care of the problem. I've setup a job to run nightly that
> runs the following sql command: DBCC DBREINDEX ('Activity','',70).
no
> problem, the next, lock up). Why does my index fragment so rapidly? I
> would expect a "build up". Could something else be going on that I'm
> missing?
>
|||But why does my table fragment so quickly? I have approx. 400,000 records in the table, with, on average, about 1 row being added a second and it appears to go from being < 1% fragment to > 90% in a matter of seconds (all after running fine for hours / d
ays without reindexing).
THe problem is that all of the sudden the website goes from working fine to completely shut down in a matter of seconds.
"Hari Prasad" wrote:
> Hi,
> Info from Books online:-
> Table fragmentation occurs through the process of data modifications
> (INSERT, UPDATE, and DELETE statements) made against the table.
> Because these modifications are not usually distributed equally among the
> rows of the table, the fullness of each page can vary over
> time causing fragments.For queries that scan part or all of a table, such
> table fragmentation can cause additional page reads,
> which hinders parallel scanning of data.
> Thanks
> Hari
> MCDBA
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> viewed. It's an intranet site, and I maybe average one page a second, no
> big deal. THe table uses an Indentity field, and has a clustered index on
> that Indenty Field alone.
> etc. However, from time to time the insert into this table will "lock up" -
> take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
> generally takes care of the problem. I've setup a job to run nightly that
> runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> problem, the next, lock up). Why does my index fragment so rapidly? I
> would expect a "build up". Could something else be going on that I'm
> missing?
>
>
|||looks like your fill factor is set to 70% ? knock it down to say 50% and see
if that helps.
Greg Jackson
PDX, Oregon
|||Perhaps it has less to do with the fragmentation than when
your pad index/fill factor gets to 100%. When you hit
100% one add'l row will cause a cascade, won't it?
>--Original Message--
>But why does my table fragment so quickly? I have
approx. 400,000 records in the table, with, on average,
about 1 row being added a second and it appears to go from
being < 1% fragment to > 90% in a matter of seconds (all
after running fine for hours / days without reindexing).
>THe problem is that all of the sudden the website goes
from working fine to completely shut down in a matter of
seconds.[vbcol=seagreen]
>"Hari Prasad" wrote:
modifications[vbcol=seagreen]
the table.[vbcol=seagreen]
equally among the[vbcol=seagreen]
over[vbcol=seagreen]
all of a table, such[vbcol=seagreen]
<KarlPierburg@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
3BD40FE07664@.microsoft.com...[vbcol=seagreen]
happinging to my database.[vbcol=seagreen]
every time a page is[vbcol=seagreen]
page a second, no[vbcol=seagreen]
clustered index on[vbcol=seagreen]
problems, no slow down,[vbcol=seagreen]
table will "lock up" -[vbcol=seagreen]
shuts down my website.[vbcol=seagreen]
logical[vbcol=seagreen]
INDEXDEFRAG, and that[vbcol=seagreen]
to run nightly that[vbcol=seagreen]
('Activity','',70).[vbcol=seagreen]
one page request, no[vbcol=seagreen]
fragment so rapidly? I[vbcol=seagreen]
going on that I'm
>.
>
|||Karl,
Are you sure there isn't a shrink operation going on? DO you have a Job
scheduled to do a shrink or worse yet is AutoShrink turned on? A clustered
index on an Identity column will not cause splits or fragmentation with just
inserts. Do you update these rows after they are inserted?
Andrew J. Kelly SQL MVP
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:1671C010-B598-4EC4-8225-0FBAE90CB611@.microsoft.com...
> But why does my table fragment so quickly? I have approx. 400,000 records
in the table, with, on average, about 1 row being added a second and it
appears to go from being < 1% fragment to > 90% in a matter of seconds (all
after running fine for hours / days without reindexing).
> THe problem is that all of the sudden the website goes from working fine
to completely shut down in a matter of seconds.[vbcol=seagreen]
> "Hari Prasad" wrote:
the[vbcol=seagreen]
such[vbcol=seagreen]
message[vbcol=seagreen]
database.[vbcol=seagreen]
is[vbcol=seagreen]
no[vbcol=seagreen]
on[vbcol=seagreen]
down,[vbcol=seagreen]
up" -[vbcol=seagreen]
website.[vbcol=seagreen]
that[vbcol=seagreen]
no[vbcol=seagreen]
|||Decreasing fill factor will not help at all, since all new inserts are going
to the last page...
Do as Andew says, a shrink will definitely frag up a table...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> I'm a little confused over something that keeps happinging to my database.
> I have a web app that insert a row into a SS2K table every time a page is
viewed. It's an intranet site, and I maybe average one page a second, no
big deal. THe table uses an Indentity field, and has a clustered index on
that Indenty Field alone.
> Most of the time this works fine, absolutely no problems, no slow down,
etc. However, from time to time the insert into this table will "lock up" -
take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> When I look at the tables, using DBCC SHOWCONTIG, the logical
fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
generally takes care of the problem. I've setup a job to run nightly that
runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> Here's my question: This will happen suddenly (i.e. one page request, no
problem, the next, lock up). Why does my index fragment so rapidly? I
would expect a "build up". Could something else be going on that I'm
missing?
|||Andrew-
Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly processing. I just read a post that you were involved with that basically said "don't do that".
I will take it out. It's just a weird problem that happens with no regularity.
These rows are NEVER updated or deleted. Some basic read-only reporting is all that takes place.
Could you comment on 2 things:
1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDENTITY fields. It seems to me that I would want to speficy a fill factor of 100%, since I'll never do any inserts earlier in the page / extent?
"Andrew J. Kelly" wrote:
> Karl,
> Are you sure there isn't a shrink operation going on? DO you have a Job
> scheduled to do a shrink or worse yet is AutoShrink turned on? A clustered
> index on an Identity column will not cause splits or fragmentation with just
> inserts. Do you update these rows after they are inserted?
> --
> Andrew J. Kelly SQL MVP
>
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:1671C010-B598-4EC4-8225-0FBAE90CB611@.microsoft.com...
> in the table, with, on average, about 1 row being added a second and it
> appears to go from being < 1% fragment to > 90% in a matter of seconds (all
> after running fine for hours / days without reindexing).
> to completely shut down in a matter of seconds.
> the
> such
> message
> database.
> is
> no
> on
> down,
> up" -
> website.
> that
> no
>
>
|||> 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
To shrink the file it must move any pages at the end of the physical file to
someplace near the beginning since the shrink happens from the end inward.
This for one is a very expensive operation in terms of resources and
logging. But chances are after the move the data that was so nicely
defragged and contiguous earlier (by the reindexing) is now spread all over
the file where ever SQL Server had a place to put the extents. This is
usually mixed in amongst all the other extents and causes extent
fragmentation. But then later that night you reindex the tables again and
this forces the database to grow and starts the whole process all over
again. Put lots of free space in the data files and leave it there.
> 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on
INDENTITY fields. It seems to me that I would want to specify a fill factor
of 100%, since I'll never do any inserts earlier in the page / extent?
Yes in your case you probably do want 100%. The new rows will be appended
and will never grow. This is an ideal situation for keeping the
fragmentation and reads to a minimum. I have to believe Autoshrink was
kicking in and not only freezing your database (so it seemed) but
fragmenting the tables as well.
Andrew J. Kelly SQL MVP
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:852300A1-859C-41DC-A047-F12C87F61D11@.microsoft.com...
> Andrew-
> Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly
processing. I just read a post that you were involved with that basically
said "don't do that".
> I will take it out. It's just a weird problem that happens with no
regularity.
> These rows are NEVER updated or deleted. Some basic read-only reporting
is all that takes place.
> Could you comment on 2 things:
> 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
> 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDEN
TITY fields. It seems to me that I would want to speficy a fill factor of
100%, since I'll never do any inserts earlier in the page / extent?[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
clustered[vbcol=seagreen]
just[vbcol=seagreen]
message[vbcol=seagreen]
records[vbcol=seagreen]
(all[vbcol=seagreen]
fine[vbcol=seagreen]
among[vbcol=seagreen]
page[vbcol=seagreen]
second,[vbcol=seagreen]
index[vbcol=seagreen]
"lock[vbcol=seagreen]
that[vbcol=seagreen]
nightly[vbcol=seagreen]
request,[vbcol=seagreen]
rapidly? I[vbcol=seagreen]
I'm[vbcol=seagreen]
Index Fragementing Rapidly
I'm a little confused over something that keeps happinging to my database.
I have a web app that insert a row into a SS2K table every time a page is vi
ewed. It's an intranet site, and I maybe average one page a second, no big
deal. THe table uses an Indentity field, and has a clustered index on that
Indenty Field alone.
Most of the time this works fine, absolutely no problems, no slow down, etc.
However, from time to time the insert into this table will "lock up" - tak
e 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
When I look at the tables, using DBCC SHOWCONTIG, the logical fragmentation
is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that generally takes care
of the problem. I've setup a job to run nightly that runs the following sq
l command: DBCC DBREINDEX (
'Activity','',70).
Here's my question: This will happen suddenly (i.e. one page request, no pr
oblem, the next, lock up). Why does my index fragment so rapidly? I would
expect a "build up". Could something else be going on that I'm missing?Hi,
Info from Books online:-
Table fragmentation occurs through the process of data modifications
(INSERT, UPDATE, and DELETE statements) made against the table.
Because these modifications are not usually distributed equally among the
rows of the table, the fullness of each page can vary over
time causing fragments.For queries that scan part or all of a table, such
table fragmentation can cause additional page reads,
which hinders parallel scanning of data.
Thanks
Hari
MCDBA
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> I'm a little confused over something that keeps happinging to my database.
> I have a web app that insert a row into a SS2K table every time a page is
viewed. It's an intranet site, and I maybe average one page a second, no
big deal. THe table uses an Indentity field, and has a clustered index on
that Indenty Field alone.
> Most of the time this works fine, absolutely no problems, no slow down,
etc. However, from time to time the insert into this table will "lock up" -
take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> When I look at the tables, using DBCC SHOWCONTIG, the logical
fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
generally takes care of the problem. I've setup a job to run nightly that
runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> Here's my question: This will happen suddenly (i.e. one page request, no
problem, the next, lock up). Why does my index fragment so rapidly? I
would expect a "build up". Could something else be going on that I'm
missing?|||Not sure if that is his problem. He says that he has an identity column,
which is clustered. This means that each insert will be added to the end of
the table. Maybe the hang is occurring when the database needs to grow.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u2mp%23t$bEHA.212@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Info from Books online:-
> Table fragmentation occurs through the process of data modifications
> (INSERT, UPDATE, and DELETE statements) made against the table.
> Because these modifications are not usually distributed equally among the
> rows of the table, the fullness of each page can vary over
> time causing fragments.For queries that scan part or all of a table, such
> table fragmentation can cause additional page reads,
> which hinders parallel scanning of data.
> Thanks
> Hari
> MCDBA
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
database.[vbcol=seagreen]
is[vbcol=seagreen]
> viewed. It's an intranet site, and I maybe average one page a second, no
> big deal. THe table uses an Indentity field, and has a clustered index on
> that Indenty Field alone.
> etc. However, from time to time the insert into this table will "lock
up" -
> take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
> generally takes care of the problem. I've setup a job to run nightly that
> runs the following sql command: DBCC DBREINDEX ('Activity','',70).
no[vbcol=seagreen]
> problem, the next, lock up). Why does my index fragment so rapidly? I
> would expect a "build up". Could something else be going on that I'm
> missing?
>|||But why does my table fragment so quickly? I have approx. 400,000 records i
n the table, with, on average, about 1 row being added a second and it appea
rs to go from being < 1% fragment to > 90% in a matter of seconds (all after
running fine for hours / d
ays without reindexing).
THe problem is that all of the sudden the website goes from working fine to
completely shut down in a matter of seconds.
"Hari Prasad" wrote:
> Hi,
> Info from Books online:-
> Table fragmentation occurs through the process of data modifications
> (INSERT, UPDATE, and DELETE statements) made against the table.
> Because these modifications are not usually distributed equally among the
> rows of the table, the fullness of each page can vary over
> time causing fragments.For queries that scan part or all of a table, such
> table fragmentation can cause additional page reads,
> which hinders parallel scanning of data.
> Thanks
> Hari
> MCDBA
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> viewed. It's an intranet site, and I maybe average one page a second, no
> big deal. THe table uses an Indentity field, and has a clustered index on
> that Indenty Field alone.
> etc. However, from time to time the insert into this table will "lock up"
-
> take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
> generally takes care of the problem. I've setup a job to run nightly that
> runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> problem, the next, lock up). Why does my index fragment so rapidly? I
> would expect a "build up". Could something else be going on that I'm
> missing?
>
>|||looks like your fill factor is set to 70% ? knock it down to say 50% and see
if that helps.
Greg Jackson
PDX, Oregon|||Perhaps it has less to do with the fragmentation than when
your pad index/fill factor gets to 100%. When you hit
100% one add'l row will cause a cascade, won't it?
>--Original Message--
>But why does my table fragment so quickly? I have
approx. 400,000 records in the table, with, on average,
about 1 row being added a second and it appears to go from
being < 1% fragment to > 90% in a matter of seconds (all
after running fine for hours / days without reindexing).
>THe problem is that all of the sudden the website goes
from working fine to completely shut down in a matter of
seconds.
>"Hari Prasad" wrote:
>
modifications[vbcol=seagreen]
the table.[vbcol=seagreen]
equally among the[vbcol=seagreen]
over[vbcol=seagreen]
all of a table, such[vbcol=seagreen]
<KarlPierburg@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
3BD40FE07664@.microsoft.com...[vbcol=seagreen]
happinging to my database.[vbcol=seagreen]
every time a page is[vbcol=seagreen]
page a second, no[vbcol=seagreen]
clustered index on[vbcol=seagreen]
problems, no slow down,[vbcol=seagreen]
table will "lock up" -[vbcol=seagreen]
shuts down my website.[vbcol=seagreen]
logical[vbcol=seagreen]
INDEXDEFRAG, and that[vbcol=seagreen]
to run nightly that[vbcol=seagreen]
('Activity','',70).[vbcol=seagreen]
one page request, no[vbcol=seagreen]
fragment so rapidly? I[vbcol=seagreen]
going on that I'm[vbcol=seagreen]
>.
>|||Karl,
Are you sure there isn't a shrink operation going on? DO you have a Job
scheduled to do a shrink or worse yet is AutoShrink turned on? A clustered
index on an Identity column will not cause splits or fragmentation with just
inserts. Do you update these rows after they are inserted?
Andrew J. Kelly SQL MVP
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:1671C010-B598-4EC4-8225-0FBAE90CB611@.microsoft.com...
> But why does my table fragment so quickly? I have approx. 400,000 records
in the table, with, on average, about 1 row being added a second and it
appears to go from being < 1% fragment to > 90% in a matter of seconds (all
after running fine for hours / days without reindexing).
> THe problem is that all of the sudden the website goes from working fine
to completely shut down in a matter of seconds.[vbcol=seagreen]
> "Hari Prasad" wrote:
>
the[vbcol=seagreen]
such[vbcol=seagreen]
message[vbcol=seagreen]
database.[vbcol=seagreen]
is[vbcol=seagreen]
no[vbcol=seagreen]
on[vbcol=seagreen]
down,[vbcol=seagreen]
up" -[vbcol=seagreen]
website.[vbcol=seagreen]
that[vbcol=seagreen]
no[vbcol=seagreen]|||Decreasing fill factor will not help at all, since all new inserts are going
to the last page...
Do as Andew says, a shrink will definitely frag up a table...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> I'm a little confused over something that keeps happinging to my database.
> I have a web app that insert a row into a SS2K table every time a page is
viewed. It's an intranet site, and I maybe average one page a second, no
big deal. THe table uses an Indentity field, and has a clustered index on
that Indenty Field alone.
> Most of the time this works fine, absolutely no problems, no slow down,
etc. However, from time to time the insert into this table will "lock up" -
take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> When I look at the tables, using DBCC SHOWCONTIG, the logical
fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
generally takes care of the problem. I've setup a job to run nightly that
runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> Here's my question: This will happen suddenly (i.e. one page request, no
problem, the next, lock up). Why does my index fragment so rapidly? I
would expect a "build up". Could something else be going on that I'm
missing?|||Andrew-
Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly pr
ocessing. I just read a post that you were involved with that basically sai
d "don't do that".
I will take it out. It's just a weird problem that happens with no regular
ity.
These rows are NEVER updated or deleted. Some basic read-only reporting is
all that takes place.
Could you comment on 2 things:
1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDENTI
TY fields. It seems to me that I would want to speficy a fill factor of 100
%, since I'll never do any inserts earlier in the page / extent?
"Andrew J. Kelly" wrote:
> Karl,
> Are you sure there isn't a shrink operation going on? DO you have a Job
> scheduled to do a shrink or worse yet is AutoShrink turned on? A clustere
d
> index on an Identity column will not cause splits or fragmentation with ju
st
> inserts. Do you update these rows after they are inserted?
> --
> Andrew J. Kelly SQL MVP
>
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:1671C010-B598-4EC4-8225-0FBAE90CB611@.microsoft.com...
> in the table, with, on average, about 1 row being added a second and it
> appears to go from being < 1% fragment to > 90% in a matter of seconds (al
l
> after running fine for hours / days without reindexing).
> to completely shut down in a matter of seconds.
> the
> such
> message
> database.
> is
> no
> on
> down,
> up" -
> website.
> that
> no
>
>|||> 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
To shrink the file it must move any pages at the end of the physical file to
someplace near the beginning since the shrink happens from the end inward.
This for one is a very expensive operation in terms of resources and
logging. But chances are after the move the data that was so nicely
defragged and contiguous earlier (by the reindexing) is now spread all over
the file where ever SQL Server had a place to put the extents. This is
usually mixed in amongst all the other extents and causes extent
fragmentation. But then later that night you reindex the tables again and
this forces the database to grow and starts the whole process all over
again. Put lots of free space in the data files and leave it there.
> 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on
INDENTITY fields. It seems to me that I would want to specify a fill factor
of 100%, since I'll never do any inserts earlier in the page / extent?
Yes in your case you probably do want 100%. The new rows will be appended
and will never grow. This is an ideal situation for keeping the
fragmentation and reads to a minimum. I have to believe Autoshrink was
kicking in and not only freezing your database (so it seemed) but
fragmenting the tables as well.
Andrew J. Kelly SQL MVP
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:852300A1-859C-41DC-A047-F12C87F61D11@.microsoft.com...
> Andrew-
> Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly
processing. I just read a post that you were involved with that basically
said "don't do that".
> I will take it out. It's just a weird problem that happens with no
regularity.
> These rows are NEVER updated or deleted. Some basic read-only reporting
is all that takes place.
> Could you comment on 2 things:
> 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
> 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDEN
TITY fields. It seems to me that I would want to speficy a fill factor of
100%, since I'll never do any inserts earlier in the page / extent?[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
>
clustered[vbcol=seagreen]
just[vbcol=seagreen]
message[vbcol=seagreen]
records[vbcol=seagreen]
(all[vbcol=seagreen]
fine[vbcol=seagreen]
among[vbcol=seagreen]
page[vbcol=seagreen]
second,[vbcol=seagreen]
index[vbcol=seagreen]
"lock[vbcol=seagreen]
that[vbcol=seagreen]
nightly[vbcol=seagreen]
request,[vbcol=seagreen]
rapidly? I[vbcol=seagreen]
I'm[vbcol=seagreen]
I have a web app that insert a row into a SS2K table every time a page is vi
ewed. It's an intranet site, and I maybe average one page a second, no big
deal. THe table uses an Indentity field, and has a clustered index on that
Indenty Field alone.
Most of the time this works fine, absolutely no problems, no slow down, etc.
However, from time to time the insert into this table will "lock up" - tak
e 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
When I look at the tables, using DBCC SHOWCONTIG, the logical fragmentation
is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that generally takes care
of the problem. I've setup a job to run nightly that runs the following sq
l command: DBCC DBREINDEX (
'Activity','',70).
Here's my question: This will happen suddenly (i.e. one page request, no pr
oblem, the next, lock up). Why does my index fragment so rapidly? I would
expect a "build up". Could something else be going on that I'm missing?Hi,
Info from Books online:-
Table fragmentation occurs through the process of data modifications
(INSERT, UPDATE, and DELETE statements) made against the table.
Because these modifications are not usually distributed equally among the
rows of the table, the fullness of each page can vary over
time causing fragments.For queries that scan part or all of a table, such
table fragmentation can cause additional page reads,
which hinders parallel scanning of data.
Thanks
Hari
MCDBA
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> I'm a little confused over something that keeps happinging to my database.
> I have a web app that insert a row into a SS2K table every time a page is
viewed. It's an intranet site, and I maybe average one page a second, no
big deal. THe table uses an Indentity field, and has a clustered index on
that Indenty Field alone.
> Most of the time this works fine, absolutely no problems, no slow down,
etc. However, from time to time the insert into this table will "lock up" -
take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> When I look at the tables, using DBCC SHOWCONTIG, the logical
fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
generally takes care of the problem. I've setup a job to run nightly that
runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> Here's my question: This will happen suddenly (i.e. one page request, no
problem, the next, lock up). Why does my index fragment so rapidly? I
would expect a "build up". Could something else be going on that I'm
missing?|||Not sure if that is his problem. He says that he has an identity column,
which is clustered. This means that each insert will be added to the end of
the table. Maybe the hang is occurring when the database needs to grow.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u2mp%23t$bEHA.212@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Info from Books online:-
> Table fragmentation occurs through the process of data modifications
> (INSERT, UPDATE, and DELETE statements) made against the table.
> Because these modifications are not usually distributed equally among the
> rows of the table, the fullness of each page can vary over
> time causing fragments.For queries that scan part or all of a table, such
> table fragmentation can cause additional page reads,
> which hinders parallel scanning of data.
> Thanks
> Hari
> MCDBA
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
database.[vbcol=seagreen]
is[vbcol=seagreen]
> viewed. It's an intranet site, and I maybe average one page a second, no
> big deal. THe table uses an Indentity field, and has a clustered index on
> that Indenty Field alone.
> etc. However, from time to time the insert into this table will "lock
up" -
> take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
> generally takes care of the problem. I've setup a job to run nightly that
> runs the following sql command: DBCC DBREINDEX ('Activity','',70).
no[vbcol=seagreen]
> problem, the next, lock up). Why does my index fragment so rapidly? I
> would expect a "build up". Could something else be going on that I'm
> missing?
>|||But why does my table fragment so quickly? I have approx. 400,000 records i
n the table, with, on average, about 1 row being added a second and it appea
rs to go from being < 1% fragment to > 90% in a matter of seconds (all after
running fine for hours / d
ays without reindexing).
THe problem is that all of the sudden the website goes from working fine to
completely shut down in a matter of seconds.
"Hari Prasad" wrote:
> Hi,
> Info from Books online:-
> Table fragmentation occurs through the process of data modifications
> (INSERT, UPDATE, and DELETE statements) made against the table.
> Because these modifications are not usually distributed equally among the
> rows of the table, the fullness of each page can vary over
> time causing fragments.For queries that scan part or all of a table, such
> table fragmentation can cause additional page reads,
> which hinders parallel scanning of data.
> Thanks
> Hari
> MCDBA
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> viewed. It's an intranet site, and I maybe average one page a second, no
> big deal. THe table uses an Indentity field, and has a clustered index on
> that Indenty Field alone.
> etc. However, from time to time the insert into this table will "lock up"
-
> take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
> generally takes care of the problem. I've setup a job to run nightly that
> runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> problem, the next, lock up). Why does my index fragment so rapidly? I
> would expect a "build up". Could something else be going on that I'm
> missing?
>
>|||looks like your fill factor is set to 70% ? knock it down to say 50% and see
if that helps.
Greg Jackson
PDX, Oregon|||Perhaps it has less to do with the fragmentation than when
your pad index/fill factor gets to 100%. When you hit
100% one add'l row will cause a cascade, won't it?
>--Original Message--
>But why does my table fragment so quickly? I have
approx. 400,000 records in the table, with, on average,
about 1 row being added a second and it appears to go from
being < 1% fragment to > 90% in a matter of seconds (all
after running fine for hours / days without reindexing).
>THe problem is that all of the sudden the website goes
from working fine to completely shut down in a matter of
seconds.
>"Hari Prasad" wrote:
>
modifications[vbcol=seagreen]
the table.[vbcol=seagreen]
equally among the[vbcol=seagreen]
over[vbcol=seagreen]
all of a table, such[vbcol=seagreen]
<KarlPierburg@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
3BD40FE07664@.microsoft.com...[vbcol=seagreen]
happinging to my database.[vbcol=seagreen]
every time a page is[vbcol=seagreen]
page a second, no[vbcol=seagreen]
clustered index on[vbcol=seagreen]
problems, no slow down,[vbcol=seagreen]
table will "lock up" -[vbcol=seagreen]
shuts down my website.[vbcol=seagreen]
logical[vbcol=seagreen]
INDEXDEFRAG, and that[vbcol=seagreen]
to run nightly that[vbcol=seagreen]
('Activity','',70).[vbcol=seagreen]
one page request, no[vbcol=seagreen]
fragment so rapidly? I[vbcol=seagreen]
going on that I'm[vbcol=seagreen]
>.
>|||Karl,
Are you sure there isn't a shrink operation going on? DO you have a Job
scheduled to do a shrink or worse yet is AutoShrink turned on? A clustered
index on an Identity column will not cause splits or fragmentation with just
inserts. Do you update these rows after they are inserted?
Andrew J. Kelly SQL MVP
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:1671C010-B598-4EC4-8225-0FBAE90CB611@.microsoft.com...
> But why does my table fragment so quickly? I have approx. 400,000 records
in the table, with, on average, about 1 row being added a second and it
appears to go from being < 1% fragment to > 90% in a matter of seconds (all
after running fine for hours / days without reindexing).
> THe problem is that all of the sudden the website goes from working fine
to completely shut down in a matter of seconds.[vbcol=seagreen]
> "Hari Prasad" wrote:
>
the[vbcol=seagreen]
such[vbcol=seagreen]
message[vbcol=seagreen]
database.[vbcol=seagreen]
is[vbcol=seagreen]
no[vbcol=seagreen]
on[vbcol=seagreen]
down,[vbcol=seagreen]
up" -[vbcol=seagreen]
website.[vbcol=seagreen]
that[vbcol=seagreen]
no[vbcol=seagreen]|||Decreasing fill factor will not help at all, since all new inserts are going
to the last page...
Do as Andew says, a shrink will definitely frag up a table...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> I'm a little confused over something that keeps happinging to my database.
> I have a web app that insert a row into a SS2K table every time a page is
viewed. It's an intranet site, and I maybe average one page a second, no
big deal. THe table uses an Indentity field, and has a clustered index on
that Indenty Field alone.
> Most of the time this works fine, absolutely no problems, no slow down,
etc. However, from time to time the insert into this table will "lock up" -
take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> When I look at the tables, using DBCC SHOWCONTIG, the logical
fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
generally takes care of the problem. I've setup a job to run nightly that
runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> Here's my question: This will happen suddenly (i.e. one page request, no
problem, the next, lock up). Why does my index fragment so rapidly? I
would expect a "build up". Could something else be going on that I'm
missing?|||Andrew-
Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly pr
ocessing. I just read a post that you were involved with that basically sai
d "don't do that".
I will take it out. It's just a weird problem that happens with no regular
ity.
These rows are NEVER updated or deleted. Some basic read-only reporting is
all that takes place.
Could you comment on 2 things:
1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDENTI
TY fields. It seems to me that I would want to speficy a fill factor of 100
%, since I'll never do any inserts earlier in the page / extent?
"Andrew J. Kelly" wrote:
> Karl,
> Are you sure there isn't a shrink operation going on? DO you have a Job
> scheduled to do a shrink or worse yet is AutoShrink turned on? A clustere
d
> index on an Identity column will not cause splits or fragmentation with ju
st
> inserts. Do you update these rows after they are inserted?
> --
> Andrew J. Kelly SQL MVP
>
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:1671C010-B598-4EC4-8225-0FBAE90CB611@.microsoft.com...
> in the table, with, on average, about 1 row being added a second and it
> appears to go from being < 1% fragment to > 90% in a matter of seconds (al
l
> after running fine for hours / days without reindexing).
> to completely shut down in a matter of seconds.
> the
> such
> message
> database.
> is
> no
> on
> down,
> up" -
> website.
> that
> no
>
>|||> 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
To shrink the file it must move any pages at the end of the physical file to
someplace near the beginning since the shrink happens from the end inward.
This for one is a very expensive operation in terms of resources and
logging. But chances are after the move the data that was so nicely
defragged and contiguous earlier (by the reindexing) is now spread all over
the file where ever SQL Server had a place to put the extents. This is
usually mixed in amongst all the other extents and causes extent
fragmentation. But then later that night you reindex the tables again and
this forces the database to grow and starts the whole process all over
again. Put lots of free space in the data files and leave it there.
> 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on
INDENTITY fields. It seems to me that I would want to specify a fill factor
of 100%, since I'll never do any inserts earlier in the page / extent?
Yes in your case you probably do want 100%. The new rows will be appended
and will never grow. This is an ideal situation for keeping the
fragmentation and reads to a minimum. I have to believe Autoshrink was
kicking in and not only freezing your database (so it seemed) but
fragmenting the tables as well.
Andrew J. Kelly SQL MVP
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:852300A1-859C-41DC-A047-F12C87F61D11@.microsoft.com...
> Andrew-
> Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly
processing. I just read a post that you were involved with that basically
said "don't do that".
> I will take it out. It's just a weird problem that happens with no
regularity.
> These rows are NEVER updated or deleted. Some basic read-only reporting
is all that takes place.
> Could you comment on 2 things:
> 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
> 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDEN
TITY fields. It seems to me that I would want to speficy a fill factor of
100%, since I'll never do any inserts earlier in the page / extent?[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
>
clustered[vbcol=seagreen]
just[vbcol=seagreen]
message[vbcol=seagreen]
records[vbcol=seagreen]
(all[vbcol=seagreen]
fine[vbcol=seagreen]
among[vbcol=seagreen]
page[vbcol=seagreen]
second,[vbcol=seagreen]
index[vbcol=seagreen]
"lock[vbcol=seagreen]
that[vbcol=seagreen]
nightly[vbcol=seagreen]
request,[vbcol=seagreen]
rapidly? I[vbcol=seagreen]
I'm[vbcol=seagreen]
Wednesday, March 7, 2012
index error?
Hi,
We had a query return the index entry error below.
Could not find the index entry for RID '36020100000200000100100007410400' in
index page (1:2223497), index ID 0, database 'dpSUM001'..
I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
planned to run a rebuild index on the table later that night to fix the
problem. Later in the afternoon I ran the query again and this time it
returned data and didn't have the index entry error. I ran the DBCC
CHECKTABLE and it reported no errors.
Does anyone know what fixed the index errors? The database has the auto
update/create stats and torn page detection turned on. Could one of these
option fixed the errors?
Thanks Richard
No idea. Thats some thing new and good for you. But from your side make sure
that you execute DBCC CHECKDB regularly and ensure that database
is in good shape. One more thing is talk to your hardware (DISK) vendor and
do some checks in DISK side during non peak time.
Thanks
Hari
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:789E3859-17AA-464A-B4FF-C30FDD6C3522@.microsoft.com...
> Hi,
> We had a query return the index entry error below.
> Could not find the index entry for RID '36020100000200000100100007410400'
> in
> index page (1:2223497), index ID 0, database 'dpSUM001'..
> I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
> planned to run a rebuild index on the table later that night to fix the
> problem. Later in the afternoon I ran the query again and this time it
> returned data and didn't have the index entry error. I ran the DBCC
> CHECKTABLE and it reported no errors.
> Does anyone know what fixed the index errors? The database has the auto
> update/create stats and torn page detection turned on. Could one of these
> option fixed the errors?
> Thanks Richard
>
We had a query return the index entry error below.
Could not find the index entry for RID '36020100000200000100100007410400' in
index page (1:2223497), index ID 0, database 'dpSUM001'..
I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
planned to run a rebuild index on the table later that night to fix the
problem. Later in the afternoon I ran the query again and this time it
returned data and didn't have the index entry error. I ran the DBCC
CHECKTABLE and it reported no errors.
Does anyone know what fixed the index errors? The database has the auto
update/create stats and torn page detection turned on. Could one of these
option fixed the errors?
Thanks Richard
No idea. Thats some thing new and good for you. But from your side make sure
that you execute DBCC CHECKDB regularly and ensure that database
is in good shape. One more thing is talk to your hardware (DISK) vendor and
do some checks in DISK side during non peak time.
Thanks
Hari
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:789E3859-17AA-464A-B4FF-C30FDD6C3522@.microsoft.com...
> Hi,
> We had a query return the index entry error below.
> Could not find the index entry for RID '36020100000200000100100007410400'
> in
> index page (1:2223497), index ID 0, database 'dpSUM001'..
> I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
> planned to run a rebuild index on the table later that night to fix the
> problem. Later in the afternoon I ran the query again and this time it
> returned data and didn't have the index entry error. I ran the DBCC
> CHECKTABLE and it reported no errors.
> Does anyone know what fixed the index errors? The database has the auto
> update/create stats and torn page detection turned on. Could one of these
> option fixed the errors?
> Thanks Richard
>
index error?
Hi,
We had a query return the index entry error below.
Could not find the index entry for RID '36020100000200000100100007410400' in
index page (1:2223497), index ID 0, database 'dpSUM001'..
I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
planned to run a rebuild index on the table later that night to fix the
problem. Later in the afternoon I ran the query again and this time it
returned data and didn't have the index entry error. I ran the DBCC
CHECKTABLE and it reported no errors.
Does anyone know what fixed the index errors? The database has the auto
update/create stats and torn page detection turned on. Could one of these
option fixed the errors?
Thanks RichardNo idea. Thats some thing new and good for you. But from your side make sure
that you execute DBCC CHECKDB regularly and ensure that database
is in good shape. One more thing is talk to your hardware (DISK) vendor and
do some checks in DISK side during non peak time.
Thanks
Hari
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:789E3859-17AA-464A-B4FF-C30FDD6C3522@.microsoft.com...
> Hi,
> We had a query return the index entry error below.
> Could not find the index entry for RID '36020100000200000100100007410400'
> in
> index page (1:2223497), index ID 0, database 'dpSUM001'..
> I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
> planned to run a rebuild index on the table later that night to fix the
> problem. Later in the afternoon I ran the query again and this time it
> returned data and didn't have the index entry error. I ran the DBCC
> CHECKTABLE and it reported no errors.
> Does anyone know what fixed the index errors? The database has the auto
> update/create stats and torn page detection turned on. Could one of these
> option fixed the errors?
> Thanks Richard
>
We had a query return the index entry error below.
Could not find the index entry for RID '36020100000200000100100007410400' in
index page (1:2223497), index ID 0, database 'dpSUM001'..
I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
planned to run a rebuild index on the table later that night to fix the
problem. Later in the afternoon I ran the query again and this time it
returned data and didn't have the index entry error. I ran the DBCC
CHECKTABLE and it reported no errors.
Does anyone know what fixed the index errors? The database has the auto
update/create stats and torn page detection turned on. Could one of these
option fixed the errors?
Thanks RichardNo idea. Thats some thing new and good for you. But from your side make sure
that you execute DBCC CHECKDB regularly and ensure that database
is in good shape. One more thing is talk to your hardware (DISK) vendor and
do some checks in DISK side during non peak time.
Thanks
Hari
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:789E3859-17AA-464A-B4FF-C30FDD6C3522@.microsoft.com...
> Hi,
> We had a query return the index entry error below.
> Could not find the index entry for RID '36020100000200000100100007410400'
> in
> index page (1:2223497), index ID 0, database 'dpSUM001'..
> I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
> planned to run a rebuild index on the table later that night to fix the
> problem. Later in the afternoon I ran the query again and this time it
> returned data and didn't have the index entry error. I ran the DBCC
> CHECKTABLE and it reported no errors.
> Does anyone know what fixed the index errors? The database has the auto
> update/create stats and torn page detection turned on. Could one of these
> option fixed the errors?
> Thanks Richard
>
Index Error
What caused the below error.
"
I/O error (torn page) detected during read at offset"
Thanks ,
AnnaProbably a power failure or I/O failure.
Hope you have a current backup!
"Anna" <anna@.ccn.com.sg> wrote in message
news:070c01c3df21$8bba8f00$a101280a@.phx.gbl...
> What caused the below error.
> "
> I/O error (torn page) detected during read at offset"
> Thanks ,
> Anna
"
I/O error (torn page) detected during read at offset"
Thanks ,
AnnaProbably a power failure or I/O failure.
Hope you have a current backup!
"Anna" <anna@.ccn.com.sg> wrote in message
news:070c01c3df21$8bba8f00$a101280a@.phx.gbl...
> What caused the below error.
> "
> I/O error (torn page) detected during read at offset"
> Thanks ,
> Anna
Index Error
What caused the below error.
"
I/O error (torn page) detected during read at offset"
Thanks ,
AnnaProbably a power failure or I/O failure.
Hope you have a current backup!
"Anna" <anna@.ccn.com.sg> wrote in message
news:070c01c3df21$8bba8f00$a101280a@.phx.gbl...
"
I/O error (torn page) detected during read at offset"
Thanks ,
AnnaProbably a power failure or I/O failure.
Hope you have a current backup!
"Anna" <anna@.ccn.com.sg> wrote in message
news:070c01c3df21$8bba8f00$a101280a@.phx.gbl...
quote:
> What caused the below error.
> "
> I/O error (torn page) detected during read at offset"
> Thanks ,
> Anna
Friday, February 24, 2012
Index creation in SQL Server 2005 Management Studio - Page locks disabled by default
When I create an index on a table using SQL Server Management Studio, the index has page locks disabled by default. I'd prefer to have page locks enabled by default so neither I nor any other developer has to remember to manually modify. How can I accomplish this?
Thanks,
Pat Brickson
exec sp_indexoption '<TableName>', 'DisAllowPageLocks','FALSE'|||Thank you, that solves my problem for existing tables. How can I ensure that this is the default for all new tables as well?
The CREATE INDEX statement in T-SQL enables page level locks by default but if I create via Management Studio, they're disabled by default. Why is there a discrepency?
Thanks, Pat
|||After testing, I find that this only affects indexes that already exist on the table. I'd like to ensure that any new indexes on this table (or any existing or new table for that matter) have page locks enabled. Unfortunately this stored procedure doesn't really help me any more than just manually altering the index's options via the index properties dialog in Management Studio.
Again, I appreciate any assistance. Any other ideas?
Thanks, Pat
|||The default settings for new indexes are hard coded in the dialog to match the defaults in SQL Server if you don't specify any options. There is no way for end users to change these defaults in the dialog.
Thanks,
Steve
Maybe I misundertand you, but isn't the default to allow page locks when accessing the database? That's the default when issuing a T-SQL CREATE INDEX statement when no options are specified.
I'm really not concerned with end users changing the default. I'm more interested in ensuring that an any index created via Management Studio by a developer or DBA will have page locks enabled. Since that doesn't seem to be the default when creating in management studio, is there a way for me to specify which defaults the indexes should take?
Thanks, Pat
|||If the dialog defaults aren't matching the default behavior in T-SQL, that's not intentional. Please file a defect report for this on http://connect.microsoft.com. Defects reported by customers via the connect site carry extra weight when the development team is prioritizing future work, including service pack work.
Be sure to mention the version of management studio you are working with and that the dialog is not defaulting to the engine default.
Thanks,
Steve
Index creation in SQL Server 2005 Management Studio - Page locks disabled by default
When I create an index on a table using SQL Server Management Studio, the index has page locks disabled by default. I'd prefer to have page locks enabled by default so neither I nor any other developer has to remember to manually modify. How can I accomplish this?
Thanks,
Pat Brickson
exec sp_indexoption '<TableName>', 'DisAllowPageLocks','FALSE'|||Thank you, that solves my problem for existing tables. How can I ensure that this is the default for all new tables as well?
The CREATE INDEX statement in T-SQL enables page level locks by default but if I create via Management Studio, they're disabled by default. Why is there a discrepency?
Thanks, Pat
|||After testing, I find that this only affects indexes that already exist on the table. I'd like to ensure that any new indexes on this table (or any existing or new table for that matter) have page locks enabled. Unfortunately this stored procedure doesn't really help me any more than just manually altering the index's options via the index properties dialog in Management Studio.
Again, I appreciate any assistance. Any other ideas?
Thanks, Pat
|||The default settings for new indexes are hard coded in the dialog to match the defaults in SQL Server if you don't specify any options. There is no way for end users to change these defaults in the dialog.
Thanks,
Steve
Maybe I misundertand you, but isn't the default to allow page locks when accessing the database? That's the default when issuing a T-SQL CREATE INDEX statement when no options are specified.
I'm really not concerned with end users changing the default. I'm more interested in ensuring that an any index created via Management Studio by a developer or DBA will have page locks enabled. Since that doesn't seem to be the default when creating in management studio, is there a way for me to specify which defaults the indexes should take?
Thanks, Pat
|||If the dialog defaults aren't matching the default behavior in T-SQL, that's not intentional. Please file a defect report for this on http://connect.microsoft.com. Defects reported by customers via the connect site carry extra weight when the development team is prioritizing future work, including service pack work.
Be sure to mention the version of management studio you are working with and that the dialog is not defaulting to the engine default.
Thanks,
Steve
Sunday, February 19, 2012
Index and Data Pages
I am trying to understand how the data in sql server is stored and
also regarding fill factor and page splitting.
also regarding fill factor and page splitting.
1) My first question what is the difference between Index pages and
Data pages.
and how are they different for clustered and non clustered indexes and
heap tables.
2) What is the relation between index and data pages.
2) BOL says that fill factor is used to define the amount of free
space on each page of index. I am confused here what does index pages
and data pages contain
for clustered/non clustered/heap tables.
3) Why does page splits occur and do they occur due to the lack of
space in index or data pages?
Thanks
skura"skura" <thotakura1@.comcast.net> wrote in message
news:ccbd7dbf.0311252154.32a46cd5@.posting.google.c om...
> I am trying to understand how the data in sql server is stored and
> also regarding fill factor and page splitting.
I recommend you get a copy of Inside SQL Server 2000. It contains a lot of
detailed information on the low-level implementation of data and index
pages. I've given some short answers below (which will certainly be
oversimplified, but hopefully accurate enough), but there's enough
information in Books Online to get a good understanding.
> 1) My first question what is the difference between Index pages and
> Data pages.
> and how are they different for clustered and non clustered indexes and
> heap tables.
Data pages have data only; clustered index pages have index information and
data (because the leaf level is made up of data pages); non-clustered index
pages have index information only.
> 2) What is the relation between index and data pages.
If the index is clustered, the leaf nodes are data pages. If the index is
non-clustered, but there is already another clustered index on the table,
the leaf nodes point to keys in the clustered index. If the table is a heap,
the index leaf nodes point to rows in data pages.
> 2) BOL says that fill factor is used to define the amount of free
> space on each page of index. I am confused here what does index pages
> and data pages contain
> for clustered/non clustered/heap tables.
As above, with a clustered index, the index includes data pages; with a heap
table, the indexes have only index pages.
> 3) Why does page splits occur and do they occur due to the lack of
> space in index or data pages?
If the table has a clustered index, and the data pages in the leaf node fill
up with data, a split occurs because SQL Server has to 'make room' for the
new data. The same applies to non-clustered indexes - as data is added,
sooner or later the index pages will get full, whatever other indexes may be
on the table.
Think of putting new books in the middle of a shelf which already has lots
of books, sorted in alphabetical order. If all the old books are side to
side with no gaps, you'll have to move some of them every time you add a new
book to the shelf, in order to keep them all in alphabetical order. But if
you leave a number of spaces between the books, then you'll usually be able
to add many more new books before you have to move any of the old ones.
> Thanks
> skura
I hope that helps.
Simon|||Simon, That helps and thanks for the info. I will get the book and read it.
"Simon Hayes" <sql@.hayes.ch> wrote in message news:<3fc51cae$1_3@.news.bluewin.ch>...
> "skura" <thotakura1@.comcast.net> wrote in message
> news:ccbd7dbf.0311252154.32a46cd5@.posting.google.c om...
> > I am trying to understand how the data in sql server is stored and
> > also regarding fill factor and page splitting.
> I recommend you get a copy of Inside SQL Server 2000. It contains a lot of
> detailed information on the low-level implementation of data and index
> pages. I've given some short answers below (which will certainly be
> oversimplified, but hopefully accurate enough), but there's enough
> information in Books Online to get a good understanding.
> > 1) My first question what is the difference between Index pages and
> > Data pages.
> > and how are they different for clustered and non clustered indexes and
> > heap tables.
> Data pages have data only; clustered index pages have index information and
> data (because the leaf level is made up of data pages); non-clustered index
> pages have index information only.
> > 2) What is the relation between index and data pages.
> If the index is clustered, the leaf nodes are data pages. If the index is
> non-clustered, but there is already another clustered index on the table,
> the leaf nodes point to keys in the clustered index. If the table is a heap,
> the index leaf nodes point to rows in data pages.
> > 2) BOL says that fill factor is used to define the amount of free
> > space on each page of index. I am confused here what does index pages
> > and data pages contain
> > for clustered/non clustered/heap tables.
> As above, with a clustered index, the index includes data pages; with a heap
> table, the indexes have only index pages.
> > 3) Why does page splits occur and do they occur due to the lack of
> > space in index or data pages?
> If the table has a clustered index, and the data pages in the leaf node fill
> up with data, a split occurs because SQL Server has to 'make room' for the
> new data. The same applies to non-clustered indexes - as data is added,
> sooner or later the index pages will get full, whatever other indexes may be
> on the table.
> Think of putting new books in the middle of a shelf which already has lots
> of books, sorted in alphabetical order. If all the old books are side to
> side with no gaps, you'll have to move some of them every time you add a new
> book to the shelf, in order to keep them all in alphabetical order. But if
> you leave a number of spaces between the books, then you'll usually be able
> to add many more new books before you have to move any of the old ones.
> > Thanks
> > skura
> I hope that helps.
> Simon
Subscribe to:
Posts (Atom)