Mapping issue

Part 0f the Schema
<xs:element name="Advice" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="OriginatingAccountRecords" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="OriginatingAccountRecord" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="OriginatingAccount"
sql:relation="XML_OriginatingAccount">
<xs:complexType>
<xs:attribute name="number" sql:field="number" sql:datatype="varchar"/>
<xs:attribute name="sortCode" sql:field="sortcode"
sql:datatype="varchar"/>
<xs:attribute name="bankName" sql:field="bankname"
sql:datatype="varchar"/>
<xs:attribute name="branchName" sql:field="branchname"
sql:datatype="varchar"/>
</xs:complexType>
</xs:element>
<xs:element name="ReturnedDebitItem" sql:relation="XML_ReturnedDebitItem">
<xs:complexType>
<xs:attribute name="ref" sql:field="ref" sql:datatype="varchar"/>
<xs:attribute name="transCode" sql:field="transcode"
sql:datatype="varchar"/>
<xs:attribute name="returnCode" sql:field="returncode"
sql:datatype="varchar"/>
<xs:attribute name="returnDescription" sql:field="returndescription"
sql:datatype="varchar"/>
<xs:attribute name="originalProcessingDate"
sql:field="originalprocessingdate" sql:datatype="varchar"/>
<xs:attribute name="valueOf" sql:field="valueof" sql:datatype="varchar"/>
<xs:attribute name="currency" sql:field="currency"
sql:datatype="varchar"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
XML File
<?xml version="1.0" encoding="ISO-8859-1"?>
<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
<Data>
<ARUDD>
<Header reportType="REFT" adviceNumber="00004"
currentProcessingDate="1999-12-30"/>
<AddresseeInformation name="A NAMES" address1="ADDRESS LINE 1"
address2="ADDRESS LINE 2" address3="ADDRESS LINE 3" address4="ADDRESS LINE 4"
address5="ADDRESS LINE 5"/>
<ServiceLicenseInformation userName="A USER" userNumber="999999"/>
<Advice>
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount number="99999999" sortCode="99-99-99" bankName="A
BANK" branchName="TOWN CENTRE"/>
<ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
<PayerAccount name="SB04012027-10.0078" sortCode="11-11-11"
number="11111111"/>
</ReturnedDebitItem>
<ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
<PayerAccount name="SB04012027-01.0078" sortCode="22-22-22"
number="22222222"/>
</ReturnedDebitItem>
<ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
<PayerAccount name="SB04072017-01.0078" sortCode="33-33-33"
number="33333333"/>
</ReturnedDebitItem>
<ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
<PayerAccount name="SB04012027-06.0078" sortCode="44-44-44"
number="44444444"/>
</ReturnedDebitItem>
<ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
<PayerAccount name="SB04012028-04.0078" sortCode="55-55-55"
number="55555555"/>
</ReturnedDebitItem>
<ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
<PayerAccount name="SB04204005-01.0078" sortCode="66-66-66"
number="66666666"/>
</ReturnedDebitItem>
<ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
<PayerAccount name="SB04519015-01.0078" sortCode="77-77-77"
number="77777777"/>
</ReturnedDebitItem>
<Totals numberOf="7" currency="GBP" valueOf="1618.75"/>
</OriginatingAccountRecord>
</OriginatingAccountRecords>
</Advice>
</ARUDD>
</Data>
<SignatureMethod/>
<Signature/>
</BACSDocument>
The problem im hving id trying to insert the <PayerAccount> details to a
different table then the <ReturnedDebitItems>
Your schema should define the <PayerAccount> element as a child of the
<ReturnedDebitItems>.
looks like now it is not defined.
and then add a sql:relation to the different table where <PayerAccount>
should go.
In order to define the <PayerAccount> as a child of <returnedDebitItems> you
need to define a sql:relationship element between the two.
HTH
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:E9D5CF0F-23F4-4E6A-B687-5D4DDF768464@.microsoft.com...
> Part 0f the Schema
> <xs:element name="Advice" sql:is-constant="true">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="OriginatingAccountRecords" sql:is-constant="true">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="OriginatingAccountRecord" sql:is-constant="true">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="OriginatingAccount"
> sql:relation="XML_OriginatingAccount">
> <xs:complexType>
> <xs:attribute name="number" sql:field="number" sql:datatype="varchar"/>
> <xs:attribute name="sortCode" sql:field="sortcode"
> sql:datatype="varchar"/>
> <xs:attribute name="bankName" sql:field="bankname"
> sql:datatype="varchar"/>
> <xs:attribute name="branchName" sql:field="branchname"
> sql:datatype="varchar"/>
> </xs:complexType>
> </xs:element>
> <xs:element name="ReturnedDebitItem" sql:relation="XML_ReturnedDebitItem">
> <xs:complexType>
> <xs:attribute name="ref" sql:field="ref" sql:datatype="varchar"/>
> <xs:attribute name="transCode" sql:field="transcode"
> sql:datatype="varchar"/>
> <xs:attribute name="returnCode" sql:field="returncode"
> sql:datatype="varchar"/>
> <xs:attribute name="returnDescription" sql:field="returndescription"
> sql:datatype="varchar"/>
> <xs:attribute name="originalProcessingDate"
> sql:field="originalprocessingdate" sql:datatype="varchar"/>
> <xs:attribute name="valueOf" sql:field="valueof"
> sql:datatype="varchar"/>
> <xs:attribute name="currency" sql:field="currency"
> sql:datatype="varchar"/>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> XML File
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
> <Data>
> <ARUDD>
> <Header reportType="REFT" adviceNumber="00004"
> currentProcessingDate="1999-12-30"/>
> <AddresseeInformation name="A NAMES" address1="ADDRESS LINE 1"
> address2="ADDRESS LINE 2" address3="ADDRESS LINE 3" address4="ADDRESS LINE
> 4"
> address5="ADDRESS LINE 5"/>
> <ServiceLicenseInformation userName="A USER" userNumber="999999"/>
> <Advice>
> <OriginatingAccountRecords>
> <OriginatingAccountRecord>
> <OriginatingAccount number="99999999" sortCode="99-99-99" bankName="A
> BANK" branchName="TOWN CENTRE"/>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04012027-10.0078" sortCode="11-11-11"
> number="11111111"/>
> </ReturnedDebitItem>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04012027-01.0078" sortCode="22-22-22"
> number="22222222"/>
> </ReturnedDebitItem>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04072017-01.0078" sortCode="33-33-33"
> number="33333333"/>
> </ReturnedDebitItem>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04012027-06.0078" sortCode="44-44-44"
> number="44444444"/>
> </ReturnedDebitItem>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04012028-04.0078" sortCode="55-55-55"
> number="55555555"/>
> </ReturnedDebitItem>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04204005-01.0078" sortCode="66-66-66"
> number="66666666"/>
> </ReturnedDebitItem>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04519015-01.0078" sortCode="77-77-77"
> number="77777777"/>
> </ReturnedDebitItem>
> <Totals numberOf="7" currency="GBP" valueOf="1618.75"/>
> </OriginatingAccountRecord>
> </OriginatingAccountRecords>
> </Advice>
> </ARUDD>
> </Data>
> <SignatureMethod/>
> <Signature/>
> </BACSDocument>
> The problem im hving id trying to insert the <PayerAccount> details to a
> different table then the <ReturnedDebitItems>
|||Your schema does not reflect your document. You should add to your schema the
<PayerAccount> element and then map it to a different table using
sql:relation and sql:relationship annotation
Regards,
Avner
"Peter Newman" wrote:

> Part 0f the Schema
> <xs:element name="Advice" sql:is-constant="true">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="OriginatingAccountRecords" sql:is-constant="true">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="OriginatingAccountRecord" sql:is-constant="true">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="OriginatingAccount"
> sql:relation="XML_OriginatingAccount">
> <xs:complexType>
> <xs:attribute name="number" sql:field="number" sql:datatype="varchar"/>
> <xs:attribute name="sortCode" sql:field="sortcode"
> sql:datatype="varchar"/>
> <xs:attribute name="bankName" sql:field="bankname"
> sql:datatype="varchar"/>
> <xs:attribute name="branchName" sql:field="branchname"
> sql:datatype="varchar"/>
> </xs:complexType>
> </xs:element>
> <xs:element name="ReturnedDebitItem" sql:relation="XML_ReturnedDebitItem">
> <xs:complexType>
> <xs:attribute name="ref" sql:field="ref" sql:datatype="varchar"/>
> <xs:attribute name="transCode" sql:field="transcode"
> sql:datatype="varchar"/>
> <xs:attribute name="returnCode" sql:field="returncode"
> sql:datatype="varchar"/>
> <xs:attribute name="returnDescription" sql:field="returndescription"
> sql:datatype="varchar"/>
> <xs:attribute name="originalProcessingDate"
> sql:field="originalprocessingdate" sql:datatype="varchar"/>
> <xs:attribute name="valueOf" sql:field="valueof" sql:datatype="varchar"/>
> <xs:attribute name="currency" sql:field="currency"
> sql:datatype="varchar"/>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> XML File
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
> <Data>
> <ARUDD>
> <Header reportType="REFT" adviceNumber="00004"
> currentProcessingDate="1999-12-30"/>
> <AddresseeInformation name="A NAMES" address1="ADDRESS LINE 1"
> address2="ADDRESS LINE 2" address3="ADDRESS LINE 3" address4="ADDRESS LINE 4"
> address5="ADDRESS LINE 5"/>
> <ServiceLicenseInformation userName="A USER" userNumber="999999"/>
> <Advice>
> <OriginatingAccountRecords>
> <OriginatingAccountRecord>
> <OriginatingAccount number="99999999" sortCode="99-99-99" bankName="A
> BANK" branchName="TOWN CENTRE"/>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04012027-10.0078" sortCode="11-11-11"
> number="11111111"/>
> </ReturnedDebitItem>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04012027-01.0078" sortCode="22-22-22"
> number="22222222"/>
> </ReturnedDebitItem>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04072017-01.0078" sortCode="33-33-33"
> number="33333333"/>
> </ReturnedDebitItem>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04012027-06.0078" sortCode="44-44-44"
> number="44444444"/>
> </ReturnedDebitItem>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04012028-04.0078" sortCode="55-55-55"
> number="55555555"/>
> </ReturnedDebitItem>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04204005-01.0078" sortCode="66-66-66"
> number="66666666"/>
> </ReturnedDebitItem>
> <ReturnedDebitItem ref="REFERENCE DDI CR3739" transCode="19"
> returnCode="8362" returnDescription="AMOUNT NOT YET DUE"
> originalProcessingDate="1999-12-28" valueOf="231.25" currency="GBP">
> <PayerAccount name="SB04519015-01.0078" sortCode="77-77-77"
> number="77777777"/>
> </ReturnedDebitItem>
> <Totals numberOf="7" currency="GBP" valueOf="1618.75"/>
> </OriginatingAccountRecord>
> </OriginatingAccountRecords>
> </Advice>
> </ARUDD>
> </Data>
> <SignatureMethod/>
> <Signature/>
> </BACSDocument>
> The problem im hving id trying to insert the <PayerAccount> details to a
> different table then the <ReturnedDebitItems>

Managing Indexes on large tables

Hi I wonder if anyone could give some advice around managing a non clustered
Index on a large table:
rows reserved data
index_size unused
229324002 27215584 KB 20200600 KB 6940968 KB 74016
KB
We keep getting problems with this index as it is on a large & busy table -
Ideally I'd rebuild the index every day but it is a very busy production
system & that just isn't practical.
Would Index defragmentation help with Invalid key/corruption issues & the
general health of this index? or has anyone any advice on mangement
strategies for big tables/indexes?
Many Thanks for any input on this!
Mike Knee
Attenda Monitoring & Management
Defragmentation is done for performance purposes. I don't know what you mean by "invalid key", but
if you have corruption issues, you need to get to the root cause of this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sinister China Penguin" <SinisterChinaPenguin@.discussions.microsoft.com> wrote in message
news:6195F440-E5C7-44E7-B162-70E5AA99A8CE@.microsoft.com...
> Hi I wonder if anyone could give some advice around managing a non clustered
> Index on a large table:
> rows reserved data
> index_size unused
> 229324002 27215584 KB 20200600 KB 6940968 KB 74016
> KB
> We keep getting problems with this index as it is on a large & busy table -
> Ideally I'd rebuild the index every day but it is a very busy production
> system & that just isn't practical.
> Would Index defragmentation help with Invalid key/corruption issues & the
> general health of this index? or has anyone any advice on mangement
> strategies for big tables/indexes?
> Many Thanks for any input on this!
> --
> Mike Knee
> Attenda Monitoring & Management
|||If you are getting errors sucha s that you most likely have corruption in
the index. Since it is a nonclustered index I suggest you drop and recreate
it or run DBCC DBREINDEX on just that index to be sure you get a clean
index. It might be busy but you need to fix it. It's unlikely that you need
to rebuild it every night. If you do then you may want to adjust your fill
factor to avoid the pagesplits.
Andrew J. Kelly SQL MVP
"Sinister China Penguin" <SinisterChinaPenguin@.discussions.microsoft.com>
wrote in message news:6195F440-E5C7-44E7-B162-70E5AA99A8CE@.microsoft.com...
> Hi I wonder if anyone could give some advice around managing a non
> clustered
> Index on a large table:
> rows reserved data
> index_size unused
> 229324002 27215584 KB 20200600 KB 6940968 KB
> 74016
> KB
> We keep getting problems with this index as it is on a large & busy
> table -
> Ideally I'd rebuild the index every day but it is a very busy production
> system & that just isn't practical.
> Would Index defragmentation help with Invalid key/corruption issues & the
> general health of this index? or has anyone any advice on mangement
> strategies for big tables/indexes?
> Many Thanks for any input on this!
> --
> Mike Knee
> Attenda Monitoring & Management
|||On Fri, 10 Feb 2006 11:03:29 -0800, "Sinister China Penguin"
<SinisterChinaPenguin@.discussions.microsoft.com> wrote:

>Hi I wonder if anyone could give some advice around managing a non clustered
>Index on a large table:
> rows reserved data index_size unused
> 229324002 27215584 KB 20200600 KB 6940968 KB 74016 KB
Well, that's reasonably large, alrighty!

>We keep getting problems with this index as it is on a large & busy table -
>Ideally I'd rebuild the index every day but it is a very busy production
>system & that just isn't practical.
>Would Index defragmentation help with Invalid key/corruption issues & the
>general health of this index? or has anyone any advice on mangement
>strategies for big tables/indexes?
That is a lot of rows on one table. Have you looked at partitioned
views (SQL2000) or partitioned tables (SQL2005)?
Only situation I've had with that many rows was with them split into
ten tables and joined with a partitioned view, had no problems with
that.
Can you say what is going on with that table when the problems occur,
I presume not all select's?
Also, what the PK is, and what the clustered key is, if different?
For that matter, what is the key in this index - single field int, or
multiple varchars, or what?
J.
|||Thanks for the replies - some more details: (System is SQL2000 clustered on
Win2K)
- The table collects data points for I.T. systems performance (perfmons etc)
on 800 odd servers so there are hundreds of Inserts going on 24/7 & no
updates.
- Once a day a maintenance job runs to delete any data points > 200 days old
so the size of the table stays roughly the same...
- This table has just the one Non-Clustered Index on DataID(int - non
unique) , Time(Int) & Value (float)
- The DataID is non unique as it id defined in a "Header" table which
contains all the definitions for the performance data being collected (name
of perfmon, system name etc), the V large table I originally posted about
holds the actual performance data so there are thousands of entries for each
dataID.
- The Table is part of a commercial product (NetIQ's AppManager) so I have
no control over the schema/Indexes etc.
I'm not sure when the problems occur - I'm gussing during a flurry of Inserts?
I hope this all makes sense - I can't help but think this table could be
managed better - should I force a Full table lock for example when the
deletes are taking place? or can I manage the Index better (hence the
questions about defragging/rebuilding)
Thanks again for any advice or ideas.
Mike Knee
Attenda Monitoring & Management
"JXStern" wrote:

> On Fri, 10 Feb 2006 11:03:29 -0800, "Sinister China Penguin"
> <SinisterChinaPenguin@.discussions.microsoft.com> wrote:
>
> Well, that's reasonably large, alrighty!
>
> That is a lot of rows on one table. Have you looked at partitioned
> views (SQL2000) or partitioned tables (SQL2005)?
> Only situation I've had with that many rows was with them split into
> ten tables and joined with a partitioned view, had no problems with
> that.
> Can you say what is going on with that table when the problems occur,
> I presume not all select's?
> Also, what the PK is, and what the clustered key is, if different?
> For that matter, what is the key in this index - single field int, or
> multiple varchars, or what?
> J.
>
|||Sinister China Penguin wrote:
> Thanks for the replies - some more details: (System is SQL2000
> clustered on Win2K)
> - The table collects data points for I.T. systems performance
> (perfmons etc) on 800 odd servers so there are hundreds of Inserts
> going on 24/7 & no updates.
> - Once a day a maintenance job runs to delete any data points > 200
> days old so the size of the table stays roughly the same...
> - This table has just the one Non-Clustered Index on DataID(int - non
> unique) , Time(Int) & Value (float)
> - The DataID is non unique as it id defined in a "Header" table which
> contains all the definitions for the performance data being collected
> (name of perfmon, system name etc), the V large table I originally
> posted about holds the actual performance data so there are thousands
> of entries for each dataID.
> - The Table is part of a commercial product (NetIQ's AppManager) so I
> have
> no control over the schema/Indexes etc.
> I'm not sure when the problems occur - I'm gussing during a flurry of
> Inserts?
> I hope this all makes sense - I can't help but think this table could
> be managed better - should I force a Full table lock for example when
> the deletes are taking place? or can I manage the Index better (hence
> the questions about defragging/rebuilding)
> Thanks again for any advice or ideas.
It sounds as if having a clustered index on the timestamp could be a good
idea.I'm guessing that you query and delete data based on timestamp so
this index probably would help both. But mind you it'll take considerable
time and space to create it - it's likely that it'll be even too resource
intensive in your case.
I still do not understand what problems you have. Did you get any error
messages about a bad index or IO errors? If so then something seems to be
seriously wrong with your db. Are queries slow? Inserts?
Regards
robert
|||Really I'm just afterany general advice around looking after this big, busy
table - the Integrity checks do seem to come up with Index problems quite
often & I wanted to make sure I was doing all the right things to keep it
working well.
For Example I don't really fully understand fragmentattion of Non Clustered
Indexes should I be defragging often? or is it not worth it? would it help to
defrag/rebuild indexes before or after this big daily delete from the table?
or doesn't it matter?
Sorry to be so vauge - my DBA knowledge is a bit patchy & I just wanna make
sure i'm doing things properly!
Cheers
Mike Knee
Attenda Monitoring & Management
"Robert Klemme" wrote:

> Sinister China Penguin wrote:
> It sounds as if having a clustered index on the timestamp could be a good
> idea.I'm guessing that you query and delete data based on timestamp so
> this index probably would help both. But mind you it'll take considerable
> time and space to create it - it's likely that it'll be even too resource
> intensive in your case.
> I still do not understand what problems you have. Did you get any error
> messages about a bad index or IO errors? If so then something seems to be
> seriously wrong with your db. Are queries slow? Inserts?
> Regards
> robert
>
|||Robert is correct in that the table should have a clustered index on
datetime. It should make the inserts and especially the deletes smoother
and faster. Does the nonclustered index cover all the queries on the table
properly? How are the deletes being done now? Are they in small batches or
one big delete statement each day?
Andrew J. Kelly SQL MVP
"Sinister China Penguin" <SinisterChinaPenguin@.discussions.microsoft.com>
wrote in message news:EE3A13E0-7D0D-4E30-90FF-6CF5AE5E7501@.microsoft.com...[vbcol=seagreen]
> Really I'm just afterany general advice around looking after this big,
> busy
> table - the Integrity checks do seem to come up with Index problems quite
> often & I wanted to make sure I was doing all the right things to keep it
> working well.
> For Example I don't really fully understand fragmentattion of Non
> Clustered
> Indexes should I be defragging often? or is it not worth it? would it help
> to
> defrag/rebuild indexes before or after this big daily delete from the
> table?
> or doesn't it matter?
> Sorry to be so vauge - my DBA knowledge is a bit patchy & I just wanna
> make
> sure i'm doing things properly!
> Cheers
> --
> Mike Knee
> Attenda Monitoring & Management
>
> "Robert Klemme" wrote:

Managing Indexes on large tables

Hi I wonder if anyone could give some advice around managing a non clustered
Index on a large table:
rows reserved data
index_size unused
229324002 27215584 KB 20200600 KB 6940968 KB 74016
KB
We keep getting problems with this index as it is on a large & busy table -
Ideally I'd rebuild the index every day but it is a very busy production
system & that just isn't practical.
Would Index defragmentation help with Invalid key/corruption issues & the
general health of this index? or has anyone any advice on mangement
strategies for big tables/indexes'
Many Thanks for any input on this!
Mike Knee
Attenda Monitoring & ManagementDefragmentation is done for performance purposes. I don't know what you mean
by "invalid key", but
if you have corruption issues, you need to get to the root cause of this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sinister China Penguin" <SinisterChinaPenguin@.discussions.microsoft.com> wr
ote in message
news:6195F440-E5C7-44E7-B162-70E5AA99A8CE@.microsoft.com...
> Hi I wonder if anyone could give some advice around managing a non cluster
ed
> Index on a large table:
> rows reserved data
> index_size unused
> 229324002 27215584 KB 20200600 KB 6940968 KB 740
16
> KB
> We keep getting problems with this index as it is on a large & busy table
-
> Ideally I'd rebuild the index every day but it is a very busy production
> system & that just isn't practical.
> Would Index defragmentation help with Invalid key/corruption issues & the
> general health of this index? or has anyone any advice on mangement
> strategies for big tables/indexes'
> Many Thanks for any input on this!
> --
> Mike Knee
> Attenda Monitoring & Management|||If you are getting errors sucha s that you most likely have corruption in
the index. Since it is a nonclustered index I suggest you drop and recreate
it or run DBCC DBREINDEX on just that index to be sure you get a clean
index. It might be busy but you need to fix it. It's unlikely that you need
to rebuild it every night. If you do then you may want to adjust your fill
factor to avoid the pagesplits.
Andrew J. Kelly SQL MVP
"Sinister China Penguin" <SinisterChinaPenguin@.discussions.microsoft.com>
wrote in message news:6195F440-E5C7-44E7-B162-70E5AA99A8CE@.microsoft.com...
> Hi I wonder if anyone could give some advice around managing a non
> clustered
> Index on a large table:
> rows reserved data
> index_size unused
> 229324002 27215584 KB 20200600 KB 6940968 KB
> 74016
> KB
> We keep getting problems with this index as it is on a large & busy
> table -
> Ideally I'd rebuild the index every day but it is a very busy production
> system & that just isn't practical.
> Would Index defragmentation help with Invalid key/corruption issues & the
> general health of this index? or has anyone any advice on mangement
> strategies for big tables/indexes'
> Many Thanks for any input on this!
> --
> Mike Knee
> Attenda Monitoring & Management|||On Fri, 10 Feb 2006 11:03:29 -0800, "Sinister China Penguin"
<SinisterChinaPenguin@.discussions.microsoft.com> wrote:

>Hi I wonder if anyone could give some advice around managing a non clustere
d
>Index on a large table:
> rows reserved data index_size unu
sed
> 229324002 27215584 KB 20200600 KB 6940968 KB 74016 KB[/v
bcol]
Well, that's reasonably large, alrighty!
[vbcol=seagreen]
>We keep getting problems with this index as it is on a large & busy table -
>Ideally I'd rebuild the index every day but it is a very busy production
>system & that just isn't practical.
>Would Index defragmentation help with Invalid key/corruption issues & the
>general health of this index? or has anyone any advice on mangement
>strategies for big tables/indexes'
That is a lot of rows on one table. Have you looked at partitioned
views (SQL2000) or partitioned tables (SQL2005)?
Only situation I've had with that many rows was with them split into
ten tables and joined with a partitioned view, had no problems with
that.
Can you say what is going on with that table when the problems occur,
I presume not all select's?
Also, what the PK is, and what the clustered key is, if different?
For that matter, what is the key in this index - single field int, or
multiple varchars, or what?
J.|||Thanks for the replies - some more details: (System is SQL2000 clustered on
Win2K)
- The table collects data points for I.T. systems performance (perfmons etc)
on 800 odd servers so there are hundreds of Inserts going on 24/7 & no
updates.
- Once a day a maintenance job runs to delete any data points > 200 days old
so the size of the table stays roughly the same...
- This table has just the one Non-Clustered Index on DataID(int - non
unique) , Time(Int) & Value (float)
- The DataID is non unique as it id defined in a "Header" table which
contains all the definitions for the performance data being collected (name
of perfmon, system name etc), the V large table I originally posted about
holds the actual performance data so there are thousands of entries for each
dataID.
- The Table is part of a commercial product (NetIQ's AppManager) so I have
no control over the schema/Indexes etc.
I'm not sure when the problems occur - I'm gussing during a flurry of Insert
s?
I hope this all makes sense - I can't help but think this table could be
managed better - should I force a Full table lock for example when the
deletes are taking place? or can I manage the Index better (hence the
questions about defragging/rebuilding)
Thanks again for any advice or ideas.
Mike Knee
Attenda Monitoring & Management
"JXStern" wrote:

> On Fri, 10 Feb 2006 11:03:29 -0800, "Sinister China Penguin"
> <SinisterChinaPenguin@.discussions.microsoft.com> wrote:
>
> Well, that's reasonably large, alrighty!
>
> That is a lot of rows on one table. Have you looked at partitioned
> views (SQL2000) or partitioned tables (SQL2005)?
> Only situation I've had with that many rows was with them split into
> ten tables and joined with a partitioned view, had no problems with
> that.
> Can you say what is going on with that table when the problems occur,
> I presume not all select's?
> Also, what the PK is, and what the clustered key is, if different?
> For that matter, what is the key in this index - single field int, or
> multiple varchars, or what?
> J.
>|||Sinister China Penguin wrote:
> Thanks for the replies - some more details: (System is SQL2000
> clustered on Win2K)
> - The table collects data points for I.T. systems performance
> (perfmons etc) on 800 odd servers so there are hundreds of Inserts
> going on 24/7 & no updates.
> - Once a day a maintenance job runs to delete any data points > 200
> days old so the size of the table stays roughly the same...
> - This table has just the one Non-Clustered Index on DataID(int - non
> unique) , Time(Int) & Value (float)
> - The DataID is non unique as it id defined in a "Header" table which
> contains all the definitions for the performance data being collected
> (name of perfmon, system name etc), the V large table I originally
> posted about holds the actual performance data so there are thousands
> of entries for each dataID.
> - The Table is part of a commercial product (NetIQ's AppManager) so I
> have
> no control over the schema/Indexes etc.
> I'm not sure when the problems occur - I'm gussing during a flurry of
> Inserts?
> I hope this all makes sense - I can't help but think this table could
> be managed better - should I force a Full table lock for example when
> the deletes are taking place? or can I manage the Index better (hence
> the questions about defragging/rebuilding)
> Thanks again for any advice or ideas.
It sounds as if having a clustered index on the timestamp could be a good
idea.I'm guessing that you query and delete data based on timestamp so
this index probably would help both. But mind you it'll take considerable
time and space to create it - it's likely that it'll be even too resource
intensive in your case.
I still do not understand what problems you have. Did you get any error
messages about a bad index or IO errors? If so then something seems to be
seriously wrong with your db. Are queries slow? Inserts?
Regards
robert|||Really I'm just afterany general advice around looking after this big, busy
table - the Integrity checks do seem to come up with Index problems quite
often & I wanted to make sure I was doing all the right things to keep it
working well.
For Example I don't really fully understand fragmentattion of Non Clustered
Indexes should I be defragging often? or is it not worth it? would it help t
o
defrag/rebuild indexes before or after this big daily delete from the table?
or doesn't it matter?
Sorry to be so vauge - my DBA knowledge is a bit patchy & I just wanna make
sure i'm doing things properly!
Cheers
Mike Knee
Attenda Monitoring & Management
"Robert Klemme" wrote:

> Sinister China Penguin wrote:
> It sounds as if having a clustered index on the timestamp could be a good
> idea.I'm guessing that you query and delete data based on timestamp so
> this index probably would help both. But mind you it'll take considerable
> time and space to create it - it's likely that it'll be even too resource
> intensive in your case.
> I still do not understand what problems you have. Did you get any error
> messages about a bad index or IO errors? If so then something seems to be
> seriously wrong with your db. Are queries slow? Inserts?
> Regards
> robert
>|||Robert is correct in that the table should have a clustered index on
datetime. It should make the inserts and especially the deletes smoother
and faster. Does the nonclustered index cover all the queries on the table
properly? How are the deletes being done now? Are they in small batches or
one big delete statement each day?
Andrew J. Kelly SQL MVP
"Sinister China Penguin" <SinisterChinaPenguin@.discussions.microsoft.com>
wrote in message news:EE3A13E0-7D0D-4E30-90FF-6CF5AE5E7501@.microsoft.com...[vbcol=seagreen]
> Really I'm just afterany general advice around looking after this big,
> busy
> table - the Integrity checks do seem to come up with Index problems quite
> often & I wanted to make sure I was doing all the right things to keep it
> working well.
> For Example I don't really fully understand fragmentattion of Non
> Clustered
> Indexes should I be defragging often? or is it not worth it? would it help
> to
> defrag/rebuild indexes before or after this big daily delete from the
> table?
> or doesn't it matter?
> Sorry to be so vauge - my DBA knowledge is a bit patchy & I just wanna
> make
> sure i'm doing things properly!
> Cheers
> --
> Mike Knee
> Attenda Monitoring & Management
>
> "Robert Klemme" wrote:
>

Managing Indexes on large tables

Hi I wonder if anyone could give some advice around managing a non clustered
Index on a large table:
rows reserved data
index_size unused
229324002 27215584 KB 20200600 KB 6940968 KB 74016
KB
We keep getting problems with this index as it is on a large & busy table -
Ideally I'd rebuild the index every day but it is a very busy production
system & that just isn't practical.
Would Index defragmentation help with Invalid key/corruption issues & the
general health of this index? or has anyone any advice on mangement
strategies for big tables/indexes'
Many Thanks for any input on this!
--
Mike Knee
Attenda Monitoring & ManagementDefragmentation is done for performance purposes. I don't know what you mean by "invalid key", but
if you have corruption issues, you need to get to the root cause of this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sinister China Penguin" <SinisterChinaPenguin@.discussions.microsoft.com> wrote in message
news:6195F440-E5C7-44E7-B162-70E5AA99A8CE@.microsoft.com...
> Hi I wonder if anyone could give some advice around managing a non clustered
> Index on a large table:
> rows reserved data
> index_size unused
> 229324002 27215584 KB 20200600 KB 6940968 KB 74016
> KB
> We keep getting problems with this index as it is on a large & busy table -
> Ideally I'd rebuild the index every day but it is a very busy production
> system & that just isn't practical.
> Would Index defragmentation help with Invalid key/corruption issues & the
> general health of this index? or has anyone any advice on mangement
> strategies for big tables/indexes'
> Many Thanks for any input on this!
> --
> Mike Knee
> Attenda Monitoring & Management|||If you are getting errors sucha s that you most likely have corruption in
the index. Since it is a nonclustered index I suggest you drop and recreate
it or run DBCC DBREINDEX on just that index to be sure you get a clean
index. It might be busy but you need to fix it. It's unlikely that you need
to rebuild it every night. If you do then you may want to adjust your fill
factor to avoid the pagesplits.
--
Andrew J. Kelly SQL MVP
"Sinister China Penguin" <SinisterChinaPenguin@.discussions.microsoft.com>
wrote in message news:6195F440-E5C7-44E7-B162-70E5AA99A8CE@.microsoft.com...
> Hi I wonder if anyone could give some advice around managing a non
> clustered
> Index on a large table:
> rows reserved data
> index_size unused
> 229324002 27215584 KB 20200600 KB 6940968 KB
> 74016
> KB
> We keep getting problems with this index as it is on a large & busy
> table -
> Ideally I'd rebuild the index every day but it is a very busy production
> system & that just isn't practical.
> Would Index defragmentation help with Invalid key/corruption issues & the
> general health of this index? or has anyone any advice on mangement
> strategies for big tables/indexes'
> Many Thanks for any input on this!
> --
> Mike Knee
> Attenda Monitoring & Management|||On Fri, 10 Feb 2006 11:03:29 -0800, "Sinister China Penguin"
<SinisterChinaPenguin@.discussions.microsoft.com> wrote:
>Hi I wonder if anyone could give some advice around managing a non clustered
>Index on a large table:
> rows reserved data index_size unused
> 229324002 27215584 KB 20200600 KB 6940968 KB 74016 KB
Well, that's reasonably large, alrighty!
>We keep getting problems with this index as it is on a large & busy table -
>Ideally I'd rebuild the index every day but it is a very busy production
>system & that just isn't practical.
>Would Index defragmentation help with Invalid key/corruption issues & the
>general health of this index? or has anyone any advice on mangement
>strategies for big tables/indexes'
That is a lot of rows on one table. Have you looked at partitioned
views (SQL2000) or partitioned tables (SQL2005)?
Only situation I've had with that many rows was with them split into
ten tables and joined with a partitioned view, had no problems with
that.
Can you say what is going on with that table when the problems occur,
I presume not all select's?
Also, what the PK is, and what the clustered key is, if different?
For that matter, what is the key in this index - single field int, or
multiple varchars, or what?
J.|||Thanks for the replies - some more details: (System is SQL2000 clustered on
Win2K)
- The table collects data points for I.T. systems performance (perfmons etc)
on 800 odd servers so there are hundreds of Inserts going on 24/7 & no
updates.
- Once a day a maintenance job runs to delete any data points > 200 days old
so the size of the table stays roughly the same...
- This table has just the one Non-Clustered Index on DataID(int - non
unique) , Time(Int) & Value (float)
- The DataID is non unique as it id defined in a "Header" table which
contains all the definitions for the performance data being collected (name
of perfmon, system name etc), the V large table I originally posted about
holds the actual performance data so there are thousands of entries for each
dataID.
- The Table is part of a commercial product (NetIQ's AppManager) so I have
no control over the schema/Indexes etc.
I'm not sure when the problems occur - I'm gussing during a flurry of Inserts?
I hope this all makes sense - I can't help but think this table could be
managed better - should I force a Full table lock for example when the
deletes are taking place? or can I manage the Index better (hence the
questions about defragging/rebuilding)
Thanks again for any advice or ideas.
--
Mike Knee
Attenda Monitoring & Management
"JXStern" wrote:
> On Fri, 10 Feb 2006 11:03:29 -0800, "Sinister China Penguin"
> <SinisterChinaPenguin@.discussions.microsoft.com> wrote:
> >Hi I wonder if anyone could give some advice around managing a non clustered
> >Index on a large table:
> >
> > rows reserved data index_size unused
> > 229324002 27215584 KB 20200600 KB 6940968 KB 74016 KB
> Well, that's reasonably large, alrighty!
> >We keep getting problems with this index as it is on a large & busy table -
> >Ideally I'd rebuild the index every day but it is a very busy production
> >system & that just isn't practical.
> >
> >Would Index defragmentation help with Invalid key/corruption issues & the
> >general health of this index? or has anyone any advice on mangement
> >strategies for big tables/indexes'
> That is a lot of rows on one table. Have you looked at partitioned
> views (SQL2000) or partitioned tables (SQL2005)?
> Only situation I've had with that many rows was with them split into
> ten tables and joined with a partitioned view, had no problems with
> that.
> Can you say what is going on with that table when the problems occur,
> I presume not all select's?
> Also, what the PK is, and what the clustered key is, if different?
> For that matter, what is the key in this index - single field int, or
> multiple varchars, or what?
> J.
>|||Sinister China Penguin wrote:
> Thanks for the replies - some more details: (System is SQL2000
> clustered on Win2K)
> - The table collects data points for I.T. systems performance
> (perfmons etc) on 800 odd servers so there are hundreds of Inserts
> going on 24/7 & no updates.
> - Once a day a maintenance job runs to delete any data points > 200
> days old so the size of the table stays roughly the same...
> - This table has just the one Non-Clustered Index on DataID(int - non
> unique) , Time(Int) & Value (float)
> - The DataID is non unique as it id defined in a "Header" table which
> contains all the definitions for the performance data being collected
> (name of perfmon, system name etc), the V large table I originally
> posted about holds the actual performance data so there are thousands
> of entries for each dataID.
> - The Table is part of a commercial product (NetIQ's AppManager) so I
> have
> no control over the schema/Indexes etc.
> I'm not sure when the problems occur - I'm gussing during a flurry of
> Inserts?
> I hope this all makes sense - I can't help but think this table could
> be managed better - should I force a Full table lock for example when
> the deletes are taking place? or can I manage the Index better (hence
> the questions about defragging/rebuilding)
> Thanks again for any advice or ideas.
It sounds as if having a clustered index on the timestamp could be a good
idea.I'm guessing that you query and delete data based on timestamp so
this index probably would help both. But mind you it'll take considerable
time and space to create it - it's likely that it'll be even too resource
intensive in your case.
I still do not understand what problems you have. Did you get any error
messages about a bad index or IO errors? If so then something seems to be
seriously wrong with your db. Are queries slow? Inserts?
Regards
robert|||Really I'm just afterany general advice around looking after this big, busy
table - the Integrity checks do seem to come up with Index problems quite
often & I wanted to make sure I was doing all the right things to keep it
working well.
For Example I don't really fully understand fragmentattion of Non Clustered
Indexes should I be defragging often? or is it not worth it? would it help to
defrag/rebuild indexes before or after this big daily delete from the table?
or doesn't it matter?
Sorry to be so vauge - my DBA knowledge is a bit patchy & I just wanna make
sure i'm doing things properly!
Cheers
--
Mike Knee
Attenda Monitoring & Management
"Robert Klemme" wrote:
> Sinister China Penguin wrote:
> > Thanks for the replies - some more details: (System is SQL2000
> > clustered on Win2K)
> >
> > - The table collects data points for I.T. systems performance
> > (perfmons etc) on 800 odd servers so there are hundreds of Inserts
> > going on 24/7 & no updates.
> > - Once a day a maintenance job runs to delete any data points > 200
> > days old so the size of the table stays roughly the same...
> > - This table has just the one Non-Clustered Index on DataID(int - non
> > unique) , Time(Int) & Value (float)
> > - The DataID is non unique as it id defined in a "Header" table which
> > contains all the definitions for the performance data being collected
> > (name of perfmon, system name etc), the V large table I originally
> > posted about holds the actual performance data so there are thousands
> > of entries for each dataID.
> > - The Table is part of a commercial product (NetIQ's AppManager) so I
> > have
> > no control over the schema/Indexes etc.
> >
> > I'm not sure when the problems occur - I'm gussing during a flurry of
> > Inserts?
> >
> > I hope this all makes sense - I can't help but think this table could
> > be managed better - should I force a Full table lock for example when
> > the deletes are taking place? or can I manage the Index better (hence
> > the questions about defragging/rebuilding)
> >
> > Thanks again for any advice or ideas.
> It sounds as if having a clustered index on the timestamp could be a good
> idea.I'm guessing that you query and delete data based on timestamp so
> this index probably would help both. But mind you it'll take considerable
> time and space to create it - it's likely that it'll be even too resource
> intensive in your case.
> I still do not understand what problems you have. Did you get any error
> messages about a bad index or IO errors? If so then something seems to be
> seriously wrong with your db. Are queries slow? Inserts?
> Regards
> robert
>|||Robert is correct in that the table should have a clustered index on
datetime. It should make the inserts and especially the deletes smoother
and faster. Does the nonclustered index cover all the queries on the table
properly? How are the deletes being done now? Are they in small batches or
one big delete statement each day?
--
Andrew J. Kelly SQL MVP
"Sinister China Penguin" <SinisterChinaPenguin@.discussions.microsoft.com>
wrote in message news:EE3A13E0-7D0D-4E30-90FF-6CF5AE5E7501@.microsoft.com...
> Really I'm just afterany general advice around looking after this big,
> busy
> table - the Integrity checks do seem to come up with Index problems quite
> often & I wanted to make sure I was doing all the right things to keep it
> working well.
> For Example I don't really fully understand fragmentattion of Non
> Clustered
> Indexes should I be defragging often? or is it not worth it? would it help
> to
> defrag/rebuild indexes before or after this big daily delete from the
> table?
> or doesn't it matter?
> Sorry to be so vauge - my DBA knowledge is a bit patchy & I just wanna
> make
> sure i'm doing things properly!
> Cheers
> --
> Mike Knee
> Attenda Monitoring & Management
>
> "Robert Klemme" wrote:
>> Sinister China Penguin wrote:
>> > Thanks for the replies - some more details: (System is SQL2000
>> > clustered on Win2K)
>> >
>> > - The table collects data points for I.T. systems performance
>> > (perfmons etc) on 800 odd servers so there are hundreds of Inserts
>> > going on 24/7 & no updates.
>> > - Once a day a maintenance job runs to delete any data points > 200
>> > days old so the size of the table stays roughly the same...
>> > - This table has just the one Non-Clustered Index on DataID(int - non
>> > unique) , Time(Int) & Value (float)
>> > - The DataID is non unique as it id defined in a "Header" table which
>> > contains all the definitions for the performance data being collected
>> > (name of perfmon, system name etc), the V large table I originally
>> > posted about holds the actual performance data so there are thousands
>> > of entries for each dataID.
>> > - The Table is part of a commercial product (NetIQ's AppManager) so I
>> > have
>> > no control over the schema/Indexes etc.
>> >
>> > I'm not sure when the problems occur - I'm gussing during a flurry of
>> > Inserts?
>> >
>> > I hope this all makes sense - I can't help but think this table could
>> > be managed better - should I force a Full table lock for example when
>> > the deletes are taking place? or can I manage the Index better (hence
>> > the questions about defragging/rebuilding)
>> >
>> > Thanks again for any advice or ideas.
>> It sounds as if having a clustered index on the timestamp could be a good
>> idea.I'm guessing that you query and delete data based on timestamp so
>> this index probably would help both. But mind you it'll take
>> considerable
>> time and space to create it - it's likely that it'll be even too resource
>> intensive in your case.
>> I still do not understand what problems you have. Did you get any error
>> messages about a bad index or IO errors? If so then something seems to
>> be
>> seriously wrong with your db. Are queries slow? Inserts?
>> Regards
>> robert
>>

Managing Database Schema Changes

I hope this is the correct news group to post this to, if not could someone
please advice me as to a better choice. We are currently releasing V1 of
our product which will include a SQL Server DB. After this point when we
want to provide Service Packs/Rollups/Upgrades/whatever to our clients we
are going to need to manage database changes in some fashion to ensure we
keep the database in sync with the App. I have seen people use source safe
for this, just keeping a script for each change and labeling them so they
get pulled out during the build process. This solution seemed to work well
enough, but I was wondering if anyone in this group had any other
suggestions. As always any advice that can be provided would be greatly
appreciated.
Thank you,
Prescott
"J Prescott Sanders" <nomorespam@.spam.com> schrieb im Newsbeitrag
news:uIemf2GVEHA.3656@.TK2MSFTNGP11.phx.gbl...
> I hope this is the correct news group to post this to, if not could
someone
> please advice me as to a better choice. We are currently releasing V1
of
> our product which will include a SQL Server DB. After this point when
we
> want to provide Service Packs/Rollups/Upgrades/whatever to our clients
we
> are going to need to manage database changes in some fashion to ensure
we
> keep the database in sync with the App. I have seen people use source
safe
> for this, just keeping a script for each change and labeling them so
they
> get pulled out during the build process. This solution seemed to work
well
> enough, but I was wondering if anyone in this group had any other
> suggestions. As always any advice that can be provided would be greatly
> appreciated.
This is a difficult topic: especially if you have to create or change
indexes on an existing database with significant amount of data. This can
easily take very long effectively stopping your application.
Since schema changes (DDL) do not happen within transaction it can be a
problem to deal with failure of schema changing statements. This is
especially true if you not only want to change the schema but also modify
data as part of a schema migration from one version to the next.
IMHO source code control is not sufficient since you'll have to take into
consideration that customers might be upgrading from different schema
versions to the current one. They might have skipped a product version
you released and the next release has to take care of both schema
migration steps.
The ideal solution would be a target schema description and some kind of
tool that compares the actual schema with this target schema and performs
all changes needed for them to be in synch again. Unfortunately I don't
know such a tool but if anybody does know such a tool I'd be glad to get
it to know.
The single golden rule I can give you: plan schema migration carefully and
try to imagine which kind of schema changes you might be doing in the
future. Since you're at the beginning of your migration history, you have
a good change to get it right. Otherwise the whole topic might easily get
out of hand. That of course depends on the nature of your application and
the expected schema changes.
Good luck!
Kind regards
robert
|||Hi Robert,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
this tool can compare the actual schema with this target schema and performs
all changes needed for them to be in synch again.
You can find it here:
http://www.extreme.co.il
"Robert Klemme" wrote:

> "J Prescott Sanders" <nomorespam@.spam.com> schrieb im Newsbeitrag
> news:uIemf2GVEHA.3656@.TK2MSFTNGP11.phx.gbl...
> someone
> of
> we
> we
> we
> safe
> they
> well
> This is a difficult topic: especially if you have to create or change
> indexes on an existing database with significant amount of data. This can
> easily take very long effectively stopping your application.
> Since schema changes (DDL) do not happen within transaction it can be a
> problem to deal with failure of schema changing statements. This is
> especially true if you not only want to change the schema but also modify
> data as part of a schema migration from one version to the next.
> IMHO source code control is not sufficient since you'll have to take into
> consideration that customers might be upgrading from different schema
> versions to the current one. They might have skipped a product version
> you released and the next release has to take care of both schema
> migration steps.
> The ideal solution would be a target schema description and some kind of
> tool that compares the actual schema with this target schema and performs
> all changes needed for them to be in synch again. Unfortunately I don't
> know such a tool but if anybody does know such a tool I'd be glad to get
> it to know.
> The single golden rule I can give you: plan schema migration carefully and
> try to imagine which kind of schema changes you might be doing in the
> future. Since you're at the beginning of your migration history, you have
> a good change to get it right. Otherwise the whole topic might easily get
> out of hand. That of course depends on the nature of your application and
> the expected schema changes.
> Good luck!
> Kind regards
> robert
>
|||Robert,
Thanks for the quick response. It seems you have quite a bit of experience
on this subject. Can you comment at all on the processes you have in place
to manage database changes within your own system. Any information will
certainly be appreciated as I work on a solution for our system.
Thanks in advance.
Prescott
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:2jdkn2F10ja1kU1@.uni-berlin.de...
> "J Prescott Sanders" <nomorespam@.spam.com> schrieb im Newsbeitrag
> news:uIemf2GVEHA.3656@.TK2MSFTNGP11.phx.gbl...
> someone
> of
> we
> we
> we
> safe
> they
> well
> This is a difficult topic: especially if you have to create or change
> indexes on an existing database with significant amount of data. This can
> easily take very long effectively stopping your application.
> Since schema changes (DDL) do not happen within transaction it can be a
> problem to deal with failure of schema changing statements. This is
> especially true if you not only want to change the schema but also modify
> data as part of a schema migration from one version to the next.
> IMHO source code control is not sufficient since you'll have to take into
> consideration that customers might be upgrading from different schema
> versions to the current one. They might have skipped a product version
> you released and the next release has to take care of both schema
> migration steps.
> The ideal solution would be a target schema description and some kind of
> tool that compares the actual schema with this target schema and performs
> all changes needed for them to be in synch again. Unfortunately I don't
> know such a tool but if anybody does know such a tool I'd be glad to get
> it to know.
> The single golden rule I can give you: plan schema migration carefully and
> try to imagine which kind of schema changes you might be doing in the
> future. Since you're at the beginning of your migration history, you have
> a good change to get it right. Otherwise the whole topic might easily get
> out of hand. That of course depends on the nature of your application and
> the expected schema changes.
> Good luck!
> Kind regards
> robert
>
|||"J Prescott Sanders" <nomorespam@.spam.com> schrieb im Newsbeitrag
news:OmmcrXHVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Robert,
> Thanks for the quick response. It seems you have quite a bit of
experience
> on this subject. Can you comment at all on the processes you have in
place
> to manage database changes within your own system. Any information will
> certainly be appreciated as I work on a solution for our system.
Well, what we do is basically store a numeric schema version number in
some table and check on startup, whether the schema version of the
software is more recent than the schema version found. If it is, we do
all single upgrade steps that we defined for each version hop.
Since a lot of legacy code is involved, things are a bit difficult
sometimes. I'd say a crucial thing to do is have several phases for
schema upgrade: a read only phase that determines whether preconditions
are satisfied (i.e. whether the schema at hand is in fact the one that it
claims to be). Only if the verification passes successfully, the next
phase is started, which modifies the dataabase. Alternatively you can do
a full backup and restore that if modifications fail.
The other factor that makes things hard for us is that we don't support a
single database.
And there's another gotcha: if you provide error fixing patches for older
versions of the software that included schema changes, then tracking of
schema versions and changes becomes really hard, because you need to
incorporate the change in the newest head version also but you have to
make sure it's not applied twice. Example:
1.0 version of software is released
2.0 version of software is released
2.0 version of software is released
1.1 version of software is released that contains a schema bug fix
Now you'll have to make sure that 3.0 checks on installation whether the
1.1 schema fix was incorporated into the db or not. You get the picture.
Kind regards
robert
[vbcol=seagreen]
> Thanks in advance.
> Prescott
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:2jdkn2F10ja1kU1@.uni-berlin.de...
V1[vbcol=seagreen]
when[vbcol=seagreen]
clients[vbcol=seagreen]
ensure[vbcol=seagreen]
source[vbcol=seagreen]
work[vbcol=seagreen]
greatly[vbcol=seagreen]
can[vbcol=seagreen]
a[vbcol=seagreen]
modify[vbcol=seagreen]
into[vbcol=seagreen]
version[vbcol=seagreen]
of[vbcol=seagreen]
performs[vbcol=seagreen]
don't[vbcol=seagreen]
get[vbcol=seagreen]
and[vbcol=seagreen]
have[vbcol=seagreen]
get[vbcol=seagreen]
and
>