Real Time DSS: fragmented

Showing posts with label fragmented. Show all posts

Friday, March 23, 2012

rebuilding index not doing anything

There are 25 indexes in my database which continue to report they are
severely fragmented even after I rebuild them. The operation completes
without error, so I don't understand why it is still fragmented. It does not
matter if I use REBUILD or REORGANIZE, the result is the same. Anyone know
how to fix this? I am using SQL Server 2005.
BTW I should mention that most indexes in the database will REBUILD and
report no fragmentation afterwards. Only 25 of them have this issue.
If I drop and recreate the index, obviously that would fix it, but I'd
rather not.
ChrisHi Chris
What type of fragmentation are you seeing? How many pages are in these
indexes? Small indexes cannot be totally defrag'ed, and the fragmentation
doesn't really matter.
REBUILD is exactly the same as drop and recreate so if REBUILD doesn't help,
drop and recreate won't either.
Can you show us the output from sys.dm_index_physical_stats for these
indexes?
Why is it so important that these indexes be defrag'ed? What operations are
being negatively impacted because of the fragmentation?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Chris" <fake@.email.com> wrote in message
news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
> There are 25 indexes in my database which continue to report they are
> severely fragmented even after I rebuild them. The operation completes
> without error, so I don't understand why it is still fragmented. It does
> not matter if I use REBUILD or REORGANIZE, the result is the same. Anyone
> know how to fix this? I am using SQL Server 2005.
> BTW I should mention that most indexes in the database will REBUILD and
> report no fragmentation afterwards. Only 25 of them have this issue.
> If I drop and recreate the index, obviously that would fix it, but I'd
> rather not.
> Chris|||In addition to what Kalen stated if they are Heaps then rebuilding the
indexes will do nothing for the table itself. You need a clustered index for
that.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris" <fake@.email.com> wrote in message
news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
> There are 25 indexes in my database which continue to report they are
> severely fragmented even after I rebuild them. The operation completes
> without error, so I don't understand why it is still fragmented. It does
> not matter if I use REBUILD or REORGANIZE, the result is the same. Anyone
> know how to fix this? I am using SQL Server 2005.
> BTW I should mention that most indexes in the database will REBUILD and
> report no fragmentation afterwards. Only 25 of them have this issue.
> If I drop and recreate the index, obviously that would fix it, but I'd
> rather not.
> Chris|||To add to Kalen and Andrew's comments, you should check the size (number of
pages) in the 25 indexes. If the indexes are very small, they're stored in
mixed extents. Mixed extents are shared by up to 8 objects, so your ability
to completely remove fragmentation is somewhat limited in this case whether
you reorganize or rebuild.
You might want to review this whitepaper on fragmentation
(http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx).
It's written for SQL Server 2000 but is still valid for SQL Server 2005.
They recommend not worrying about fragmentation on indexes with fewer than
1000 pages because the workload performance gain isn't significant enough to
warrant it.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Chris" <fake@.email.com> wrote in message
news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
> There are 25 indexes in my database which continue to report they are
> severely fragmented even after I rebuild them. The operation completes
> without error, so I don't understand why it is still fragmented. It does
> not matter if I use REBUILD or REORGANIZE, the result is the same. Anyone
> know how to fix this? I am using SQL Server 2005.
> BTW I should mention that most indexes in the database will REBUILD and
> report no fragmentation afterwards. Only 25 of them have this issue.
> If I drop and recreate the index, obviously that would fix it, but I'd
> rather not.
> Chris|||I think the most important thing not mentioned yet is how much free space to
you have in the database? If you are like every single client I have been
at the answer is essentially none. You are allowing autogrowth (possibly at
the default setting) to grow your database. If there isn't empty space in
the database, there is no contiguous blocks of disk space in which to
defragment anything. Double the size of the database and try again with the
rebuild. I think you will be pleased with the results.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Chris" <fake@.email.com> wrote in message
news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
> There are 25 indexes in my database which continue to report they are
> severely fragmented even after I rebuild them. The operation completes
> without error, so I don't understand why it is still fragmented. It does
> not matter if I use REBUILD or REORGANIZE, the result is the same. Anyone
> know how to fix this? I am using SQL Server 2005.
> BTW I should mention that most indexes in the database will REBUILD and
> report no fragmentation afterwards. Only 25 of them have this issue.
> If I drop and recreate the index, obviously that would fix it, but I'd
> rather not.
> Chris|||This was part of his original post:
>BTW I should mention that most indexes in the database will REBUILD and
>report no fragmentation afterwards. Only 25 of them have this issue.
While it is absolutely true that you need plenty of free space in the data
files when rebuilding an index it is unlikely the cause here or the results
would not be so consistent. He also mentioned that a REORG has no effect
either. Since that works by swapping on a page by page basis the lack of
free space would not be be much of a factor.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kuhuki5tflk88@.corp.supernews.com...
>I think the most important thing not mentioned yet is how much free space
>to you have in the database? If you are like every single client I have
>been at the answer is essentially none. You are allowing autogrowth
>(possibly at the default setting) to grow your database. If there isn't
>empty space in the database, there is no contiguous blocks of disk space in
>which to defragment anything. Double the size of the database and try
>again with the rebuild. I think you will be pleased with the results.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Chris" <fake@.email.com> wrote in message
> news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
>> There are 25 indexes in my database which continue to report they are
>> severely fragmented even after I rebuild them. The operation completes
>> without error, so I don't understand why it is still fragmented. It does
>> not matter if I use REBUILD or REORGANIZE, the result is the same. Anyone
>> know how to fix this? I am using SQL Server 2005.
>> BTW I should mention that most indexes in the database will REBUILD and
>> report no fragmentation afterwards. Only 25 of them have this issue.
>> If I drop and recreate the index, obviously that would fix it, but I'd
>> rather not.
>> Chris
>|||But does a page by page swap not still leave fragmentation as long as only 1
index is being page swapped at a time? Or does a reorg allow for page moves
for multiple objects? Never really thought about it deeply enough.
For example, say there are index objects A, B and C. They are physically on
disk as follows (the number is the 'correct' Btree order of the index):
A2B1C1C2A3B2A1.
If index A is is currently being REORGd, how do the pages actually get
manipulated during the operation? Let us assume there is no other free
space in the database.
Does B1 get swapped for A1, leaving this: A2A1C1C2A3B2B1
And then A1 and A2 swap, leaving this: A1A2C1C2A3B2B1, etc, etc?
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OyTW6KuMIHA.2208@.TK2MSFTNGP06.phx.gbl...
> This was part of his original post:
>>BTW I should mention that most indexes in the database will REBUILD and
>>report no fragmentation afterwards. Only 25 of them have this issue.
>
> While it is absolutely true that you need plenty of free space in the data
> files when rebuilding an index it is unlikely the cause here or the
> results would not be so consistent. He also mentioned that a REORG has no
> effect either. Since that works by swapping on a page by page basis the
> lack of free space would not be be much of a factor.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13kuhuki5tflk88@.corp.supernews.com...
>>I think the most important thing not mentioned yet is how much free space
>>to you have in the database? If you are like every single client I have
>>been at the answer is essentially none. You are allowing autogrowth
>>(possibly at the default setting) to grow your database. If there isn't
>>empty space in the database, there is no contiguous blocks of disk space
>>in which to defragment anything. Double the size of the database and try
>>again with the rebuild. I think you will be pleased with the results.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Chris" <fake@.email.com> wrote in message
>> news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
>> There are 25 indexes in my database which continue to report they are
>> severely fragmented even after I rebuild them. The operation completes
>> without error, so I don't understand why it is still fragmented. It does
>> not matter if I use REBUILD or REORGANIZE, the result is the same.
>> Anyone know how to fix this? I am using SQL Server 2005.
>> BTW I should mention that most indexes in the database will REBUILD and
>> report no fragmentation afterwards. Only 25 of them have this issue.
>> If I drop and recreate the index, obviously that would fix it, but I'd
>> rather not.
>> Chris
>>
>|||During reorg, pages are only swapped with other pages from the same index.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kup01apko432d@.corp.supernews.com...
> But does a page by page swap not still leave fragmentation as long as only
> 1 index is being page swapped at a time? Or does a reorg allow for page
> moves for multiple objects? Never really thought about it deeply enough.
> For example, say there are index objects A, B and C. They are physically
> on disk as follows (the number is the 'correct' Btree order of the index):
> A2B1C1C2A3B2A1.
> If index A is is currently being REORGd, how do the pages actually get
> manipulated during the operation? Let us assume there is no other free
> space in the database.
> Does B1 get swapped for A1, leaving this: A2A1C1C2A3B2B1
> And then A1 and A2 swap, leaving this: A1A2C1C2A3B2B1, etc, etc?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OyTW6KuMIHA.2208@.TK2MSFTNGP06.phx.gbl...
>> This was part of his original post:
>>BTW I should mention that most indexes in the database will REBUILD and
>>report no fragmentation afterwards. Only 25 of them have this issue.
>>
>> While it is absolutely true that you need plenty of free space in the
>> data files when rebuilding an index it is unlikely the cause here or the
>> results would not be so consistent. He also mentioned that a REORG has no
>> effect either. Since that works by swapping on a page by page basis the
>> lack of free space would not be be much of a factor.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13kuhuki5tflk88@.corp.supernews.com...
>>I think the most important thing not mentioned yet is how much free space
>>to you have in the database? If you are like every single client I have
>>been at the answer is essentially none. You are allowing autogrowth
>>(possibly at the default setting) to grow your database. If there isn't
>>empty space in the database, there is no contiguous blocks of disk space
>>in which to defragment anything. Double the size of the database and try
>>again with the rebuild. I think you will be pleased with the results.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Chris" <fake@.email.com> wrote in message
>> news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
>> There are 25 indexes in my database which continue to report they are
>> severely fragmented even after I rebuild them. The operation completes
>> without error, so I don't understand why it is still fragmented. It
>> does not matter if I use REBUILD or REORGANIZE, the result is the same.
>> Anyone know how to fix this? I am using SQL Server 2005.
>> BTW I should mention that most indexes in the database will REBUILD and
>> report no fragmentation afterwards. Only 25 of them have this issue.
>> If I drop and recreate the index, obviously that would fix it, but I'd
>> rather not.
>> Chris
>>
>|||Which would leave fragmentation in place if there is no free space in the
file. Hmm, come to think of it, if page SWAPPING is all that can be done
then even huge amounts of free space won't do any good. It would need to be
able to move pages to empty space and lay them down contiguously to really
defrag the index, but if it can only swap pages that isn't good enough.
Curious.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OSG1InvMIHA.2308@.TK2MSFTNGP05.phx.gbl...
> During reorg, pages are only swapped with other pages from the same index.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13kup01apko432d@.corp.supernews.com...
>> But does a page by page swap not still leave fragmentation as long as
>> only 1 index is being page swapped at a time? Or does a reorg allow for
>> page moves for multiple objects? Never really thought about it deeply
>> enough.
>> For example, say there are index objects A, B and C. They are physically
>> on disk as follows (the number is the 'correct' Btree order of the
>> index):
>> A2B1C1C2A3B2A1.
>> If index A is is currently being REORGd, how do the pages actually get
>> manipulated during the operation? Let us assume there is no other free
>> space in the database.
>> Does B1 get swapped for A1, leaving this: A2A1C1C2A3B2B1
>> And then A1 and A2 swap, leaving this: A1A2C1C2A3B2B1, etc, etc?
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OyTW6KuMIHA.2208@.TK2MSFTNGP06.phx.gbl...
>> This was part of his original post:
>>BTW I should mention that most indexes in the database will REBUILD and
>>report no fragmentation afterwards. Only 25 of them have this issue.
>>
>> While it is absolutely true that you need plenty of free space in the
>> data files when rebuilding an index it is unlikely the cause here or the
>> results would not be so consistent. He also mentioned that a REORG has
>> no effect either. Since that works by swapping on a page by page basis
>> the lack of free space would not be be much of a factor.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13kuhuki5tflk88@.corp.supernews.com...
>>I think the most important thing not mentioned yet is how much free
>>space to you have in the database? If you are like every single client
>>I have been at the answer is essentially none. You are allowing
>>autogrowth (possibly at the default setting) to grow your database. If
>>there isn't empty space in the database, there is no contiguous blocks
>>of disk space in which to defragment anything. Double the size of the
>>database and try again with the rebuild. I think you will be pleased
>>with the results.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Chris" <fake@.email.com> wrote in message
>> news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
>> There are 25 indexes in my database which continue to report they are
>> severely fragmented even after I rebuild them. The operation completes
>> without error, so I don't understand why it is still fragmented. It
>> does not matter if I use REBUILD or REORGANIZE, the result is the
>> same. Anyone know how to fix this? I am using SQL Server 2005.
>> BTW I should mention that most indexes in the database will REBUILD
>> and report no fragmentation afterwards. Only 25 of them have this
>> issue.
>> If I drop and recreate the index, obviously that would fix it, but I'd
>> rather not.
>> Chris
>>
>>
>|||Defrag or Reorg (depending on the version) does not rebuild the pages and
extents like a Rebuild does. It simply swaps one page with another and this
can happen multiple times before it is done. None of this requires free
space (actually except for the very first first page I believe) to happen.
But since it does the reorg in two phases (compaction & defrag) it can
actually free up some pages if it compacts enough to do so. A reorg can can
remove virtually all of the Logical fragmentation but it not necessarily fix
extent fragmentation in which the next and previous extents are physically
contiguous.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13l05fijdqgpl71@.corp.supernews.com...
> Which would leave fragmentation in place if there is no free space in the
> file. Hmm, come to think of it, if page SWAPPING is all that can be done
> then even huge amounts of free space won't do any good. It would need to
> be able to move pages to empty space and lay them down contiguously to
> really defrag the index, but if it can only swap pages that isn't good
> enough. Curious.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OSG1InvMIHA.2308@.TK2MSFTNGP05.phx.gbl...
>> During reorg, pages are only swapped with other pages from the same
>> index.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13kup01apko432d@.corp.supernews.com...
>> But does a page by page swap not still leave fragmentation as long as
>> only 1 index is being page swapped at a time? Or does a reorg allow for
>> page moves for multiple objects? Never really thought about it deeply
>> enough.
>> For example, say there are index objects A, B and C. They are
>> physically on disk as follows (the number is the 'correct' Btree order
>> of the index):
>> A2B1C1C2A3B2A1.
>> If index A is is currently being REORGd, how do the pages actually get
>> manipulated during the operation? Let us assume there is no other free
>> space in the database.
>> Does B1 get swapped for A1, leaving this: A2A1C1C2A3B2B1
>> And then A1 and A2 swap, leaving this: A1A2C1C2A3B2B1, etc, etc?
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OyTW6KuMIHA.2208@.TK2MSFTNGP06.phx.gbl...
>> This was part of his original post:
>>BTW I should mention that most indexes in the database will REBUILD and
>>report no fragmentation afterwards. Only 25 of them have this issue.
>>
>> While it is absolutely true that you need plenty of free space in the
>> data files when rebuilding an index it is unlikely the cause here or
>> the results would not be so consistent. He also mentioned that a REORG
>> has no effect either. Since that works by swapping on a page by page
>> basis the lack of free space would not be be much of a factor.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13kuhuki5tflk88@.corp.supernews.com...
>>I think the most important thing not mentioned yet is how much free
>>space to you have in the database? If you are like every single client
>>I have been at the answer is essentially none. You are allowing
>>autogrowth (possibly at the default setting) to grow your database. If
>>there isn't empty space in the database, there is no contiguous blocks
>>of disk space in which to defragment anything. Double the size of the
>>database and try again with the rebuild. I think you will be pleased
>>with the results.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Chris" <fake@.email.com> wrote in message
>> news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
>> There are 25 indexes in my database which continue to report they are
>> severely fragmented even after I rebuild them. The operation
>> completes without error, so I don't understand why it is still
>> fragmented. It does not matter if I use REBUILD or REORGANIZE, the
>> result is the same. Anyone know how to fix this? I am using SQL
>> Server 2005.
>> BTW I should mention that most indexes in the database will REBUILD
>> and report no fragmentation afterwards. Only 25 of them have this
>> issue.
>> If I drop and recreate the index, obviously that would fix it, but
>> I'd rather not.
>> Chris
>>
>>
>>
>

rebuilding index not doing anything

Wednesday, March 21, 2012

Rebuild indices

hello,
I have a problem where certain indexes on an sql server 2005 database
remain fragmented no matter how much rebuild I do on them.
Why is this? and how can I rebuild the indexes correctly without
fragmentation.. (I'm having about 80%) ?
Something weird I noticed is when I'm using the DBCC DBREINDEX on this
field (the one having 80% fragmentation),
when giving a fill factor of 10 or less the fragmentation is reduced
to 11% ... while greater than 10 the fragmentation remains basically
the same.
So the thing is the less and the better? What's happening exactly? I
thought it was because of the fill factor .. however by default Sql
server makes a fill factor of 90%... so shouldn't be that problem
Thanks in advance for shedding a bit of light on this!Varangian,
Can you post the "create index" statement and the result from "DBCC
SHOWCONTI" or "select ... from sys.dm_db_index_physical_stats"?
AMB
"Varangian" wrote:
> hello,
> I have a problem where certain indexes on an sql server 2005 database
> remain fragmented no matter how much rebuild I do on them.
> Why is this? and how can I rebuild the indexes correctly without
> fragmentation.. (I'm having about 80%) ?
> Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> field (the one having 80% fragmentation),
> when giving a fill factor of 10 or less the fragmentation is reduced
> to 11% ... while greater than 10 the fragmentation remains basically
> the same.
> So the thing is the less and the better? What's happening exactly? I
> thought it was because of the fill factor .. however by default Sql
> server makes a fill factor of 90%... so shouldn't be that problem
> Thanks in advance for shedding a bit of light on this!
>|||How much free space is in your database? If you are like 100% of my clients
you rely on autogrowths to size your dbs and thus you never have any
contiguous free space in your databases in which the defrag operations can
lay down the indexes. Double or triple the size of your databae and then do
a defrag and see what happens.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Varangian" <ofmars@.gmail.com> wrote in message
news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
> hello,
> I have a problem where certain indexes on an sql server 2005 database
> remain fragmented no matter how much rebuild I do on them.
> Why is this? and how can I rebuild the indexes correctly without
> fragmentation.. (I'm having about 80%) ?
> Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> field (the one having 80% fragmentation),
> when giving a fill factor of 10 or less the fragmentation is reduced
> to 11% ... while greater than 10 the fragmentation remains basically
> the same.
> So the thing is the less and the better? What's happening exactly? I
> thought it was because of the fill factor .. however by default Sql
> server makes a fill factor of 90%... so shouldn't be that problem
> Thanks in advance for shedding a bit of light on this!
>|||In addition to the other posts:
How many pages`We frequently see similar posts here and it turns out that the index is only 3-4
pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Varangian" <ofmars@.gmail.com> wrote in message
news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
> hello,
> I have a problem where certain indexes on an sql server 2005 database
> remain fragmented no matter how much rebuild I do on them.
> Why is this? and how can I rebuild the indexes correctly without
> fragmentation.. (I'm having about 80%) ?
> Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> field (the one having 80% fragmentation),
> when giving a fill factor of 10 or less the fragmentation is reduced
> to 11% ... while greater than 10 the fragmentation remains basically
> the same.
> So the thing is the less and the better? What's happening exactly? I
> thought it was because of the fill factor .. however by default Sql
> server makes a fill factor of 90%... so shouldn't be that problem
> Thanks in advance for shedding a bit of light on this!
>|||On Oct 29, 5:56 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> In addition to the other posts:
> How many pages`We frequently see similar posts here and it turns out that the index is only 3-4
> pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Varangian" <ofm...@.gmail.com> wrote in message
> news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
> > hello,
> > I have a problem where certain indexes on an sql server 2005 database
> > remain fragmented no matter how much rebuild I do on them.
> > Why is this? and how can I rebuild the indexes correctly without
> > fragmentation.. (I'm having about 80%) ?
> > Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> > field (the one having 80% fragmentation),
> > when giving a fill factor of 10 or less the fragmentation is reduced
> > to 11% ... while greater than 10 the fragmentation remains basically
> > the same.
> > So the thing is the less and the better? What's happening exactly? I
> > thought it was because of the fill factor .. however by default Sql
> > server makes a fill factor of 90%... so shouldn't be that problem
> > Thanks in advance for shedding a bit of light on this!
Yes ok the pages are 6... but anyhow still I want to know how to
resolve such issue as it may appear on a database with 500-1000
this is the dbcc showcontig on this table
DBCC SHOWCONTIG scanning 'Users' table...
Table: 'Users' (1748201278); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 6
- Extents Scanned.......................: 6
- Extent Switches.......................: 5
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
- Logical Scan Fragmentation ..............: 83.33%
- Extent Scan Fragmentation ...............: 83.33%
- Avg. Bytes Free per Page................: 1531.8
- Avg. Page Density (full)................: 81.07%
I changed the database size (as well as the log file) but nothing
happened.|||> Yes ok the pages are 6... but anyhow still I want to know how to
> resolve such issue as it may appear on a database with 500-1000
There's nothing to resolve. The first 8 pages from an index comes from shared extents. So, until you
have 8 pages, the pages can be allocated from any extent where there is free space. I.e, such an
index will be "fragmented", by nature of how storage allocation work in SQL server. So again,
exclude small indexes when you look at fragmentation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Varangian" <ofmars@.gmail.com> wrote in message
news:1193681980.777348.73060@.v3g2000hsg.googlegroups.com...
> On Oct 29, 5:56 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> In addition to the other posts:
>> How many pages`We frequently see similar posts here and it turns out that the index is only 3-4
>> pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Varangian" <ofm...@.gmail.com> wrote in message
>> news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
>> > hello,
>> > I have a problem where certain indexes on an sql server 2005 database
>> > remain fragmented no matter how much rebuild I do on them.
>> > Why is this? and how can I rebuild the indexes correctly without
>> > fragmentation.. (I'm having about 80%) ?
>> > Something weird I noticed is when I'm using the DBCC DBREINDEX on this
>> > field (the one having 80% fragmentation),
>> > when giving a fill factor of 10 or less the fragmentation is reduced
>> > to 11% ... while greater than 10 the fragmentation remains basically
>> > the same.
>> > So the thing is the less and the better? What's happening exactly? I
>> > thought it was because of the fill factor .. however by default Sql
>> > server makes a fill factor of 90%... so shouldn't be that problem
>> > Thanks in advance for shedding a bit of light on this!
>
> Yes ok the pages are 6... but anyhow still I want to know how to
> resolve such issue as it may appear on a database with 500-1000
> this is the dbcc showcontig on this table
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (1748201278); index ID: 1, database ID: 6
> TABLE level scan performed.
> - Pages Scanned........................: 6
> - Extents Scanned.......................: 6
> - Extent Switches.......................: 5
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
> - Logical Scan Fragmentation ..............: 83.33%
> - Extent Scan Fragmentation ...............: 83.33%
> - Avg. Bytes Free per Page................: 1531.8
> - Avg. Page Density (full)................: 81.07%
>
> I changed the database size (as well as the log file) but nothing
> happened.
>|||On Oct 29, 8:18 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Yes ok the pages are 6... but anyhow still I want to know how to
> > resolve such issue as it may appear on a database with 500-1000
> There's nothing to resolve. The first 8 pages from an index comes from shared extents. So, until you
> have 8 pages, the pages can be allocated from any extent where there is free space. I.e, such an
> index will be "fragmented", by nature of how storage allocation work in SQL server. So again,
> exclude small indexes when you look at fragmentation.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Varangian" <ofm...@.gmail.com> wrote in message
> news:1193681980.777348.73060@.v3g2000hsg.googlegroups.com...
> > On Oct 29, 5:56 pm, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> In addition to the other posts:
> >> How many pages`We frequently see similar posts here and it turns out that the index is only 3-4
> >> pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> "Varangian" <ofm...@.gmail.com> wrote in message
> >>news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
> >> > hello,
> >> > I have a problem where certain indexes on an sql server 2005 database
> >> > remain fragmented no matter how much rebuild I do on them.
> >> > Why is this? and how can I rebuild the indexes correctly without
> >> > fragmentation.. (I'm having about 80%) ?
> >> > Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> >> > field (the one having 80% fragmentation),
> >> > when giving a fill factor of 10 or less the fragmentation is reduced
> >> > to 11% ... while greater than 10 the fragmentation remains basically
> >> > the same.
> >> > So the thing is the less and the better? What's happening exactly? I
> >> > thought it was because of the fill factor .. however by default Sql
> >> > server makes a fill factor of 90%... so shouldn't be that problem
> >> > Thanks in advance for shedding a bit of light on this!
> > Yes ok the pages are 6... but anyhow still I want to know how to
> > resolve such issue as it may appear on a database with 500-1000
> > this is the dbcc showcontig on this table
> > DBCC SHOWCONTIG scanning 'Users' table...
> > Table: 'Users' (1748201278); index ID: 1, database ID: 6
> > TABLE level scan performed.
> > - Pages Scanned........................: 6
> > - Extents Scanned.......................: 6
> > - Extent Switches.......................: 5
> > - Avg. Pages per Extent..................: 1.0
> > - Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
> > - Logical Scan Fragmentation ..............: 83.33%
> > - Extent Scan Fragmentation ...............: 83.33%
> > - Avg. Bytes Free per Page................: 1531.8
> > - Avg. Page Density (full)................: 81.07%
> > I changed the database size (as well as the log file) but nothing
> > happened.
Tibor .. ok but when the pages will be 8 and over what shall I do?
this means that it will be slower over a period of time. It's like
saying that no-one has control over this type of fragmentation|||There's no need to worry about fragmentation for small indexes. If it isn't hot (not frequently
accessed), then if it is small and you rarely access it, why worry. If it is hot (often accesses)
and small it will likely stay in cache and fragmentation is only an issue when pages are read from
disk.
For larger indexes, then you want to care about fragmentation. There are ways to defragment an
index. Check out:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Varangian" <ofmars@.gmail.com> wrote in message
news:1193685964.003127.81220@.k79g2000hse.googlegroups.com...
> On Oct 29, 8:18 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> > Yes ok the pages are 6... but anyhow still I want to know how to
>> > resolve such issue as it may appear on a database with 500-1000
>> There's nothing to resolve. The first 8 pages from an index comes from shared extents. So, until
>> you
>> have 8 pages, the pages can be allocated from any extent where there is free space. I.e, such an
>> index will be "fragmented", by nature of how storage allocation work in SQL server. So again,
>> exclude small indexes when you look at fragmentation.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Varangian" <ofm...@.gmail.com> wrote in message
>> news:1193681980.777348.73060@.v3g2000hsg.googlegroups.com...
>> > On Oct 29, 5:56 pm, "Tibor Karaszi"
>> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> >> In addition to the other posts:
>> >> How many pages`We frequently see similar posts here and it turns out that the index is only
>> >> 3-4
>> >> pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
>> >> --
>> >> Tibor Karaszi, SQL Server
>> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
>> >> "Varangian" <ofm...@.gmail.com> wrote in message
>> >>news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
>> >> > hello,
>> >> > I have a problem where certain indexes on an sql server 2005 database
>> >> > remain fragmented no matter how much rebuild I do on them.
>> >> > Why is this? and how can I rebuild the indexes correctly without
>> >> > fragmentation.. (I'm having about 80%) ?
>> >> > Something weird I noticed is when I'm using the DBCC DBREINDEX on this
>> >> > field (the one having 80% fragmentation),
>> >> > when giving a fill factor of 10 or less the fragmentation is reduced
>> >> > to 11% ... while greater than 10 the fragmentation remains basically
>> >> > the same.
>> >> > So the thing is the less and the better? What's happening exactly? I
>> >> > thought it was because of the fill factor .. however by default Sql
>> >> > server makes a fill factor of 90%... so shouldn't be that problem
>> >> > Thanks in advance for shedding a bit of light on this!
>> > Yes ok the pages are 6... but anyhow still I want to know how to
>> > resolve such issue as it may appear on a database with 500-1000
>> > this is the dbcc showcontig on this table
>> > DBCC SHOWCONTIG scanning 'Users' table...
>> > Table: 'Users' (1748201278); index ID: 1, database ID: 6
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 6
>> > - Extents Scanned.......................: 6
>> > - Extent Switches.......................: 5
>> > - Avg. Pages per Extent..................: 1.0
>> > - Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
>> > - Logical Scan Fragmentation ..............: 83.33%
>> > - Extent Scan Fragmentation ...............: 83.33%
>> > - Avg. Bytes Free per Page................: 1531.8
>> > - Avg. Page Density (full)................: 81.07%
>> > I changed the database size (as well as the log file) but nothing
>> > happened.
> Tibor .. ok but when the pages will be 8 and over what shall I do?
> this means that it will be slower over a period of time. It's like
> saying that no-one has control over this type of fragmentation
>

Friday, March 23, 2012

rebuilding index not doing anything

rebuilding index not doing anything

rebuilding index not doing anything

Wednesday, March 21, 2012

Rebuild indices

Real Time DSS

Blog Archive

About Me