index rebuilds

Hello,
We are running sql server 2000 enterprise with w2k advanced server on a
stand alone system. The application that connects to this database server is
sap version R3C. The database is over 400GB and once a month during a time
that no one is supposed to be using the system I have an index rebuild job
that rebuilds indexes which are more than 30% fragmented. This job has run
for over 2 years without issue until 2 weeks ago when our sap team hit a snag
with one of their processes(data extraction for another system) that they say
hasn't ran correctly since after the index rebuild. We have an sap
consultant who is now questioning whether performing index rebuilds is an
acceptable practice on an sap/sql server database system and seems convinced
that the index rebuild is the cause of the current state of the
malfunctioning sap process. I'm not so sure as I am told the reindexing job
was not running during the data load, (which obviously would have caused
blocking issues) and I haven't heard of any issues with index rebuilds such
as this.
Any ideas as to what could have happened during the index rebuild that could
be affecting this data extraction job post index rebuild? I have run dbcc
checkdb and it came back cleanly and there aren't any sql server errors. I'm
also not clear on how index maintenance would be handled if defragmenting
indexes is not an acceptable practice within an sap database system. Please
let me know if I'm missing something.
Are there any known issues with index rebuilds with dbcc dbreindex other
than the obvious(locking, blocking) that I should be aware of? Does anyone
know of any specific issues regarding rebuilding indexes on an sap R3 sql
server database system? Any help would be greatly appreciated.
Thanks,
Greg
In the reindex job are you using DBCC INDEXDEFRAG or DBCC DBREINDEX?
"gj111" wrote:

> Hello,
> We are running sql server 2000 enterprise with w2k advanced server on a
> stand alone system. The application that connects to this database server is
> sap version R3C. The database is over 400GB and once a month during a time
> that no one is supposed to be using the system I have an index rebuild job
> that rebuilds indexes which are more than 30% fragmented. This job has run
> for over 2 years without issue until 2 weeks ago when our sap team hit a snag
> with one of their processes(data extraction for another system) that they say
> hasn't ran correctly since after the index rebuild. We have an sap
> consultant who is now questioning whether performing index rebuilds is an
> acceptable practice on an sap/sql server database system and seems convinced
> that the index rebuild is the cause of the current state of the
> malfunctioning sap process. I'm not so sure as I am told the reindexing job
> was not running during the data load, (which obviously would have caused
> blocking issues) and I haven't heard of any issues with index rebuilds such
> as this.
> Any ideas as to what could have happened during the index rebuild that could
> be affecting this data extraction job post index rebuild? I have run dbcc
> checkdb and it came back cleanly and there aren't any sql server errors. I'm
> also not clear on how index maintenance would be handled if defragmenting
> indexes is not an acceptable practice within an sap database system. Please
> let me know if I'm missing something.
> Are there any known issues with index rebuilds with dbcc dbreindex other
> than the obvious(locking, blocking) that I should be aware of? Does anyone
> know of any specific issues regarding rebuilding indexes on an sap R3 sql
> server database system? Any help would be greatly appreciated.
> Thanks,
> Greg
>
|||In the reindex job are you using DBCC INDEXDEFRAG or DBCC DBREINDEX?
"gj111" wrote:

> Hello,
> We are running sql server 2000 enterprise with w2k advanced server on a
> stand alone system. The application that connects to this database server is
> sap version R3C. The database is over 400GB and once a month during a time
> that no one is supposed to be using the system I have an index rebuild job
> that rebuilds indexes which are more than 30% fragmented. This job has run
> for over 2 years without issue until 2 weeks ago when our sap team hit a snag
> with one of their processes(data extraction for another system) that they say
> hasn't ran correctly since after the index rebuild. We have an sap
> consultant who is now questioning whether performing index rebuilds is an
> acceptable practice on an sap/sql server database system and seems convinced
> that the index rebuild is the cause of the current state of the
> malfunctioning sap process. I'm not so sure as I am told the reindexing job
> was not running during the data load, (which obviously would have caused
> blocking issues) and I haven't heard of any issues with index rebuilds such
> as this.
> Any ideas as to what could have happened during the index rebuild that could
> be affecting this data extraction job post index rebuild? I have run dbcc
> checkdb and it came back cleanly and there aren't any sql server errors. I'm
> also not clear on how index maintenance would be handled if defragmenting
> indexes is not an acceptable practice within an sap database system. Please
> let me know if I'm missing something.
> Are there any known issues with index rebuilds with dbcc dbreindex other
> than the obvious(locking, blocking) that I should be aware of? Does anyone
> know of any specific issues regarding rebuilding indexes on an sap R3 sql
> server database system? Any help would be greatly appreciated.
> Thanks,
> Greg
>
|||I am using dbcc dbreindex.
"Cory Brown" wrote:
[vbcol=seagreen]
> In the reindex job are you using DBCC INDEXDEFRAG or DBCC DBREINDEX?
> "gj111" wrote:
|||Hi Greg,
Can you define 'hasn't run correctly'? What's the difference in behavior
before and after? And why is the consultant convinced that rebuild is the
cause of the problem?
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:7C62BEC7-E2B2-4F57-9A50-A06B5DB34EB9@.microsoft.com...
> Hello,
> We are running sql server 2000 enterprise with w2k advanced server on a
> stand alone system. The application that connects to this database server
is
> sap version R3C. The database is over 400GB and once a month during a time
> that no one is supposed to be using the system I have an index rebuild job
> that rebuilds indexes which are more than 30% fragmented. This job has
run
> for over 2 years without issue until 2 weeks ago when our sap team hit a
snag
> with one of their processes(data extraction for another system) that they
say
> hasn't ran correctly since after the index rebuild. We have an sap
> consultant who is now questioning whether performing index rebuilds is an
> acceptable practice on an sap/sql server database system and seems
convinced
> that the index rebuild is the cause of the current state of the
> malfunctioning sap process. I'm not so sure as I am told the reindexing
job
> was not running during the data load, (which obviously would have caused
> blocking issues) and I haven't heard of any issues with index rebuilds
such
> as this.
> Any ideas as to what could have happened during the index rebuild that
could
> be affecting this data extraction job post index rebuild? I have run dbcc
> checkdb and it came back cleanly and there aren't any sql server errors.
I'm
> also not clear on how index maintenance would be handled if defragmenting
> indexes is not an acceptable practice within an sap database system.
Please
> let me know if I'm missing something.
> Are there any known issues with index rebuilds with dbcc dbreindex other
> than the obvious(locking, blocking) that I should be aware of? Does
anyone
> know of any specific issues regarding rebuilding indexes on an sap R3 sql
> server database system? Any help would be greatly appreciated.
> Thanks,
> Greg
>
|||They have a app job that pulls data from a sql server base table in order to
prepare this data to be sent to a data warehouse. They are claiming that
since the day that re-indexing occured this job has began taking either an
unusually long amount of time(whatever that means) or timing out. I'm not
sure why the consultant is convinced (with almost no knowledge of sql server)
that the rebuild is the problem other than that he is tying whatever sql
server operation that happened since the last successful data extraction to
this issue for whatever reason. Oh the joy of politics!
The table that the extractions occur from was one of the tables that was
rebuilt during this index rebuild, but I honestly have no idea what this
would have to do with whatever challenges are being faced on the app side
after the rebuild. I believe I understand how index rebuild works for the
most part and can't think of anything that occurs during this process that
would cause this issue. Just want to investigate with you guys to see if
there is
anything I should be looking into as far as known issues with index rebuilds
before I begin looking elsewhere. If you know of anything please let me know.
Thanks,
Greg
"Paul S Randal [MS]" wrote:

> Hi Greg,
> Can you define 'hasn't run correctly'? What's the difference in behavior
> before and after? And why is the consultant convinced that rebuild is the
> cause of the problem?
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:7C62BEC7-E2B2-4F57-9A50-A06B5DB34EB9@.microsoft.com...
> is
> run
> snag
> say
> convinced
> job
> such
> could
> I'm
> Please
> anyone
>
>
|||Was the SQL Server service bounced after the rebuild? You might want to
check your logs for sure. An index rebuild will slow performance
*during* the rebuild, but not after, indeed it should improve performance.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
gj111 wrote:[vbcol=seagreen]
> They have a app job that pulls data from a sql server base table in order to
> prepare this data to be sent to a data warehouse. They are claiming that
> since the day that re-indexing occured this job has began taking either an
> unusually long amount of time(whatever that means) or timing out. I'm not
> sure why the consultant is convinced (with almost no knowledge of sql server)
> that the rebuild is the problem other than that he is tying whatever sql
> server operation that happened since the last successful data extraction to
> this issue for whatever reason. Oh the joy of politics!
> The table that the extractions occur from was one of the tables that was
> rebuilt during this index rebuild, but I honestly have no idea what this
> would have to do with whatever challenges are being faced on the app side
> after the rebuild. I believe I understand how index rebuild works for the
> most part and can't think of anything that occurs during this process that
> would cause this issue. Just want to investigate with you guys to see if
> there is
> anything I should be looking into as far as known issues with index rebuilds
> before I begin looking elsewhere. If you know of anything please let me know.
> Thanks,
> Greg
>
> "Paul S Randal [MS]" wrote:
>
|||The SAP people maybe correct... Since you are using the DBCC dbreindex, SQL
does lots of locking during the index rebuild, so the table will not be
available during the rebuild of its indexes... This could cause timeouts,etc
for the DW process.. You can either ensure the schedules do NOT overlap...
Or consider changing the DBCC dbreindex, to a dbcc indexdefrag, ...
indexdefrag takes much shorter, less intrusive locks... It also may run
longer, and not do quite as good a job as dbreindex... But the difference
may be negligable...
Hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:7C62BEC7-E2B2-4F57-9A50-A06B5DB34EB9@.microsoft.com...
> Hello,
> We are running sql server 2000 enterprise with w2k advanced server on a
> stand alone system. The application that connects to this database server
is
> sap version R3C. The database is over 400GB and once a month during a time
> that no one is supposed to be using the system I have an index rebuild job
> that rebuilds indexes which are more than 30% fragmented. This job has
run
> for over 2 years without issue until 2 weeks ago when our sap team hit a
snag
> with one of their processes(data extraction for another system) that they
say
> hasn't ran correctly since after the index rebuild. We have an sap
> consultant who is now questioning whether performing index rebuilds is an
> acceptable practice on an sap/sql server database system and seems
convinced
> that the index rebuild is the cause of the current state of the
> malfunctioning sap process. I'm not so sure as I am told the reindexing
job
> was not running during the data load, (which obviously would have caused
> blocking issues) and I haven't heard of any issues with index rebuilds
such
> as this.
> Any ideas as to what could have happened during the index rebuild that
could
> be affecting this data extraction job post index rebuild? I have run dbcc
> checkdb and it came back cleanly and there aren't any sql server errors.
I'm
> also not clear on how index maintenance would be handled if defragmenting
> indexes is not an acceptable practice within an sap database system.
Please
> let me know if I'm missing something.
> Are there any known issues with index rebuilds with dbcc dbreindex other
> than the obvious(locking, blocking) that I should be aware of? Does
anyone
> know of any specific issues regarding rebuilding indexes on an sap R3 sql
> server database system? Any help would be greatly appreciated.
> Thanks,
> Greg
>
|||No, sql server wasn't bounced. I've checked sql logs and NT logs and haven't
found anything. I agree it should if anything improve performance. Just
looking for any possibility that an index rebuild may have caused some
unstable issue after the rebuild.
"Mark Allison" wrote:

> Was the SQL Server service bounced after the rebuild? You might want to
> check your logs for sure. An index rebuild will slow performance
> *during* the rebuild, but not after, indeed it should improve performance.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> gj111 wrote:
>
|||The schedules didn't overlap. They are saying that this issue has come up
post index rebuild. Basically they are saying that they have tried multiple
times to extract data from a base table in sql server and it is taking way
longer or timing out post index rebuild. The table they are having issues
with was rebuilt with a 90% fill factor and I just checked it yesterday and
it appears to be in good shape from a fragmentation and integrity
perspective. Can you think of anything that may occur during an index
rebuild that may cause selects from a table to slow down substantially after
the index rebuild is finished?
Thanks,
Greg
"Wayne Snyder" wrote:

> The SAP people maybe correct... Since you are using the DBCC dbreindex, SQL
> does lots of locking during the index rebuild, so the table will not be
> available during the rebuild of its indexes... This could cause timeouts,etc
> for the DW process.. You can either ensure the schedules do NOT overlap...
> Or consider changing the DBCC dbreindex, to a dbcc indexdefrag, ...
> indexdefrag takes much shorter, less intrusive locks... It also may run
> longer, and not do quite as good a job as dbreindex... But the difference
> may be negligable...
> Hope this helps.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:7C62BEC7-E2B2-4F57-9A50-A06B5DB34EB9@.microsoft.com...
> is
> run
> snag
> say
> convinced
> job
> such
> could
> I'm
> Please
> anyone
>
>