Jeff Moden

Forum Replies Created

Viewing 15 posts - 1,891 through 1,905 (of 59,091 total)

Reply To: Query performing slow in SQL 2016 with compat level (130)

Based on the Compatibility Levels you've posted, there's a really good chance that this is because of a fairly major change they made to the "Cardinality Estimator" (CE for short)...
--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)

February 17, 2023 at 6:29 pm

#4151151
Reply To: How to calculate idle times for each session

Ah... totally got it. Thank you for the feedback.
Understood on the 3 second SLA but that doesn't seem to be the predominant issue. The 1 or 2 day sleeping spids...
--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)

February 17, 2023 at 5:41 pm

#4151125
Reply To: Azure SQL

PJ_SQL wrote:
Thank you, I think the other factor is that there are more than 100 tables that have dependency on this table, so the delete might be looking into all...
--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)

February 17, 2023 at 5:19 pm

#4151117
Reply To: shrink datafile in aoag?

I can't help on AOAG because I've never had to use it. Once someone helps you figure that out, I can give you a very strong hint on how 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)

February 17, 2023 at 4:30 pm

#4151086
Reply To: How to calculate idle times for each session

vsamantha35 wrote:
We are seeing some stale connections open for long duration and causing blocking chains. My question is how can track/calculate the idle time for each user session.
Almost without exception,...
--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)

February 17, 2023 at 4:09 pm

#4151079
Reply To: Alternative to row_number in natively compiled stored procedure

MullerN wrote:
Jeff, when done right, natively compiled stored procedure achieve incredible performance gains. They are compiled software (DLLs), much like C++ code. The problem is that there are many limitations...
--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)

February 17, 2023 at 3:39 pm

#4151075
Reply To: Generate Script for all Triggers on a Table?

chandrakant_gaurav wrote:
You can get it from system views:
Example:
select
so.name, st.name, sc.text
from sys.triggers st inner join sysobjects so on st.parent_id = SO.id inner join syscomments sc on st.object_id = sc.id where so.name...
--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)

February 17, 2023 at 3:35 pm

#4151074
Reply To: Agent Job History is missing

Arsh wrote:
The setting is 1000 and 100 respectively.
You REALLY need to change that ASAP.
--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)

February 17, 2023 at 3:32 pm

#4151073
Reply To: Cascading Parameters not Working with Date Parameter

Not sure how I can help. The earlier level of code is obviously not producing code that will discriminate against facilities. There's nothing in the code you posted that will...
--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)

February 17, 2023 at 4:37 am

#4150869
Reply To: Cascading Parameters not Working with Date Parameter
On the question about the facility parameter, I was basing that off your comment in your original post which says...
```
"... when I select only 1 facility, it...
```
--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)
February 16, 2023 at 11:45 pm

#4150808
Reply To: Strange behaviour when changing SP name

It's a fairly common occurrence. Please see the following article.
https://www.sommarskog.se/query-plan-mysteries.html
And I totally agree with Ed B's recommendation about the use of sp_ and I'll extend that to the general use...
--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)

February 16, 2023 at 10:54 pm

#4150802

Reply To: Cascading Parameters not Working with Date Parameter

First of all, the last line of that code needs some "bullet-proofing" to prevent missing most of the entire last day of the month if times are included.

--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)

February 16, 2023 at 10:47 pm

#4150800

Reply To: What is ChatGPT – Is it the rise of the Skynet era?

Good grief. It just keeps getting worse. Even 99.9% logical fragmentation has absolutely no effect on index SEEKs but ChatGPT would have you believe the opposite is true

--Jeff Moden