March 26, 2015 at 8:08 pm
SQLRNNR (3/26/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
- The issue doesn't show in the actual execution plan
- The query itself runs reasonably quickly.
Edit: This query shows the same thing:
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))
SELECT * FROM sys.all_columns;
Which means that it has something to do with the 100% cost calculated for the Constant Scan generated for the IF. The remainder of the query must be getting its costs rated against that.
Add this to the list of SQL Developer interviewee questions...
Interesting indeed.
I generally "manufacture" a plan if I really want to demonstrate something out of whack and quite get it to show with data or fascinating queries. It is all just xml afterall.
With minimal skew, but skew just the same:
IF EXISTS (SELECT n FROM (VALUES(CAST(1 AS BIT))) d (n))
SELECT 1 FROM (VALUES(0),(0)) d (n) CROSS JOIN (VALUES(0),(0)) e (n);
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 27, 2015 at 3:47 am
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
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2015 at 4:13 am
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
- The issue doesn't show in the actual execution plan
- The query itself runs reasonably quickly.
Edit: This query shows the same thing:
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))
SELECT * FROM sys.all_columns;
Which means that it has something to do with the 100% cost calculated for the Constant Scan generated for the IF. The remainder of the query must be getting its costs rated against that.
Add this to the list of SQL Developer interviewee questions...
Choose a db with a relatively small number of columns and an EXISTS query with a less than minimal cost - for accuracy:
USE master
IF EXISTS (SELECT TOP 1 1 FROM sys.columns)
SELECT 'Anything' FROM sys.columns a
FULL OUTER JOIN sys.columns b
ON a.[precision] = b.[precision]
The hash match in the estimated plan for the SELECT query alone has an estimated cost relative to the total of 83%. The same operator in the conditional query has a cost of 7686%.
The estimated plan for the EXISTS query alone shows an Estimated Subtree Cost of 0.0032835; the ESC for the SELECT query is 0.304218.
0.0032835/0.304218 * 7686% = 82.9%
The percentage Estimated Operator Costs in the SELECT query are inflated by (the Estimated Subtree Cost of the SELECT query divided by the ESC of the EXISTS query). A cheap EXISTS query controlling an expensive SELECT (or DELETE) results in a nicely skewed plan.
For 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
March 27, 2015 at 4:29 am
GilaMonster (3/25/2015)
spaghettidba (3/24/2015)
What I find disturbing is the assumption that men are sexist and if they say they don't do that it's just "not-meing".That's certainly not what I said anywhere in the post you quoted. (yes, there are some that assume that all men are evil but there are idiots everywhere)
I said that the 'I've never seen that' reaction is unproductive. Brandie said the same thing. The 'I'm not like that' is similar.
If I was talking about how many times my house had been broken into, no one would start their reply with 'Well I don't rob houses...'
Well said, Gail.
March 27, 2015 at 5:10 am
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. Feminists reacted to an image showing violence against women. The truth is that it was a violent psychopath against a specific girl, who used that tragedy to become better. But nobody complained about The Joker killing Robin or treating Harley Quinn as garbage all the time (which is closer to most domestic violence cases).
I guess this depends on perspective. Harley Quinn (yes, she's a poster child for domestic violence) has become an empowered bad-*** woman who deliberately subjects herself to the Joker's behavior because of an obsession issue. On the other hand, she did try to kill him the last time he pulled some crap (they were dancing on the edge of a volcano). So yes, there are issues, but they are not anti-feminist because they are part of her character.
The Batgirl cover was a variant that had nothing to do with the current story line (well, none of them really do), but no other variant cover DC has recently released did something so disempowering to any of its heroes. Except, perhaps the Flash / Wonder Woman cover where Wonder Woman was standing at an altar getting married to a monster. None of the male heroes have quite received that kind of treatment. In my opinion, the biggest issue with the Batgirl cover wasn't that it was anti-feminist. The problem was that cover was a trigger.
For those who don't know the phrase "trigger warning," a trigger is an image or text that causes a rape / assault victim (or even people with PTSD) to flash back on the experience that caused their trauma. Triggers work on both men and women. In the case of the Batgirl cover, though, it was a trigger that caused more problems for women than for men. Another fun fact about the Joker shooting Batgirl, a lot of people assume (even though it was never stated in the story) that the Joker sexually assualted Barbara Gordon either before or after he shot her.
So, lots and lots and lots of drama there. And I totally see their point, if only because this was the first cover DC has done (that I know of) that requires a trigger warning attached to it.
March 27, 2015 at 5:57 am
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?
March 27, 2015 at 7:01 am
dwain.c (3/26/2015)
...Edit: This query shows the same thing:
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))
SELECT * FROM sys.all_columns;
Which means that it has something to do with the 100% cost calculated for the Constant Scan generated for the IF. The remainder of the query must be getting its costs rated against that.
Add this to the list of SQL Developer interviewee questions...
There are some amusing variants - for example try changing the condition:
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))
SELECT * FROM sys.all_columns;
😀
Tom
March 27, 2015 at 7:22 am
TomThomson (3/27/2015)
dwain.c (3/26/2015)
...Edit: This query shows the same thing:
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))
SELECT * FROM sys.all_columns;
Which means that it has something to do with the 100% cost calculated for the Constant Scan generated for the IF. The remainder of the query must be getting its costs rated against that.
Add this to the list of SQL Developer interviewee questions...
There are some amusing variants - for example try changing the condition:
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))SELECT * FROM sys.all_columns;
😀
Nice one, Tom - it's the simplest yet and on this server I see 27 million % cost for one of the operators :w00t:
For 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
March 27, 2015 at 7:51 am
ChrisM@Work (3/27/2015)
TomThomson (3/27/2015)
dwain.c (3/26/2015)
...Edit: This query shows the same thing:
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))
SELECT * FROM sys.all_columns;
Which means that it has something to do with the 100% cost calculated for the Constant Scan generated for the IF. The remainder of the query must be getting its costs rated against that.
Add this to the list of SQL Developer interviewee questions...
There are some amusing variants - for example try changing the condition:
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))SELECT * FROM sys.all_columns;
😀
Nice one, Tom - it's the simplest yet and on this server I see 27 million % cost for one of the operators :w00t:
Color me confused. Tom's code looks the same as Dwain's.
???
March 27, 2015 at 7:55 am
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.
March 27, 2015 at 7:59 am
Brandie Tarvin (3/27/2015)
ChrisM@Work (3/27/2015)
TomThomson (3/27/2015)
dwain.c (3/26/2015)
...Edit: This query shows the same thing:
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))
SELECT * FROM sys.all_columns;
Which means that it has something to do with the 100% cost calculated for the Constant Scan generated for the IF. The remainder of the query must be getting its costs rated against that.
Add this to the list of SQL Developer interviewee questions...
There are some amusing variants - for example try changing the condition:
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))SELECT * FROM sys.all_columns;
😀
Nice one, Tom - it's the simplest yet and on this server I see 27 million % cost for one of the operators :w00t:
Color me confused. Tom's code looks the same as Dwain's.
???
Hmmm. Need chocolate.
For 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
March 27, 2015 at 8:00 am
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.
For 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
March 27, 2015 at 8:09 am
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 🙁
For 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
March 27, 2015 at 8:14 am
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.
March 27, 2015 at 8:16 am
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...
Viewing 15 posts - 47,926 through 47,940 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply