- You install an instance of Microsoft SQL Server 2008 R2 or Microsoft SQL Server 2012 on a computer.
- Concurrent statements that resemble the following are executed in the instance:Or, you run a query that contains the following code:
CREATE PROC p AS RETURN (
); EXEC p;
- The CPU usage is consistently high, and the work threads are in runnable state.
- The contention for the QUERY_EXEC_STATS spinlock is high.
For example, the spin count, the possible collisions, and the back-off values for the QUERY_EXEC_STATS spinlock increase quickly.
Note For more information about how to monitor the QUERY_EXEC_STATS spinlock, see the "More information" section.
In this scenario, slow performance might occur.
Cumulative update information
SQL Server 2012The fix for this issue was first released in Cumulative Update 1 for SQL Server 2012. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
Cumulative update package 5 for SQL Server 2008 R2 SP1The fix for this issue was first released in Cumulative Update 5. For more information about how to obtain this cumulative update package for SQL Server 2008 R2 SP1, click the following article number to view the article in the Microsoft Knowledge Base:
SET NOCOUNT ON
CREATE TABLE #spins([Spinlock Name] varchar(50),Collisions numeric,Spins numeric,[Spins/Collision] float,[Sleep Time (ms)] numeric,Backoffs numeric, InsertTime datetime DEFAULT GETDATE())
DECLARE @counter int = 1
WHILE @counter < 10
INSERT INTO #spins ([Spinlock Name], Collisions, Spins, [Spins/Collision], [Sleep Time (ms)], Backoffs) EXECUTE ('DBCC SQLPERF (''SPINLOCKSTATS'') WITH NO_INFOMSGS')
WAITFOR DELAY '00:00:05'
SET @counter +=1
SELECT * FROM #spins WHERE [Spinlock Name] = 'QUERY_EXEC_STATS' ORDER BY InsertTime
DROP TABLE #spins
Article ID: 2662301 - Last Review: Nov 19, 2012 - Revision: 1