index and key: rebuilt

Showing posts with label rebuilt. Show all posts

Wednesday, March 28, 2012

Index rebuilds question

Hi,
My question is: How do you know when an index is
actually finished being rebuilt? I run a script that goes
through and searches for indexes that need rebuilding
because of fragmentation or "staleness" and then run DBCC
DBREINDEX on them. The DBCC command returns that the
index "is being rebuilt" and then moves on to the next
one. Is there anywhere I can query to see when the index
is actually finished being rebuilt? I'd like to know this
so I can then run an UPDATE STATISTICS on it. Right now
the procedure dies occasionally because it attempts to do
the stats update before the other process is complete.
I realize that I can schedule the stats update as a
separate process later in the evening (my current
workaround) but I was curious to see if there was another
alternative. Thanks!
-DanDan,
Not sure how your getting into this situation as the next line of code
should not execute until the DBCC DBREINDEX is completed. The first line
must execute before the next one can start. In either case there is no need
to update the stats on an index that you just rebuilt as it is done as part
of the DBREINDEX.
--
Andrew J. Kelly
SQL Server MVP
"Dan Wunder" <dwunder@.pentontech.com> wrote in message
news:019a01c39404$8b5ace70$a301280a@.phx.gbl...
> Hi,
> My question is: How do you know when an index is
> actually finished being rebuilt? I run a script that goes
> through and searches for indexes that need rebuilding
> because of fragmentation or "staleness" and then run DBCC
> DBREINDEX on them. The DBCC command returns that the
> index "is being rebuilt" and then moves on to the next
> one. Is there anywhere I can query to see when the index
> is actually finished being rebuilt? I'd like to know this
> so I can then run an UPDATE STATISTICS on it. Right now
> the procedure dies occasionally because it attempts to do
> the stats update before the other process is complete.
> I realize that I can schedule the stats update as a
> separate process later in the evening (my current
> workaround) but I was curious to see if there was another
> alternative. Thanks!
> -Dan

Monday, March 12, 2012

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 is large on a small Table?

Table data size is 3 meg
Index_size is 1 gig,
I rebuilt the index and it did drop my a meg or so but index is still
1gig.
XML indexes are in play,
Why would the index be that big?
Thanks in advance,rDray
Can you post the DDL?
"rDray" <randy.dray@.gmail.com> wrote in message
news:1162846100.891547.231880@.h54g2000cwb.googlegroups.com...
> Table data size is 3 meg
> Index_size is 1 gig,
> I rebuilt the index and it did drop my a meg or so but index is still
> 1gig.
> XML indexes are in play,
> Why would the index be that big?
> Thanks in advance,
>|||Thanks for responding, I am Green, What and how do you post the DDL?
Thanks
Uri Dimant wrote:
> rDray
> Can you post the DDL?
>
> "rDray" <randy.dray@.gmail.com> wrote in message
> news:1162846100.891547.231880@.h54g2000cwb.googlegroups.com...
> > Table data size is 3 meg
> >
> > Index_size is 1 gig,
> >
> > I rebuilt the index and it did drop my a meg or so but index is still
> > 1gig.
> > XML indexes are in play,
> >
> > Why would the index be that big?
> >
> > Thanks in advance,
> >|||"rDray" <randy.dray@.gmail.com> wrote in message
news:1163162103.739927.217160@.k70g2000cwa.googlegroups.com...
> Thanks for responding, I am Green, What and how do you post the DDL?
A script to recreate the database and some example data.
> Thanks
>
> Uri Dimant wrote:
>> rDray
>> Can you post the DDL?
>>
>> "rDray" <randy.dray@.gmail.com> wrote in message
>> news:1162846100.891547.231880@.h54g2000cwb.googlegroups.com...
>> > Table data size is 3 meg
>> >
>> > Index_size is 1 gig,
>> >
>> > I rebuilt the index and it did drop my a meg or so but index is still
>> > 1gig.
>> > XML indexes are in play,
>> >
>> > Why would the index be that big?
>> >
>> > Thanks in advance,
>> >
>

Index is large on a small Table?

Index is being rebuilt

Usually when I create an index in the Query Analyzer, no message is
returned other than the command completed. But sometimes I get a
message than the index is being rebuilt. I have come to ignore this
message as it appears to not be an issue that needs research.

I am now doing more and more in the VB.NET environment using ADO.NET
to maniupulate SQL objects. (Using SQL7, btw.) When I need to create
an index in VB.NET code I use the ExecuteNonQuery method since I do
not expect to process a result set. However, if the call results in
the index being rebuilt the return message blows up the
ExecuteNonQuery method with a general network error.

As I see it I have two options. One is to use the ExecuteReader method
instead and simply ignore anything returned. The other is to tell SQL
to not tell me that an index is being rebuilt as I don't care to even
know that. I could trap the general network error and ignore it but I
think it would be dangerous to ignore all network errors.

Any suggestions?

Please respond here as email address is invalid to avoid spam.Zack Sessions (zcsessions@.visionair.com) writes:
> Usually when I create an index in the Query Analyzer, no message is
> returned other than the command completed. But sometimes I get a
> message than the index is being rebuilt. I have come to ignore this
> message as it appears to not be an issue that needs research.

You get that message when you create, change or drop on a clustered index
on a table that also has a non-clustered index. Since the clustered index
serves as a the pointer to the data pages, non-clustered indexes must
be rebuilt when the clustered index is changed.

I have to admit that I am a little mystified. I tried first to get
the message on SQL 2000 SP3, but it remained silent. I know that I have
seen this message quite a bit, but I have not worked much with SQL 7,
so I suspect this message might have been silenced with SP3 of SQL 2000.

> I am now doing more and more in the VB.NET environment using ADO.NET
> to maniupulate SQL objects. (Using SQL7, btw.) When I need to create
> an index in VB.NET code I use the ExecuteNonQuery method since I do
> not expect to process a result set. However, if the call results in
> the index being rebuilt the return message blows up the
> ExecuteNonQuery method with a general network error.

That should not happen. I have a little test app for ADO .Net, and I was
not able to reproduce the problem. Which version of the .Net framework
are you using? Have you set up an InfoMessages event handler?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94D7F2295295EYazorman@.127.0.0.1>...
> Zack Sessions (zcsessions@.visionair.com) writes:
> > Usually when I create an index in the Query Analyzer, no message is
> > returned other than the command completed. But sometimes I get a
> > message than the index is being rebuilt. I have come to ignore this
> > message as it appears to not be an issue that needs research.
> You get that message when you create, change or drop on a clustered index
> on a table that also has a non-clustered index. Since the clustered index
> serves as a the pointer to the data pages, non-clustered indexes must
> be rebuilt when the clustered index is changed.

Ahh, mystery cleared up. I was wondering what the answer to "why" was.
> I have to admit that I am a little mystified. I tried first to get
> the message on SQL 2000 SP3, but it remained silent. I know that I have
> seen this message quite a bit, but I have not worked much with SQL 7,
> so I suspect this message might have been silenced with SP3 of SQL 2000.

Hmm, if so all the more reason to upgrade, eh? :-)

> > I am now doing more and more in the VB.NET environment using ADO.NET
> > to maniupulate SQL objects. (Using SQL7, btw.) When I need to create
> > an index in VB.NET code I use the ExecuteNonQuery method since I do
> > not expect to process a result set. However, if the call results in
> > the index being rebuilt the return message blows up the
> > ExecuteNonQuery method with a general network error.
> That should not happen. I have a little test app for ADO .Net, and I was
> not able to reproduce the problem. Which version of the .Net framework
> are you using? Have you set up an InfoMessages event handler?

Workstation is XP with VB.NET using ADO.NET. Server is NT4 SP6a, SQL7
SP4. Workstation and server are at .NET Framework 1.1.

Don't understand InfoMessages event handler. Can't find InfoMessages
mentioned in SQL7 or .NET Books Online. I am using a Try/Catch, that's
how I discovered the exception neing raised was a "general network
error".|||Zack Sessions (zcsessions@.visionair.com) writes:
> Erland Sommarskog <sommar@.algonet.se> wrote in message
news:<Xns94D7F2295295EYazorman@.127.0.0.1>...
>> I have to admit that I am a little mystified. I tried first to get
>> the message on SQL 2000 SP3, but it remained silent. I know that I have
>> seen this message quite a bit, but I have not worked much with SQL 7,
>> so I suspect this message might have been silenced with SP3 of SQL 2000.
> Hmm, if so all the more reason to upgrade, eh? :-)

I would say that upgrading because SQL2000 does not produce a message
that wrecks .Net Framework is the most convincing the reason to upgrade
I've seen. :-)

> Workstation is XP with VB.NET using ADO.NET. Server is NT4 SP6a, SQL7
> SP4. Workstation and server are at .NET Framework 1.1.

I have WinXP, and .Net Framwork 1.1. I have no idea, whether NT/XP could
make the difference.

By the way, if you just submit a print statement with ExecuteNonQuery,
does that too give General Network Error?

> Don't understand InfoMessages event handler. Can't find InfoMessages
> mentioned in SQL7 or .NET Books Online. I am using a Try/Catch, that's
> how I discovered the exception neing raised was a "general network
> error".

It's in the SqlConnection class. And it's InfoMessage.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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 in Yukon

Hi ,
What is the diff between alter index .. Rebuilt & reorganise
Thanks
ARRSame as between DBCC DBREINDEX and DBCC INDEXDEFRAG. Rebuild will create and drop (same as if you
executed those two commands). Reorg will "shuffle" the pages so they are (more) contiguous.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Aju" <ajuonline@.yahoo.com> wrote in message news:eFnLaZ%23FFHA.3732@.tk2msftngp13.phx.gbl...
> Hi ,
> What is the diff between alter index .. Rebuilt & reorganise
> Thanks
> ARR
>|||There are some extras too - with ALTER INDEX you can rebuild an index online
(i.e. with concurrent DML). BOL has all the details.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#w8NE9#FFHA.208@.TK2MSFTNGP12.phx.gbl...
> Same as between DBCC DBREINDEX and DBCC INDEXDEFRAG. Rebuild will create
and drop (same as if you
> executed those two commands). Reorg will "shuffle" the pages so they are
(more) contiguous.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
news:eFnLaZ%23FFHA.3732@.tk2msftngp13.phx.gbl...
> > Hi ,
> > What is the diff between alter index .. Rebuilt & reorganise
> >
> > Thanks
> > ARR
> >
> >
>

index in Yukon

Hi ,
What is the diff between alter index .. Rebuilt & reorganise
Thanks
ARR
Same as between DBCC DBREINDEX and DBCC INDEXDEFRAG. Rebuild will create and drop (same as if you
executed those two commands). Reorg will "shuffle" the pages so they are (more) contiguous.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Aju" <ajuonline@.yahoo.com> wrote in message news:eFnLaZ%23FFHA.3732@.tk2msftngp13.phx.gbl...
> Hi ,
> What is the diff between alter index .. Rebuilt & reorganise
> Thanks
> ARR
>
|||There are some extras too - with ALTER INDEX you can rebuild an index online
(i.e. with concurrent DML). BOL has all the details.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#w8NE9#FFHA.208@.TK2MSFTNGP12.phx.gbl...
> Same as between DBCC DBREINDEX and DBCC INDEXDEFRAG. Rebuild will create
and drop (same as if you
> executed those two commands). Reorg will "shuffle" the pages so they are
(more) contiguous.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
news:eFnLaZ%23FFHA.3732@.tk2msftngp13.phx.gbl...
>

index in Yukon

Hi ,
What is the diff between alter index .. Rebuilt & reorganise
Thanks
ARRSame as between DBCC DBREINDEX and DBCC INDEXDEFRAG. Rebuild will create and
drop (same as if you
executed those two commands). Reorg will "shuffle" the pages so they are (mo
re) contiguous.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Aju" <ajuonline@.yahoo.com> wrote in message news:eFnLaZ%23FFHA.3732@.tk2msftngp13.phx.gbl...

> Hi ,
> What is the diff between alter index .. Rebuilt & reorganise
> Thanks
> ARR
>|||There are some extras too - with ALTER INDEX you can rebuild an index online
(i.e. with concurrent DML). BOL has all the details.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#w8NE9#FFHA.208@.TK2MSFTNGP12.phx.gbl...
> Same as between DBCC DBREINDEX and DBCC INDEXDEFRAG. Rebuild will create
and drop (same as if you
> executed those two commands). Reorg will "shuffle" the pages so they are
(more) contiguous.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
news:eFnLaZ%23FFHA.3732@.tk2msftngp13.phx.gbl...
>

Friday, March 9, 2012

index fragmentation

Hi,
I rebuilt index for my tables yersterday morning, today when I check using
SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I read that
high logic fragmentaion is not good, why it happened?
ThanksIs this a Heap? How many pages in the index? Can you post the results of
DBCC SHOWCONTIG?
--
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
> Hi,
> I rebuilt index for my tables yersterday morning, today when I check using
> SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I read
> that
> high logic fragmentaion is not good, why it happened?
> Thanks|||here is the result:(I changed my table name and index name)
table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL NULL 0 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812 NULL NULL 95.079950799507998 773 813 99.579154968261719 NULL
table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595 NULL NULL 90.771812080536918 541 596 99.884445190429687 NULL
table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850 NULL NULL 97.414806110458287 829 851 99.864151000976563 NULL
table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787 NULL NULL 80.076142131979694 631 788 99.841461181640625 NULL
"Andrew J. Kelly" wrote:
> Is this a Heap? How many pages in the index? Can you post the results of
> DBCC SHOWCONTIG?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
> > Hi,
> > I rebuilt index for my tables yersterday morning, today when I check using
> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I read
> > that
> > high logic fragmentaion is not good, why it happened?
> > Thanks
>
>|||That is pretty hard to make out. It is better to show the regular output
and not with TABLERESULTS option. But it seems like you do have a clustered
index on this. What is the datatype of the Column(s) in the Clustered
index? Did you shrink the database or is the AutoShrink option turned on?
--
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
> here is the result:(I changed my table name and index name)
> table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL NULL 0
> 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
> table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812 NULL
> NULL 95.079950799507998 773 813 99.579154968261719 NULL
> table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595 NULL
> NULL 90.771812080536918 541 596 99.884445190429687 NULL
> table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850 NULL
> NULL 97.414806110458287 829 851 99.864151000976563 NULL
> table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787 NULL
> NULL 80.076142131979694 631 788 99.841461181640625 NULL
>
> "Andrew J. Kelly" wrote:
>> Is this a Heap? How many pages in the index? Can you post the results
>> of
>> DBCC SHOWCONTIG?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
>> > Hi,
>> > I rebuilt index for my tables yersterday morning, today when I check
>> > using
>> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I
>> > read
>> > that
>> > high logic fragmentaion is not good, why it happened?
>> > Thanks
>>|||sorry, I do have a numerica field (primary key) clusterd index.
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 1, database ID: 3
TABLE level scan performed.
- Pages Scanned........................: 38433
- Extent Switches.......................: 5950
- Scan Density [Best Count:Actual Count]......: 80.74% [4805:5951]
- Logical Scan Fragmentation ..............: 99.56%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 15, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 6177
- Extent Switches.......................: 788
- Scan Density [Best Count:Actual Count]......: 97.97% [773:789]
- Logical Scan Fragmentation ..............: 99.58%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 16, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 4329
- Extent Switches.......................: 548
- Scan Density [Best Count:Actual Count]......: 98.72% [542:549]
- Logical Scan Fragmentation ..............: 99.88%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 25, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 6629
- Extent Switches.......................: 840
- Scan Density [Best Count:Actual Count]......: 98.57% [829:841]
- Logical Scan Fragmentation ..............: 99.86%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 32, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 5046
- Extent Switches.......................: 638
- Scan Density [Best Count:Actual Count]......: 98.75% [631:639]
- Logical Scan Fragmentation ..............: 99.84%
"Andrew J. Kelly" wrote:
> That is pretty hard to make out. It is better to show the regular output
> and not with TABLERESULTS option. But it seems like you do have a clustered
> index on this. What is the datatype of the Column(s) in the Clustered
> index? Did you shrink the database or is the AutoShrink option turned on?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
> > here is the result:(I changed my table name and index name)
> > table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL NULL 0
> > 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
> > table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812 NULL
> > NULL 95.079950799507998 773 813 99.579154968261719 NULL
> > table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595 NULL
> > NULL 90.771812080536918 541 596 99.884445190429687 NULL
> > table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850 NULL
> > NULL 97.414806110458287 829 851 99.864151000976563 NULL
> > table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787 NULL
> > NULL 80.076142131979694 631 788 99.841461181640625 NULL
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Is this a Heap? How many pages in the index? Can you post the results
> >> of
> >> DBCC SHOWCONTIG?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> >> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
> >> > Hi,
> >> > I rebuilt index for my tables yersterday morning, today when I check
> >> > using
> >> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I
> >> > read
> >> > that
> >> > high logic fragmentaion is not good, why it happened?
> >> > Thanks
> >>
> >>
> >>
>
>|||And also the database has AutoShrink option on.
"Andrew J. Kelly" wrote:
> That is pretty hard to make out. It is better to show the regular output
> and not with TABLERESULTS option. But it seems like you do have a clustered
> index on this. What is the datatype of the Column(s) in the Clustered
> index? Did you shrink the database or is the AutoShrink option turned on?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
> > here is the result:(I changed my table name and index name)
> > table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL NULL 0
> > 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
> > table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812 NULL
> > NULL 95.079950799507998 773 813 99.579154968261719 NULL
> > table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595 NULL
> > NULL 90.771812080536918 541 596 99.884445190429687 NULL
> > table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850 NULL
> > NULL 97.414806110458287 829 851 99.864151000976563 NULL
> > table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787 NULL
> > NULL 80.076142131979694 631 788 99.841461181640625 NULL
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Is this a Heap? How many pages in the index? Can you post the results
> >> of
> >> DBCC SHOWCONTIG?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> >> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
> >> > Hi,
> >> > I rebuilt index for my tables yersterday morning, today when I check
> >> > using
> >> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I
> >> > read
> >> > that
> >> > high logic fragmentaion is not good, why it happened?
> >> > Thanks
> >>
> >>
> >>
>
>|||ok - here's what it looks like has happened:
1) you've rebuilt all your indexes and acheived near perfect fragmentation
2) shrink has run and totally reversed the fragmentation status - to be the
worst possible as far as DBCC SHOWCONTIG is concerned
Fragmentation is when the next logical page in an index is not the next
physical page in an index, moving from left to right logically (and from the
beginning of the file to the end, physically). Shrink starts at the end of
the file and works towards the front, moving single pages. Looks like its
made every page appear 'fragmented' to showcontig. Unfortunately, they are
also fragmented as the buffer pool sees them and so no readahead can occur
and your range scan performance will be terrible.
The simple lesson here is: don't ever run auto-shrink. The database needs
the extra space most of the time. Andrew - can you provide a link to Tibor's
'don't run shrink' webpage?
You should also read the whitepaper below which goes into detail on
fragmentation and when/how to get rid of it.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4851F94F-34A2-48B7-8C2A-BBE678CF970E@.microsoft.com...
> And also the database has AutoShrink option on.
> "Andrew J. Kelly" wrote:
> > That is pretty hard to make out. It is better to show the regular
output
> > and not with TABLERESULTS option. But it seems like you do have a
clustered
> > index on this. What is the datatype of the Column(s) in the Clustered
> > index? Did you shrink the database or is the AutoShrink option turned
on?
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Jen" <Jen@.discussions.microsoft.com> wrote in message
> > news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
> > > here is the result:(I changed my table name and index name)
> > > table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL
NULL 0
> > > 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
> > > table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812
NULL
> > > NULL 95.079950799507998 773 813 99.579154968261719 NULL
> > > table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595
NULL
> > > NULL 90.771812080536918 541 596 99.884445190429687 NULL
> > > table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850
NULL
> > > NULL 97.414806110458287 829 851 99.864151000976563 NULL
> > > table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787
NULL
> > > NULL 80.076142131979694 631 788 99.841461181640625 NULL
> > >
> > >
> > > "Andrew J. Kelly" wrote:
> > >
> > >> Is this a Heap? How many pages in the index? Can you post the
results
> > >> of
> > >> DBCC SHOWCONTIG?
> > >>
> > >> --
> > >> Andrew J. Kelly SQL MVP
> > >>
> > >>
> > >> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> > >> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
> > >> > Hi,
> > >> > I rebuilt index for my tables yersterday morning, today when I
check
> > >> > using
> > >> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I
> > >> > read
> > >> > that
> > >> > high logic fragmentaion is not good, why it happened?
> > >> > Thanks
> > >>
> > >>
> > >>
> >
> >
> >|||Yes it was the shrinking that did it. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:A01DBDCB-EA16-4295-A69B-55A36DDE0412@.microsoft.com...
> sorry, I do have a numerica field (primary key) clusterd index.
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 1, database ID: 3
> TABLE level scan performed.
> - Pages Scanned........................: 38433
> - Extent Switches.......................: 5950
> - Scan Density [Best Count:Actual Count]......: 80.74% [4805:5951]
> - Logical Scan Fragmentation ..............: 99.56%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 15, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 6177
> - Extent Switches.......................: 788
> - Scan Density [Best Count:Actual Count]......: 97.97% [773:789]
> - Logical Scan Fragmentation ..............: 99.58%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 16, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 4329
> - Extent Switches.......................: 548
> - Scan Density [Best Count:Actual Count]......: 98.72% [542:549]
> - Logical Scan Fragmentation ..............: 99.88%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 25, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 6629
> - Extent Switches.......................: 840
> - Scan Density [Best Count:Actual Count]......: 98.57% [829:841]
> - Logical Scan Fragmentation ..............: 99.86%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 32, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 5046
> - Extent Switches.......................: 638
> - Scan Density [Best Count:Actual Count]......: 98.75% [631:639]
> - Logical Scan Fragmentation ..............: 99.84%
> "Andrew J. Kelly" wrote:
>> That is pretty hard to make out. It is better to show the regular output
>> and not with TABLERESULTS option. But it seems like you do have a
>> clustered
>> index on this. What is the datatype of the Column(s) in the Clustered
>> index? Did you shrink the database or is the AutoShrink option turned
>> on?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
>> > here is the result:(I changed my table name and index name)
>> > table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL
>> > NULL 0
>> > 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
>> > table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812
>> > NULL
>> > NULL 95.079950799507998 773 813 99.579154968261719 NULL
>> > table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595
>> > NULL
>> > NULL 90.771812080536918 541 596 99.884445190429687 NULL
>> > table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850
>> > NULL
>> > NULL 97.414806110458287 829 851 99.864151000976563 NULL
>> > table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787
>> > NULL
>> > NULL 80.076142131979694 631 788 99.841461181640625 NULL
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Is this a Heap? How many pages in the index? Can you post the
>> >> results
>> >> of
>> >> DBCC SHOWCONTIG?
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> >> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
>> >> > Hi,
>> >> > I rebuilt index for my tables yersterday morning, today when I check
>> >> > using
>> >> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I
>> >> > read
>> >> > that
>> >> > high logic fragmentaion is not good, why it happened?
>> >> > Thanks
>> >>
>> >>
>> >>
>>|||> Andrew - can you provide a link to Tibor's
> 'don't run shrink' webpage?
Here goes. I'd appreciate much if you let me know if you find anything incorrect or that should be
expanded upon.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eOgH$vnAFHA.1452@.TK2MSFTNGP11.phx.gbl...
> ok - here's what it looks like has happened:
> 1) you've rebuilt all your indexes and acheived near perfect fragmentation
> 2) shrink has run and totally reversed the fragmentation status - to be the
> worst possible as far as DBCC SHOWCONTIG is concerned
> Fragmentation is when the next logical page in an index is not the next
> physical page in an index, moving from left to right logically (and from the
> beginning of the file to the end, physically). Shrink starts at the end of
> the file and works towards the front, moving single pages. Looks like its
> made every page appear 'fragmented' to showcontig. Unfortunately, they are
> also fragmented as the buffer pool sees them and so no readahead can occur
> and your range scan performance will be terrible.
> The simple lesson here is: don't ever run auto-shrink. The database needs
> the extra space most of the time. Andrew - can you provide a link to Tibor's
> 'don't run shrink' webpage?
> You should also read the whitepaper below which goes into detail on
> fragmentation and when/how to get rid of it.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:4851F94F-34A2-48B7-8C2A-BBE678CF970E@.microsoft.com...
>> And also the database has AutoShrink option on.
>> "Andrew J. Kelly" wrote:
>> > That is pretty hard to make out. It is better to show the regular
> output
>> > and not with TABLERESULTS option. But it seems like you do have a
> clustered
>> > index on this. What is the datatype of the Column(s) in the Clustered
>> > index? Did you shrink the database or is the AutoShrink option turned
> on?
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> > news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
>> > > here is the result:(I changed my table name and index name)
>> > > table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL
> NULL 0
>> > > 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
>> > > table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812
> NULL
>> > > NULL 95.079950799507998 773 813 99.579154968261719 NULL
>> > > table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595
> NULL
>> > > NULL 90.771812080536918 541 596 99.884445190429687 NULL
>> > > table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850
> NULL
>> > > NULL 97.414806110458287 829 851 99.864151000976563 NULL
>> > > table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787
> NULL
>> > > NULL 80.076142131979694 631 788 99.841461181640625 NULL
>> > >
>> > >
>> > > "Andrew J. Kelly" wrote:
>> > >
>> > >> Is this a Heap? How many pages in the index? Can you post the
> results
>> > >> of
>> > >> DBCC SHOWCONTIG?
>> > >>
>> > >> --
>> > >> Andrew J. Kelly SQL MVP
>> > >>
>> > >>
>> > >> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> > >> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
>> > >> > Hi,
>> > >> > I rebuilt index for my tables yersterday morning, today when I
> check
>> > >> > using
>> > >> > SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I
>> > >> > read
>> > >> > that
>> > >> > high logic fragmentaion is not good, why it happened?
>> > >> > Thanks
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >
>

index fragmentation

I changed my table name and index name)
table1859150106PK__table1__3429BB531038408NULLNULLNULLNULLNULL05935NULLNULL80.8793800539083634801593699.614662170410156NULL
table1859150106IX_table11506178NULLNULLNULLNULLNULL0812NULLNULL95.07995079950799877381399.579154968261719NULL
table1859150106IX_table1_11604327NULLNULLNULLNULLNULL0595NULLNULL90.77181208053691854159699.884445190429687NULL
table1859150106IX_table1_22506625NULLNULLNULLNULLNULL0850NULLNULL97.41480611045828782985199.864151000976563NULL
table1859150106IX_table1_32805046NULLNULLNULLNULLNULL0787NULLNULL80.07614213197969463178899.841461181640625NULL
"Andrew J. Kelly" wrote:

> Is this a Heap? How many pages in the index? Can you post the results of
> DBCC SHOWCONTIG?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
>
>
|||That is pretty hard to make out. It is better to show the regular output
and not with TABLERESULTS option. But it seems like you do have a clustered
index on this. What is the datatype of the Column(s) in the Clustered
index? Did you shrink the database or is the AutoShrink option turned on?
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...[vbcol=seagreen]
> here is the result

I changed my table name and index name)
> table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL NULL 0
> 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
> table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812 NULL
> NULL 95.079950799507998 773 813 99.579154968261719 NULL
> table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595 NULL
> NULL 90.771812080536918 541 596 99.884445190429687 NULL
> table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850 NULL
> NULL 97.414806110458287 829 851 99.864151000976563 NULL
> table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787 NULL
> NULL 80.076142131979694 631 788 99.841461181640625 NULL
>
> "Andrew J. Kelly" wrote:
|||sorry, I do have a numerica field (primary key) clusterd index.
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 1, database ID: 3
TABLE level scan performed.
- Pages Scanned........................: 38433
- Extent Switches.......................: 5950
- Scan Density [Best Count:Actual Count]......: 80.74% [4805:5951]
- Logical Scan Fragmentation ..............: 99.56%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 15, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 6177
- Extent Switches.......................: 788
- Scan Density [Best Count:Actual Count]......: 97.97% [773:789]
- Logical Scan Fragmentation ..............: 99.58%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 16, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 4329
- Extent Switches.......................: 548
- Scan Density [Best Count:Actual Count]......: 98.72% [542:549]
- Logical Scan Fragmentation ..............: 99.88%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 25, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 6629
- Extent Switches.......................: 840
- Scan Density [Best Count:Actual Count]......: 98.57% [829:841]
- Logical Scan Fragmentation ..............: 99.86%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 32, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 5046
- Extent Switches.......................: 638
- Scan Density [Best Count:Actual Count]......: 98.75% [631:639]
- Logical Scan Fragmentation ..............: 99.84%
"Andrew J. Kelly" wrote:

