I was planning to re-index my database (225GB) this coming weekend however I
have just realised that it will be running through the server clock changing
(advancing 1 hour). Will this have any adverse effect on the procedure?
GavNo problem. The only thing to be careful with is when you go back in time,
and you have transactions occurring during that time, you can find yourself
in a position where you cannot do point in time restore for transaction that
occurred during that hour.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
news:c3n37r$l3p$1@.hercules.btinternet.com...
> I was planning to re-index my database (225GB) this coming weekend however
I
> have just realised that it will be running through the server clock
changing
> (advancing 1 hour). Will this have any adverse effect on the procedure?
> Gav
>
Showing posts with label re-indexing. Show all posts
Showing posts with label re-indexing. Show all posts
Tuesday, March 20, 2012
Monday, March 12, 2012
Re-indexing table causes Windows to re-boot !
I have a DB with multiple tables and when I attempt to index one of the
tables the whole Windows servers crashes and reboots. My understanding is
that SQLServer should run as an isolated application and so should not be
able to affect the operating system.
This server has been fine for over a year now but has just started to
exhibit this problem (no recent changes or updates to the server, apart from
regular anti-virus updates, that could account for this behaviour).
The problem in more detail:
Database - 1577MB data (518 MB free). Set to automatically grow at 10% to
an un-restricted limit (data and log)
Table in question - 900808 KB data, ~7000000 rows of data
Indexes - there are three "user" indexes that I create. One of them is a
clustered index. When any of these indexes is built the server crashes.
Exhaustive DBCC tests imply that the DB and table are in perfect health.
Nothing in the event log and no memory dumps.
SQLServer - SQLServer 2000 sp3a
Windows - Win2000 sp3, IBM XSERIES_335 with 3 GHz xeon processor and oodles
of memory.
User - at wits end
Any advice would be most welcome
Thanks
GriffGriff,
You are correct. You should focus on the operating system. Most likely SQL
Server is just taxing the IO or Processor hard and there is either a
hardware or driver issue. In the event log is it an unexpected reboot or is
there a stop error listed?
"Griff" <Howling@.The.Moon> wrote in message
news:%234bILC2vFHA.2556@.TK2MSFTNGP15.phx.gbl...
>I have a DB with multiple tables and when I attempt to index one of the
>tables the whole Windows servers crashes and reboots. My understanding is
>that SQLServer should run as an isolated application and so should not be
>able to affect the operating system.
> This server has been fine for over a year now but has just started to
> exhibit this problem (no recent changes or updates to the server, apart
> from regular anti-virus updates, that could account for this behaviour).
> The problem in more detail:
> Database - 1577MB data (518 MB free). Set to automatically grow at 10% to
> an un-restricted limit (data and log)
> Table in question - 900808 KB data, ~7000000 rows of data
> Indexes - there are three "user" indexes that I create. One of them is a
> clustered index. When any of these indexes is built the server crashes.
> Exhaustive DBCC tests imply that the DB and table are in perfect health.
> Nothing in the event log and no memory dumps.
> SQLServer - SQLServer 2000 sp3a
> Windows - Win2000 sp3, IBM XSERIES_335 with 3 GHz xeon processor and
> oodles of memory.
> User - at wits end
> Any advice would be most welcome
> Thanks
> Griff
>
>|||Having read the event logs (application, system, security & SQLServer
ones) and searched for a memory dmp file I get the impression that the
server crashed so fast that it simply didn't have the chance to log
anything. Metaphorically speaking, it had the rug pulled from under
it.
The event log simply lists the fact that it rebooted after an
unexpected system shutdown, but I can't find anything that implies what
could have caused the system shutdown.
Watching the task manager whilst the re-indexing is occuring (before it
dies), it shows no real sign of taxing the server's resources. Of
course now, running live without indexes, the server is as slow as a
lame dog, presumably because SQLServer is having to do lots of full
table scans.|||Oddly enough I've seen this scenerio with two of our 32 way servers. We and
the hardware vendor were able to reproduce the system fault but the vendor
never could tell us what was wrong or how to fix it. Basically when the
server approached 100% CPU (doing masive qeries) the system started geting
dirty memory transfers between pods. It all started after we upgraded our
SQL Server 2000 build. We switched to 64 bit HP servers and immediately
stopped experiencing the issue. Assuming a good power supply, in my opinion
any unexpected reboot on a Windows server is one of three things.
From most likely to least likely:
1. A hardware issue
2. A poorly written driver in kernal mode
3. Windows not trapping the error.
SQL Server may be pushing the system in a new way or to 100% but that would
be a performance issue not an availability issue.
Good luck.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:1127390278.062254.254300@.g49g2000cwa.googlegroups.com...
> Having read the event logs (application, system, security & SQLServer
> ones) and searched for a memory dmp file I get the impression that the
> server crashed so fast that it simply didn't have the chance to log
> anything. Metaphorically speaking, it had the rug pulled from under
> it.
> The event log simply lists the fact that it rebooted after an
> unexpected system shutdown, but I can't find anything that implies what
> could have caused the system shutdown.
> Watching the task manager whilst the re-indexing is occuring (before it
> dies), it shows no real sign of taxing the server's resources. Of
> course now, running live without indexes, the server is as slow as a
> lame dog, presumably because SQLServer is having to do lots of full
> table scans.
>|||Well, I can't say what the problem was, but we do appear to have fixed it.
Despite nothing "appearing" to have changed on the system (we manually apply
the Microsoft critical upgrades), this problem started occuring out of the
blue.
However, it got worse throughout the day - the server started rebooting
during "normal" use, approximately ever 30 minutes. As mentioned
previously, the CPU was more or less at 100% because we couldn't rebuild the
indexes, so I was wondering whether it could be overheating or some other
symptom of the hardware being over-stressed (though it's a good spec server
so should cope).
Anyhow, that evening we clutched at straws and applied Win2000 sp4 (had been
sp3) and, would you believe it, it fixed the problem and a good nights sleep
was had by all.|||Griff wrote:
> Well, I can't say what the problem was, but we do appear to have
> fixed it.
> Despite nothing "appearing" to have changed on the system (we
> manually apply the Microsoft critical upgrades), this problem started
> occuring out of the blue.
> However, it got worse throughout the day - the server started
> rebooting during "normal" use, approximately ever 30 minutes. As
> mentioned previously, the CPU was more or less at 100% because we
> couldn't rebuild the indexes, so I was wondering whether it could be
> overheating or some other symptom of the hardware being over-stressed
> (though it's a good spec server so should cope).
> Anyhow, that evening we clutched at straws and applied Win2000 sp4
> (had been sp3) and, would you believe it, it fixed the problem and a
> good nights sleep was had by all.
Probably someone was "joking": there are some holes in some windows
versions which allow a host to be restarted / switched off or similar via
network (these are usually closed by sp4 IIRC). So it could even be
manual intervention that caused this...
Kind regards
robert|||Hi Robert
I believe that we can almost rule that one out. The server is behind a
firewall that only allows HTTP in from unknown IP addresses, everything else
is effectively blocked. The only person who can connect to it in any other
way is me.
But hey, we know that the only way to make a computer completely safe is to
switch it off.
tables the whole Windows servers crashes and reboots. My understanding is
that SQLServer should run as an isolated application and so should not be
able to affect the operating system.
This server has been fine for over a year now but has just started to
exhibit this problem (no recent changes or updates to the server, apart from
regular anti-virus updates, that could account for this behaviour).
The problem in more detail:
Database - 1577MB data (518 MB free). Set to automatically grow at 10% to
an un-restricted limit (data and log)
Table in question - 900808 KB data, ~7000000 rows of data
Indexes - there are three "user" indexes that I create. One of them is a
clustered index. When any of these indexes is built the server crashes.
Exhaustive DBCC tests imply that the DB and table are in perfect health.
Nothing in the event log and no memory dumps.
SQLServer - SQLServer 2000 sp3a
Windows - Win2000 sp3, IBM XSERIES_335 with 3 GHz xeon processor and oodles
of memory.
User - at wits end
Any advice would be most welcome
Thanks
GriffGriff,
You are correct. You should focus on the operating system. Most likely SQL
Server is just taxing the IO or Processor hard and there is either a
hardware or driver issue. In the event log is it an unexpected reboot or is
there a stop error listed?
"Griff" <Howling@.The.Moon> wrote in message
news:%234bILC2vFHA.2556@.TK2MSFTNGP15.phx.gbl...
>I have a DB with multiple tables and when I attempt to index one of the
>tables the whole Windows servers crashes and reboots. My understanding is
>that SQLServer should run as an isolated application and so should not be
>able to affect the operating system.
> This server has been fine for over a year now but has just started to
> exhibit this problem (no recent changes or updates to the server, apart
> from regular anti-virus updates, that could account for this behaviour).
> The problem in more detail:
> Database - 1577MB data (518 MB free). Set to automatically grow at 10% to
> an un-restricted limit (data and log)
> Table in question - 900808 KB data, ~7000000 rows of data
> Indexes - there are three "user" indexes that I create. One of them is a
> clustered index. When any of these indexes is built the server crashes.
> Exhaustive DBCC tests imply that the DB and table are in perfect health.
> Nothing in the event log and no memory dumps.
> SQLServer - SQLServer 2000 sp3a
> Windows - Win2000 sp3, IBM XSERIES_335 with 3 GHz xeon processor and
> oodles of memory.
> User - at wits end
> Any advice would be most welcome
> Thanks
> Griff
>
>|||Having read the event logs (application, system, security & SQLServer
ones) and searched for a memory dmp file I get the impression that the
server crashed so fast that it simply didn't have the chance to log
anything. Metaphorically speaking, it had the rug pulled from under
it.
The event log simply lists the fact that it rebooted after an
unexpected system shutdown, but I can't find anything that implies what
could have caused the system shutdown.
Watching the task manager whilst the re-indexing is occuring (before it
dies), it shows no real sign of taxing the server's resources. Of
course now, running live without indexes, the server is as slow as a
lame dog, presumably because SQLServer is having to do lots of full
table scans.|||Oddly enough I've seen this scenerio with two of our 32 way servers. We and
the hardware vendor were able to reproduce the system fault but the vendor
never could tell us what was wrong or how to fix it. Basically when the
server approached 100% CPU (doing masive qeries) the system started geting
dirty memory transfers between pods. It all started after we upgraded our
SQL Server 2000 build. We switched to 64 bit HP servers and immediately
stopped experiencing the issue. Assuming a good power supply, in my opinion
any unexpected reboot on a Windows server is one of three things.
From most likely to least likely:
1. A hardware issue
2. A poorly written driver in kernal mode
3. Windows not trapping the error.
SQL Server may be pushing the system in a new way or to 100% but that would
be a performance issue not an availability issue.
Good luck.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:1127390278.062254.254300@.g49g2000cwa.googlegroups.com...
> Having read the event logs (application, system, security & SQLServer
> ones) and searched for a memory dmp file I get the impression that the
> server crashed so fast that it simply didn't have the chance to log
> anything. Metaphorically speaking, it had the rug pulled from under
> it.
> The event log simply lists the fact that it rebooted after an
> unexpected system shutdown, but I can't find anything that implies what
> could have caused the system shutdown.
> Watching the task manager whilst the re-indexing is occuring (before it
> dies), it shows no real sign of taxing the server's resources. Of
> course now, running live without indexes, the server is as slow as a
> lame dog, presumably because SQLServer is having to do lots of full
> table scans.
>|||Well, I can't say what the problem was, but we do appear to have fixed it.
Despite nothing "appearing" to have changed on the system (we manually apply
the Microsoft critical upgrades), this problem started occuring out of the
blue.
However, it got worse throughout the day - the server started rebooting
during "normal" use, approximately ever 30 minutes. As mentioned
previously, the CPU was more or less at 100% because we couldn't rebuild the
indexes, so I was wondering whether it could be overheating or some other
symptom of the hardware being over-stressed (though it's a good spec server
so should cope).
Anyhow, that evening we clutched at straws and applied Win2000 sp4 (had been
sp3) and, would you believe it, it fixed the problem and a good nights sleep
was had by all.|||Griff wrote:
> Well, I can't say what the problem was, but we do appear to have
> fixed it.
> Despite nothing "appearing" to have changed on the system (we
> manually apply the Microsoft critical upgrades), this problem started
> occuring out of the blue.
> However, it got worse throughout the day - the server started
> rebooting during "normal" use, approximately ever 30 minutes. As
> mentioned previously, the CPU was more or less at 100% because we
> couldn't rebuild the indexes, so I was wondering whether it could be
> overheating or some other symptom of the hardware being over-stressed
> (though it's a good spec server so should cope).
> Anyhow, that evening we clutched at straws and applied Win2000 sp4
> (had been sp3) and, would you believe it, it fixed the problem and a
> good nights sleep was had by all.
Probably someone was "joking": there are some holes in some windows
versions which allow a host to be restarted / switched off or similar via
network (these are usually closed by sp4 IIRC). So it could even be
manual intervention that caused this...
Kind regards
robert|||Hi Robert
I believe that we can almost rule that one out. The server is behind a
firewall that only allows HTTP in from unknown IP addresses, everything else
is effectively blocked. The only person who can connect to it in any other
way is me.
But hey, we know that the only way to make a computer completely safe is to
switch it off.
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?
>>
>>
>
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...
>
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...
>
Re-indexing required?
Hi, I'm developing a database driven application that, besides everything
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peter
pnp,
When are your maintenance windows? Do you have ANY maintenance windows? If you get a chance it would be good to recreate your indexes using the CREATE INDEX statement and the DROP_EXISTING clause - however test this for performance against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation using DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild your index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not lock tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and then decide when to do it based on your maintenance windows. From the activity you describe it sounds like you may need to monitor it daily with DBCC SHOWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>
|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peter
pnp,
When are your maintenance windows? Do you have ANY maintenance windows? If you get a chance it would be good to recreate your indexes using the CREATE INDEX statement and the DROP_EXISTING clause - however test this for performance against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation using DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild your index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not lock tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and then decide when to do it based on your maintenance windows. From the activity you describe it sounds like you may need to monitor it daily with DBCC SHOWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>
|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
Re-indexing required?
Hi, I'm developing a database driven application that, besides everything
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peterpnp,
When are your maintenance windows? Do you have ANY maintenance windows? If y
ou get a chance it would be good to recreate your indexes using the CREATE I
NDEX statement and the DROP_EXISTING clause - however test this for performa
nce against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation us
ing DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild you
r index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not loc
k tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and
then decide when to do it based on your maintenance windows. From the activi
ty you describe it sounds like you may need to monitor it daily with DBCC SH
OWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peterpnp,
When are your maintenance windows? Do you have ANY maintenance windows? If y
ou get a chance it would be good to recreate your indexes using the CREATE I
NDEX statement and the DROP_EXISTING clause - however test this for performa
nce against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation us
ing DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild you
r index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not loc
k tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and
then decide when to do it based on your maintenance windows. From the activi
ty you describe it sounds like you may need to monitor it daily with DBCC SH
OWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
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.
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
re-indexing "sliding window" partitioned tables - is it necessary?
Hello
We have a partitioning strategy in place where we keep 10 days worth of data
each on its own day partition and a weekly sliding window where we remove old
days and add new days.
These tables only experience INSERTS (Bulk inserts and BCP), no updates or
deletes and our clustered index is also partitioned.
I would like to know if it is necessary to ever check for fragmentation or
re-index this table. My thinking is that it is not as all INSERTS will be
contigiuous.
thanks
--
-- cranfield, DBA> I would like to know if it is necessary to ever check for fragmentation or
> re-index this table. My thinking is that it is not as all INSERTS will be
> contigiuous.
It is likely you have at least some fragmentation unless you load data in
index key order of all indexes. You might consider including an ALTER INDEX
REBUILD or REORGANIZE of the last loaded partition as part of your daily
sliding window maintenance. If you SWITCH a fully loaded table into the
partitioned table, you can reorg before switching in.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:89CC380F-46B3-4E0B-927A-4552627BF667@.microsoft.com...
> Hello
> We have a partitioning strategy in place where we keep 10 days worth of
> data
> each on its own day partition and a weekly sliding window where we remove
> old
> days and add new days.
> These tables only experience INSERTS (Bulk inserts and BCP), no updates or
> deletes and our clustered index is also partitioned.
> I would like to know if it is necessary to ever check for fragmentation or
> re-index this table. My thinking is that it is not as all INSERTS will be
> contigiuous.
> thanks
> --
> -- cranfield, DBA|||Hi Dan
Yes, that makes sense. Our partitioned table gets loaded intra-businessday
and we the window gets moved only once a week on the weekend. We have 7 days
of future partitions always defined. So would you suggest a fragmentation
check at the end of each business day and then a rebuild of the entire index
should there be excessive fragmentation? Our maintenance window is very
small and these partitioned tables have approx 1 mill rows/day.
--
-- cranfield, DBA
"Dan Guzman" wrote:
> > I would like to know if it is necessary to ever check for fragmentation or
> > re-index this table. My thinking is that it is not as all INSERTS will be
> > contigiuous.
> It is likely you have at least some fragmentation unless you load data in
> index key order of all indexes. You might consider including an ALTER INDEX
> REBUILD or REORGANIZE of the last loaded partition as part of your daily
> sliding window maintenance. If you SWITCH a fully loaded table into the
> partitioned table, you can reorg before switching in.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:89CC380F-46B3-4E0B-927A-4552627BF667@.microsoft.com...
> > Hello
> >
> > We have a partitioning strategy in place where we keep 10 days worth of
> > data
> > each on its own day partition and a weekly sliding window where we remove
> > old
> > days and add new days.
> >
> > These tables only experience INSERTS (Bulk inserts and BCP), no updates or
> > deletes and our clustered index is also partitioned.
> >
> > I would like to know if it is necessary to ever check for fragmentation or
> > re-index this table. My thinking is that it is not as all INSERTS will be
> > contigiuous.
> >
> > thanks
> > --
> > -- cranfield, DBA
>|||> So would you suggest a fragmentation
> check at the end of each business day and then a rebuild of the entire
> index
> should there be excessive fragmentation? Our maintenance window is very
> small and these partitioned tables have approx 1 mill rows/day.
Assuming your indexes are aligned, you might consider an unconditional
REBUILD or REORGANIZE of only the last loaded partition since I expect
you'll have about the same level of fragmentation of the newly loaded
partition every day. If you don't have a large enough maintenance window to
REBUILD, you can still REORGANIZE online to reduce fragmentation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:8F5B6484-731A-4B99-8EAC-EBD811B42854@.microsoft.com...
> Hi Dan
> Yes, that makes sense. Our partitioned table gets loaded intra-businessday
> and we the window gets moved only once a week on the weekend. We have 7
> days
> of future partitions always defined. So would you suggest a fragmentation
> check at the end of each business day and then a rebuild of the entire
> index
> should there be excessive fragmentation? Our maintenance window is very
> small and these partitioned tables have approx 1 mill rows/day.
> --
> -- cranfield, DBA
>
> "Dan Guzman" wrote:
>> > I would like to know if it is necessary to ever check for fragmentation
>> > or
>> > re-index this table. My thinking is that it is not as all INSERTS will
>> > be
>> > contigiuous.
>> It is likely you have at least some fragmentation unless you load data in
>> index key order of all indexes. You might consider including an ALTER
>> INDEX
>> REBUILD or REORGANIZE of the last loaded partition as part of your daily
>> sliding window maintenance. If you SWITCH a fully loaded table into the
>> partitioned table, you can reorg before switching in.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:89CC380F-46B3-4E0B-927A-4552627BF667@.microsoft.com...
>> > Hello
>> >
>> > We have a partitioning strategy in place where we keep 10 days worth of
>> > data
>> > each on its own day partition and a weekly sliding window where we
>> > remove
>> > old
>> > days and add new days.
>> >
>> > These tables only experience INSERTS (Bulk inserts and BCP), no updates
>> > or
>> > deletes and our clustered index is also partitioned.
>> >
>> > I would like to know if it is necessary to ever check for fragmentation
>> > or
>> > re-index this table. My thinking is that it is not as all INSERTS will
>> > be
>> > contigiuous.
>> >
>> > thanks
>> > --
>> > -- cranfield, DBA
We have a partitioning strategy in place where we keep 10 days worth of data
each on its own day partition and a weekly sliding window where we remove old
days and add new days.
These tables only experience INSERTS (Bulk inserts and BCP), no updates or
deletes and our clustered index is also partitioned.
I would like to know if it is necessary to ever check for fragmentation or
re-index this table. My thinking is that it is not as all INSERTS will be
contigiuous.
thanks
--
-- cranfield, DBA> I would like to know if it is necessary to ever check for fragmentation or
> re-index this table. My thinking is that it is not as all INSERTS will be
> contigiuous.
It is likely you have at least some fragmentation unless you load data in
index key order of all indexes. You might consider including an ALTER INDEX
REBUILD or REORGANIZE of the last loaded partition as part of your daily
sliding window maintenance. If you SWITCH a fully loaded table into the
partitioned table, you can reorg before switching in.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:89CC380F-46B3-4E0B-927A-4552627BF667@.microsoft.com...
> Hello
> We have a partitioning strategy in place where we keep 10 days worth of
> data
> each on its own day partition and a weekly sliding window where we remove
> old
> days and add new days.
> These tables only experience INSERTS (Bulk inserts and BCP), no updates or
> deletes and our clustered index is also partitioned.
> I would like to know if it is necessary to ever check for fragmentation or
> re-index this table. My thinking is that it is not as all INSERTS will be
> contigiuous.
> thanks
> --
> -- cranfield, DBA|||Hi Dan
Yes, that makes sense. Our partitioned table gets loaded intra-businessday
and we the window gets moved only once a week on the weekend. We have 7 days
of future partitions always defined. So would you suggest a fragmentation
check at the end of each business day and then a rebuild of the entire index
should there be excessive fragmentation? Our maintenance window is very
small and these partitioned tables have approx 1 mill rows/day.
--
-- cranfield, DBA
"Dan Guzman" wrote:
> > I would like to know if it is necessary to ever check for fragmentation or
> > re-index this table. My thinking is that it is not as all INSERTS will be
> > contigiuous.
> It is likely you have at least some fragmentation unless you load data in
> index key order of all indexes. You might consider including an ALTER INDEX
> REBUILD or REORGANIZE of the last loaded partition as part of your daily
> sliding window maintenance. If you SWITCH a fully loaded table into the
> partitioned table, you can reorg before switching in.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:89CC380F-46B3-4E0B-927A-4552627BF667@.microsoft.com...
> > Hello
> >
> > We have a partitioning strategy in place where we keep 10 days worth of
> > data
> > each on its own day partition and a weekly sliding window where we remove
> > old
> > days and add new days.
> >
> > These tables only experience INSERTS (Bulk inserts and BCP), no updates or
> > deletes and our clustered index is also partitioned.
> >
> > I would like to know if it is necessary to ever check for fragmentation or
> > re-index this table. My thinking is that it is not as all INSERTS will be
> > contigiuous.
> >
> > thanks
> > --
> > -- cranfield, DBA
>|||> So would you suggest a fragmentation
> check at the end of each business day and then a rebuild of the entire
> index
> should there be excessive fragmentation? Our maintenance window is very
> small and these partitioned tables have approx 1 mill rows/day.
Assuming your indexes are aligned, you might consider an unconditional
REBUILD or REORGANIZE of only the last loaded partition since I expect
you'll have about the same level of fragmentation of the newly loaded
partition every day. If you don't have a large enough maintenance window to
REBUILD, you can still REORGANIZE online to reduce fragmentation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:8F5B6484-731A-4B99-8EAC-EBD811B42854@.microsoft.com...
> Hi Dan
> Yes, that makes sense. Our partitioned table gets loaded intra-businessday
> and we the window gets moved only once a week on the weekend. We have 7
> days
> of future partitions always defined. So would you suggest a fragmentation
> check at the end of each business day and then a rebuild of the entire
> index
> should there be excessive fragmentation? Our maintenance window is very
> small and these partitioned tables have approx 1 mill rows/day.
> --
> -- cranfield, DBA
>
> "Dan Guzman" wrote:
>> > I would like to know if it is necessary to ever check for fragmentation
>> > or
>> > re-index this table. My thinking is that it is not as all INSERTS will
>> > be
>> > contigiuous.
>> It is likely you have at least some fragmentation unless you load data in
>> index key order of all indexes. You might consider including an ALTER
>> INDEX
>> REBUILD or REORGANIZE of the last loaded partition as part of your daily
>> sliding window maintenance. If you SWITCH a fully loaded table into the
>> partitioned table, you can reorg before switching in.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:89CC380F-46B3-4E0B-927A-4552627BF667@.microsoft.com...
>> > Hello
>> >
>> > We have a partitioning strategy in place where we keep 10 days worth of
>> > data
>> > each on its own day partition and a weekly sliding window where we
>> > remove
>> > old
>> > days and add new days.
>> >
>> > These tables only experience INSERTS (Bulk inserts and BCP), no updates
>> > or
>> > deletes and our clustered index is also partitioned.
>> >
>> > I would like to know if it is necessary to ever check for fragmentation
>> > or
>> > re-index this table. My thinking is that it is not as all INSERTS will
>> > be
>> > contigiuous.
>> >
>> > thanks
>> > --
>> > -- cranfield, DBA
Subscribe to:
Posts (Atom)