I am using the desktop sqlexpress 2005 and I was experimenting with the account setting and I accidentally disabled my access. I am using windows authentication and there is no password, just the windows login name.
How can I reactivate the login for this connection. Any help is greatly appreciated.Log on as administrator, then try.sql
Showing posts with label setting. Show all posts
Showing posts with label setting. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
LOCK REQUEST TIME OUT PERIOD EXCEEDED
It seems the table is locked by one process for a longer
time, and this has forced other process to throw the
error 1222.
The LOCK_TIMEOUT setting allows an application to set a
maximum time that a statement waits on a blocked
resource. When a statement has waited longer than the
LOCK_TIMEOUT setting, the blocked statement is canceled
automatically, and error message 1222 "Lock request time-
out period exceeded" is returned to the application.
I think if after every 1000 inserts, if you commit the
transaction, then the resource will not be locked for a
longer duration.
regds,
Shrikant Patil,
MCDBA
>--Original Message--
>Got a VB6 program creating records in an SQL 2000 DB. I
have 5 client
>machines creating 100,000 records each in the same table
at the same time.
>On a couple of machine I get the error "LOCK REQUEST
TIME OUT PERIOD
>EXCEEDED". This happened maybe 4 times. Would this be
due to a network or
>hardware limitation or is it a SQL DB factor maybe ?
>The SQL server is only a P4 with 512 ram.
>Clients machines vary greatly and run 2000pro and XPpro.
>Thanks for any pointers.
>Scott.
>
>.
>
i see.
so the error was recived because one clinet/process was ready to commit 1000
records while another clinet/process was in the process of commiting - hence
the error.
the chances that a clinet machine would recive this with our software in
practice is very remotei guess as they would probablty never create that
many records at the same time to the same table. even if they did the RETRY
option seems to deal with it well anyway.
Is there other locking methods that can be employed rather than the 1000
batch one ?
Thanks for your time
Scott
time, and this has forced other process to throw the
error 1222.
The LOCK_TIMEOUT setting allows an application to set a
maximum time that a statement waits on a blocked
resource. When a statement has waited longer than the
LOCK_TIMEOUT setting, the blocked statement is canceled
automatically, and error message 1222 "Lock request time-
out period exceeded" is returned to the application.
I think if after every 1000 inserts, if you commit the
transaction, then the resource will not be locked for a
longer duration.
regds,
Shrikant Patil,
MCDBA
>--Original Message--
>Got a VB6 program creating records in an SQL 2000 DB. I
have 5 client
>machines creating 100,000 records each in the same table
at the same time.
>On a couple of machine I get the error "LOCK REQUEST
TIME OUT PERIOD
>EXCEEDED". This happened maybe 4 times. Would this be
due to a network or
>hardware limitation or is it a SQL DB factor maybe ?
>The SQL server is only a P4 with 512 ram.
>Clients machines vary greatly and run 2000pro and XPpro.
>Thanks for any pointers.
>Scott.
>
>.
>
i see.
so the error was recived because one clinet/process was ready to commit 1000
records while another clinet/process was in the process of commiting - hence
the error.
the chances that a clinet machine would recive this with our software in
practice is very remotei guess as they would probablty never create that
many records at the same time to the same table. even if they did the RETRY
option seems to deal with it well anyway.
Is there other locking methods that can be employed rather than the 1000
batch one ?
Thanks for your time
Scott
Monday, March 26, 2012
Lock Pages in Memory
My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
Lock Pages in Memory setting is ignored.
We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
configuration). The only other application sharing SQL Server resources is
CLR.
With the Lock Pages in Memory setting ignored, does this mean that the 14GB
dedicated to SQL Server is fair game, to be taken as desired by other
processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Still hoping for an answer.
We had a server running 32bit with 2 GB RAM, converted it to 64bit with 16GB
RAM (14GB Max Server Memory) and in less than a week we are getting the
following error in our error log:
"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):
117484, committed (KB): 11856700, memory utilization: 0%."
We have not made any modifications to MemToLeave, so it is at the default
(256MB?). But from what I understand, 64bit gives you a huge extended memory.
So we have not done anything to MemToLeave.
Why do we have memory issues with 16GB RAM, 64bit, but did not have memory
issues on 2GB with 32 bit?
cbrichards wrote:
>My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
>Lock Pages in Memory setting is ignored.
>We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
>configuration). The only other application sharing SQL Server resources is
>CLR.
>With the Lock Pages in Memory setting ignored, does this mean that the 14GB
>dedicated to SQL Server is fair game, to be taken as desired by other
>processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||Hello there!
Have you read the following document?
http://support.microsoft.com/default.aspx/kb/918483
Ekrem Ã?nsoy
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7925eb5963cda@.uwe...
> My understanding is that running 64 bit SQL Server 2005 Standard Edition,
> the
> Lock Pages in Memory setting is ignored.
> We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server
> Memory
> configuration). The only other application sharing SQL Server resources is
> CLR.
> With the Lock Pages in Memory setting ignored, does this mean that the
> 14GB
> dedicated to SQL Server is fair game, to be taken as desired by other
> processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||I highly recommend you read the KB that was posted by Ekrem. I don't
believe Std Edition supports Lock Pages in Memory but you have to understand
there is a huge difference in how memory is utilized between 32 and 64 bit
and even SQL2000 and 2005. These messages are not related to the MemToLeave
area. That memory is pre-allocated and will not be paged out. But unlike in
32 bit the 64 bit SQL Server can utilize all of the memory for things
normally confined to the 2 or 3GB area in a 32 env and can use considerably
more memory than before. For instance the procedure cache would have been
limited to less than 2GB on your old server. Now it can grow at times to as
much as 75% ( exact amount changes with service packs and current conditions
and this is peak, usually won't be more than 50%) of total available memory.
This memory is all dynamic by default and if windows needs more it can call
for memory from SQL Server which will give you the messages you are seeing.
My guess is that you have a lot of adhoc SQL and the proc cache is huge due
to all the non-reusable plans. And or you have operations other than SQL
Server itself running on the server such as SSIS, CLR, Term Services,
Winzip, Notepad etc.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7929227ce194c@.uwe...
> Still hoping for an answer.
> We had a server running 32bit with 2 GB RAM, converted it to 64bit with
> 16GB
> RAM (14GB Max Server Memory) and in less than a week we are getting the
> following error in our error log:
> "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):
> 117484, committed (KB): 11856700, memory utilization: 0%."
> We have not made any modifications to MemToLeave, so it is at the default
> (256MB?). But from what I understand, 64bit gives you a huge extended
> memory.
> So we have not done anything to MemToLeave.
> Why do we have memory issues with 16GB RAM, 64bit, but did not have memory
> issues on 2GB with 32 bit?
> cbrichards wrote:
>>My understanding is that running 64 bit SQL Server 2005 Standard Edition,
>>the
>>Lock Pages in Memory setting is ignored.
>>We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server
>>Memory
>>configuration). The only other application sharing SQL Server resources is
>>CLR.
>>With the Lock Pages in Memory setting ignored, does this mean that the
>>14GB
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via http://www.sqlmonster.com
>|||Well, Hello Ekrem!
I have read that document, and that applies to SQL Server 2005 64 bit
Enterprise Edition. In SQL Server 2005 64 bit Standard Edition (which I noted
in my post, is what we run), that Lock Pages in Memory is ignored.
So, I am still, seeking for answers...
Ekrem Ã?nsoy wrote:
>Hello there!
>Have you read the following document?
>http://support.microsoft.com/default.aspx/kb/918483
>> My understanding is that running 64 bit SQL Server 2005 Standard Edition,
>> the
>[quoted text clipped - 9 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||So now the question is, if I cannot lock pages in memory, then is my only
choice to upgrade to Enterprise edition?
cbrichards wrote:
>Well, Hello Ekrem!
>I have read that document, and that applies to SQL Server 2005 64 bit
>Enterprise Edition. In SQL Server 2005 64 bit Standard Edition (which I noted
>in my post, is what we run), that Lock Pages in Memory is ignored.
>So, I am still, seeking for answers...
>>Hello there!
>[quoted text clipped - 6 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||Thanks Andrew for the information.
From what you wrote, I believe our server ran out of memory due to an export
of data from a 250,000,000 row table. This table holds 90 days worth of
archive data and the "adhoc" query was selecting 1 day to export.
I am still perplexed, because this same archive table was (a week ago) on a
32 bit server with 2 GB RAM, and nothing like this ever happened (running out
of memory). The volume of data, number of stored procedures, user connections,
etc., etc. are all the same as when it was on a 32 bit server. One month ago
I performed this same export when it was 32 bit, and while the CPU escalated,
the server did not crater.
I am having difficulty wrapping my arms around the fact that the server did
not crater under 32 bit and 2 GB RAM, but the same operation cratered 64 bit
with 16 GB RAM.
The following came from running DBCC MemoryStatus:
Procedure Cache Value
-- --
TotalProcs 88
TotalPages 2086
InUsePages 70
Any further insights would be appreciated. We are nervous to move forward
with 64 bit Standard Edition with all these unknowns.
Andrew J. Kelly wrote:
>I highly recommend you read the KB that was posted by Ekrem. I don't
>believe Std Edition supports Lock Pages in Memory but you have to understand
>there is a huge difference in how memory is utilized between 32 and 64 bit
>and even SQL2000 and 2005. These messages are not related to the MemToLeave
>area. That memory is pre-allocated and will not be paged out. But unlike in
>32 bit the 64 bit SQL Server can utilize all of the memory for things
>normally confined to the 2 or 3GB area in a 32 env and can use considerably
>more memory than before. For instance the procedure cache would have been
>limited to less than 2GB on your old server. Now it can grow at times to as
>much as 75% ( exact amount changes with service packs and current conditions
>and this is peak, usually won't be more than 50%) of total available memory.
>This memory is all dynamic by default and if windows needs more it can call
>for memory from SQL Server which will give you the messages you are seeing.
>My guess is that you have a lot of adhoc SQL and the proc cache is huge due
>to all the non-reusable plans. And or you have operations other than SQL
>Server itself running on the server such as SSIS, CLR, Term Services,
>Winzip, Notepad etc.
>> Still hoping for an answer.
>[quoted text clipped - 30 lines]
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||OK now we are getting somewhere. You keep feeding bits and pieces to us but
it would be great to have a more detailed description of the issue and
circumstances. Exactly how are you doing this data export? Is it SSIS? If
so that explains a lot. SSIS is a totally separate process from SQL Server
and it will use it's own memory space just like another app on the server.
On a 32 bit machine it can only use up to 2GB max but probably a lot less.
On the 64 bit it can use all it wants. Since the memory is dynamic it will
compete with SQL Server for sure if run on the same machine. SSIS like to do
the work totally in memory if at all possible and can use a lot more than
you might expect. If you provide more details we can give a more directed or
intelligent answer. Also if you do something like this routinely it sounds
like you may want to partition the data to suite these needs.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79322e221281d@.uwe...
> Thanks Andrew for the information.
> From what you wrote, I believe our server ran out of memory due to an
> export
> of data from a 250,000,000 row table. This table holds 90 days worth of
> archive data and the "adhoc" query was selecting 1 day to export.
> I am still perplexed, because this same archive table was (a week ago) on
> a
> 32 bit server with 2 GB RAM, and nothing like this ever happened (running
> out
> of memory). The volume of data, number of stored procedures, user
> connections,
> etc., etc. are all the same as when it was on a 32 bit server. One month
> ago
> I performed this same export when it was 32 bit, and while the CPU
> escalated,
> the server did not crater.
> I am having difficulty wrapping my arms around the fact that the server
> did
> not crater under 32 bit and 2 GB RAM, but the same operation cratered 64
> bit
> with 16 GB RAM.
> The following came from running DBCC MemoryStatus:
> Procedure Cache Value
> -- --
> TotalProcs 88
> TotalPages 2086
> InUsePages 70
> Any further insights would be appreciated. We are nervous to move forward
> with 64 bit Standard Edition with all these unknowns.
> Andrew J. Kelly wrote:
>>I highly recommend you read the KB that was posted by Ekrem. I don't
>>believe Std Edition supports Lock Pages in Memory but you have to
>>understand
>>there is a huge difference in how memory is utilized between 32 and 64 bit
>>and even SQL2000 and 2005. These messages are not related to the
>>MemToLeave
>>area. That memory is pre-allocated and will not be paged out. But unlike
>>in
>>32 bit the 64 bit SQL Server can utilize all of the memory for things
>>normally confined to the 2 or 3GB area in a 32 env and can use
>>considerably
>>more memory than before. For instance the procedure cache would have been
>>limited to less than 2GB on your old server. Now it can grow at times to
>>as
>>much as 75% ( exact amount changes with service packs and current
>>conditions
>>and this is peak, usually won't be more than 50%) of total available
>>memory.
>>This memory is all dynamic by default and if windows needs more it can
>>call
>>for memory from SQL Server which will give you the messages you are
>>seeing.
>>My guess is that you have a lot of adhoc SQL and the proc cache is huge
>>due
>>to all the non-reusable plans. And or you have operations other than SQL
>>Server itself running on the server such as SSIS, CLR, Term Services,
>>Winzip, Notepad etc.
>> Still hoping for an answer.
>>[quoted text clipped - 30 lines]
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
> --
> Message posted via http://www.sqlmonster.com
>|||> Is Max Server Memory even needed in this case?
Personally, I'd always set Max Server Memory and Min Server Memory on any
serious instance. Why let the SQL Server process engage in expensive back and
forth memory trading with OS?
Linchi
"cbrichards via SQLMonster.com" wrote:
> My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
> Lock Pages in Memory setting is ignored.
> We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
> configuration). The only other application sharing SQL Server resources is
> CLR.
> With the Lock Pages in Memory setting ignored, does this mean that the 14GB
> dedicated to SQL Server is fair game, to be taken as desired by other
> processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Interesting...
I am using SSIS to perform the export.
This adds more questions:
1. You stated, "On a 32 bit machine it can only use up to 2GB max but
probably a lot less." Why only 2GB and probably a lot less?
2. You stated, "On the 64 bit it can use all it wants. Since the memory is
dynamic..." Is this true even when using Enterprise edition when you can lock
pages in memory?
Andrew J. Kelly wrote:
>OK now we are getting somewhere. You keep feeding bits and pieces to us but
>it would be great to have a more detailed description of the issue and
>circumstances. Exactly how are you doing this data export? Is it SSIS? If
>so that explains a lot. SSIS is a totally separate process from SQL Server
>and it will use it's own memory space just like another app on the server.
>On a 32 bit machine it can only use up to 2GB max but probably a lot less.
>On the 64 bit it can use all it wants. Since the memory is dynamic it will
>compete with SQL Server for sure if run on the same machine. SSIS like to do
>the work totally in memory if at all possible and can use a lot more than
>you might expect. If you provide more details we can give a more directed or
>intelligent answer. Also if you do something like this routinely it sounds
>like you may want to partition the data to suite these needs.
>> Thanks Andrew for the information.
>[quoted text clipped - 65 lines]
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||> This adds more questions:
> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
> probably a lot less." Why only 2GB and probably a lot less?
Well if the system only had 2GB you need memory for other things as well.
But in 32 bit OS by default any app can only use up to 2GB of directly
addressable memory. If you had more than 2GB and the OS was capable of using
PAE and the app was AWE aware it may be able to use more than 2GB. But I
don't think SSIS was AWE aware so on 32 bit I believe it was only able to
use 2GB or 3GB if /3gb was set max. I could be wrong there but I am pretty
sure that is correct.
> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
> dynamic..." Is this true even when using Enterprise edition when you can
> lock
> pages in memory?
No. The purpose of Lock Pages is to prevent something else from stealing or
borrowing those pages once they are allocated. You can achieve a similar
functionality as Linchi mentioned by setting the MIN and MAX to the same
value. Then as long as you start SQL Server, use that much memory and the
memory is available it will keep it once it has used it. But now you have
another potentially serious issue and that is with the memory that is left.
If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the OS
and anything else you run. If you attempt to run that SSIS package again in
this mode you will surely starve the OS of memory and the machine and
everything on it will not be happy. This is one of the main reasons why it
is recommended you run larger SSIS packages on a separate machine.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79367cc8cda07@.uwe...
> Interesting...
> I am using SSIS to perform the export.
> This adds more questions:
> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
> probably a lot less." Why only 2GB and probably a lot less?
> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
> dynamic..." Is this true even when using Enterprise edition when you can
> lock
> pages in memory?
> Andrew J. Kelly wrote:
>>OK now we are getting somewhere. You keep feeding bits and pieces to us
>>but
>>it would be great to have a more detailed description of the issue and
>>circumstances. Exactly how are you doing this data export? Is it SSIS?
>>If
>>so that explains a lot. SSIS is a totally separate process from SQL Server
>>and it will use it's own memory space just like another app on the server.
>>On a 32 bit machine it can only use up to 2GB max but probably a lot less.
>>On the 64 bit it can use all it wants. Since the memory is dynamic it will
>>compete with SQL Server for sure if run on the same machine. SSIS like to
>>do
>>the work totally in memory if at all possible and can use a lot more than
>>you might expect. If you provide more details we can give a more directed
>>or
>>intelligent answer. Also if you do something like this routinely it
>>sounds
>>like you may want to partition the data to suite these needs.
>> Thanks Andrew for the information.
>>[quoted text clipped - 65 lines]
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||> 1. So, in my case, if SSIS were to be run on a separate machine, would it
> not
> equally starve the Memory on the separate machine?
If those are the only two processes on the machine they would live much
better together than with SQL Server and lock pages.
> 2. When running this SSIS export on the same machine, (loosely speaking)
> does
> SSIS first consume the 14GB, then go after the 2GB of the OS?
I don't know the allocations work at that level but I would say there are
factors that would make it a DEPENDS type of answer.
> 3. At what point does the 8TB of virtual address space get used.
Virtual address space is always used but if you mean when does it start
swapping to disk? Then generally when all the physical memory is depleted.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:793df7e614c62@.uwe...
> This raises a few more questions:
> 1. So, in my case, if SSIS were to be run on a separate machine, would it
> not
> equally starve the Memory on the separate machine?
> 2. When running this SSIS export on the same machine, (loosely speaking)
> does
> SSIS first consume the 14GB, then go after the 2GB of the OS?
> 3. At what point does the 8TB of virtual address space get used.
> Andrew J. Kelly wrote:
>> This adds more questions:
>> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
>> probably a lot less." Why only 2GB and probably a lot less?
>>Well if the system only had 2GB you need memory for other things as well.
>>But in 32 bit OS by default any app can only use up to 2GB of directly
>>addressable memory. If you had more than 2GB and the OS was capable of
>>using
>>PAE and the app was AWE aware it may be able to use more than 2GB. But I
>>don't think SSIS was AWE aware so on 32 bit I believe it was only able to
>>use 2GB or 3GB if /3gb was set max. I could be wrong there but I am
>>pretty
>>sure that is correct.
>> 2. You stated, "On the 64 bit it can use all it wants. Since the memory
>> is
>> dynamic..." Is this true even when using Enterprise edition when you can
>> lock
>> pages in memory?
>>No. The purpose of Lock Pages is to prevent something else from stealing
>>or
>>borrowing those pages once they are allocated. You can achieve a similar
>>functionality as Linchi mentioned by setting the MIN and MAX to the same
>>value. Then as long as you start SQL Server, use that much memory and the
>>memory is available it will keep it once it has used it. But now you have
>>another potentially serious issue and that is with the memory that is
>>left.
>>If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the
>>OS
>>and anything else you run. If you attempt to run that SSIS package again
>>in
>>this mode you will surely starve the OS of memory and the machine and
>>everything on it will not be happy. This is one of the main reasons why
>>it
>>is recommended you run larger SSIS packages on a separate machine.
>> Interesting...
>>[quoted text clipped - 32 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
> --
> Message posted via http://www.sqlmonster.com
>|||This raises a few more questions:
1. So, in my case, if SSIS were to be run on a separate machine, would it not
equally starve the Memory on the separate machine?
2. When running this SSIS export on the same machine, (loosely speaking) does
SSIS first consume the 14GB, then go after the 2GB of the OS?
3. At what point does the 8TB of virtual address space get used.
Andrew J. Kelly wrote:
>> This adds more questions:
>> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
>> probably a lot less." Why only 2GB and probably a lot less?
>Well if the system only had 2GB you need memory for other things as well.
>But in 32 bit OS by default any app can only use up to 2GB of directly
>addressable memory. If you had more than 2GB and the OS was capable of using
>PAE and the app was AWE aware it may be able to use more than 2GB. But I
>don't think SSIS was AWE aware so on 32 bit I believe it was only able to
>use 2GB or 3GB if /3gb was set max. I could be wrong there but I am pretty
>sure that is correct.
>> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
>> dynamic..." Is this true even when using Enterprise edition when you can
>> lock
>> pages in memory?
>No. The purpose of Lock Pages is to prevent something else from stealing or
>borrowing those pages once they are allocated. You can achieve a similar
>functionality as Linchi mentioned by setting the MIN and MAX to the same
>value. Then as long as you start SQL Server, use that much memory and the
>memory is available it will keep it once it has used it. But now you have
>another potentially serious issue and that is with the memory that is left.
>If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the OS
>and anything else you run. If you attempt to run that SSIS package again in
>this mode you will surely starve the OS of memory and the machine and
>everything on it will not be happy. This is one of the main reasons why it
>is recommended you run larger SSIS packages on a separate machine.
>> Interesting...
>[quoted text clipped - 32 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
--
Message posted via http://www.sqlmonster.com|||Thanks for all the info, Andrew.
Hopefully, (for your sake) this is my last set of questions. If I set my
Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS, then,
if I execute an expensive adhoc query directly on the server from Management
Studio, and that query consumes the 14GB, is that query able to consume any
of the OS memory?
On the flip side, if my Max Server Memory is set to 14GB and Min Server
Memory is left at its default (0), then with this configuration, the same
adhoc query is executed above, does that mean the query could consume both
the 14GB as well as the 2GB OS?
Andrew J. Kelly wrote:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would it
>> not
>> equally starve the Memory on the separate machine?
>If those are the only two processes on the machine they would live much
>better together than with SQL Server and lock pages.
>> 2. When running this SSIS export on the same machine, (loosely speaking)
>> does
>> SSIS first consume the 14GB, then go after the 2GB of the OS?
>I don't know the allocations work at that level but I would say there are
>factors that would make it a DEPENDS type of answer.
>> 3. At what point does the 8TB of virtual address space get used.
>Virtual address space is always used but if you mean when does it start
>swapping to disk? Then generally when all the physical memory is depleted.
>> This raises a few more questions:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would it
>[quoted text clipped - 49 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||> Hopefully, (for your sake) this is my last set of questions. If I set my
> Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS,
> then,
> if I execute an expensive adhoc query directly on the server from
> Management
> Studio, and that query consumes the 14GB, is that query able to consume
> any
> of the OS memory?
Well the portion of memory that used to be called MemTo Leave still comes
into play as a section of memory pre-reserved at startup and is not part of
the buffer pool. In 2000 that defaulted to about 384MB (128M for the worker
threads). In 2005 the allocations are a little more complicated but lets
just say 384MB comes right off the top. The MAX Memory is for the buffer
pool only. So you can theoriticallyuse 14GB for bufferpool and 384MB for
Contiguous memory. That will leave about 1.7GB for the OS. Having said that
none of the 1.7GB will be used for a query in SQL Server under the
conditions you outlined.
> On the flip side, if my Max Server Memory is set to 14GB and Min Server
> Memory is left at its default (0), then with this configuration, the same
> adhoc query is executed above, does that mean the query could consume both
> the 14GB as well as the 2GB OS?
No, this still follows the exact same rules. The MIN just states that after
SQL Server grabs that memory it will not give it back to the OS.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:793ea42168c08@.uwe...
> Thanks for all the info, Andrew.
> Hopefully, (for your sake) this is my last set of questions. If I set my
> Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS,
> then,
> if I execute an expensive adhoc query directly on the server from
> Management
> Studio, and that query consumes the 14GB, is that query able to consume
> any
> of the OS memory?
> On the flip side, if my Max Server Memory is set to 14GB and Min Server
> Memory is left at its default (0), then with this configuration, the same
> adhoc query is executed above, does that mean the query could consume both
> the 14GB as well as the 2GB OS?
> Andrew J. Kelly wrote:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would
>> it
>> not
>> equally starve the Memory on the separate machine?
>>If those are the only two processes on the machine they would live much
>>better together than with SQL Server and lock pages.
>> 2. When running this SSIS export on the same machine, (loosely speaking)
>> does
>> SSIS first consume the 14GB, then go after the 2GB of the OS?
>>I don't know the allocations work at that level but I would say there are
>>factors that would make it a DEPENDS type of answer.
>> 3. At what point does the 8TB of virtual address space get used.
>>Virtual address space is always used but if you mean when does it start
>>swapping to disk? Then generally when all the physical memory is
>>depleted.
>> This raises a few more questions:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would
>> it
>>[quoted text clipped - 49 lines]
>>>>processes whenever needed? Is Max Server Memory even needed in
>>>>this
>>>>case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Thanks for the advice Linchi.
I am trying to understand the difference between the ability to lock pages in
memory on 64 bit Enterprise Edition and setting the Min/Max Server Memory
setting the same on a 64 bit Standard Edition.
We are running 64 bit Standard Edition, with 16 GB RAM, and have the Min
Server Memory set to zero and Max Server Memory set to 14336.
We are still experiencing a shortage of memory and I believe it is either a
separate application, such as Reporting Services or SSIS that is depleting
our memory.
So, it this were an Enterprise addition box (instead of Standard Edition),
and I had the ability to Lock Pages in Memory, how would this change my
situation?
Or, with my current Standard Edition situation, how would setting the Min and
Max Server Memory to the same amount change my situation?
Linchi Shea wrote:
>> Is Max Server Memory even needed in this case?
>Personally, I'd always set Max Server Memory and Min Server Memory on any
>serious instance. Why let the SQL Server process engage in expensive back and
>forth memory trading with OS?
>Linchi
>> My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
>> Lock Pages in Memory setting is ignored.
>[quoted text clipped - 6 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1sql
Lock Pages in Memory setting is ignored.
We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
configuration). The only other application sharing SQL Server resources is
CLR.
With the Lock Pages in Memory setting ignored, does this mean that the 14GB
dedicated to SQL Server is fair game, to be taken as desired by other
processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Still hoping for an answer.
We had a server running 32bit with 2 GB RAM, converted it to 64bit with 16GB
RAM (14GB Max Server Memory) and in less than a week we are getting the
following error in our error log:
"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):
117484, committed (KB): 11856700, memory utilization: 0%."
We have not made any modifications to MemToLeave, so it is at the default
(256MB?). But from what I understand, 64bit gives you a huge extended memory.
So we have not done anything to MemToLeave.
Why do we have memory issues with 16GB RAM, 64bit, but did not have memory
issues on 2GB with 32 bit?
cbrichards wrote:
>My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
>Lock Pages in Memory setting is ignored.
>We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
>configuration). The only other application sharing SQL Server resources is
>CLR.
>With the Lock Pages in Memory setting ignored, does this mean that the 14GB
>dedicated to SQL Server is fair game, to be taken as desired by other
>processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||Hello there!
Have you read the following document?
http://support.microsoft.com/default.aspx/kb/918483
Ekrem Ã?nsoy
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7925eb5963cda@.uwe...
> My understanding is that running 64 bit SQL Server 2005 Standard Edition,
> the
> Lock Pages in Memory setting is ignored.
> We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server
> Memory
> configuration). The only other application sharing SQL Server resources is
> CLR.
> With the Lock Pages in Memory setting ignored, does this mean that the
> 14GB
> dedicated to SQL Server is fair game, to be taken as desired by other
> processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||I highly recommend you read the KB that was posted by Ekrem. I don't
believe Std Edition supports Lock Pages in Memory but you have to understand
there is a huge difference in how memory is utilized between 32 and 64 bit
and even SQL2000 and 2005. These messages are not related to the MemToLeave
area. That memory is pre-allocated and will not be paged out. But unlike in
32 bit the 64 bit SQL Server can utilize all of the memory for things
normally confined to the 2 or 3GB area in a 32 env and can use considerably
more memory than before. For instance the procedure cache would have been
limited to less than 2GB on your old server. Now it can grow at times to as
much as 75% ( exact amount changes with service packs and current conditions
and this is peak, usually won't be more than 50%) of total available memory.
This memory is all dynamic by default and if windows needs more it can call
for memory from SQL Server which will give you the messages you are seeing.
My guess is that you have a lot of adhoc SQL and the proc cache is huge due
to all the non-reusable plans. And or you have operations other than SQL
Server itself running on the server such as SSIS, CLR, Term Services,
Winzip, Notepad etc.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7929227ce194c@.uwe...
> Still hoping for an answer.
> We had a server running 32bit with 2 GB RAM, converted it to 64bit with
> 16GB
> RAM (14GB Max Server Memory) and in less than a week we are getting the
> following error in our error log:
> "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):
> 117484, committed (KB): 11856700, memory utilization: 0%."
> We have not made any modifications to MemToLeave, so it is at the default
> (256MB?). But from what I understand, 64bit gives you a huge extended
> memory.
> So we have not done anything to MemToLeave.
> Why do we have memory issues with 16GB RAM, 64bit, but did not have memory
> issues on 2GB with 32 bit?
> cbrichards wrote:
>>My understanding is that running 64 bit SQL Server 2005 Standard Edition,
>>the
>>Lock Pages in Memory setting is ignored.
>>We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server
>>Memory
>>configuration). The only other application sharing SQL Server resources is
>>CLR.
>>With the Lock Pages in Memory setting ignored, does this mean that the
>>14GB
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via http://www.sqlmonster.com
>|||Well, Hello Ekrem!
I have read that document, and that applies to SQL Server 2005 64 bit
Enterprise Edition. In SQL Server 2005 64 bit Standard Edition (which I noted
in my post, is what we run), that Lock Pages in Memory is ignored.
So, I am still, seeking for answers...
Ekrem Ã?nsoy wrote:
>Hello there!
>Have you read the following document?
>http://support.microsoft.com/default.aspx/kb/918483
>> My understanding is that running 64 bit SQL Server 2005 Standard Edition,
>> the
>[quoted text clipped - 9 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||So now the question is, if I cannot lock pages in memory, then is my only
choice to upgrade to Enterprise edition?
cbrichards wrote:
>Well, Hello Ekrem!
>I have read that document, and that applies to SQL Server 2005 64 bit
>Enterprise Edition. In SQL Server 2005 64 bit Standard Edition (which I noted
>in my post, is what we run), that Lock Pages in Memory is ignored.
>So, I am still, seeking for answers...
>>Hello there!
>[quoted text clipped - 6 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||Thanks Andrew for the information.
From what you wrote, I believe our server ran out of memory due to an export
of data from a 250,000,000 row table. This table holds 90 days worth of
archive data and the "adhoc" query was selecting 1 day to export.
I am still perplexed, because this same archive table was (a week ago) on a
32 bit server with 2 GB RAM, and nothing like this ever happened (running out
of memory). The volume of data, number of stored procedures, user connections,
etc., etc. are all the same as when it was on a 32 bit server. One month ago
I performed this same export when it was 32 bit, and while the CPU escalated,
the server did not crater.
I am having difficulty wrapping my arms around the fact that the server did
not crater under 32 bit and 2 GB RAM, but the same operation cratered 64 bit
with 16 GB RAM.
The following came from running DBCC MemoryStatus:
Procedure Cache Value
-- --
TotalProcs 88
TotalPages 2086
InUsePages 70
Any further insights would be appreciated. We are nervous to move forward
with 64 bit Standard Edition with all these unknowns.
Andrew J. Kelly wrote:
>I highly recommend you read the KB that was posted by Ekrem. I don't
>believe Std Edition supports Lock Pages in Memory but you have to understand
>there is a huge difference in how memory is utilized between 32 and 64 bit
>and even SQL2000 and 2005. These messages are not related to the MemToLeave
>area. That memory is pre-allocated and will not be paged out. But unlike in
>32 bit the 64 bit SQL Server can utilize all of the memory for things
>normally confined to the 2 or 3GB area in a 32 env and can use considerably
>more memory than before. For instance the procedure cache would have been
>limited to less than 2GB on your old server. Now it can grow at times to as
>much as 75% ( exact amount changes with service packs and current conditions
>and this is peak, usually won't be more than 50%) of total available memory.
>This memory is all dynamic by default and if windows needs more it can call
>for memory from SQL Server which will give you the messages you are seeing.
>My guess is that you have a lot of adhoc SQL and the proc cache is huge due
>to all the non-reusable plans. And or you have operations other than SQL
>Server itself running on the server such as SSIS, CLR, Term Services,
>Winzip, Notepad etc.
>> Still hoping for an answer.
>[quoted text clipped - 30 lines]
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||OK now we are getting somewhere. You keep feeding bits and pieces to us but
it would be great to have a more detailed description of the issue and
circumstances. Exactly how are you doing this data export? Is it SSIS? If
so that explains a lot. SSIS is a totally separate process from SQL Server
and it will use it's own memory space just like another app on the server.
On a 32 bit machine it can only use up to 2GB max but probably a lot less.
On the 64 bit it can use all it wants. Since the memory is dynamic it will
compete with SQL Server for sure if run on the same machine. SSIS like to do
the work totally in memory if at all possible and can use a lot more than
you might expect. If you provide more details we can give a more directed or
intelligent answer. Also if you do something like this routinely it sounds
like you may want to partition the data to suite these needs.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79322e221281d@.uwe...
> Thanks Andrew for the information.
> From what you wrote, I believe our server ran out of memory due to an
> export
> of data from a 250,000,000 row table. This table holds 90 days worth of
> archive data and the "adhoc" query was selecting 1 day to export.
> I am still perplexed, because this same archive table was (a week ago) on
> a
> 32 bit server with 2 GB RAM, and nothing like this ever happened (running
> out
> of memory). The volume of data, number of stored procedures, user
> connections,
> etc., etc. are all the same as when it was on a 32 bit server. One month
> ago
> I performed this same export when it was 32 bit, and while the CPU
> escalated,
> the server did not crater.
> I am having difficulty wrapping my arms around the fact that the server
> did
> not crater under 32 bit and 2 GB RAM, but the same operation cratered 64
> bit
> with 16 GB RAM.
> The following came from running DBCC MemoryStatus:
> Procedure Cache Value
> -- --
> TotalProcs 88
> TotalPages 2086
> InUsePages 70
> Any further insights would be appreciated. We are nervous to move forward
> with 64 bit Standard Edition with all these unknowns.
> Andrew J. Kelly wrote:
>>I highly recommend you read the KB that was posted by Ekrem. I don't
>>believe Std Edition supports Lock Pages in Memory but you have to
>>understand
>>there is a huge difference in how memory is utilized between 32 and 64 bit
>>and even SQL2000 and 2005. These messages are not related to the
>>MemToLeave
>>area. That memory is pre-allocated and will not be paged out. But unlike
>>in
>>32 bit the 64 bit SQL Server can utilize all of the memory for things
>>normally confined to the 2 or 3GB area in a 32 env and can use
>>considerably
>>more memory than before. For instance the procedure cache would have been
>>limited to less than 2GB on your old server. Now it can grow at times to
>>as
>>much as 75% ( exact amount changes with service packs and current
>>conditions
>>and this is peak, usually won't be more than 50%) of total available
>>memory.
>>This memory is all dynamic by default and if windows needs more it can
>>call
>>for memory from SQL Server which will give you the messages you are
>>seeing.
>>My guess is that you have a lot of adhoc SQL and the proc cache is huge
>>due
>>to all the non-reusable plans. And or you have operations other than SQL
>>Server itself running on the server such as SSIS, CLR, Term Services,
>>Winzip, Notepad etc.
>> Still hoping for an answer.
>>[quoted text clipped - 30 lines]
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
> --
> Message posted via http://www.sqlmonster.com
>|||> Is Max Server Memory even needed in this case?
Personally, I'd always set Max Server Memory and Min Server Memory on any
serious instance. Why let the SQL Server process engage in expensive back and
forth memory trading with OS?
Linchi
"cbrichards via SQLMonster.com" wrote:
> My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
> Lock Pages in Memory setting is ignored.
> We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
> configuration). The only other application sharing SQL Server resources is
> CLR.
> With the Lock Pages in Memory setting ignored, does this mean that the 14GB
> dedicated to SQL Server is fair game, to be taken as desired by other
> processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Interesting...
I am using SSIS to perform the export.
This adds more questions:
1. You stated, "On a 32 bit machine it can only use up to 2GB max but
probably a lot less." Why only 2GB and probably a lot less?
2. You stated, "On the 64 bit it can use all it wants. Since the memory is
dynamic..." Is this true even when using Enterprise edition when you can lock
pages in memory?
Andrew J. Kelly wrote:
>OK now we are getting somewhere. You keep feeding bits and pieces to us but
>it would be great to have a more detailed description of the issue and
>circumstances. Exactly how are you doing this data export? Is it SSIS? If
>so that explains a lot. SSIS is a totally separate process from SQL Server
>and it will use it's own memory space just like another app on the server.
>On a 32 bit machine it can only use up to 2GB max but probably a lot less.
>On the 64 bit it can use all it wants. Since the memory is dynamic it will
>compete with SQL Server for sure if run on the same machine. SSIS like to do
>the work totally in memory if at all possible and can use a lot more than
>you might expect. If you provide more details we can give a more directed or
>intelligent answer. Also if you do something like this routinely it sounds
>like you may want to partition the data to suite these needs.
>> Thanks Andrew for the information.
>[quoted text clipped - 65 lines]
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||> This adds more questions:
> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
> probably a lot less." Why only 2GB and probably a lot less?
Well if the system only had 2GB you need memory for other things as well.
But in 32 bit OS by default any app can only use up to 2GB of directly
addressable memory. If you had more than 2GB and the OS was capable of using
PAE and the app was AWE aware it may be able to use more than 2GB. But I
don't think SSIS was AWE aware so on 32 bit I believe it was only able to
use 2GB or 3GB if /3gb was set max. I could be wrong there but I am pretty
sure that is correct.
> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
> dynamic..." Is this true even when using Enterprise edition when you can
> lock
> pages in memory?
No. The purpose of Lock Pages is to prevent something else from stealing or
borrowing those pages once they are allocated. You can achieve a similar
functionality as Linchi mentioned by setting the MIN and MAX to the same
value. Then as long as you start SQL Server, use that much memory and the
memory is available it will keep it once it has used it. But now you have
another potentially serious issue and that is with the memory that is left.
If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the OS
and anything else you run. If you attempt to run that SSIS package again in
this mode you will surely starve the OS of memory and the machine and
everything on it will not be happy. This is one of the main reasons why it
is recommended you run larger SSIS packages on a separate machine.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79367cc8cda07@.uwe...
> Interesting...
> I am using SSIS to perform the export.
> This adds more questions:
> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
> probably a lot less." Why only 2GB and probably a lot less?
> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
> dynamic..." Is this true even when using Enterprise edition when you can
> lock
> pages in memory?
> Andrew J. Kelly wrote:
>>OK now we are getting somewhere. You keep feeding bits and pieces to us
>>but
>>it would be great to have a more detailed description of the issue and
>>circumstances. Exactly how are you doing this data export? Is it SSIS?
>>If
>>so that explains a lot. SSIS is a totally separate process from SQL Server
>>and it will use it's own memory space just like another app on the server.
>>On a 32 bit machine it can only use up to 2GB max but probably a lot less.
>>On the 64 bit it can use all it wants. Since the memory is dynamic it will
>>compete with SQL Server for sure if run on the same machine. SSIS like to
>>do
>>the work totally in memory if at all possible and can use a lot more than
>>you might expect. If you provide more details we can give a more directed
>>or
>>intelligent answer. Also if you do something like this routinely it
>>sounds
>>like you may want to partition the data to suite these needs.
>> Thanks Andrew for the information.
>>[quoted text clipped - 65 lines]
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||> 1. So, in my case, if SSIS were to be run on a separate machine, would it
> not
> equally starve the Memory on the separate machine?
If those are the only two processes on the machine they would live much
better together than with SQL Server and lock pages.
> 2. When running this SSIS export on the same machine, (loosely speaking)
> does
> SSIS first consume the 14GB, then go after the 2GB of the OS?
I don't know the allocations work at that level but I would say there are
factors that would make it a DEPENDS type of answer.
> 3. At what point does the 8TB of virtual address space get used.
Virtual address space is always used but if you mean when does it start
swapping to disk? Then generally when all the physical memory is depleted.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:793df7e614c62@.uwe...
> This raises a few more questions:
> 1. So, in my case, if SSIS were to be run on a separate machine, would it
> not
> equally starve the Memory on the separate machine?
> 2. When running this SSIS export on the same machine, (loosely speaking)
> does
> SSIS first consume the 14GB, then go after the 2GB of the OS?
> 3. At what point does the 8TB of virtual address space get used.
> Andrew J. Kelly wrote:
>> This adds more questions:
>> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
>> probably a lot less." Why only 2GB and probably a lot less?
>>Well if the system only had 2GB you need memory for other things as well.
>>But in 32 bit OS by default any app can only use up to 2GB of directly
>>addressable memory. If you had more than 2GB and the OS was capable of
>>using
>>PAE and the app was AWE aware it may be able to use more than 2GB. But I
>>don't think SSIS was AWE aware so on 32 bit I believe it was only able to
>>use 2GB or 3GB if /3gb was set max. I could be wrong there but I am
>>pretty
>>sure that is correct.
>> 2. You stated, "On the 64 bit it can use all it wants. Since the memory
>> is
>> dynamic..." Is this true even when using Enterprise edition when you can
>> lock
>> pages in memory?
>>No. The purpose of Lock Pages is to prevent something else from stealing
>>or
>>borrowing those pages once they are allocated. You can achieve a similar
>>functionality as Linchi mentioned by setting the MIN and MAX to the same
>>value. Then as long as you start SQL Server, use that much memory and the
>>memory is available it will keep it once it has used it. But now you have
>>another potentially serious issue and that is with the memory that is
>>left.
>>If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the
>>OS
>>and anything else you run. If you attempt to run that SSIS package again
>>in
>>this mode you will surely starve the OS of memory and the machine and
>>everything on it will not be happy. This is one of the main reasons why
>>it
>>is recommended you run larger SSIS packages on a separate machine.
>> Interesting...
>>[quoted text clipped - 32 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
> --
> Message posted via http://www.sqlmonster.com
>|||This raises a few more questions:
1. So, in my case, if SSIS were to be run on a separate machine, would it not
equally starve the Memory on the separate machine?
2. When running this SSIS export on the same machine, (loosely speaking) does
SSIS first consume the 14GB, then go after the 2GB of the OS?
3. At what point does the 8TB of virtual address space get used.
Andrew J. Kelly wrote:
>> This adds more questions:
>> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
>> probably a lot less." Why only 2GB and probably a lot less?
>Well if the system only had 2GB you need memory for other things as well.
>But in 32 bit OS by default any app can only use up to 2GB of directly
>addressable memory. If you had more than 2GB and the OS was capable of using
>PAE and the app was AWE aware it may be able to use more than 2GB. But I
>don't think SSIS was AWE aware so on 32 bit I believe it was only able to
>use 2GB or 3GB if /3gb was set max. I could be wrong there but I am pretty
>sure that is correct.
>> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
>> dynamic..." Is this true even when using Enterprise edition when you can
>> lock
>> pages in memory?
>No. The purpose of Lock Pages is to prevent something else from stealing or
>borrowing those pages once they are allocated. You can achieve a similar
>functionality as Linchi mentioned by setting the MIN and MAX to the same
>value. Then as long as you start SQL Server, use that much memory and the
>memory is available it will keep it once it has used it. But now you have
>another potentially serious issue and that is with the memory that is left.
>If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the OS
>and anything else you run. If you attempt to run that SSIS package again in
>this mode you will surely starve the OS of memory and the machine and
>everything on it will not be happy. This is one of the main reasons why it
>is recommended you run larger SSIS packages on a separate machine.
>> Interesting...
>[quoted text clipped - 32 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
--
Message posted via http://www.sqlmonster.com|||Thanks for all the info, Andrew.
Hopefully, (for your sake) this is my last set of questions. If I set my
Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS, then,
if I execute an expensive adhoc query directly on the server from Management
Studio, and that query consumes the 14GB, is that query able to consume any
of the OS memory?
On the flip side, if my Max Server Memory is set to 14GB and Min Server
Memory is left at its default (0), then with this configuration, the same
adhoc query is executed above, does that mean the query could consume both
the 14GB as well as the 2GB OS?
Andrew J. Kelly wrote:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would it
>> not
>> equally starve the Memory on the separate machine?
>If those are the only two processes on the machine they would live much
>better together than with SQL Server and lock pages.
>> 2. When running this SSIS export on the same machine, (loosely speaking)
>> does
>> SSIS first consume the 14GB, then go after the 2GB of the OS?
>I don't know the allocations work at that level but I would say there are
>factors that would make it a DEPENDS type of answer.
>> 3. At what point does the 8TB of virtual address space get used.
>Virtual address space is always used but if you mean when does it start
>swapping to disk? Then generally when all the physical memory is depleted.
>> This raises a few more questions:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would it
>[quoted text clipped - 49 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||> Hopefully, (for your sake) this is my last set of questions. If I set my
> Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS,
> then,
> if I execute an expensive adhoc query directly on the server from
> Management
> Studio, and that query consumes the 14GB, is that query able to consume
> any
> of the OS memory?
Well the portion of memory that used to be called MemTo Leave still comes
into play as a section of memory pre-reserved at startup and is not part of
the buffer pool. In 2000 that defaulted to about 384MB (128M for the worker
threads). In 2005 the allocations are a little more complicated but lets
just say 384MB comes right off the top. The MAX Memory is for the buffer
pool only. So you can theoriticallyuse 14GB for bufferpool and 384MB for
Contiguous memory. That will leave about 1.7GB for the OS. Having said that
none of the 1.7GB will be used for a query in SQL Server under the
conditions you outlined.
> On the flip side, if my Max Server Memory is set to 14GB and Min Server
> Memory is left at its default (0), then with this configuration, the same
> adhoc query is executed above, does that mean the query could consume both
> the 14GB as well as the 2GB OS?
No, this still follows the exact same rules. The MIN just states that after
SQL Server grabs that memory it will not give it back to the OS.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:793ea42168c08@.uwe...
> Thanks for all the info, Andrew.
> Hopefully, (for your sake) this is my last set of questions. If I set my
> Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS,
> then,
> if I execute an expensive adhoc query directly on the server from
> Management
> Studio, and that query consumes the 14GB, is that query able to consume
> any
> of the OS memory?
> On the flip side, if my Max Server Memory is set to 14GB and Min Server
> Memory is left at its default (0), then with this configuration, the same
> adhoc query is executed above, does that mean the query could consume both
> the 14GB as well as the 2GB OS?
> Andrew J. Kelly wrote:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would
>> it
>> not
>> equally starve the Memory on the separate machine?
>>If those are the only two processes on the machine they would live much
>>better together than with SQL Server and lock pages.
>> 2. When running this SSIS export on the same machine, (loosely speaking)
>> does
>> SSIS first consume the 14GB, then go after the 2GB of the OS?
>>I don't know the allocations work at that level but I would say there are
>>factors that would make it a DEPENDS type of answer.
>> 3. At what point does the 8TB of virtual address space get used.
>>Virtual address space is always used but if you mean when does it start
>>swapping to disk? Then generally when all the physical memory is
>>depleted.
>> This raises a few more questions:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would
>> it
>>[quoted text clipped - 49 lines]
>>>>processes whenever needed? Is Max Server Memory even needed in
>>>>this
>>>>case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Thanks for the advice Linchi.
I am trying to understand the difference between the ability to lock pages in
memory on 64 bit Enterprise Edition and setting the Min/Max Server Memory
setting the same on a 64 bit Standard Edition.
We are running 64 bit Standard Edition, with 16 GB RAM, and have the Min
Server Memory set to zero and Max Server Memory set to 14336.
We are still experiencing a shortage of memory and I believe it is either a
separate application, such as Reporting Services or SSIS that is depleting
our memory.
So, it this were an Enterprise addition box (instead of Standard Edition),
and I had the ability to Lock Pages in Memory, how would this change my
situation?
Or, with my current Standard Edition situation, how would setting the Min and
Max Server Memory to the same amount change my situation?
Linchi Shea wrote:
>> Is Max Server Memory even needed in this case?
>Personally, I'd always set Max Server Memory and Min Server Memory on any
>serious instance. Why let the SQL Server process engage in expensive back and
>forth memory trading with OS?
>Linchi
>> My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
>> Lock Pages in Memory setting is ignored.
>[quoted text clipped - 6 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1sql
Lock page in memory setting in SQl 64 system
Hi All,
We know that locking page in memory setting is recommended to set in SQL 64 system. The purpose is used to avoid memory pages in Sql address will not be OS paged out.
It is a little confused that if the memory pages of sql cannot be paged out after setting lock page in memory. Then the memory used by SqL will increase forever. If the physical memory is limited, it will obviously caused the memory pressure of the system eventually. How does the 64 bit sql manage his Buff pool in such scenario? Will it be paged out? Thanks in advance
SQL Server listens to memory notifications from windows. These notifications indicate the state of physical memory on the system, low (meaning that the system does not have enough memory to satisfy new allocations) or High (meaning there is an abundance of memory available).
When the low memory notification is set SQL Server reduces memory usage until the notification goes off, thereby avoiding the scenario you describe.
To answer your question about whether locked pages will ever be paged out - no, locked pages cannot be paged out by the OS. If no more physical memory is available new allocations will simply fail.
|||Thanks. you mentioned "when the low memory notification is set SQL Server reduces memory usage." However, (in 64 bit environment), since the page is locked in memory at that time, how did SQL Server reduce the memory usage?
Do you mean that the SQL will report error if the sql server is in such scenario (low physical memory in OS but heavy workload in SQL requests more memory) ?
|||Locking a page does not mean you cannot deallocate it. The application that allocated and locked the page is free to deallocate it at any time.|||Thanks a lot !!|||You also can and should set the max memory setting in SQL Server 2005 to a level that reserves some memory for the OS. Slava Oks has blogged about the recommended settings for this. Hopefully, you are running SQL Server 2005 on a dedicated server, so its not competing for memory with any other applications.
Wednesday, March 21, 2012
Location of ERRORLOG and SQLAGENNT.OUT
SQL Server 2000, Windows 2003 R2, Cluster 5.2
We're setting up a new cluster and I would like to move the LOGFILE and
SQLAGENT.OUT file off of the drive(s) that contain .ldf & .mdf files. My
reason is that these files will contribute to drive fragmentation (also, I
would like it to be a simpler path to get to the files, something right off
of the drive root).
Now, I've never done this on a cluster before and our Windows admin is
telling me that these logs are somehow used by the cluster administrator to
do some part of the failover. I'm unclear how.
Anyway, The drives I have available are:
C: OS (not shared)
E: Apps (SQL Server Install/not shared)
Q: Quorum
L: Logs (shared)
S: Data (shared)
R: Backup (shared)
The ERRORLOG & SQLAGENT.OUT files are currently on S: under the "S:\Program
Files\Microsoft SQL Server\MSSQL\LOG" path (same disk as the data files) and
I would like to place them on the E: drive (which is not shared).
I can see that this would cause me to not have log entries from the other
server immediatly available to me on a changeover as it wouldn't be on a
shared disk -- and I don't know if that is a bad thing, or not.
Can I, or should I, move these log files off the disk with the data files?
Thanks,
Jay
These log files hardly cause any significant fragmentation that would have a
significant impact on the database performance.
I have never tried to move the log files to local non-shared disks, and will
probably never try it since that is not the way it is supposed to work.
Even if you managed to relocate the log files to E, consider this. When your
instance is running on node A, and the logs would be on the E drive on node
A. If then node A is hosed, your SQL instance will fail over to another node,
but your log files. So you won't have access to the log files for
troubleshooting. Not good! Okay, the cluster service may manage to replicate
some of the errorlog entries that are also writtne to the app eventlog to the
other nodes, but the completeness of that is not guaranteed when there is a
sudden failure.
Don't do it!
Linchi
"Jay" wrote:
> SQL Server 2000, Windows 2003 R2, Cluster 5.2
> We're setting up a new cluster and I would like to move the LOGFILE and
> SQLAGENT.OUT file off of the drive(s) that contain .ldf & .mdf files. My
> reason is that these files will contribute to drive fragmentation (also, I
> would like it to be a simpler path to get to the files, something right off
> of the drive root).
> Now, I've never done this on a cluster before and our Windows admin is
> telling me that these logs are somehow used by the cluster administrator to
> do some part of the failover. I'm unclear how.
> Anyway, The drives I have available are:
> C: OS (not shared)
> E: Apps (SQL Server Install/not shared)
> Q: Quorum
> L: Logs (shared)
> S: Data (shared)
> R: Backup (shared)
> The ERRORLOG & SQLAGENT.OUT files are currently on S: under the "S:\Program
> Files\Microsoft SQL Server\MSSQL\LOG" path (same disk as the data files) and
> I would like to place them on the E: drive (which is not shared).
> I can see that this would cause me to not have log entries from the other
> server immediatly available to me on a changeover as it wouldn't be on a
> shared disk -- and I don't know if that is a bad thing, or not.
> Can I, or should I, move these log files off the disk with the data files?
> Thanks,
> Jay
>
>
|||Point taken, thanks.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:2B7D6FFA-DAF5-4F1A-8479-95AD50156DE7@.microsoft.com...[vbcol=seagreen]
> These log files hardly cause any significant fragmentation that would have
> a
> significant impact on the database performance.
> I have never tried to move the log files to local non-shared disks, and
> will
> probably never try it since that is not the way it is supposed to work.
> Even if you managed to relocate the log files to E, consider this. When
> your
> instance is running on node A, and the logs would be on the E drive on
> node
> A. If then node A is hosed, your SQL instance will fail over to another
> node,
> but your log files. So you won't have access to the log files for
> troubleshooting. Not good! Okay, the cluster service may manage to
> replicate
> some of the errorlog entries that are also writtne to the app eventlog to
> the
> other nodes, but the completeness of that is not guaranteed when there is
> a
> sudden failure.
> Don't do it!
> Linchi
> "Jay" wrote:
sql
We're setting up a new cluster and I would like to move the LOGFILE and
SQLAGENT.OUT file off of the drive(s) that contain .ldf & .mdf files. My
reason is that these files will contribute to drive fragmentation (also, I
would like it to be a simpler path to get to the files, something right off
of the drive root).
Now, I've never done this on a cluster before and our Windows admin is
telling me that these logs are somehow used by the cluster administrator to
do some part of the failover. I'm unclear how.
Anyway, The drives I have available are:
C: OS (not shared)
E: Apps (SQL Server Install/not shared)
Q: Quorum
L: Logs (shared)
S: Data (shared)
R: Backup (shared)
The ERRORLOG & SQLAGENT.OUT files are currently on S: under the "S:\Program
Files\Microsoft SQL Server\MSSQL\LOG" path (same disk as the data files) and
I would like to place them on the E: drive (which is not shared).
I can see that this would cause me to not have log entries from the other
server immediatly available to me on a changeover as it wouldn't be on a
shared disk -- and I don't know if that is a bad thing, or not.
Can I, or should I, move these log files off the disk with the data files?
Thanks,
Jay
These log files hardly cause any significant fragmentation that would have a
significant impact on the database performance.
I have never tried to move the log files to local non-shared disks, and will
probably never try it since that is not the way it is supposed to work.
Even if you managed to relocate the log files to E, consider this. When your
instance is running on node A, and the logs would be on the E drive on node
A. If then node A is hosed, your SQL instance will fail over to another node,
but your log files. So you won't have access to the log files for
troubleshooting. Not good! Okay, the cluster service may manage to replicate
some of the errorlog entries that are also writtne to the app eventlog to the
other nodes, but the completeness of that is not guaranteed when there is a
sudden failure.
Don't do it!
Linchi
"Jay" wrote:
> SQL Server 2000, Windows 2003 R2, Cluster 5.2
> We're setting up a new cluster and I would like to move the LOGFILE and
> SQLAGENT.OUT file off of the drive(s) that contain .ldf & .mdf files. My
> reason is that these files will contribute to drive fragmentation (also, I
> would like it to be a simpler path to get to the files, something right off
> of the drive root).
> Now, I've never done this on a cluster before and our Windows admin is
> telling me that these logs are somehow used by the cluster administrator to
> do some part of the failover. I'm unclear how.
> Anyway, The drives I have available are:
> C: OS (not shared)
> E: Apps (SQL Server Install/not shared)
> Q: Quorum
> L: Logs (shared)
> S: Data (shared)
> R: Backup (shared)
> The ERRORLOG & SQLAGENT.OUT files are currently on S: under the "S:\Program
> Files\Microsoft SQL Server\MSSQL\LOG" path (same disk as the data files) and
> I would like to place them on the E: drive (which is not shared).
> I can see that this would cause me to not have log entries from the other
> server immediatly available to me on a changeover as it wouldn't be on a
> shared disk -- and I don't know if that is a bad thing, or not.
> Can I, or should I, move these log files off the disk with the data files?
> Thanks,
> Jay
>
>
|||Point taken, thanks.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:2B7D6FFA-DAF5-4F1A-8479-95AD50156DE7@.microsoft.com...[vbcol=seagreen]
> These log files hardly cause any significant fragmentation that would have
> a
> significant impact on the database performance.
> I have never tried to move the log files to local non-shared disks, and
> will
> probably never try it since that is not the way it is supposed to work.
> Even if you managed to relocate the log files to E, consider this. When
> your
> instance is running on node A, and the logs would be on the E drive on
> node
> A. If then node A is hosed, your SQL instance will fail over to another
> node,
> but your log files. So you won't have access to the log files for
> troubleshooting. Not good! Okay, the cluster service may manage to
> replicate
> some of the errorlog entries that are also writtne to the app eventlog to
> the
> other nodes, but the completeness of that is not guaranteed when there is
> a
> sudden failure.
> Don't do it!
> Linchi
> "Jay" wrote:
sql
Subscribe to:
Posts (Atom)