Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Mistery never ending query Expand / Collapse
Author
Message
Posted Tuesday, June 4, 2013 9:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 19, 2015 9:42 AM
Points: 174, Visits: 632
what version of 2012?
Post #1459817
Posted Tuesday, June 4, 2013 9:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:36 PM
Points: 13,694, Visits: 35,027
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1459827
Posted Tuesday, June 4, 2013 9:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 19, 2015 9:42 AM
Points: 174, Visits: 632
not an stats issue
Post #1459833
Posted Tuesday, June 4, 2013 9:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:06 AM
Points: 7,447, Visits: 15,699
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
Post #1459835
Posted Tuesday, June 4, 2013 9:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 19, 2015 9:42 AM
Points: 174, Visits: 632
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
Post #1459839
Posted Tuesday, June 4, 2013 9:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:36 PM
Points: 13,694, Visits: 35,027
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1459847
Posted Tuesday, June 4, 2013 10:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:05 PM
Points: 42,728, Visits: 39,631
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
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

Post #1459853
Posted Thursday, August 6, 2015 1:25 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 11:56 PM
Points: 3, Visits: 12
I've just suffered the same issue on RTM.
Brand new table, definitely not statistics problem.

Moving data to 11.0.3000 helped.
Post #1709009
Posted Monday, August 10, 2015 3:09 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:44 AM
Points: 2,571, Visits: 6,398
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;




You are standing in an open field west of a white house, with a boarded front door.... Opening the small mailbox reveals a leaflet.

> read leaflet

"ZORK is a game of adventure, danger, and low cunning. In it you will explore some of the most amazing territory ever seen by mortals." http://www.web-adventures.org/cgi-bin/webfrotz?s=Zork1
Post #1710174
Posted Tuesday, August 11, 2015 8:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:43 PM
Points: 38,313, Visits: 35,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1710540
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse