Inconsistent Horrible Performance on UPDATE

We're trying to figure out what is causing an incredible inconsistency in
our query performance on the same update. query
We have a table with ~75 million records, it is indexed on a field which
breaks the table into about 25 chunks of ~2 - 3 million records.
If we run an update on a particular chunk of this table ( WHERE idField = 25000 ) and idField is set up in 2-3 million chunks with idFields from 25000
to 25025 It runs for upwards of 13 hours.
If we select out these 2-3 millions records, run an update on them delete
them from the original table, then insert them back changed it can be done
in under 2 hours.
We've been finding SQL server isn't performing well with recrodsets over 5
million. Fillfactors on tables with lots of writing are often set VERY low
~25%, wasting 30 or so GB for the sake of speed is often the route we must
go.
We suspected page splitting, but found 0 page splits occurring during these
updates, but found the reading and writing of these transactions to be maxed
out.
Also The Enterprise Manager shows the Wait Type as PAGEIOLATCH_EX and _SH.
What is causing this? All the drives are defragmented.
Also with the databases in Simple Recovery mode, why are the transaction
logs filling up?
Thanks for any information on these problems.The transaction log filling up is normal since the update is rather large
and the complete action needs to be logged and will only checkpoint once the
transaction is complete.
Could you provide DDL for the table (include the attached indexes) and the
DML (statments used during large update) plus a brief server description,
Disk. Memory, CPU.
I suspect that the logging and the index updates are what is taking so long
without these specifics though it's tough to give advice.
-Lars
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:e7p#5$pWDHA.2568@.tk2msftngp13.phx.gbl...
> We're trying to figure out what is causing an incredible inconsistency in
> our query performance on the same update. query
> We have a table with ~75 million records, it is indexed on a field which
> breaks the table into about 25 chunks of ~2 - 3 million records.
> If we run an update on a particular chunk of this table ( WHERE idField => 25000 ) and idField is set up in 2-3 million chunks with idFields from
25000
> to 25025 It runs for upwards of 13 hours.
> If we select out these 2-3 millions records, run an update on them delete
> them from the original table, then insert them back changed it can be done
> in under 2 hours.
> We've been finding SQL server isn't performing well with recrodsets over 5
> million. Fillfactors on tables with lots of writing are often set VERY
low
> ~25%, wasting 30 or so GB for the sake of speed is often the route we must
> go.
> We suspected page splitting, but found 0 page splits occurring during
these
> updates, but found the reading and writing of these transactions to be
maxed
> out.
> Also The Enterprise Manager shows the Wait Type as PAGEIOLATCH_EX and _SH.
> What is causing this? All the drives are defragmented.
> Also with the databases in Simple Recovery mode, why are the transaction
> logs filling up?
> Thanks for any information on these problems.
>|||Yes. MS has at least two KB articles (don't have the numbers offhand)
regarding parallelism problems. You might to search for them. The
workaround is to disable parallelism for the affected queries. I have also
avoided it by rewriting the query (avoid outer / self joins involving large
datasets). I've only experienced the problem in SELECTs involving large
resultsets - don't do any mass updates.
Note - using SQL Server 7. I believe that the basic issues apply to SS2K as
well.
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:%23xBZ9%23qWDHA.2444@.tk2msftngp13.phx.gbl...
> Some of these problems we've traced to when the optimizer decides to
> parallelize the process. We believe we've encountered a bug where the
> parallel processing with CXPacket and PAGEIOLATCH waits cause the process
to
> go into a dead lock of some sort but the disk usage and cpu usage for the
> process continues to be used with the disk usage at about an 8:1 ratio
> slowing the entire system dramatically.
> Has Anyone else run into problems with parallelism flaking out on large
> updates?
>
> "Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
> news:e7p%235$pWDHA.2568@.tk2msftngp13.phx.gbl...
> > We're trying to figure out what is causing an incredible inconsistency
in
> > our query performance on the same update. query
> >
> > We have a table with ~75 million records, it is indexed on a field which
> > breaks the table into about 25 chunks of ~2 - 3 million records.
> >
> > If we run an update on a particular chunk of this table ( WHERE idField
=> > 25000 ) and idField is set up in 2-3 million chunks with idFields from
> 25000
> > to 25025 It runs for upwards of 13 hours.
> >
> > If we select out these 2-3 millions records, run an update on them
delete
> > them from the original table, then insert them back changed it can be
done
> > in under 2 hours.
> >
> > We've been finding SQL server isn't performing well with recrodsets over
5
> > million. Fillfactors on tables with lots of writing are often set VERY
> low
> > ~25%, wasting 30 or so GB for the sake of speed is often the route we
must
> > go.
> >
> > We suspected page splitting, but found 0 page splits occurring during
> these
> > updates, but found the reading and writing of these transactions to be
> maxed
> > out.
> >
> > Also The Enterprise Manager shows the Wait Type as PAGEIOLATCH_EX and
_SH.
> > What is causing this? All the drives are defragmented.
> >
> > Also with the databases in Simple Recovery mode, why are the transaction
> > logs filling up?
> >
> > Thanks for any information on these problems.
> >
> >
>|||if you are have trouble with a parallel execution plan,
try it with OPTION(MAXDOP 1) to restrict the plan to 1 CPU.
There are some serious issues with Hyper-threading and
parallel execution plans, if this is applicable, disable
HT.
also, in high row count operations, the plan may spool to
the tempdb, you need to verify a high-performance disk
system for both the main app & tempdb data files, and
separate disk drive for each heavy use log file
>--Original Message--
>Some of these problems we've traced to when the optimizer
decides to
>parallelize the process. We believe we've encountered a
bug where the
>parallel processing with CXPacket and PAGEIOLATCH waits
cause the process to
>go into a dead lock of some sort but the disk usage and
cpu usage for the
>process continues to be used with the disk usage at about
an 8:1 ratio
>slowing the entire system dramatically.
>Has Anyone else run into problems with parallelism
flaking out on large
>updates?
>
>"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
>news:e7p%235$pWDHA.2568@.tk2msftngp13.phx.gbl...
>> We're trying to figure out what is causing an
incredible inconsistency in
>> our query performance on the same update. query
>> We have a table with ~75 million records, it is indexed
on a field which
>> breaks the table into about 25 chunks of ~2 - 3 million
records.
>> If we run an update on a particular chunk of this table
( WHERE idField =>> 25000 ) and idField is set up in 2-3 million chunks
with idFields from
>25000
>> to 25025 It runs for upwards of 13 hours.
>> If we select out these 2-3 millions records, run an
update on them delete
>> them from the original table, then insert them back
changed it can be done
>> in under 2 hours.
>> We've been finding SQL server isn't performing well
with recrodsets over 5
>> million. Fillfactors on tables with lots of writing
are often set VERY
>low
>> ~25%, wasting 30 or so GB for the sake of speed is
often the route we must
>> go.
>> We suspected page splitting, but found 0 page splits
occurring during
>these
>> updates, but found the reading and writing of these
transactions to be
>maxed
>> out.
>> Also The Enterprise Manager shows the Wait Type as
PAGEIOLATCH_EX and _SH.
>> What is causing this? All the drives are defragmented.
>> Also with the databases in Simple Recovery mode, why
are the transaction
>> logs filling up?
>> Thanks for any information on these problems.
>>
>
>.
>|||Well I found the OPTION MAXDOP 1 to alleviate the problems fixing 13 hour
incomplete queries to finish in under a minute! This has been a nightmare
problem causing a lot of problems and very little information on this to
find this work around.
We have every database on it's own 3 disk RAID 0 stripe, including the
system drive, temp DB, and logs. I don't think we could break it down any
more. This is on a 2GB dual Xeon 2.8Ghz with Hyperthreaded to 4. Is it
possible using Standard edition SQL server and the 3GB switch to get SQL
server to use 3GB of memory? I think we'll grab 2 more GB of memory and
see if we can get it to use some of it.
All out updates (and mostly all queries) which run over 500,000 records
which most do and are upwards of 30 or so million), now run more as expected
with the parallelism forced off. Pretty much a waste of dual cpu's with
hyperthreading.
"joe chang" <jchang6@.yahoo.com> wrote in message
news:01a601c35abb$10276fb0$a501280a@.phx.gbl...
> if you are have trouble with a parallel execution plan,
> try it with OPTION(MAXDOP 1) to restrict the plan to 1 CPU.
> There are some serious issues with Hyper-threading and
> parallel execution plans, if this is applicable, disable
> HT.
> also, in high row count operations, the plan may spool to
> the tempdb, you need to verify a high-performance disk
> system for both the main app & tempdb data files, and
> separate disk drive for each heavy use log file
> >--Original Message--
> >Some of these problems we've traced to when the optimizer
> decides to
> >parallelize the process. We believe we've encountered a
> bug where the
> >parallel processing with CXPacket and PAGEIOLATCH waits
> cause the process to
> >go into a dead lock of some sort but the disk usage and
> cpu usage for the
> >process continues to be used with the disk usage at about
> an 8:1 ratio
> >slowing the entire system dramatically.
> >
> >Has Anyone else run into problems with parallelism
> flaking out on large
> >updates?
> >
> >
> >"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
> >news:e7p%235$pWDHA.2568@.tk2msftngp13.phx.gbl...
> >> We're trying to figure out what is causing an
> incredible inconsistency in
> >> our query performance on the same update. query
> >>
> >> We have a table with ~75 million records, it is indexed
> on a field which
> >> breaks the table into about 25 chunks of ~2 - 3 million
> records.
> >>
> >> If we run an update on a particular chunk of this table
> ( WHERE idField => >> 25000 ) and idField is set up in 2-3 million chunks
> with idFields from
> >25000
> >> to 25025 It runs for upwards of 13 hours.
> >>
> >> If we select out these 2-3 millions records, run an
> update on them delete
> >> them from the original table, then insert them back
> changed it can be done
> >> in under 2 hours.
> >>
> >> We've been finding SQL server isn't performing well
> with recrodsets over 5
> >> million. Fillfactors on tables with lots of writing
> are often set VERY
> >low
> >> ~25%, wasting 30 or so GB for the sake of speed is
> often the route we must
> >> go.
> >>
> >> We suspected page splitting, but found 0 page splits
> occurring during
> >these
> >> updates, but found the reading and writing of these
> transactions to be
> >maxed
> >> out.
> >>
> >> Also The Enterprise Manager shows the Wait Type as
> PAGEIOLATCH_EX and _SH.
> >> What is causing this? All the drives are defragmented.
> >>
> >> Also with the databases in Simple Recovery mode, why
> are the transaction
> >> logs filling up?
> >>
> >> Thanks for any information on these problems.
> >>
> >>
> >
> >
> >.
> >

Inconsistency Report

I have a SQL Server 6.5 installation that I want to
migrate to sql 2000. I've installed sql 2000 on the same
machine, and next I'm starting the database upgrade.
Just before starting this process, the installation gives
me a "Warning" :
****************
Inconsistency Report for database : yumyum
****************
dbo.sp_ImporterEnteteCommande
Procedure
Text Not found
--
What can I do for that?Thanks for your Answer.
To bypass this problems here is what i've done (but not
sure if it's the best idea)
-generated a sql script of every stored proc in 6.5
-deleted every stored proc in 6.5
-migrating database to 2000
-creating every stored proc from the sql script of my
stored proc.
The migrating process gives me no warning and seems
succesfull. Then I just goes to the login section and re-
enter every password.
The problem is that on a client computer there is a c++
programm connecting to the sql server through odbc. He
is crashing at startup. After debugging this c++
programm, I've seen that the faulty lines is this one:
PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
CRecordset::none);
If you have any clue about this issue, let me know..
thanks!
Note: I have already tryed some version of Mdac component
in the client machine (running nt4) but no sucess.
>--Original Message--
>The upgrade needs the text to recreate the stored
procedure. As it is, this
>proc will not upgrade successfully. You can drop it and
the error will go
>away.
>For your new database, you should go to wherever you
keep the source of you
>stored procedures and recreate it from source code.
>(FWIW - In SQL 6.5 I believe that the text of a stored
procedure could be
>deleted as a 'security' measure to keep the code
hidden. From 7.0 forward
>this no longer works.)
>Russell Fields
>"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
wrote in message
>news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
>> I have a SQL Server 6.5 installation that I want to
>> migrate to sql 2000. I've installed sql 2000 on the
same
>> machine, and next I'm starting the database upgrade.
>> Just before starting this process, the installation
gives
>> me a "Warning" :
>> ****************
>> Inconsistency Report for database : yumyum
>> ****************
>> dbo.sp_ImporterEnteteCommande
>> Procedure
>> Text Not found
>> --
>> What can I do for that?
>
>.
>|||Jean-Francois,
Well, I am not a C++ guy, so I am not much help here. Is the C++ crashing
on its own? (Could it be an ODBC or OLE DB error in setup?) Or is the SQL
Server failing to return it the expected result and crashing because of
that?
I would be interested in what is inside strSQL. It would be interesting if
it was a call to the stored procedure that had no text. (If, when you say
"generated a sql script of every stored proc in 6.5" you mean that you
generated them from the database, any procedure that had no text in
syscomments would not be recreated.)
Russell Fields
"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com> wrote in message
news:0b2d01c35db8$108122d0$a301280a@.phx.gbl...
> Thanks for your Answer.
> To bypass this problems here is what i've done (but not
> sure if it's the best idea)
> -generated a sql script of every stored proc in 6.5
> -deleted every stored proc in 6.5
> -migrating database to 2000
> -creating every stored proc from the sql script of my
> stored proc.
> The migrating process gives me no warning and seems
> succesfull. Then I just goes to the login section and re-
> enter every password.
> The problem is that on a client computer there is a c++
> programm connecting to the sql server through odbc. He
> is crashing at startup. After debugging this c++
> programm, I've seen that the faulty lines is this one:
> PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
> CRecordset::none);
> If you have any clue about this issue, let me know..
> thanks!
> Note: I have already tryed some version of Mdac component
> in the client machine (running nt4) but no sucess.
>
> >--Original Message--
> >The upgrade needs the text to recreate the stored
> procedure. As it is, this
> >proc will not upgrade successfully. You can drop it and
> the error will go
> >away.
> >
> >For your new database, you should go to wherever you
> keep the source of you
> >stored procedures and recreate it from source code.
> >
> >(FWIW - In SQL 6.5 I believe that the text of a stored
> procedure could be
> >deleted as a 'security' measure to keep the code
> hidden. From 7.0 forward
> >this no longer works.)
> >
> >Russell Fields
> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> wrote in message
> >news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
> >> I have a SQL Server 6.5 installation that I want to
> >> migrate to sql 2000. I've installed sql 2000 on the
> same
> >> machine, and next I'm starting the database upgrade.
> >>
> >> Just before starting this process, the installation
> gives
> >> me a "Warning" :
> >>
> >> ****************
> >> Inconsistency Report for database : yumyum
> >> ****************
> >> dbo.sp_ImporterEnteteCommande
> >> Procedure
> >> Text Not found
> >> --
> >>
> >> What can I do for that?
> >
> >
> >.
> >|||If you are getting an unhandled SQL exception, you might try a Profiler
trace to spot the errant SQL statement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com> wrote in message
news:0b2d01c35db8$108122d0$a301280a@.phx.gbl...
> Thanks for your Answer.
> To bypass this problems here is what i've done (but not
> sure if it's the best idea)
> -generated a sql script of every stored proc in 6.5
> -deleted every stored proc in 6.5
> -migrating database to 2000
> -creating every stored proc from the sql script of my
> stored proc.
> The migrating process gives me no warning and seems
> succesfull. Then I just goes to the login section and re-
> enter every password.
> The problem is that on a client computer there is a c++
> programm connecting to the sql server through odbc. He
> is crashing at startup. After debugging this c++
> programm, I've seen that the faulty lines is this one:
> PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
> CRecordset::none);
> If you have any clue about this issue, let me know..
> thanks!
> Note: I have already tryed some version of Mdac component
> in the client machine (running nt4) but no sucess.
>
> >--Original Message--
> >The upgrade needs the text to recreate the stored
> procedure. As it is, this
> >proc will not upgrade successfully. You can drop it and
> the error will go
> >away.
> >
> >For your new database, you should go to wherever you
> keep the source of you
> >stored procedures and recreate it from source code.
> >
> >(FWIW - In SQL 6.5 I believe that the text of a stored
> procedure could be
> >deleted as a 'security' measure to keep the code
> hidden. From 7.0 forward
> >this no longer works.)
> >
> >Russell Fields
> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> wrote in message
> >news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
> >> I have a SQL Server 6.5 installation that I want to
> >> migrate to sql 2000. I've installed sql 2000 on the
> same
> >> machine, and next I'm starting the database upgrade.
> >>
> >> Just before starting this process, the installation
> gives
> >> me a "Warning" :
> >>
> >> ****************
> >> Inconsistency Report for database : yumyum
> >> ****************
> >> dbo.sp_ImporterEnteteCommande
> >> Procedure
> >> Text Not found
> >> --
> >>
> >> What can I do for that?
> >
> >
> >.
> >|||Jean-Francois,
The texts for stored procedures are stored in the syscomments table.
Without the texts, it is not possible to create a script for the stored
procedures in question since the scripting tool cannot create the TSQL
needed.
Regarding your problem, if the stored procedure exists and the things it
references exist, then I have no idea. Dan's recommendation to trace your
efforts with Profiler is the best idea I have.
Russell Fields
"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com> wrote in message
news:06af01c36003$b4bf4c30$a301280a@.phx.gbl...
> Sorry I didn't paste the good c++ line that is crashing..
> here it is:
> PointeurSecuriteSet->Open(CRecordset::dynaset, "{call
> sp_SelectSecurite()}", CRecordset::none);
> It is a call to a simple stored procedure with no params.
> About the stored proc, the way i have transfer them
> between sql 6.5 to sql 2000 is:
> -I just generated a sql script from 6.5;
> -in sql 2000, I copied the content of the sql script in
> the query analyser and run it.
> Like you said, the "no text" error may not be solved this
> way.. Where are defined those "text" and what are they?
> Thanks!!
>
> >--Original Message--
> >Jean-Francois,
> >
> >Well, I am not a C++ guy, so I am not much help here.
> Is the C++ crashing
> >on its own? (Could it be an ODBC or OLE DB error in
> setup?) Or is the SQL
> >Server failing to return it the expected result and
> crashing because of
> >that?
> >
> >I would be interested in what is inside strSQL. It
> would be interesting if
> >it was a call to the stored procedure that had no text.
> (If, when you say
> >"generated a sql script of every stored proc in 6.5" you
> mean that you
> >generated them from the database, any procedure that had
> no text in
> >syscomments would not be recreated.)
> >
> >Russell Fields
> >
> >
> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> wrote in message
> >news:0b2d01c35db8$108122d0$a301280a@.phx.gbl...
> >> Thanks for your Answer.
> >> To bypass this problems here is what i've done (but not
> >> sure if it's the best idea)
> >> -generated a sql script of every stored proc in 6.5
> >> -deleted every stored proc in 6.5
> >> -migrating database to 2000
> >> -creating every stored proc from the sql script of my
> >> stored proc.
> >>
> >> The migrating process gives me no warning and seems
> >> succesfull. Then I just goes to the login section and
> re-
> >> enter every password.
> >>
> >> The problem is that on a client computer there is a c++
> >> programm connecting to the sql server through odbc. He
> >> is crashing at startup. After debugging this c++
> >> programm, I've seen that the faulty lines is this one:
> >>
> >> PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
> >> CRecordset::none);
> >>
> >> If you have any clue about this issue, let me know..
> >> thanks!
> >>
> >> Note: I have already tryed some version of Mdac
> component
> >> in the client machine (running nt4) but no sucess.
> >>
> >>
> >> >--Original Message--
> >> >The upgrade needs the text to recreate the stored
> >> procedure. As it is, this
> >> >proc will not upgrade successfully. You can drop it
> and
> >> the error will go
> >> >away.
> >> >
> >> >For your new database, you should go to wherever you
> >> keep the source of you
> >> >stored procedures and recreate it from source code.
> >> >
> >> >(FWIW - In SQL 6.5 I believe that the text of a stored
> >> procedure could be
> >> >deleted as a 'security' measure to keep the code
> >> hidden. From 7.0 forward
> >> >this no longer works.)
> >> >
> >> >Russell Fields
> >> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> >> wrote in message
> >> >news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
> >> >> I have a SQL Server 6.5 installation that I want to
> >> >> migrate to sql 2000. I've installed sql 2000 on the
> >> same
> >> >> machine, and next I'm starting the database upgrade.
> >> >>
> >> >> Just before starting this process, the installation
> >> gives
> >> >> me a "Warning" :
> >> >>
> >> >> ****************
> >> >> Inconsistency Report for database : yumyum
> >> >> ****************
> >> >> dbo.sp_ImporterEnteteCommande
> >> >> Procedure
> >> >> Text Not found
> >> >> --
> >> >>
> >> >> What can I do for that?
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||The c++ programm was not working because the call to a
stored procedure in SQL 2000 must be without () if you
don't have parameters.
sp_SelectSecurite() must be :
sp_SelectSecurite
It's working well now, even with the inconsistency error.
Thanks guys

Inconsistency in HTML display

Hi all i am currently using a custom aggregate function in the Stored Proc to Concatenate certain fields and group the others. The concatenation is done be leaving an empty line (Line Break) inbetween. The problem is that the New Line Break is getting displayed in the PDF format of the Reports generated using Reporting Service 2005 but in the HTML (using IE) format the Line break does not occur, can anyone help me out with this. Doesnt IE support New Line?

You can change the width of this line with specific property (lineheigt) on that TABLEROW object. You must complete the "write" expression on that property and the results are fine.

For example you can use the function posted below to get the number of occurence of char(13) character.

TABLEROW1.LineHeight= n*(1-occurs(@.yourstring,char(13))

Create function occurs

(

@.iStr varchar(4000),

@.fStr varchar(100)

)

Returns smallint As

Begin

Declare @.rStr varchar(100)

Select @.rStr = case when charindex(left(@.fStr,1),@.fStr,2)>0 then stuff(@.fStr,charindex(left(@.fStr,1),@.fStr,2),0,'~') else Replicate('~',datalength(@.fStr)+1) end

Return(datalength(Replace(Replace(@.iStr,@.fStr,@.rStr),@.fStr,@.rStr)) - datalength(@.iStr))

End

inconsistency errors and how to fix them?

One of the db's I support is prone to throwing up inconsistency errors from time to time. The latest problem seems to relate to indexes.

Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 645577338, Index ID 3. Keys out of order on page (1:286818), slots 308 and 309.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 645577338, Index ID 3. Keys out of order on page (1:286826), slots 308 and 309.
DBCC results for 'OrderItem'.
There are 9973753 rows in 67394 pages for object 'OrderItem'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'OrderItem' (object ID 645577338).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (Iceland.dbo.OrderItem ).

I could run a DBCC checktable with the options that *might* fix the above. I am also thinking about doing an index rebuild (currently a nightly index defrag is done but no schedule for an index rebuild is in place). Another table is involved. Both table are reasonably large (one is 7 million rows and the other is nearly 10 million rows). These tables are involved in a chain of many to many relationships. I don't know if it's a factor but the chain is circular. However, this doesn't seem to cause problem in other dbs that have the same design.

What would be the recommended approach for:-

a) fixing the current db inconsistencies?
b) finding the underlying cause of the problem to stop it from happening again?

Thank in advance,

CliveMicrosoft Knowledge Base Article - 822747

Inconsistency between Data Source Designer and underlying code

I have 2 data sources that have recently been updated from SQL Express to full versions of 2005. The connection strings have been changed, and the changes appear in the code, but the data source designer still shows the SQLExpress portion of the connection string. This seems to be fouling up SSIS packages that are using these data sources. Has anyone else encountered this? If so, what can I do to fix this issue?Can you recreate them?|||Are you using configurations? If so, have you updated them to reflect the right connect strings?|||Turns out the problem was this: the project was checked out by the person who created the datasources. When he checked the datasources in, he did NOT check the project back in. The project's definition contained the incorrect connection strings. Once the project was checked in, that issue was resolved. Thanks for your responses.