Get Next Number: indexes

Showing posts with label indexes. Show all posts

Monday, March 26, 2012

FT indexes are not populating

I've created a couple of full-text indexes using the FT wizards and/or sample
code from BOL. Everything appears to be correct and I've run a full
population on them without getting any errors. However, when I run my
queries to test the results I get no rows returned. When I look at the
properties of the FT Index, the item count states 0.
Did I miss something on setup?
Kevin Z
Are there any messages from MSSCI or MSSEach in the event log?
You also might want to review this kb article.
http://support.microsoft.com/default...b;en-us;317746
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"kzakoski" <kzakoski@.discussions.microsoft.com> wrote in message
news:F1DD2400-556F-4E0A-998A-CF9A2C43F29F@.microsoft.com...
> I've created a couple of full-text indexes using the FT wizards and/or
sample
> code from BOL. Everything appears to be correct and I've run a full
> population on them without getting any errors. However, when I run my
> queries to test the results I get no rows returned. When I look at the
> properties of the FT Index, the item count states 0.
> Did I miss something on setup?
> --
> Kevin Z
|||Builtin\Administrator account had been removed causing the problem. KB
aritcle had a work around.
Additonal question? Will a FT Index process an entire 'text' datatype
column when building the index or is there a limit on the number of
characters it will process? On the FT index that I just created, I'm
searching the FT columnn that contains SQL scripts. The table name I'm using
in my contains clause is a pretty unique table name, but buried in a lengthy
script. When I run the query, I don't get any matches, but if I use a LIKE
clause with wildcards I can find it. Any suggestions?
Kevin Z
"Hilary Cotter" wrote:

> Are there any messages from MSSCI or MSSEach in the event log?
> You also might want to review this kb article.
> http://support.microsoft.com/default...b;en-us;317746
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "kzakoski" <kzakoski@.discussions.microsoft.com> wrote in message
> news:F1DD2400-556F-4E0A-998A-CF9A2C43F29F@.microsoft.com...
> sample
>
>
|||From BOL entitled Filtering Supported File Types
Note For full-text indexing, a document must be less than 16 megabytes (MB)
in size and must not contain more than 256 kilobytes (KB) of filtered text.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"kzakoski" <kzakoski@.discussions.microsoft.com> wrote in message
news:1696B0D8-F132-4DAA-9099-44790BDADB65@.microsoft.com...
> Builtin\Administrator account had been removed causing the problem. KB
> aritcle had a work around.
> Additonal question? Will a FT Index process an entire 'text' datatype
> column when building the index or is there a limit on the number of
> characters it will process? On the FT index that I just created, I'm
> searching the FT columnn that contains SQL scripts. The table name I'm
using
> in my contains clause is a pretty unique table name, but buried in a
lengthy
> script. When I run the query, I don't get any matches, but if I use a
LIKE[vbcol=seagreen]
> clause with wildcards I can find it. Any suggestions?
> --
> Kevin Z
>
> "Hilary Cotter" wrote:
the[vbcol=seagreen]

Monday, March 12, 2012

frequently updated tables

Hi
I have a transaction table that is very frequently updated, so I have no
indexes on it. I also need to be able to run queries against this data on,
for example, a date range. I think the easiest solution would be to extract
data on a daily basis onto another table with indexes. Or should I be looking
at some sort of data warehousing/data cube approach? Any ideas/resources?
Thanksif your just updating the table i dont see any reason why you couldnt
index that table and run data off of it. and if you would be able to
export it to a different table once a day i'm guess the data your
viewing isnt live data you need to see right away. i'd just index that
table and run queries off of it. just set all the tables your
selecting from 'WITH (NOLOCK)' then it wont matter if a record is
being access at that time. it doesnt sound like your worried about any
type of dirty reads. if it is being inserted into quite frequently you
might have to take another approach or run a job to reindex at night.
i'm not 100% sure of your exact situation. hope that helps.|||We have been advised in the past that it wasn't a good idea to have indexes
on the transaction table that is being frequently written too i.e. less than
a second between inserts, but I'd still like to query the data. Without some
sort of index, the query ends up doing a full table scan on a massive table,
so I think I need some sort of extract to an indexed version to make these
queries manageable.
"GlennThomas5" wrote:
> if your just updating the table i dont see any reason why you couldnt
> index that table and run data off of it. and if you would be able to
> export it to a different table once a day i'm guess the data your
> viewing isnt live data you need to see right away. i'd just index that
> table and run queries off of it. just set all the tables your
> selecting from 'WITH (NOLOCK)' then it wont matter if a record is
> being access at that time. it doesnt sound like your worried about any
> type of dirty reads. if it is being inserted into quite frequently you
> might have to take another approach or run a job to reindex at night.
> i'm not 100% sure of your exact situation. hope that helps.
>|||Who ever told you that certainly didn't know what they were talking about.
If you are going to update a row you need to know which row to update
otherwise you scan and most likely lock the entire table. If you need to
query it (which that is what tables are for) and don't want all the rows you
also need an index. The trick is to pick the right column(s) to index and
the correct index type. If you post the DDL for the table and a sample of
the queries you run against it we can help you decide on the proper
indexing.
--
Andrew J. Kelly SQL MVP
"Gary Homewood" <GaryHomewood@.discussions.microsoft.com> wrote in message
news:AE25E15B-FC19-4FDF-BEF1-DEAAA4671C5D@.microsoft.com...
> We have been advised in the past that it wasn't a good idea to have
> indexes
> on the transaction table that is being frequently written too i.e. less
> than
> a second between inserts, but I'd still like to query the data. Without
> some
> sort of index, the query ends up doing a full table scan on a massive
> table,
> so I think I need some sort of extract to an indexed version to make these
> queries manageable.
>
> "GlennThomas5" wrote:
>> if your just updating the table i dont see any reason why you couldnt
>> index that table and run data off of it. and if you would be able to
>> export it to a different table once a day i'm guess the data your
>> viewing isnt live data you need to see right away. i'd just index that
>> table and run queries off of it. just set all the tables your
>> selecting from 'WITH (NOLOCK)' then it wont matter if a record is
>> being access at that time. it doesnt sound like your worried about any
>> type of dirty reads. if it is being inserted into quite frequently you
>> might have to take another approach or run a job to reindex at night.
>> i'm not 100% sure of your exact situation. hope that helps.
>>|||OK here's what I'm doing. My transaction table is written to potentially
several times a second; this is just an insert, not an update. I was under
the impression that if I had an index on such a large and rapidly expanding
table, there would be a performance hit when the index was recalculated as
rows are written to the table. I would subsequently like to retrieve results
from this table for a particular date range, in which case I do really need
an index on date to avoid doing a full table scan. Is there a way to get SQL
Server to maybe ignore the date index as rows are being inserted, and then
perhaps do a reindexing of the table once a day? Or do I need to extract data
to some sort of warehouse or data cube with a date index? In which case I'd
still have the same problem, because I'd need to extract entries from my very
large transaction table for a particular date.
Thanks for your replies so far. Hope I've phrased my problem a bit more
clearly this time.
"Andrew J. Kelly" wrote:
> Who ever told you that certainly didn't know what they were talking about.
> If you are going to update a row you need to know which row to update
> otherwise you scan and most likely lock the entire table. If you need to
> query it (which that is what tables are for) and don't want all the rows you
> also need an index. The trick is to pick the right column(s) to index and
> the correct index type. If you post the DDL for the table and a sample of
> the queries you run against it we can help you decide on the proper
> indexing.
> --
> Andrew J. Kelly SQL MVP
>
> "Gary Homewood" <GaryHomewood@.discussions.microsoft.com> wrote in message
> news:AE25E15B-FC19-4FDF-BEF1-DEAAA4671C5D@.microsoft.com...
> > We have been advised in the past that it wasn't a good idea to have
> > indexes
> > on the transaction table that is being frequently written too i.e. less
> > than
> > a second between inserts, but I'd still like to query the data. Without
> > some
> > sort of index, the query ends up doing a full table scan on a massive
> > table,
> > so I think I need some sort of extract to an indexed version to make these
> > queries manageable.
> >
> >
> >
> > "GlennThomas5" wrote:
> >
> >> if your just updating the table i dont see any reason why you couldnt
> >> index that table and run data off of it. and if you would be able to
> >> export it to a different table once a day i'm guess the data your
> >> viewing isnt live data you need to see right away. i'd just index that
> >> table and run queries off of it. just set all the tables your
> >> selecting from 'WITH (NOLOCK)' then it wont matter if a record is
> >> being access at that time. it doesnt sound like your worried about any
> >> type of dirty reads. if it is being inserted into quite frequently you
> >> might have to take another approach or run a job to reindex at night.
> >> i'm not 100% sure of your exact situation. hope that helps.
> >>
> >>
>
>

Friday, February 24, 2012

Freakin X-files type unexplained bull crap...

People
Scenario 1
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It has a column POSTALCODE. This table has no indexes/keys yet. The database size as per SQL Server enterprise manager is 77MB. I create a clustered index on the POSTALCODE column. The database size now is 135MB and space available is 24MB. I shrink the database. The database size is now back to 77MB. YIKES! Does the clustered index take no space'?
Scenario 2
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It has a column POSTALCODE. This table has no indexes/keys yet. The database size as per SQL Server enterprise manager is 77MB. I create a clustered index on the POSTALCODE column. The database size now is 135MB and space available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the space available is 74MB. Want more? I shrink the database and lo behold everything's intact!!!!!!!!!!!!! What the f...
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a typical MS crap
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0095_01C3F0B0.98CB5A80
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks.
--=_NextPart_000_0095_01C3F0B0.98CB5A80
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

No surprises here. In order to =create a clustered index on a table, you need 1.2X the size of the table's data =in free space available. This equates to 1.2*77MB =3D 92.4MB - in addition =to the existing 77MB. Once the clustered index has been created, it frees =up that space again. Thus, your database will have a fair bit of unused =space when you are done.
Check out Kalen Delaney's "Inside SQl =Server 2000" for more details.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Frustrated..." wrote in message news:746=F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...People,Scenario 1:I have a small DEMOGRAPHICS table that has close to 500000 rows of =data. It has a column POSTALCODE. This table has no indexes/keys yet. The =database size as per SQL Server enterprise manager is 77MB. I create a clustered =index on the POSTALCODE column. The database size now is 135MB and space =available is 24MB. I shrink the database. The database size is now back to 77MB. =YIKES! Does the clustered index take no space'Scenario 2:I have a =small DEMOGRAPHICS table that has close to 500000 rows of data. It has a =column POSTALCODE. This table has no indexes/keys yet. The database size as per =SQL Server enterprise manager is 77MB. I create a clustered index on the =POSTALCODE column. The database size now is 135MB and space available is 24MB. =I run sp_spaceused @.updateusage=3Dtrue. The database size is now 135MB and =the space available is 74MB. Want more? I shrink the database and lo behold =everything's intact!!!!!!!!!!!!! What the f...?Can anyone, I mean ANYONE, =including all you MVPs explain this sort of a typical MS crap?Thanks.

--=_NextPart_000_0095_01C3F0B0.98CB5A80--|||Don't forget that any non-clustered indexes are also de-allocated, but that
space is not freed until the overall transaction is complete, so that the
existing indexes can be snapped back in if need be.
If you're worried about this space usage, you could always use
SORT_IN_TEMPDB, althought that'll use more space, just not in your local
database. (and you could have TempDB on a different set of spindles).
James Hokes
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23%23eTGqN8DHA.2764@.TK2MSFTNGP09.phx.gbl...
No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks.|||Thanks. If you read carefully, you will see that I am NOT surprised at the fact that a clustered index took space. Quite the contrary actually.
-- Tom Moreau wrote: --
No surprises here. In order to create a clustered index on a table, yo
need 1.2X the size of the table's data in free space available. Thi
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once th
clustered index has been created, it frees up that space again. Thus, you
database will have a fair bit of unused space when you are done
Check out Kalen Delaney's "Inside SQl Server 2000" for more details
-
To
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDB
SQL Server MV
Columnist, SQL Server Professiona
Toronto, ON Canad
www.pinnaclepublishing.com/sq
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in messag
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com..
People
Scenario 1
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. I
has a column POSTALCODE. This table has no indexes/keys yet. The databas
size as per SQL Server enterprise manager is 77MB. I create a clustere
index on the POSTALCODE column. The database size now is 135MB and spac
available is 24MB. I shrink the database. The database size is now back t
77MB. YIKES! Does the clustered index take no space'?
Scenario 2
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. I
has a column POSTALCODE. This table has no indexes/keys yet. The databas
size as per SQL Server enterprise manager is 77MB. I create a clustere
index on the POSTALCODE column. The database size now is 135MB and spac
available is 24MB
I run sp_spaceused @.updateusage=true. The database size is now 135MB and th
space available is 74MB. Want more? I shrink the database and lo behol
everything's intact!!!!!!!!!!!!! What the f...
Can anyone, I mean ANYONE, including all you MVPs explain this sort of
typical MS crap
Thanks|||This is a multi-part message in MIME format.
--=_NextPart_000_0104_01C3F0B4.E5AA0410
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Then I guess I don't understand your problem. The growth of the database is
in reaction to having to create a clustered index. It frees the space - but
does not shrink the database - when the index build is complete.
As for the use of @.updateusage, a better test would be to update the usage
before and after the clustered index build, in order to get accurate
information. It appears from your experience that Enterprise Manager did
not update usage before querying the space used.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
-- Tom Moreau wrote: --
No surprises here. In order to create a clustered index on a table,
you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once
the
clustered index has been created, it frees up that space again. Thus,
your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
has a column POSTALCODE. This table has no indexes/keys yet. The
database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and
space
available is 24MB. I shrink the database. The database size is now back
to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
has a column POSTALCODE. This table has no indexes/keys yet. The
database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and
space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of
a
typical MS crap?
Thanks
--=_NextPart_000_0104_01C3F0B4.E5AA0410
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Then I guess I don't understand your problem. The growth of the database is in reaction to having to =create a clustered index. It frees the space - but does not shrink the =database - when the index build is complete.
As for the use of @.updateusage, a =better test would be to update the usage before and after the clustered index build, =in order to get accurate information. It appears from your experience =that Enterprise Manager did not update usage before querying the space used.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Frustrated..." wrote in message news:841=8A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...Thanks. If you read carefully, you will see that I am NOT surprised at the fact =that a clustered index took space. Quite the contrary actually. -- Tom =Moreau wrote: -- No =surprises here. In order to create a clustered index on a table, you need 1.2X the size of the table's data =in free space available. This equates to =1.2*77MB =3D 92.4MB - in addition to the existing 77MB. Once the clustered index has been created, it =frees up that space again. Thus, your database =will have a fair bit of unused space when you are =done. Check out Kalen Delaney's "Inside SQl =Server 2000" for more details. = -- Tom --- = Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL =Server MVP Columnist, SQL Server Professional Toronto, ON Canada http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql ="Frustrated..." wrote in message news:746=F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com... &nb=sp; People, =Scenario 1: I have a small DEMOGRAPHICS table that =has close to 500000 rows of data. It has a column =POSTALCODE. This table has no indexes/keys yet. The =database size as per SQL Server enterprise manager is 77MB. I create a clustered index on the POSTALCODE column. =The database size now is 135MB and space =available is 24MB. I shrink the database. The database size is now back to 77MB. YIKES! Does the clustered index =take no space' =Scenario 2: I have a small DEMOGRAPHICS table that =has close to 500000 rows of data. It has a column =POSTALCODE. This table has no indexes/keys yet. The =database size as per SQL Server enterprise manager is 77MB. I create a clustered index on the POSTALCODE column. =The database size now is 135MB and space =available is 24MB. I run sp_spaceused @.updateusage=3Dtrue. The database size is now 135MB and the space available is 74MB. Want more? I =shrink the database and lo behold everything's intact!!!!!!!!!!!!! What the f...? Can anyone, I mean ANYONE, including all =you MVPs explain this sort of a typical MS crap? Thanks

--=_NextPart_000_0104_01C3F0B4.E5AA0410--|||Your frustration stems from a fundamental lack of understanding... A table
that took up 77MB with no indexes on it would still take up 77MB once a
clustered index is applied because a clustered index is the same thing as an
Index Organized Table in Oracle (if you are familiar with Oracle).
Basically the leaf level of the clustered index IS the table. This is the
reason you can have only one per table. Obviously, there would be a few
extra pages for the top and middle layers of the index, but in most cases
the amount of space that they occupy are negligible.
Now, if you create non-clustered indexes those will definitely use extra
space...
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
> Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
> -- Tom Moreau wrote: --
> No surprises here. In order to create a clustered index on a table,
you
> need 1.2X the size of the table's data in free space available. This
> equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB.
Once the
> clustered index has been created, it frees up that space again.
Thus, your
> database will have a fair bit of unused space when you are done.
> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Frustrated..." <anonymous@.discussions.microsoft.com> wrote in
message
> news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
> People,
> Scenario 1:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB. I shrink the database. The database size is now
back to
> 77MB. YIKES! Does the clustered index take no space'
> Scenario 2:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB.
> I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
> space available is 74MB. Want more? I shrink the database and lo
behold
> everything's intact!!!!!!!!!!!!! What the f...?
> Can anyone, I mean ANYONE, including all you MVPs explain this sort
of a
> typical MS crap?
> Thanks|||This is a multi-part message in MIME format.
--=_NextPart_000_0027_01C3F0C9.D41032F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
One other reason for the apparent discrepancy is that the un-indexed table
could have been heavily fragmented. Adding a clustered index automatically
defrags the table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Don Peterson" <no1@.nunya.com> wrote in message
news:ejwVbtO8DHA.1040@.TK2MSFTNGP10.phx.gbl...
Your frustration stems from a fundamental lack of understanding... A table
that took up 77MB with no indexes on it would still take up 77MB once a
clustered index is applied because a clustered index is the same thing as an
Index Organized Table in Oracle (if you are familiar with Oracle).
Basically the leaf level of the clustered index IS the table. This is the
reason you can have only one per table. Obviously, there would be a few
extra pages for the top and middle layers of the index, but in most cases
the amount of space that they occupy are negligible.
Now, if you create non-clustered indexes those will definitely use extra
space...
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
> Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
> -- Tom Moreau wrote: --
> No surprises here. In order to create a clustered index on a table,
you
> need 1.2X the size of the table's data in free space available. This
> equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB.
Once the
> clustered index has been created, it frees up that space again.
Thus, your
> database will have a fair bit of unused space when you are done.
> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Frustrated..." <anonymous@.discussions.microsoft.com> wrote in
message
> news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
> People,
> Scenario 1:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB. I shrink the database. The database size is now
back to
> 77MB. YIKES! Does the clustered index take no space'
> Scenario 2:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB.
> I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
> space available is 74MB. Want more? I shrink the database and lo
behold
> everything's intact!!!!!!!!!!!!! What the f...?
> Can anyone, I mean ANYONE, including all you MVPs explain this sort
of a
> typical MS crap?
> Thanks
--=_NextPart_000_0027_01C3F0C9.D41032F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

One other reason for the apparent =discrepancy is that the un-indexed table could have been heavily fragmented. =Adding a clustered index automatically defrags the table.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Don Peterson" wrote in message news:ejwVbtO8DHA.1040=@.TK2MSFTNGP10.phx.gbl...Your frustration stems from a fundamental lack of understanding... A tablethat took up 77MB with no indexes on it would still take up =77MB once aclustered index is applied because a clustered index is the same =thing as anIndex Organized Table in Oracle (if you are familiar with Oracle).Basically the leaf level of the clustered index IS the =table. This is thereason you can have only one per table. Obviously, =there would be a fewextra pages for the top and middle layers of the =index, but in most casesthe amount of space that they occupy are =negligible.Now, if you create non-clustered indexes those will definitely use extraspace..."Frustrated..." wrote in messagenews:841=8A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...> Thanks. If you read carefully, you will see that I am NOT surprised at thefact that a clustered index took space. Quite the contrary actually.>> -- Tom Moreau =wrote: -->> No surprises =here. In order to create a clustered index on a table,you> need 1.2X the size =of the table's data in free space available. This> equates to 1.2*77MB =3D =92.4MB - in addition to the existing 77MB.Once =the> clustered index has been created, it frees up that space again.Thus, =your> database will have a fair bit =of unused space when you are =done.>> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.>> --> Tom>> --->&n=bsp; Thomas A. Moreau, BSc, PhD, MCSE, =MCDBA> SQL Server MVP> Columnist, SQL =Server Professional> Toronto, ON Canada>http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql>>> "Frustrated..." wrote inmessage> news:746=F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...> =; People,>> Scenario 1:> I have a small DEMOGRAPHICS =table that has close to 500000 rows ofdata. =It> has a column POSTALCODE. This table has no indexes/keys yet. Thedatabase> size as per SQL =Server enterprise manager is 77MB. I create aclustered> index on the =POSTALCODE column. The database size now is 135MB andspace> available is 24MB. I =shrink the database. The database size is nowback to> 77MB. YIKES! Does the clustered =index take no space'>> =Scenario 2:> I have a small DEMOGRAPHICS =table that has close to 500000 rows ofdata. =It> has a column POSTALCODE. This table has no indexes/keys yet. Thedatabase> size as per SQL =Server enterprise manager is 77MB. I create aclustered> index on the =POSTALCODE column. The database size now is 135MB andspace> available is 24MB.>> I run sp_spaceused @.updateusage=3Dtrue. The database size is now 135MBand the> space available is 74MB. Want =more? I shrink the database and =lobehold> everything's intact!!!!!!!!!!!!! What the f...?>> Can anyone, I mean =ANYONE, including all you MVPs explain this sortof a> typical MS crap?>> =Thanks

--=_NextPart_000_0027_01C3F0C9.D41032F0--

Freakin X-files type unexplained bull crap...

People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database si
ze as per SQL Server enterprise manager is 77MB. I create a clustered index
on the POSTALCODE column. T
he database size now is 135MB and space available is 24MB. I shrink the data
base. The database size is now back to 77MB. YIKES! Does the clustered index
take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database si
ze as per SQL Server enterprise manager is 77MB. I create a clustered index
on the POSTALCODE column. T
he database size now is 135MB and space available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold eve
rything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a typ
ical MS crap?
Thanks.No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks.|||Don't forget that any non-clustered indexes are also de-allocated, but that
space is not freed until the overall transaction is complete, so that the
existing indexes can be snapped back in if need be.
If you're worried about this space usage, you could always use
SORT_IN_TEMPDB, althought that'll use more space, just not in your local
database. (and you could have TempDB on a different set of spindles).
James Hokes
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23%23eTGqN8DHA.2764@.TK2MSFTNGP09.phx.gbl...
No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks.|||Thanks. If you read carefully, you will see that I am NOT surprised at the f
act that a clustered index took space. Quite the contrary actually.
-- Tom Moreau wrote: --
No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks|||Then I guess I don't understand your problem. The growth of the database is
in reaction to having to create a clustered index. It frees the space - but
does not shrink the database - when the index build is complete.
As for the use of @.updateusage, a better test would be to update the usage
before and after the clustered index build, in order to get accurate
information. It appears from your experience that Enterprise Manager did
not update usage before querying the space used.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
-- Tom Moreau wrote: --
No surprises here. In order to create a clustered index on a table,
you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once
the
clustered index has been created, it frees up that space again. Thus,
your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
has a column POSTALCODE. This table has no indexes/keys yet. The
database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and
space
available is 24MB. I shrink the database. The database size is now back
to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
has a column POSTALCODE. This table has no indexes/keys yet. The
database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and
space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of
a
typical MS crap?
Thanks|||Your frustration stems from a fundamental lack of understanding... A table
that took up 77MB with no indexes on it would still take up 77MB once a
clustered index is applied because a clustered index is the same thing as an
Index Organized Table in Oracle (if you are familiar with Oracle).
Basically the leaf level of the clustered index IS the table. This is the
reason you can have only one per table. Obviously, there would be a few
extra pages for the top and middle layers of the index, but in most cases
the amount of space that they occupy are negligible.
Now, if you create non-clustered indexes those will definitely use extra
space...
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
> Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
> -- Tom Moreau wrote: --
> No surprises here. In order to create a clustered index on a table,
you
> need 1.2X the size of the table's data in free space available. This
> equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB.
Once the
> clustered index has been created, it frees up that space again.
Thus, your
> database will have a fair bit of unused space when you are done.
> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Frustrated..." <anonymous@.discussions.microsoft.com> wrote in
message
> news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
> People,
> Scenario 1:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB. I shrink the database. The database size is now
back to
> 77MB. YIKES! Does the clustered index take no space'
> Scenario 2:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB.
> I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
> space available is 74MB. Want more? I shrink the database and lo
behold
> everything's intact!!!!!!!!!!!!! What the f...?
> Can anyone, I mean ANYONE, including all you MVPs explain this sort
of a
> typical MS crap?
> Thanks|||One other reason for the apparent discrepancy is that the un-indexed table
could have been heavily fragmented. Adding a clustered index automatically
defrags the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Don Peterson" <no1@.nunya.com> wrote in message
news:ejwVbtO8DHA.1040@.TK2MSFTNGP10.phx.gbl...
Your frustration stems from a fundamental lack of understanding... A table
that took up 77MB with no indexes on it would still take up 77MB once a
clustered index is applied because a clustered index is the same thing as an
Index Organized Table in Oracle (if you are familiar with Oracle).
Basically the leaf level of the clustered index IS the table. This is the
reason you can have only one per table. Obviously, there would be a few
extra pages for the top and middle layers of the index, but in most cases
the amount of space that they occupy are negligible.
Now, if you create non-clustered indexes those will definitely use extra
space...
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
> Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
> -- Tom Moreau wrote: --
> No surprises here. In order to create a clustered index on a table,
you
> need 1.2X the size of the table's data in free space available. This
> equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB.
Once the
> clustered index has been created, it frees up that space again.
Thus, your
> database will have a fair bit of unused space when you are done.
> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Frustrated..." <anonymous@.discussions.microsoft.com> wrote in
message
> news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
> People,
> Scenario 1:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB. I shrink the database. The database size is now
back to
> 77MB. YIKES! Does the clustered index take no space'
> Scenario 2:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB.
> I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
> space available is 74MB. Want more? I shrink the database and lo
behold
> everything's intact!!!!!!!!!!!!! What the f...?
> Can anyone, I mean ANYONE, including all you MVPs explain this sort
of a
> typical MS crap?
> Thanks

Sunday, February 19, 2012

Fragmented Indexes

I have a query to find all indexes that are fragmented over 10%. The query
also excludes any where the page count is less than 8 and the index size is
smaller than 64k. Immediately after running my maintenance script that
reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
returns quite a few fragmented indexes. I've also insured that all tables
have a clustered index (most of them on an identity column). Why do I still
show fragmented indexes? Is there some reason they all won't defragment?
Any help is greatly appreciated!Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
calculated, things like extent order etc. As the index gets to some realistic size, you will not be
affected by this "misreport".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>I have a query to find all indexes that are fragmented over 10%. The query
> also excludes any where the page count is less than 8 and the index size is
> smaller than 64k. Immediately after running my maintenance script that
> reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
> returns quite a few fragmented indexes. I've also insured that all tables
> have a clustered index (most of them on an identity column). Why do I still
> show fragmented indexes? Is there some reason they all won't defragment?
> Any help is greatly appreciated!|||Thanks for your quick response! Any chance you have a link to a page that
shows the 1000 pages MS recommends? Any other MS recommendation pages?
"Tibor Karaszi" wrote:
> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
> calculated, things like extent order etc. As the index gets to some realistic size, you will not be
> affected by this "misreport".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
> >I have a query to find all indexes that are fragmented over 10%. The query
> > also excludes any where the page count is less than 8 and the index size is
> > smaller than 64k. Immediately after running my maintenance script that
> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
> > returns quite a few fragmented indexes. I've also insured that all tables
> > have a clustered index (most of them on an identity column). Why do I still
> > show fragmented indexes? Is there some reason they all won't defragment?
> > Any help is greatly appreciated!
>|||Below might have to 1000 pages recommendation:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
> Thanks for your quick response! Any chance you have a link to a page that
> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>
> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only
>> two
>> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation
>> is
>> calculated, things like extent order etc. As the index gets to some realistic size, you will not
>> be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The query
>> > also excludes any where the page count is less than 8 and the index size is
>> > smaller than 64k. Immediately after running my maintenance script that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
>> > returns quite a few fragmented indexes. I've also insured that all tables
>> > have a clustered index (most of them on an identity column). Why do I still
>> > show fragmented indexes? Is there some reason they all won't defragment?
>> > Any help is greatly appreciated!|||Or then again, you might check this paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
It specifically says:
Generally, you should not be concerned with fragmentation levels of indexes
with less than 1,000 pages. In the tests, indexes containing more than
10,000 pages realized performance gains, with the biggest gains on indexes
with significantly more pages (greater than 50,000 pages).
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Below might have to 1000 pages recommendation:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page
>> that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000
>> pages). You can, with only two
>> extents, still have reported fragmentation after a rebuild. This is due
>> to the way fragmentation is
>> calculated, things like extent order etc. As the index gets to some
>> realistic size, you will not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The
>> >query
>> > also excludes any where the page count is less than 8 and the index
>> > size is
>> > smaller than 64k. Immediately after running my maintenance script
>> > that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
>> > query still
>> > returns quite a few fragmented indexes. I've also insured that all
>> > tables
>> > have a clustered index (most of them on an identity column). Why do I
>> > still
>> > show fragmented indexes? Is there some reason they all won't
>> > defragment?
>> > Any help is greatly appreciated!
>|||Thank you both for your input! I'll change my maintenance script, and I'm
sure you've just saved us several thousand reindexing jobs that aren't
necessary on a weekly basis! Thanks again!
"Kalen Delaney" wrote:
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes
> with less than 1,000 pages. In the tests, indexes containing more than
> 10,000 pages realized performance gains, with the biggest gains on indexes
> with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> > Below might have to 1000 pages recommendation:
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Tony" <Tony@.discussions.microsoft.com> wrote in message
> > news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
> >> Thanks for your quick response! Any chance you have a link to a page
> >> that
> >> shows the 1000 pages MS recommends? Any other MS recommendation pages?
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Ignore indexes with less than at least 500 pages (MS recommends 1000
> >> pages). You can, with only two
> >> extents, still have reported fragmentation after a rebuild. This is due
> >> to the way fragmentation is
> >> calculated, things like extent order etc. As the index gets to some
> >> realistic size, you will not be
> >> affected by this "misreport".
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> >> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
> >> >I have a query to find all indexes that are fragmented over 10%. The
> >> >query
> >> > also excludes any where the page count is less than 8 and the index
> >> > size is
> >> > smaller than 64k. Immediately after running my maintenance script
> >> > that
> >> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
> >> > query still
> >> > returns quite a few fragmented indexes. I've also insured that all
> >> > tables
> >> > have a clustered index (most of them on an identity column). Why do I
> >> > still
> >> > show fragmented indexes? Is there some reason they all won't
> >> > defragment?
> >> > Any help is greatly appreciated!
> >>
> >
>
>|||Thanks Kalen. That was, of course, indeed the paper I meant to refer to. I have no idea why I posted
an URL to the I/O basic paper...
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%238Hxd5LeIHA.4464@.TK2MSFTNGP02.phx.gbl...
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000
> pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with
> the biggest gains on indexes with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Below might have to 1000 pages recommendation:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only
>> two
>> extents, still have reported fragmentation after a rebuild. This is due to the way
>> fragmentation is
>> calculated, things like extent order etc. As the index gets to some realistic size, you will
>> not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The query
>> > also excludes any where the page count is less than 8 and the index size is
>> > smaller than 64k. Immediately after running my maintenance script that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
>> > returns quite a few fragmented indexes. I've also insured that all tables
>> > have a clustered index (most of them on an identity column). Why do I still
>> > show fragmented indexes? Is there some reason they all won't defragment?
>> > Any help is greatly appreciated!
>>
>|||You probably have them all in a list, just like I do, and you grabbed the
wrong one. I just made absolutely sure that I grabbed the right one!
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23JZtwKMeIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Thanks Kalen. That was, of course, indeed the paper I meant to refer to. I
> have no idea why I posted an URL to the I/O basic paper...
> :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%238Hxd5LeIHA.4464@.TK2MSFTNGP02.phx.gbl...
>> Or then again, you might check this paper:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> It specifically says:
>> Generally, you should not be concerned with fragmentation levels of
>> indexes with less than 1,000 pages. In the tests, indexes containing more
>> than 10,000 pages realized performance gains, with the biggest gains on
>> indexes with significantly more pages (greater than 50,000 pages).
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Below might have to 1000 pages recommendation:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page
>> that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000
>> pages). You can, with only two
>> extents, still have reported fragmentation after a rebuild. This is
>> due to the way fragmentation is
>> calculated, things like extent order etc. As the index gets to some
>> realistic size, you will not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The
>> >query
>> > also excludes any where the page count is less than 8 and the index
>> > size is
>> > smaller than 64k. Immediately after running my maintenance script
>> > that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
>> > query still
>> > returns quite a few fragmented indexes. I've also insured that all
>> > tables
>> > have a clustered index (most of them on an identity column). Why do
>> > I still
>> > show fragmented indexes? Is there some reason they all won't
>> > defragment?
>> > Any help is greatly appreciated!
>>
>>
>

Fragmented Indexes

Fragmentation Problem

SQL Server 2005 SP2.
I have a table with 1964 rows of data in it. I have a primary key and 4
non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
column per index) and the other 2 indexes are on the int column (Again 1
column per index).
The problem is, when I go to "Properties" then select the "fragmentation"
of the indexes, for the first index (varchar(80)) I have;
Page fullness: 99.72%
Total Fragmentation: 85.71%
Second index (varchar(80)) I have;
Page fullness: 86.72%
Total Fragmentation: 87.50%
The third index (int column)
Page fullness: 88.95%
Total Fragmentation: 66.67%
The fourth index (int column)
Page fullness: 84.90%
Total Fragmentation: 75.00%
I have re-created the table in another database and add the indexes but the
similar result. Why do I have the fragmentation on the indexes ?. Is it
because of the data type ?. I also tried drop-recreate, rebuild indexes and
update statistics but result is the same or similar. Other tables I have have
0.0% Fragmentation.
Thanks for any help.
1) how much free space is in the database? You need contiguous free space
for indexes to be laid down contiguously during their creation or defrag
processes. Double the size of the database if possible then drop and create
the indexes and see what you get.
2) How did you populate the table in the other database? INSERT scripts?
If so, that could be the reason for fragmentation. Also, did you update the
fields at all after they were in place? That too can lead to fragmentation.
3) what order did you create the indexes in on the second database?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
> SQL Server 2005 SP2.
> I have a table with 1964 rows of data in it. I have a primary key and 4
> non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
> column per index) and the other 2 indexes are on the int column (Again 1
> column per index).
> The problem is, when I go to "Properties" then select the "fragmentation"
> of the indexes, for the first index (varchar(80)) I have;
> Page fullness: 99.72%
> Total Fragmentation: 85.71%
> Second index (varchar(80)) I have;
> Page fullness: 86.72%
> Total Fragmentation: 87.50%
> The third index (int column)
> Page fullness: 88.95%
> Total Fragmentation: 66.67%
> The fourth index (int column)
> Page fullness: 84.90%
> Total Fragmentation: 75.00%
>
> I have re-created the table in another database and add the indexes but
> the
> similar result. Why do I have the fragmentation on the indexes ?. Is it
> because of the data type ?. I also tried drop-recreate, rebuild indexes
> and
> update statistics but result is the same or similar. Other tables I have
> have
> 0.0% Fragmentation.
> Thanks for any help.
|||The table is so small that the reported fragmentation will make no
difference to performance.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
SQL Server 2005 SP2.
I have a table with 1964 rows of data in it. I have a primary key and 4
non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
column per index) and the other 2 indexes are on the int column (Again 1
column per index).
The problem is, when I go to "Properties" then select the "fragmentation"
of the indexes, for the first index (varchar(80)) I have;
Page fullness: 99.72%
Total Fragmentation: 85.71%
Second index (varchar(80)) I have;
Page fullness: 86.72%
Total Fragmentation: 87.50%
The third index (int column)
Page fullness: 88.95%
Total Fragmentation: 66.67%
The fourth index (int column)
Page fullness: 84.90%
Total Fragmentation: 75.00%
I have re-created the table in another database and add the indexes but the
similar result. Why do I have the fragmentation on the indexes ?. Is it
because of the data type ?. I also tried drop-recreate, rebuild indexes and
update statistics but result is the same or similar. Other tables I have
have
0.0% Fragmentation.
Thanks for any help.
|||1) Total space usage: 2,888.69 MB
Data Files Space Usage: 618.19 MB
Transaction Log Space Usage: 2,270.50 MB
Both data and log files set to 10% file growth and Unrestricted File Growth.
2) I have created the table from DDL (Without Primary key and indexes). I
have not updates the fields after the data import. Just select data from the
table.
3) (From script) Created the primary key first, then index 1 and 2 which are
the varchar(80) columns then the other 2 int columns.
"TheSQLGuru" wrote:

> 1) how much free space is in the database? You need contiguous free space
> for indexes to be laid down contiguously during their creation or defrag
> processes. Double the size of the database if possible then drop and create
> the indexes and see what you get.
> 2) How did you populate the table in the other database? INSERT scripts?
> If so, that could be the reason for fragmentation. Also, did you update the
> fields at all after they were in place? That too can lead to fragmentation.
> 3) what order did you create the indexes in on the second database?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
>
>
|||DXC,
You should not worry about fragmentation on a table with only 1,964 records.
One recommendation is to ignore fragmentation on indexes with less than
1,000 pages, as shown by dbcc showcontig.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"DXC" wrote:

> SQL Server 2005 SP2.
> I have a table with 1964 rows of data in it. I have a primary key and 4
> non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
> column per index) and the other 2 indexes are on the int column (Again 1
> column per index).
> The problem is, when I go to "Properties" then select the "fragmentation"
> of the indexes, for the first index (varchar(80)) I have;
> Page fullness: 99.72%
> Total Fragmentation: 85.71%
> Second index (varchar(80)) I have;
> Page fullness: 86.72%
> Total Fragmentation: 87.50%
> The third index (int column)
> Page fullness: 88.95%
> Total Fragmentation: 66.67%
> The fourth index (int column)
> Page fullness: 84.90%
> Total Fragmentation: 75.00%
>
> I have re-created the table in another database and add the indexes but the
> similar result. Why do I have the fragmentation on the indexes ?. Is it
> because of the data type ?. I also tried drop-recreate, rebuild indexes and
> update statistics but result is the same or similar. Other tables I have have
> 0.0% Fragmentation.
> Thanks for any help.
|||Yes but why would it show some 87.00% fragmentation ?. By the way, the
primary key column (int) has fragmentation at 0.0%.
"Tom Moreau" wrote:

> The table is so small that the reported fragmentation will make no
> difference to performance.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
> SQL Server 2005 SP2.
> I have a table with 1964 rows of data in it. I have a primary key and 4
> non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
> column per index) and the other 2 indexes are on the int column (Again 1
> column per index).
> The problem is, when I go to "Properties" then select the "fragmentation"
> of the indexes, for the first index (varchar(80)) I have;
> Page fullness: 99.72%
> Total Fragmentation: 85.71%
> Second index (varchar(80)) I have;
> Page fullness: 86.72%
> Total Fragmentation: 87.50%
> The third index (int column)
> Page fullness: 88.95%
> Total Fragmentation: 66.67%
> The fourth index (int column)
> Page fullness: 84.90%
> Total Fragmentation: 75.00%
>
> I have re-created the table in another database and add the indexes but the
> similar result. Why do I have the fragmentation on the indexes ?. Is it
> because of the data type ?. I also tried drop-recreate, rebuild indexes and
> update statistics but result is the same or similar. Other tables I have
> have
> 0.0% Fragmentation.
> Thanks for any help.
>
|||Yes..........This is what shows after the DBCC SHOWCONTIG but
fragmentation on the index properties shows something else.........Thanks.
DBCC SHOWCONTIG scanning 'customerinfo' table...
Table: 'customerinfo' (1288391659); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 94
- Extents Scanned.......................: 12
- Extent Switches.......................: 11
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 100.00% [12:12]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 265.5
- Avg. Page Density (full)................: 96.72%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> DXC,
> You should not worry about fragmentation on a table with only 1,964 records.
> One recommendation is to ignore fragmentation on indexes with less than
> 1,000 pages, as shown by dbcc showcontig.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "DXC" wrote:
|||Your output doesn't reveal how much actual data is being used in the
database, just that 618MB is allocated.
Your output also reveals that you probably have your database set to FULL
recovery mode but are not doing transaction log backups. If so your tlog
will continue to grow until it fills up the disk it is on, at which point
your database activity will come to a grinding halt. :-)
As others pointed out, fragmentation on a table with 2000 rows is pretty
much a non-issue from a performance standpoint.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:51C396D7-ECE8-4588-91F7-E078620A481E@.microsoft.com...[vbcol=seagreen]
> 1) Total space usage: 2,888.69 MB
> Data Files Space Usage: 618.19 MB
> Transaction Log Space Usage: 2,270.50 MB
> Both data and log files set to 10% file growth and Unrestricted File
> Growth.
> 2) I have created the table from DDL (Without Primary key and indexes). I
> have not updates the fields after the data import. Just select data from
> the
> table.
> 3) (From script) Created the primary key first, then index 1 and 2 which
> are
> the varchar(80) columns then the other 2 int columns.
>
>
> "TheSQLGuru" wrote:

Fragmentation Problem

SQL Server 2005 SP2.
I have a table with 1964 rows of data in it. I have a primary key and 4
non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
column per index) and the other 2 indexes are on the int column (Again 1
column per index).
The problem is, when I go to "Properties" then select the "fragmentation"
of the indexes, for the first index (varchar(80)) I have;
Page fullness: 99.72%
Total Fragmentation: 85.71%
Second index (varchar(80)) I have;
Page fullness: 86.72%
Total Fragmentation: 87.50%
The third index (int column)
Page fullness: 88.95%
Total Fragmentation: 66.67%
The fourth index (int column)
Page fullness: 84.90%
Total Fragmentation: 75.00%
I have re-created the table in another database and add the indexes but the
similar result. Why do I have the fragmentation on the indexes ?. Is it
because of the data type ?. I also tried drop-recreate, rebuild indexes and
update statistics but result is the same or similar. Other tables I have hav
e
0.0% Fragmentation.
Thanks for any help.1) how much free space is in the database? You need contiguous free space
for indexes to be laid down contiguously during their creation or defrag
processes. Double the size of the database if possible then drop and create
the indexes and see what you get.
2) How did you populate the table in the other database? INSERT scripts?
If so, that could be the reason for fragmentation. Also, did you update the
fields at all after they were in place? That too can lead to fragmentation.
3) what order did you create the indexes in on the second database?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
> SQL Server 2005 SP2.
> I have a table with 1964 rows of data in it. I have a primary key and 4
> non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
> column per index) and the other 2 indexes are on the int column (Again 1
> column per index).
> The problem is, when I go to "Properties" then select the "fragmentation"
> of the indexes, for the first index (varchar(80)) I have;
> Page fullness: 99.72%
> Total Fragmentation: 85.71%
> Second index (varchar(80)) I have;
> Page fullness: 86.72%
> Total Fragmentation: 87.50%
> The third index (int column)
> Page fullness: 88.95%
> Total Fragmentation: 66.67%
> The fourth index (int column)
> Page fullness: 84.90%
> Total Fragmentation: 75.00%
>
> I have re-created the table in another database and add the indexes but
> the
> similar result. Why do I have the fragmentation on the indexes ?. Is it
> because of the data type ?. I also tried drop-recreate, rebuild indexes
> and
> update statistics but result is the same or similar. Other tables I have
> have
> 0.0% Fragmentation.
> Thanks for any help.|||The table is so small that the reported fragmentation will make no
difference to performance.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
SQL Server 2005 SP2.
I have a table with 1964 rows of data in it. I have a primary key and 4
non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
column per index) and the other 2 indexes are on the int column (Again 1
column per index).
The problem is, when I go to "Properties" then select the "fragmentation"
of the indexes, for the first index (varchar(80)) I have;
Page fullness: 99.72%
Total Fragmentation: 85.71%
Second index (varchar(80)) I have;
Page fullness: 86.72%
Total Fragmentation: 87.50%
The third index (int column)
Page fullness: 88.95%
Total Fragmentation: 66.67%
The fourth index (int column)
Page fullness: 84.90%
Total Fragmentation: 75.00%
I have re-created the table in another database and add the indexes but the
similar result. Why do I have the fragmentation on the indexes ?. Is it
because of the data type ?. I also tried drop-recreate, rebuild indexes and
update statistics but result is the same or similar. Other tables I have
have
0.0% Fragmentation.
Thanks for any help.|||1) Total space usage: 2,888.69 MB
Data Files Space Usage: 618.19 MB
Transaction Log Space Usage: 2,270.50 MB
Both data and log files set to 10% file growth and Unrestricted File Growth.
2) I have created the table from DDL (Without Primary key and indexes). I
have not updates the fields after the data import. Just select data from the
table.
3) (From script) Created the primary key first, then index 1 and 2 which are
the varchar(80) columns then the other 2 int columns.
"TheSQLGuru" wrote:

> 1) how much free space is in the database? You need contiguous free space
> for indexes to be laid down contiguously during their creation or defrag
> processes. Double the size of the database if possible then drop and crea
te
> the indexes and see what you get.
> 2) How did you populate the table in the other database? INSERT scripts?
> If so, that could be the reason for fragmentation. Also, did you update t
he
> fields at all after they were in place? That too can lead to fragmentatio
n.
> 3) what order did you create the indexes in on the second database?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
>
>|||DXC,
You should not worry about fragmentation on a table with only 1,964 records.
One recommendation is to ignore fragmentation on indexes with less than
1,000 pages, as shown by dbcc showcontig.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"DXC" wrote:

> SQL Server 2005 SP2.
> I have a table with 1964 rows of data in it. I have a primary key and 4
> non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
> column per index) and the other 2 indexes are on the int column (Again 1
> column per index).
> The problem is, when I go to "Properties" then select the "fragmentation"
> of the indexes, for the first index (varchar(80)) I have;
> Page fullness: 99.72%
> Total Fragmentation: 85.71%
> Second index (varchar(80)) I have;
> Page fullness: 86.72%
> Total Fragmentation: 87.50%
> The third index (int column)
> Page fullness: 88.95%
> Total Fragmentation: 66.67%
> The fourth index (int column)
> Page fullness: 84.90%
> Total Fragmentation: 75.00%
>
> I have re-created the table in another database and add the indexes but th
e
> similar result. Why do I have the fragmentation on the indexes ?. Is it
> because of the data type ?. I also tried drop-recreate, rebuild indexes an
d
> update statistics but result is the same or similar. Other tables I have h
ave
> 0.0% Fragmentation.
> Thanks for any help.|||Yes but why would it show some 87.00% fragmentation ?. By the way, the
primary key column (int) has fragmentation at 0.0%.
"Tom Moreau" wrote:

> The table is so small that the reported fragmentation will make no
> difference to performance.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
> SQL Server 2005 SP2.
> I have a table with 1964 rows of data in it. I have a primary key and 4
> non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
> column per index) and the other 2 indexes are on the int column (Again 1
> column per index).
> The problem is, when I go to "Properties" then select the "fragmentation"
> of the indexes, for the first index (varchar(80)) I have;
> Page fullness: 99.72%
> Total Fragmentation: 85.71%
> Second index (varchar(80)) I have;
> Page fullness: 86.72%
> Total Fragmentation: 87.50%
> The third index (int column)
> Page fullness: 88.95%
> Total Fragmentation: 66.67%
> The fourth index (int column)
> Page fullness: 84.90%
> Total Fragmentation: 75.00%
>
> I have re-created the table in another database and add the indexes but th
e
> similar result. Why do I have the fragmentation on the indexes ?. Is it
> because of the data type ?. I also tried drop-recreate, rebuild indexes an
d
> update statistics but result is the same or similar. Other tables I have
> have
> 0.0% Fragmentation.
> Thanks for any help.
>|||Yes..........This is what shows after the DBCC SHOWCONTIG but
fragmentation on the index properties shows something else.........Thanks
.
DBCC SHOWCONTIG scanning 'customerinfo' table...
Table: 'customerinfo' (1288391659); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 94
- Extents Scanned.......................: 12
- Extent Switches.......................: 11
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 100.00% [12:12]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 265.5
- Avg. Page Density (full)................: 96.72%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> DXC,
> You should not worry about fragmentation on a table with only 1,964 record
s.
> One recommendation is to ignore fragmentation on indexes with less than
> 1,000 pages, as shown by dbcc showcontig.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "DXC" wrote:
>|||Your output doesn't reveal how much actual data is being used in the
database, just that 618MB is allocated.
Your output also reveals that you probably have your database set to FULL
recovery mode but are not doing transaction log backups. If so your tlog
will continue to grow until it fills up the disk it is on, at which point
your database activity will come to a grinding halt. :-)
As others pointed out, fragmentation on a table with 2000 rows is pretty
much a non-issue from a performance standpoint.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:51C396D7-ECE8-4588-91F7-E078620A481E@.microsoft.com...[vbcol=seagreen]
> 1) Total space usage: 2,888.69 MB
> Data Files Space Usage: 618.19 MB
> Transaction Log Space Usage: 2,270.50 MB
> Both data and log files set to 10% file growth and Unrestricted File
> Growth.
> 2) I have created the table from DDL (Without Primary key and indexes). I
> have not updates the fields after the data import. Just select data from
> the
> table.
> 3) (From script) Created the primary key first, then index 1 and 2 which
> are
> the varchar(80) columns then the other 2 int columns.
>
>
> "TheSQLGuru" wrote:
>

Monday, March 26, 2012

FT indexes are not populating

Monday, March 12, 2012

frequently updated tables

Friday, February 24, 2012

Freakin X-files type unexplained bull crap...

Freakin X-files type unexplained bull crap...

Sunday, February 19, 2012

Fragmented Indexes

Fragmented Indexes

Fragmentation Problem

Fragmentation Problem

Get Next Number

Blog Archive

About Me