Paul Hayes-294329 (10/29/2009)
a resource semaphore wait type is typically waiting on a memory grant, check pending/outstanding memory grants in perfmon. That's normally because a query plan has compiled in a "non-optimal" fashion.You can identify which queries are causing this to occur by running the query below.
You may need to be more selective with youre where clause or add an index, identify the query, go through the tuning process and sort it out.
Production server you say, maxdop to 1 mate, every time. Thats many years of experience talking. Maxdop is a waste of time on anything but pure reporting systems in my experience. ...Just turn parallelism off for a more predixctable experience IMO.
--Replacement for master.dbo.sysprocesses
...
Thanks very much for the query identifying queries waiting for memory grants, I'll add to my tool box.
BTW, I totally agree on the MAXDOP comment, I've been having the same experience.
CXPACKET waits (parallelism) are just a mask of something more sinister going on with query performance, and not just on Halloween night... :w00t:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]