Keeping my cardinality

Question

Post reply

Keeping my cardinality

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

Nice question -

Hope this helps...

Ford Fairlane
Rock and Roll Detective

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

paul s-306273 (4/29/2014)
Looks like I'm going to learn something everyday this week.
Thanks for the 2014 questions Steve.

+1

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

dawryn SSCertifiable Points: 5399 More actions · Answer 3

PHYData DBA (4/29/2014)
You are absolutely right about that this was not an answer that could be selected. Not sure it would be right. The question never states if the database existed prior to the upgrade. It could have been there before, or created after. 😎

Reworded question gives a hint like it was existing database:

a specific database where I am not changing any code

I such case if you wanted to use new CE for the entire database then changing compatibility level to 120 comes right. For old CE nothing is needed.

TomThomson SSC Guru Points: 104773 More actions · Answer 4

dawryn (4/29/2014)
:blink: :blink: Is this question about previously existing database or a newly created one? :unsure: According to linked document answers are only for the latter :Whistling:

He has a point there. The question says you've upgraded your server, and it's a database which already exists. The upgrade process will leave this dabase's compatability level at 110 specifically in order to avoid any surprises from the new cardinality estimator, and you are advised (on the page referenced as well as in SQL Server 2014’s new cardinality estimator (Part 1)) to test thoroughly before using the new cardinality estimator for an old database in production. So the correct answer to this question is actually "do nothing - you'll get the old cardinality estimator for your old databases anyway".

edit: at least the question says you aren't changing any code in that database, which seems to me to imply it already exists.

Tom

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 5

I know this is nitpicking, but "change the database compatibility level to 110" is not really a correct answer. The question mentions upgrading, and the uograde process will not change the compatibility level. So the technically correct answer would be "don't do anything, and especially don't increase the compatibility level".

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 6

PChiragS

SSCarpal Tunnel

Points: 4965

April 30, 2014 at 6:48 pm

#1710035

nice question steve.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 7

I hate questions.

I've awarded back points (finally), and changed the question to note that after the upgrade, all databases were set to 120.

I'm open to other ways to work this and get the point across. Obviously I've failed somewhat here.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 8

free_mascot (4/29/2014)
paul s-306273 (4/29/2014)
Looks like I'm going to learn something everyday this week.
Thanks for the 2014 questions Steve.
+1

+1

Thanks