Clustered Index -minimize the cost of Page Splitting

Hi guys,
I hope this is the last post from me for a while. Your help in the last 2
ws has been appreciated. We have run into problems with a production
system that was deployed at a site with 4 times the data found at our other
sites and have just 2 more ws to optimize.
We have found that using clustered primary keys on Order and Invoice tables
has decreased the Read time dramatically. The system is highly transactional
and now I am concerned about inserts.
Each site has multiple internal Divisions. We have included the records for
all Divisions (by table type) in a single table, rather than creating
separate tables for each Division.. This results in a PK of (for example)
DIVID + INVNUM.
I am now expecting that we will be dealing with many page splits during peak
transactional periods because of the clustered index. In lieu of creating
separate Invoice tables for each Division (a maintenance nightmare) how can
we minimize the impact of page splits since new records will be inserted at
the end of each "division group" within the table.
I have set up a nightly maintenance plan that reorganizes Data and Index
pages. I currently have it set to allow 10% free space. It also shrinks the
database allowing 10% free space. Should the free space be increased to
handle a daily load of about 40 new invoices per Division and 10 Divisions?
Each record is about 3k in size with no variable size fields. Am I being
overly concerned about the performance effects of inserts with the Clustered
index?
Please take into consideration that this sytem is deployed at 30 smaller
sites already and there is a huge amount of code written to read and write
on the basis of DIVID + INVNUM. There are about 12 other tables that hold
accounting related info that get updated simultaneously with every write. Of
course we will consider a complete re-write (if necessary) for future
releases.
Any suggestions would be appreciated. Thanks to all.....Hi John
Your solution sounds like you may benefit from a partitioned view (see books
online for more).
John
"John Kotuby" wrote:

> Hi guys,
> I hope this is the last post from me for a while. Your help in the last 2
> ws has been appreciated. We have run into problems with a production
> system that was deployed at a site with 4 times the data found at our othe
r
> sites and have just 2 more ws to optimize.
> We have found that using clustered primary keys on Order and Invoice table
s
> has decreased the Read time dramatically. The system is highly transaction
al
> and now I am concerned about inserts.
> Each site has multiple internal Divisions. We have included the records fo
r
> all Divisions (by table type) in a single table, rather than creating
> separate tables for each Division.. This results in a PK of (for example)
> DIVID + INVNUM.
> I am now expecting that we will be dealing with many page splits during pe
ak
> transactional periods because of the clustered index. In lieu of creating
> separate Invoice tables for each Division (a maintenance nightmare) how ca
n
> we minimize the impact of page splits since new records will be inserted a
t
> the end of each "division group" within the table.
> I have set up a nightly maintenance plan that reorganizes Data and Index
> pages. I currently have it set to allow 10% free space. It also shrinks th
e
> database allowing 10% free space. Should the free space be increased to
> handle a daily load of about 40 new invoices per Division and 10 Divisions
?
> Each record is about 3k in size with no variable size fields. Am I being
> overly concerned about the performance effects of inserts with the Cluster
ed
> index?
> Please take into consideration that this sytem is deployed at 30 smaller
> sites already and there is a huge amount of code written to read and write
> on the basis of DIVID + INVNUM. There are about 12 other tables that hold
> accounting related info that get updated simultaneously with every write.
Of
> course we will consider a complete re-write (if necessary) for future
> releases.
> Any suggestions would be appreciated. Thanks to all.....
>
>|||Thanks John,
I will check it out
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FB376DEF-6846-43D5-B437-9FC0FAFE5F46@.microsoft.com...
> Hi John
> Your solution sounds like you may benefit from a partitioned view (see
> books
> online for more).
> John
> "John Kotuby" wrote:
>