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]
No comments:
Post a Comment