page splits/ fragmentation

Hi,
I am inserting data into a table in what I believe is the correct way. The
table is clustered on the date and I am inserting data that 90% of the time
comes in the correct order, ie today I am inserting 2006/04/27 but 10% may b
e
from 2005 or 2004. I think I need a clustered index on the date cause I am
comparing series of dates together.
From the below...
DBCC SHOWCONTIG scanning 'staging_daily_edf_history' table...
Table: 'staging_daily_edf_history' (199671759); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 96488
- Extents Scanned.......................: 12157
- Extent Switches.......................: 12707
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 94.91% [12061:12708]
- Logical Scan Fragmentation ..............: 1.87%
- Extent Scan Fragmentation ...............: 1.49%
- Avg. Bytes Free per Page................: 134.5
- Avg. Page Density (full)................: 98.34%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I believe that my table is not really fragmented. Does anyone else think the
opposite. It worries me though that the insertion become slower and slower a
s
the table icreases in size maybe because of page splits. Any ideas what's th
e
best strategy? should I have my table non clustered instead? Thanks.
Panos.The results show that it is not very fragmented at all and I doubt that page
splits on the clustered index are the issue here. You can also try setting
your fill factor to 90% to remove most of any page splits you may get. How
many non-clustered indexes on this table do you have? Can you give more
details on how you are inserting and how you determine they get slower?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:18D591DA-BCA2-44D8-984A-DE89A4CD5374@.microsoft.com...
> Hi,
> I am inserting data into a table in what I believe is the correct way. The
> table is clustered on the date and I am inserting data that 90% of the
> time
> comes in the correct order, ie today I am inserting 2006/04/27 but 10% may
> be
> from 2005 or 2004. I think I need a clustered index on the date cause I am
> comparing series of dates together.
> From the below...
> DBCC SHOWCONTIG scanning 'staging_daily_edf_history' table...
> Table: 'staging_daily_edf_history' (199671759); index ID: 1, database ID:
> 5
> TABLE level scan performed.
> - Pages Scanned........................: 96488
> - Extents Scanned.......................: 12157
> - Extent Switches.......................: 12707
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 94.91% [12061:12708]
> - Logical Scan Fragmentation ..............: 1.87%
> - Extent Scan Fragmentation ...............: 1.49%
> - Avg. Bytes Free per Page................: 134.5
> - Avg. Page Density (full)................: 98.34%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I believe that my table is not really fragmented. Does anyone else think
> the
> opposite. It worries me though that the insertion become slower and slower
> as
> the table icreases in size maybe because of page splits. Any ideas what's
> the
> best strategy? should I have my table non clustered instead? Thanks.
> Panos.|||Hi,
Well there are 2 more indexes. I guess that must be the problem updating the
non clustered indexes. The way I can see it's slow is because I have a recor
d
on the time the file was processed (insertion via iiss) and can see that it
just takes longer and longer. I guess this is what I should expect when I
have 8m rows and 3 indexes.
Cheers,
Panos.
"Andrew J. Kelly" wrote:

> The results show that it is not very fragmented at all and I doubt that pa
ge
> splits on the clustered index are the issue here. You can also try settin
g
> your fill factor to 90% to remove most of any page splits you may get. Ho
w
> many non-clustered indexes on this table do you have? Can you give more
> details on how you are inserting and how you determine they get slower?
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:18D591DA-BCA2-44D8-984A-DE89A4CD5374@.microsoft.com...
>
>|||8M rows and 3 indexes should not slow down the inserts to a degree that
should be easily detectable. Maybe it is the way in which you are doing the
inserts. Can you post the actual code used for the inserts?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:1BA642CC-5545-4B7F-B44A-056CEEB17004@.microsoft.com...
> Hi,
> Well there are 2 more indexes. I guess that must be the problem updating
> the
> non clustered indexes. The way I can see it's slow is because I have a
> record
> on the time the file was processed (insertion via iiss) and can see that
> it
> just takes longer and longer. I guess this is what I should expect when I
> have 8m rows and 3 indexes.
> Cheers,
> Panos.
>
> "Andrew J. Kelly" wrote:
>|||it is a straight forward insert into table, no conditions or anything, can't
be simpler. It doesn't slow down too much but you can still notice. Well the
actual stats is avg insertion 10secs for the whole process at the beginning
and then it goes to avg 26 secs in the end.
Thanks.
"Andrew J. Kelly" wrote:

> 8M rows and 3 indexes should not slow down the inserts to a degree that
> should be easily detectable. Maybe it is the way in which you are doing th
e
> inserts. Can you post the actual code used for the inserts?
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:1BA642CC-5545-4B7F-B44A-056CEEB17004@.microsoft.com...
>
>|||10 Seconds for an insert? So this isn't a simple insert then, there must be
more to it. Something you might not think is important may in fact be so
the more details you provide the better chance you will get a proper answer.
Are you sure there are no triggers on the table?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:4F99553F-EEEA-4D19-88E0-CC34B121176B@.microsoft.com...
> it is a straight forward insert into table, no conditions or anything,
> can't
> be simpler. It doesn't slow down too much but you can still notice. Well
> the
> actual stats is avg insertion 10secs for the whole process at the
> beginning
> and then it goes to avg 26 secs in the end.
> Thanks.
> "Andrew J. Kelly" wrote:
>|||I am talking about 20-30,000 rows insertions. Although now it seems to take
longer, more than 2 min. I think it's probably fine. There are no triggers b
y
the way on the table. You think that few odd rows which don't come in order
will not cause a massive problem. Ill also change the fill factor and see if
that's better. Appreciate your help.
Panos.
"Andrew J. Kelly" wrote:

> 10 Seconds for an insert? So this isn't a simple insert then, there must
be
> more to it. Something you might not think is important may in fact be so
> the more details you provide the better chance you will get a proper answe
r.
> Are you sure there are no triggers on the table?
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:4F99553F-EEEA-4D19-88E0-CC34B121176B@.microsoft.com...
>
>|||You still are not providing any real details as to exactly how you do these
inserts and that makes it very hard to give any solid advice. In my opinion
that is still too long for just 20 or 30K rows. Have you checked for
blocking? What about your disk queues? Is your log file on a separate Raid
1 from any of the data files?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:CE4272A5-1F5B-4CB4-89A0-38CA5BDCC3F0@.microsoft.com...
>I am talking about 20-30,000 rows insertions. Although now it seems to take
> longer, more than 2 min. I think it's probably fine. There are no triggers
> by
> the way on the table. You think that few odd rows which don't come in
> order
> will not cause a massive problem. Ill also change the fill factor and see
> if
> that's better. Appreciate your help.
> Panos.
> "Andrew J. Kelly" wrote:
>