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,
GregIn 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:
> 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
> >
> >|||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...
> > 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
> >
> >
>
>|||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:
> 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...
>>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
>>
>>|||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:
> > 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...
> >>
> >>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
> >>
> >>
> >>
> >>
> >>
>|||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...
> > 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
> >
> >
>
>|||Have you checked for fragmentation?
DBCC SHOWCONTIG (<tablename>)
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
"gj111" <gj111@.discussions.microsoft.com> wrote in message
news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> 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:
> > > 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...
> > >>
> > >>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
> > >>
> > >>
> > >>
> > >>
> > >>
> >|||Yeah, the table looks good. Scan density is close to 100% and page density
is at 90.
"michelle" wrote:
> Have you checked for fragmentation?
> DBCC SHOWCONTIG (<tablename>)
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> "gj111" <gj111@.discussions.microsoft.com> wrote in message
> news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > 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:
> > > > 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...
> > > >>
> > > >>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 can't think of anything (related to rebuild) that would cause what you're
describing. Do you have auto-shrink turned on (or a shrink maintenance job)?
It's possible that shrink ran after the rebuild and reversed the defrag that
rebuild did (making the index totally fragmented) - I've seen that happen.
--
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:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> 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:
> > > 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...
> > >>
> > >>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
> > >>
> > >>
> > >>
> > >>
> > >>
> >|||What about logical/extent fragmentation?
--
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:19CB75FC-812F-4612-AE32-A2C519C73276@.microsoft.com...
> Yeah, the table looks good. Scan density is close to 100% and page
density
> is at 90.
> "michelle" wrote:
> > Have you checked for fragmentation?
> >
> > DBCC SHOWCONTIG (<tablename>)
> > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> >
> > "gj111" <gj111@.discussions.microsoft.com> wrote in message
> > news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > > 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:
> > > > > 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...
> > > > >>
> > > > >>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
> > > > >>
> > > > >>
> > > > >>
> > > > >>
> > > > >>
> > > >
> >
> >
> >|||You could make sure that your statistics are up to date so that the query
optimizer is getting the latest information from your index defragmentation.
Check to see if you have Auto Create/Auto Update statustics on. If not then
you might want to run sp_updatestats after the index rebuild.
Regarding your question about index creation and locking issues take a
look at this white paper for detailed info on the DBCC index commands:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
"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...
> > > 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
> > >
> > >
> >
> >
> >|||Doesn't a dbcc index rebuild update the statistics as well? We have auto
update and auto create stats on. Some of the issues they were having were
the day after the index rebuild and there wasn't a large data change in the
table from the time of the rebuild and the time that there job began. I will
check to make sure the last time the stats were updated for the indexes on
this table to make sure though.
Thanks,
Greg
"Cory Brown" wrote:
> You could make sure that your statistics are up to date so that the query
> optimizer is getting the latest information from your index defragmentation.
> Check to see if you have Auto Create/Auto Update statustics on. If not then
> you might want to run sp_updatestats after the index rebuild.
> Regarding your question about index creation and locking issues take a
> look at this white paper for detailed info on the DBCC index commands:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> "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...
> > > > 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
> > > >
> > > >
> > >
> > >
> > >|||Logical and extent fragmentation were next to nothing. I would produce a
dbcc showcontig for you but they are currently trying to run this job against
that table. Once it is finished I will check the fragmentation and stats
again.
"Paul S Randal [MS]" wrote:
> What about logical/extent fragmentation?
> --
> 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:19CB75FC-812F-4612-AE32-A2C519C73276@.microsoft.com...
> > Yeah, the table looks good. Scan density is close to 100% and page
> density
> > is at 90.
> >
> > "michelle" wrote:
> >
> > > Have you checked for fragmentation?
> > >
> > > DBCC SHOWCONTIG (<tablename>)
> > > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> > >
> > > "gj111" <gj111@.discussions.microsoft.com> wrote in message
> > > news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > > > 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:
> > > > > > 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...
> > > > > >>
> > > > > >>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
> > > > > >>
> > > > > >>
> > > > > >>
> > > > > >>
> > > > > >>
> > > > >
> > >
> > >
> > >
>
>|||Rebuild automatically updates stats too.
--
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:E5B77D66-C3D8-4C2B-98CE-395CD61E6DE3@.microsoft.com...
> Doesn't a dbcc index rebuild update the statistics as well? We have auto
> update and auto create stats on. Some of the issues they were having were
> the day after the index rebuild and there wasn't a large data change in
the
> table from the time of the rebuild and the time that there job began. I
will
> check to make sure the last time the stats were updated for the indexes on
> this table to make sure though.
> Thanks,
> Greg
> "Cory Brown" wrote:
> > You could make sure that your statistics are up to date so that the
query
> > optimizer is getting the latest information from your index
defragmentation.
> > Check to see if you have Auto Create/Auto Update statustics on. If not
then
> > you might want to run sp_updatestats after the index rebuild.
> > Regarding your question about index creation and locking issues
take a
> > look at this white paper for detailed info on the DBCC index commands:
> >
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >
> > "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...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > > >|||Is there a hardware problem with the drive that table is on? What's query
performance like against the table? Have you examined the IO throughput/disk
queue lengths?
What about blocking? Are there any other rogue queries running against the
table and holding blocking locks?
Something else to consider - is this table rebuilt regularly or was this the
first time? If it was the first time, its possible that its undergoing a
massive number of page splits during the data load and that's slowing things
down. Have you checked the Page Splits/Sec perfmon counter?
--
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:3A091614-9CE7-4F56-B7C5-EF4E18640E44@.microsoft.com...
> Logical and extent fragmentation were next to nothing. I would produce a
> dbcc showcontig for you but they are currently trying to run this job
against
> that table. Once it is finished I will check the fragmentation and stats
> again.
> "Paul S Randal [MS]" wrote:
> > What about logical/extent fragmentation?
> >
> > --
> > 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:19CB75FC-812F-4612-AE32-A2C519C73276@.microsoft.com...
> > > Yeah, the table looks good. Scan density is close to 100% and page
> > density
> > > is at 90.
> > >
> > > "michelle" wrote:
> > >
> > > > Have you checked for fragmentation?
> > > >
> > > > DBCC SHOWCONTIG (<tablename>)
> > > > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> > > >
> > > > "gj111" <gj111@.discussions.microsoft.com> wrote in message
> > > > news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > > > > 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:
> > > > > > > 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...
> > > > > > >>
> > > > > > >>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
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||One more thing - is the data load done with a simple or complex query? Has
the query plan changed?
And on the page split issue I mentioned in the previous reply, are there any
non-clustered indexes (that must be maintained during data load) that are
suffereing from page split problems or poor fragmentation?
--
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:3A091614-9CE7-4F56-B7C5-EF4E18640E44@.microsoft.com...
> Logical and extent fragmentation were next to nothing. I would produce a
> dbcc showcontig for you but they are currently trying to run this job
against
> that table. Once it is finished I will check the fragmentation and stats
> again.
> "Paul S Randal [MS]" wrote:
> > What about logical/extent fragmentation?
> >
> > --
> > 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:19CB75FC-812F-4612-AE32-A2C519C73276@.microsoft.com...
> > > Yeah, the table looks good. Scan density is close to 100% and page
> > density
> > > is at 90.
> > >
> > > "michelle" wrote:
> > >
> > > > Have you checked for fragmentation?
> > > >
> > > > DBCC SHOWCONTIG (<tablename>)
> > > > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> > > >
> > > > "gj111" <gj111@.discussions.microsoft.com> wrote in message
> > > > news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > > > > 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:
> > > > > > > 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...
> > > > > > >>
> > > > > > >>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, there aren't any hardware issues that I can see. Everything seems to be
running fine as far as typical user query response times against this table.
I haven't seen any disk bottlenecks through performance monitor.
Blocking doesn't seem to be an issue as I am currently monitoring this job
and there isn't any blocking going on and I'm refreshing sp_who2 frequently.
The table that data is being extracted from is mostly read but you may be
onto something as far as the setup tables that are being inserted into. I
just noticed that these tables have clustered keys on fields(not identity or
datetime) which would likely lead to page splits in a data load. I'll look
into page splitting.
Thanks,
Greg
"Paul S Randal [MS]" wrote:
> Is there a hardware problem with the drive that table is on? What's query
> performance like against the table? Have you examined the IO throughput/disk
> queue lengths?
> What about blocking? Are there any other rogue queries running against the
> table and holding blocking locks?
> Something else to consider - is this table rebuilt regularly or was this the
> first time? If it was the first time, its possible that its undergoing a
> massive number of page splits during the data load and that's slowing things
> down. Have you checked the Page Splits/Sec perfmon counter?
> --
> 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:3A091614-9CE7-4F56-B7C5-EF4E18640E44@.microsoft.com...
> > Logical and extent fragmentation were next to nothing. I would produce a
> > dbcc showcontig for you but they are currently trying to run this job
> against
> > that table. Once it is finished I will check the fragmentation and stats
> > again.
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > What about logical/extent fragmentation?
> > >
> > > --
> > > 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:19CB75FC-812F-4612-AE32-A2C519C73276@.microsoft.com...
> > > > Yeah, the table looks good. Scan density is close to 100% and page
> > > density
> > > > is at 90.
> > > >
> > > > "michelle" wrote:
> > > >
> > > > > Have you checked for fragmentation?
> > > > >
> > > > > DBCC SHOWCONTIG (<tablename>)
> > > > > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> > > > >
> > > > > "gj111" <gj111@.discussions.microsoft.com> wrote in message
> > > > > news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > > > > > 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:
> > > > > > > > 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...
> > > > > > > >>
> > > > > > > >>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
> > > > > > > >>
> > > > > > > >>
> > > > > > > >>
> > > > > > > >>
> > > > > > > >>
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||On that note, you should also examine the fragmentation state of the tables
being read from, plusthe queries being used. If the query is using a range
or table scan and fragmentation is high, performance will suffer.
Very interested to see you results...
Regards.
--
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:8A55E84A-84B9-4F83-8277-07695EAB35CD@.microsoft.com...
> No, there aren't any hardware issues that I can see. Everything seems to
be
> running fine as far as typical user query response times against this
table.
> I haven't seen any disk bottlenecks through performance monitor.
> Blocking doesn't seem to be an issue as I am currently monitoring this job
> and there isn't any blocking going on and I'm refreshing sp_who2
frequently.
> The table that data is being extracted from is mostly read but you may be
> onto something as far as the setup tables that are being inserted into. I
> just noticed that these tables have clustered keys on fields(not identity
or
> datetime) which would likely lead to page splits in a data load. I'll
look
> into page splitting.
> Thanks,
> Greg
> "Paul S Randal [MS]" wrote:
> > Is there a hardware problem with the drive that table is on? What's
query
> > performance like against the table? Have you examined the IO
throughput/disk
> > queue lengths?
> >
> > What about blocking? Are there any other rogue queries running against
the
> > table and holding blocking locks?
> >
> > Something else to consider - is this table rebuilt regularly or was this
the
> > first time? If it was the first time, its possible that its undergoing a
> > massive number of page splits during the data load and that's slowing
things
> > down. Have you checked the Page Splits/Sec perfmon counter?
> >
> > --
> > 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:3A091614-9CE7-4F56-B7C5-EF4E18640E44@.microsoft.com...
> > > Logical and extent fragmentation were next to nothing. I would
produce a
> > > dbcc showcontig for you but they are currently trying to run this job
> > against
> > > that table. Once it is finished I will check the fragmentation and
stats
> > > again.
> > >
> > > "Paul S Randal [MS]" wrote:
> > >
> > > > What about logical/extent fragmentation?
> > > >
> > > > --
> > > > 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:19CB75FC-812F-4612-AE32-A2C519C73276@.microsoft.com...
> > > > > Yeah, the table looks good. Scan density is close to 100% and
page
> > > > density
> > > > > is at 90.
> > > > >
> > > > > "michelle" wrote:
> > > > >
> > > > > > Have you checked for fragmentation?
> > > > > >
> > > > > > DBCC SHOWCONTIG (<tablename>)
> > > > > > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> > > > > >
> > > > > > "gj111" <gj111@.discussions.microsoft.com> wrote in message
> > > > > > news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > > > > > > 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:
> > > > > > > > > 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...
> > > > > > > > >>
> > > > > > > > >>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've put out an inquiry asking the exact method that is used for this data
load, but haven't heard anything yet.
There aren't any non-clustered indexes on the the tables being inserted.
There is a composite clustered primary key spanning four fields on these
tables. Right now while the job is running page splitting is holding steady
at 14 page splits/sec. This system is mostly reads so I'm sure that the page
splitting is mostly specific to the inserts in these setup tables. I don't
know if there is a specific threshold to watch out for when it comes to page
splits, so I'm not sure if 14 a second should be considered an issue or not.
Thanks,
Greg
"Paul S Randal [MS]" wrote:
> One more thing - is the data load done with a simple or complex query? Has
> the query plan changed?
> And on the page split issue I mentioned in the previous reply, are there any
> non-clustered indexes (that must be maintained during data load) that are
> suffereing from page split problems or poor fragmentation?
> --
> 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:3A091614-9CE7-4F56-B7C5-EF4E18640E44@.microsoft.com...
> > Logical and extent fragmentation were next to nothing. I would produce a
> > dbcc showcontig for you but they are currently trying to run this job
> against
> > that table. Once it is finished I will check the fragmentation and stats
> > again.
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > What about logical/extent fragmentation?
> > >
> > > --
> > > 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:19CB75FC-812F-4612-AE32-A2C519C73276@.microsoft.com...
> > > > Yeah, the table looks good. Scan density is close to 100% and page
> > > density
> > > > is at 90.
> > > >
> > > > "michelle" wrote:
> > > >
> > > > > Have you checked for fragmentation?
> > > > >
> > > > > DBCC SHOWCONTIG (<tablename>)
> > > > > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> > > > >
> > > > > "gj111" <gj111@.discussions.microsoft.com> wrote in message
> > > > > news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > > > > > 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:
> > > > > > > > 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...
> > > > > > > >>
> > > > > > > >>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
> > > > > > > >>
> > > > > > > >>
> > > > > > > >>
> > > > > > > >>
> > > > > > > >>
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||It depends on the size of the rows, fillfactor and rows inserted per second.
As a rough example, consider a table with 100 byte records and fillfactor of
100%. One page holds roughly 80 records. A page split copies half the
records to the new page. So, for loading perfectly sorted data you'd expect
the 1st page to split at 80 rows inserted and then subsequent pages to split
every 40 rows inserted (i.e. as they fill up). For random data, a split
should occur every 40-80 inserts.
So, given your avg row size, fillfactor and rows inserted/second you should
be able to come up with a reasonable number for page splits per second.
Regards.
--
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:0C0610BE-D78F-4A22-87F7-DEB08CFACB9C@.microsoft.com...
> I've put out an inquiry asking the exact method that is used for this data
> load, but haven't heard anything yet.
> There aren't any non-clustered indexes on the the tables being inserted.
> There is a composite clustered primary key spanning four fields on these
> tables. Right now while the job is running page splitting is holding
steady
> at 14 page splits/sec. This system is mostly reads so I'm sure that the
page
> splitting is mostly specific to the inserts in these setup tables. I
don't
> know if there is a specific threshold to watch out for when it comes to
page
> splits, so I'm not sure if 14 a second should be considered an issue or
not.
> Thanks,
> Greg
> "Paul S Randal [MS]" wrote:
> > One more thing - is the data load done with a simple or complex query?
Has
> > the query plan changed?
> >
> > And on the page split issue I mentioned in the previous reply, are there
any
> > non-clustered indexes (that must be maintained during data load) that
are
> > suffereing from page split problems or poor fragmentation?
> >
> > --
> > 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:3A091614-9CE7-4F56-B7C5-EF4E18640E44@.microsoft.com...
> > > Logical and extent fragmentation were next to nothing. I would
produce a
> > > dbcc showcontig for you but they are currently trying to run this job
> > against
> > > that table. Once it is finished I will check the fragmentation and
stats
> > > again.
> > >
> > > "Paul S Randal [MS]" wrote:
> > >
> > > > What about logical/extent fragmentation?
> > > >
> > > > --
> > > > 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:19CB75FC-812F-4612-AE32-A2C519C73276@.microsoft.com...
> > > > > Yeah, the table looks good. Scan density is close to 100% and
page
> > > > density
> > > > > is at 90.
> > > > >
> > > > > "michelle" wrote:
> > > > >
> > > > > > Have you checked for fragmentation?
> > > > > >
> > > > > > DBCC SHOWCONTIG (<tablename>)
> > > > > > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> > > > > >
> > > > > > "gj111" <gj111@.discussions.microsoft.com> wrote in message
> > > > > > news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > > > > > > 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:
> > > > > > > > > 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...
> > > > > > > > >>
> > > > > > > > >>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
> > > > > > > > >>
> > > > > > > > >>
> > > > > > > > >>
> > > > > > > > >>
> > > > > > > > >>
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Thanks Paul. I appreciate your help with this.
Greg
"Paul S Randal [MS]" wrote:
> It depends on the size of the rows, fillfactor and rows inserted per second.
> As a rough example, consider a table with 100 byte records and fillfactor of
> 100%. One page holds roughly 80 records. A page split copies half the
> records to the new page. So, for loading perfectly sorted data you'd expect
> the 1st page to split at 80 rows inserted and then subsequent pages to split
> every 40 rows inserted (i.e. as they fill up). For random data, a split
> should occur every 40-80 inserts.
> So, given your avg row size, fillfactor and rows inserted/second you should
> be able to come up with a reasonable number for page splits per second.
> Regards.
> --
> 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:0C0610BE-D78F-4A22-87F7-DEB08CFACB9C@.microsoft.com...
> > I've put out an inquiry asking the exact method that is used for this data
> > load, but haven't heard anything yet.
> >
> > There aren't any non-clustered indexes on the the tables being inserted.
> > There is a composite clustered primary key spanning four fields on these
> > tables. Right now while the job is running page splitting is holding
> steady
> > at 14 page splits/sec. This system is mostly reads so I'm sure that the
> page
> > splitting is mostly specific to the inserts in these setup tables. I
> don't
> > know if there is a specific threshold to watch out for when it comes to
> page
> > splits, so I'm not sure if 14 a second should be considered an issue or
> not.
> >
> > Thanks,
> > Greg
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > One more thing - is the data load done with a simple or complex query?
> Has
> > > the query plan changed?
> > >
> > > And on the page split issue I mentioned in the previous reply, are there
> any
> > > non-clustered indexes (that must be maintained during data load) that
> are
> > > suffereing from page split problems or poor fragmentation?
> > >
> > > --
> > > 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:3A091614-9CE7-4F56-B7C5-EF4E18640E44@.microsoft.com...
> > > > Logical and extent fragmentation were next to nothing. I would
> produce a
> > > > dbcc showcontig for you but they are currently trying to run this job
> > > against
> > > > that table. Once it is finished I will check the fragmentation and
> stats
> > > > again.
> > > >
> > > > "Paul S Randal [MS]" wrote:
> > > >
> > > > > What about logical/extent fragmentation?
> > > > >
> > > > > --
> > > > > 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:19CB75FC-812F-4612-AE32-A2C519C73276@.microsoft.com...
> > > > > > Yeah, the table looks good. Scan density is close to 100% and
> page
> > > > > density
> > > > > > is at 90.
> > > > > >
> > > > > > "michelle" wrote:
> > > > > >
> > > > > > > Have you checked for fragmentation?
> > > > > > >
> > > > > > > DBCC SHOWCONTIG (<tablename>)
> > > > > > > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> > > > > > >
> > > > > > > "gj111" <gj111@.discussions.microsoft.com> wrote in message
> > > > > > > news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > > > > > > > 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:
> > > > > > > > > > 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...
> > > > > > > > > >>
> > > > > > > > > >>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
> > > > > > > > > >>
> > > > > > > > > >>
> > > > > > > > > >>
> > > > > > > > > >>
> > > > > > > > > >>
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||One of the larger tables being read from is pretty fragmented so I'm sure
this is contributing to the performance of this data load. We updated the
stats on some of the tables involved and recompiled the stored procs that are
used for the data load and this seems to have helped. I'm trying to get a
window to rebuild the indexes on the larger table at which time we will
recompile again and see how it goes.
Thanks,
Greg
"Paul S Randal [MS]" wrote:
> On that note, you should also examine the fragmentation state of the tables
> being read from, plusthe queries being used. If the query is using a range
> or table scan and fragmentation is high, performance will suffer.
> Very interested to see you results...
> Regards.
> --
> 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:8A55E84A-84B9-4F83-8277-07695EAB35CD@.microsoft.com...
> > No, there aren't any hardware issues that I can see. Everything seems to
> be
> > running fine as far as typical user query response times against this
> table.
> > I haven't seen any disk bottlenecks through performance monitor.
> >
> > Blocking doesn't seem to be an issue as I am currently monitoring this job
> > and there isn't any blocking going on and I'm refreshing sp_who2
> frequently.
> >
> > The table that data is being extracted from is mostly read but you may be
> > onto something as far as the setup tables that are being inserted into. I
> > just noticed that these tables have clustered keys on fields(not identity
> or
> > datetime) which would likely lead to page splits in a data load. I'll
> look
> > into page splitting.
> >
> > Thanks,
> > Greg
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > Is there a hardware problem with the drive that table is on? What's
> query
> > > performance like against the table? Have you examined the IO
> throughput/disk
> > > queue lengths?
> > >
> > > What about blocking? Are there any other rogue queries running against
> the
> > > table and holding blocking locks?
> > >
> > > Something else to consider - is this table rebuilt regularly or was this
> the
> > > first time? If it was the first time, its possible that its undergoing a
> > > massive number of page splits during the data load and that's slowing
> things
> > > down. Have you checked the Page Splits/Sec perfmon counter?
> > >
> > > --
> > > 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:3A091614-9CE7-4F56-B7C5-EF4E18640E44@.microsoft.com...
> > > > Logical and extent fragmentation were next to nothing. I would
> produce a
> > > > dbcc showcontig for you but they are currently trying to run this job
> > > against
> > > > that table. Once it is finished I will check the fragmentation and
> stats
> > > > again.
> > > >
> > > > "Paul S Randal [MS]" wrote:
> > > >
> > > > > What about logical/extent fragmentation?
> > > > >
> > > > > --
> > > > > 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:19CB75FC-812F-4612-AE32-A2C519C73276@.microsoft.com...
> > > > > > Yeah, the table looks good. Scan density is close to 100% and
> page
> > > > > density
> > > > > > is at 90.
> > > > > >
> > > > > > "michelle" wrote:
> > > > > >
> > > > > > > Have you checked for fragmentation?
> > > > > > >
> > > > > > > DBCC SHOWCONTIG (<tablename>)
> > > > > > > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> > > > > > >
> > > > > > > "gj111" <gj111@.discussions.microsoft.com> wrote in message
> > > > > > > news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > > > > > > > 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:
> > > > > > > > > > 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...
> > > > > > > > > >>
> > > > > > > > > >>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
> > > > > > > > > >>
> > > > > > > > > >>
> > > > > > > > > >>
> > > > > > > > > >>
> > > > > > > > > >>
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Cool. Sounds like the consultant that was hired needs some perf
troubleshooting education too :-)
Regards.
--
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:EE2E4BC5-B158-4A7D-A076-9F976808AF82@.microsoft.com...
> One of the larger tables being read from is pretty fragmented so I'm sure
> this is contributing to the performance of this data load. We updated the
> stats on some of the tables involved and recompiled the stored procs that
are
> used for the data load and this seems to have helped. I'm trying to get a
> window to rebuild the indexes on the larger table at which time we will
> recompile again and see how it goes.
> Thanks,
> Greg
> "Paul S Randal [MS]" wrote:
> > On that note, you should also examine the fragmentation state of the
tables
> > being read from, plusthe queries being used. If the query is using a
range
> > or table scan and fragmentation is high, performance will suffer.
> >
> > Very interested to see you results...
> >
> > Regards.
> >
> > --
> > 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:8A55E84A-84B9-4F83-8277-07695EAB35CD@.microsoft.com...
> > > No, there aren't any hardware issues that I can see. Everything seems
to
> > be
> > > running fine as far as typical user query response times against this
> > table.
> > > I haven't seen any disk bottlenecks through performance monitor.
> > >
> > > Blocking doesn't seem to be an issue as I am currently monitoring this
job
> > > and there isn't any blocking going on and I'm refreshing sp_who2
> > frequently.
> > >
> > > The table that data is being extracted from is mostly read but you may
be
> > > onto something as far as the setup tables that are being inserted
into. I
> > > just noticed that these tables have clustered keys on fields(not
identity
> > or
> > > datetime) which would likely lead to page splits in a data load. I'll
> > look
> > > into page splitting.
> > >
> > > Thanks,
> > > Greg
> > >
> > > "Paul S Randal [MS]" wrote:
> > >
> > > > Is there a hardware problem with the drive that table is on? What's
> > query
> > > > performance like against the table? Have you examined the IO
> > throughput/disk
> > > > queue lengths?
> > > >
> > > > What about blocking? Are there any other rogue queries running
against
> > the
> > > > table and holding blocking locks?
> > > >
> > > > Something else to consider - is this table rebuilt regularly or was
this
> > the
> > > > first time? If it was the first time, its possible that its
undergoing a
> > > > massive number of page splits during the data load and that's
slowing
> > things
> > > > down. Have you checked the Page Splits/Sec perfmon counter?
> > > >
> > > > --
> > > > 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:3A091614-9CE7-4F56-B7C5-EF4E18640E44@.microsoft.com...
> > > > > Logical and extent fragmentation were next to nothing. I would
> > produce a
> > > > > dbcc showcontig for you but they are currently trying to run this
job
> > > > against
> > > > > that table. Once it is finished I will check the fragmentation
and
> > stats
> > > > > again.
> > > > >
> > > > > "Paul S Randal [MS]" wrote:
> > > > >
> > > > > > What about logical/extent fragmentation?
> > > > > >
> > > > > > --
> > > > > > 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:19CB75FC-812F-4612-AE32-A2C519C73276@.microsoft.com...
> > > > > > > Yeah, the table looks good. Scan density is close to 100% and
> > page
> > > > > > density
> > > > > > > is at 90.
> > > > > > >
> > > > > > > "michelle" wrote:
> > > > > > >
> > > > > > > > Have you checked for fragmentation?
> > > > > > > >
> > > > > > > > DBCC SHOWCONTIG (<tablename>)
> > > > > > > > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> > > > > > > >
> > > > > > > > "gj111" <gj111@.discussions.microsoft.com> wrote in message
> > > > > > > > news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > > > > > > > > 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:
> > > > > > > > > > > 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...
> > > > > > > > > > >>
> > > > > > > > > > >>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
> > > > > > > > > > >>
> > > > > > > > > > >>
> > > > > > > > > > >>
> > > > > > > > > > >>
> > > > > > > > > > >>
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Yeah, among other things : )
Thanks again for the help with this, very much appreciated.
Greg
"Paul S Randal [MS]" wrote:
> Cool. Sounds like the consultant that was hired needs some perf
> troubleshooting education too :-)
> Regards.
> --
> 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:EE2E4BC5-B158-4A7D-A076-9F976808AF82@.microsoft.com...
> > One of the larger tables being read from is pretty fragmented so I'm sure
> > this is contributing to the performance of this data load. We updated the
> > stats on some of the tables involved and recompiled the stored procs that
> are
> > used for the data load and this seems to have helped. I'm trying to get a
> > window to rebuild the indexes on the larger table at which time we will
> > recompile again and see how it goes.
> >
> > Thanks,
> > Greg
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > On that note, you should also examine the fragmentation state of the
> tables
> > > being read from, plusthe queries being used. If the query is using a
> range
> > > or table scan and fragmentation is high, performance will suffer.
> > >
> > > Very interested to see you results...
> > >
> > > Regards.
> > >
> > > --
> > > 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:8A55E84A-84B9-4F83-8277-07695EAB35CD@.microsoft.com...
> > > > No, there aren't any hardware issues that I can see. Everything seems
> to
> > > be
> > > > running fine as far as typical user query response times against this
> > > table.
> > > > I haven't seen any disk bottlenecks through performance monitor.
> > > >
> > > > Blocking doesn't seem to be an issue as I am currently monitoring this
> job
> > > > and there isn't any blocking going on and I'm refreshing sp_who2
> > > frequently.
> > > >
> > > > The table that data is being extracted from is mostly read but you may
> be
> > > > onto something as far as the setup tables that are being inserted
> into. I
> > > > just noticed that these tables have clustered keys on fields(not
> identity
> > > or
> > > > datetime) which would likely lead to page splits in a data load. I'll
> > > look
> > > > into page splitting.
> > > >
> > > > Thanks,
> > > > Greg
> > > >
> > > > "Paul S Randal [MS]" wrote:
> > > >
> > > > > Is there a hardware problem with the drive that table is on? What's
> > > query
> > > > > performance like against the table? Have you examined the IO
> > > throughput/disk
> > > > > queue lengths?
> > > > >
> > > > > What about blocking? Are there any other rogue queries running
> against
> > > the
> > > > > table and holding blocking locks?
> > > > >
> > > > > Something else to consider - is this table rebuilt regularly or was
> this
> > > the
> > > > > first time? If it was the first time, its possible that its
> undergoing a
> > > > > massive number of page splits during the data load and that's
> slowing
> > > things
> > > > > down. Have you checked the Page Splits/Sec perfmon counter?
> > > > >
> > > > > --
> > > > > 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:3A091614-9CE7-4F56-B7C5-EF4E18640E44@.microsoft.com...
> > > > > > Logical and extent fragmentation were next to nothing. I would
> > > produce a
> > > > > > dbcc showcontig for you but they are currently trying to run this
> job
> > > > > against
> > > > > > that table. Once it is finished I will check the fragmentation
> and
> > > stats
> > > > > > again.
> > > > > >
> > > > > > "Paul S Randal [MS]" wrote:
> > > > > >
> > > > > > > What about logical/extent fragmentation?
> > > > > > >
> > > > > > > --
> > > > > > > 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:19CB75FC-812F-4612-AE32-A2C519C73276@.microsoft.com...
> > > > > > > > Yeah, the table looks good. Scan density is close to 100% and
> > > page
> > > > > > > density
> > > > > > > > is at 90.
> > > > > > > >
> > > > > > > > "michelle" wrote:
> > > > > > > >
> > > > > > > > > Have you checked for fragmentation?
> > > > > > > > >
> > > > > > > > > DBCC SHOWCONTIG (<tablename>)
> > > > > > > > > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> > > > > > > > >
> > > > > > > > > "gj111" <gj111@.discussions.microsoft.com> wrote in message
> > > > > > > > > news:A6CB818B-3343-40C4-99CF-5F21FF369345@.microsoft.com...
> > > > > > > > > > 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:
> > > > > > > > > > > > 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...
> > > > > > > > > > > >>
> > > > > > > > > > > >>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
> > > > > > > > > > > >>
> > > > > > > > > > > >>
> > > > > > > > > > > >>
> > > > > > > > > > > >>
> > > > > > > > > > > >>
> > > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||The only thing I can think of is that now would be the time to increase the
free space/decrease the fill factor, especially for load processes.
What I am not sure about your comments is whether the SAP rep is saying the
load processes are slowing down inserting into the tables that where
reindexed, or, that the reads are now taking more time?
If it is the reads, what are the current query durations when you've
profiled the system? If the query reads are taking too long, then it may
very well be the statistics. We have started seeing odd issues with
statistics after rebuild operations. The fix was to run sp_updatestats
which runs fairly fast just after a rebuild operation.
Then again, maybe SAP just likes highly fragmented systems. Tell that rep
to go away until they can talk without their head between their legs.
Sincerely,
Anthony Thomas
"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