> That is pretty hard to make out. It is better to show the regular output
> and not with TABLERESULTS option. But it seems like you do have a clustered
> index on this. What is the datatype of the Column(s) in the Clustered
> index? Did you shrink the database or is the AutoShrink option turned on?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
>
>
|||And also the database has AutoShrink option on.
"Andrew J. Kelly" wrote:

> That is pretty hard to make out. It is better to show the regular output
> and not with TABLERESULTS option. But it seems like you do have a clustered
> index on this. What is the datatype of the Column(s) in the Clustered
> index? Did you shrink the database or is the AutoShrink option turned on?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
>
>
|||ok - here's what it looks like has happened:
1) you've rebuilt all your indexes and acheived near perfect fragmentation
2) shrink has run and totally reversed the fragmentation status - to be the
worst possible as far as DBCC SHOWCONTIG is concerned
Fragmentation is when the next logical page in an index is not the next
physical page in an index, moving from left to right logically (and from the
beginning of the file to the end, physically). Shrink starts at the end of
the file and works towards the front, moving single pages. Looks like its
made every page appear 'fragmented' to showcontig. Unfortunately, they are
also fragmented as the buffer pool sees them and so no readahead can occur
and your range scan performance will be terrible.
The simple lesson here is: don't ever run auto-shrink. The database needs
the extra space most of the time. Andrew - can you provide a link to Tibor's
'don't run shrink' webpage?
You should also read the whitepaper below which goes into detail on
fragmentation and when/how to get rid of it.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4851F94F-34A2-48B7-8C2A-BBE678CF970E@.microsoft.com...[vbcol=seagreen]
> And also the database has AutoShrink option on.
> "Andrew J. Kelly" wrote:
output[vbcol=seagreen]
clustered[vbcol=seagreen]
on?[vbcol=seagreen]
NULL 0[vbcol=seagreen]
NULL[vbcol=seagreen]
NULL[vbcol=seagreen]
NULL[vbcol=seagreen]
NULL[vbcol=seagreen]
results[vbcol=seagreen]
check[vbcol=seagreen]
|||Yes it was the shrinking that did it. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:A01DBDCB-EA16-4295-A69B-55A36DDE0412@.microsoft.com...[vbcol=seagreen]
> sorry, I do have a numerica field (primary key) clusterd index.
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 1, database ID: 3
> TABLE level scan performed.
> - Pages Scanned........................: 38433
> - Extent Switches.......................: 5950
> - Scan Density [Best Count:Actual Count]......: 80.74% [4805:5951]
> - Logical Scan Fragmentation ..............: 99.56%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 15, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 6177
> - Extent Switches.......................: 788
> - Scan Density [Best Count:Actual Count]......: 97.97% [773:789]
> - Logical Scan Fragmentation ..............: 99.58%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 16, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 4329
> - Extent Switches.......................: 548
> - Scan Density [Best Count:Actual Count]......: 98.72% [542:549]
> - Logical Scan Fragmentation ..............: 99.88%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 25, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 6629
> - Extent Switches.......................: 840
> - Scan Density [Best Count:Actual Count]......: 98.57% [829:841]
> - Logical Scan Fragmentation ..............: 99.86%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 32, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 5046
> - Extent Switches.......................: 638
> - Scan Density [Best Count:Actual Count]......: 98.75% [631:639]
> - Logical Scan Fragmentation ..............: 99.84%
> "Andrew J. Kelly" wrote:
|||> Andrew - can you provide a link to Tibor's
> 'don't run shrink' webpage?
Here goes. I'd appreciate much if you let me know if you find anything incorrect or that should be
expanded upon.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eOgH$vnAFHA.1452@.TK2MSFTNGP11.phx.gbl...
> ok - here's what it looks like has happened:
> 1) you've rebuilt all your indexes and acheived near perfect fragmentation
> 2) shrink has run and totally reversed the fragmentation status - to be the
> worst possible as far as DBCC SHOWCONTIG is concerned
> Fragmentation is when the next logical page in an index is not the next
> physical page in an index, moving from left to right logically (and from the
> beginning of the file to the end, physically). Shrink starts at the end of
> the file and works towards the front, moving single pages. Looks like its
> made every page appear 'fragmented' to showcontig. Unfortunately, they are
> also fragmented as the buffer pool sees them and so no readahead can occur
> and your range scan performance will be terrible.
> The simple lesson here is: don't ever run auto-shrink. The database needs
> the extra space most of the time. Andrew - can you provide a link to Tibor's
> 'don't run shrink' webpage?
> You should also read the whitepaper below which goes into detail on
> fragmentation and when/how to get rid of it.
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:4851F94F-34A2-48B7-8C2A-BBE678CF970E@.microsoft.com...
> output
> clustered
> on?
> NULL 0
> NULL
> NULL
> NULL
> NULL
> results
> check
>

index fragmentation

I changed my table name and index name)
table1 859150106 PK__table1__3429BB53 1
0 38408 NULL NULL NULL NULL NULL 0 5
935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
table1 859150106 IX_table1 15 0 6178 NUL
L NULL NULL NULL NULL 0 812 NULL NUL
L 95.079950799507998 773 813 99.579154968261719 NULL
table1 859150106 IX_table1_1 16 0 4327 N
ULL NULL NULL NULL NULL 0 595 NULL N
ULL 90.771812080536918 541 596 99.884445190429687 NULL
table1 859150106 IX_table1_2 25 0 6625 N
ULL NULL NULL NULL NULL 0 850 NULL N
ULL 97.414806110458287 829 851 99.864151000976563 NULL
table1 859150106 IX_table1_3 28 0 5046 N
ULL NULL NULL NULL NULL 0 787 NULL N
ULL 80.076142131979694 631 788 99.841461181640625 NULL
"Andrew J. Kelly" wrote:

> Is this a Heap? How many pages in the index? Can you post the results of
> DBCC SHOWCONTIG?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
>
>|||That is pretty hard to make out. It is better to show the regular output
and not with TABLERESULTS option. But it seems like you do have a clustered
index on this. What is the datatype of the Column(s) in the Clustered
index? Did you shrink the database or is the AutoShrink option turned on?
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...[vbcol=seagreen]
> here is the result

I changed my table name and index name)
> table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL NULL 0
> 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
> table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812 NULL
> NULL 95.079950799507998 773 813 99.579154968261719 NULL
> table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595 NULL
> NULL 90.771812080536918 541 596 99.884445190429687 NULL
> table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850 NULL
> NULL 97.414806110458287 829 851 99.864151000976563 NULL
> table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787 NULL
> NULL 80.076142131979694 631 788 99.841461181640625 NULL
>
> "Andrew J. Kelly" wrote:
>|||sorry, I do have a numerica field (primary key) clusterd index.
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 1, database ID: 3
TABLE level scan performed.
- Pages Scanned........................: 38433
- Extent Switches.......................: 5950
- Scan Density [Best Count:Actual Count]......: 80.74% [4805:5951]
- Logical Scan Fragmentation ..............: 99.56%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 15, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 6177
- Extent Switches.......................: 788
- Scan Density [Best Count:Actual Count]......: 97.97% [773:789]
- Logical Scan Fragmentation ..............: 99.58%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 16, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 4329
- Extent Switches.......................: 548
- Scan Density [Best Count:Actual Count]......: 98.72% [542:549]
- Logical Scan Fragmentation ..............: 99.88%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 25, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 6629
- Extent Switches.......................: 840
- Scan Density [Best Count:Actual Count]......: 98.57% [829:841]
- Logical Scan Fragmentation ..............: 99.86%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 32, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 5046
- Extent Switches.......................: 638
- Scan Density [Best Count:Actual Count]......: 98.75% [631:639]
- Logical Scan Fragmentation ..............: 99.84%
"Andrew J. Kelly" wrote:

> That is pretty hard to make out. It is better to show the regular output
> and not with TABLERESULTS option. But it seems like you do have a cluster
ed
> index on this. What is the datatype of the Column(s) in the Clustered
> index? Did you shrink the database or is the AutoShrink option turned on?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
>
>|||And also the database has AutoShrink option on.
"Andrew J. Kelly" wrote:

> That is pretty hard to make out. It is better to show the regular output
> and not with TABLERESULTS option. But it seems like you do have a cluster
ed
> index on this. What is the datatype of the Column(s) in the Clustered
> index? Did you shrink the database or is the AutoShrink option turned on?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
>
>|||ok - here's what it looks like has happened:
1) you've rebuilt all your indexes and acheived near perfect fragmentation
2) shrink has run and totally reversed the fragmentation status - to be the
worst possible as far as DBCC SHOWCONTIG is concerned
Fragmentation is when the next logical page in an index is not the next
physical page in an index, moving from left to right logically (and from the
beginning of the file to the end, physically). Shrink starts at the end of
the file and works towards the front, moving single pages. Looks like its
made every page appear 'fragmented' to showcontig. Unfortunately, they are
also fragmented as the buffer pool sees them and so no readahead can occur
and your range scan performance will be terrible.
The simple lesson here is: don't ever run auto-shrink. The database needs
the extra space most of the time. Andrew - can you provide a link to Tibor's
'don't run shrink' webpage?
You should also read the whitepaper below which goes into detail on
fragmentation and when/how to get rid of it.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4851F94F-34A2-48B7-8C2A-BBE678CF970E@.microsoft.com...[vbcol=seagreen]
> And also the database has AutoShrink option on.
> "Andrew J. Kelly" wrote:
>
output[vbcol=seagreen]
clustered[vbcol=seagreen]
on?[vbcol=seagreen]
NULL 0[vbcol=seagreen]
NULL[vbcol=seagreen]
NULL[vbcol=seagreen]
NULL[vbcol=seagreen]
NULL[vbcol=seagreen]
results[vbcol=seagreen]
check[vbcol=seagreen]|||Yes it was the shrinking that did it. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:A01DBDCB-EA16-4295-A69B-55A36DDE0412@.microsoft.com...[vbcol=seagreen]
> sorry, I do have a numerica field (primary key) clusterd index.
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 1, database ID: 3
> TABLE level scan performed.
> - Pages Scanned........................: 38433
> - Extent Switches.......................: 5950
> - Scan Density [Best Count:Actual Count]......: 80.74% [4805:5951
]
> - Logical Scan Fragmentation ..............: 99.56%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 15, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 6177
> - Extent Switches.......................: 788
> - Scan Density [Best Count:Actual Count]......: 97.97% [773:789]
> - Logical Scan Fragmentation ..............: 99.58%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 16, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 4329
> - Extent Switches.......................: 548
> - Scan Density [Best Count:Actual Count]......: 98.72% [542:549]
> - Logical Scan Fragmentation ..............: 99.88%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 25, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 6629
> - Extent Switches.......................: 840
> - Scan Density [Best Count:Actual Count]......: 98.57% [829:841]
> - Logical Scan Fragmentation ..............: 99.86%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 32, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 5046
> - Extent Switches.......................: 638
> - Scan Density [Best Count:Actual Count]......: 98.75% [631:639]
> - Logical Scan Fragmentation ..............: 99.84%
> "Andrew J. Kelly" wrote:
>|||> Andrew - can you provide a link to Tibor's
> 'don't run shrink' webpage?
Here goes. I'd appreciate much if you let me know if you find anything incor
rect or that should be
expanded upon.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eOgH$vnAFHA.1452@.TK2MSFTNGP11.phx.gbl...
> ok - here's what it looks like has happened:
> 1) you've rebuilt all your indexes and acheived near perfect fragmentation
> 2) shrink has run and totally reversed the fragmentation status - to be th
e
> worst possible as far as DBCC SHOWCONTIG is concerned
> Fragmentation is when the next logical page in an index is not the next
> physical page in an index, moving from left to right logically (and from t
he
> beginning of the file to the end, physically). Shrink starts at the end of
> the file and works towards the front, moving single pages. Looks like its
> made every page appear 'fragmented' to showcontig. Unfortunately, they are
> also fragmented as the buffer pool sees them and so no readahead can occur
> and your range scan performance will be terrible.
> The simple lesson here is: don't ever run auto-shrink. The database needs
> the extra space most of the time. Andrew - can you provide a link to Tibor
's
> 'don't run shrink' webpage?
> You should also read the whitepaper below which goes into detail on
> fragmentation and when/how to get rid of it.
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:4851F94F-34A2-48B7-8C2A-BBE678CF970E@.microsoft.com...
> output
> clustered
> on?
> NULL 0
> NULL
> NULL
> NULL
> NULL
> results
> check
>

Wednesday, March 28, 2012

Index rebuilds question

Monday, March 12, 2012

INDEX LAST REBUILT TIME

Index is large on a small Table?

Index is large on a small Table?

Index is being rebuilt

Index Internals - Last time index was rebuilt?

index in Yukon

index in Yukon

index in Yukon

Friday, March 9, 2012

index fragmentation

index fragmentation

index fragmentation

index and key

Blog Archive

About Me