December 14, 2016 at 11:29 am
I'm sure we all are aware of this, but just to be clear for others who might stumble across this discussion, this functionality is tied to a version of SSMS, not the database engine.
You can compare plans from any version of SQL Server that still has official support, so it doesn't matter if you're running 2008(R2)/2012/2014/2016 for the database engine.
Cheers!
December 14, 2016 at 3:27 pm
sknox (12/14/2016)
Koen Verbeeck (12/14/2016)
sknox (12/14/2016)
The question is written as "How can you identify any differences?"However, 3 of the four answers fit that question as written.
The question should have been "How can you most easily identify all differences?"
SQL professionals should know the importance of phrasing a query properly.
True.
However you can only select one answer.
In real life situations, if you have 3 alternative solutions to a problem and one is clearly the most efficient, which one are you going to choose?
In real life, if you were doing code review and saw a query of the form:
SELECT TOP 1 ....WITHOUT an ORDER BY clause, with knowledge that the query without TOP would return multiple rows with different values, would you pass it?
No, because it's clearly not a correct solution.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2016 at 4:55 pm
Koen Verbeeck (12/14/2016)
sknox (12/14/2016)
The question is written as "How can you identify any differences?"However, 3 of the four answers fit that question as written.
The question should have been "How can you most easily identify all differences?"
SQL professionals should know the importance of phrasing a query properly.
True.
However you can only select one answer.
In real life situations, if you have 3 alternative solutions to a problem and one is clearly the most efficient, which one are you going to choose?
In real life situations the latest version of SQL Server is not available.
Especially in Prod environment, where you need to analyse query plans.
_____________
Code for TallyGenerator
December 14, 2016 at 6:55 pm
Koen Verbeeck (12/14/2016)
Igor Micev (12/14/2016)
Nowhere in the question is stated that the SSMS is 2016 version. "Compare Showplan" is not available in the lower versions.So the answer is not always correct.
As said before, always assume most recent version unless explicitly stated otherwise.
DB people do not usually assume much.
Otherwise they are quickly out of jobs.
And if it comes to assumptions, then it must be rather "most popular version (the one with the most running instances on actual servers, not Redgate's laptops) unless explicitly stated otherwise".
Otherwise almost any question can be countered with "yeah but it doesn't work on SQL v7".
v7 is deprecated and out of its life cycle.
Same as SQL2000.
They definitely would require an explicit statement in a QOD is applicable to them.
_____________
Code for TallyGenerator
December 14, 2016 at 9:57 pm
Koen Verbeeck (12/14/2016)
sknox (12/14/2016)
Koen Verbeeck (12/14/2016)
sknox (12/14/2016)
The question is written as "How can you identify any differences?"However, 3 of the four answers fit that question as written.
The question should have been "How can you most easily identify all differences?"
SQL professionals should know the importance of phrasing a query properly.
True.
However you can only select one answer.
In real life situations, if you have 3 alternative solutions to a problem and one is clearly the most efficient, which one are you going to choose?
In real life, if you were doing code review and saw a query of the form:
SELECT TOP 1 ....WITHOUT an ORDER BY clause, with knowledge that the query without TOP would return multiple rows with different values, would you pass it?
No, because it's clearly not a correct solution.
You're absolutely right. It's clearly not correct.
This question as written is the English equivalent of that form. It's clearly not correct.
December 15, 2016 at 12:30 am
Sergiy (12/14/2016)
Koen Verbeeck (12/14/2016)
sknox (12/14/2016)
The question is written as "How can you identify any differences?"However, 3 of the four answers fit that question as written.
The question should have been "How can you most easily identify all differences?"
SQL professionals should know the importance of phrasing a query properly.
True.
However you can only select one answer.
In real life situations, if you have 3 alternative solutions to a problem and one is clearly the most efficient, which one are you going to choose?
In real life situations the latest version of SQL Server is not available.
Especially in Prod environment, where you need to analyse query plans.
SQL Server 2016 is available. It already runs in production environments.
But, as someone mentioned, the version of SQL Server doesn't even matter that much. It's the version of SSMS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 15, 2016 at 2:17 am
So 36% of people thought that either: You don't need to compare them since the graphical plans are the same, which is blatantly false, or that You can compare the tool tips to arrive at the differences, which is also completely false since the differences are on property values not found in the tool tips. I'm sorry if anyone thought those were correct answers, they're not.
As to checking the property values one by one, which 24% thought it was... sure, I suppose that can be construed as a possibly correct answer. Please feel free to check all the properties on the attached picture of a plan to identify any differences if I supply a very similar or nearly identical plan... I'll wait... In short, a possible answer, but certainly not the best possible answer.
I didn't include the version of SQL Server because the new functionality covers 2012, 2014 and 2016. SSMS is divorced from the SQL Server engine now (and you should absolutely not be running the GUI directly on your production servers, regardless of the version).
It's one point out of 10 million possible points. Personally, I'd let it go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 15, 2016 at 2:22 am
Grant Fritchey (12/15/2016)
Personally, I'd let it go.
... aaaaand now I have the soundtrack of Frozen into my head. Thanks. :crazy:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 15, 2016 at 2:37 am
Oh here, let me help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 15, 2016 at 2:43 am
Grant Fritchey (12/15/2016)
Oh here, let me help.
The worst part is: I can already guess what you're linking to, but still get the song stuck in my head just by thinking about it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 15, 2016 at 2:55 am
Grant Fritchey (12/15/2016)
So 36% of people thought that either: You don't need to compare them since the graphical plans are the same, which is blatantly false, or that You can compare the tool tips to arrive at the differences, which is also completely false since the differences are on property values not found in the tool tips. I'm sorry if anyone thought those were correct answers, they're not.As to checking the property values one by one, which 24% thought it was... sure, I suppose that can be construed as a possibly correct answer. Please feel free to check all the properties on the attached picture of a plan to identify any differences if I supply a very similar or nearly identical plan... I'll wait... In short, a possible answer, but certainly not the best possible answer.
I didn't include the version of SQL Server because the new functionality covers 2012, 2014 and 2016. SSMS is divorced from the SQL Server engine now (and you should absolutely not be running the GUI directly on your production servers, regardless of the version).
It's one point out of 10 million possible points. Personally, I'd let it go.
To be precise, the difference appears not in execution plans.
The differences all belong to compilations.
Execution plans are identical, because both queries are compiled at the end to the same execution code.
So, the new feature in 2016 SSMS is a bit misleading, even though it's quite useful.
_____________
Code for TallyGenerator
December 15, 2016 at 4:58 am
I've edited the question to state Management Studio 2016.
As Grant mentioned, this has nothing to do with SQL Server versions, but the tools version. However, since these were linked prior to this summer, I think it makes some sense to ensure people are aware that they can change SSMS without upgrading SQL Server.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply