| Author |
 Topic  |
|
|
sql-lover
Yak Posting Veteran
59 Posts |
 Posted - 07/15/2012 : 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... |
Edited by - sql-lover on 07/15/2012 15:03:10
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/15/2012 : 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
59 Posts |
Posted - 07/15/2012 : 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
Sweden
29138 Posts |
Posted - 07/15/2012 : 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
59 Posts |
Posted - 07/15/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1775 Posts |
Posted - 07/16/2012 : 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
59 Posts |
Posted - 07/16/2012 : 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
USA
5000 Posts |
Posted - 07/17/2012 : 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
59 Posts |
Posted - 07/17/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1775 Posts |
Posted - 08/01/2012 : 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 |
|
|
| |
Topic |
|
pages/sec and context switches/sec too high
