Jeff Moden

Forum Replies Created

Viewing 15 posts - 4,636 through 4,650 (of 59,069 total)

Reply To: Good query plan gone bad - can I pinpoint a cause with no repro?

p.s. If there's a DISTINCT or a GROUP BY that you're using like a DISTINCT, you really need to fix that. Really. And identifying the "core" data and using "Divide'n'Conquer"...
--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)

September 15, 2021 at 2:38 am

#3929654
Reply To: Good query plan gone bad - can I pinpoint a cause with no repro?

You say "On some occasions, again all under the same execution plan, poor performance occurs " but the execution plans you posted aren't even close to being the same.
The culprit...
--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)

September 15, 2021 at 2:36 am

#3929653
Reply To: Exclude rows where value used in another row

AND, looking a bit closer, my code doesn't work correctly using the following data but the mod to pietlinden's code does, so use his code with the mod.
Here's the code...
--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)

September 15, 2021 at 1:28 am

#3929648
Reply To: Exclude rows where value used in another row

Looking a bit closer at the code, there' may be a fly in the ointment of pietlinden's code. Give the following a run and see.
[EDIT] I DISCOVERED THE CODE...
--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)

September 15, 2021 at 1:04 am

#3929647
Reply To: Exclude rows where value used in another row
MattNorman88 wrote:
Hi Phil,
Thanks for the reply.
The create/ insert would be as follows:
```
CREATE TABLE CustomerPolicies
(
[Mobile] NVARCHAR(20),
[WorkTelephone] NVARCHAR(20),
[HomeTelephone] NVARCHAR(20),
[FirstName] NVARCHAR(30),
[LastName] NVARCHAR(30),
[PolicyNumber] INT
)

INSERT INTO CustomerPolicies VALUES ('07972234564', 'N/A', 'N/A', 'Jon',...
```
--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)
September 15, 2021 at 12:40 am

#3929644
Reply To:

Grant Fritchey wrote:
It's all Steve Jones. His idea. His execution. He deserves all the credit.
Not to mention being the prime driver of this whole site! +1 Billion to Steve!
--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)

September 15, 2021 at 12:16 am

#3929643
Reply To: Generate Monthly Transactions between Start and End Dates

@rjjh78,
Did any of those solutions help you or is there something else that you need help with on this problem?
--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)

September 15, 2021 at 12:12 am

#3929639
Reply To: always backup database with encryption

BWAAAA-HAAAA-HAAAA!!! I've found that "Penalty of Death" will sometimes work! 😀
I agree with Ant-Green's statement. All of our data is encrypted and that means everything and not just SQL Server...
--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)

September 15, 2021 at 12:08 am

#3929637
Reply To: Implicit conversion on hash key column is causes very slow insert

frederico_fonseca wrote:
@jeff - while concat_ws deals well with nulls on this case it could also cause a collision on a case where one row has null vs one with ''...
--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)

September 14, 2021 at 11:53 pm

#3929631
Reply To: Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents

Solomon Rutzky wrote:
P.S. I will also be presenting on the topic of Module Signing at the upcoming PASS Data Community Summit 2021: Module Signing: Use Certificates to...
--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)

September 14, 2021 at 10:50 pm

#3929606
Reply To: Backup has stopped working

Interesting. Thank you for taking the time to post the feedback.
Out of curiosity, when is the last time the SQL Server Service was restarted?
--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)

September 14, 2021 at 10:36 pm

#3929604
Reply To: SPIDs not terminating

Steve,
I'm pretty sure that most folks will recommend against the use of the Lightweight Pooling option for most things. The reason I mentioned it was to make sure it wasn't...
--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)

September 14, 2021 at 12:39 am

#3929264
Reply To: Implicit conversion on hash key column is causes very slow insert

I agree with Brian. The code you posted doesn't even contain the "product_month" column and so I also wonder if you have the correct code posted or if you've just...
--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)

September 13, 2021 at 11:17 pm

#3929249
Reply To: Execute sql task behave differently when run in isolation versus part of package

It could be a settings difference between VS and SSIS. For example, VS could be using connections that use one type of setting for ANSI NULLs and SSIS could be...
--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)

September 13, 2021 at 10:27 pm

#3929246
Reply To: Backup has stopped working

Been there and done that. Totally understood.
--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)

September 13, 2021 at 4:58 pm

#3929166

Viewing 15 posts - 4,636 through 4,650 (of 59,069 total)