Click here to Skip to main content
15,886,258 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Memory consumption is high on my server 
SQL server is installed on my server and my database is SAP database i.e Data is coming from sap server.


there is lot of query in sleeping session.
is this query can cause memory high.

SPID	 Status   	DBName	    Command           CPUTime	   DiskIO
52	sleeping        MYDB	 AWAITING COMMAND	52284	    66383

all following queries are in above given format and is sleeping stats and most of queries have high disk IO


should i kill all queries which are in sleeping mode.
if i kill these sleeping session. is there any impact on SAP users.

----------------------------------------------------------------------


<pre>SPID	Status	Login	HostName	BlkBy	DBName	Command	CPUTime	DiskIO	LastBatch	ProgramName	SPID	REQUESTID
52	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	52284	66383	1/9/2020 11:25	R3D18 comm rd ODBC                            	52	0
53	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	402502	778144	1/9/2020 12:04	R3D18 unc rd ODBC                             	53	0
54	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	232675	290642	1/9/2020 11:33	R3D19 comm rd ODBC                            	54	0
55	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	5697537	66671897	1/9/2020 12:04	R3D19 unc rd ODBC                             	55	0
56	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	357309	591459	1/9/2020 11:49	R3D28 comm rd ODBC                            	56	0
57	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	137247	346344	1/9/2020 11:56	R3D28 comm rd ODBC                            	57	0
58	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	12518163	66886188	1/9/2020 12:04	R3D28 unc rd ODBC                             	58	0
59	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	3669336	47097168	1/9/2020 11:56	R3D28 unc rd ODBC                             	59	0
60	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	144621	249727	1/9/2020 12:04	R3D15 comm rd ODBC                            	60	0
62	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	25047472	578263	1/9/2020 12:04	R3B42 comm rd ODBC                            	62	0
63	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	52287401	60779976	1/9/2020 12:04	R3B42 unc rd ODBC                             	63	0
64	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	304933	464837	1/9/2020 11:53	R3D00 comm rd ODBC                            	64	0
65	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	8843948	35546561	1/9/2020 12:04	R3D00 unc rd ODBC                             	65	0
66	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	325972	519703	1/9/2020 11:49	R3D24 comm rd ODBC                            	66	0
67	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	6987624	155621062	1/9/2020 11:49	R3D24 unc rd ODBC                             	67	0
68	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	1366168	8166944	1/9/2020 12:04	R3U30 comm rd ODBC                            	68	0
69	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	1680554	740012	1/9/2020 12:04	R3U30 unc rd ODBC                             	69	0
70	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	70845	126059	1/9/2020 11:49	R3D21 comm rd ODBC                            	70	0
71	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	2033088	9785251	1/9/2020 12:04	R3D21 unc rd ODBC                             	71	0
72	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	301296	967509	1/9/2020 11:55	R3250 comm rd ODBC                            	72	0
73	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	181007	37425	1/9/2020 11:55	R3250 unc rd ODBC                             	73	0
74	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	39372	17944	1/9/2020 12:00	R3S47 comm rd ODBC                            	74	0
75	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	26662	31027	1/9/2020 11:56	R3D29 comm rd ODBC                            	75	0
76	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	14030	38464	1/9/2020 12:04	R3D02 comm rd ODBC                            	76	0
77	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	432440	15495180	1/9/2020 12:04	R3D02 unc rd ODBC                             	77	0
78	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	2163926	321423	1/9/2020 12:01	R3B42 comm rd ODBC                            	78	0
79	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	14149935	25790898	1/9/2020 12:01	R3B42 unc rd ODBC                             	79	0
80	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	139049	186983	1/9/2020 11:59	R3D23 comm rd ODBC                            	80	0
81	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	2894202	14072492	1/9/2020 11:59	R3D23 unc rd ODBC                             	81	0
82	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	93158	289204	1/9/2020 11:44	R3D09 comm rd ODBC                            	82	0
83	sleeping                      	LoginName	HostName	  .	MYDB	AWAITING COMMAND	2288965	7939756	1/9/2020 11:44	R3D09 unc rd ODBC                             	83	0


What I have tried:

I am facing this problem first time.

also Windows task manager show a lot of process for.

disp + work.
Posted
Updated 8-Jan-20 23:58pm

1 solution

I would not try to mess with active processes, but instead configure MS SQL Server to limit its memory usage:
Server Memory Configuration Options[^]
I would not do it unless there are other services on the same server. I usually use a dedicated server for MS SQL, so consumed memory is not an issue.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900