Execution Plan Cursors
-
Comments posted to this topic are about the item Execution Plan Cursors
-
This was removed by the editor as SPAM
-
If it`s going to make you dig `n` search, then it`s a good question (most probably).
Thanks Steve.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
Steve, good question. Thanks.
-
Hany Helmy (12/21/2013)
If it`s going to make you dig `n` search, then it`s a good question (most probably).Thanks Steve.
+1
~ 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
-
very good start of the week and QotD 🙂
Thanks for sharing Steve.
-
I liked the question, as it made me do research. Thank you, Steve.
-
Wasn't really sure when I clicked 'submit'. I would have thought that KEYSET was a TYPE of cursor, and not the operator that generated the cursor.
For example (stolen from a 2007 Grant Fritchey post):
DECLARE CurrencyList CURSOR KEYSET FORSELECT CurrencyCode FROM [Sales].[Currency]
WHERE Name LIKE '%Dollar%'
But, since none of the other choices seemed correct, I went with it and was rewarded with another point I can put in my retirement account.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
Can I get 1 point for getting 1 right?
-
Thomas Abraham (12/23/2013)
But, since none of the other choices seemed correct, I went with it and was rewarded with another point I can put in my retirement account.What is the current SQL Server Central point conversion to US Dollar ratio these days anyway?
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
Thomas Abraham (12/23/2013)
Wasn't really sure when I clicked 'submit'. I would have thought that KEYSET was a TYPE of cursor, and not the operator that generated the cursor.For example (stolen from a 2007 Grant Fritchey post):
DECLARE CurrencyList CURSOR KEYSET FORSELECT CurrencyCode FROM [Sales].[Currency]
WHERE Name LIKE '%Dollar%'
But, since none of the other choices seemed correct, I went with it and was rewarded with another point I can put in my retirement account.
+1. I expected to somehow be wrong on this one although BOL pointed me at the right answer.
-
Ed Wagner (12/23/2013)
I liked the question, as it made me do research. Thank you, Steve.+1
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 -
This was removed by the editor as SPAM
-
good question Steve.
Thanks
-
In all honesty - I do not like this question.
"Which of these operators creates a cursor" - define cursor?
The dictionary gives me two meanings, neither of which is appropriate here: the blinking symbol on my screen that points to where the next character will be entered, and the sliding part of a measuring instrument.
Within the context of SQL Server, there are a few common interpretations for this word.
* A specific language feature (both in ANSI SQL and in T-SQL with some additional features) that is used for row by row processing. These are not created by execution plan operators. Specific plan operators may be used by the optimizer to implement them - so you could say that a cursor "creates" these operators. But definitely not the other way around.
* Any T-SQL code that does row by row processing. This includes the standard CURSOR feature, but also includes other ways to fetch rows one at a time from a table. These, too, are not created by execution plan operators.
* Any form of internal row by row processing. When I first saw the question, I though this was the intended interpretation. And since all execution plans internally always process rows one by one (with the except of batch-enabled parts of a plan in SQL Server 2012 when columnstore indexes are involved), these "cursors" would be created by the topmost operators - and those are always SELECT, INSERT, UPDATE, DELETE, or MERGE. (Or OPEN CURSOR / FETCH CURSOR if you use T-SQL cursors in your code). None of these were available as answer options.
* Within relational theory, the term "cursor" is sometimes used to specify that the data returned is technically no longer a "set" (a bunch of data that is conceptually unordered and can hence be handled in any order), but ordered data that is supposed to be processed in the order it is produced. Relational theory purists say that a query that has an ORDER BY clause returns a cursor, and queries without ORDER BY cliase return a set. I cannot see any way to apply this definition to this question.
At this point, I knew I would have to disagree with the answer whatever it was. But I still wanted to answer, so I used my google-fu and found the article that is also referenced in the answer. And yes - Books Online does state that "The Snapshot operator creates a cursor (...)" - bad wording in Books Online! It also states that "The Keyset operator uses a cursor (...)" - hmmm, uses, not creates. But close enough, and a lot closer than the alternatives, so I went with it and got it right.
But I still don't like the question, I do not agree with Books Online, and I do not agree with the "correct" answer. None of these operators creates a cursor.
Oh, and for what it's worth - I also investigated when one would actually see these operators. Turns out, you can see them if:
1) you write code that used a T-SQL cursor; and
2) you look at the estimated execution plan of the DECLARE CURSOR statement.
You will not see them in the actual plan. In fact, you will not see an actual plan at all when executing a DECLARE CURSOR statement. The reason for that is simple: a DECLARE CURSOR statement will cause the optimizer to create up to two plans, tied together in the logical plan by one of the operators Snapshot, Dynamic, Fast Forward, or Keyset. These plans are not executed (hence no actual plan), but cached. When you then execute the OPEN CURSOR statement, the first part of the plan ("Population Query") will be executed. And each subsequent FETCH will then execute the second part of the plan ("Fetch Query"). If you first look at the estimated plan of the DECLARE CURSOR, then at the ]actual execution plan of the OPEN CURSOR and FETCH statements, you will see this.
So again: the Keyset and Snapshot operators do not create a cursor; they are created as a result of a cursor, and serve as an umbrella for the two cached plans that will be used for opening the cursor and for fetching its rows.
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/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply