Mistery never ending query

Question

Mistery never ending query

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

ricardo_chicas (6/3/2013)
if I remove field 2 from the equation it will work just fine
Any ideas?

An actual plan from the whole original query with "field 2" commented out as you describe will help immensely.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 2

I just solved the issue by adding a new index ( not necessary since I was using the clustered one)

the query started working, then I removed the new index and the issue is gone

The table was new and not corrupted, so this was indeed some kind of bug

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

ricardo_chicas (6/4/2013)
I just solved the issue by adding a new index ( not necessary since I was using the clustered one)
the query started working, then I removed the new index and the issue is gone
The table was new and not corrupted, so this was indeed some kind of bug

It's more likely to be stale statistics than a bug. Can you post the actual plan now please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 4

I am 100% sure it is a bug, you can even replicate it on your side if you also have a 2012 RTM server

just create a table like this

CREATE TABLE [dbo].[temptable2](

[Field1] [varchar](23) NOT NULL,

[field2] [varchar](20) NULL,

[field3] [varchar](50) NULL,

[field4] [datetime] NULL,

[field5] [varchar](255) NULL

) ON [PRIMARY]

Create clustered index idx1 on temptable2(field1)

load it with at least 100k rows

then do this:

select field3,count(1)

from temptable2

group by field3

it will never ever ends, now that is only happening on 2012, I just test it in a 2008 R2 sp2 server and it worked fine

Lowell SSC Guru Points: 323518 More actions · Answer 5

nope, not a valid test/way to reproduce the issue.

here's your exact code, but loaded with random data.

on my machine, with all four versions of SQL Server I have installed (2005/2008/2008R2/2012), it returned results in about 9 seconds.

CREATE TABLE [dbo].[temptable2](

[Field1] [varchar](23) NOT NULL,

[field2] [varchar](20) NULL,

[field3] [varchar](50) NULL,

[field4] [datetime] NULL,

[field5] [varchar](255) NULL

) ON [PRIMARY]

Create clustered index idx1 on temptable2(field1)

INSERT INTO temptable2

SELECT TOP 200000

LEFT(CONVERT(varchar(50),NEWID()),23), --23 chars

LEFT(CONVERT(varchar(50),NEWID()),20),--20 chars

LEFT(CONVERT(varchar(50),NEWID()),50),--50 chars

CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME), --datetime

LEFT(CONVERT(varchar(255),NEWID()),255) --255 chars

FROM sys.columns T1 cross apply sys.columns T2

select field3,count(1)

from temptable2

group by field3

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!

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 6

ricardo_chicas

SSCertifiable

Points: 5024

June 4, 2013 at 9:06 am

#1621010

what version of 2012?

Lowell SSC Guru Points: 323518 More actions · Answer 7

ricardo_chicas (6/4/2013)
what version of 2012?

again, it's not the version, it's most likely stale statistics, that we mentioned a few times; there's nothing in your example that is not done in millions of other transactions, that makes it a unique/edge case where it's a real "bug"

remember automatic update of statistical(if enabled) requires 20% of the rows plus 500 rows of the table to be modified before the stats would update;

on a big table, a much, much lesser of rows can be changed, and adversely affect queries because the stats are no longer accurately reflecting the unique distributions of values within the table. THAT is a known, common issue, and something you should be aware of as a DBA or Developer.

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

Feb 10 2012 19:39:15

Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows NT 6.1 <X64>

(Build 7601: Service Pack 1)

11.0.2100.60

RTM

Developer Edition (64-bit)

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!

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 8

ricardo_chicas

SSCertifiable

Points: 5024

June 4, 2013 at 9:33 am

#1621023

not an stats issue

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

ricardo_chicas (6/4/2013)
not an stats issue

There's no evidence to support anything else. Lowell's explanation seems the most likely.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 10

explain stale stats in a brand new table, with a brand new clustered index with a run of update stats a few minutes ago, again not a stats issue

Lowell SSC Guru Points: 323518 More actions · Answer 11

ricardo_chicas (6/4/2013)
explain stale stats in a brand new table, with a brand new clustered index with a run of update stats a few minutes ago, again not a stats issue

take a look at this article to help you understand the issue:

http://www.sqlservercentral.com/blogs/briankmcdonald/2010/11/05/how-stale-are-my-statistics_3F00_/

in your specific example,

when we did an insert on a brand new table as shown in my example above: 200K rows;

on creation, the stats are accurate.

you can see it with this:

DBCC SHOW_STATISTICS (temptable2,'field3' )

if you look, the distribution is very unique because of my randomization code)

now lets update 10% of the table(which i don't remember you mentioning you did) to the exact same value, and also run the same statistics query

SET ROWCOUNT 10000

UPDATE temptable2 SET field3 = 'bananas'

SET ROWCOUNT 0

DBCC SHOW_STATISTICS (temptable2,'field3' )

now, the distribution didn't change! but we KNOW there's one value in there that is certainly no even close to being unique any more...THAT's the stale statistics that will could potentially throw off a query; during testing, that didn't happen, bu the underlying issue is this cause.

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Did you check for blocking? Did you check what wait type the query is getting? Did you check for any messages in the error log?

It probably isn't a stats issue if the query plan can't be generated, but that doesn't mean it's a bug unless you've rules out every single other possibility (and there are lots)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alimka3 Valued Member Points: 73 More actions · Answer 13

I've just suffered the same issue on RTM.

Brand new table, definitely not statistics problem.

Moving data to 11.0.3000 helped.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 14

This can also happen if your query's execution plan is creating extremely large hash tables or sort operations in the background and spooling out to tempdb.

Use the following DMV query to see what sessions have space allocated in TEMPDB.

select * from sys.dm_db_task_space_usage

where (internal_objects_alloc_page_count + user_objects_alloc_page_count) > 0

order by (internal_objects_alloc_page_count + user_objects_alloc_page_count) desc;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 15

ricardo_chicas (6/3/2013)
...and sql server 2012 RTM in it

2012 had some serious problems. If you're not running at least SP2 CU6, then you're wasting your time trying to tweek any query. Do the upgrade first and do it now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)