My DEV instance of SQL Server is super slow for some reason from time to time. It doesn't happen all the time, just sometimes and there are queries that take a very long time. But it's not just that, sometimes just expanding the "Stored Procedures" or the "Tables" folder in the Management Studio takes several seconds. Sometimes doing a simple query also takes several seconds.
I'm working on a small database (~300mb) and in a new laptop with an i7, 8GB of memory and a SSD drive.
Part of the time the SSD goes to 100% when performing slow. However, I don't see a slow down in any of the other applications I use.
I believe the problem is only in one database, but I ran
DBCC CHECKDB already and everything was fine. The
LOG file is pretty small and both the
DATA and the
LOG files have space available, so it's not expanding.
It's not just a problem with the Management Studio as the ASP.NET application I'm working on works slow too sometimes. Also, it's not a problem with indexes or anything like that since I'm running a much bigger copy of the database and it works fine (needless to say, such a small database shouldn't have that problem even if it hasn't any indexes).
Some configurations I've already checked:
- Max server memory: 2.5GB
- Auto-close/auto-shrink db: False
- Auto Create/Update Statistics: True
- DB compatilibity level: MSSQL 2008 (100)
- Recovery model: Full
- Allow snapshot isolation
I ran Blitz and couldn't find the problem either, these message appear and might or might not be related:
- At least one NUMA node is reporting THREAD_RESOURCES_LOW in sys.dm_os_nodes and can no longer create threads.
- 7 forced grants reported in the DMV sys.dm_exec_query_resource_semaphores, indicating memory pressure has affected query runtimes.
And running BlitzFirst:
- THREADPOOL (avg ms per wait): 130832.4
- RESOURCE_SEMAPHORE (avg ms per wait): 17202.5