REINDEXING?

I have a big table with more than 7583117
records. The table is updated every day during the End of the day activity.

There is a job running a query on this table for updating another table.

The table is not reindexed and the job running freezes on the EXECUTING state many times is it because of not reindexing the indexes?

Thanks in advance

Jacx

Hi Jacx,

What dou you see in SQL Server Error Log?

In the moment of this job running you monitoring the locks?

Regards,

|||

I checked the error log nothing about that particular job because it is not cancelled because of an error but it stays in the executing state.

|||When you say it "freezes" what do you mean, where are you seeing it "freeze"? Have you run the Profiler to see what is reallly going on?

How are you updating the table and selecting the records? Are you doing:

INSERT INTO XXX
SELECT ......

If so, that is probably expanding tempdb when you see it "freeze" because it writtes the entire select to tempdb and then inserts it into the target table.

Without seeing exactly how you are "updating" the table, it is hard to say what it is doing. The index being corrupt and needing reindexing is not normally a problem in MS SQL.

reindexing, rebuilding, etc

hi, new to sql server (2000)..in my previous database, we had to do
maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
database is relatively small (8G)but the reindexing in sql server is done in
minutes, whereas in our old system it used to take up to 4 hrs...Is sql
server that fast or am I doing something wrong?
also, what routines are recommended for regular maintenance and should I do
individually or create a plan?
Thanks,
GerryUse the code found in Books Online, DBCC SHOWCONTIG for reindexing, and read
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
first.
As for the other things:
Backup schedule and types is determined by the owner of the system.
I prefer to do DBCC CHECKDB as often as I can. I prefer to do it as often as
I do db backup. This
was, if I get a corruption, I can do a log backup, restore the most recent d
b backup and all
subsequent log backups and the corruption probably not re-introduced by rest
oring the log backup.
Your mileage will vary, so just put in some common sense into planning these
things.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:8295C0D3-477A-454C-92E9-51EE95D9E435@.microsoft.com...
> hi, new to sql server (2000)..in my previous database, we had to do
> maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> database is relatively small (8G)but the reindexing in sql server is done
in
> minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> server that fast or am I doing something wrong?
> also, what routines are recommended for regular maintenance and should I d
o
> individually or create a plan?
> Thanks,
> Gerry|||Thank you very much.
"Tibor Karaszi" wrote:

> Use the code found in Books Online, DBCC SHOWCONTIG for reindexing, and re
ad
> http://www.microsoft.com/technet/pr...ver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:8295C0D3-477A-454C-92E9-51EE95D9E435@.microsoft.com...
>|||Gerry M wrote:
> hi, new to sql server (2000)..in my previous database, we had to do
> maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> database is relatively small (8G)but the reindexing in sql server is done
in
> minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> server that fast or am I doing something wrong?
> also, what routines are recommended for regular maintenance and should I d
o
> individually or create a plan?
> Thanks,
> Gerry
Hi Gerry... I have a script that I run that checks the fragmentation of
all my indexes, and rebuilds those that are badly fragmented. My site
is currently "in limbo", but you can obtain the script from Google's
cache. Search for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded"
Tracy McKibben
MCDBA
http://www.realsqlguy.com

reindexing, rebuilding, etc

hi, new to sql server (2000)..in my previous database, we had to do
maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
database is relatively small (8G)but the reindexing in sql server is done in
minutes, whereas in our old system it used to take up to 4 hrs...Is sql
server that fast or am I doing something wrong?
also, what routines are recommended for regular maintenance and should I do
individually or create a plan?
Thanks,
GerryUse the code found in Books Online, DBCC SHOWCONTIG for reindexing, and read
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx first.
As for the other things:
Backup schedule and types is determined by the owner of the system.
I prefer to do DBCC CHECKDB as often as I can. I prefer to do it as often as I do db backup. This
was, if I get a corruption, I can do a log backup, restore the most recent db backup and all
subsequent log backups and the corruption probably not re-introduced by restoring the log backup.
Your mileage will vary, so just put in some common sense into planning these things.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:8295C0D3-477A-454C-92E9-51EE95D9E435@.microsoft.com...
> hi, new to sql server (2000)..in my previous database, we had to do
> maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> database is relatively small (8G)but the reindexing in sql server is done in
> minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> server that fast or am I doing something wrong?
> also, what routines are recommended for regular maintenance and should I do
> individually or create a plan?
> Thanks,
> Gerry|||Thank you very much.
"Tibor Karaszi" wrote:
> Use the code found in Books Online, DBCC SHOWCONTIG for reindexing, and read
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx first.
> As for the other things:
> Backup schedule and types is determined by the owner of the system.
> I prefer to do DBCC CHECKDB as often as I can. I prefer to do it as often as I do db backup. This
> was, if I get a corruption, I can do a log backup, restore the most recent db backup and all
> subsequent log backups and the corruption probably not re-introduced by restoring the log backup.
> Your mileage will vary, so just put in some common sense into planning these things.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:8295C0D3-477A-454C-92E9-51EE95D9E435@.microsoft.com...
> > hi, new to sql server (2000)..in my previous database, we had to do
> > maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> > database is relatively small (8G)but the reindexing in sql server is done in
> > minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> > server that fast or am I doing something wrong?
> >
> > also, what routines are recommended for regular maintenance and should I do
> > individually or create a plan?
> >
> > Thanks,
> > Gerry
>|||Gerry M wrote:
> hi, new to sql server (2000)..in my previous database, we had to do
> maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> database is relatively small (8G)but the reindexing in sql server is done in
> minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> server that fast or am I doing something wrong?
> also, what routines are recommended for regular maintenance and should I do
> individually or create a plan?
> Thanks,
> Gerry
Hi Gerry... I have a script that I run that checks the fragmentation of
all my indexes, and rebuilds those that are badly fragmented. My site
is currently "in limbo", but you can obtain the script from Google's
cache. Search for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded"
Tracy McKibben
MCDBA
http://www.realsqlguy.com

ReIndexing while using replication Important

Does anyone know of a good set of guidelines to follow, after running DBCC
Showcontig on a publisher of replication and DBCC reindexing is called for?
What are the pitfalls and things to watch out for? I can't afford to make a
mistake since there are three servers involved and it's in production.
I can do the reindexing during night time hours. I've read a lot of material
but haven't found "replication considerations" and have not found anything
other than generalities when explaining how do analyze the Index fragmenting
statistics.
Matthew Mark
For merge replication it helps to reindex or defragment the msmerge tables.
You need to stop the merge agents which you are doing this. Ideally you
would stop all users while you are doing the reindexing.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:15C56BD5-DED8-41D2-9C16-9C87B664751C@.microsoft.com...
> Does anyone know of a good set of guidelines to follow, after running DBCC
> Showcontig on a publisher of replication and DBCC reindexing is called
> for?
> What are the pitfalls and things to watch out for? I can't afford to make
> a
> mistake since there are three servers involved and it's in production.
> I can do the reindexing during night time hours. I've read a lot of
> material
> but haven't found "replication considerations" and have not found anything
> other than generalities when explaining how do analyze the Index
> fragmenting
> statistics.
> --
> Matthew Mark
>
|||Here's what I understand from this.
Since I couldn't find a MSMerge table I assume you mean to reindex the
Article (tables) in MSarticles from the publisher.
If so, how does this fall into the procedure. For example, based on what I
know now, here's how I would do this.
1. Turn all merge agents off.
2. Put DB in restricted mode.
3. Run DBCC reindex, looping through all tables in this database.
4. Put dB back in Full access mode.
5. Turn merge agents back on.
So, how does your suggesting fit in or modify this plan?
Does the reindexing naturally replicate to the subscribers? or ?
thanks
Matthew Mark
"Hilary Cotter" wrote:

> For merge replication it helps to reindex or defragment the msmerge tables.
> You need to stop the merge agents which you are doing this. Ideally you
> would stop all users while you are doing the reindexing.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
> news:15C56BD5-DED8-41D2-9C16-9C87B664751C@.microsoft.com...
>
>
|||reindexing does not propagate to the subscribers, I meant that you should
reinindex msmerge_contents, and msmerge_tombstone.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:2CB7AB6E-8D3B-48D8-A092-E55A8A25540A@.microsoft.com...[vbcol=seagreen]
> Here's what I understand from this.
> Since I couldn't find a MSMerge table I assume you mean to reindex the
> Article (tables) in MSarticles from the publisher.
> If so, how does this fall into the procedure. For example, based on what I
> know now, here's how I would do this.
> 1. Turn all merge agents off.
> 2. Put DB in restricted mode.
> 3. Run DBCC reindex, looping through all tables in this database.
> 4. Put dB back in Full access mode.
> 5. Turn merge agents back on.
> So, how does your suggesting fit in or modify this plan?
> Does the reindexing naturally replicate to the subscribers? or ?
>
> thanks
>
> --
> Matthew Mark
>
> "Hilary Cotter" wrote:
|||When I do reindex, and loop through the tables, doesn't it include these
tabels too?
Matthew Mark
"Hilary Cotter" wrote:

> reindexing does not propagate to the subscribers, I meant that you should
> reinindex msmerge_contents, and msmerge_tombstone.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
> news:2CB7AB6E-8D3B-48D8-A092-E55A8A25540A@.microsoft.com...
>
>
|||Matthew,
this partly depends on what you are reindexing - system tables or user
tables. Generally speaking though, I ensure synchronization isn't occurring
while reindexing takes place, otherwise there tend to be job failures
because of blocking.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
I have never run reindexing on a replicated db. I have ,though, run a script
that runs the reindexing on all user tables. Are these MSarticles etc tables
system tables? I'd assume they are since the replication process creates it.
To avoid asking you a lot of specific questions, is there a comprehensive
guide I can read? The microsoft info online is usually good, only if you
already know the pitfalls. I don't.
If I make a mistake here because I didn't do my homework..., well, I'm sure
you understand. We don't replicate on our development server, so I can't
exactly practice ahead of time.
Matthew Mark
"Paul Ibison" wrote:

> Matthew,
> this partly depends on what you are reindexing - system tables or user
> tables. Generally speaking though, I ensure synchronization isn't occurring
> while reindexing takes place, otherwise there tend to be job failures
> because of blocking.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||I am unsure what you mean here. Can you clarify it? I normally issue the
following dbcc dbreindex('msmerge_contents')
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:9038947F-DAA7-476C-92ED-729E905E77E3@.microsoft.com...[vbcol=seagreen]
> When I do reindex, and loop through the tables, doesn't it include these
> tabels too?
> --
> Matthew Mark
>
> "Hilary Cotter" wrote:
|||Matthew,
I don't know of any guide about reindexing and replication. However I'm not
too sure there's much to it. If you know the difference between DBCC
DBREINDEX and DBCC INDEXDEFRAG, understand locking and blocking and can see
what the agents require access to in order to work, these are the basic
facts. All I can sey is that a simple guideline is to run DBCC DBREINDEX not
overly often and when you do, try to ensure that synchronization isn't
occurring.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables?
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***
Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/de...es_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables?
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default...b;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables?
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default...b;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/d...>
_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/defaul...kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as
maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you sho
uld use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.
gbl...
> If you're using a non-named instance and are running SP4, you can use a -s
upportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named
instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/defaul...kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job wit
h two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you t
he script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2M
SFTNGP15.phx.gbl...
>

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.developersdex.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>>I need to reindex a table with a computed column. The column is not
>> included in any indexes, but when I run the DBCC it crashes with the
>> following error:
>> DBCC failed because the following SET options have incorrect settings:
>> 'QUOTED_IDENTIFIER'.
>> Any ideas on how I can reindex these tables'
>> Thanks!
>> Richard
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>

reindexing table with blob data type

Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advanceUnfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > Unfortunately, the only way to defrag tables with BLOB columns is to
> rebuild
> > the table.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "james" <kush@.brandes.com> wrote in message
> > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi! Is there a way to defrag image data type column in a table? I use
dbcc
> > dbreindex on the table itself during maintenance period, but I don't
know
> if
> > that is sufficient to defrag those pages which actually stores image
data
> > type. We use third party application and they designed to put some
> > transaction information on image data type column. Now we are
experiencing
> > gradual degradation on performance and wondering if it is related to
image
> > data type and its growth.
> >
> > Sql server 2000 sp3a
> >
> > thanks in advance
> >
> >
>|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > Unfortunately, the only way to defrag tables with BLOB columns is to
> rebuild
> > the table.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "james" <kush@.brandes.com> wrote in message
> > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi! Is there a way to defrag image data type column in a table? I use
dbcc
> > dbreindex on the table itself during maintenance period, but I don't
know
> if
> > that is sufficient to defrag those pages which actually stores image
data
> > type. We use third party application and they designed to put some
> > transaction information on image data type column. Now we are
experiencing
> > gradual degradation on performance and wondering if it is related to
image
> > data type and its growth.
> >
> > Sql server 2000 sp3a
> >
> > thanks in advance
> >
> >
>|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> > Just to clarify, what Tom means is to bcp out/in the data, not to run
DBCC
> > DBREINDEX on the table.
> >
> > We have this fixed in SQL Server 2005.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > > Unfortunately, the only way to defrag tables with BLOB columns is to
> > rebuild
> > > the table.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Columnist, SQL Server Professional
> > > Toronto, ON Canada
> > > www.pinpub.com
> > > .
> > > "james" <kush@.brandes.com> wrote in message
> > > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > > Hi! Is there a way to defrag image data type column in a table? I use
> dbcc
> > > dbreindex on the table itself during maintenance period, but I don't
> know
> > if
> > > that is sufficient to defrag those pages which actually stores image
> data
> > > type. We use third party application and they designed to put some
> > > transaction information on image data type column. Now we are
> experiencing
> > > gradual degradation on performance and wondering if it is related to
> image
> > > data type and its growth.
> > >
> > > Sql server 2000 sp3a
> > >
> > > thanks in advance
> > >
> > >
> >
> >
>|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> > Unfortunately, the only way to defrag tables with BLOB columns is to
> rebuild
> > the table.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "james" <kush@.brandes.com> wrote in message
> > news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi! Is there a way to defrag image data type column in a table? I use
dbcc
> > dbreindex on the table itself during maintenance period, but I don't
know
> if
> > that is sufficient to defrag those pages which actually stores image
data
> > type. We use third party application and they designed to put some
> > transaction information on image data type column. Now we are
experiencing
> > gradual degradation on performance and wondering if it is related to
image
> > data type and its growth.
> >
> > Sql server 2000 sp3a
> >
> > thanks in advance
> >
> >
>

reindexing table with blob data type

Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance
Unfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance
|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>
|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image
>
|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image
>
|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
DBCC
> rights.
> dbcc
> know
> data
> experiencing
> image
>
|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas

"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image
>

reindexing table with blob data type

Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advanceUnfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
DBCC[vbcol=seagreen]
> rights.
> dbcc
> know
> data
> experiencing
> image
>|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>

reindexing slow after installing SQL 2K SP4?

I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from SP3
to SP4, my re-indexing job takes 3-4 times longer than before. The amount
of data is about the same. Nothing else within SQL has changed. I am
running SQL on the other node, but the hardware is identical and I checked
the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
Has anyone experienced this after installing Service Pack 4?There is an issue with AWE in SP4 - it stops using it. Get the post-SP4 hot
fix:
http://support.microsoft.com/kb/899761
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from SP3
to SP4, my re-indexing job takes 3-4 times longer than before. The amount
of data is about the same. Nothing else within SQL has changed. I am
running SQL on the other node, but the hardware is identical and I checked
the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
Has anyone experienced this after installing Service Pack 4?|||Thanks. I actually forgot to mention that I did install the Post SP 4
Hotfix rollup. My SQL Version is at 8.00.2187.
However, I did check Perfmon and SQL is using up more than 50% of the
physical memory, which I think, according to the article, means that it is
running normally.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
> hot
> fix:
> http://support.microsoft.com/kb/899761
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
> SP3
> to SP4, my re-indexing job takes 3-4 times longer than before. The amount
> of data is about the same. Nothing else within SQL has changed. I am
> running SQL on the other node, but the hardware is identical and I checked
> the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
> Has anyone experienced this after installing Service Pack 4?
>|||That's odd. How much RAM do you have on each node and let us know whether
the /3GB switch is/isn't used.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
Thanks. I actually forgot to mention that I did install the Post SP 4
Hotfix rollup. My SQL Version is at 8.00.2187.
However, I did check Perfmon and SQL is using up more than 50% of the
physical memory, which I think, according to the article, means that it is
running normally.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
> hot
> fix:
> http://support.microsoft.com/kb/899761
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
> SP3
> to SP4, my re-indexing job takes 3-4 times longer than before. The amount
> of data is about the same. Nothing else within SQL has changed. I am
> running SQL on the other node, but the hardware is identical and I checked
> the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
> Has anyone experienced this after installing Service Pack 4?
>|||Thanks,
There's 8 GB of RAM on each node. The 3/GB switch is not used and this was
actually PER Microsoft.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
> That's odd. How much RAM do you have on each node and let us know whether
> the /3GB switch is/isn't used.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Thanks. I actually forgot to mention that I did install the Post SP 4
> Hotfix rollup. My SQL Version is at 8.00.2187.
> However, I did check Perfmon and SQL is using up more than 50% of the
> physical memory, which I think, according to the article, means that it is
> running normally.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>|||Just wanted to sure. What's odd is that it appears to run differently on
each node. Also, of the 8 GB, how much did you tell it to use for SQL
Server? Could you give us the sp_configure output for max server memory and
AWE?
Are there other processes running on one node and not the other?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
Thanks,
There's 8 GB of RAM on each node. The 3/GB switch is not used and this was
actually PER Microsoft.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
> That's odd. How much RAM do you have on each node and let us know whether
> the /3GB switch is/isn't used.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Thanks. I actually forgot to mention that I did install the Post SP 4
> Hotfix rollup. My SQL Version is at 8.00.2187.
> However, I did check Perfmon and SQL is using up more than 50% of the
> physical memory, which I think, according to the article, means that it is
> running normally.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>|||Thanks so much for your help Tom.
There are no processes running on one node that's not running on the other.
SQL Server is configured to use 6 GB - again, this was suggested by
microsoft when we were troubleshooting another problem several months back.
Here's the sp_configure output
max server memory (MB) 4 2147483647 6008
6008
awe enabled 0 1 1 1
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>> That's odd. How much RAM do you have on each node and let us know
>> whether
>> the /3GB switch is/isn't used.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
>> Thanks. I actually forgot to mention that I did install the Post SP 4
>> Hotfix rollup. My SQL Version is at 8.00.2187.
>> However, I did check Perfmon and SQL is using up more than 50% of the
>> physical memory, which I think, according to the article, means that it
>> is
>> running normally.
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>>
>|||If I were to increase the Max Memory, would I need to restart SQL service
for it to take effect?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>> That's odd. How much RAM do you have on each node and let us know
>> whether
>> the /3GB switch is/isn't used.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
>> Thanks. I actually forgot to mention that I did install the Post SP 4
>> Hotfix rollup. My SQL Version is at 8.00.2187.
>> However, I did check Perfmon and SQL is using up more than 50% of the
>> physical memory, which I think, according to the article, means that it
>> is
>> running normally.
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>>
>|||I just ran a perfmon and measured SQLServer:Memory Manager - Target Server
Memory and Total Server Memory. It's at a steady 5 GB. My Max memory is
set to 6 GB. Is this normal?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>> That's odd. How much RAM do you have on each node and let us know
>> whether
>> the /3GB switch is/isn't used.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
>> Thanks. I actually forgot to mention that I did install the Post SP 4
>> Hotfix rollup. My SQL Version is at 8.00.2187.
>> However, I did check Perfmon and SQL is using up more than 50% of the
>> physical memory, which I think, according to the article, means that it
>> is
>> running normally.
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>>
>|||6GB = 6,144MB, so you're just a little short of using the 6GB. Leaving the
remaining 2GB to the OS is plenty.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:OxSToz2qGHA.3564@.TK2MSFTNGP03.phx.gbl...
Thanks so much for your help Tom.
There are no processes running on one node that's not running on the other.
SQL Server is configured to use 6 GB - again, this was suggested by
microsoft when we were troubleshooting another problem several months back.
Here's the sp_configure output
max server memory (MB) 4 2147483647 6008
6008
awe enabled 0 1 1 1
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>> That's odd. How much RAM do you have on each node and let us know
>> whether
>> the /3GB switch is/isn't used.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
>> Thanks. I actually forgot to mention that I did install the Post SP 4
>> Hotfix rollup. My SQL Version is at 8.00.2187.
>> However, I did check Perfmon and SQL is using up more than 50% of the
>> physical memory, which I think, according to the article, means that it
>> is
>> running normally.
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>>
>|||If you were using AWE, yes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:enizG02qGHA.2180@.TK2MSFTNGP05.phx.gbl...
If I were to increase the Max Memory, would I need to restart SQL service
for it to take effect?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>> That's odd. How much RAM do you have on each node and let us know
>> whether
>> the /3GB switch is/isn't used.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
>> Thanks. I actually forgot to mention that I did install the Post SP 4
>> Hotfix rollup. My SQL Version is at 8.00.2187.
>> However, I did check Perfmon and SQL is using up more than 50% of the
>> physical memory, which I think, according to the article, means that it
>> is
>> running normally.
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>>
>|||Your max server memory should be at 6 GB = 6,144 MB. Not sure why it's
below that. The only other thing I can think of is that you may have a
Windows hotfix that was applied to one box and not the other.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:ea$m452qGHA.4864@.TK2MSFTNGP03.phx.gbl...
I just ran a perfmon and measured SQLServer:Memory Manager - Target Server
Memory and Total Server Memory. It's at a steady 5 GB. My Max memory is
set to 6 GB. Is this normal?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>> That's odd. How much RAM do you have on each node and let us know
>> whether
>> the /3GB switch is/isn't used.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
>> Thanks. I actually forgot to mention that I did install the Post SP 4
>> Hotfix rollup. My SQL Version is at 8.00.2187.
>> However, I did check Perfmon and SQL is using up more than 50% of the
>> physical memory, which I think, according to the article, means that it
>> is
>> running normally.
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>>
>

reindexing slow after installing SQL 2K SP4?

I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from SP3
to SP4, my re-indexing job takes 3-4 times longer than before. The amount
of data is about the same. Nothing else within SQL has changed. I am
running SQL on the other node, but the hardware is identical and I checked
the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
Has anyone experienced this after installing Service Pack 4?There is an issue with AWE in SP4 - it stops using it. Get the post-SP4 hot
fix:
http://support.microsoft.com/kb/899761
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from SP3
to SP4, my re-indexing job takes 3-4 times longer than before. The amount
of data is about the same. Nothing else within SQL has changed. I am
running SQL on the other node, but the hardware is identical and I checked
the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
Has anyone experienced this after installing Service Pack 4?|||Thanks. I actually forgot to mention that I did install the Post SP 4
Hotfix rollup. My SQL Version is at 8.00.2187.
However, I did check Perfmon and SQL is using up more than 50% of the
physical memory, which I think, according to the article, means that it is
running normally.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
> hot
> fix:
> http://support.microsoft.com/kb/899761
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
> SP3
> to SP4, my re-indexing job takes 3-4 times longer than before. The amount
> of data is about the same. Nothing else within SQL has changed. I am
> running SQL on the other node, but the hardware is identical and I checked
> the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
> Has anyone experienced this after installing Service Pack 4?
>|||That's odd. How much RAM do you have on each node and let us know whether
the /3GB switch is/isn't used.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
Thanks. I actually forgot to mention that I did install the Post SP 4
Hotfix rollup. My SQL Version is at 8.00.2187.
However, I did check Perfmon and SQL is using up more than 50% of the
physical memory, which I think, according to the article, means that it is
running normally.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
> hot
> fix:
> http://support.microsoft.com/kb/899761
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
> SP3
> to SP4, my re-indexing job takes 3-4 times longer than before. The amount
> of data is about the same. Nothing else within SQL has changed. I am
> running SQL on the other node, but the hardware is identical and I checked
> the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
> Has anyone experienced this after installing Service Pack 4?
>|||Thanks,
There's 8 GB of RAM on each node. The 3/GB switch is not used and this was
actually PER Microsoft.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
> That's odd. How much RAM do you have on each node and let us know whether
> the /3GB switch is/isn't used.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Thanks. I actually forgot to mention that I did install the Post SP 4
> Hotfix rollup. My SQL Version is at 8.00.2187.
> However, I did check Perfmon and SQL is using up more than 50% of the
> physical memory, which I think, according to the article, means that it is
> running normally.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>|||Just wanted to sure. What's odd is that it appears to run differently on
each node. Also, of the 8 GB, how much did you tell it to use for SQL
Server? Could you give us the sp_configure output for max server memory and
AWE?
Are there other processes running on one node and not the other?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
Thanks,
There's 8 GB of RAM on each node. The 3/GB switch is not used and this was
actually PER Microsoft.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
> That's odd. How much RAM do you have on each node and let us know whether
> the /3GB switch is/isn't used.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Thanks. I actually forgot to mention that I did install the Post SP 4
> Hotfix rollup. My SQL Version is at 8.00.2187.
> However, I did check Perfmon and SQL is using up more than 50% of the
> physical memory, which I think, according to the article, means that it is
> running normally.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>|||Thanks so much for your help Tom.
There are no processes running on one node that's not running on the other.
SQL Server is configured to use 6 GB - again, this was suggested by
microsoft when we were troubleshooting another problem several months back.
Here's the sp_configure output
max server memory (MB) 4 2147483647 6008
6008
awe enabled 0 1 1 1
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>|||If I were to increase the Max Memory, would I need to restart SQL service
for it to take effect?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>|||I just ran a perfmon and measured SQLServer:Memory Manager - Target Server
Memory and Total Server Memory. It's at a steady 5 GB. My Max memory is
set to 6 GB. Is this normal?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>|||6GB = 6,144MB, so you're just a little short of using the 6GB. Leaving the
remaining 2GB to the OS is plenty.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:OxSToz2qGHA.3564@.TK2MSFTNGP03.phx.gbl...
Thanks so much for your help Tom.
There are no processes running on one node that's not running on the other.
SQL Server is configured to use 6 GB - again, this was suggested by
microsoft when we were troubleshooting another problem several months back.
Here's the sp_configure output
max server memory (MB) 4 2147483647 6008
6008
awe enabled 0 1 1 1
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>