Are the posted questions getting worse?
- Jeff Moden wrote:
I'm having an issue on 2016 and 2017... If you have 2019 or any version prior to 2016, would you run the following code, please, and let me know the version you ran it on and post the results? Thanks for the help, folks.
SELECT A = 2147483647/1000
,B = 2147483648/1000
;
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - Jeff Moden wrote:
It's just my opinion but that violates the rules of integer division
Why would you expect the rules of integer division to apply when the input data are not integers?
You do not specify at all, in any way, the data types. There are no variables. There is no CAST or CONVERT. So you are relying on SQL Server to infer a data type.
And somehow you even seem to expect that the infered datatype for the constant 2147483648 would be integer ... which would make the query fail because this value exceeds the integer range. Obviously the internal logic to infer data type from a constant is smarter than that.
For constant values up to 2147483647 the inferred data type is int. For 2147483648 and up, it is decimal(9,0). And for decimal, obviously, the rules of integer division do not apply.
SELECT 2147483647 AS a, 2147483648 AS b
INTO TestTableInfer;
GO
EXEC sp_help TestTableInfer;
GO
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/ -
This is interesting:
DECLARE @X NVARCHAR(MAX) = N'DECLARE @SQLSTR NVARCHAR(MAX)
;with xdata as
(
SELECT
A = 2147483647/1000
,B = 2147483648/1000
,C = 2147483647.0/1000
,D = 2147483648.0/1000
)
SELECT * FROM xdata;
'
SELECT *
FROM sys.dm_exec_describe_first_result_set(@X,NULL,NULL);😎
is_hidden column_ordinal name is_nullable system_type_id system_type_name
--------- -------------- ------ ----------- -------------- -----------------
0 1 A 1 56 int
0 2 B 1 108 numeric(16,6)
0 3 C 1 108 numeric(16,6)
0 4 D 1 108 numeric(16,6) - Michael L John wrote:Jeff Moden wrote:
I'm having an issue on 2016 and 2017... If you have 2019 or any version prior to 2016, would you run the following code, please, and let me know the version you ran it on and post the results? Thanks for the help, folks.
SELECT A = 2147483647/1000
,B = 2147483648/1000
;Oh my! Thank you, Michael!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Hugo Kornelis wrote:Jeff Moden wrote:
It's just my opinion but that violates the rules of integer division
Why would you expect the rules of integer division to apply when the input data are not integers?
You do not specify at all, in any way, the data types. There are no variables. There is no CAST or CONVERT. So you are relying on SQL Server to infer a data type.
And somehow you even seem to expect that the infered datatype for the constant 2147483648 would be integer ... which would make the query fail because this value exceeds the integer range. Obviously the internal logic to infer data type from a constant is smarter than that.
For constant values up to 2147483647 the inferred data type is int. For 2147483648 and up, it is decimal(9,0). And for decimal, obviously, the rules of integer division do not apply.
SELECT 2147483647 AS a, 2147483648 AS b INTO TestTableInfer; GO EXEC sp_help TestTableInfer; GO
First, because the Modulus operator does recognize them as integers.
Second, because the second formula of the first example will fit a BIGINT and should be treated as such, IMHO.
Third, because the MS documentation on constants says an integer is a list of digits with no decimal point with no qualification as to size.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Grant Fritchey wrote:
Question for the smart people, and the rest of you.
Would you endorse or get behind the statement: Some deadlocks are healthy. or Some level of deadlocks is healthy.
Or is more something like: Some level of deadlocks we can live with.
I'm just surprised to hear deadlocks described as "healthy" in any terms. I'd argue, in a perfect world, any deadlock is bad. I simply don't see a place where using the word "healthy" to describe deadlocks is accurate. Certainly, there's a level below which they can be tolerated, and there's a level above which they're a major issue. That level is very much up to interpretation.
What do you think?
I know the risk of absolutes, but I really don't think it matters in this case. Hell no, deadlocks are NOT healthy!!! Some level being lived with...it depends, but it's still not healthy. Ever.
And now, I'll kick back and wait for Jeff "King of the Edge Cases" Moden to ride in and tell me what I forgot. 😉
- Ed Wagner wrote:Grant Fritchey wrote:
Question for the smart people, and the rest of you.
Would you endorse or get behind the statement: Some deadlocks are healthy. or Some level of deadlocks is healthy.
Or is more something like: Some level of deadlocks we can live with.
I'm just surprised to hear deadlocks described as "healthy" in any terms. I'd argue, in a perfect world, any deadlock is bad. I simply don't see a place where using the word "healthy" to describe deadlocks is accurate. Certainly, there's a level below which they can be tolerated, and there's a level above which they're a major issue. That level is very much up to interpretation.
What do you think?
I know the risk of absolutes, but I really don't think it matters in this case. Hell no, deadlocks are NOT healthy!!! Some level being lived with...it depends, but it's still not healthy. Ever.
And now, I'll kick back and wait for Jeff "King of the Edge Cases" Moden to ride in and tell me what I forgot. 😉
I remember a DBA that I worked with in a previous company He was predominately an Oracle DBA that had become the accidental DBA for the SQL Servers.
We had solved the case of having more than 700 deadlocks per day with spikes to over 4000 in a day all because of one little item to "get the next ID". We fixed it and got it so that little bit of code no longer produced deadlocks, ever. There were 12 deadlocks, but none of them were coming from what we had fixed.
To celebrate, he and I went to "Roosevelt's" (a local restaurant with a good bar) that evening. We had a nice dinner and moved to the bar and we discussed the problem, again. Just when I thought the discussion on that subject had ended, he looked at me with one of his patent-able one-eyed quizzical looks and then looked down as if disgusted and shook his head. Of course, I asked him what he was thinking.
He looked at me and said, "It's absolutely amazing what has happened. Here we are, actually celebrating having ONLY 12 deadlocks and being proud of our accomplishment". "And?", I asked. He said, "In the Oracle world, we'd have stopped production if there was just one for the day and wouldn't stop until it was fixed".
I'm going to disappoint Ed by not having an "edge case" to point out here. Every deadlock has at least one loser and that's not really acceptable. Difficult to attain unless you do something like turning on Snapshot Isolation but that may also be the reason why some people think that "some deadlock are healthy". They're not and never have been. People that say such a thing are either numb or dumb. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hey all!
Back from sabbatical. Happy to see we're still talking deadlocks. Ha!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Jeff Moden wrote:Ed Wagner wrote:Grant Fritchey wrote:
Question for the smart people, and the rest of you.
Would you endorse or get behind the statement: Some deadlocks are healthy. or Some level of deadlocks is healthy.
Or is more something like: Some level of deadlocks we can live with.
I'm just surprised to hear deadlocks described as "healthy" in any terms. I'd argue, in a perfect world, any deadlock is bad. I simply don't see a place where using the word "healthy" to describe deadlocks is accurate. Certainly, there's a level below which they can be tolerated, and there's a level above which they're a major issue. That level is very much up to interpretation.
What do you think?
I know the risk of absolutes, but I really don't think it matters in this case. Hell no, deadlocks are NOT healthy!!! Some level being lived with...it depends, but it's still not healthy. Ever.
And now, I'll kick back and wait for Jeff "King of the Edge Cases" Moden to ride in and tell me what I forgot. 😉
I remember a DBA that I worked with in a previous company He was predominately an Oracle DBA that had become the accidental DBA for the SQL Servers.
We had solved the case of having more than 700 deadlocks per day with spikes to over 4000 in a day all because of one little item to "get the next ID". We fixed it and got it so that little bit of code no longer produced deadlocks, ever. There were 12 deadlocks, but none of them were coming from what we had fixed.
To celebrate, he and I went to "Roosevelt's" (a local restaurant with a good bar) that evening. We had a nice dinner and moved to the bar and we discussed the problem, again. Just when I thought the discussion on that subject had ended, he looked at me with one of his patent-able one-eyed quizzical looks and then looked down as if disgusted and shook his head. Of course, I asked him what he was thinking.
He looked at me and said, "It's absolutely amazing what has happened. Here we are, actually celebrating having ONLY 12 deadlocks and being proud of our accomplishment". "And?", I asked. He said, "In the Oracle world, we'd have stopped production if there was just one for the day and wouldn't stop until it was fixed".
I'm going to disappoint Ed by not having an "edge case" to point out here. Every deadlock has at least one loser and that's not really acceptable. Difficult to attain unless you do something like turning on Snapshot Isolation but that may also be the reason why some people think that "some deadlock are healthy". They're not and never have been. People that say such a thing are either numb or dumb. 😀
When I read this, I don't know whether to smile or tear my hair off, although the latter would be a futile exercise as I got none left.
The edge cases are there but not in the way most of us think!
😎
Few years back I fixed a deadlock issue that was causing some massive performance degradation, data corruption and other issues. Turned out that part of the business logic was using a deadlock as a valid request response, so by fixing the deadlock issue I broke the business logick.
Goes without saying that my approach is that from a database perspective, there is no such thing as "healthy deadlocks", the point is that what is healthy for one part of a system may be lethal for an other part of the same system 😉
-
@Eirikur...
I guess that I'd hunt down the people that designed that "edge case" and introduce them to pork chop dinners in a major way.
Heh... reminds me of the battles I had with Developers about sp_GetAppLock. Lordy.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
@Eirikur...
I guess that I'd hunt down the people that designed that "edge case" and introduce them to pork chop dinners in a major way.
Heh... reminds me of the battles I had with Developers about sp_GetAppLock. Lordy.
I've got a secret weapon that is much more potent than a pork chop for the same purpose, an Icelandic delicacy that is a few hundred years old rotten sharks, commonly served frozen but for added potency, it can be brought up to room temperature (in a controlled environment)
😎
Goes without saying that in the before-mentioned situation, I was the "bad guy", although the deadlocks were more expensive than the business logic branches relying on those. This was a SaaS environment and catering for deadlocks and other bottlenecks were costing more than 75% of the cloud tenant's costs. Multiply that with few thousands and there will be a big number there 😉
-
I think all deadlocks are good. Job security.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - Michael L John wrote:
I think all deadlocks are good. Job security.
Job security is a very flexible term 😉
😎
- Eirikur Eiriksson wrote:Jeff Moden wrote:
@Eirikur...
I guess that I'd hunt down the people that designed that "edge case" and introduce them to pork chop dinners in a major way.
Heh... reminds me of the battles I had with Developers about sp_GetAppLock. Lordy.
I've got a secret weapon that is much more potent than a pork chop for the same purpose, an Icelandic delicacy that is a few hundred years old rotten sharks, commonly served frozen but for added potency, it can be brought up to room temperature (in a controlled environment) 😎 Goes without saying that in the before-mentioned situation, I was the "bad guy", although the deadlocks were more expensive than the business logic branches relying on those. This was a SaaS environment and catering for deadlocks and other bottlenecks were costing more than 75% of the cloud tenant's costs. Multiply that with few thousands and there will be a big number there 😉
I have a saying that I borrowed from the Submarine Service about things like that... "Don't worry about the fire... the flooding will put it out". 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 66,031 through 66,045 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply