SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 RESOURCE_SEMAPHORE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AustraliaDBA
Starting Member

30 Posts

Posted - 08/28/2014 :  19:13:18  Show Profile  Reply with Quote
Hi There,

I need help in resolving thi issue. i have SQL server 2012 7GB memory assigned to thsi instance. when i look in task manager this instance is using only 1.5GB. but when i look at sysprocesses most of them have wait type RESOURCE_SEMAPHORE causing peformance issues on application. please advise what should i do i have no idea what is going on.
CommandType LastWaitType
BULK INSERT LCK_M_X
DBCC TABLE CHECK RESOURCE_SEMAPHORE
SELECT RESOURCE_SEMAPHORE
SELECT RESOURCE_SEMAPHORE
SELECT RESOURCE_SEMAPHORE
SELECT RESOURCE_SEMAPHORE
SELECT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
DELETE BROKER_RECEIVE_WAITFOR
SELECT RESOURCE_SEMAPHORE
BULK INSERT RESOURCE_SEMAPHORE
SELECT RESOURCE_SEMAPHORE
SELECT RESOURCE_SEMAPHORE
SELECT RESOURCE_SEMAPHORE

any help will be much apprciated

Thanks

gbritton
Flowing Fount of Yak Knowledge

1094 Posts

Posted - 08/29/2014 :  07:53:44  Show Profile  Reply with Quote
see: http://www.mssqltips.com/sqlservertip/2827/troubleshooting-sql-server-resourcesemaphore-waittype-memory-issues/
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 08/29/2014 :  10:51:13  Show Profile  Visit jackv's Homepage  Reply with Quote
Don't use task manager for memory usage reporting , use sys.dm_os_process_memory , check here for a query
http://www.sqlserver-dba.com/2013/05/task-manager-not-showing-correct-sql-server-memory-usage.html

For RESOURCE SEMAPHORE troubleshooting use these steps - http://www.sqlserver-dba.com/2011/06/sql-wait-type-resource-semaphore-and-how-to-reduce-it.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Shanky
Yak Posting Veteran

United Kingdom
71 Posts

Posted - 08/30/2014 :  07:22:19  Show Profile  Reply with Quote
Hi,
7G seems less memory to me for SQL Server. Although its just a guess. As already mentioned task manager is not correct place to look for SQL Server memory consumption especially when SQL Server service account has Locked pages in memory privilege as task manager would only show working set not memory allocated via AWE API. That is why task manager shows 1.5 G.
if you have SQL Server 2005 and above you can use below query to find correct memory utilized by SQL Server. Taken from my article http://social.technet.microsoft.com/wiki/contents/articles/22316.sql-server-memory-and-troubleshooting.aspx

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

Now regarding resource semaphore wait you must look at counter SQLServer:Memory Manager--Memory Grants Pending if value is non zero its a sign of memory pressure and you should add more RAM. Below article will also help in understanding semaphore waits
http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

Hope this helps

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/30/2014 :  07:42:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The 7 gig max memory is an upper limit that SQL Server will not exceed.
However, if min memory is set to 0 (default) SQL Server will only utilize as much memory as needed.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Shanky
Yak Posting Veteran

United Kingdom
71 Posts

Posted - 08/30/2014 :  15:55:22  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

The 7 gig max memory is an upper limit that SQL Server will not exceed.
However, if min memory is set to 0 (default) SQL Server will only utilize as much memory as needed.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



Just to make things more clear SQL Server can take memory beyond value given in max server memory. This is because memory for large page request is not satisfied by buffer pool and is done by windows API directly which again comes under memory consumed by SQL Server. 7 G is max restriction for buffer pool

Hope this helps

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/31/2014 :  01:53:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think that changed with SQL Server 2012.
See http://mssqlwiki.com/2012/10/21/sql-server-2012-memory-2/



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 08/31/2014 :  03:31:26  Show Profile  Visit jackv's Homepage  Reply with Quote
For the resource semaphore and if you're limited to 7 GB identify Memory intensive queries - sorting and hashing are the main underlying reasons. The GROUP BY clause and ORDER BY clause use sorting and hashing.
Optimize those queries .
If this doesn't clear your problem - follow further steps on the link i posted earlier



Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Shanky
Yak Posting Veteran

United Kingdom
71 Posts

Posted - 08/31/2014 :  08:50:35  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

I think that changed with SQL Server 2012.
See http://mssqlwiki.com/2012/10/21/sql-server-2012-memory-2/



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



Yes it changed quite a few but still there are memory allocations which are done outside buffer pool and in 2012 as well buffer pool does not control all memory allocated to SQL Server. If you read Karthick's article you can see memory for stack structures and DLL's still are satisfied outside buffer pool

Hope this helps

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000