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 04, 2013 9:06 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 4:29 AM
Points: 129, Visits: 510
what version of 2012?
Post #1459817
Posted Tuesday, June 04, 2013 9:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:53 AM
Points: 12,749, Visits: 31,108
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 04, 2013 9:32 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 4:29 AM
Points: 129, Visits: 510
not an stats issue
Post #1459833
Posted Tuesday, June 04, 2013 9:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:34 AM
Points: 6,770, Visits: 12,871
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 04, 2013 9:39 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 4:29 AM
Points: 129, Visits: 510
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 04, 2013 9:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:53 AM
Points: 12,749, Visits: 31,108
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 04, 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: Today @ 3:42 AM
Points: 41,547, Visits: 34,469
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 2008, 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
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse