Reindex depleting Working Set

I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The Min
Server Memory setting is 0, and Max is set at 14 GB.
When I reindex (ALTER INDEX...REBUILD) a database that is approximately 6GB
in size, the server grinds to a halt and the error log contains errors
similar to the following:
A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 655 seconds. Working set
(KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
Reporting Services is also running on this box, but when the Reindex is
taking place, the Reindex job is the only active SPID.
The "PF Usage" in Task Manager is at 15.3 GB.
Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Hi
Check you are locking pages in memory see:
http://support.microsoft.com/kb/918483
John
"cbrichards via SQLMonster.com" wrote:
> I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The Min
> Server Memory setting is 0, and Max is set at 14 GB.
> When I reindex (ALTER INDEX...REBUILD) a database that is approximately 6GB
> in size, the server grinds to a halt and the error log contains errors
> similar to the following:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 655 seconds. Working set
> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
> Reporting Services is also running on this box, but when the Reindex is
> taking place, the Reindex job is the only active SPID.
> The "PF Usage" in Task Manager is at 15.3 GB.
> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||John,
Not applicable to my situation. Lock Pages in Memory is ignored when running
Standard Edition.
Still in need of help!
John Bell wrote:
>Hi
>Check you are locking pages in memory see:
>http://support.microsoft.com/kb/918483
>John
>> I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The Min
>> Server Memory setting is 0, and Max is set at 14 GB.
>[quoted text clipped - 13 lines]
>> Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Hi
Have you tried lowering the max memory and setting the minimum to the same
value (say 12GB)?
John
"cbrichards via SQLMonster.com" wrote:
> John,
> Not applicable to my situation. Lock Pages in Memory is ignored when running
> Standard Edition.
> Still in need of help!
> John Bell wrote:
> >Hi
> >
> >Check you are locking pages in memory see:
> >
> >http://support.microsoft.com/kb/918483
> >
> >John
> >
> >> I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The Min
> >> Server Memory setting is 0, and Max is set at 14 GB.
> >[quoted text clipped - 13 lines]
> >>
> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||I have heard of that option, but I do not have the understanding of SQL
Servers memory structures to know how that would change my reindexing issue.
From what I understand is happening with my reindexing, is that the
reindexing is consuming all 14GB dedicated to SQL Servers buffer pool and
from there, needs even more memory, which is then going to the Page File.
Whether that is correct or not, I do not know, I just know that the Working
Set is being trimmed.
Are you saying that setting the Max and Min server memory settings to the
same value, that this will eliminate Page File usage when reindexing? I know
it will prevent the Working Set from being trimmed, but does that eliminate
Page File swapping?
John Bell wrote:
>Hi
>Have you tried lowering the max memory and setting the minimum to the same
>value (say 12GB)?
>John
>> John,
>> Not applicable to my situation. Lock Pages in Memory is ignored when running
>[quoted text clipped - 15 lines]
>> >>
>> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Hi
By setting a lower maximum then it may allow other processes to be allocated
memory without SQL Server grabbing/regrabbing it.
John
"cbrichards via SQLMonster.com" wrote:
> I have heard of that option, but I do not have the understanding of SQL
> Servers memory structures to know how that would change my reindexing issue.
> From what I understand is happening with my reindexing, is that the
> reindexing is consuming all 14GB dedicated to SQL Servers buffer pool and
> from there, needs even more memory, which is then going to the Page File.
> Whether that is correct or not, I do not know, I just know that the Working
> Set is being trimmed.
> Are you saying that setting the Max and Min server memory settings to the
> same value, that this will eliminate Page File usage when reindexing? I know
> it will prevent the Working Set from being trimmed, but does that eliminate
> Page File swapping?
> John Bell wrote:
> >Hi
> >
> >Have you tried lowering the max memory and setting the minimum to the same
> >value (say 12GB)?
> >
> >John
> >
> >> John,
> >> Not applicable to my situation. Lock Pages in Memory is ignored when running
> >[quoted text clipped - 15 lines]
> >> >>
> >> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||So you are saying that other processes are chewing up SQL Servers Working Set,
and that by lowering SQL Servers Working Set (from 14Gb to 12Gb) it will
still have enough Working Set memory?
John Bell wrote:
>Hi
>By setting a lower maximum then it may allow other processes to be allocated
>memory without SQL Server grabbing/regrabbing it.
>John
>> I have heard of that option, but I do not have the understanding of SQL
>> Servers memory structures to know how that would change my reindexing issue.
>[quoted text clipped - 22 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via http://www.sqlmonster.com|||WorkingSet is a perfmon counter for _ALL_ demands against virtual memory,
not just SQL Server.
John is suggesting that you reduce the amount that SQL Server is asking for
in the first place.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79ca83aaaea1d@.uwe...
> So you are saying that other processes are chewing up SQL Servers Working
> Set,
> and that by lowering SQL Servers Working Set (from 14Gb to 12Gb) it will
> still have enough Working Set memory?
> John Bell wrote:
>>Hi
>>By setting a lower maximum then it may allow other processes to be
>>allocated
>>memory without SQL Server grabbing/regrabbing it.
>>John
>> I have heard of that option, but I do not have the understanding of SQL
>> Servers memory structures to know how that would change my reindexing
>> issue.
>>[quoted text clipped - 22 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via http://www.sqlmonster.com
>|||Okay, as far as I can tell the reindexing is the only active SPID and this
reindexing is depleting the Working Set.
So you are saying that reducing the amount SQL Server is asking for, will in
turn not deplete the Working Set any more than it is currently? Does this
mean that reducing the amount SQL Server is asking for (reducing from 14GB to
12Gb) will make the Working Set larger?
Jay wrote:
>WorkingSet is a perfmon counter for _ALL_ demands against virtual memory,
>not just SQL Server.
>John is suggesting that you reduce the amount that SQL Server is asking for
>in the first place.
>> So you are saying that other processes are chewing up SQL Servers Working
>> Set,
>[quoted text clipped - 15 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||What do you mean when you say "Working Set"?
I think you mean the (Windows) perfmon counter that measures the total
demands against virtual memory. However, I have no idea how that concept can
include the term "deplete".
Since it measures the TOTAL, if you reduce the amount of memory demanded,
then the TOTAL demanded will also be reduced.
Beyond that, just try what John suggested without completely understanding
everything first. You will probably be happy with the result and once you
see it, you'll understand better.
Jay
PS. I'm not feeling so well, so I doubt I'll repost until tomorrow at the
soonest.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79cc0a5c0e9bb@.uwe...
> Okay, as far as I can tell the reindexing is the only active SPID and this
> reindexing is depleting the Working Set.
> So you are saying that reducing the amount SQL Server is asking for, will
> in
> turn not deplete the Working Set any more than it is currently? Does this
> mean that reducing the amount SQL Server is asking for (reducing from 14GB
> to
> 12Gb) will make the Working Set larger?
> Jay wrote:
>>WorkingSet is a perfmon counter for _ALL_ demands against virtual memory,
>>not just SQL Server.
>>John is suggesting that you reduce the amount that SQL Server is asking
>>for
>>in the first place.
>> So you are saying that other processes are chewing up SQL Servers
>> Working
>> Set,
>>[quoted text clipped - 15 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||When I say "Working Set" I am referring to SQL Servers Working Set, not the
perfmon counter. This is the error in my SQL Error Log that I posted at the
beginning of this thread:
A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 655 seconds. Working set
(KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
And while I believe John's suggestion may be the solution, I really need to
be able to explain it to my team, before I just make a wild configuration
change.
Which gets back to my latest inquiry:
As far as I can tell the reindexing is the only active SPID and this
reindexing is depleting the Working Set.
And you are saying that reducing the amount SQL Server is asking for, will in
turn not deplete the Working Set any more than it is currently? Does this
mean that reducing the amount SQL Server is asking for (reducing from 14GB to
12Gb) will make the Working Set larger?
Jay wrote:
>What do you mean when you say "Working Set"?
>I think you mean the (Windows) perfmon counter that measures the total
>demands against virtual memory. However, I have no idea how that concept can
>include the term "deplete".
>Since it measures the TOTAL, if you reduce the amount of memory demanded,
>then the TOTAL demanded will also be reduced.
>Beyond that, just try what John suggested without completely understanding
>everything first. You will probably be happy with the result and once you
>see it, you'll understand better.
>Jay
>PS. I'm not feeling so well, so I doubt I'll repost until tomorrow at the
>soonest.
>> Okay, as far as I can tell the reindexing is the only active SPID and this
>> reindexing is depleting the Working Set.
>[quoted text clipped - 19 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||cbrichards,
I decided to answer your last post first (below) and then look at the
problem from the beginning. You don't really care about Working Sets, paging
and max memory, you just want to re-index a table.
If you read BOL (SQL Server Books Online) for the page "ALTER INDEX" you
will see it is a rich and fully featured command (I was amazed when I first
read it). There are ways you can make it less intrusive to your system.
At the top of my list would be the following three options:
From BOL:
--
REORGANIZE
Specifies the index leaf level will be reorganized. This clause is
equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always
performed online. This means long-term blocking table locks are not held and
queries or updates to the underlying table can continue during the ALTER
INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled
index or an index with ALLOW_PAGE_LOCKS set to OFF.
-and-
The rebuild operation can be minimally logged if the database recovery model
is set to either bulk-logged or simple. For more information, see Choosing a
Recovery Model for Index Operations.
--
and rebuilding one index at a time, not using the ALL option.
I'm not sure how the logging change will affect memory, but it could easily
help. One thing is for sure, your .ldf file won't bloat.
Reorganize is the option I always go for first when dealing with indexes.
It's lower impact overall and will frequently do the job. There might even
be more options that can help you in there, but I can't tell you because I
don't have a heavy use 2005 server yet (14 2000's and 2 2005's).
DBCC SHOWCONTIG('Table') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS will
tell you the fragmentation within the index.
Also, INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 will eliminate
indexes where the defrag will do no good.
On to current answers.
> When I say "Working Set" I am referring to SQL Servers Working Set, not
> the
> perfmon counter. This is the error in my SQL Error Log that I posted at
> the
> beginning of this thread:
It is the same thing. The Working Set (for the 3rd time) is the TOTAL demand
for memory. Your total memory is RAM + (Page File size - RAM). If you go
over the RAM you have available, you start to page.
Please read the following link. It is a short article and very good.
http://support.microsoft.com/kb/555223
> And while I believe John's suggestion may be the solution, I really need
> to
> be able to explain it to my team, before I just make a wild configuration
> change.
His suggestion was to reduce SQL Server's MAX memory setting so that it
didn't ask for as much from Windows. Hardly a "wild configuration change".
> Which gets back to my latest inquiry:
> As far as I can tell the reindexing is the only active SPID and this
> reindexing is depleting the Working Set.
The SPID you are looking at is in SQL Server and therefore controlled by the
server's settings.
> And you are saying that reducing the amount SQL Server is asking for, will
> in
> turn not deplete the Working Set any more than it is currently? Does this
> mean that reducing the amount SQL Server is asking for (reducing from 14GB
> to
> 12Gb) will make the Working Set larger?
Sigh. No, it will not make the working set larger, it will make it smaller -
which is what you want. You seem to think the Working Set is the pool of
memory you are drawing from, it is not. The Working Set is drawing from
system memory.
> Jay wrote:
>>What do you mean when you say "Working Set"?
>>I think you mean the (Windows) perfmon counter that measures the total
>>demands against virtual memory. However, I have no idea how that concept
>>can
>>include the term "deplete".
>>Since it measures the TOTAL, if you reduce the amount of memory demanded,
>>then the TOTAL demanded will also be reduced.
>>Beyond that, just try what John suggested without completely understanding
>>everything first. You will probably be happy with the result and once you
>>see it, you'll understand better.
>>Jay
>>PS. I'm not feeling so well, so I doubt I'll repost until tomorrow at the
>>soonest.
>> Okay, as far as I can tell the reindexing is the only active SPID and
>> this
>> reindexing is depleting the Working Set.
>>[quoted text clipped - 19 lines]
>>> >> >>
>>> >> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||1) is this a repeatable error?
2) do you have other applications/services running on the server?
3) Is this an HP box? If so, they have a VERY nasty bug in 64 bit in their
iLO system that will flush memory.
4) Check if you are doing any large-file copies at the time. Win2003 64 bit
ALSO has a very nasty bug that will flush memory.
5) only 2GB available is probably not enough even if you don't have other
stuff running other than just the sql relational engine (things such as
reporting services, iis, analysis services, etc, etc)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79bdf69e9af82@.uwe...
>I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The
>Min
> Server Memory setting is 0, and Max is set at 14 GB.
> When I reindex (ALTER INDEX...REBUILD) a database that is approximately
> 6GB
> in size, the server grinds to a halt and the error log contains errors
> similar to the following:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 655 seconds. Working
> set
> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
> Reporting Services is also running on this box, but when the Reindex is
> taking place, the Reindex job is the only active SPID.
> The "PF Usage" in Task Manager is at 15.3 GB.
> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Thanks Jay for the detailed response and the link to RAM, Virtual Memory, and
the Paging File.
I am still not understanding some things.
So the Working Set is drawing from system memory, and when you say system
memory I interpret that as being RAM + Paging File.
In decreasing the Max Server memory I therefore decrease the Working Set
dedicated to SQL Server.
From the following two error messages I read that the sql server process is
utilizing (Max Server or Working Set) memory 0% and 12% respectively.
A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 0 seconds. Working set
(KB): 93180, committed (KB): 15052932, memory utilization: 0%.
A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 328 seconds. Working set
(KB): 609700, committed (KB): 4931236, memory utilization: 12%.
My interpretation to my issue of not being able to reindex, is that this
reindexing job exceeds the amount of RAM available and the OS is moving pages
out of SQL Servers Working Set (comparing the (KB) in the the error log
entries above for the Working Set). If in setting the Max Server memory to a
lower setting decreases the Working Set, and the error log shows the Working
Set as suffering already when under stress, I do not see how lowering Max
Server memory will help.
Please clarify.
Jay wrote:
>cbrichards,
>I decided to answer your last post first (below) and then look at the
>problem from the beginning. You don't really care about Working Sets, paging
>and max memory, you just want to re-index a table.
>If you read BOL (SQL Server Books Online) for the page "ALTER INDEX" you
>will see it is a rich and fully featured command (I was amazed when I first
>read it). There are ways you can make it less intrusive to your system.
>At the top of my list would be the following three options:
>From BOL:
>--
> REORGANIZE
> Specifies the index leaf level will be reorganized. This clause is
>equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always
>performed online. This means long-term blocking table locks are not held and
>queries or updates to the underlying table can continue during the ALTER
>INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled
>index or an index with ALLOW_PAGE_LOCKS set to OFF.
>-and-
>The rebuild operation can be minimally logged if the database recovery model
>is set to either bulk-logged or simple. For more information, see Choosing a
>Recovery Model for Index Operations.
>--
>and rebuilding one index at a time, not using the ALL option.
>I'm not sure how the logging change will affect memory, but it could easily
>help. One thing is for sure, your .ldf file won't bloat.
>Reorganize is the option I always go for first when dealing with indexes.
>It's lower impact overall and will frequently do the job. There might even
>be more options that can help you in there, but I can't tell you because I
>don't have a heavy use 2005 server yet (14 2000's and 2 2005's).
>DBCC SHOWCONTIG('Table') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS will
>tell you the fragmentation within the index.
>Also, INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 will eliminate
>indexes where the defrag will do no good.
>On to current answers.
>> When I say "Working Set" I am referring to SQL Servers Working Set, not
>> the
>> perfmon counter. This is the error in my SQL Error Log that I posted at
>> the
>> beginning of this thread:
>It is the same thing. The Working Set (for the 3rd time) is the TOTAL demand
>for memory. Your total memory is RAM + (Page File size - RAM). If you go
>over the RAM you have available, you start to page.
>Please read the following link. It is a short article and very good.
>http://support.microsoft.com/kb/555223
>> And while I believe John's suggestion may be the solution, I really need
>> to
>> be able to explain it to my team, before I just make a wild configuration
>> change.
>His suggestion was to reduce SQL Server's MAX memory setting so that it
>didn't ask for as much from Windows. Hardly a "wild configuration change".
>> Which gets back to my latest inquiry:
>> As far as I can tell the reindexing is the only active SPID and this
>> reindexing is depleting the Working Set.
>The SPID you are looking at is in SQL Server and therefore controlled by the
>server's settings.
>> And you are saying that reducing the amount SQL Server is asking for, will
>> in
>> turn not deplete the Working Set any more than it is currently? Does this
>> mean that reducing the amount SQL Server is asking for (reducing from 14GB
>> to
>> 12Gb) will make the Working Set larger?
>Sigh. No, it will not make the working set larger, it will make it smaller -
>which is what you want. You seem to think the Working Set is the pool of
>memory you are drawing from, it is not. The Working Set is drawing from
>system memory.
>>What do you mean when you say "Working Set"?
>[quoted text clipped - 21 lines]
>>> >> >>
>>> >> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via http://www.sqlmonster.com|||Before going on (and to continue) I require answers to the following:
1) Why are you doing a reindex? What told you you needed it?
2) Syntax specifically how are you doing it?
3) Why have you completly ignored the posibility of using indexdefrag, or
changing the Recovery model while you reindex?
Please post both your reasons and the ALTER INDEX statement (you may, of
course change the database/table/index names, if you feel those shouldn't be
posted).
> Thanks Jay for the detailed response and the link to RAM, Virtual Memory,
> and
> the Paging File.
> I am still not understanding some things.
Did you study the link?
> So the Working Set is drawing from system memory, and when you say system
> memory I interpret that as being RAM + Paging File.
correct.
> In decreasing the Max Server memory I therefore decrease the Working Set
> dedicated to SQL Server.
Decreasing Max Server memory will decrease the Working Set. However, since
the Working Set is a TOTAL for Windows, we neither know, or care what is
dedicated to SQL Server.
> From the following two error messages I read that the sql server process
> is
> utilizing (Max Server or Working Set) memory 0% and 12% respectively.
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 0 seconds. Working set
> (KB): 93180, committed (KB): 15052932, memory utilization: 0%.
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 328 seconds. Working
> set
> (KB): 609700, committed (KB): 4931236, memory utilization: 12%.
> My interpretation to my issue of not being able to reindex, is that this
> reindexing job exceeds the amount of RAM available and the OS is moving
> pages
> out of SQL Servers Working Set (comparing the (KB) in the the error log
> entries above for the Working Set). If in setting the Max Server memory to
> a
> lower setting decreases the Working Set, and the error log shows the
> Working
> Set as suffering already when under stress, I do not see how lowering Max
> Server memory will help.
Because, decreasing the Max Server memory will reduce the amount of used
memory on the system, thus making more available for other tasks.
AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
> Please clarify.
>
> Jay wrote:
>>cbrichards,
>>I decided to answer your last post first (below) and then look at the
>>problem from the beginning. You don't really care about Working Sets,
>>paging
>>and max memory, you just want to re-index a table.
>>If you read BOL (SQL Server Books Online) for the page "ALTER INDEX" you
>>will see it is a rich and fully featured command (I was amazed when I
>>first
>>read it). There are ways you can make it less intrusive to your system.
>>At the top of my list would be the following three options:
>>From BOL:
>>--
>> REORGANIZE
>> Specifies the index leaf level will be reorganized. This clause is
>>equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always
>>performed online. This means long-term blocking table locks are not held
>>and
>>queries or updates to the underlying table can continue during the ALTER
>>INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a
>>disabled
>>index or an index with ALLOW_PAGE_LOCKS set to OFF.
>>-and-
>>The rebuild operation can be minimally logged if the database recovery
>>model
>>is set to either bulk-logged or simple. For more information, see Choosing
>>a
>>Recovery Model for Index Operations.
>>--
>>and rebuilding one index at a time, not using the ALL option.
>>I'm not sure how the logging change will affect memory, but it could
>>easily
>>help. One thing is for sure, your .ldf file won't bloat.
>>Reorganize is the option I always go for first when dealing with indexes.
>>It's lower impact overall and will frequently do the job. There might even
>>be more options that can help you in there, but I can't tell you because I
>>don't have a heavy use 2005 server yet (14 2000's and 2 2005's).
>>DBCC SHOWCONTIG('Table') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS will
>>tell you the fragmentation within the index.
>>Also, INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 will eliminate
>>indexes where the defrag will do no good.
>>On to current answers.
>> When I say "Working Set" I am referring to SQL Servers Working Set, not
>> the
>> perfmon counter. This is the error in my SQL Error Log that I posted at
>> the
>> beginning of this thread:
>>It is the same thing. The Working Set (for the 3rd time) is the TOTAL
>>demand
>>for memory. Your total memory is RAM + (Page File size - RAM). If you go
>>over the RAM you have available, you start to page.
>>Please read the following link. It is a short article and very good.
>>http://support.microsoft.com/kb/555223
>> And while I believe John's suggestion may be the solution, I really need
>> to
>> be able to explain it to my team, before I just make a wild
>> configuration
>> change.
>>His suggestion was to reduce SQL Server's MAX memory setting so that it
>>didn't ask for as much from Windows. Hardly a "wild configuration change".
>> Which gets back to my latest inquiry:
>> As far as I can tell the reindexing is the only active SPID and this
>> reindexing is depleting the Working Set.
>>The SPID you are looking at is in SQL Server and therefore controlled by
>>the
>>server's settings.
>> And you are saying that reducing the amount SQL Server is asking for,
>> will
>> in
>> turn not deplete the Working Set any more than it is currently? Does
>> this
>> mean that reducing the amount SQL Server is asking for (reducing from
>> 14GB
>> to
>> 12Gb) will make the Working Set larger?
>>Sigh. No, it will not make the working set larger, it will make it
>>smaller -
>>which is what you want. You seem to think the Working Set is the pool of
>>memory you are drawing from, it is not. The Working Set is drawing from
>>system memory.
>>What do you mean when you say "Working Set"?
>>[quoted text clipped - 21 lines]
>>> >> >>
>>> >> >> Any idea what one can do to allow this Reindex to take
>>> >> >> place?
> --
> Message posted via http://www.sqlmonster.com
>|||Before I answer the questions you posed, let me say that it is not just
reindexing that is cratering the server, but queries in general on the server
itself. These queries would not crater the system (I am assuming) if we could
get the tables they referenced, reindexed. But in trying to reindex, the
reindex craters the server. So either way, we are up against a wall.
1a. Why am I doing a reindex?
For the reason most people want to reindex, to get the data they are asking
for returned in a timely and efficient manner.
1b. What told you you needed it?
A. The queries themself taking a long time to return results and at times
degrading overall server performance. Which in turn pointed to index
fragmentation.
B. The following query (when we were lucky enough to get results) confirmed
our suspicions that our indexes were greatly fragmented:
SELECT s.name,
i.name,
ps.page_count,
ps.avg_fragmentation_in_percent,
ps.fragment_count
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
JOIN master.sys.dm_db_index_physical_stats (' + CAST(@.DBID as varchar(max)
) + ', NULL, NULL , NULL, 'LIMITED') ps
ON s.object_id = ps.object_id
AND i.index_id = ps.index_id
WHERE s.type_desc = 'USER_TABLE'
AND i.index_id > 0
AND i.index_id < 255
AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ps.Page_Count >= 100
AND (ps.avg_fragmentation_in_percent >= 5.0
OR ps.fragment_count >= 50)
GROUP BY s.name,
i.name,
ps.page_count,
ps.avg_fragmentation_in_percent,
ps.fragment_count
2. Syntax specifically how are you doing it?
ALTER INDEX ' + @.IndexName + ' ON [' + @.DBName + '].[dbo].[' + @.TableName + ']
REBUILD WITH ( FILLFACTOR = ' + CAST(@.FillFactor AS varchar(3)) + ',
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = OFF )
3. Why have you completly [sic] ignored the posibility [sic] of using
indexdefrag, or changing the Recovery model while you reindex?
I have not ignored the possibility of using indexdefrag. We might give it a
try once we implement new Max/Min Server memory settings. You might say we
are a bit gun shy of making these changes without understanding all the
implications, and additionally, since any resource intense operation seems to
greatly degrade performance, I am a bit hesitant in performing an indexdefrag,
too.
Lastly, the Recovery model on the database is already set to SIMPLE.
Jay wrote:
>Before going on (and to continue) I require answers to the following:
>1) Why are you doing a reindex? What told you you needed it?
>2) Syntax specifically how are you doing it?
>3) Why have you completly ignored the posibility of using indexdefrag, or
>changing the Recovery model while you reindex?
>Please post both your reasons and the ALTER INDEX statement (you may, of
>course change the database/table/index names, if you feel those shouldn't be
>posted).
>> Thanks Jay for the detailed response and the link to RAM, Virtual Memory,
>> and
>> the Paging File.
>> I am still not understanding some things.
>Did you study the link?
>> So the Working Set is drawing from system memory, and when you say system
>> memory I interpret that as being RAM + Paging File.
>correct.
>> In decreasing the Max Server memory I therefore decrease the Working Set
>> dedicated to SQL Server.
>Decreasing Max Server memory will decrease the Working Set. However, since
>the Working Set is a TOTAL for Windows, we neither know, or care what is
>dedicated to SQL Server.
>> From the following two error messages I read that the sql server process
>> is
>[quoted text clipped - 19 lines]
>> Set as suffering already when under stress, I do not see how lowering Max
>> Server memory will help.
>Because, decreasing the Max Server memory will reduce the amount of used
>memory on the system, thus making more available for other tasks.
>AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
>WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
>> Please clarify.
>[quoted text clipped - 107 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Hmm, thank you sir. Three follow up questions:
Have you tried recompiling any procedures to see if it improves performance?
(see sp_recompile in BOL)
Is the drive itself fragmented?
Have you used the Profiler to verify effecient query plans?
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79d74e1593f56@.uwe...
> Before I answer the questions you posed, let me say that it is not just
> reindexing that is cratering the server, but queries in general on the
> server
> itself. These queries would not crater the system (I am assuming) if we
> could
> get the tables they referenced, reindexed. But in trying to reindex, the
> reindex craters the server. So either way, we are up against a wall.
> 1a. Why am I doing a reindex?
> For the reason most people want to reindex, to get the data they are
> asking
> for returned in a timely and efficient manner.
> 1b. What told you you needed it?
> A. The queries themself taking a long time to return results and at times
> degrading overall server performance. Which in turn pointed to index
> fragmentation.
> B. The following query (when we were lucky enough to get results)
> confirmed
> our suspicions that our indexes were greatly fragmented:
> SELECT s.name,
> i.name,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count
> FROM sys.objects s
> JOIN sys.indexes i
> ON s.object_id = i.object_id
> JOIN master.sys.dm_db_index_physical_stats (' + CAST(@.DBID as
> varchar(max)
> ) + ', NULL, NULL , NULL, 'LIMITED') ps
> ON s.object_id = ps.object_id
> AND i.index_id = ps.index_id
> WHERE s.type_desc = 'USER_TABLE'
> AND i.index_id > 0
> AND i.index_id < 255
> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
> AND ps.Page_Count >= 100
> AND (ps.avg_fragmentation_in_percent >= 5.0
> OR ps.fragment_count >= 50)
> GROUP BY s.name,
> i.name,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count
> 2. Syntax specifically how are you doing it?
> ALTER INDEX ' + @.IndexName + ' ON [' + @.DBName + '].[dbo].[' + @.TableName
> + ']
> REBUILD WITH ( FILLFACTOR = ' + CAST(@.FillFactor AS varchar(3)) + ',
> SORT_IN_TEMPDB = ON,
> STATISTICS_NORECOMPUTE = OFF )
> 3. Why have you completly [sic] ignored the posibility [sic] of using
> indexdefrag, or changing the Recovery model while you reindex?
> I have not ignored the possibility of using indexdefrag. We might give it
> a
> try once we implement new Max/Min Server memory settings. You might say we
> are a bit gun shy of making these changes without understanding all the
> implications, and additionally, since any resource intense operation seems
> to
> greatly degrade performance, I am a bit hesitant in performing an
> indexdefrag,
> too.
> Lastly, the Recovery model on the database is already set to SIMPLE.
> Jay wrote:
>>Before going on (and to continue) I require answers to the following:
>>1) Why are you doing a reindex? What told you you needed it?
>>2) Syntax specifically how are you doing it?
>>3) Why have you completly ignored the posibility of using indexdefrag, or
>>changing the Recovery model while you reindex?
>>Please post both your reasons and the ALTER INDEX statement (you may, of
>>course change the database/table/index names, if you feel those shouldn't
>>be
>>posted).
>> Thanks Jay for the detailed response and the link to RAM, Virtual
>> Memory,
>> and
>> the Paging File.
>> I am still not understanding some things.
>>Did you study the link?
>> So the Working Set is drawing from system memory, and when you say
>> system
>> memory I interpret that as being RAM + Paging File.
>>correct.
>> In decreasing the Max Server memory I therefore decrease the Working Set
>> dedicated to SQL Server.
>>Decreasing Max Server memory will decrease the Working Set. However, since
>>the Working Set is a TOTAL for Windows, we neither know, or care what is
>>dedicated to SQL Server.
>> From the following two error messages I read that the sql server process
>> is
>>[quoted text clipped - 19 lines]
>> Set as suffering already when under stress, I do not see how lowering
>> Max
>> Server memory will help.
>>Because, decreasing the Max Server memory will reduce the amount of used
>>memory on the system, thus making more available for other tasks.
>>AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
>>WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
>> Please clarify.
>>[quoted text clipped - 107 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Thanks again Jay. Your tolerance on this matter is appreciated. Getting to
your questions:
1. Have you tried recompiling any procedures to see if it improves
performance?
(see sp_recompile in BOL)
No, this has not been attempted. The tables are mostly archive data, and for
now, we mostly compose ad-hoc queries to mine the data when we need it.
Is the drive itself fragmented?
I do not believe it is very fragmented, as the server was newly built as 64
bit, and has only been in operation for 30 days. Our files are set to auto
grow, but they have not grown since they were created, as we grew them to
begin with, with ample room to start.
Have you used the Profiler to verify effecient query plans?
No, I did not put a trace on my reindex attempt, and the other ad-hoc query
that greatly degraded performance I did not either. I would be interested in
the query plan output, but the degredation after converting to 64 bit has me
focusing my time more on understanding the nuances of 64 bit versus 32 bit.
Jay wrote:
>Hmm, thank you sir. Three follow up questions:
>Have you tried recompiling any procedures to see if it improves performance?
>(see sp_recompile in BOL)
>Is the drive itself fragmented?
>Have you used the Profiler to verify effecient query plans?
>> Before I answer the questions you posed, let me say that it is not just
>> reindexing that is cratering the server, but queries in general on the
>[quoted text clipped - 121 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Jay,
You are probably quite put out with my understanding of the Working Set, and
understandably so. My thinking that the SQL Server has it's own Working Set
came from the following blog:
http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx
The second paragraph reads:
"First - let's define what exactly "Working Set" is. The working set of a
program is a collection of those pages in its virtual address space that have
been recently referenced. This includes both shared and private data. The
shared data includes pages that contain all instructions an application
executes, including those in its own DLL's and the system DLL's. As the
working set size increases, memory demand increases. A process has an
associated minimum working set size and maximum working set size. Each time
a process is created, it reserves the minimum working set size for the
process. The virtual memory manager attempts to keep enough memory for the
minimum working set resident when the process is active, but keeps no more
than the maximum size."
The following statements from the above paragraph led me to believe there was
a Working Set for each process, rather than a single Working Set shared by
all processes, which I believe you have stated. Nevertheless, the following
clips from the above paragraph led me to believe that SQL Server has its own
Working Set:
1. "The working set of a program"
2. "Each time a process is created, it reserves the minimum working set size
for the process."
There are other references in the article that support multiple Working Sets,
or a Working Set per process, such as: "If available server memory drops
below 100MB, then the Memory Manager will trim the Working Set of all
processes."
You may slap me alongside the head with a wet fish, if you please. I am just
confused and seeking for better understanding as to how, setting the Min/Max
Memory server settings to the same value and reducing the Max Memory Server
setting will help reduce the following error log entries:
"A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 655 seconds. Working set
(KB): 1009520, committed (KB): 7169156, memory utilization: 14%."
Thanks again for sharing your knowledge and tolerating my ignorance.
Jay wrote:
>Before going on (and to continue) I require answers to the following:
>1) Why are you doing a reindex? What told you you needed it?
>2) Syntax specifically how are you doing it?
>3) Why have you completly ignored the posibility of using indexdefrag, or
>changing the Recovery model while you reindex?
>Please post both your reasons and the ALTER INDEX statement (you may, of
>course change the database/table/index names, if you feel those shouldn't be
>posted).
>> Thanks Jay for the detailed response and the link to RAM, Virtual Memory,
>> and
>> the Paging File.
>> I am still not understanding some things.
>Did you study the link?
>> So the Working Set is drawing from system memory, and when you say system
>> memory I interpret that as being RAM + Paging File.
>correct.
>> In decreasing the Max Server memory I therefore decrease the Working Set
>> dedicated to SQL Server.
>Decreasing Max Server memory will decrease the Working Set. However, since
>the Working Set is a TOTAL for Windows, we neither know, or care what is
>dedicated to SQL Server.
>> From the following two error messages I read that the sql server process
>> is
>[quoted text clipped - 19 lines]
>> Set as suffering already when under stress, I do not see how lowering Max
>> Server memory will help.
>Because, decreasing the Max Server memory will reduce the amount of used
>memory on the system, thus making more available for other tasks.
>AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
>WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
>> Please clarify.
>[quoted text clipped - 107 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
--
Message posted via http://www.sqlmonster.com|||The quote from the blog is correct and led to a unfortunate
misunderstanding.
In the second paragraph it says:
"As the working set size increases, memory demand increases."
Meaning that it is not a fixed resource, but variable to an outside pool.
Beyond that, it does indeed talk about the working set for seperate
processes, which makes sense. Its just that when speaking about paging and
running out of system memory, it is generally assumed that you're looking at
the total in Windows.
Actually, after reading the link, if I knew about it before hand, I would
have sent it to you as it supports John's suggestion.
However, I'm not so sure that reindexing will solve your problem. I think
it's in the query plan, but not sure exactly why (of if) it is an issue.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79d8a46a06185@.uwe...
> Jay,
> You are probably quite put out with my understanding of the Working Set,
> and
> understandably so. My thinking that the SQL Server has it's own Working
> Set
> came from the following blog:
> http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx
>
> The second paragraph reads:
> "First - let's define what exactly "Working Set" is. The working set of a
> program is a collection of those pages in its virtual address space that
> have
> been recently referenced. This includes both shared and private data.
> The
> shared data includes pages that contain all instructions an application
> executes, including those in its own DLL's and the system DLL's. As the
> working set size increases, memory demand increases. A process has an
> associated minimum working set size and maximum working set size. Each
> time
> a process is created, it reserves the minimum working set size for the
> process. The virtual memory manager attempts to keep enough memory for
> the
> minimum working set resident when the process is active, but keeps no more
> than the maximum size."
> The following statements from the above paragraph led me to believe there
> was
> a Working Set for each process, rather than a single Working Set shared by
> all processes, which I believe you have stated. Nevertheless, the
> following
> clips from the above paragraph led me to believe that SQL Server has its
> own
> Working Set:
> 1. "The working set of a program"
> 2. "Each time a process is created, it reserves the minimum working set
> size
> for the process."
> There are other references in the article that support multiple Working
> Sets,
> or a Working Set per process, such as: "If available server memory drops
> below 100MB, then the Memory Manager will trim the Working Set of all
> processes."
> You may slap me alongside the head with a wet fish, if you please. I am
> just
> confused and seeking for better understanding as to how, setting the
> Min/Max
> Memory server settings to the same value and reducing the Max Memory
> Server
> setting will help reduce the following error log entries:
> "A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 655 seconds. Working
> set
> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%."
> Thanks again for sharing your knowledge and tolerating my ignorance.
>
> Jay wrote:
>>Before going on (and to continue) I require answers to the following:
>>1) Why are you doing a reindex? What told you you needed it?
>>2) Syntax specifically how are you doing it?
>>3) Why have you completly ignored the posibility of using indexdefrag, or
>>changing the Recovery model while you reindex?
>>Please post both your reasons and the ALTER INDEX statement (you may, of
>>course change the database/table/index names, if you feel those shouldn't
>>be
>>posted).
>> Thanks Jay for the detailed response and the link to RAM, Virtual
>> Memory,
>> and
>> the Paging File.
>> I am still not understanding some things.
>>Did you study the link?
>> So the Working Set is drawing from system memory, and when you say
>> system
>> memory I interpret that as being RAM + Paging File.
>>correct.
>> In decreasing the Max Server memory I therefore decrease the Working Set
>> dedicated to SQL Server.
>>Decreasing Max Server memory will decrease the Working Set. However, since
>>the Working Set is a TOTAL for Windows, we neither know, or care what is
>>dedicated to SQL Server.
>> From the following two error messages I read that the sql server process
>> is
>>[quoted text clipped - 19 lines]
>> Set as suffering already when under stress, I do not see how lowering
>> Max
>> Server memory will help.
>>Because, decreasing the Max Server memory will reduce the amount of used
>>memory on the system, thus making more available for other tasks.
>>AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
>>WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
>> Please clarify.
>>[quoted text clipped - 107 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
> --
> Message posted via http://www.sqlmonster.com
>|||Hi cbrichards,
Any movement?
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79bdf69e9af82@.uwe...
>I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The
>Min
> Server Memory setting is 0, and Max is set at 14 GB.
> When I reindex (ALTER INDEX...REBUILD) a database that is approximately
> 6GB
> in size, the server grinds to a halt and the error log contains errors
> similar to the following:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 655 seconds. Working
> set
> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
> Reporting Services is also running on this box, but when the Reindex is
> taking place, the Reindex job is the only active SPID.
> The "PF Usage" in Task Manager is at 15.3 GB.
> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Jay,
Check this article out:
918483 How to reduce paging of buffer pool memory in the 64-bit version of
SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483
There are also some drivers that are known to cause this issue, like the the
iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard
is known to cause this issue on x64 editions of SQL Server 2005.
Also some of the Broadcom drivers , so apart from setting the locked pages
in memory option to on, I would also make sure you are running the latest
drivers for your hardware and software if possible.
HTH
"Jay" <nospan@.nospam.org> wrote in message
news:%23dipTOMFIHA.3716@.TK2MSFTNGP03.phx.gbl...
> Hi cbrichards,
> Any movement?
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:79bdf69e9af82@.uwe...
>>I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The
>>Min
>> Server Memory setting is 0, and Max is set at 14 GB.
>> When I reindex (ALTER INDEX...REBUILD) a database that is approximately
>> 6GB
>> in size, the server grinds to a halt and the error log contains errors
>> similar to the following:
>> A significant part of sql server process memory has been paged out. This
>> may result in a performance degradation. Duration: 655 seconds. Working
>> set
>> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
>> Reporting Services is also running on this box, but when the Reindex is
>> taking place, the Reindex job is the only active SPID.
>> The "PF Usage" in Task Manager is at 15.3 GB.
>> Any idea what one can do to allow this Reindex to take place?
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>