This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(176, 25%, 27%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKS%3C/text%3E%3C/svg%3E)
This happens only with a large Windows App. If I have Visual Studio open and I change a simple line of code and then run my app, it gets a timeout 95% of the time. If I close Visual Studio and re-open and run the app then it works. It is frustrating; I now do a build then shutdown Visual Studio and restart Visual Studio so I can run the app without waiting 60 seconds for the timeout. The app never times out in production or on my desktop if Visual Studio is not running. Can anyone tell me how to determine what causes this timeout?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(249.60000000000002, 10%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAX%3C/text%3E%3C/svg%3E)
Hello, do you mean that it occurs in specific project? Please view: Troubleshoot query time-out errors and check if the app sets a time-out value.
This definitely occurs only in a specific large solution (of 13 projects). Even with this large app, it never encounters a timeout on the Customer's PC or on my PC if I don't have Visual Studio open. I will take a look at the trouble-shooting link you provided.
This definitely occurs only in a specific large solution (of 13 projects). Even with this large app, it never encounters a timeout on the Customer's PC or on my PC if I don't have Visual Studio open. I will take a look at the trouble-shooting link you provided. Running SP_WHO2 I can see the status of SUSPENDED but nothing blocked. How can I find out why it is suspended?
I can see the status of SUSPENDED but nothing blocked. How can I find out why it is suspended?
That would suggest that it is executing, but currently waiting for something. That could be disk I/O if it is running some vile query. My beta_lockinfo will show what it currently is running.
- Version of SQL Server (stayed on 2008 R2 because 2012 caused nightmares with IDENTITY column jumping by thousands).
That is nothing you should care about. The IDENTITY feature exist to permit high-concurrent inserts into a table, and it is designed to produce gaps. It writes the current value to disk only for every thousand rows, exactly to make things quicker. It is intended for surrogate keys where you don't care about the values. If you care about the values, you should not use IDENTITY, but roll your own.
(There is an option to turn of caching, but why would you press a go-slower button?)
And you can't stay on SQL 2008 R2 forever. It is out of support and outdated. Time to move on.
As for your issue, it is as I started to suspect. The RESOURCE_SEMAPHORE waits together the information that you have 8 GB of RAM tells me that you are low on memory. You could check in Task Manager how much RAM Visual Studio takes up. But now that it is 64-bit, it can ask for quite a bit I guess. Personally, I would not like a laptop with less than 32 GB of RAM.
Task Manager showed 95% memory usage with everything running and timing out so I suspect you are correct that low memory is the culprit. I will try to confirm and update my comments after I get more memory. Sadly the app goes back a long time (2010-ish) and there is a reliance on the Identity column so we just stuck with 2008 R2 because everything works. Then we also had the problem where edmx files got outdated but we used them everywhere so we kept building EF with VS 2010 since it creates edmx files easily. I am the sole developer/designer/tester/support so I try not to change things that are working. All of my newer stuff uses EF Code First (still runs on old SQL 2008 R2). I have some Blazor Server apps on Azure so it's nice that I don't have to worry about obsolete systems there.)
there is a reliance on the Identity column so we just stuck with 2008 R2 because everything works.
You cannot rely on anything with IDENTITY columns even in SQL 2008. Their promise is to permit concurrent inserts. Not to give you an unbroken series of numbers.
Try this:
CREATE TABLE #temp(id int IDENTITY NOT NULL,
data int CHECK (data > 1))
go
INSERT #temp(data) VALUES(99)
go
INSERT #temp(data)
VALUES(9), (8), (7), (6), (-1)
go
INSERT #temp(data) VALUES(106)
go
SELECT *FROM #temp ORDER BY id
go
DROP TABLE #temp
SQL 2008 R2 is out of any level of support for quite a few years now. You should definitely look into moving to SQL 2022. There is a setting to turn of the identity cache, but if you are relying on contiguous numbers, you should move away from IDENTITY altogether.
You have been extremely helpful and patient as we worked through my original problem (which has frustrated me for a year or more). Hopefully (and likely) additional memory will solve that problem. I know I should move to current SQL Server. Our IDENTITY column has worked very well for 10+ years; I experimented with SEQUENCE (we can tolerate small gaps but not gaps of thousands for that specific app). None of the new apps use IDENTITY. What do you recommend if I really wanted a sequential integer with tolerance for gaps of 10 or less? For this customer this old app is the only one utilizing SQL Server on his small network of 2 PCs.
Anyway, I appreciate all the time you spent helping me.
I will have to admit that I can't see a situation where you can tolerate small gaps, but not big gaps. I have worked with a system where it was a requirement that voucher numbers were entirely contiguous. That is, no gaps at all were permitted. We implemented those numbers with one-column one-row tables that held the counters. But they also became serialisation points, which in the end hampered concurrency a bit too much.
I'm not working with that system anymore, but I recall that I outlined a scheme where we would use sequences and then sort of track the gaps, so we could keep auditors happy. I don't know exactly what happened, but I know that they got a new customer of the system with very stiff requirements on performance.
With sequences you can control the cache size and set it to any number you like. But if you try to insert 1000 rows, and the last row is invalid, you will have a gap of 1000 rows, no matter the cache size.
If there are only 2PCs, I guess high concurrency is not an issue, and there is no need to consider high concurrency. Then it is better to roll your own. You don't need a separate table. You can do:
BEGIN TRANSACTION
SELECT @id = isnull(MAX(id), 0) + 1 FROM tbl WITH (UPDLOCK)
INSERT tbl(id, ...)
SELECT @id, ....
COMMIT TRANSACTION
The UPDLOCK will cause serialisation, but with a low level of concurrency, that should not be a problem.
Well thank you Erland. I now have 24 GB memory (instead of 8 GB) and I have not encountered an SQL timeout after several attempts to cause my normal failure. I have been frustrated with this problem for over a year and I would never have suspected a memory problem, but you worked with me for several days and never gave up looking for my problem. I appreciate it.
Sometime hardware upgrades can do wonders! I remember when, many years ago, I replaced my spinning system disk to an SSD. My machine was now useful 2-3 minutes after a reboot as opposed to 20-30 minutes before the change.
My prime guess would be a blocking issue. Somehow you have left something with an open transaction, and then the app gets blocked on that open transaction.
Make sure you have SSMS or Azure Data Studio running. Do your Visual Studio thing and start the app. Run sp_who. Keep an eye on the Blk column. It there is a number in that column. that is the spid on that row.
You can also use my beta_lockfinfo which gives you a lot more details, including locks, so you can see what the process is waiting on. You also see the current query and its query plan, in case this is a query-plan issue (which would be the other alternative.)
I will act on your suggestions; it may take a while to familiarize myself with sp_who and the beta_lockfinfo. Thanks for your suggestions.
I ran beta_lockinfo with nothing running on my Microsoft Server 2008 R2 (SQL Express). it completed immediately with only a header row of output (spid...) and no data lines (no **** either).
Then I started my problematic app which causes the timeout after 60 seconds. I ran beta_lockinfo and it hung. My app timed out in 60 seconds (as usual) but beta_lockinfo was still running. I let it run over a minute and then cancelled it. I don't know if this helps narrow down my problem.
Occasionally my app does not time out and I ran beta_lockinfo in that scenario. I do see this:
Is that a clue? If yes, does it explain why I only have this problem when Visual Studio is running?
I ran beta_lockinfo again and let it keep running after my app timed out. Here are results.
More output:
It is more interesting to run beta_lockinfo while the app is just hanging and you are waiting for the timeout to elapse.
It seems that you got more than these two lines. I can see that spid 53 is a lead blocker because of those double exclamation mark in the blk. This would suggest that there are more rows in the output, and some of these rows would have a number on the Blk column. A number 1 means that the process is blocked by the lead blocker, a 2 means that it is number two in the block queue etc.
beta_lockinfo does not normally list itself, but if the process is blocking another session, it will.
The query plan tells me little since I don't know your database. It seems to be doing something like
SELECT col1, col2, ... SOME_AGGREGATE()
FROM (SELECT col1, col2...
FROM tbl1
UNION ALL
SELECT col1, col2, ...
FROM tbl2) AS u
GROUP BY col1, col2, ...
Does not look the most horrible query, but I don't know how big these tables are.
Here is my latest scenario with interesting results.
Oops, that's a little unexpected. Ideally, beta_lockinfo should return in a few seconds, but if the system under load, it take longer time. (In which case usually interrupt it, not to burden the system even more.)
The fact that beta_lockinfo completes once you close beta_lockinfo suggests that beta_lockinfo itself is blocked, one way or another.
This can take some time to troubleshoot, given that I will have to remote-control you. I hope you have the patience.
SELECT wt.session_id, wt.exec_context_id, wt.wait_duration_ms,
wt.wait_type, wt.blocking_session_id, wt.blocking_exec_context_id,
wt.blocking_task_address
FROM sys.dm_os_waiting_tasks wt
LEFT JOIN sys.dm_exec_sessions s ON s.session_id = wt.session_id
WHERE isnull(s.is_user_process, 1) = 1
I appreciate all your help. I am glad there are not timeouts after rebuild solution and restart Visual Studio. Otherwise I would have to publish/test/publish/test etc. Here are answers:
1. Version of SQL Server (stayed on 2008 R2 because 2012 caused nightmares with IDENTITY column jumping by thousands).
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) Mar 19 2015 12:32:14
Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.3 <X64> (Build 19045: )
2. I have 8 GB RAM
OS Name Microsoft Windows 10 Pro
Version 10.0.19045 Build 19045
Other OS Description Not Available
Processor Intel(R) Core(TM) i7-4770 CPU @ 3.40GHz, 3401 Mhz, 4 Core(s), 8 Logical Processor(s)
Installed Physical Memory (RAM) 8.00 GB
3. Microsoft Visual Studio Community 2022 (64-bit) - Current Version 17.8.2
(but I believe the problem occurs even on my older version 2019 and older).
4. SELECT COUNT(*) FROM sys.dm_tran_locks
- shows 10 while app is about to timeout
- shows 1 after app timeout (and stays at 1 until close empty app screen)
- shows 0 after app closed (but Visual Studio still open)
5.
session_id exec_context_id wait_duration_ms wait_type blocking_session_id blocking_exec_context_id blocking_task_address
53 0 878 RESOURCE_SEMAPHORE NULL NULL NULL
session_id exec_context_id wait_duration_ms wait_type blocking_session_id blocking_exec_context_id blocking_task_address
53 0 20212 RESOURCE_SEMAPHORE NULL NULL NULL
I should also point out that I am using very old Entity Framework (.NET Framework 4) which I build using Microsoft Visual Express 2010 because it generates an HNPdataModel.edmx. This becomes one of the projects in my app that times out.