A significant part of sql server process memory has been paged out

On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?

Error:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.

This means that Windows has decided to move SQL Server memory out of RAM and stored it in disk until it's needed again. This article has some more information on how to avoid this:

http://support.microsoft.com/kb/918483/en-us

You also mentioned that SQL Server has restarted after this. Could you please post the last 10 lines or so of errorlog when this occurred?

Thanks,

Fabricio.

|||

I did look at that article but I don't think that will work for SQL Standard Edition.

Note SQL Server 2005 Enterprise Edition is the only edition that is designed to use lock pages in memory.

|||

In that case, try to identify what's causing Windows to page SQL Server out to disk. SQL Server tries to fill up the available memory on the machine. When you have other apps running on the same machine, they may use a lot of memory and push SQL Server out to the swap file. SQL Server will detect this and trim its memory, but in some rare cases it can't release enough memory or can't release fast enough and may end up being swapped out. This message is to notify you that such event happened and that SQL Server's memory is being fetched from disk, causing some temporary slowness.

Please, let me know if you need help figuring out what app is using memory on the machine.

Thanks,

Fabricio.

|||

I believe the KB article is incorrect stating it’s only valid for x64 Enterprise Edition. I encountered the same problem you reported and the Lock Pages In Memory policy work just fine with x64 standard edition.

|||

This article explains the problem I was having:

http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx

The system was running Windows 2003 SP1. Have upgraded to Windows 2003 SP2 and have not had the problem since.

|||

Kmoskal,

The KB article is correct. Only SQL Server Enterprise will lock pages in memory. You may set the priviledge with any other edition, but it will have no effect in SQL Server. I guess you just have been lucky (and SQL Server has been doing a great job keeping itself in RAM) :-)

Thanks,

Fabricio.

A list of Auto exec SPs

Hello All,
A SP can be made to automatically execute when SQLServer restarts using the
store procedure "sp_procoption".
But is there a way to find out the list of SPs that have been congifured to
execute automatically. I took over as a DBA for an existing system and I was
wondering if there are any SP configured this way.
Thanks,
rgnIf you look at the definition for sp_procoption, you will discover the
following line of code:
UPDATE sysobjects SET status = (status & ~2) | (2 * @.intOptionValue) WHERE
id = @.tabid
This tells you, along with the rest of the definition, that if you query the
master.dbo.sysobjects table for status values of 2 on xtypes of X or P you
will find you startup procs and extended procs.
Sincerely,
Anthony Thomas
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
to
> execute automatically. I took over as a DBA for an existing system and I
was
> wondering if there are any SP configured this way.
> Thanks,
> rgn|||Hi rgn
You can use the OBJECTPROPERTY function.
SELECT name
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartup') =1
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
> the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
> to
> execute automatically. I took over as a DBA for an existing system and I
> was
> wondering if there are any SP configured this way.
> Thanks,
> rgn

A list of Auto exec SPs

Hello All,
A SP can be made to automatically execute when SQLServer restarts using the
store procedure "sp_procoption".
But is there a way to find out the list of SPs that have been congifured to
execute automatically. I took over as a DBA for an existing system and I was
wondering if there are any SP configured this way.
Thanks,
rgn
If you look at the definition for sp_procoption, you will discover the
following line of code:
UPDATE sysobjects SET status = (status & ~2) | (2 * @.intOptionValue) WHERE
id = @.tabid
This tells you, along with the rest of the definition, that if you query the
master.dbo.sysobjects table for status values of 2 on xtypes of X or P you
will find you startup procs and extended procs.
Sincerely,
Anthony Thomas

"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
to
> execute automatically. I took over as a DBA for an existing system and I
was
> wondering if there are any SP configured this way.
> Thanks,
> rgn
|||Hi rgn
You can use the OBJECTPROPERTY function.
SELECT name
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartup') =1
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
> the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
> to
> execute automatically. I took over as a DBA for an existing system and I
> was
> wondering if there are any SP configured this way.
> Thanks,
> rgn

A list of Auto exec SPs

Hello All,
A SP can be made to automatically execute when SQLServer restarts using the
store procedure "sp_procoption".
But is there a way to find out the list of SPs that have been congifured to
execute automatically. I took over as a DBA for an existing system and I was
wondering if there are any SP configured this way.
Thanks,
rgnIf you look at the definition for sp_procoption, you will discover the
following line of code:
UPDATE sysobjects SET status = (status & ~2) | (2 * @.intOptionValue) WHERE
id = @.tabid
This tells you, along with the rest of the definition, that if you query the
master.dbo.sysobjects table for status values of 2 on xtypes of X or P you
will find you startup procs and extended procs.
Sincerely,
Anthony Thomas
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
to
> execute automatically. I took over as a DBA for an existing system and I
was
> wondering if there are any SP configured this way.
> Thanks,
> rgn|||Hi rgn
You can use the OBJECTPROPERTY function.
SELECT name
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartup') =1
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
> the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
> to
> execute automatically. I took over as a DBA for an existing system and I
> was
> wondering if there are any SP configured this way.
> Thanks,
> rgn