Mistery never ending query
-
Hello all
I have a big machine with 192gb, 16 cpus, and sql server 2012 RTM in it
I am trying to run this query
Select top 1
t.field1,
isnull( t.field2,'nothing'),
a.field3,
count(1) as total--,
FROM temptable2 t WITH(NOLOCK)
inner join temptable a WITH(NOLOCK) on t.field1 = a.field1
GROUP BY t.field1,a.field3,isnull( t.field2,'nothing')
NOthing fancy about it, temptable has 300k rows, temptable2 has 110k rows
the thing is that it never ends, none is blocking the process, the join is done using the clustered index
the fields are like this
field1 varchar(23, not null)
field2 datetime
field3 varchar(255)
I even recrete the tables in a different database with the same result, if I remove field 2 from the equation it will work just fine
Any ideas?
-
If field2 is a datetime, trying to return 'nothing' if it is null will be giving the database engine a headache in terms of mismatching data types.
Instead, maybe try returning '1900-01-01'.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
ricardo_chicas (6/3/2013)
Hello allI have a big machine with 192gb, 16 cpus, and sql server 2012 RTM in it
I am trying to run this query
Select top 1
t.field1,
isnull( t.field2,'nothing'),
a.field3,
count(1) as total--,
FROM temptable2 t WITH(NOLOCK)
inner join temptable a WITH(NOLOCK) on t.field1 = a.field1
GROUP BY t.field1,a.field3,isnull( t.field2,'nothing')
NOthing fancy about it, temptable has 300k rows, temptable2 has 110k rows
the thing is that it never ends, none is blocking the process, the join is done using the clustered index
the fields are like this
field1 varchar(23, not null)
field2 datetime
field3 varchar(255)
I even recrete the tables in a different database with the same result, if I remove field 2 from the equation it will work just fine
Any ideas?
It's a completely pointless query. TOP 1 by what? Car colour? Why do all the aggregation work then return a single random aggregated row? Nevertheless, folks will be curious. Can you post the estimated plan as a .sqlplan attachment?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Besides NOLOCK being a bad idea in general, as it can lead to bad thinks like missing data, duplicate data and more, the NOLOCKS on a temp table are pointless.
if you created the #temptable yourself, and noone else can use it(since it's session specific to this set of queries) , why would you need nolock? a bad habit you haven't broken yet?
some of my peers can point you to the link that has a great explanation on the hazards of nolock,
i think the actual query, instead of pseudo code might get you some better answers; the actual execution plan would show us what is going wrong int he query itself, if you can post that as an attached .sqlplan file.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here is a small piece of code to back up my assertion that this is a datatype problem:
if object_id('tempdb..#ex', 'U') is not nulldrop table #ex
create table #ex
(
someDate datetime null
)
insert #ex
(someDate)
select '20130101'
select isnull(someDate, 'nothing')
from #ex
group by isnull(someDate, 'nothing')
Msg 241, Level 16, State 1, Line 13
Conversion failed when converting date and/or time from character string.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
All
Thanks but you are missing the point. The original query was something much bigger
I used a top 1 just to show you that there is something wrong with one of the tables
Even a select field2 from temptable
Never ends, it is a really small table,
The engine just can't resolve it, surely a bug or something
I am just asking what can it be?
-
ricardo_chicas (6/4/2013)
AllThanks but you are missing the point. The original query was something much bigger
I used a top 1 just to show you that there is something wrong with one of the tables
Even a select field2 from temptable
Never ends, it is a really small table,
The engine just can't resolve it, surely a bug or something
I am just asking what can it be?
Post the estimated plan for the original query, it's the first (and best) point of call for this type of problem. Without it, folks can only guess.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
if you have a specific performance problem, we really would want to see at least the estimated execution plan, and the actual query.
when you abstract it out, you end up cutting out critical details;
All of us are volunteers, but with a lot of experience and willingness to help.
help us help you!
provide better details, datatypes, etc.
post a .sqlplan for the query, that would show us a lot, for example we could see if out of date statistics are really affecting the query.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
All
I can't even generate the exec plan. It never ends, and none is using that table since it is a copy of the original
And none is blocking that spod either
This is only happenning to me with that specific query
-
ricardo_chicas (6/4/2013)
AllI can't even generate the exec plan. It never ends, and none is using that table since it is a copy of the original
And none is blocking that spod either
This is only happenning to me with that specific query
Not even an estimated plan?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
show the real query, and you can generate an estimated execution plan without running the query at all. just highlight the query and click the button for estimated execution plan.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I transformed the data so what I showed is yhe actual query
And the estimated plan never is calculated. It just keeps running for hours until I stop it
-
ChrisM@Work (6/4/2013)
ricardo_chicas (6/4/2013)
AllI can't even generate the exec plan. It never ends, and none is using that table since it is a copy of the original
And none is blocking that spod either
This is only happenning to me with that specific query
Not even an estimated plan?
Yes. Not even the estimate, and only happenning on that table.
-
wow that's unusual, can you check for corruption? that might explain everything:
dbcc checktable ('YourTableName') WITH ALL_ERRORMSGS
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
While this query is running in one SSMS tab, switch to another tab and run sp_who2. Is the spid running your stuck query blocked?
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply