SQL session that is 'never end', always stay in a running status but does not return a result set (Until I...
1
I am a beginner SQL DBA and I experienced a weird thing in SQL server.
Symptom:
- Session is in
Runningstate (not runnable, not suspended). - CPU time is zero, or if it is not, after a while it stops growing.
- ReadsWritesLogical Reads are zero, or if is not, after a while they stop growing as well.
- No result set that is returned.
- They will run until I kill all these sessions. (These can be days, but these queries run within 3 mins usually)
More info:
- This is static database.
- Indexes are not fragmented.
- Statistics are updated
- CPU is not under stress, used 10% only
- Memory is more than enough
- SQL Server 2016 is used
Is there any idea what can be the reason for this behaviour?
I'm very grateful for all the answers.
sql-server sql-server-2016
edited Dec 7 '17 at 10:55
hot2use
8,35752055
asked Dec 7 '17 at 10:35
MonaMona
61
bumped to the homepage by Community♦ 15 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
1
I am a beginner SQL DBA and I experienced a weird thing in SQL server.
Symptom:
- Session is in
Runningstate (not runnable, not suspended). - CPU time is zero, or if it is not, after a while it stops growing.
- ReadsWritesLogical Reads are zero, or if is not, after a while they stop growing as well.
- No result set that is returned.
- They will run until I kill all these sessions. (These can be days, but these queries run within 3 mins usually)
More info:
- This is static database.
- Indexes are not fragmented.
- Statistics are updated
- CPU is not under stress, used 10% only
- Memory is more than enough
- SQL Server 2016 is used
Is there any idea what can be the reason for this behaviour?
I'm very grateful for all the answers.
sql-server sql-server-2016
edited Dec 7 '17 at 10:55
hot2use
8,35752055
asked Dec 7 '17 at 10:35
MonaMona
61
bumped to the homepage by Community♦ 15 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
My bet is with poison waits.
– Erik Darling
Dec 7 '17 at 10:58
add a comment |
1
1
1
I am a beginner SQL DBA and I experienced a weird thing in SQL server.
Symptom:
- Session is in
Runningstate (not runnable, not suspended). - CPU time is zero, or if it is not, after a while it stops growing.
- ReadsWritesLogical Reads are zero, or if is not, after a while they stop growing as well.
- No result set that is returned.
- They will run until I kill all these sessions. (These can be days, but these queries run within 3 mins usually)
More info:
- This is static database.
- Indexes are not fragmented.
- Statistics are updated
- CPU is not under stress, used 10% only
- Memory is more than enough
- SQL Server 2016 is used
Is there any idea what can be the reason for this behaviour?
I'm very grateful for all the answers.
sql-server sql-server-2016
edited Dec 7 '17 at 10:55
hot2use
8,35752055
asked Dec 7 '17 at 10:35
MonaMona
61
I am a beginner SQL DBA and I experienced a weird thing in SQL server.
Symptom:
- Session is in
Runningstate (not runnable, not suspended). - CPU time is zero, or if it is not, after a while it stops growing.
- ReadsWritesLogical Reads are zero, or if is not, after a while they stop growing as well.
- No result set that is returned.
- They will run until I kill all these sessions. (These can be days, but these queries run within 3 mins usually)
More info:
- This is static database.
- Indexes are not fragmented.
- Statistics are updated
- CPU is not under stress, used 10% only
- Memory is more than enough
- SQL Server 2016 is used
Is there any idea what can be the reason for this behaviour?
I'm very grateful for all the answers.
sql-server sql-server-2016
sql-server sql-server-2016
edited Dec 7 '17 at 10:55
hot2use
8,35752055
asked Dec 7 '17 at 10:35
MonaMona
61
edited Dec 7 '17 at 10:55
hot2use
8,35752055
asked Dec 7 '17 at 10:35
MonaMona
61
edited Dec 7 '17 at 10:55
hot2use
8,35752055
edited Dec 7 '17 at 10:55
hot2use
8,35752055
edited Dec 7 '17 at 10:55
hot2use
8,35752055
8,35752055
asked Dec 7 '17 at 10:35
MonaMona
61
asked Dec 7 '17 at 10:35
MonaMona
61
asked Dec 7 '17 at 10:35
MonaMona
61
61
bumped to the homepage by Community♦ 15 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 15 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
My bet is with poison waits.
– Erik Darling
Dec 7 '17 at 10:58
add a comment |
My bet is with poison waits.
– Erik Darling
Dec 7 '17 at 10:58
My bet is with poison waits.
– Erik Darling
Dec 7 '17 at 10:58
My bet is with poison waits.
– Erik Darling
Dec 7 '17 at 10:58
add a comment |
1 Answer
1
active
oldest
votes
0
I would check the following
- is the session you normally kill, waiting on something
- is the session you normally kill, being blocked
- is the session you normally kill, in a transaction that someone forgot to end?
you can use the below query to check the first 2. You may be able to check the third if the query gets picked up in the last column.
SELECT [spid]
-- ,[kpid]
,[blocked]
,[open_tran]
-- ,[waittype]
,[waittime]
,[lastwaittype]
,[waitresource]
,[name]
,CAST([cpu] AS NUMERIC) cpu
,[physical_io]
,[loginame]
,[hostname]
,[program_name]
-- ,[hostprocess]
,[cmd]
-- ,[nt_domain]
-- ,[nt_username]
-- ,[net_address]
,[net_library]
-- ,[uid]
,[memusage]
,[login_time]
,[last_batch]
-- ,[ecid]
,[status]
-- ,[sid]
-- ,[context_info]
,[sql_handle]
-- ,[stmt_start]
-- ,[stmt_end]
-- ,[request_id]
,[text]
FROM [master].[sys].[sysprocesses] p
JOIN SYS.databases d
ON p.dbid = d.database_id
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
--WHERE status = 'runnable'
--WHERE name = 'runnable'
--ORDER BY blocked DESC
--ORDER BY cpu DESC
ORDER BY waittime DESC
edited Dec 8 '17 at 2:55
Michael Green
14.7k83060
answered Dec 7 '17 at 14:17
Chris LumnahChris Lumnah
51424
Have you heard of sp_WhoIsActive?
– Erik Darling
Dec 7 '17 at 18:35
Thank for all the answers. I tried lots of queries to figure out what the problem is. All of them havehad runnable status (I checked all of suspicious sessions in sys.dm_exec_sessions but they were running, because there is no runnable status in it, only running). But I experienced that this happens only when I use sqlcmd to run a query. If I use a simple declaration there is no problem. Why is this? Maybe different driver? Thx,
– Mona
Dec 12 '17 at 10:31
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f192611%2fsql-session-that-is-never-end-always-stay-in-a-running-status-but-does-not-re%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
I would check the following
- is the session you normally kill, waiting on something
- is the session you normally kill, being blocked
- is the session you normally kill, in a transaction that someone forgot to end?
you can use the below query to check the first 2. You may be able to check the third if the query gets picked up in the last column.
SELECT [spid]
-- ,[kpid]
,[blocked]
,[open_tran]
-- ,[waittype]
,[waittime]
,[lastwaittype]
,[waitresource]
,[name]
,CAST([cpu] AS NUMERIC) cpu
,[physical_io]
,[loginame]
,[hostname]
,[program_name]
-- ,[hostprocess]
,[cmd]
-- ,[nt_domain]
-- ,[nt_username]
-- ,[net_address]
,[net_library]
-- ,[uid]
,[memusage]
,[login_time]
,[last_batch]
-- ,[ecid]
,[status]
-- ,[sid]
-- ,[context_info]
,[sql_handle]
-- ,[stmt_start]
-- ,[stmt_end]
-- ,[request_id]
,[text]
FROM [master].[sys].[sysprocesses] p
JOIN SYS.databases d
ON p.dbid = d.database_id
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
--WHERE status = 'runnable'
--WHERE name = 'runnable'
--ORDER BY blocked DESC
--ORDER BY cpu DESC
ORDER BY waittime DESC
edited Dec 8 '17 at 2:55
Michael Green
14.7k83060
answered Dec 7 '17 at 14:17
Chris LumnahChris Lumnah
51424
Have you heard of sp_WhoIsActive?
– Erik Darling
Dec 7 '17 at 18:35
Thank for all the answers. I tried lots of queries to figure out what the problem is. All of them havehad runnable status (I checked all of suspicious sessions in sys.dm_exec_sessions but they were running, because there is no runnable status in it, only running). But I experienced that this happens only when I use sqlcmd to run a query. If I use a simple declaration there is no problem. Why is this? Maybe different driver? Thx,
– Mona
Dec 12 '17 at 10:31
add a comment |
0
I would check the following
- is the session you normally kill, waiting on something
- is the session you normally kill, being blocked
- is the session you normally kill, in a transaction that someone forgot to end?
you can use the below query to check the first 2. You may be able to check the third if the query gets picked up in the last column.
SELECT [spid]
-- ,[kpid]
,[blocked]
,[open_tran]
-- ,[waittype]
,[waittime]
,[lastwaittype]
,[waitresource]
,[name]
,CAST([cpu] AS NUMERIC) cpu
,[physical_io]
,[loginame]
,[hostname]
,[program_name]
-- ,[hostprocess]
,[cmd]
-- ,[nt_domain]
-- ,[nt_username]
-- ,[net_address]
,[net_library]
-- ,[uid]
,[memusage]
,[login_time]
,[last_batch]
-- ,[ecid]
,[status]
-- ,[sid]
-- ,[context_info]
,[sql_handle]
-- ,[stmt_start]
-- ,[stmt_end]
-- ,[request_id]
,[text]
FROM [master].[sys].[sysprocesses] p
JOIN SYS.databases d
ON p.dbid = d.database_id
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
--WHERE status = 'runnable'
--WHERE name = 'runnable'
--ORDER BY blocked DESC
--ORDER BY cpu DESC
ORDER BY waittime DESC
edited Dec 8 '17 at 2:55
Michael Green
14.7k83060
answered Dec 7 '17 at 14:17
Chris LumnahChris Lumnah
51424
Have you heard of sp_WhoIsActive?
– Erik Darling
Dec 7 '17 at 18:35
Thank for all the answers. I tried lots of queries to figure out what the problem is. All of them havehad runnable status (I checked all of suspicious sessions in sys.dm_exec_sessions but they were running, because there is no runnable status in it, only running). But I experienced that this happens only when I use sqlcmd to run a query. If I use a simple declaration there is no problem. Why is this? Maybe different driver? Thx,
– Mona
Dec 12 '17 at 10:31
add a comment |
0
0
0
I would check the following
- is the session you normally kill, waiting on something
- is the session you normally kill, being blocked
- is the session you normally kill, in a transaction that someone forgot to end?
you can use the below query to check the first 2. You may be able to check the third if the query gets picked up in the last column.
SELECT [spid]
-- ,[kpid]
,[blocked]
,[open_tran]
-- ,[waittype]
,[waittime]
,[lastwaittype]
,[waitresource]
,[name]
,CAST([cpu] AS NUMERIC) cpu
,[physical_io]
,[loginame]
,[hostname]
,[program_name]
-- ,[hostprocess]
,[cmd]
-- ,[nt_domain]
-- ,[nt_username]
-- ,[net_address]
,[net_library]
-- ,[uid]
,[memusage]
,[login_time]
,[last_batch]
-- ,[ecid]
,[status]
-- ,[sid]
-- ,[context_info]
,[sql_handle]
-- ,[stmt_start]
-- ,[stmt_end]
-- ,[request_id]
,[text]
FROM [master].[sys].[sysprocesses] p
JOIN SYS.databases d
ON p.dbid = d.database_id
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
--WHERE status = 'runnable'
--WHERE name = 'runnable'
--ORDER BY blocked DESC
--ORDER BY cpu DESC
ORDER BY waittime DESC
edited Dec 8 '17 at 2:55
Michael Green
14.7k83060
answered Dec 7 '17 at 14:17
Chris LumnahChris Lumnah
51424
I would check the following
- is the session you normally kill, waiting on something
- is the session you normally kill, being blocked
- is the session you normally kill, in a transaction that someone forgot to end?
you can use the below query to check the first 2. You may be able to check the third if the query gets picked up in the last column.
SELECT [spid]
-- ,[kpid]
,[blocked]
,[open_tran]
-- ,[waittype]
,[waittime]
,[lastwaittype]
,[waitresource]
,[name]
,CAST([cpu] AS NUMERIC) cpu
,[physical_io]
,[loginame]
,[hostname]
,[program_name]
-- ,[hostprocess]
,[cmd]
-- ,[nt_domain]
-- ,[nt_username]
-- ,[net_address]
,[net_library]
-- ,[uid]
,[memusage]
,[login_time]
,[last_batch]
-- ,[ecid]
,[status]
-- ,[sid]
-- ,[context_info]
,[sql_handle]
-- ,[stmt_start]
-- ,[stmt_end]
-- ,[request_id]
,[text]
FROM [master].[sys].[sysprocesses] p
JOIN SYS.databases d
ON p.dbid = d.database_id
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
--WHERE status = 'runnable'
--WHERE name = 'runnable'
--ORDER BY blocked DESC
--ORDER BY cpu DESC
ORDER BY waittime DESC
edited Dec 8 '17 at 2:55
Michael Green
14.7k83060
answered Dec 7 '17 at 14:17
Chris LumnahChris Lumnah
51424
edited Dec 8 '17 at 2:55
Michael Green
14.7k83060
edited Dec 8 '17 at 2:55
Michael Green
14.7k83060
edited Dec 8 '17 at 2:55
Michael Green
14.7k83060
14.7k83060
answered Dec 7 '17 at 14:17
Chris LumnahChris Lumnah
51424
answered Dec 7 '17 at 14:17
Chris LumnahChris Lumnah
51424
answered Dec 7 '17 at 14:17
Chris LumnahChris Lumnah
51424
51424
Have you heard of sp_WhoIsActive?
– Erik Darling
Dec 7 '17 at 18:35
Thank for all the answers. I tried lots of queries to figure out what the problem is. All of them havehad runnable status (I checked all of suspicious sessions in sys.dm_exec_sessions but they were running, because there is no runnable status in it, only running). But I experienced that this happens only when I use sqlcmd to run a query. If I use a simple declaration there is no problem. Why is this? Maybe different driver? Thx,
– Mona
Dec 12 '17 at 10:31
add a comment |
Have you heard of sp_WhoIsActive?
– Erik Darling
Dec 7 '17 at 18:35
Thank for all the answers. I tried lots of queries to figure out what the problem is. All of them havehad runnable status (I checked all of suspicious sessions in sys.dm_exec_sessions but they were running, because there is no runnable status in it, only running). But I experienced that this happens only when I use sqlcmd to run a query. If I use a simple declaration there is no problem. Why is this? Maybe different driver? Thx,
– Mona
Dec 12 '17 at 10:31
Have you heard of sp_WhoIsActive?
– Erik Darling
Dec 7 '17 at 18:35
Have you heard of sp_WhoIsActive?
– Erik Darling
Dec 7 '17 at 18:35
Thank for all the answers. I tried lots of queries to figure out what the problem is. All of them havehad runnable status (I checked all of suspicious sessions in sys.dm_exec_sessions but they were running, because there is no runnable status in it, only running). But I experienced that this happens only when I use sqlcmd to run a query. If I use a simple declaration there is no problem. Why is this? Maybe different driver? Thx,
– Mona
Dec 12 '17 at 10:31
Thank for all the answers. I tried lots of queries to figure out what the problem is. All of them havehad runnable status (I checked all of suspicious sessions in sys.dm_exec_sessions but they were running, because there is no runnable status in it, only running). But I experienced that this happens only when I use sqlcmd to run a query. If I use a simple declaration there is no problem. Why is this? Maybe different driver? Thx,
– Mona
Dec 12 '17 at 10:31
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f192611%2fsql-session-that-is-never-end-always-stay-in-a-running-status-but-does-not-re%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
My bet is with poison waits.
– Erik Darling
Dec 7 '17 at 10:58