Memory is not releasing and performance issue??

Question

Memory is not releasing and performance issue??

Lavanyasri

SSCarpal Tunnel

Points: 4907
More actions
November 14, 2011 at 3:37 am

#262663

HI Team ,
I am working with SQL server 2005 standard version 64 bit sp3 with windows 2008 64 bit.
20 GB RAM. All databases is in simple recover model.
I unchecked AWE and configured max memory to 17.5 GB .
I am running perfmon counter .
1.Last week[upto Friday] i found that around 10GB Available MB bytes are available . But today i found that only 3GB available MB bytes are available.
Why the SQL server is not releasing the memory ???
on every weekend we are running one maintenance plan with rebuild index,update statistics and database integrity.
2.SQL server general statistics : user connections and SQl server databases : transaction\sec
when the user connection increases ,transaction per sec increases the processor time goes to 100 % .
3. Most of the times i am seeing SQl server buffer manager : Free pages counter is less than 300 , in most of the times .
what is recommended value for this counter ???
4. When the processor time is 100 % i am running the below query .
SELECT
scheduler_id,current_tasks_count,runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
we have 2 processors :
its showing current task counts runnable task counts .
0 113 104
1 120 106
Can you advise me on this counters ?? we need to add extra CPU ??
I need u r suggestions please ........
Regards,
Lavanya Sri

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Lavanyasri (11/14/2011)
Why the SQL server is not releasing the memory ???

Because it's not supposed to. What you're seeing is the expected, documented behaviour. SQL won't release memory unless it has to (why would it, it uses memory so as to cache data?)

4. When the processor time is 100 % i am running the below query .
SELECT
scheduler_id,current_tasks_count,runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
we have 2 processors :
its showing current task counts runnable task counts .
0 113 104
1 120 106
Can you advise me on this counters ?? we need to add extra CPU ??

By the looks of things you seriously need to add a lot more CPU or you need to do some major optimisation of your code.

http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Chapters 3 and 4.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 2

Thanks Monster ,

Can you help me on this counter please ::

3. Most of the times i am seeing SQl server buffer manager : Free pages counter is less than 300 , in most of the times

Thanks

Lavanya

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ok, so it's less than 300. What's normal for your system?

Frankly it looks like you need to either do some serious tuning of your queries, or you need to get someone in to do some serious tuning of your queries.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 4

Thanks Monster,

I ran the below query and attached the output file with this mail .

select * from sys.dm_os_wait_stats .

Can you please look into the attached file .

I am not good at locks . So i need u r help .

Thanks

Lavanya

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Lavanyasri (11/14/2011)
Thanks Monster,
I ran the below query and attached the output file with this mail .
select * from sys.dm_os_wait_stats .
Can you please look into the attached file .
I am not good at locks . So i need u r help .
Thanks
Lavanya

If you want to do anything useful with waits stats you need to use a query similar to this =>

/*

Cost threshold for parallelism (CXPACKET) http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/19/tuning-cost-threshold-of-parallelism-from-the-plan-cache.aspx

Paul White: Understanding parallelism http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/

Microsoft White Paper on waits http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc

Next query by Paul Randal http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx

*/

WITH Waits AS

(SELECT

wait_type,

wait_time_ms / 1000.0 AS WaitS,

(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,

signal_wait_time_ms / 1000.0 AS SignalS,

waiting_tasks_count AS WaitCount,

100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum

FROM sys.dm_os_wait_stats

WHERE wait_type NOT IN (

'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',

'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',

'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',

'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',

'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',

'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR')

)

SELECT

W1.wait_type AS WaitType,

CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,

CAST (W1.WaitS * 1000 / W1.WaitCount AS DECIMAL(14, 2)) AS AvgWait_MS,

CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,

CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,

W1.WaitCount AS WaitCount,

CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.RowNum <= W1.RowNum

GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage

HAVING SUM (W2.Percentage) - W1.Percentage < 97 -- percentage threshold

AND W1.WaitCount > 0;

GO

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Ok, and?

You have to make some effort here. I referred you to a very good book in an earlier post. Have you looked at it?

What about the wait stats concerns you? What specific questions do you have?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 7

HI Monster,

I reading parallel with my work.

Just now i had a issue of cpu 100 %. Please find the perfmon counter values ::

with attached document .

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Lavanyasri (11/14/2011)
HI Monster,
I reading parallel with my work.
Just now i had a issue of cpu 100 %. Please find the perfmon counter values ::
with attached document .

For how long?? Spikes are normal. What's your baseline? what's the current avg use?

Did you read & act on those? =>

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 9

HI All,

Wish you a happy new year.

In my environment every weekend maintenance plan is running with rebuild index,database integrity.I am running a perfmon counters with Available MBytes.After completing the maintenance plan the total available Mbytes are showing less than 400 . The problem is the SQl server is not releasing the memory .

If any possibility can we release the SQl server memory manually i.e by using any scripts or changing the settings??

Thanks

Lavanya

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Lavanyasri (1/3/2012)
The problem is the SQl server is not releasing the memory .

That's not a problem, that's how SQL Server works, by design and it's documented to work that way.

If SQL is taking too much memory, reduce the setting 'max server memory' so that there's more left free.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 11

Thanks Monster.

For my performance issues i am referring the below book :

SQL server 2005 administrator's companion ::

[processor bottle neck]

1.The Processor-Context Switches/sec counter should not be excessively high per processor. (Use 8,000 as a very rough threshold.)

In my environment total 4 processors : i ran the system:Context Switches\sec counter : the avg value of the counter is more than 40,000.

[Memory bottle neck ]

2.Examine the Memory: Pages/sec and Memory: Page Faults/sec counters in System

Monitor. Ideally they should be as close to zero as possible. Sustained values

greater than two, for example, indicate a problem.

In my environment : Memory: Page Faults/sec counter value is always greater than zero and its varying from sec to sec.

any help on this ???

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

1) Optimise your queries (see the links Ninja posted back in November)

2) Reduce server max memory to a sensible value

p.s. And get that book I recommended back in November and read it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass