How to investigate Lock:Timeout
I've done a lot of Googling, and I can't solve this issue.
With no other query running, and only an SQL profiler and my own copy of SQL Management Studio connected to my server I run the following:
delete_more:
DELETE TOP (200) A
FROM someTable
WHERE parentId = 15
IF @@ROWCOUNT > 0 GOTO delete_more;
Query courtesy of Microsoft
With this query running I observe Microsoft's SQL profiler. I see thousands of messages that say:
Lock:Timeout (51983df52b56)
Lock:Timeout (d89e387017b0)
Lock:Timeout (21d253e7f2bc)
Lock:Timeout (c1e67d683600)
Lock:Timeout (38aa16ffd30c)
Lock:Timeout (008f2176bf79)
The table has no triggers, and no foreign keys reference this table. The column in the where clause is indexed.
It only seems to be deleting 100 records every 2 - 3 seconds. I have about a million records to delete this way, it's going to take upwards of 8 hours.
My machine isn't slow, and the database has 8GB of RAM assigned to it.
I'm not sure if it's a clue, but when I run the profiler against the database when no apps or people are running queries against it I constantly see Lock:Acquired and Lock:Released (with no SQL, RPC, and SP statements starting). Is this typical?
I'm not a DBA, though I've been Googling. I've been playing with:
exec sp_lock
exec sp_who2
... but really the output doesn't mean much to me. I've been Googling to learn a bit about each, and other people's scripts to give more meaningful output, but currently my MSSQL knowledge isn't nearly deep enough to make most of it makes sense (I'm a computer programmer with no DBA on staff).
sql-server-2008
asked Nov 22 at 21:13
Developer Webs
16810
add a comment |
I've done a lot of Googling, and I can't solve this issue.
With no other query running, and only an SQL profiler and my own copy of SQL Management Studio connected to my server I run the following:
delete_more:
DELETE TOP (200) A
FROM someTable
WHERE parentId = 15
IF @@ROWCOUNT > 0 GOTO delete_more;
Query courtesy of Microsoft
With this query running I observe Microsoft's SQL profiler. I see thousands of messages that say:
Lock:Timeout (51983df52b56)
Lock:Timeout (d89e387017b0)
Lock:Timeout (21d253e7f2bc)
Lock:Timeout (c1e67d683600)
Lock:Timeout (38aa16ffd30c)
Lock:Timeout (008f2176bf79)
The table has no triggers, and no foreign keys reference this table. The column in the where clause is indexed.
It only seems to be deleting 100 records every 2 - 3 seconds. I have about a million records to delete this way, it's going to take upwards of 8 hours.
My machine isn't slow, and the database has 8GB of RAM assigned to it.
I'm not sure if it's a clue, but when I run the profiler against the database when no apps or people are running queries against it I constantly see Lock:Acquired and Lock:Released (with no SQL, RPC, and SP statements starting). Is this typical?
I'm not a DBA, though I've been Googling. I've been playing with:
exec sp_lock
exec sp_who2
... but really the output doesn't mean much to me. I've been Googling to learn a bit about each, and other people's scripts to give more meaningful output, but currently my MSSQL knowledge isn't nearly deep enough to make most of it makes sense (I'm a computer programmer with no DBA on staff).
sql-server-2008
asked Nov 22 at 21:13
Developer Webs
16810
When I runexec sp_who2in SQL Management studio the BlkBy column is always " ." and the status for my DELETE command is often "SUSPENDED" with a CPUTime of "1,027,405".
– Developer Webs
Nov 23 at 17:30
add a comment |
I've done a lot of Googling, and I can't solve this issue.
With no other query running, and only an SQL profiler and my own copy of SQL Management Studio connected to my server I run the following:
delete_more:
DELETE TOP (200) A
FROM someTable
WHERE parentId = 15
IF @@ROWCOUNT > 0 GOTO delete_more;
Query courtesy of Microsoft
With this query running I observe Microsoft's SQL profiler. I see thousands of messages that say:
Lock:Timeout (51983df52b56)
Lock:Timeout (d89e387017b0)
Lock:Timeout (21d253e7f2bc)
Lock:Timeout (c1e67d683600)
Lock:Timeout (38aa16ffd30c)
Lock:Timeout (008f2176bf79)
The table has no triggers, and no foreign keys reference this table. The column in the where clause is indexed.
It only seems to be deleting 100 records every 2 - 3 seconds. I have about a million records to delete this way, it's going to take upwards of 8 hours.
My machine isn't slow, and the database has 8GB of RAM assigned to it.
I'm not sure if it's a clue, but when I run the profiler against the database when no apps or people are running queries against it I constantly see Lock:Acquired and Lock:Released (with no SQL, RPC, and SP statements starting). Is this typical?
I'm not a DBA, though I've been Googling. I've been playing with:
exec sp_lock
exec sp_who2
... but really the output doesn't mean much to me. I've been Googling to learn a bit about each, and other people's scripts to give more meaningful output, but currently my MSSQL knowledge isn't nearly deep enough to make most of it makes sense (I'm a computer programmer with no DBA on staff).
sql-server-2008
asked Nov 22 at 21:13
Developer Webs
16810
I've done a lot of Googling, and I can't solve this issue.
With no other query running, and only an SQL profiler and my own copy of SQL Management Studio connected to my server I run the following:
delete_more:
DELETE TOP (200) A
FROM someTable
WHERE parentId = 15
IF @@ROWCOUNT > 0 GOTO delete_more;
Query courtesy of Microsoft
With this query running I observe Microsoft's SQL profiler. I see thousands of messages that say:
Lock:Timeout (51983df52b56)
Lock:Timeout (d89e387017b0)
Lock:Timeout (21d253e7f2bc)
Lock:Timeout (c1e67d683600)
Lock:Timeout (38aa16ffd30c)
Lock:Timeout (008f2176bf79)
The table has no triggers, and no foreign keys reference this table. The column in the where clause is indexed.
It only seems to be deleting 100 records every 2 - 3 seconds. I have about a million records to delete this way, it's going to take upwards of 8 hours.
My machine isn't slow, and the database has 8GB of RAM assigned to it.
I'm not sure if it's a clue, but when I run the profiler against the database when no apps or people are running queries against it I constantly see Lock:Acquired and Lock:Released (with no SQL, RPC, and SP statements starting). Is this typical?
I'm not a DBA, though I've been Googling. I've been playing with:
exec sp_lock
exec sp_who2
... but really the output doesn't mean much to me. I've been Googling to learn a bit about each, and other people's scripts to give more meaningful output, but currently my MSSQL knowledge isn't nearly deep enough to make most of it makes sense (I'm a computer programmer with no DBA on staff).
sql-server-2008
sql-server-2008
asked Nov 22 at 21:13
Developer Webs
16810
asked Nov 22 at 21:13
Developer Webs
16810
edited Nov 23 at 19:22
asked Nov 22 at 21:13
Developer Webs
16810
asked Nov 22 at 21:13
Developer Webs
16810
asked Nov 22 at 21:13
Developer Webs
16810
16810
When I runexec sp_who2in SQL Management studio the BlkBy column is always " ." and the status for my DELETE command is often "SUSPENDED" with a CPUTime of "1,027,405".
– Developer Webs
Nov 23 at 17:30
add a comment |
When I runexec sp_who2in SQL Management studio the BlkBy column is always " ." and the status for my DELETE command is often "SUSPENDED" with a CPUTime of "1,027,405".
– Developer Webs
Nov 23 at 17:30
When I run
exec sp_who2 in SQL Management studio the BlkBy column is always " ." and the status for my DELETE command is often "SUSPENDED" with a CPUTime of "1,027,405".– Developer Webs
Nov 23 at 17:30
When I run
exec sp_who2 in SQL Management studio the BlkBy column is always " ." and the status for my DELETE command is often "SUSPENDED" with a CPUTime of "1,027,405".– Developer Webs
Nov 23 at 17:30
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f53438041%2fhow-to-investigate-locktimeout%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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%2fstackoverflow.com%2fquestions%2f53438041%2fhow-to-investigate-locktimeout%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
When I run
exec sp_who2in SQL Management studio the BlkBy column is always " ." and the status for my DELETE command is often "SUSPENDED" with a CPUTime of "1,027,405".– Developer Webs
Nov 23 at 17:30