I was looking at SQL Server performance and noticed that CPU utilization goes high when the number of connections go high(I believe it is normal).Usually number of connections on an average was approximately 120 but from past 1 week the number of connections on an average goes to 200+ and someti...
Those 120 connections seems to have the same SID as the 70 "known" connections. Sysprocesses is an ancient "virtual table". What does sys.dm_exec_sessions say?
@H.79 and user9516827: It would likely be even better to just stop using the very old and deprecated as of SQL Server 2005 compatibility views (i.e. the ones starting with sys.sys*. It is far better to use some combination of: sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests, and sys.server_principals.
For the moment, try either SELECT * FROM sys.sysprocesses WHERE [loginame] = N''; and/or SELECT * FROM sys.dm_exec_connections con INNER JOIN sys.dm_exec_sessions ses ON con.[session_id] = ses.[session_id] LEFT JOIN sys.dm_exec_requests req ON req.[session_id] = con.[session_id] WHERE ses.[login_name] = N''; to get a sense of what is going on. No need to aggregate result when debugging as you lose all of the details.
@H.79 Yes, very much so. The documentation, docs.microsoft.com/en-us/sql/relational-databases/…, even confirms that it was only included in SQL Server 2005 and newer for backwards compatibility and should not otherwise be used.
@H.79 and user: yes, we know that from the original query / screen shot based on the SID being the same. What we need to figure out is why 120 rows for that login are missing the login_name value, but the other 70 rows for the same login have it filled out as expected.
@SolomonRutzky - There is certain information that lives in sys.syslogins that you can't get anywhere else, and is used by the beloved sp_help_revlogin stored proc which uses sys.syslogins - I know what the link says, but it's not going anywhere.
@SolomonRutzky - Write a custom query that joins tables based on the SID, and the user will know who and what is talking to his sql server. I see things like this all the time as a Data and AI consultant for Microsoft.
May I please request you to download sp_whoisactive(whoisactive.com) from Mr. Adam Machanic, It would be ready to use and will show much more meaningful result. You may also download first responder kit of Mr. Brent Ozar and that could be also of great help.
@user9516827 Can you at least execute this query: SELECT con.*, '--' AS [---], ses.*, '--' AS [---], req.* FROM sys.dm_exec_connections con INNER JOIN sys.dm_exec_sessions ses ON con.[session_id] = ses.[session_id] LEFT JOIN sys.dm_exec_requests req ON req.[session_id] = con.[session_id] WHERE ses.[login_name] = N''; ? Is there anything currently running for those? I am wondering if this is due to connection pooling.
@user9516827 Not sure which columns are most useful at this point, but at least: status, command, last_request_end_time, host_name, program_name, and host_process_id. Also, need to get at least one row of those where login name is the value that is showing up with 70 connections (to compare with).
@user9516827 Interesting. Only correlation I see so far is that the "empty" login names are currently running, while the rows with login names are currently sleeping (with 1 exception). Also, I did some testing on my own related to connection pooling, and this behavior does not seem to be due to that.
yeah i see that too.The queries from my application are complex and so they run in parallel.Is there any chance that multiple threads are running while the waiting/other threads are sleeping.?Ignore if i am misleading
@SolomonRutzky : please see the updated post.I found an interesting case.
@user9516827 what type of functions? T-SQL or SQLCLR or built-in? UDFs or TVFs? Also, are those unique session IDs, or are they shared with entries that do have login names filled out? Maybe remove the WHERE ses.[login_name] = N'' and instead add ORDER BY ses.[session_id].
Discussion on question by user9516827…
Discussion on question by user9516827…