November 17, 2014 at 10:17 pm
Comments posted to this topic are about the item Trace Flags
Thanks.
November 18, 2014 at 12:55 am
Interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2014 at 1:04 am
Sql2005 never installed!
November 18, 2014 at 7:07 am
Thanks for the question, I learned something today.
November 18, 2014 at 7:33 am
Still trying figure out what the point of this feature is suppose to help with.
After some reading it appears to be a quick short cut to update stats for new data in low volume environment.
If so, what is the point? It is a low volume environment.
A high volume environment will trigger the native optimizer, making this feature useless.
November 18, 2014 at 7:51 am
Nice question, Sourav, thanks.
November 18, 2014 at 7:54 am
Sadly the question posed can lead to confusion and a low correct answer rate due to the availability of four correct answers.
The correct answers listed are only for trace flag 2389 and neglect trace flag 2390. When 2390 is set, you get the same stats update behavior but you do not have the same pre-requisites as trace flag 2389. With 2390, that stats will be updated even if the ascending nature of the column is unknown (so it can also be descending). And 2390 only requires that the column is a leading column in an index.
And since the question is framed as follows
Trace flags 2389 and 2390 were added in SQL Server 2005 SP1. The statistics will be updated automatically at query compile time if the following conditions are true:
(Choose 3 correct answers.)
It makes it quite difficult to derive a correct answer when you have these as options
trace flag 2389 is set
trace flag 2390 is set
the column is branded ascending
the column is branded descending
a covering index exists with the ascending column as the leading key
the column is a leading column in an index
Based on the requirements for the two trace flags, one could easily see why this question becomes difficult to answer correctly without resorting to a guessing game. Both trace flags have different requirements in order to cause an update to the statistics. Both should be used together if using them. So why not include answers that support the requirements for both?
A simple modification to the question would change the validity of the currently "correct" answers though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 18, 2014 at 9:13 am
+1 on not knowing which way to guess on this one. I almost chose 4 answers until I saw it said to pick 3. Had flag 2390 not been mentioned then this would have been a little more clear to me. Oh well. good information to know anyway.
November 18, 2014 at 10:08 am
SQLRNNR (11/18/2014)
Sadly the question posed can lead to confusion and a low correct answer rate due to the availability of four correct answers.The correct answers listed are only for trace flag 2389 and neglect trace flag 2390. When 2390 is set, you get the same stats update behavior but you do not have the same pre-requisites as trace flag 2389. With 2390, that stats will be updated even if the ascending nature of the column is unknown (so it can also be descending). And 2390 only requires that the column is a leading column in an index.
...
A simple modification to the question would change the validity of the currently "correct" answers though.
+1 -- went less specific 2390 requirement for third choice
November 18, 2014 at 11:28 am
+1 to this reply but I would like give props to the question author
November 18, 2014 at 1:53 pm
doug.davidson (11/18/2014)
Still trying figure out what the point of this feature is suppose to help with.
The "ascending key problem" is a well-known problem related to stale statistics on dynamic data.
Consider the population registry at the municipal office. Freshly updated statistics on the FirstName column show that the number of people named John or Mary is relatively high, and the number of people named Adolf or Irinea is much lower. After adding data for three weeks, before the threshold for statistics update is hit, the statistics will be out of date, but that will hardly cause big problems - names that were common three weeks ago are still common now. A query for all Mary's will probably still return way more people than a query for all Adolfs, so they may get different plans - optimal for each value.
But now consider the statistics on the BirthDate column. In the past three weeks, ten thousand people have been added. Four thousand have moved in the city, six thousand are newly born. If you now do a query for all children born in the last 20 days, the statistics will show zero matches - the most recent birthdate in the statistics is three weeks ago. So SQL Server will assume one row (the minimum for estimation) and build a plan optimized for that. That query will probably run excessively long - perhaps because of a loop join that processes 6,000 rows, or because insufficient memory is reserved for a sort or hash operation, causing a spill to tempdb.
This problem is frequently encountered when dealing with indexes on fully ascending (identity, insert date) or mostly ascending (registration date, birthdate) columns. Trace flags 2389 and 2390 are designed to help fix this issue. And the new cardinality estimator in SQL Server 2014 attempts to fix it in another way.
PS: In the above discussion, the term "ascending" is a generalisation for any sequence of data that is added to at one of the extreme ends. In other words, a column with descending data is also considered an "ascending key". It can cause the same problems, and it is subject to the same behaviour changes for TF 2389 and TF 2390, or for the new cardinality estimator.
November 18, 2014 at 2:10 pm
SQLRNNR (11/18/2014)
Sadly the question posed can lead to confusion and a low correct answer rate due to the availability of four correct answers.The correct answers listed are only for trace flag 2389 and neglect trace flag 2390. When 2390 is set, you get the same stats update behavior but you do not have the same pre-requisites as trace flag 2389. With 2390, that stats will be updated even if the ascending nature of the column is unknown (so it can also be descending). And 2390 only requires that the column is a leading column in an index.
And since the question is framed as follows
Trace flags 2389 and 2390 were added in SQL Server 2005 SP1. The statistics will be updated automatically at query compile time if the following conditions are true:
(Choose 3 correct answers.)
It makes it quite difficult to derive a correct answer when you have these as options
trace flag 2389 is set
trace flag 2390 is set
the column is branded ascending
the column is branded descending
a covering index exists with the ascending column as the leading key
the column is a leading column in an index
Based on the requirements for the two trace flags, one could easily see why this question becomes difficult to answer correctly without resorting to a guessing game. Both trace flags have different requirements in order to cause an update to the statistics. Both should be used together if using them. So why not include answers that support the requirements for both?
A simple modification to the question would change the validity of the currently "correct" answers though.
You saved me a lot of composition and typing with this, Jason. Thanks.
Tom
November 19, 2014 at 4:57 am
doug.davidson (11/18/2014)
Still trying figure out what the point of this feature is suppose to help with.After some reading it appears to be a quick short cut to update stats for new data in low volume environment.
If so, what is the point? It is a low volume environment.
A high volume environment will trigger the native optimizer, making this feature useless.
Good point 🙂
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
November 19, 2014 at 7:47 am
Thanks for the info. It's all a bit confusing to me. I'll do some research. Thanks again.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply