Index reported as corrupt after upgrading to 2005

After upgrading a server from SQL Server 2000 to 2005,
an index was suddenly being reported as corrupt in the SQL Server log
(probably every time an Insert was done). DBCC CHECKDB / CHECKTABLE
reported no errors, with or without the new WITH DATA_PURITY option.

Anyone else experienced something similar?

How can an index be reported as corrupt when DBCC doesn't report it?

Dropping and re-creating the index solved the problem.
I've restored a backup of the database (made before dropping/re-creating
the index) to try repeating the problem, but no success so far.The only data purity issue I have had in SQL 2005 so far has been with an XML index on a set of XML documents that may or may not be well formed. The developers are rethinking their strategy at the moment.

Index rename

What's a SQL statement for renaming an existing index. I know how to add an
index at the time that the table is created, but how do you rename an
existing index.
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1Check out sp_rename in the Books Online.
Hope this helps.
Dan Guzman
SQL Server MVP
"HockeyFan via webservertalk.com" <u16651@.uwe> wrote in message
news:58d63becf3fe8@.uwe...
> What's a SQL statement for renaming an existing index. I know how to add
> an
> index at the time that the table is created, but how do you rename an
> existing index.
> --
> E. coli Happens.
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> What's a SQL statement for renaming an existing index.
Books Online is your friend.
EXEC sp_rename 'tablename.indexname', 'newname', 'index'|||too bad there's not a search.
Aaron Bertrand [SQL Server MVP] wrote:
>Books Online is your friend.
>EXEC sp_rename 'tablename.indexname', 'newname', 'index'
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||To make sure that I don't rename something else, I'm hoping to verify that
the "table.indexname" is also not the name of a column. I want to get the
correct item to rename. Maybe that can't be done with SP_Rename. since it
takes a string, not an object_ID.
HockeyFan wrote:
>too bad there's not a search.
>
>
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> too bad there's not a search.
But there is. Also, I found the topic very quickly in the index by typing
'renaming indexes'.
Hope this helps.
Dan Guzman
SQL Server MVP
"HockeyFan via webservertalk.com" <u16651@.uwe> wrote in message
news:58d681f6f81ec@.uwe...
> too bad there's not a search.
> Aaron Bertrand [SQL Server MVP] wrote:
> --
> E. coli Happens.
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> To make sure that I don't rename something else, I'm hoping to verify that
> the "table.indexname" is also not the name of a column.
That's what the third parameter is for. Notice I say 'index' and not
'column'...|||> too bad there's not a search.
? There sure is.|||There's one that you can limit the search just to a particular book?
Aaron Bertrand [SQL Server MVP] wrote:
>? There sure is.
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> There's one that you can limit the search just to a particular book?
Well, I searched for "rename index SQL Server" at msdn.microsoft.com and
sp_rename came up on the first page (and it was the first one that said
anything about Transact-SQL).
If you want an easier way to limit you search to SQL Server only, then
install Books Online on your own computer (if it isn't there already).
See http://www.aspfaq.com/2229 for information about where to find Books
Online on your own computer, and where to get it if you don't already have
it installed. The download for SQL Server 2000 is currently broken, so you
may want to download the SQL Server 2005 edition. However, note that some
content is not applicable to SQL Server 2000 (though the basic gist of the
sp_rename topic should get you going), and you will need to have the .Net
2.0 Framework installed.
A

Index question

Hello,
I have a telephone log table that tracks the following: (phone number, time
of day, duration of call). This table that is populated with over 1/2 a
million telephone numbers per day. My question is what is the appropriate
way of indexing a table with this volume of inserts?
--
Thanks in advance,
sck10Something like that usually does well with a clustered index on the datetime
but it depends on how you need to access the data. What are your typical
queries like?
--
Andrew J. Kelly SQL MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uTER80kzEHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a telephone log table that tracks the following: (phone number,
> time
> of day, duration of call). This table that is populated with over 1/2 a
> million telephone numbers per day. My question is what is the appropriate
> way of indexing a table with this volume of inserts?
> --
> Thanks in advance,
> sck10
>|||Hi sck10,
Addtionally to MVP Andrew J. Kelly's suggestions, you would also make the
decision based on Index Tuning Wizard in Query Analyzer. Here is some
guidelines for you
Index Tuning Wizard for Microsoft SQL Server 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/itwforsql.asp
INF: Index Tuning Wizard Best Practices
http://support.microsoft.com/kb/311826
Support WebCast: Effective Indexing and Statistics with SQL 2000
http://support.microsoft.com/kb/325024
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||It depends on whether this data is being inserted in real time
or if the data is loaded from provider files, and also on whether
the data is constantly being queried, or if it is only accessed for
occasional reports and bill generation. If it's bulk loaded
from provider files, or if it's processed only for reports and
bills, you have more flexibility, and can index it in a way
that's useful to what you do with the data. If it's loaded
in real time, but not queried in real time, it could even be
appropriate to load it into an unindexed table, or into
a table with an artificial clustered key like a column with
the identity property. If the hardware cannot spit
out the same call twice, you don't need a natural key while
the data is loading.
Can you say more about where this data comes from,
what kinds of queries you run against it, and whether
there are typically few or many connections querying
the data at once?
Steve Kass
Drew University
sck10 wrote:
>Hello,
>I have a telephone log table that tracks the following: (phone number, time
>of day, duration of call). This table that is populated with over 1/2 a
>million telephone numbers per day. My question is what is the appropriate
>way of indexing a table with this volume of inserts?
>
>

INDEX LAST REBUILT TIME

Hi folks,
Is there a way to find out when was the last time an index was rebuilt?
Is this information is available in any system tables?
Thx,
RamThat information is not stored.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A6D3D5D4-4678-4197-AEF6-2F0952E8DD43@.microsoft.com...
> Hi folks,
> Is there a way to find out when was the last time an index was rebuilt?
> Is this information is available in any system tables?
> Thx,
> Ram|||Heh heh, when you issue the DBCC command, write it down in your notebook.
:-)
SQL Server does not track this information. If you wish to track this
information, you'll have to do it yourself. For example, you could have a
stored procedure that issues the DBCC command(s) and logs to a table.
A
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A6D3D5D4-4678-4197-AEF6-2F0952E8DD43@.microsoft.com...
> Hi folks,
> Is there a way to find out when was the last time an index was rebuilt?
> Is this information is available in any system tables?
> Thx,
> Ram|||Why? If you're trying to schedule regular index rebuilds, do this by
examining actual fragmentation, rather than estimating the amout of time tha
t
should elapse between each defragmentation (rebuild).
See "DBCC SHOWCONTIG" in Books Online.
ML

Index Internals - Last time index was rebuilt?

I'm trying to find whether there is a dmv or system view that can help me see the last time an index was rebuilt or created. Assuming I rebuilt an index using tsql commands (not a job with a history), is there a way to find out the last time that index was rebuilt?

Thanks much.

Perhaps the information you seek is available here:

SELECT *

FROM sys.dm_index_usage_stats

|||

Arnie,

Thank you for your reply. I'm afraid that I have not seen the information I'm after in any of the documented columns of the dmvs. I've combed through the dmvs related to indexes and have been unable to find it. That's why I'm wondering if this metadata is stored elsewhere, and if so, where.

If, when looking at the dmv you mentioned, you saw a particular column you think contains the information I'm after, please let me know what it is.

Thanks!

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]

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]