August 23, 2008 at 1:07 pm
I have been getting these messages very frequently from our performance diagnostic tool:
8/23/2008 10:14:17 AM, OS Paging (Per Second) on is Critical.
SQL Server instance - The operating system is paging at 3146.24 pages/second.
OS Paging (Per Second): Page fault rate for all processes on the SQL Server computer.
It seems that the database server is under memory pressure, but any additional feedback on this would be appreciated.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 24, 2008 at 12:58 am
What are the box specifics? Mem, CPU, Page File size, OS
SQL 2005? 64bit?
Any other applications running outside of normal Windows processes?
Are you limiting memory to SQL? (I would suspect not... due to the result)
Are you using AWE?
August 24, 2008 at 6:50 am
Sean Kohler (8/24/2008)
What are the box specifics? Mem, CPU, Page File size, OSSQL 2005? 64bit?
Any other applications running outside of normal Windows processes?
Are you limiting memory to SQL? (I would suspect not... due to the result)
Are you using AWE?
Sorry, should have provided more info. Here it is:
RAM: 15.5 GB
CPU: Intel(R) Xeon(TM) MP CPU 2.70 GHz
PageFile Size: how do I locate the page file?
OS: Windows Server 2003 Enterprise Edition SP1
SQL 2000 SP4
32-bit
This is a dedicated database server - no other user apps running.
We are using AWE: we have locked the working set to 11.5 GB (we should probably increase that).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 25, 2008 at 9:27 am
1) >> We are using AWE: we have locked the working set to 11.5 GB (we should probably increase that).
NO you shouldn't, not if you are already seeing paging. Increasing SQL memory can only make things worse, not better.
2) in task manager, show additional columns Page Faults and PF Delta and find out what is causing the paging. I have seen WMI go out to lunch on a number of client's system and page like crazy.
3) Just to make sure, have you set /PAE in your boot.ini file so AWE will actually work?
4) check out the undocumented dbcc memorystatus and check for anomolies against the online article about that command.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 25, 2008 at 10:01 am
You need to look at the type of paging to know if there is a problem.
Running backups can cause paging as the OS allocates file system cache, and this is a common cause of paging on a SQL Server and not usually a cause or concern.
In addition, a lot of paging does not mean that anything is actually being written into the page file. You really need to look at the paging read and write rates.
August 25, 2008 at 10:53 am
TheSQLGuru (8/25/2008)
1) >> We are using AWE: we have locked the working set to 11.5 GB (we should probably increase that).NO you shouldn't, not if you are already seeing paging. Increasing SQL memory can only make things worse, not better.
2) in task manager, show additional columns Page Faults and PF Delta and find out what is causing the paging. I have seen WMI go out to lunch on a number of client's system and page like crazy.
3) Just to make sure, have you set /PAE in your boot.ini file so AWE will actually work?
4) check out the undocumented dbcc memorystatus and check for anomolies against the online article about that command.
1) I thought increasing SQL memory would cause less paging, not more, as long as the set value is a couple of GB below the RAM value.
2) How do I do that?
3) Yes, Physical Address Extension is enabled.
4) Will do
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 25, 2008 at 10:54 am
Michael Valentine Jones (8/25/2008)
You need to look at the type of paging to know if there is a problem.Running backups can cause paging as the OS allocates file system cache, and this is a common cause of paging on a SQL Server and not usually a cause or concern.
In addition, a lot of paging does not mean that anything is actually being written into the page file. You really need to look at the paging read and write rates.
Thanks, I will look at what kind of apps are running at the time.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 25, 2008 at 1:22 pm
Task manager: Processes tab: View menu: Select Columns
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 30, 2009 at 12:48 pm
I am currently experiencing some issues as well. Attached is a view of task manager on the server.
RAM: 3.25GB
CPU: Intel Xeon 5150, 2.66GHz
Windows Server 2006 Std Edition
SQL Server 2005 Std Edition
Getting a lot of OS disk time alerts and OS average disk queue length alerts. I have also attached our S and L drive info...we have most log and data files separated out.
January 30, 2009 at 2:05 pm
checkai (1/30/2009)
I am currently experiencing some issues as well. Attached is a view of task manager on the server.RAM: 3.25GB
CPU: Intel Xeon 5150, 2.66GHz
Windows Server 2006 Std Edition
SQL Server 2005 Std Edition
Getting a lot of OS disk time alerts and OS average disk queue length alerts. I have also attached our S and L drive info...we have most log and data files separated out.
You did not give us nearly enough information to help you. But I will say that you don't have much ram, and only 1 cpu?? What is the underlying IO configuration? Single drive in server partioned in to 3? RAID/ SAN??
If you have poor IO performance you can
1) fix queries to be more efficient
2) improve indexing strategy to reduce IO demands
3) buy more RAM
4) buy more / faster drives
5) fix configuration problems with IO system if any
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply