pages/sec and context switches/sec too high

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

pages/sec and context switches/sec too high

Author

Topic

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-07-15 : 11:32:24

Hi,

I changed jobs a few weeks ago and taking care of new databases and servers.

As a proactive measure, I am running perfom on my servers for about a week (5 business days) On the most important one, I found these two values that grabbed my attention:

Pages/sec = 12.34
Context Switches/sec = 3500

Available RAM and page life expectancy look good, 2GB and 2060, so I do not see memory starvation here. I know there are lot of queries that need improvement plus the db suffers of over indexing (dealing with each issue at the time)

What could be the main reason for those two bad values on pages/sec and context switches, any ideas?

Thanks in advance...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-15 : 14:46:44

Is that 12 thousand 348 pages per second, or 12 comma 348 pages per second?

N 56°04'39.26"
E 12°55'05.63"

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-07-15 : 15:02:24

12 comma 348 ... which still, I find too high ... that's average, for 5 days data , I believe.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-15 : 18:25:32

All pages read are counted in this metrics. Even backups.
So 12.348 pages/sec indicates an average of 99kb/s which is not much.
I would be worried if the number went up to 100+ or even 1000+

N 56°04'39.26"
E 12°55'05.63"

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-07-15 : 22:10:21

quote:
Originally posted by SwePeso

All pages read are counted in this metrics. Even backups.
So 12.348 pages/sec indicates an average of 99kb/s which is not much.
I would be worried if the number went up to 100+ or even 1000+

N 56°04'39.26"
E 12°55'05.63"

Hmmm ... Sorry, I disagree.

I have two very old servers, less powerful, which page/sec is close to zero. As a matter of fact, this is the only server which such value is not close to zero.

I would like to know what would be possible reasons for this value to be so high.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-07-16 : 01:50:29

Could you supply : a) SELECT total_physical_memory_kb, available_physical_memory_kb,
total_page_file_kb, available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory;
This will give us some good information on available memory and clues on memory pressure.Could you try and get this information through a busy period
b) Just to check if the number indicates memory mapped files sequential reads (as opposed to memory thrashing) - check Memory: Available Bytes versus Paging File:% usage

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-07-16 : 11:31:56

quote:
Originally posted by jackv

Could you supply : a) SELECT total_physical_memory_kb, available_physical_memory_kb,
total_page_file_kb, available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory;
This will give us some good information on available memory and clues on memory pressure.Could you try and get this information through a busy period
b) Just to check if the number indicates memory mapped files sequential reads (as opposed to memory thrashing) - check Memory: Available Bytes versus Paging File:% usage

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Jack,

You just read my mind!

After further investigation and using that exact DMV this morning, I decided to go back and check MS-SQL memory settings. Upper limit was not set! (remember, I just received these servers, started to getting familiar with each)

I restarted my perfom but I noticed immediately that server's memory was pinned because MS-SQL. I do believe that was causing the paging issue on the server. Keeping an eye though, in order to confirm.

Regardless, I had to change that to a fix value. The server has 64GB, I put 18GB, initially...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-07-17 : 14:54:26

You didn't give SQL Server nearly enough memory (assuming this is the only instance on the box).

Start with 56-60GB.

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-07-17 : 17:22:35

quote:
Originally posted by russell

You didn't give SQL Server nearly enough memory (assuming this is the only instance on the box).

Start with 56-60GB.

Agree. Prior your post, I incremented the upper size limit more.

Thanks for reply.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-01 : 01:52:35

In deciding the upper limit to set include: a) memory required by OS b) other software on the server which has memory requirements.
Quite often monitoring agents and other systems management tools require memory - which is constrained if the memory is set to high on the SQL Server

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources