So I was fiddling around with SharePoint 2013, and everything on my workstation seemed rather slow. I checked the task manager and my SQL Server was consuming so many CPU power, like all the time.
Checking the resource monitor didn’t show much, so I had to dig down deeper. I fired up my SQL Server Management Studio and copy pasted the following query:
SELECT * FROM sys.dm_exec_requests a OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b WHERE session_id > 50 and session_id <> @@spid
When you check out the text column, you’ll see lots of queries being executed like “CREATE PROCEDURE”.
If you check the ULS logs you’ll see a lot of “slow queries” and when you check your SQL logs, you’ll see lots of warnings about insufficient memory available and that paging slows down your queries. Well, it does. If you’re on a “low memory” machine (I’m running on a virtual machine with 6GB) it’s best to give your SQL server some air. Navigate to your SQL Server properties:
Configure a minimum amount of memory (default is 0):
And boost your SQL Server’s priority: