SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mistery never ending query


Mistery never ending query

Author
Message
ricardo_chicas
ricardo_chicas
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 Visits: 694
what version of 2012?
Lowell
Lowell
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68428 Visits: 40898
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
ricardo_chicas
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 Visits: 694
not an stats issue
ChrisM@Work
ChrisM@Work
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39715 Visits: 19995
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
Exploring Recursive CTEs by Example Dwain Camps
ricardo_chicas
ricardo_chicas
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 Visits: 694
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
Lowell
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68428 Visits: 40898
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!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214246 Visits: 46266
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
alimka3
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 37
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
Eric M Russell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27994 Visits: 11481
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;




"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206027 Visits: 41952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search