index hints on deletes

Can I not use index hints on delete statements as below ? Using SQL 2005
delete
from dbo.table1 WITH (index(idx_test))
where col1 <= 5
Are you getting an error? If so it would be nice to know what.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> Can I not use index hints on delete statements as below ? Using SQL 2005
> delete from dbo.table1 WITH (index(idx_test))
> where col1 <= 5
|||Msg 1069, Level 15, State 1, Line 3
Index hints are only allowed in a FROM clause.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Are you getting an error? If so it would be nice to know what.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>
|||How about this:
delete a
from dbo.table1 AS a WITH (index(idx_test))
where a.col1 <= 5
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> Msg 1069, Level 15, State 1, Line 3
> Index hints are only allowed in a FROM clause.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||or just
DELETE table1 from table1 with(index(idx_test))
WHERE col <= 5
But as far as I can tell from the BOL syntax description, the original
DELETE statement is perfectly legal. Is this some kind of bug?
Linchi
"Andrew J. Kelly" wrote:

> How about this:
> delete a
> from dbo.table1 AS a WITH (index(idx_test))
> where a.col1 <= 5
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>
|||What I'm reading is that DELETE only takes hints from a limited list
<table_hint_limited> and index hints are not in that list.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3E750688-DD9F-4567-BD65-515160B60119@.microsoft.com...[vbcol=seagreen]
> or just
> DELETE table1 from table1 with(index(idx_test))
> WHERE col <= 5
> But as far as I can tell from the BOL syntax description, the original
> DELETE statement is perfectly legal. Is this some kind of bug?
> Linchi
> "Andrew J. Kelly" wrote:

index hints on deletes

Can I not use index hints on delete statements as below ? Using SQL 2005
delete
from dbo.table1 WITH (index(idx_test))
where col1 <= 5Are you getting an error? If so it would be nice to know what.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> Can I not use index hints on delete statements as below ? Using SQL 2005
> delete from dbo.table1 WITH (index(idx_test))
> where col1 <= 5|||Msg 1069, Level 15, State 1, Line 3
Index hints are only allowed in a FROM clause.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Are you getting an error? If so it would be nice to know what.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> Can I not use index hints on delete statements as below ? Using SQL 2005
>> delete from dbo.table1 WITH (index(idx_test))
>> where col1 <= 5
>|||How about this:
delete a
from dbo.table1 AS a WITH (index(idx_test))
where a.col1 <= 5
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> Msg 1069, Level 15, State 1, Line 3
> Index hints are only allowed in a FROM clause.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> Are you getting an error? If so it would be nice to know what.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> Can I not use index hints on delete statements as below ? Using SQL 2005
>> delete from dbo.table1 WITH (index(idx_test))
>> where col1 <= 5
>|||or just
DELETE table1 from table1 with(index(idx_test))
WHERE col <= 5
But as far as I can tell from the BOL syntax description, the original
DELETE statement is perfectly legal. Is this some kind of bug?
Linchi
"Andrew J. Kelly" wrote:
> How about this:
> delete a
> from dbo.table1 AS a WITH (index(idx_test))
> where a.col1 <= 5
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> > Msg 1069, Level 15, State 1, Line 3
> > Index hints are only allowed in a FROM clause.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> >> Are you getting an error? If so it would be nice to know what.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Hassan" <hassan@.test.com> wrote in message
> >> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> >> Can I not use index hints on delete statements as below ? Using SQL 2005
> >>
> >> delete from dbo.table1 WITH (index(idx_test))
> >> where col1 <= 5
> >>
> >
>|||What I'm reading is that DELETE only takes hints from a limited list
<table_hint_limited> and index hints are not in that list.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3E750688-DD9F-4567-BD65-515160B60119@.microsoft.com...
> or just
> DELETE table1 from table1 with(index(idx_test))
> WHERE col <= 5
> But as far as I can tell from the BOL syntax description, the original
> DELETE statement is perfectly legal. Is this some kind of bug?
> Linchi
> "Andrew J. Kelly" wrote:
>> How about this:
>> delete a
>> from dbo.table1 AS a WITH (index(idx_test))
>> where a.col1 <= 5
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>> > Msg 1069, Level 15, State 1, Line 3
>> > Index hints are only allowed in a FROM clause.
>> >
>> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> > news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> >> Are you getting an error? If so it would be nice to know what.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Hassan" <hassan@.test.com> wrote in message
>> >> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> >> Can I not use index hints on delete statements as below ? Using SQL
>> >> 2005
>> >>
>> >> delete from dbo.table1 WITH (index(idx_test))
>> >> where col1 <= 5
>> >>
>> >
>>

Index fragmentation

I've got 99% fragmentation on a clustered index of a table with 29699
rows (there have been just 29699 inserts, no updates, no deletes).
There are 8 columns on the table with an int and a char(32) being the
unique clustered index - in that order - and I'm pretty sure that the
rows are inserted in int,char(32) order. Average row size is 852,
there are 3769 pages with average fullness being 83%.
The database file was created as 3.5GB and has 51% space free. The
data file itself is only fragmented into 3 pieces according to Disk
Fragmenter.
I don't really understand why it has fragmented so much. All index
rows should be appended because they are inserted in key order, there
is plenty of space in the file - would it have been better to have
made the file smaller and done AutoGrow by 1MB'
The table also has a non-clustered index, which is hardly fragmented
at all.
The insert transaction consisted of many inserts on many different
tables, all with similar indexes, all inserted in key value order.
There are maybe 100 tables - inserts take place across the 100 tables,
then another group of inserts take place against all the tables. But
for any specific table any insert will be in key value order and
should therefore be appended (which seems to be supported by the page
fullness being 83%). But how can I avoid the logical fragmentation? I
know I can use DBCC DBREINDEX but this is happening so quickly in the
system that it is not reasonable to have to do it this soon (and it's
actually happening three quarters of the way through a data load).
There would have been hundreds of thousands of records inserted during
the transaction.
Thanks for any help.On Apr 26, 10:05 pm, phancey <d...@.2bytes.co.uk> wrote:
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
actually the database file was created without a size, then changed to
500MB, then changed again to 3.5GB, all before the majority of the
data was written to the tables (and certainly before any data was
written to this particular table).|||Can you give the exact statement you are executing to get this information,
and the exact, complete output you receive? Perhaps (hopefully) you are
misinterpreting the data.
--
TheSQLGuru
President
Indicium Resources, Inc.
"phancey" <deja@.2bytes.co.uk> wrote in message
news:1177621507.322821.214250@.o40g2000prh.googlegroups.com...
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
>|||Using SQL 2005, I view the properties of the index, then
"Fragmentation".
Also, my data load (which does a check on existing records before
inserting) grinds to a halt presumably because of this query. I have
managed to run the data load a few times without any problem but
unfortunately did not make enough notes on the exact setup because I
did not think there was going to be a problem (I'm pretty sure I did
check fragmentation of the indexes though and am pretty sure that
fragmentation was less than 10%). Now I cannot recreate that fast load
and am at my wits end to figure out what is different.
The database file sizes again are got from the properties page of the
database in Management Studio.
thanks
On Apr 26, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Can you give the exact statement you are executing to get this information,
> and the exact, complete output you receive? Perhaps (hopefully) you are
> misinterpreting the data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "phancey" <d...@.2bytes.co.uk> wrote in message
> news:1177621507.322821.214250@.o40g2000prh.googlegroups.com...
> > I've got 99% fragmentation on a clustered index of a table with 29699
> > rows (there have been just 29699 inserts, no updates, no deletes).
> > There are 8 columns on the table with an int and a char(32) being the
> > unique clustered index - in that order - and I'm pretty sure that the
> > rows are inserted in int,char(32) order. Average row size is 852,
> > there are 3769 pages with average fullness being 83%.
> > The database file was created as 3.5GB and has 51% space free. The
> > data file itself is only fragmented into 3 pieces according to Disk
> > Fragmenter.
> > I don't really understand why it has fragmented so much. All index
> > rows should be appended because they are inserted in key order, there
> > is plenty of space in the file - would it have been better to have
> > made the file smaller and done AutoGrow by 1MB'
> > The table also has a non-clustered index, which is hardly fragmented
> > at all.
> > The insert transaction consisted of many inserts on many different
> > tables, all with similar indexes, all inserted in key value order.
> > There are maybe 100 tables - inserts take place across the 100 tables,
> > then another group of inserts take place against all the tables. But
> > for any specific table any insert will be in key value order and
> > should therefore be appended (which seems to be supported by the page
> > fullness being 83%). But how can I avoid the logical fragmentation? I
> > know I can use DBCC DBREINDEX but this is happening so quickly in the
> > system that it is not reasonable to have to do it this soon (and it's
> > actually happening three quarters of the way through a data load).
> > There would have been hundreds of thousands of records inserted during
> > the transaction.
> > Thanks for any help.

Index fragmentation

I've got 99% fragmentation on a clustered index of a table with 29699
rows (there have been just 29699 inserts, no updates, no deletes).
There are 8 columns on the table with an int and a char(32) being the
unique clustered index - in that order - and I'm pretty sure that the
rows are inserted in int,char(32) order. Average row size is 852,
there are 3769 pages with average fullness being 83%.
The database file was created as 3.5GB and has 51% space free. The
data file itself is only fragmented into 3 pieces according to Disk
Fragmenter.
I don't really understand why it has fragmented so much. All index
rows should be appended because they are inserted in key order, there
is plenty of space in the file - would it have been better to have
made the file smaller and done AutoGrow by 1MB'
The table also has a non-clustered index, which is hardly fragmented
at all.
The insert transaction consisted of many inserts on many different
tables, all with similar indexes, all inserted in key value order.
There are maybe 100 tables - inserts take place across the 100 tables,
then another group of inserts take place against all the tables. But
for any specific table any insert will be in key value order and
should therefore be appended (which seems to be supported by the page
fullness being 83%). But how can I avoid the logical fragmentation? I
know I can use DBCC DBREINDEX but this is happening so quickly in the
system that it is not reasonable to have to do it this soon (and it's
actually happening three quarters of the way through a data load).
There would have been hundreds of thousands of records inserted during
the transaction.
Thanks for any help.Hi
"phancey" wrote:
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
>
Could you post DDL and the command(s) you have used to show the
fragmentation with their output?
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
Disc fragmentation may have an effect on performance, but is not really
related to index fragmentation which could have a more critical effect. To
remove disc fragmentation you would need to stop SQL Server or detatch the
database and run the defragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
No, incrementing with smaller amounts increases the risk of disc
fragmentation, and slows down the process of inserting rows as you will have
to wait while the file expands. Planned pre-allocation of the data files is
the best option. Do you have autoshrink on? Have you shrunk the files
manually?
Is the log file on the same drive?
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
You don't mention the value for extent fragmentation! What you could do is
load into a staging table, then do the inserts into each table in one go
rather than on a row per row basis. Could you drop the clustered index during
the upload?
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
What fragmentation value do you get after the DBREINDEX?
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
>
John|||Hi
I have just seen the other post, and seen that you are using SQL 2005, look
at the function sys.dm_db_index_physical_stats, information can be obtained
from books online. You may also want to read about index fragmentation in
Inside Microsoft SQL Server 2005 :The Storage Engine by Kalen Delaney ISBN
0735621055
John|||Doh! The application (which I have no control over), despite asking
them to insert in int,char(32) asc order, was actually inserting in
int,char(32) desc order. So that would probably explain it.
Thanks anyway
On 27 Apr, 08:36, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> I have just seen the other post, and seen that you are using SQL 2005, look
> at the function sys.dm_db_index_physical_stats, information can be obtained
> from books online. You may also want to read aboutindexfragmentationin
> Inside Microsoft SQL Server 2005 :The Storage Engine by Kalen Delaney ISBN
> 0735621055
> John|||Hi
"phancey" wrote:
> Doh! The application (which I have no control over), despite asking
> them to insert in int,char(32) asc order, was actually inserting in
> int,char(32) desc order. So that would probably explain it.
> Thanks anyway
>
In which case dropping the index may be your best option.
John|||Better may be to order the index columns in desc order instead. See BOL for
the CREATE INDEX statement.
--
TheSQLGuru
President
Indicium Resources, Inc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0EFD52C4-245E-4551-BD6E-87E9924C29B4@.microsoft.com...
> Hi
> "phancey" wrote:
>> Doh! The application (which I have no control over), despite asking
>> them to insert in int,char(32) asc order, was actually inserting in
>> int,char(32) desc order. So that would probably explain it.
>> Thanks anyway
> In which case dropping the index may be your best option.
> John|||On 27 Apr 2007 05:34:09 -0700, phancey <deja@.2bytes.co.uk> wrote:
>Doh! The application (which I have no control over), despite asking
>them to insert in int,char(32) asc order, was actually inserting in
>int,char(32) desc order. So that would probably explain it.
>Thanks anyway
:)
J.

Index fragmentation

I've got 99% fragmentation on a clustered index of a table with 29699
rows (there have been just 29699 inserts, no updates, no deletes).
There are 8 columns on the table with an int and a char(32) being the
unique clustered index - in that order - and I'm pretty sure that the
rows are inserted in int,char(32) order. Average row size is 852,
there are 3769 pages with average fullness being 83%.
The database file was created as 3.5GB and has 51% space free. The
data file itself is only fragmented into 3 pieces according to Disk
Fragmenter.
I don't really understand why it has fragmented so much. All index
rows should be appended because they are inserted in key order, there
is plenty of space in the file - would it have been better to have
made the file smaller and done AutoGrow by 1MB'
The table also has a non-clustered index, which is hardly fragmented
at all.
The insert transaction consisted of many inserts on many different
tables, all with similar indexes, all inserted in key value order.
There are maybe 100 tables - inserts take place across the 100 tables,
then another group of inserts take place against all the tables. But
for any specific table any insert will be in key value order and
should therefore be appended (which seems to be supported by the page
fullness being 83%). But how can I avoid the logical fragmentation? I
know I can use DBCC DBREINDEX but this is happening so quickly in the
system that it is not reasonable to have to do it this soon (and it's
actually happening three quarters of the way through a data load).
There would have been hundreds of thousands of records inserted during
the transaction.
Thanks for any help.Hi
"phancey" wrote:

> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
>
Could you post DDL and the command(s) you have used to show the
fragmentation with their output?

> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
Disc fragmentation may have an effect on performance, but is not really
related to index fragmentation which could have a more critical effect. To
remove disc fragmentation you would need to stop SQL Server or detatch the
database and run the defragmenter.

> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
No, incrementing with smaller amounts increases the risk of disc
fragmentation, and slows down the process of inserting rows as you will have
to wait while the file expands. Planned pre-allocation of the data files is
the best option. Do you have autoshrink on? Have you shrunk the files
manually?
Is the log file on the same drive?

> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
You don't mention the value for extent fragmentation! What you could do is
load into a staging table, then do the inserts into each table in one go
rather than on a row per row basis. Could you drop the clustered index durin
g
the upload?

> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
What fragmentation value do you get after the DBREINDEX?

> There would have been hundreds of thousands of records inserted during
> the transaction.

> Thanks for any help.
>
John|||Hi
I have just seen the other post, and seen that you are using SQL 2005, look
at the function sys.dm_db_index_physical_stats, information can be obtained
from books online. You may also want to read about index fragmentation in
Inside Microsoft SQL Server 2005 :The Storage Engine by Kalen Delaney ISBN
0735621055
John|||Doh! The application (which I have no control over), despite asking
them to insert in int,char(32) asc order, was actually inserting in
int,char(32) desc order. So that would probably explain it.
Thanks anyway
On 27 Apr, 08:36, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> I have just seen the other post, and seen that you are using SQL 2005, loo
k
> at the function sys.dm_db_index_physical_stats, information can be obtaine
d
> from books online. You may also want to read aboutindexfragmentationin
> Inside Microsoft SQL Server 2005 :The Storage Engine by Kalen Delaney ISBN
> 0735621055
> John|||Hi
"phancey" wrote:

> Doh! The application (which I have no control over), despite asking
> them to insert in int,char(32) asc order, was actually inserting in
> int,char(32) desc order. So that would probably explain it.
> Thanks anyway
>
In which case dropping the index may be your best option.
John|||Better may be to order the index columns in desc order instead. See BOL for
the CREATE INDEX statement.
TheSQLGuru
President
Indicium Resources, Inc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0EFD52C4-245E-4551-BD6E-87E9924C29B4@.microsoft.com...
> Hi
> "phancey" wrote:
>
> In which case dropping the index may be your best option.
> John|||On 27 Apr 2007 05:34:09 -0700, phancey <deja@.2bytes.co.uk> wrote:

>Doh! The application (which I have no control over), despite asking
>them to insert in int,char(32) asc order, was actually inserting in
>int,char(32) desc order. So that would probably explain it.
>Thanks anyway

J.

Index fragmentation

I've got 99% fragmentation on a clustered index of a table with 29699
rows (there have been just 29699 inserts, no updates, no deletes).
There are 8 columns on the table with an int and a char(32) being the
unique clustered index - in that order - and I'm pretty sure that the
rows are inserted in int,char(32) order. Average row size is 852,
there are 3769 pages with average fullness being 83%.
The database file was created as 3.5GB and has 51% space free. The
data file itself is only fragmented into 3 pieces according to Disk
Fragmenter.
I don't really understand why it has fragmented so much. All index
rows should be appended because they are inserted in key order, there
is plenty of space in the file - would it have been better to have
made the file smaller and done AutoGrow by 1MB'
The table also has a non-clustered index, which is hardly fragmented
at all.
The insert transaction consisted of many inserts on many different
tables, all with similar indexes, all inserted in key value order.
There are maybe 100 tables - inserts take place across the 100 tables,
then another group of inserts take place against all the tables. But
for any specific table any insert will be in key value order and
should therefore be appended (which seems to be supported by the page
fullness being 83%). But how can I avoid the logical fragmentation? I
know I can use DBCC DBREINDEX but this is happening so quickly in the
system that it is not reasonable to have to do it this soon (and it's
actually happening three quarters of the way through a data load).
There would have been hundreds of thousands of records inserted during
the transaction.
Thanks for any help.On Apr 26, 10:05 pm, phancey <d...@.2bytes.co.uk> wrote:
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
actually the database file was created without a size, then changed to
500MB, then changed again to 3.5GB, all before the majority of the
data was written to the tables (and certainly before any data was
written to this particular table).|||Can you give the exact statement you are executing to get this information,
and the exact, complete output you receive? Perhaps (hopefully) you are
misinterpreting the data.
TheSQLGuru
President
Indicium Resources, Inc.
"phancey" <deja@.2bytes.co.uk> wrote in message
news:1177621507.322821.214250@.o40g2000prh.googlegroups.com...
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
>|||Using SQL 2005, I view the properties of the index, then
"Fragmentation".
Also, my data load (which does a check on existing records before
inserting) grinds to a halt presumably because of this query. I have
managed to run the data load a few times without any problem but
unfortunately did not make enough notes on the exact setup because I
did not think there was going to be a problem (I'm pretty sure I did
check fragmentation of the indexes though and am pretty sure that
fragmentation was less than 10%). Now I cannot recreate that fast load
and am at my wits end to figure out what is different.
The database file sizes again are got from the properties page of the
database in Management Studio.
thanks
On Apr 26, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Can you give the exact statement you are executing to get this information
,
> and the exact, complete output you receive? Perhaps (hopefully) you are
> misinterpreting the data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "phancey" <d...@.2bytes.co.uk> wrote in message
> news:1177621507.322821.214250@.o40g2000prh.googlegroups.com...
>
>
>
>
>
>
>