I have a database that I recently reindexed (all tables) and users are
complaining of slow report performance. What could cause this? Is it updating
statistics some how? Or rebuilding the cache?
I ran the same complaint reports on the same database on another server that
was not reindexed and the reports ran just fine.
Auto update and auto create statistics are checked in the above databases.
Thank you in advance.
--
Message posted via http://www.sqlmonster.comDid you check (and compare!) execution plans on each server?
A
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message
news:7d53e85ad059a@.uwe...
>I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.sqlmonster.com
>|||I was able to get statistics on the query on the database that completes the
query, I'm still waiting for the query to complete on the problem database...
I'll post when finished.
Aaron Bertrand [SQL Server MVP] wrote:
>Did you check (and compare!) execution plans on each server?
>A
>>I have a database that I recently reindexed (all tables) and users are
>> complaining of slow report performance. What could cause this? Is it
>[quoted text clipped - 8 lines]
>> Thank you in advance.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200712/1|||Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics go
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via SQLMonster.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.sqlmonster.com
>|||If you rebuild an index, the stats are updated automatically.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:23D02E88-B53F-415C-9DF8-571EC80004B4@.microsoft.com...
Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics
go
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via SQLMonster.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.sqlmonster.com
>
Showing posts with label reindexed. Show all posts
Showing posts with label reindexed. Show all posts
Monday, March 12, 2012
Reindexing can cause slowness?
I have a database that I recently reindexed (all tables) and users are
complaining of slow report performance. What could cause this? Is it updating
statistics some how? Or rebuilding the cache?
I ran the same complaint reports on the same database on another server that
was not reindexed and the reports ran just fine.
Auto update and auto create statistics are checked in the above databases.
Thank you in advance.
Message posted via http://www.droptable.com
Did you check (and compare!) execution plans on each server?
A
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:7d53e85ad059a@.uwe...
>I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
|||I was able to get statistics on the query on the database that completes the
query, I'm still waiting for the query to complete on the problem database...
I'll post when finished.
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>Did you check (and compare!) execution plans on each server?
>A
>[quoted text clipped - 8 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200712/1
|||Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics go
out of sync with the index information and degrade performance.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
|||If you rebuild an index, the stats are updated automatically.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:23D02E88-B53F-415C-9DF8-571EC80004B4@.microsoft.com...
Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics
go
out of sync with the index information and degrade performance.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
complaining of slow report performance. What could cause this? Is it updating
statistics some how? Or rebuilding the cache?
I ran the same complaint reports on the same database on another server that
was not reindexed and the reports ran just fine.
Auto update and auto create statistics are checked in the above databases.
Thank you in advance.
Message posted via http://www.droptable.com
Did you check (and compare!) execution plans on each server?
A
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:7d53e85ad059a@.uwe...
>I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
|||I was able to get statistics on the query on the database that completes the
query, I'm still waiting for the query to complete on the problem database...
I'll post when finished.
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>Did you check (and compare!) execution plans on each server?
>A
>[quoted text clipped - 8 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200712/1
|||Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics go
out of sync with the index information and degrade performance.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
|||If you rebuild an index, the stats are updated automatically.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:23D02E88-B53F-415C-9DF8-571EC80004B4@.microsoft.com...
Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics
go
out of sync with the index information and degrade performance.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
Reindexing can cause slowness?
I have a database that I recently reindexed (all tables) and users are
complaining of slow report performance. What could cause this? Is it updatin
g
statistics some how? Or rebuilding the cache?
I ran the same complaint reports on the same database on another server that
was not reindexed and the reports ran just fine.
Auto update and auto create statistics are checked in the above databases.
Thank you in advance.
Message posted via http://www.droptable.comDid you check (and compare!) execution plans on each server?
A
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:7d53e85ad059a@.uwe...
>I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>|||I was able to get statistics on the query on the database that completes the
query, I'm still waiting for the query to complete on the problem database..
.
I'll post when finished.
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>Did you check (and compare!) execution plans on each server?
>A
>
>[quoted text clipped - 8 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200712/1|||Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics g
o
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it updat
ing
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server th
at
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>|||If you rebuild an index, the stats are updated automatically.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:23D02E88-B53F-415C-9DF8-571EC80004B4@.microsoft.com...
Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics
go
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
complaining of slow report performance. What could cause this? Is it updatin
g
statistics some how? Or rebuilding the cache?
I ran the same complaint reports on the same database on another server that
was not reindexed and the reports ran just fine.
Auto update and auto create statistics are checked in the above databases.
Thank you in advance.
Message posted via http://www.droptable.comDid you check (and compare!) execution plans on each server?
A
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:7d53e85ad059a@.uwe...
>I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>|||I was able to get statistics on the query on the database that completes the
query, I'm still waiting for the query to complete on the problem database..
.
I'll post when finished.
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>Did you check (and compare!) execution plans on each server?
>A
>
>[quoted text clipped - 8 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200712/1|||Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics g
o
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it updat
ing
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server th
at
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>|||If you rebuild an index, the stats are updated automatically.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:23D02E88-B53F-415C-9DF8-571EC80004B4@.microsoft.com...
Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics
go
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
Reindex tables question
We reindexed all of our tables this weekend. We ran the showcontig for
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!How many pages are the indexes? How much free space in the database? Clustered or non-clustered indexes? Do
you have one or several data files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
--
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!How many pages are the indexes? How much free space in the database? Clustered or non-clustered indexes? Do
you have one or several data files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
--
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
Reindex tables question
We reindexed all of our tables this weekend. We ran the showcontig for
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
How many pages are the indexes? How much free space in the database? Clustered or non-clustered indexes? Do
you have one or several data files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thanks everyone for responding. I actually printed out that white paper
this morning and am going to wait to play with this anymore until I have
read the whole thing cover to cover.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
How many pages are the indexes? How much free space in the database? Clustered or non-clustered indexes? Do
you have one or several data files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thanks everyone for responding. I actually printed out that white paper
this morning and am going to wait to play with this anymore until I have
read the whole thing cover to cover.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Friday, March 9, 2012
Reindex tables question
We reindexed all of our tables this weekend. We ran the showcontig for
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!How many pages are the indexes? How much free space in the database? Cluster
ed or non-clustered indexes? Do
you have one or several data files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl
..
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks everyone for responding. I actually printed out that white paper
this morning and am going to wait to play with this anymore until I have
read the whole thing cover to cover.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!How many pages are the indexes? How much free space in the database? Cluster
ed or non-clustered indexes? Do
you have one or several data files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl
..
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks everyone for responding. I actually printed out that white paper
this morning and am going to wait to play with this anymore until I have
read the whole thing cover to cover.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Reindex HUGE table - production issue
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LTRun sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather
using DBCC INDEXDEFRAG so that you can defrag online and allow your users
access to the table.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||sp_who2 will show the processes that are building the Index. You may see 4
or more processes at work if you have a box with multiple processes.
Every time you run sp_who2, the values for the processes columns "DiskIO"
and "CPUTime" will be seen to increase. This indicates that the processes
are still running and alive.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
dex some of them at one weekend, the next portion the following weekend etc.
Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:eFiDyCS2DHA.34
96@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather u
sing DBCC INDEXDEFRAG so that you can defrag online and allow your users acc
ess to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message news:4fdf6507.0401120718.11fe
ddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||Yep. Taking that a little further, consider horizontal partitioning. Break
up the table into more manageable chunks and use a partitioned view. It
could very well be that only one partition need reindexing.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Quentin Ran" <ab@.who.com> wrote in message
news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
... and if you do feel needing to reindex, run the indexing script, to
reindex some of them at one weekend, the next portion the following weekend
etc. Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with
the index rebuild. Also, in future, consider not doing a rebuild but,
rather using DBCC INDEXDEFRAG so that you can defrag online and allow your
users access to the table.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||Thanks for your help. I did notice the DISK IO moving up,
but not by much. And I haven't seen it move in the past 30
minutes. Do you know what the consequences of killing the
job will be? Will the table get corrupt? How long will it
take to rollback?
Thanks,
LT
partitioning. Break
partitioned view. It
reindexing.
--
indexing script, to
following weekend
about index defrag.
SPID associated with
rebuild but,
online and allow your
--
middle of
table before
doing anything or
users are
during business
Potentially, this can take just as long as it has already been running.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...
Thanks for your help. I did notice the DISK IO moving up,
but not by much. And I haven't seen it move in the past 30
minutes. Do you know what the consequences of killing the
job will be? Will the table get corrupt? How long will it
take to rollback?
Thanks,
LT
partitioning. Break
partitioned view. It
reindexing.
--
indexing script, to
following weekend
about index defrag.
SPID associated with
rebuild but,
online and allow your
--
middle of
table before
doing anything or
users are
during business
, the log file could get really big!
The script is in books online - it does the defrag by percent fragmentation
- so start at something like 70% and work your way down over a period of day
s...
"Quentin Ran" <ab@.who.com> wrote in message news:ul0s1VS2DHA.556@.TK2MSFTNGP1
1.phx.gbl...
.. and if you do feel needing to reindex, run the indexing script, to reind
ex some of them at one weekend, the next portion the following weekend etc.
Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:eFiDyCS2DHA.34
96@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather u
sing DBCC INDEXDEFRAG so that you can defrag online and allow your users acc
ess to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message news:4fdf6507.0401120718.11fe
ddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||Right, but that's the tradeoff you make for running the defrag online instea
d of rebuilding the index offline.
See the whitepaper at
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
for more details.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message news:e4YBLod2DHA
.3216@.TK2MSFTNGP11.phx.gbl...
Good advise ... Because IndexDefrag runs in short fully logged transactions
, the log file could get really big!
The script is in books online - it does the defrag by percent fragmentation
- so start at something like 70% and work your way down over a period of day
s...
"Quentin Ran" <ab@.who.com> wrote in message news:ul0s1VS2DHA.556@.TK2MSFTNGP1
1.phx.gbl...
.. and if you do feel needing to reindex, run the indexing script, to reind
ex some of them at one weekend, the next portion the following weekend etc.
Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:eFiDyCS2DHA.34
96@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather u
sing DBCC INDEXDEFRAG so that you can defrag online and allow your users acc
ess to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message news:4fdf6507.0401120718.11fe
ddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||FYI- I ended up canceling the job, it did a rollback for
about 10 minutes and that's it! Thanks for your help
again.
rolled back.
been running.
--
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LTRun sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather
using DBCC INDEXDEFRAG so that you can defrag online and allow your users
access to the table.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||sp_who2 will show the processes that are building the Index. You may see 4
or more processes at work if you have a box with multiple processes.
Every time you run sp_who2, the values for the processes columns "DiskIO"
and "CPUTime" will be seen to increase. This indicates that the processes
are still running and alive.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
quote:|||... and if you do feel needing to reindex, run the indexing script, to rein
> Hi,
> I have a very large table (400 Gig) that I am in the middle of
> reindexing, it has been 60 hours. I have reindexed this table before
> and it ran for 44 hours.
> Does anyone know how to tell if it is still actually doing anything or
> if it is stuck?
> Please if anyone has any suggestions, let me know. The users are
> complaining about this table not being availiable during business
> hours!!!
> Thanks,
> LT
dex some of them at one weekend, the next portion the following weekend etc.
Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:eFiDyCS2DHA.34
96@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather u
sing DBCC INDEXDEFRAG so that you can defrag online and allow your users acc
ess to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message news:4fdf6507.0401120718.11fe
ddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||Yep. Taking that a little further, consider horizontal partitioning. Break
up the table into more manageable chunks and use a partitioned view. It
could very well be that only one partition need reindexing.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Quentin Ran" <ab@.who.com> wrote in message
news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
... and if you do feel needing to reindex, run the indexing script, to
reindex some of them at one weekend, the next portion the following weekend
etc. Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with
the index rebuild. Also, in future, consider not doing a rebuild but,
rather using DBCC INDEXDEFRAG so that you can defrag online and allow your
users access to the table.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||Thanks for your help. I did notice the DISK IO moving up,
but not by much. And I haven't seen it move in the past 30
minutes. Do you know what the consequences of killing the
job will be? Will the table get corrupt? How long will it
take to rollback?
Thanks,
LT
quote:
>--Original Message--
>Yep. Taking that a little further, consider horizontal
partitioning. Break
quote:
>up the table into more manageable chunks and use a
partitioned view. It
quote:
>could very well be that only one partition need
reindexing.
quote:
>--
>Tom
>----
--
quote:
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Quentin Ran" <ab@.who.com> wrote in message
>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
>... and if you do feel needing to reindex, run the
indexing script, to
quote:
>reindex some of them at one weekend, the next portion the
following weekend
quote:
>etc. Search for dbcc INDEXDEFRAG in BOL for more detail
about index defrag.
quote:
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
> Run sp_who2 and see if DiskIO is increasing for the
SPID associated with
quote:
>the index rebuild. Also, in future, consider not doing a
rebuild but,
quote:
>rather using DBCC INDEXDEFRAG so that you can defrag
online and allow your
quote:
>users access to the table.
> --
> Tom
> ---
--
quote:
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Laura" <lntaggart@.yahoo.com> wrote in message
>news:4fdf6507.0401120718.11feddb@.posting.google.com...
> Hi,
> I have a very large table (400 Gig) that I am in the
middle of
quote:
> reindexing, it has been 60 hours. I have reindexed this
table before
quote:
> and it ran for 44 hours.
> Does anyone know how to tell if it is still actually
doing anything or
quote:
> if it is stuck?
> Please if anyone has any suggestions, let me know. The
users are
quote:
> complaining about this table not being availiable
during business
quote:|||Killing it is probably "safe". However, all work will be rolled back.
> hours!!!
> Thanks,
> LT
>
Potentially, this can take just as long as it has already been running.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...
Thanks for your help. I did notice the DISK IO moving up,
but not by much. And I haven't seen it move in the past 30
minutes. Do you know what the consequences of killing the
job will be? Will the table get corrupt? How long will it
take to rollback?
Thanks,
LT
quote:
>--Original Message--
>Yep. Taking that a little further, consider horizontal
partitioning. Break
quote:
>up the table into more manageable chunks and use a
partitioned view. It
quote:
>could very well be that only one partition need
reindexing.
quote:
>--
>Tom
>----
--
quote:
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Quentin Ran" <ab@.who.com> wrote in message
>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
>... and if you do feel needing to reindex, run the
indexing script, to
quote:
>reindex some of them at one weekend, the next portion the
following weekend
quote:
>etc. Search for dbcc INDEXDEFRAG in BOL for more detail
about index defrag.
quote:
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
> Run sp_who2 and see if DiskIO is increasing for the
SPID associated with
quote:
>the index rebuild. Also, in future, consider not doing a
rebuild but,
quote:
>rather using DBCC INDEXDEFRAG so that you can defrag
online and allow your
quote:
>users access to the table.
> --
> Tom
> ---
--
quote:
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Laura" <lntaggart@.yahoo.com> wrote in message
>news:4fdf6507.0401120718.11feddb@.posting.google.com...
> Hi,
> I have a very large table (400 Gig) that I am in the
middle of
quote:
> reindexing, it has been 60 hours. I have reindexed this
table before
quote:
> and it ran for 44 hours.
> Does anyone know how to tell if it is still actually
doing anything or
quote:
> if it is stuck?
> Please if anyone has any suggestions, let me know. The
users are
quote:
> complaining about this table not being availiable
during business
quote:|||Good advise ... Because IndexDefrag runs in short fully logged transactions
> hours!!!
> Thanks,
> LT
>
, the log file could get really big!
The script is in books online - it does the defrag by percent fragmentation
- so start at something like 70% and work your way down over a period of day
s...
"Quentin Ran" <ab@.who.com> wrote in message news:ul0s1VS2DHA.556@.TK2MSFTNGP1
1.phx.gbl...
.. and if you do feel needing to reindex, run the indexing script, to reind
ex some of them at one weekend, the next portion the following weekend etc.
Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:eFiDyCS2DHA.34
96@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather u
sing DBCC INDEXDEFRAG so that you can defrag online and allow your users acc
ess to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message news:4fdf6507.0401120718.11fe
ddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||Right, but that's the tradeoff you make for running the defrag online instea
d of rebuilding the index offline.
See the whitepaper at
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
for more details.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message news:e4YBLod2DHA
.3216@.TK2MSFTNGP11.phx.gbl...
Good advise ... Because IndexDefrag runs in short fully logged transactions
, the log file could get really big!
The script is in books online - it does the defrag by percent fragmentation
- so start at something like 70% and work your way down over a period of day
s...
"Quentin Ran" <ab@.who.com> wrote in message news:ul0s1VS2DHA.556@.TK2MSFTNGP1
1.phx.gbl...
.. and if you do feel needing to reindex, run the indexing script, to reind
ex some of them at one weekend, the next portion the following weekend etc.
Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:eFiDyCS2DHA.34
96@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather u
sing DBCC INDEXDEFRAG so that you can defrag online and allow your users acc
ess to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message news:4fdf6507.0401120718.11fe
ddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT|||FYI- I ended up canceling the job, it did a rollback for
about 10 minutes and that's it! Thanks for your help
again.
quote:
>--Original Message--
>Killing it is probably "safe". However, all work will be
rolled back.
quote:
>Potentially, this can take just as long as it has already
been running.
quote:
>--
>Tom
>----
--
quote:
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Laura" <lntaggart@.yahoo.com> wrote in message
>news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...
>Thanks for your help. I did notice the DISK IO moving up,
>but not by much. And I haven't seen it move in the past 30
>minutes. Do you know what the consequences of killing the
>job will be? Will the table get corrupt? How long will it
>take to rollback?
>Thanks,
>LT
>
>partitioning. Break
>partitioned view. It
>reindexing.
-[QUOTE]
>--
>indexing script, to
>following weekend
>about index defrag.
message[QUOTE]
>SPID associated with
>rebuild but,
>online and allow your
-[QUOTE]
>--
>middle of
>table before
>doing anything or
>users are
>during business
>
Reindex HUGE table - production issue
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LTThis is a multi-part message in MIME format.
--=_NextPart_000_0102_01C3D8F6.AE701000
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Run sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather
using DBCC INDEXDEFRAG so that you can defrag online and allow your users
access to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT
--=_NextPart_000_0102_01C3D8F6.AE701000
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Run sp_who2 and see if DiskIO is =increasing for the SPID associated with the index rebuild. Also, in future, =consider not doing a rebuild but, rather using DBCC INDEXDEFRAG so that you can =defrag online and allow your users access to the table.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura" wrote in =message news:4fdf650=7.0401120718.11feddb@.posting.google.com...Hi, I have a very large table (400 Gig) that I am in the middle ofreindexing, it has been 60 hours. I have reindexed this table beforeand it ran for 44 hours.Does anyone know how to tell =if it is still actually doing anything orif it is stuck?Please if =anyone has any suggestions, let me know. The users arecomplaining about this =table not being availiable during =businesshours!!!Thanks,LT
--=_NextPart_000_0102_01C3D8F6.AE701000--|||sp_who2 will show the processes that are building the Index. You may see 4
or more processes at work if you have a box with multiple processes.
Every time you run sp_who2, the values for the processes columns "DiskIO"
and "CPUTime" will be seen to increase. This indicates that the processes
are still running and alive.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
> Hi,
> I have a very large table (400 Gig) that I am in the middle of
> reindexing, it has been 60 hours. I have reindexed this table before
> and it ran for 44 hours.
> Does anyone know how to tell if it is still actually doing anything or
> if it is stuck?
> Please if anyone has any suggestions, let me know. The users are
> complaining about this table not being availiable during business
> hours!!!
> Thanks,
> LT|||This is a multi-part message in MIME format.
--=_NextPart_000_025A_01C3D8F3.77BC2F60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
... and if you do feel needing to reindex, run the indexing script, to =reindex some of them at one weekend, the next portion the following =weekend etc. Search for dbcc INDEXDEFRAG in BOL for more detail about =index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated =with the index rebuild. Also, in future, consider not doing a rebuild =but, rather using DBCC INDEXDEFRAG so that you can defrag online and =allow your users access to the table.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message =news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT --=_NextPart_000_025A_01C3D8F3.77BC2F60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
... and if you do feel needing to =reindex, run the indexing script, to reindex some of them at one weekend, the next =portion the following weekend etc. Search for dbcc INDEXDEFRAG in BOL for more =detail about index defrag.
"Tom Moreau"= wrote in message news:eFiDyCS2DHA.3496=@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is =increasing for the SPID associated with the index rebuild. Also, in future, =consider not doing a rebuild but, rather using DBCC INDEXDEFRAG so that you can =defrag online and allow your users access to the table.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura" wrote =in message news:4fdf650=7.0401120718.11feddb@.posting.google.com...Hi, I have a very large table (400 Gig) that I am in the middle ofreindexing, it has been 60 hours. I have reindexed this table beforeand it ran for 44 hours.Does anyone know how to tell =if it is still actually doing anything orif it is stuck?Please =if anyone has any suggestions, let me know. The users arecomplaining about =this table not being availiable during =businesshours!!!Thanks,LT
--=_NextPart_000_025A_01C3D8F3.77BC2F60--|||This is a multi-part message in MIME format.
--=_NextPart_000_0185_01C3D8FD.7D0E23B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Yep. Taking that a little further, consider horizontal partitioning. Break
up the table into more manageable chunks and use a partitioned view. It
could very well be that only one partition need reindexing.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Quentin Ran" <ab@.who.com> wrote in message
news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
... and if you do feel needing to reindex, run the indexing script, to
reindex some of them at one weekend, the next portion the following weekend
etc. Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with
the index rebuild. Also, in future, consider not doing a rebuild but,
rather using DBCC INDEXDEFRAG so that you can defrag online and allow your
users access to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT
--=_NextPart_000_0185_01C3D8FD.7D0E23B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Yep. Taking that a little =further, consider horizontal partitioning. Break up the table into more manageable =chunks and use a partitioned view. It could very well be that only one =partition need reindexing.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Quentin Ran" =wrote in message news:ul0s1VS2DHA.556@.T=K2MSFTNGP11.phx.gbl...
... and if you do feel needing to =reindex, run the indexing script, to reindex some of them at one weekend, the next =portion the following weekend etc. Search for dbcc INDEXDEFRAG in BOL for more =detail about index defrag.
"Tom Moreau"= wrote in message news:eFiDyCS2DHA.3496=@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is =increasing for the SPID associated with the index rebuild. Also, in future, =consider not doing a rebuild but, rather using DBCC INDEXDEFRAG so that you can =defrag online and allow your users access to the table.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura" wrote =in message news:4fdf650=7.0401120718.11feddb@.posting.google.com...Hi, I have a very large table (400 Gig) that I am in the middle ofreindexing, it has been 60 hours. I have reindexed this table beforeand it ran for 44 hours.Does anyone know how to tell =if it is still actually doing anything orif it is stuck?Please =if anyone has any suggestions, let me know. The users arecomplaining about =this table not being availiable during =businesshours!!!Thanks,LT
--=_NextPart_000_0185_01C3D8FD.7D0E23B0--|||Thanks for your help. I did notice the DISK IO moving up,
but not by much. And I haven't seen it move in the past 30
minutes. Do you know what the consequences of killing the
job will be? Will the table get corrupt? How long will it
take to rollback?
Thanks,
LT
>--Original Message--
>Yep. Taking that a little further, consider horizontal
partitioning. Break
>up the table into more manageable chunks and use a
partitioned view. It
>could very well be that only one partition need
reindexing.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Quentin Ran" <ab@.who.com> wrote in message
>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
>... and if you do feel needing to reindex, run the
indexing script, to
>reindex some of them at one weekend, the next portion the
following weekend
>etc. Search for dbcc INDEXDEFRAG in BOL for more detail
about index defrag.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
> Run sp_who2 and see if DiskIO is increasing for the
SPID associated with
>the index rebuild. Also, in future, consider not doing a
rebuild but,
>rather using DBCC INDEXDEFRAG so that you can defrag
online and allow your
>users access to the table.
> --
> Tom
> ---
--
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Laura" <lntaggart@.yahoo.com> wrote in message
>news:4fdf6507.0401120718.11feddb@.posting.google.com...
> Hi,
> I have a very large table (400 Gig) that I am in the
middle of
> reindexing, it has been 60 hours. I have reindexed this
table before
> and it ran for 44 hours.
> Does anyone know how to tell if it is still actually
doing anything or
> if it is stuck?
> Please if anyone has any suggestions, let me know. The
users are
> complaining about this table not being availiable
during business
> hours!!!
> Thanks,
> LT
>|||This is a multi-part message in MIME format.
--=_NextPart_000_02A9_01C3D909.7F209280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Killing it is probably "safe". However, all work will be rolled back.
Potentially, this can take just as long as it has already been running.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...
Thanks for your help. I did notice the DISK IO moving up,
but not by much. And I haven't seen it move in the past 30
minutes. Do you know what the consequences of killing the
job will be? Will the table get corrupt? How long will it
take to rollback?
Thanks,
LT
>--Original Message--
>Yep. Taking that a little further, consider horizontal
partitioning. Break
>up the table into more manageable chunks and use a
partitioned view. It
>could very well be that only one partition need
reindexing.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Quentin Ran" <ab@.who.com> wrote in message
>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
>... and if you do feel needing to reindex, run the
indexing script, to
>reindex some of them at one weekend, the next portion the
following weekend
>etc. Search for dbcc INDEXDEFRAG in BOL for more detail
about index defrag.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
> Run sp_who2 and see if DiskIO is increasing for the
SPID associated with
>the index rebuild. Also, in future, consider not doing a
rebuild but,
>rather using DBCC INDEXDEFRAG so that you can defrag
online and allow your
>users access to the table.
> --
> Tom
> ---
--
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Laura" <lntaggart@.yahoo.com> wrote in message
>news:4fdf6507.0401120718.11feddb@.posting.google.com...
> Hi,
> I have a very large table (400 Gig) that I am in the
middle of
> reindexing, it has been 60 hours. I have reindexed this
table before
> and it ran for 44 hours.
> Does anyone know how to tell if it is still actually
doing anything or
> if it is stuck?
> Please if anyone has any suggestions, let me know. The
users are
> complaining about this table not being availiable
during business
> hours!!!
> Thanks,
> LT
>
--=_NextPart_000_02A9_01C3D909.7F209280
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Killing it is probably "safe". =However, all work will be rolled back. Potentially, this can take just as =long as it has already been running.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura" wrote in =message news:0c4b01c3d92d$96=cc8f50$a601280a@.phx.gbl...Thanks for your help. I did notice the DISK IO moving up, but not by much. =And I haven't seen it move in the past 30 minutes. Do you know what =the consequences of killing the job will be? Will the table get corrupt? =How long will it take to =rollback?Thanks,LT>--Original Message-->Yep. Taking that a little further, consider =horizontal partitioning. Break>up the table into more manageable =chunks and use a partitioned view. It>could very well be that =only one partition need reindexing.>>-->Tom>>--=----->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Quen=tin Ran" wrote in message>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...>... =and if you do feel needing to reindex, run the indexing script, =to>reindex some of them at one weekend, the next portion the following weekend>etc. Search for dbcc INDEXDEFRAG in BOL for more =detail about index defrag.> "Tom Moreau" = wrote in message>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...> =; Run sp_who2 and see if DiskIO is increasing for the SPID associated with>the index rebuild. Also, in future, consider not doing =a rebuild but,>rather using DBCC INDEXDEFRAG so that you can =defrag online and allow your>users access to the table.>> --> Tom>> -----&g=t; Thomas A. Moreau, BSc, PhD, MCSE, MCDBA> SQL Server MVP> Columnist, SQL Server Professional> =Toronto, ON Canada>http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql>>> "Laura" wrote in message>news:4fdf6507.0401120718.11feddb@.posting.google.com...=> Hi,>> I have a very large table (400 Gig) that I am =in the middle of> reindexing, it has been 60 hours. I have =reindexed this table before> and it ran for 44 hours.>> Does anyone know how to tell if it is still =actually doing anything or> if it is stuck?>> Please if anyone has any suggestions, let =me know. The users are> complaining about this table not being availiable during business> =hours!!!>> Thanks,> LT>
--=_NextPart_000_02A9_01C3D909.7F209280--|||This is a multi-part message in MIME format.
--=_NextPart_000_0035_01C3D9A7.BF40D9D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Good advise ... Because IndexDefrag runs in short fully logged =transactions, the log file could get really big!
The script is in books online - it does the defrag by percent =fragmentation - so start at something like 70% and work your way down =over a period of days...
"Quentin Ran" <ab@.who.com> wrote in message =news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
... and if you do feel needing to reindex, run the indexing script, to =reindex some of them at one weekend, the next portion the following =weekend etc. Search for dbcc INDEXDEFRAG in BOL for more detail about =index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated =with the index rebuild. Also, in future, consider not doing a rebuild =but, rather using DBCC INDEXDEFRAG so that you can defrag online and =allow your users access to the table.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message =news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything =or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT
--=_NextPart_000_0035_01C3D9A7.BF40D9D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Good advise ... Because IndexDefrag =runs in short fully logged transactions, the log file could get really =big!
The script is in books online - it does =the defrag by percent fragmentation - so start at something like 70% and work your =way down over a period of days...
"Quentin Ran" wrote in message news:ul0s1VS2DHA.556@.T=K2MSFTNGP11.phx.gbl...
... and if you do feel needing to =reindex, run the indexing script, to reindex some of them at one weekend, the next =portion the following weekend etc. Search for dbcc INDEXDEFRAG in BOL =for more detail about index defrag.
"Tom Moreau"= wrote in message news:eFiDyCS2DHA.3496=@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is =increasing for the SPID associated with the index rebuild. Also, in =future, consider not doing a rebuild but, rather using DBCC INDEXDEFRAG so =that you can defrag online and allow your users access to the =table.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura" =wrote in message news:4fdf650=7.0401120718.11feddb@.posting.google.com...Hi, I have a very large table (400 Gig) that I am in the middle ofreindexing, it has been 60 hours. I have reindexed this table beforeand it ran for 44 hours.Does anyone know how to =tell if it is still actually doing anything orif it is stuck?Please =if anyone has any suggestions, let me know. The users =arecomplaining about this table not being availiable during businesshours!!!Thanks,LT
--=_NextPart_000_0035_01C3D9A7.BF40D9D0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0079_01C3D9A6.9C013830
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Right, but that's the tradeoff you make for running the defrag online =instead of rebuilding the index offline.
See the whitepaper at http://www.microsoft.com/technet/treeview/default.asp?url=3D/technet/prod=
technol/sql/maintain/optimize/ss2kidbp.asp
for more details.
Regards.
-- Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no =rights.
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message =news:e4YBLod2DHA.3216@.TK2MSFTNGP11.phx.gbl...
Good advise ... Because IndexDefrag runs in short fully logged =transactions, the log file could get really big!
The script is in books online - it does the defrag by percent =fragmentation - so start at something like 70% and work your way down =over a period of days...
"Quentin Ran" <ab@.who.com> wrote in message =news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
... and if you do feel needing to reindex, run the indexing script, =to reindex some of them at one weekend, the next portion the following =weekend etc. Search for dbcc INDEXDEFRAG in BOL for more detail about =index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID =associated with the index rebuild. Also, in future, consider not doing =a rebuild but, rather using DBCC INDEXDEFRAG so that you can defrag =online and allow your users access to the table.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message =news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table =before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing =anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT
--=_NextPart_000_0079_01C3D9A6.9C013830
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Right, but that's the tradeoff you make =for running the defrag online instead of rebuilding the index offline.
See the whitepaper at http://www.microsoft.=com/technet/treeview/default.asp?url=3D/technet/prodtechnol/sql/maintain/=optimize/ss2kidbp.asp
for more details.
Regards.
-- Paul RandalDev =Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no =rights.
"frankm" wrote in message news:e4YBLod2DHA.3216=@.TK2MSFTNGP11.phx.gbl...
Good advise ... Because IndexDefrag =runs in short fully logged transactions, the log file could get really big!
The script is in books online - it =does the defrag by percent fragmentation - so start at something like 70% and =work your way down over a period of days...
"Quentin Ran" wrote in message news:ul0s1VS2DHA.556@.T=K2MSFTNGP11.phx.gbl...
... and if you do feel needing to =reindex, run the indexing script, to reindex some of them at one weekend, the =next portion the following weekend etc. Search for dbcc INDEXDEFRAG =in BOL for more detail about index defrag.
"Tom Moreau"= wrote in message news:eFiDyCS2DHA.3496=@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is =increasing for the SPID associated with the index rebuild. Also, in =future, consider not doing a rebuild but, rather using DBCC INDEXDEFRAG so =that you can defrag online and allow your users access to the table.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, =SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura" =wrote in message news:4fdf650=7.0401120718.11feddb@.posting.google.com...Hi, I have a very large table (400 Gig) that I am in the =middle ofreindexing, it has been 60 hours. I have reindexed this =table beforeand it ran for 44 hours.Does anyone know how to =tell if it is still actually doing anything orif it is =stuck?Please if anyone has any suggestions, let me know. The users =arecomplaining about this table not being availiable during businesshours!!!Thanks,LT
--=_NextPart_000_0079_01C3D9A6.9C013830--|||FYI- I ended up canceling the job, it did a rollback for
about 10 minutes and that's it! Thanks for your help
again.
>--Original Message--
>Killing it is probably "safe". However, all work will be
rolled back.
>Potentially, this can take just as long as it has already
been running.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Laura" <lntaggart@.yahoo.com> wrote in message
>news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...
>Thanks for your help. I did notice the DISK IO moving up,
>but not by much. And I haven't seen it move in the past 30
>minutes. Do you know what the consequences of killing the
>job will be? Will the table get corrupt? How long will it
>take to rollback?
>Thanks,
>LT
>>--Original Message--
>>Yep. Taking that a little further, consider horizontal
>partitioning. Break
>>up the table into more manageable chunks and use a
>partitioned view. It
>>could very well be that only one partition need
>reindexing.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Quentin Ran" <ab@.who.com> wrote in message
>>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
>>... and if you do feel needing to reindex, run the
>indexing script, to
>>reindex some of them at one weekend, the next portion the
>following weekend
>>etc. Search for dbcc INDEXDEFRAG in BOL for more detail
>about index defrag.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in
message
>>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
>> Run sp_who2 and see if DiskIO is increasing for the
>SPID associated with
>>the index rebuild. Also, in future, consider not doing a
>rebuild but,
>>rather using DBCC INDEXDEFRAG so that you can defrag
>online and allow your
>>users access to the table.
>> --
>> Tom
>> ----
-
>--
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com/sql
>>
>> "Laura" <lntaggart@.yahoo.com> wrote in message
>>news:4fdf6507.0401120718.11feddb@.posting.google.com...
>> Hi,
>> I have a very large table (400 Gig) that I am in the
>middle of
>> reindexing, it has been 60 hours. I have reindexed this
>table before
>> and it ran for 44 hours.
>> Does anyone know how to tell if it is still actually
>doing anything or
>> if it is stuck?
>> Please if anyone has any suggestions, let me know. The
>users are
>> complaining about this table not being availiable
>during business
>> hours!!!
>> Thanks,
>> LT
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01D0_01C3DB5E.2C804B40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Makes you wonder what the problem was. Consider DBCC INDEXDEFRAG in the
future.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <anonymous@.discussions.microsoft.com> wrote in message
news:007f01c3db87$88c38a10$a301280a@.phx.gbl...
FYI- I ended up canceling the job, it did a rollback for
about 10 minutes and that's it! Thanks for your help
again.
>--Original Message--
>Killing it is probably "safe". However, all work will be
rolled back.
>Potentially, this can take just as long as it has already
been running.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Laura" <lntaggart@.yahoo.com> wrote in message
>news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...
>Thanks for your help. I did notice the DISK IO moving up,
>but not by much. And I haven't seen it move in the past 30
>minutes. Do you know what the consequences of killing the
>job will be? Will the table get corrupt? How long will it
>take to rollback?
>Thanks,
>LT
>>--Original Message--
>>Yep. Taking that a little further, consider horizontal
>partitioning. Break
>>up the table into more manageable chunks and use a
>partitioned view. It
>>could very well be that only one partition need
>reindexing.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Quentin Ran" <ab@.who.com> wrote in message
>>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
>>... and if you do feel needing to reindex, run the
>indexing script, to
>>reindex some of them at one weekend, the next portion the
>following weekend
>>etc. Search for dbcc INDEXDEFRAG in BOL for more detail
>about index defrag.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in
message
>>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
>> Run sp_who2 and see if DiskIO is increasing for the
>SPID associated with
>>the index rebuild. Also, in future, consider not doing a
>rebuild but,
>>rather using DBCC INDEXDEFRAG so that you can defrag
>online and allow your
>>users access to the table.
>> --
>> Tom
>> ----
-
>--
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com/sql
>>
>> "Laura" <lntaggart@.yahoo.com> wrote in message
>>news:4fdf6507.0401120718.11feddb@.posting.google.com...
>> Hi,
>> I have a very large table (400 Gig) that I am in the
>middle of
>> reindexing, it has been 60 hours. I have reindexed this
>table before
>> and it ran for 44 hours.
>> Does anyone know how to tell if it is still actually
>doing anything or
>> if it is stuck?
>> Please if anyone has any suggestions, let me know. The
>users are
>> complaining about this table not being availiable
>during business
>> hours!!!
>> Thanks,
>> LT
>
--=_NextPart_000_01D0_01C3DB5E.2C804B40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Makes you wonder what the problem =was. Consider DBCC INDEXDEFRAG in the future.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura" wrote in message news:007f01c3db87$88=c38a10$a301280a@.phx.gbl...FYI- I ended up canceling the job, it did a rollback for about 10 minutes =and that's it! Thanks for your help =again.>--Original Message-->Killing it is probably "safe". However, all =work will be rolled back.>Potentially, this can take just as long as it =has already been running.>>-->Tom>>--=----->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Laur=a" =wrote in message>news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...>Than=ks for your help. I did notice the DISK IO moving up,>but not by much. =And I haven't seen it move in the past 30>minutes. Do you know =what the consequences of killing the>job will be? Will the table get =corrupt? How long will it>take to rollback?>>Thanks,>LT>>--Origin=al Message-->Yep. Taking that a little further, =consider horizontal>partitioning. Break>up the table into =more manageable chunks and use a>partitioned view. =It>could very well be that only one partition need>reindexing.>>-->Tom>&=gt;>----->-->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Quentin Ran" wrote in message>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...>&g=t;... and if you do feel needing to reindex, run the>indexing script, to>reindex some of them at one weekend, the next portion the>following weekend>etc. Search for dbcc =INDEXDEFRAG in BOL for more detail>about index defrag.> "Tom =Moreau" = wrote in message>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...&=gt;> Run sp_who2 and see if DiskIO is increasing for the>SPID =associated with>the index rebuild. Also, in future, consider not =doing a>rebuild but,>rather using DBCC INDEXDEFRAG so that =you can defrag>online and allow your>users access to the table.>> --> Tom>> ----->--=--> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA> SQL Server MVP> Columnist, SQL Server =Professional> Toronto, ON Canada>http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql>>> "Laura" wrote in message>news:4fdf6507.0401120718.11feddb@.posting.google.com...=> Hi,>> I have a very large table (400 Gig) =that I am in the>middle of> reindexing, it has been 60 =hours. I have reindexed this>table before> and it ran for =44 hours.>> Does anyone know how to tell if it =is still actually>doing anything or> if it is stuck?>> Please if anyone has any =suggestions, let me know. The>users are> complaining about this =table not being availiable>during business> hours!!!>> Thanks,> LT>>
--=_NextPart_000_01D0_01C3DB5E.2C804B40--
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LTThis is a multi-part message in MIME format.
--=_NextPart_000_0102_01C3D8F6.AE701000
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Run sp_who2 and see if DiskIO is increasing for the SPID associated with the
index rebuild. Also, in future, consider not doing a rebuild but, rather
using DBCC INDEXDEFRAG so that you can defrag online and allow your users
access to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT
--=_NextPart_000_0102_01C3D8F6.AE701000
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Run sp_who2 and see if DiskIO is =increasing for the SPID associated with the index rebuild. Also, in future, =consider not doing a rebuild but, rather using DBCC INDEXDEFRAG so that you can =defrag online and allow your users access to the table.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura"
--=_NextPart_000_0102_01C3D8F6.AE701000--|||sp_who2 will show the processes that are building the Index. You may see 4
or more processes at work if you have a box with multiple processes.
Every time you run sp_who2, the values for the processes columns "DiskIO"
and "CPUTime" will be seen to increase. This indicates that the processes
are still running and alive.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
> Hi,
> I have a very large table (400 Gig) that I am in the middle of
> reindexing, it has been 60 hours. I have reindexed this table before
> and it ran for 44 hours.
> Does anyone know how to tell if it is still actually doing anything or
> if it is stuck?
> Please if anyone has any suggestions, let me know. The users are
> complaining about this table not being availiable during business
> hours!!!
> Thanks,
> LT|||This is a multi-part message in MIME format.
--=_NextPart_000_025A_01C3D8F3.77BC2F60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
... and if you do feel needing to reindex, run the indexing script, to =reindex some of them at one weekend, the next portion the following =weekend etc. Search for dbcc INDEXDEFRAG in BOL for more detail about =index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated =with the index rebuild. Also, in future, consider not doing a rebuild =but, rather using DBCC INDEXDEFRAG so that you can defrag online and =allow your users access to the table.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message =news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT --=_NextPart_000_025A_01C3D8F3.77BC2F60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
... and if you do feel needing to =reindex, run the indexing script, to reindex some of them at one weekend, the next =portion the following weekend etc. Search for dbcc INDEXDEFRAG in BOL for more =detail about index defrag.
"Tom Moreau"
Run sp_who2 and see if DiskIO is =increasing for the SPID associated with the index rebuild. Also, in future, =consider not doing a rebuild but, rather using DBCC INDEXDEFRAG so that you can =defrag online and allow your users access to the table.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura"
--=_NextPart_000_025A_01C3D8F3.77BC2F60--|||This is a multi-part message in MIME format.
--=_NextPart_000_0185_01C3D8FD.7D0E23B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Yep. Taking that a little further, consider horizontal partitioning. Break
up the table into more manageable chunks and use a partitioned view. It
could very well be that only one partition need reindexing.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Quentin Ran" <ab@.who.com> wrote in message
news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
... and if you do feel needing to reindex, run the indexing script, to
reindex some of them at one weekend, the next portion the following weekend
etc. Search for dbcc INDEXDEFRAG in BOL for more detail about index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated with
the index rebuild. Also, in future, consider not doing a rebuild but,
rather using DBCC INDEXDEFRAG so that you can defrag online and allow your
users access to the table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT
--=_NextPart_000_0185_01C3D8FD.7D0E23B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Yep. Taking that a little =further, consider horizontal partitioning. Break up the table into more manageable =chunks and use a partitioned view. It could very well be that only one =partition need reindexing.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Quentin Ran" =wrote in message news:ul0s1VS2DHA.556@.T=K2MSFTNGP11.phx.gbl...
... and if you do feel needing to =reindex, run the indexing script, to reindex some of them at one weekend, the next =portion the following weekend etc. Search for dbcc INDEXDEFRAG in BOL for more =detail about index defrag.
"Tom Moreau"
Run sp_who2 and see if DiskIO is =increasing for the SPID associated with the index rebuild. Also, in future, =consider not doing a rebuild but, rather using DBCC INDEXDEFRAG so that you can =defrag online and allow your users access to the table.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura"
--=_NextPart_000_0185_01C3D8FD.7D0E23B0--|||Thanks for your help. I did notice the DISK IO moving up,
but not by much. And I haven't seen it move in the past 30
minutes. Do you know what the consequences of killing the
job will be? Will the table get corrupt? How long will it
take to rollback?
Thanks,
LT
>--Original Message--
>Yep. Taking that a little further, consider horizontal
partitioning. Break
>up the table into more manageable chunks and use a
partitioned view. It
>could very well be that only one partition need
reindexing.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Quentin Ran" <ab@.who.com> wrote in message
>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
>... and if you do feel needing to reindex, run the
indexing script, to
>reindex some of them at one weekend, the next portion the
following weekend
>etc. Search for dbcc INDEXDEFRAG in BOL for more detail
about index defrag.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
> Run sp_who2 and see if DiskIO is increasing for the
SPID associated with
>the index rebuild. Also, in future, consider not doing a
rebuild but,
>rather using DBCC INDEXDEFRAG so that you can defrag
online and allow your
>users access to the table.
> --
> Tom
> ---
--
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Laura" <lntaggart@.yahoo.com> wrote in message
>news:4fdf6507.0401120718.11feddb@.posting.google.com...
> Hi,
> I have a very large table (400 Gig) that I am in the
middle of
> reindexing, it has been 60 hours. I have reindexed this
table before
> and it ran for 44 hours.
> Does anyone know how to tell if it is still actually
doing anything or
> if it is stuck?
> Please if anyone has any suggestions, let me know. The
users are
> complaining about this table not being availiable
during business
> hours!!!
> Thanks,
> LT
>|||This is a multi-part message in MIME format.
--=_NextPart_000_02A9_01C3D909.7F209280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Killing it is probably "safe". However, all work will be rolled back.
Potentially, this can take just as long as it has already been running.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message
news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...
Thanks for your help. I did notice the DISK IO moving up,
but not by much. And I haven't seen it move in the past 30
minutes. Do you know what the consequences of killing the
job will be? Will the table get corrupt? How long will it
take to rollback?
Thanks,
LT
>--Original Message--
>Yep. Taking that a little further, consider horizontal
partitioning. Break
>up the table into more manageable chunks and use a
partitioned view. It
>could very well be that only one partition need
reindexing.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Quentin Ran" <ab@.who.com> wrote in message
>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
>... and if you do feel needing to reindex, run the
indexing script, to
>reindex some of them at one weekend, the next portion the
following weekend
>etc. Search for dbcc INDEXDEFRAG in BOL for more detail
about index defrag.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
> Run sp_who2 and see if DiskIO is increasing for the
SPID associated with
>the index rebuild. Also, in future, consider not doing a
rebuild but,
>rather using DBCC INDEXDEFRAG so that you can defrag
online and allow your
>users access to the table.
> --
> Tom
> ---
--
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Laura" <lntaggart@.yahoo.com> wrote in message
>news:4fdf6507.0401120718.11feddb@.posting.google.com...
> Hi,
> I have a very large table (400 Gig) that I am in the
middle of
> reindexing, it has been 60 hours. I have reindexed this
table before
> and it ran for 44 hours.
> Does anyone know how to tell if it is still actually
doing anything or
> if it is stuck?
> Please if anyone has any suggestions, let me know. The
users are
> complaining about this table not being availiable
during business
> hours!!!
> Thanks,
> LT
>
--=_NextPart_000_02A9_01C3D909.7F209280
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Killing it is probably "safe". =However, all work will be rolled back. Potentially, this can take just as =long as it has already been running.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura"
--=_NextPart_000_02A9_01C3D909.7F209280--|||This is a multi-part message in MIME format.
--=_NextPart_000_0035_01C3D9A7.BF40D9D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Good advise ... Because IndexDefrag runs in short fully logged =transactions, the log file could get really big!
The script is in books online - it does the defrag by percent =fragmentation - so start at something like 70% and work your way down =over a period of days...
"Quentin Ran" <ab@.who.com> wrote in message =news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
... and if you do feel needing to reindex, run the indexing script, to =reindex some of them at one weekend, the next portion the following =weekend etc. Search for dbcc INDEXDEFRAG in BOL for more detail about =index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID associated =with the index rebuild. Also, in future, consider not doing a rebuild =but, rather using DBCC INDEXDEFRAG so that you can defrag online and =allow your users access to the table.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message =news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing anything =or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT
--=_NextPart_000_0035_01C3D9A7.BF40D9D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Good advise ... Because IndexDefrag =runs in short fully logged transactions, the log file could get really =big!
The script is in books online - it does =the defrag by percent fragmentation - so start at something like 70% and work your =way down over a period of days...
"Quentin Ran" wrote in message news:ul0s1VS2DHA.556@.T=K2MSFTNGP11.phx.gbl...
... and if you do feel needing to =reindex, run the indexing script, to reindex some of them at one weekend, the next =portion the following weekend etc. Search for dbcc INDEXDEFRAG in BOL =for more detail about index defrag.
"Tom Moreau"
Run sp_who2 and see if DiskIO is =increasing for the SPID associated with the index rebuild. Also, in =future, consider not doing a rebuild but, rather using DBCC INDEXDEFRAG so =that you can defrag online and allow your users access to the =table.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura"
--=_NextPart_000_0035_01C3D9A7.BF40D9D0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0079_01C3D9A6.9C013830
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Right, but that's the tradeoff you make for running the defrag online =instead of rebuilding the index offline.
See the whitepaper at http://www.microsoft.com/technet/treeview/default.asp?url=3D/technet/prod=
technol/sql/maintain/optimize/ss2kidbp.asp
for more details.
Regards.
-- Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no =rights.
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message =news:e4YBLod2DHA.3216@.TK2MSFTNGP11.phx.gbl...
Good advise ... Because IndexDefrag runs in short fully logged =transactions, the log file could get really big!
The script is in books online - it does the defrag by percent =fragmentation - so start at something like 70% and work your way down =over a period of days...
"Quentin Ran" <ab@.who.com> wrote in message =news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
... and if you do feel needing to reindex, run the indexing script, =to reindex some of them at one weekend, the next portion the following =weekend etc. Search for dbcc INDEXDEFRAG in BOL for more detail about =index defrag.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
Run sp_who2 and see if DiskIO is increasing for the SPID =associated with the index rebuild. Also, in future, consider not doing =a rebuild but, rather using DBCC INDEXDEFRAG so that you can defrag =online and allow your users access to the table.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <lntaggart@.yahoo.com> wrote in message =news:4fdf6507.0401120718.11feddb@.posting.google.com...
Hi,
I have a very large table (400 Gig) that I am in the middle of
reindexing, it has been 60 hours. I have reindexed this table =before
and it ran for 44 hours.
Does anyone know how to tell if it is still actually doing =anything or
if it is stuck?
Please if anyone has any suggestions, let me know. The users are
complaining about this table not being availiable during business
hours!!!
Thanks,
LT
--=_NextPart_000_0079_01C3D9A6.9C013830
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Right, but that's the tradeoff you make =for running the defrag online instead of rebuilding the index offline.
See the whitepaper at http://www.microsoft.=com/technet/treeview/default.asp?url=3D/technet/prodtechnol/sql/maintain/=optimize/ss2kidbp.asp
for more details.
Regards.
-- Paul RandalDev =Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no =rights.
"frankm"
Good advise ... Because IndexDefrag =runs in short fully logged transactions, the log file could get really big!
The script is in books online - it =does the defrag by percent fragmentation - so start at something like 70% and =work your way down over a period of days...
"Quentin Ran" wrote in message news:ul0s1VS2DHA.556@.T=K2MSFTNGP11.phx.gbl...
... and if you do feel needing to =reindex, run the indexing script, to reindex some of them at one weekend, the =next portion the following weekend etc. Search for dbcc INDEXDEFRAG =in BOL for more detail about index defrag.
"Tom Moreau"
Run sp_who2 and see if DiskIO is =increasing for the SPID associated with the index rebuild. Also, in =future, consider not doing a rebuild but, rather using DBCC INDEXDEFRAG so =that you can defrag online and allow your users access to the table.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, =SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura"
--=_NextPart_000_0079_01C3D9A6.9C013830--|||FYI- I ended up canceling the job, it did a rollback for
about 10 minutes and that's it! Thanks for your help
again.
>--Original Message--
>Killing it is probably "safe". However, all work will be
rolled back.
>Potentially, this can take just as long as it has already
been running.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Laura" <lntaggart@.yahoo.com> wrote in message
>news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...
>Thanks for your help. I did notice the DISK IO moving up,
>but not by much. And I haven't seen it move in the past 30
>minutes. Do you know what the consequences of killing the
>job will be? Will the table get corrupt? How long will it
>take to rollback?
>Thanks,
>LT
>>--Original Message--
>>Yep. Taking that a little further, consider horizontal
>partitioning. Break
>>up the table into more manageable chunks and use a
>partitioned view. It
>>could very well be that only one partition need
>reindexing.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Quentin Ran" <ab@.who.com> wrote in message
>>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
>>... and if you do feel needing to reindex, run the
>indexing script, to
>>reindex some of them at one weekend, the next portion the
>following weekend
>>etc. Search for dbcc INDEXDEFRAG in BOL for more detail
>about index defrag.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in
message
>>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
>> Run sp_who2 and see if DiskIO is increasing for the
>SPID associated with
>>the index rebuild. Also, in future, consider not doing a
>rebuild but,
>>rather using DBCC INDEXDEFRAG so that you can defrag
>online and allow your
>>users access to the table.
>> --
>> Tom
>> ----
-
>--
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com/sql
>>
>> "Laura" <lntaggart@.yahoo.com> wrote in message
>>news:4fdf6507.0401120718.11feddb@.posting.google.com...
>> Hi,
>> I have a very large table (400 Gig) that I am in the
>middle of
>> reindexing, it has been 60 hours. I have reindexed this
>table before
>> and it ran for 44 hours.
>> Does anyone know how to tell if it is still actually
>doing anything or
>> if it is stuck?
>> Please if anyone has any suggestions, let me know. The
>users are
>> complaining about this table not being availiable
>during business
>> hours!!!
>> Thanks,
>> LT
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01D0_01C3DB5E.2C804B40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Makes you wonder what the problem was. Consider DBCC INDEXDEFRAG in the
future.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura" <anonymous@.discussions.microsoft.com> wrote in message
news:007f01c3db87$88c38a10$a301280a@.phx.gbl...
FYI- I ended up canceling the job, it did a rollback for
about 10 minutes and that's it! Thanks for your help
again.
>--Original Message--
>Killing it is probably "safe". However, all work will be
rolled back.
>Potentially, this can take just as long as it has already
been running.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Laura" <lntaggart@.yahoo.com> wrote in message
>news:0c4b01c3d92d$96cc8f50$a601280a@.phx.gbl...
>Thanks for your help. I did notice the DISK IO moving up,
>but not by much. And I haven't seen it move in the past 30
>minutes. Do you know what the consequences of killing the
>job will be? Will the table get corrupt? How long will it
>take to rollback?
>Thanks,
>LT
>>--Original Message--
>>Yep. Taking that a little further, consider horizontal
>partitioning. Break
>>up the table into more manageable chunks and use a
>partitioned view. It
>>could very well be that only one partition need
>reindexing.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Quentin Ran" <ab@.who.com> wrote in message
>>news:ul0s1VS2DHA.556@.TK2MSFTNGP11.phx.gbl...
>>... and if you do feel needing to reindex, run the
>indexing script, to
>>reindex some of them at one weekend, the next portion the
>following weekend
>>etc. Search for dbcc INDEXDEFRAG in BOL for more detail
>about index defrag.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in
message
>>news:eFiDyCS2DHA.3496@.TK2MSFTNGP11.phx.gbl...
>> Run sp_who2 and see if DiskIO is increasing for the
>SPID associated with
>>the index rebuild. Also, in future, consider not doing a
>rebuild but,
>>rather using DBCC INDEXDEFRAG so that you can defrag
>online and allow your
>>users access to the table.
>> --
>> Tom
>> ----
-
>--
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com/sql
>>
>> "Laura" <lntaggart@.yahoo.com> wrote in message
>>news:4fdf6507.0401120718.11feddb@.posting.google.com...
>> Hi,
>> I have a very large table (400 Gig) that I am in the
>middle of
>> reindexing, it has been 60 hours. I have reindexed this
>table before
>> and it ran for 44 hours.
>> Does anyone know how to tell if it is still actually
>doing anything or
>> if it is stuck?
>> Please if anyone has any suggestions, let me know. The
>users are
>> complaining about this table not being availiable
>during business
>> hours!!!
>> Thanks,
>> LT
>
--=_NextPart_000_01D0_01C3DB5E.2C804B40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Makes you wonder what the problem =was. Consider DBCC INDEXDEFRAG in the future.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Laura" wrote in message news:007f01c3db87$88=c38a10$a301280a@.phx.gbl...FYI- I ended up canceling the job, it did a rollback for about 10 minutes =and that's it! Thanks for your help =again.>--Original Message-->Killing it is probably "safe". However, all =work will be rolled back.>Potentially, this can take just as long as it =has already been running.>>-->Tom>>--=----->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Laur=a"
--=_NextPart_000_01D0_01C3DB5E.2C804B40--
Subscribe to:
Posts (Atom)