RESOURCE_SEMAPHORE - SQL Server Forums

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 2012 Forums

SQL Server Administration (2012)

RESOURCE_SEMAPHORE

Author

Topic

AustraliaDBA
Starting Member

38 Posts

Posted - 2014-08-28 : 19:13:18

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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-29 : 07:53:44

see: http://www.mssqltips.com/sqlservertip/2827/troubleshooting-sql-server-resourcesemaphore-waittype-memory-issues/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-08-29 : 10:51:13

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

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-08-30 : 07:22:19

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-30 : 07:42:50

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

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-08-30 : 15:55:22

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-31 : 01:53:38

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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-08-31 : 03:31:26

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

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-08-31 : 08:50:35

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources