Are the posted questions getting worse?
-
Brandie Tarvin (3/27/2015)
ChrisM@Work (3/27/2015)
Brandie Tarvin (3/27/2015)
dwain.c (3/26/2015)
ChrisM@Work (3/26/2015)
Where’s a plan showing huge Estimated Operator Costs when you need one? Have a look at the estimated plan for this conditional query i.e. the IF as well as the SELECT. You probably don’t want to execute the query in prod and you don’t need to – you only see the skew in the estimated plan. The explanation is surprisingly simple 😉IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))SELECT 'Anything' FROM sys.columns a
FULL OUTER JOIN sys.columns b
ON a.[precision] = b.[precision]
Quite a bizarre estimated plan to be sure. Facts:
- The issue goes away if you remove the IF
You mean remove the entire IF clause, including the SELECT ... CAST? Or are you just removing the IF keyword and putting something else in its place?
I'm assuming the former, but I just want to be sure.
Just the conditional, which results in two estimated plans instead of one:
--IF EXISTS(SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))
SELECT 'Anything' FROM sys.columns a
FULL OUTER JOIN sys.columns b
ON a.[precision] = b.[precision]
Edit: first release was designed to confuse.
Funny... Oh, and now I'm getting a yellow triangle on one of the estimated execution plans. Nested Loops (No Join Predicate). I don't think I've ever seen a warning triangle on an execution plan before. NEAT.
I wonder if there's a way to get a little failure red circle on one...
What are you on? I mean, what SQL Server version? 😉
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM@Work (3/27/2015)
Brandie Tarvin (3/27/2015)
ChrisM@Work (3/27/2015)
Brandie Tarvin (3/27/2015)
dwain.c (3/26/2015)
ChrisM@Work (3/26/2015)
Where’s a plan showing huge Estimated Operator Costs when you need one? Have a look at the estimated plan for this conditional query i.e. the IF as well as the SELECT. You probably don’t want to execute the query in prod and you don’t need to – you only see the skew in the estimated plan. The explanation is surprisingly simple 😉IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))SELECT 'Anything' FROM sys.columns a
FULL OUTER JOIN sys.columns b
ON a.[precision] = b.[precision]
Quite a bizarre estimated plan to be sure. Facts:
- The issue goes away if you remove the IF
You mean remove the entire IF clause, including the SELECT ... CAST? Or are you just removing the IF keyword and putting something else in its place?
I'm assuming the former, but I just want to be sure.
Just the conditional, which results in two estimated plans instead of one:
--IF EXISTS(SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))
SELECT 'Anything' FROM sys.columns a
FULL OUTER JOIN sys.columns b
ON a.[precision] = b.[precision]
Edit: first release was designed to confuse.
Funny... Oh, and now I'm getting a yellow triangle on one of the estimated execution plans. Nested Loops (No Join Predicate). I don't think I've ever seen a warning triangle on an execution plan before. NEAT.
I wonder if there's a way to get a little failure red circle on one...
What are you on? I mean, what SQL Server version? 😉
2008 Enterprise (no R2 unfortunately).
-
My Friday fun version:rolleyes:
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n)) SELECT 1 FROM sys.all_columns X,sys.columns Y,sys.computed_columns Z -
I hate Fridays around this place...
Unless you've got some project going on that can be worked on when 50+% of the staff is off, you spend a good chunk of the day trying to keep busy...
No point to this post, just a general whine...
-
jasona.work (3/27/2015)
I hate Fridays around this place...Unless you've got some project going on that can be worked on when 50+% of the staff is off, you spend a good chunk of the day trying to keep busy...
No point to this post, just a general whine...
Wine should help
-
jasona.work (3/27/2015)
I hate Fridays around this place...Unless you've got some project going on that can be worked on when 50+% of the staff is off, you spend a good chunk of the day trying to keep busy...
No point to this post, just a general whine...
So join us in producing the most obscure execution plan:-D
😎
-
djj (3/27/2015)
jasona.work (3/27/2015)
I hate Fridays around this place...Unless you've got some project going on that can be worked on when 50+% of the staff is off, you spend a good chunk of the day trying to keep busy...
No point to this post, just a general whine...
Wine should help
Silent "h" type....
😎
-
Brandie Tarvin (3/27/2015)
ChrisM@Work (3/27/2015)
Brandie Tarvin (3/27/2015)
So here's a tale from my workplace that will change up the conversation...I was having a Really Bad Day last week. I was trying to send an email to a committee, so I grabbed an email sent out by a coworker because I figured he had sent it to just the committee members. I then REPLIED ALL, wiped out the subject and body, and replaced it with my own message. Fortunately it wasn't a bad message, it was just a link to the importance of peers in employee recognition. But basically I sent out an email to the entire division when I didn't mean to.
If that weren't bad enough, we were having major production issues that morning. Plus I did another Stupid Thing that I can't place at the moment. Some of my coworkers were teasing me and I threw my hands up in the air and said "This is an emergency chocolate morning!"
I meant it as a joke, but the head of the Help Desk team was standing by my desk and he offered to go find me some. I laughed and told him it wasn't necessary. Thirty minutes later, he came by my desk and delivered a brand new bag of Ghirardelli dark chocolate squares to me. He apparently had called another employee (one of the server guys) and asked him to stop by the store on the way into work. Essentially the two colluded to get me some emergency chocolate and brighten up my day.
It's little things like that which make me love the people I work with. This is a workplace that looks out for each other.
How many of you have coworkers willing to do things like this?
The bu88ers here are more likely to nick my morning Snickers bar than provide a supplemental pick-me-up in hard times 😛
I lie, they're a great bunch. Six of us in this pod, two gay (one of each), one native Kenyan who, when he smiles (which is often) creates a stunning monochrome picture, one total geek, one cyclist (boo) and an old git - me. Or to put it another way - a BA, three coders and two system admins.
I have two more days to enjoy their company and feed them cookies 🙁
That sounds like a contract coming to an end.
Yep. Back to the Knicker shop in two weeks.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
jasona.work (3/27/2015)
I hate Fridays around this place...Unless you've got some project going on that can be worked on when 50+% of the staff is off, you spend a good chunk of the day trying to keep busy...
No point to this post, just a general whine...
Would you like some cheese with that?
-
Eirikur Eiriksson (3/27/2015)
My Friday fun version:rolleyes:IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n)) SELECT 1 FROM sys.all_columns X,sys.columns Y,sys.computed_columns ZAWESOME!!!!!!!
-
GilaMonster (3/27/2015)
Luis Cazares (3/26/2015)
By the way, about the article that Gail posted where they mention a comic book cover. I found that discussion out of place.Yeah, that protest is stupid.
The reason that Person B's response to that was discussed (and I do know both the author and Person B) is that the reaction wasn't a specific one of 'that protest is stupid' but rather 'they're ruining *everything* for *everyone*' (paraphrased), another massive, useless generalisation that serves to end any conversation you could possibly have
Yes, I understood that the point of quoting him wasn't the protest, but the attitude that person takes on subjects he's against or not interested.
And I thank Brandie for the explanation of the cover being a trigger and should have been handled better.
-
Question of the day: what datatype is the Estimated Number of Rows (or Estimated Subtree Cost) displayed in the property sheet of the SELECT operator in the estimated plan of this batch?
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))WITH a AS (SELECT n = 1 FROM sys.columns a,sys.columns b,sys.columns c),
b AS (SELECT n = 1 FROM a a, a b, a c),
c AS (SELECT n = 1 FROM b a, b b, b c),
d AS (SELECT n = 1 FROM c a, c b, c c)
SELECT * FROM d
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM@Work (3/27/2015)
Question of the day: what datatype is the Estimated Number of Rows (or Estimated Subtree Cost) displayed in the property sheet of the SELECT operator in the estimated plan of this batch?IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))WITH a AS (SELECT n = 1 FROM sys.columns a,sys.columns b,sys.columns c),
b AS (SELECT n = 1 FROM a a, a b, a c),
c AS (SELECT n = 1 FROM b a, b b, b c),
d AS (SELECT n = 1 FROM c a, c b, c c)
SELECT * FROM d
Trivial, 1.34078E+154
😎
-
Brandie Tarvin (3/27/2015)
Eirikur Eiriksson (3/27/2015)
My Friday fun version:rolleyes:IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n)) SELECT 1 FROM sys.all_columns X,sys.columns Y,sys.computed_columns ZAWESOME!!!!!!!
Yes you can certainly get a red circle on them. Eirikur did a great job with those operator percentages as well. It's equally as fun producing a plan that has 19 quintillion rows coming from a 290 row table (with or without xml editing).
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 -
In a different order of ideas, I want to share with you my happiness.
Starting on April 21st I'll be working in the US. I'll make a stop at Miami on the 20th to continue my trip on Tuesday. I'll spend some time at Detroit and possibly be changing my location after being assigned a project on-site with a client.
I just can't wait to start my new adventure.
Viewing 15 posts - 47,941 through 47,955 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply