April 29, 2014 at 7:14 am
shanjan.sapra (4/29/2014)
Nice question. But I guess the another answer is also true as an Another alternative to enable/disable the different CE algorithms is the usage of a trace flag. Even with the compatibility level of a database set to ‘SQL Server 2014 (120)’, the trace flag would enforce the usage of the old CE.Hence the approach to change to the new CE would be the same and use the trace flag 9481 to enforce the old CE algorithms.
Enabling the trace flag would disable it for entire server not just one database.
The question clearly states the person wans just one database to not use the CE, not every database on the server.
April 29, 2014 at 7:23 am
shanjan.sapra (4/29/2014)
Nice question. But I guess the another answer is also true as an Another alternative to enable/disable the different CE algorithms is the usage of a trace flag. Even with the compatibility level of a database set to ‘SQL Server 2014 (120)’, the trace flag would enforce the usage of the old CE.Hence the approach to change to the new CE would be the same and use the trace flag 9481 to enforce the old CE algorithms.
"You can enable these trace flags at server, session or query level. To enable the trace flag at query level, you use QUERYTRACEON hint documented in KB2801413. "
From this article:
You can not enable these trace flags at only the database level.
April 29, 2014 at 7:24 am
PHYData DBA (4/29/2014)
shanjan.sapra (4/29/2014)
Nice question. But I guess the another answer is also true as an Another alternative to enable/disable the different CE algorithms is the usage of a trace flag. Even with the compatibility level of a database set to ‘SQL Server 2014 (120)’, the trace flag would enforce the usage of the old CE.Hence the approach to change to the new CE would be the same and use the trace flag 9481 to enforce the old CE algorithms.
Enabling the trace flag would disable it for entire server not just one database.
The question clearly states the person wans just one database to not use the CE, not every database on the server.
This webpage[/url] clearly shows that the trace flag is used per query, so any queries on any database that you want to use the new optimiser, you can just leave the flag out
April 29, 2014 at 7:27 am
paul.goldstraw (4/29/2014)
PHYData DBA (4/29/2014)
shanjan.sapra (4/29/2014)
Nice question. But I guess the another answer is also true as an Another alternative to enable/disable the different CE algorithms is the usage of a trace flag. Even with the compatibility level of a database set to ‘SQL Server 2014 (120)’, the trace flag would enforce the usage of the old CE.Hence the approach to change to the new CE would be the same and use the trace flag 9481 to enforce the old CE algorithms.
Enabling the trace flag would disable it for entire server not just one database.
The question clearly states the person wans just one database to not use the CE, not every database on the server.
This webpage[/url] clearly shows that the trace flag is used per query, so any queries on any database that you want to use the new optimiser, you can just leave the flag out
This is a great example of a correct statement that is still wrong.
What you are saying is true, but that was not one of the possible answers now was it?
It's not even what the post you are quoting is talking about.
April 29, 2014 at 7:34 am
PHYData DBA (4/29/2014)
paul.goldstraw (4/29/2014)
PHYData DBA (4/29/2014)
shanjan.sapra (4/29/2014)
Nice question. But I guess the another answer is also true as an Another alternative to enable/disable the different CE algorithms is the usage of a trace flag. Even with the compatibility level of a database set to ‘SQL Server 2014 (120)’, the trace flag would enforce the usage of the old CE.Hence the approach to change to the new CE would be the same and use the trace flag 9481 to enforce the old CE algorithms.
Enabling the trace flag would disable it for entire server not just one database.
The question clearly states the person wans just one database to not use the CE, not every database on the server.
This webpage[/url] clearly shows that the trace flag is used per query, so any queries on any database that you want to use the new optimiser, you can just leave the flag out
This is a great example of a correct statement that is still wrong.
What you are saying is true, but that was not one of the possible answers now was it?
It's not even what the post you are quoting is talking about.
The final option was "Alter all queries to use the trace flag 9481". Altering the queries in the way the link mentions, but using trace flag 9481 instead of 2312 as in the example given would force those queries to use the old optimiser, which is what the question is asking. The answer Steve is saying is correct wouldn't allow the use of memory optimised tables, which the question said was the point of upgrading in the first place.
April 29, 2014 at 7:35 am
PHYData DBA (4/29/2014)
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:It is an option in the database properties page.
I was not clear enough :pinch:
Every linked document from QoTD or this thread states upgrading does not change compatibility level of any database (the same when attaching database of older version). That means you don't have to do anything to not use new Cardinality Estimator in existing database :doze:
That was not an option for QoTD :discuss:
April 29, 2014 at 7:41 am
paul.goldstraw (4/29/2014)
PHYData DBA (4/29/2014)
paul.goldstraw (4/29/2014)
PHYData DBA (4/29/2014)
shanjan.sapra (4/29/2014)
Nice question. But I guess the another answer is also true as an Another alternative to enable/disable the different CE algorithms is the usage of a trace flag. Even with the compatibility level of a database set to ‘SQL Server 2014 (120)’, the trace flag would enforce the usage of the old CE.Hence the approach to change to the new CE would be the same and use the trace flag 9481 to enforce the old CE algorithms.
Enabling the trace flag would disable it for entire server not just one database.
The question clearly states the person wans just one database to not use the CE, not every database on the server.
This webpage[/url] clearly shows that the trace flag is used per query, so any queries on any database that you want to use the new optimiser, you can just leave the flag out
This is a great example of a correct statement that is still wrong.
What you are saying is true, but that was not one of the possible answers now was it?
It's not even what the post you are quoting is talking about.
The final option was "Alter all queries to use the trace flag 9481". Altering the queries in the way the link mentions, but using trace flag 9481 instead of 2312 as in the example given would force those queries to use the old optimiser, which is what the question is asking. The answer Steve is saying is correct wouldn't allow the use of memory optimised tables, which the question said was the point of upgrading in the first place.
You are right. It said Alter ALL queries to use this trace flag. Not just the queries that use the database they want to disable CE for. Makes it a wrong answer and makes it something that the thread you are trolling was not talking about.
Also your other point is invalid because the question never states he wants to use Memory optimized tables on the database, just that he upgraded an instance of SQL server to use them.
Read and comprehend the entire question before answering. It is also really cool if you do that before posting/quoting a thread that is not yours. 😎
April 29, 2014 at 7:55 am
PHYData DBA (4/29/2014)
...
You are right. It said Alter ALL queries to use this trace flag. Not just the queries that use the database they want to disable CE for. Makes it a wrong answer and makes it something that the thread you are trolling was not talking about.
Also your other point is invalid because the question never states he wants to use Memory optimized tables on the database, just that he upgraded an instance of SQL server to use them.
Read and comprehend the entire question before answering. It is also really cool if you do that before posting/quoting a thread that is not yours. 😎
I think we're just reading the question differently. I read 'ALL queries' in the context of the database we don't want to use the new optimiser. If we changed all queries in that DB then we would only be using the old optimiser, which is what the question is asking.
Regards the use of memory optimised tables, I could just as reasonably say your point is invalid, Steve makes specific mention of using this functionality in the question, and it seems more of a reach for you to assume this is an exception that for me to assume it isn't.
I did read and comprehend the whole question, and I don't appreciate being called a troll. Reading a question differently to you, and having a different opinion doesn't make me a troll. It's also not inappropriate to post links to other people's blogs
April 29, 2014 at 8:15 am
PHYData DBA (4/29/2014)
It is an option in the database properties page.
+1
This is the link that I referenced when researching this QOTD, and found it very informative.
Thanks for another great question on the new features of SQL 2014!
April 29, 2014 at 8:30 am
paul.goldstraw (4/29/2014)
I think we're just reading the question differently. I read 'ALL queries' in the context of the database we don't want to use the new optimiser. If we changed all queries in that DB then we would only be using the old optimiser, which is what the question is asking.Regards the use of memory optimised tables, I could just as reasonably say your point is invalid, Steve makes specific mention of using this functionality in the question, and it seems more of a reach for you to assume this is an exception that for me to assume it isn't.
I did read and comprehend the whole question, and I don't appreciate being called a troll. Reading a question differently to you, and having a different opinion doesn't make me a troll. It's also not inappropriate to post links to other people's blogs
Paul we are definitely reading the question differently.
However, you need to ask your self what the difference between these two sentences are. Be honest to yourself with the answer.
Sentence One - "I have upgraded my instance of SQL Server to 2014 to take advantage of memory-optimized tables."
Sentence Two - " If I do not want the cardinality estimatior to be used in a specific database, what can I do?"
One sentence is talking about an entire server instance, and another clearly states "a specific database".
If you were arguing that you can never turn off the CE, only the new CE that is in SQL 2014, I would be agreeing with you. However that is not the thing you posted to a thread that had nothing to do with you.
You are trying to tell me that Sentence One and Two above are talking about the same subject. They clearly are not and that is not interpretative. 😎
April 29, 2014 at 8:33 am
dawryn (4/29/2014)
PHYData DBA (4/29/2014)
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:It is an option in the database properties page.
I was not clear enough :pinch:
Every linked document from QoTD or this thread states upgrading does not change compatibility level of any database (the same when attaching database of older version). That means you don't have to do anything to not use new Cardinality Estimator in existing database :doze:
That was not an option for QoTD :discuss:
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. 😎
April 29, 2014 at 8:45 am
PHYData DBA (4/29/2014)
paul.goldstraw (4/29/2014)
I think we're just reading the question differently. I read 'ALL queries' in the context of the database we don't want to use the new optimiser. If we changed all queries in that DB then we would only be using the old optimiser, which is what the question is asking.Regards the use of memory optimised tables, I could just as reasonably say your point is invalid, Steve makes specific mention of using this functionality in the question, and it seems more of a reach for you to assume this is an exception that for me to assume it isn't.
I did read and comprehend the whole question, and I don't appreciate being called a troll. Reading a question differently to you, and having a different opinion doesn't make me a troll. It's also not inappropriate to post links to other people's blogs
Paul we are definitely reading the question differently.
However, you need to ask your self what the difference between these two sentences are. Be honest to yourself with the answer.
Sentence One - "I have upgraded my instance of SQL Server to 2014 to take advantage of memory-optimized tables."
Sentence Two - " If I do not want the cardinality estimatior to be used in a specific database, what can I do?"
One sentence is talking about an entire server instance, and another clearly states "a specific database".
If you were arguing that you can never turn off the CE, only the new CE that is in SQL 2014, I would be agreeing with you. However that is not the thing you posted to a thread that had nothing to do with you.
You are trying to tell me that Sentence One and Two above are talking about the same subject. They clearly are not and that is not interpretative. 😎
This is a question of the day, how is it nothing to do with me? Anyone is welcome to chip in.
April 29, 2014 at 8:48 am
paul.goldstraw (4/29/2014)
It's also not inappropriate to post links to other people's blogs
In what way? Where is this documented? I have seen nothing but links to other peoples article and blogs posted here since I created my first work account on this site in 2006 (I am on my third now).
You did not earn troll by reading the question different. You earned that by commenting negatively on a comment posted to a different reader using a different topic than the one me and the other commenter was using. Then you continued to cross post on someone else's thread. :sick:
April 29, 2014 at 8:50 am
I'm going to award back points for this one. I've reworded the question, as I implied that I might use memory optimized tables and want the old CE in the same database. That would not be correct.
I also said "turn off the CE", but I meant "turn off the NEW CE".
My apologies.
April 29, 2014 at 12:37 pm
Great question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply