Click here to monitor SSC
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
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 676
what version of 2012?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14971 Visits: 38989
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
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 676
not an stats issue
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9007 Visits: 19028
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
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 676
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14971 Visits: 38989
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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47299 Visits: 44392
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
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4625 Visits: 9577
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45205 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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