Queries
-
Toreador (11/28/2012)
I didn't understand the question at all, so guessed. Having read the explanation I was none the wiser.So I tried it, and the execution plan was empty.
confused.com
+1
Same was the case with me 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
This was removed by the editor as SPAM
-
Koen Verbeeck (11/28/2012)
The only correct answer is 0.There are 8 statements in the estimated execution plan, but no queries.
From BOL:
A query is a request for data that is stored in SQL Server.
There isn't a single statement in the batch that does a request for data, hence no queries.
+1
Toreador (11/28/2012)
I didn't understand the question at all, so guessed. Having read the explanation I was none the wiser.So I tried it, and the execution plan was empty.
confused.com
+1
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font] -
every person should be awarded +1 points who opted 0
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
The profiler shows Read 0
This is the most wrong and useless qotd in the history of ssc.
-
Koen Verbeeck (11/28/2012)
The only correct answer is 0.There are 8 statements in the estimated execution plan, but no queries.
From BOL:
A query is a request for data that is stored in SQL Server.
There isn't a single statement in the batch that does a request for data, hence no queries.
+1.
As most here say, the correct answer is ZERO. Still got it wrong though (answered 1) coz I thought this must be a trick question.
-
Koen Verbeeck (11/28/2012)
The only correct answer is 0.There are 8 statements in the estimated execution plan, but no queries.
From BOL:
A query is a request for data that is stored in SQL Server.
There isn't a single statement in the batch that does a request for data, hence no queries.
Thank you for the autoritative definition. I just wanted to ask "What is a querry?"
See, understand, learn, try, use efficient
© Dr.Plch -
Interesting question.
Quite bizarre.
My first reaction was "this is a trick, so 0 will be wrong and so will 9". But maybe it wasn't meant as a trick, but a good question to draw our attention to something rather strange.
Anyway, there are at least 5 different definitions of query knocking about:
1) a query is a dml statement (actually the most common definition, I think) - that definition couldn't be intended if 0 wasn't the answer.
2) a query is a statement that requests data from a database (the definition which has been quoted above; it's what I used to use until i realised I was out of step with the world and update and insert and delete statements were generally referred to as queries) - like (1) above, that results in 0 so probably wasn't the answer.
3) a query is any DML or DDL statement - that would still give answer 0.
4) a query is any sql statement - a pretty silly definition in my view) - but that delivers 9 so still not going to be the "right" definition.
5) a query is an sql statement that affects the state of the database. That's not at all a good definition because "affects the state of the datbase" would be the source of too much argument. For this set of statements it leads to 0, 1, or 2 depending on pecidely what this definition means, so not something I could use to pick an answer. Although it offers 1 and 2, either of which is sufficiently strange to be right in what looks so like a trick question, there would be no way but flipping a coin to choose between them.
So I started to try to dream up definitions that might do something different, and came up with two:
6) anything that shows up in an execution plan as a query - that's a real execution plan, not a maybe (aka estimated) execution plan; but that was going to be 0 so didn't fit in with the idea that 0 and 9 were not going to be right.
7) anything that shows up in a query in an "estimated execution plan"; I expected that to be 9 in this case, having seen some non-queries showing up in estimated plans before, but decided to try it and see and it produced 8.
Why definition 7 lead to 8? I haven't a clue - something bizarre is going on. Why doesn't it lead to 0? Again I haven't a clue - perhaps whatever does the estimating is just plain silly? Anyway, 8 was obviously bizarrre enough to satisfy the requirement to be the right answer to a trick question, as it was neither 0 nor 9, so that's what I picked. I had, admittedly, very little hope of getting a point, so I was quite pleased to get one.
So now we have some rather bizarre behaviours: one of these 9 statements is not, according to profiler, a statement. Neither is it, acording to estimated exectution plans, a query. Estimated execution plans show all the others as queries, although none of them is, on any reasonable defintion, a query. Those are interesting behaviours, and with a bit of luck someone will track down what causes them and tell us in this topic (not me, I'm too lazy to try to do that); I guess that makes it a good question.
Tom
-
ACTUAL execution plan agrees with BOL. The answer explanation clearly disagrees with both. If we assume that the correct answer would be the one the SQL Server returns, the answer is 0. Everyone who answered 0 OR 8 should be given the point.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson -
I came across this odd behavior in SSMS the other day when I was looking at estimated execution plans and thought I'd share it with the community.
Sorry if some do not have the same interest as I and a few others who have answered did.
I guess learning is kind of a subjective thing. One can learn as little or as much as one wants from any given situation.
Enjoy!
-
L' Eomot Inversé (11/28/2012)
So now we have some rather bizarre behaviours: one of these 9 statements is not, according to profiler, a statement. Neither is it, acording to estimated exectution plans, a query. Estimated execution plans show all the others as queries, although none of them is, on any reasonable defintion, a query. Those are interesting behaviours, and with a bit of luck someone will track down what causes them and tell us in this topic (not me, I'm too lazy to try to do that); I guess that makes it a good question.Actually, Tom, when I run this for Estimated Execution Plan, all of them show up in the 8 queries. BUT!
Queries 1 and 8 are the BEGIN TRANSACTION and COMMIT statements, respectively. That's 2 "queries."
Query 2 is a combination of the first two SET statements for SET QUOTED_IDENTIFIER ON; SET ARITHABORT ON;
One query, one line in the estimated plan. That's 1 query, for a running total of 3.
Queries 3 - 7 are the remaining 5 SET statements, 1 SET statement to a query. That's another 5 "queries", final running total is 8.
So that yields 8 "queries".
Why do the first 2 SET statements -- and only those 2 -- get combined into 1 "query"? No idea. Don't really care either.
I don't care about the points, but I think this is perhaps the most useless QoTD I've seen.
Rich
P.S. Tested on 2005 Enterprise.
-
Dave62 (11/28/2012)
I came across this odd behavior in SSMS the other day when I was looking at estimated execution plans and thought I'd share it with the community.Sorry if some do not have the same interest as I and a few others who have answered did.
I guess learning is kind of a subjective thing. One can learn as little or as much as one wants from any given situation.
Enjoy!
Dave, I appreciate your effort to share with us. But in order for it to be a learing experience, it would have been helpful if you had provided some kind of explanation. *WHY* does the estimated execution plan show only 8 "queries"? And *WHY* does the actual execution plan show none (well, I guess I can take a stab at the latter - because there are no actual queries!)
Next time you encounter some interesting behaviour, please create a Question of the Day for it again - but only do so after researching the issue, finding an explanation, and then add either the explanation or a link to it in the explanation of the correct answer.
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/ -
No, this question was not useless.
As many have said, the answer given was incorrect, but the value of this question lies in why the answer given was given, and why it's incorrect.
This shows that when estimating an execution plan, SQL server considers every statement -- even those which do not affect data or structures. This is important to know because SQL server must use some resources for each statement considered.
Since the actual execution plan included none of these statements, we know they were discarded in terms of optimization, probably because they have fixed costs (not as in constant across all environments but as in unchangeable by the optimizer.)
But these costs still exist. Some (probably very small amount per connection) resources can be saved by not including these statements except when actually necessary. All of these options should have server-, database-, and/or connection-level defaults where applicable. These defaults should be well-documented, and should only be changed when necessary.
Since the costs are small per connection, one obvious exception to this rule is if you writing code which must execute consistently in environments with different defaults. Then it can make sense to set the relevant options to what is expected by the given code.
-
Hugo Kornelis (11/28/2012)
Dave62 (11/28/2012)
I came across this odd behavior in SSMS the other day when I was looking at estimated execution plans and thought I'd share it with the community.Sorry if some do not have the same interest as I and a few others who have answered did.
I guess learning is kind of a subjective thing. One can learn as little or as much as one wants from any given situation.
Enjoy!
Dave, I appreciate your effort to share with us. But in order for it to be a learing experience, it would have been helpful if you had provided some kind of explanation. *WHY* does the estimated execution plan show only 8 "queries"? And *WHY* does the actual execution plan show none (well, I guess I can take a stab at the latter - because there are no actual queries!)
Next time you encounter some interesting behaviour, please create a Question of the Day for it again - but only do so after researching the issue, finding an explanation, and then add either the explanation or a link to it in the explanation of the correct answer.
Sometimes there are QotD with weak or incorrect explanation. Many times someone like You makes very good explanation. The discussion can be a source of inspiration and innormation. I learned a lot here.
This question is not the case.
See, understand, learn, try, use efficient
© Dr.Plch -
Ok, the right answer is 8. And the explanation is...? :unsure:
Viewing 15 posts - 16 through 30 (of 60 total)
You must be logged in to reply to this topic. Login to reply