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?
For 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