CopyPaste.Net: January 2014

RESOURCE_SEMAPHORE wait type

RESOURCE_SEMAPHORE:

Long explanation: RESOURCE_SEMAPHORE Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.

Short explanation: memory grant pressure.

( Understanding SQL server memory grant: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx )

In order to get more data, use:

select * from sys.dm_exec_query_resource_semaphores

Returns the information about the current query-resource semaphore status.

http://technet.microsoft.com/en-us/library/ms366321.aspx

total_memory_kb - Memory held by the resource semaphore in kilobytes. check it against target_memory_kb or max_target_memory_kb values.
waiter_count - the number of queries waiting for grants to be satisfied.

select * from sys.dm_exec_query_memory_grants

Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute

http://technet.microsoft.com/en-us/library/ms365393.aspx

SELECT cntr_value AS [Memory Grants Outstanding]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$MyInstance:Memory Manager'
AND counter_name = 'Memory Grants Outstanding'

SELECT OBJECT_NAME,cntr_value AS [Memory Grants Pending]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$MyInstance:Memory Manager'
AND counter_name = 'Memory Grants Pending'

An useful link regarding RESOURCE_SEMAPHORE:

http://www.sqlserver-dba.com/2011/06/sql-wait-type-resource-semaphore-and-how-to-reduce-it.html

Find the waiting reason of a waiting_task - sys.dm_os_waiting_tasks

SELECT *

FROM sys.dm_os_waiting_tasks

WHERE [session_id] = 71 -- or without filter on a specific session

SET NOEXEC ON

SET NOEXEC ON - it's exactly what is sound like - compiles each query but does not execute it.

Example:

DBCC OPENTRAN - display current open transactions

DBCC OPENTRAN

Display the current open/active transactions that may cause locks.

Get DBCC CHECKDB results - checkdb with tableresults

DBCC checkdb ... with tableresults

Because it's not suggested to run DBCC CHECKDB when the DB is active (in order not to overload the and not disturb users) - it should be good to run it when the DB is inactive, and it's possible to do it in a job and insert the results into a log table:

-- create the log table:

CREATE TABLE [dbo].[checkdbHistory](

[Error] [int] NULL,

[Level] [int] NULL,

[State] [int] NULL,

[MessageText] [varchar](7000) NULL,

[RepairLevel] [int] NULL,

[Status] [int] NULL,

[DbId] [int] NULL,

[Id] [int] NULL,

[IndId] [int] NULL,

[PartitionID] [int] NULL,

[AllocUnitID] [int] NULL,

[File] [int] NULL,

[Page] [int] NULL,

[Slot] [int] NULL,

[RefFile] [int] NULL,

[RefPage] [int] NULL,

[RefSlot] [int] NULL,

[Allocation] [int] NULL,

[TimeStamp] [datetime] NULL DEFAULT (GETDATE())

) ON [PRIMARY]

-- this insertion csn be stored in a job:

INSERT INTO dbo.checkdbHistory

( [Error], [Level], [State], MessageText, RepairLevel,

[Status], [DbId], Id, IndId, PartitionId,

AllocUnitId, [File], Page, Slot, RefFile,

RefPage, RefSlot, Allocation)

EXEC ('dbcc checkdb(''DB_NAME'') with tableresults')

In order to do this to all of the databases, run the insertion in a cursor.

http://www.mssqltips.com/sqlservertip/2325/capture-and-store-sql-server-database-integrity-history-using-dbcc-checkdb/

SET DEADLOCK_PRIORITY

SET DEADLOCK_PRIORITY - is exactly what it sounds like: Specifies the relative importance that the current session continue processing if it is deadlocked with another session.

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }

<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
LOW maps to -5, NORMAL to 0, and HIGH to 5.

http://technet.microsoft.com/en-us/library/ms186736.aspx