0

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).

Any ideas?

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

UPDATE:

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
  • 1
    is absolutely everything running locally and are you relatively patched up with firmware/updates/etc. Is your windows/disk caching doing OK as far as you can tell? – Ali Razeghi Sep 2 '16 at 20:19
  • @AliRazeghi yes. It's a 2-month old computer with Window 10 computer with all drivers up to date AFAIK. Nothing is running outside of the laptop. Nothing weird installed or running AFAIK: SQL Server, VS.NET, and other desktop utilities. Not even an AntiVirus (besides Windows' one). – Diego Jancic Sep 2 '16 at 20:46
  • 1
    SQL Server needs more memory. – Tara Kizer Sep 2 '16 at 20:50
  • 1
    @TaraKizer For a dev machine running like 2 queries per minute or expanding the Tables folder in the Management Studio? No way. – Diego Jancic Sep 2 '16 at 20:53
  • 1
    @DiegoJancic, the output from sp_Blitz indicates memory pressure. RESOURCE_SEMAPHORE is a poison wait. You've got resource issues. But do be sure you are on the latest service pack and hotfix for SQL 2008. – Tara Kizer Sep 2 '16 at 23:50
2

If you want a fast, easy, free health check that flags common issues in seconds, and for each warning, gives you a link to a web page with more in-depth advice.

First steps for you:

1

After double checking what @AliRazeghi suggested, I've found that there were updates for SQL Server that were not being installed.

After installing these 2 updates, SQL Server seems to be consuming 1.5GB less of memory and working fine (for now at least):

As @TaraKizer pointed out, it was a memory problem. The solution wasn't add more memory as it shouldn't be needed, but it seems there was a leak or something in the pre-SP1 version of SQL Server 2014.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.