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 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...
>

Reindexing log shipped database

Hallo
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tomyou want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>

Reindexing log shipped database

Hallo
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tom
you want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon
|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>
|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ
|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>

Reindexing log shipped database

Hallo
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tomyou want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>

reindexing job taking too long

I have an optimization job on a 4 way SQL 2000 box that takes 7-12 hours.
The db is 150 gigs. I can't figure out why sometimes it finishes 5 hours
earlier than other times. Backups are not contending with this optimization
job, but users may be. How can I find this out?
TIA
jjAre there active processes locking this table during the index
creation/defragmentation/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
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:esxLN5m1GHA.4116@.TK2MSFTNGP02.phx.gbl...
>I have an optimization job on a 4 way SQL 2000 box that takes 7-12 hours.
>The db is 150 gigs. I can't figure out why sometimes it finishes 5 hours
>earlier than other times. Backups are not contending with this optimization
>job, but users may be. How can I find this out?
> TIA
> jj
>|||jj wrote:
> I have an optimization job on a 4 way SQL 2000 box that takes 7-12 hours.
> The db is 150 gigs. I can't figure out why sometimes it finishes 5 hours
> earlier than other times. Backups are not contending with this optimization
> job, but users may be. How can I find this out?
> TIA
> jj
>
You'll have to monitor blocking during the times that this process is
running, either with Profiler or with your own script that watches
sysprocesses.
Something to consider would be to not rebuild all indexes, but to only
rebuild those that are badly fragmented. I have a script that will
automate this for you:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

reindexing job taking too long

I have an optimization job on a 4 way SQL 2000 box that takes 7-12 hours.
The db is 150 gigs. I can't figure out why sometimes it finishes 5 hours
earlier than other times. Backups are not contending with this optimization
job, but users may be. How can I find this out?
TIA
jjAre there active processes locking this table during the index
creation/defragmentation/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
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:esxLN5m1GHA.4116@.TK2MSFTNGP02.phx.gbl...
>I have an optimization job on a 4 way SQL 2000 box that takes 7-12 hours.
>The db is 150 gigs. I can't figure out why sometimes it finishes 5 hours
>earlier than other times. Backups are not contending with this optimization
>job, but users may be. How can I find this out?
> TIA
> jj
>|||jj wrote:
> I have an optimization job on a 4 way SQL 2000 box that takes 7-12 hours.
> The db is 150 gigs. I can't figure out why sometimes it finishes 5 hours
> earlier than other times. Backups are not contending with this optimizatio
n
> job, but users may be. How can I find this out?
> TIA
> jj
>
You'll have to monitor blocking during the times that this process is
running, either with Profiler or with your own script that watches
sysprocesses.
Something to consider would be to not rebuild all indexes, but to only
rebuild those that are badly fragmented. I have a script that will
automate this for you:
http://realsqlguy.com/serendipity/a...realsqlguy.com

re-indexing job takes more time

We have a re-indexing all DBs schedule job in our SQL 2000 box,
normally it took 7 hours to complete but all of the sudden now it
takes more than 20 hours.
What do you think it cause this problem? We have no clue."Frank" <soal6570@.yahoo.com> wrote in message
news:42601b2.0405052143.66cdbe69@.posting.google.co m...
> We have a re-indexing all DBs schedule job in our SQL 2000 box,
> normally it took 7 hours to complete but all of the sudden now it
> takes more than 20 hours.
> What do you think it cause this problem? We have no clue.

Have you checked general CPU and I/O activity on the server, using Perfmon?
There may be another process which is taking up resources, or perhaps it
could be a symptom of Slammer infection, if the MSSQL service has a very
high CPU load. You might also want to use Profiler to check if there's any
unusual activity within MSSQL itself, which might be creating problems.

Simon|||It could also be caused by other activity blocking the rebuilds, or badly
fragmented freespace if the indexes have grown.

On a seperate note, here's a link to a whitepaper you may want to read to
determine if reindexing everything is the right thing to do for your
particular situation.

http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx

Regards

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:409a67f2$1_1@.news.bluewin.ch...
> "Frank" <soal6570@.yahoo.com> wrote in message
> news:42601b2.0405052143.66cdbe69@.posting.google.co m...
> > We have a re-indexing all DBs schedule job in our SQL 2000 box,
> > normally it took 7 hours to complete but all of the sudden now it
> > takes more than 20 hours.
> > What do you think it cause this problem? We have no clue.
> Have you checked general CPU and I/O activity on the server, using
Perfmon?
> There may be another process which is taking up resources, or perhaps it
> could be a symptom of Slammer infection, if the MSSQL service has a very
> high CPU load. You might also want to use Profiler to check if there's any
> unusual activity within MSSQL itself, which might be creating problems.
> Simon

Reindexing 27GB table

On one of the database, I support has 16957722 records in a table and it's
clustered index size is 27606.30.
I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
It takes more than 7 hours.
Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
cancelling the job.
In this situation what would you suggest to reindex the table of
such big one ( 27 GB ) ?
Appreciate your advise.
--
Thanks
S SIVAPRASADFirst you need to determine whether you benefit from reindexing. See
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.[/url
]
To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_s
tats (2005).
In 2000, you have little options of you want to do this "online", your optio
n is DBCC INDEXDEFRAG.
In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG
), or use ALTER INDEX
with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new opti
on).
All have pro and cons, one thing to consider is the usage of transaction log
space (which depends on
what type of command you run and can also be influenced by recovery model).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote
in message
news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> On one of the database, I support has 16957722 records in a table and it's
> clustered index size is 27606.30.
> I used to run the Reindex job created on weekly Sunday starting at 11:00 P
M.
> It takes more than 7 hours.
> Since it is a 24x7 server, when Reindexing job runs it causes blocks so I
am
> cancelling the job.
> In this situation what would you suggest to reindex the table of
> such big one ( 27 GB ) ?
> Appreciate your advise.
> --
> Thanks
> S SIVAPRASAD|||It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
Database is on Full recovery model and it has enough space for Log file
growth.
DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
Let me know what would be the best optimal way of rebuilding the indexes.
The box is on 24x7 and I want to rebuild the index as quick as possible.
The clustered index sized around 27 GB.
--
Thanks
S SIVAPRASAD
"Tibor Karaszi" wrote:

> First you need to determine whether you benefit from reindexing. See
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.[/u
rl]
> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical
_stats (2005).
> In 2000, you have little options of you want to do this "online", your opt
ion is DBCC INDEXDEFRAG.
> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFR
AG), or use ALTER INDEX
> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new op
tion).
> All have pro and cons, one thing to consider is the usage of transaction l
og space (which depends on
> what type of command you run and can also be influenced by recovery model)
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wro
te in message
> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
>
>|||There's no "optimal way". There are different options, all with advantages a
nd disadvantages. Based
on your requirements, you can weigh the advantages to the disadvantages and
pick the one that suits
your particular need best. Make sure you understand the pros and cons with e
ach method and read the
whitepaper I posted a link to.
Having said that, if the box is 24*7, indexdefrag might be better. But it ca
n take longer time than
dbreindex (but it doesn't block nearly as much), it can produce more log rec
ords, and the
defragmentation might not be as perfect as dbreindex.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote
in message
news:8A93A9FA-E357-475D-BB40-8F9BE0A43CCF@.microsoft.com...[vbcol=seagreen]
> It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
> Database is on Full recovery model and it has enough space for Log file
> growth.
> DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
> Let me know what would be the best optimal way of rebuilding the indexes.
> The box is on 24x7 and I want to rebuild the index as quick as possible.
> The clustered index sized around 27 GB.
> --
> Thanks
> S SIVAPRASAD
>
> "Tibor Karaszi" wrote:
>

Reindexing 27GB table

On one of the database, I support has 16957722 records in a table and it's
clustered index size is 27606.30.
I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
It takes more than 7 hours.
Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
cancelling the job.
In this situation what would you suggest to reindex the table of
such big one ( 27 GB ) ?
Appreciate your advise.
--
Thanks
S SIVAPRASADFirst you need to determine whether you benefit from reindexing. See
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
In 2000, you have little options of you want to do this "online", your option is DBCC INDEXDEFRAG.
In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
All have pro and cons, one thing to consider is the usage of transaction log space (which depends on
what type of command you run and can also be influenced by recovery model).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> On one of the database, I support has 16957722 records in a table and it's
> clustered index size is 27606.30.
> I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
> It takes more than 7 hours.
> Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
> cancelling the job.
> In this situation what would you suggest to reindex the table of
> such big one ( 27 GB ) ?
> Appreciate your advise.
> --
> Thanks
> S SIVAPRASAD|||It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
Database is on Full recovery model and it has enough space for Log file
growth.
DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
Let me know what would be the best optimal way of rebuilding the indexes.
The box is on 24x7 and I want to rebuild the index as quick as possible.
The clustered index sized around 27 GB.
--
Thanks
S SIVAPRASAD
"Tibor Karaszi" wrote:
> First you need to determine whether you benefit from reindexing. See
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
> In 2000, you have little options of you want to do this "online", your option is DBCC INDEXDEFRAG.
> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
> All have pro and cons, one thing to consider is the usage of transaction log space (which depends on
> what type of command you run and can also be influenced by recovery model).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> > On one of the database, I support has 16957722 records in a table and it's
> > clustered index size is 27606.30.
> >
> > I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
> > It takes more than 7 hours.
> >
> > Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
> > cancelling the job.
> >
> > In this situation what would you suggest to reindex the table of
> > such big one ( 27 GB ) ?
> >
> > Appreciate your advise.
> > --
> > Thanks
> > S SIVAPRASAD
>
>|||There's no "optimal way". There are different options, all with advantages and disadvantages. Based
on your requirements, you can weigh the advantages to the disadvantages and pick the one that suits
your particular need best. Make sure you understand the pros and cons with each method and read the
whitepaper I posted a link to.
Having said that, if the box is 24*7, indexdefrag might be better. But it can take longer time than
dbreindex (but it doesn't block nearly as much), it can produce more log records, and the
defragmentation might not be as perfect as dbreindex.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
news:8A93A9FA-E357-475D-BB40-8F9BE0A43CCF@.microsoft.com...
> It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
> Database is on Full recovery model and it has enough space for Log file
> growth.
> DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
> Let me know what would be the best optimal way of rebuilding the indexes.
> The box is on 24x7 and I want to rebuild the index as quick as possible.
> The clustered index sized around 27 GB.
> --
> Thanks
> S SIVAPRASAD
>
> "Tibor Karaszi" wrote:
>> First you need to determine whether you benefit from reindexing. See
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
>> In 2000, you have little options of you want to do this "online", your option is DBCC
>> INDEXDEFRAG.
>> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
>> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
>> All have pro and cons, one thing to consider is the usage of transaction log space (which depends
>> on
>> what type of command you run and can also be influenced by recovery model).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
>> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
>> > On one of the database, I support has 16957722 records in a table and it's
>> > clustered index size is 27606.30.
>> >
>> > I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
>> > It takes more than 7 hours.
>> >
>> > Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
>> > cancelling the job.
>> >
>> > In this situation what would you suggest to reindex the table of
>> > such big one ( 27 GB ) ?
>> >
>> > Appreciate your advise.
>> > --
>> > Thanks
>> > S SIVAPRASAD
>>

reindex versus maintenance plan

Hi,
I would like to reindex all the tables in my database by using a job
instead of a maintenance plan. In a maintenance plan this is possible with
the checkbox: "reorganize data and index pages". How can I do the same thing
in a script without using DDBC REINDEX for every table?Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I would like to reindex all the tables in my database by using a job
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing
> in a script without using DDBC REINDEX for every table?
>
>|||Thanks,
This solved my problem.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> schreef in bericht
news:ePhnW9eIEHA.3376@.TK2MSFTNGP09.phx.gbl...
> Can use sp_msForEachTable system procedure for the same. Like
> EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I would like to reindex all the tables in my database by using a job
> > instead of a maintenance plan. In a maintenance plan this is possible
with
> > the checkbox: "reorganize data and index pages". How can I do the same
> thing
> > in a script without using DDBC REINDEX for every table?
> >
> >
> >
>|||Here is what I use to dbcc indexDefrag all indexes each night:
--***********************************************************************
DECLARE @.Table sysname
DECLARE @.Indid Int
DECLARE cur_tblFetch CURSOR FOR
SELECT Table_Name from information_Schema.tables where table_type = 'base
table'
OPEN cur_tblFetch
FETCH NEXT From cur_tblFetch INTO @.Table
While @.@.FETCH_STATUS = 0
BEGIN
DECLARE cur_indFetch CURSOR FOR
SELECT indid FROM SysIndexes WHERE id = Object_ID(@.Table) AND keycnt > 0
OPEN cur_indFetch
FETCH NEXT FROM cur_indFetch INTO @.Indid
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.Indid <> 255
BEGIN
DBCC INDEXDEFRAG(Creditnet,@.Table,@.Indid) WITH NO_INFOMSGS
END
FETCH NEXT FROM cur_indFetch INTO @.Indid
END
CLOSE cur_IndFetch
DEALLOCATE cur_IndFetch
FETCH NEXT FROM cur_tblFetch INTO @.Table
END
CLOSE cur_tblFetch
DEALLOCATE cur_tblFetch
EXEC sp_updatestats
--***********************************************************************
cheers,
Greg Jackson
PDX, Oregon

Reindex

The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
takes about 1hr and 20 minutes in a 64 bit server. The same set of databases
exist on both servers and the file structure also is the same with the drives
for the database files for both being SAN LUNs. The activity on SQL Server is
also the same for both servers when the reindex job is run , there is no one
else on the system. The 32 bit server has only 2G available to SQL whereas
the 64 bit has 8G available to SQL.
How many processors on the 32 bit and the 64bit? What is your MAXDOP set
to? Just because the 64bit is connected to a SAN does not mean you will get
the same performance as another LUN.
Andrew J. Kelly SQL MVP
"MANCHO" <MANCHO@.discussions.microsoft.com> wrote in message
news:3EC0BE26-484E-4714-A25D-35A038E2D7A7@.microsoft.com...
> The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
> takes about 1hr and 20 minutes in a 64 bit server. The same set of
> databases
> exist on both servers and the file structure also is the same with the
> drives
> for the database files for both being SAN LUNs. The activity on SQL Server
> is
> also the same for both servers when the reindex job is run , there is no
> one
> else on the system. The 32 bit server has only 2G available to SQL whereas
> the 64 bit has 8G available to SQL.

Reindex

The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
takes about 1hr and 20 minutes in a 64 bit server. The same set of databases
exist on both servers and the file structure also is the same with the drive
s
for the database files for both being SAN LUNs. The activity on SQL Server i
s
also the same for both servers when the reindex job is run , there is no one
else on the system. The 32 bit server has only 2G available to SQL whereas
the 64 bit has 8G available to SQL.How many processors on the 32 bit and the 64bit? What is your MAXDOP set
to? Just because the 64bit is connected to a SAN does not mean you will get
the same performance as another LUN.
Andrew J. Kelly SQL MVP
"MANCHO" <MANCHO@.discussions.microsoft.com> wrote in message
news:3EC0BE26-484E-4714-A25D-35A038E2D7A7@.microsoft.com...
> The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
> takes about 1hr and 20 minutes in a 64 bit server. The same set of
> databases
> exist on both servers and the file structure also is the same with the
> drives
> for the database files for both being SAN LUNs. The activity on SQL Server
> is
> also the same for both servers when the reindex job is run , there is no
> one
> else on the system. The 32 bit server has only 2G available to SQL whereas
> the 64 bit has 8G available to SQL.

Reindex

The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
takes about 1hr and 20 minutes in a 64 bit server. The same set of databases
exist on both servers and the file structure also is the same with the drives
for the database files for both being SAN LUNs. The activity on SQL Server is
also the same for both servers when the reindex job is run , there is no one
else on the system. The 32 bit server has only 2G available to SQL whereas
the 64 bit has 8G available to SQL.How many processors on the 32 bit and the 64bit? What is your MAXDOP set
to? Just because the 64bit is connected to a SAN does not mean you will get
the same performance as another LUN.
--
Andrew J. Kelly SQL MVP
"MANCHO" <MANCHO@.discussions.microsoft.com> wrote in message
news:3EC0BE26-484E-4714-A25D-35A038E2D7A7@.microsoft.com...
> The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
> takes about 1hr and 20 minutes in a 64 bit server. The same set of
> databases
> exist on both servers and the file structure also is the same with the
> drives
> for the database files for both being SAN LUNs. The activity on SQL Server
> is
> also the same for both servers when the reindex job is run , there is no
> one
> else on the system. The 32 bit server has only 2G available to SQL whereas
> the 64 bit has 8G available to SQL.