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

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

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

Almost without exception,...

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

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

You REALLY need to change that ASAP.

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

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

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

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.

            
February 16, 2023 at 10:47 pm
            
#4150800

            
            
                                                
        

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

.

February 16, 2023 at 10:33 pm

#4150799

I found an alternative to my specific problem. I used the identity to also sort the data and had another column that I could use to sort the data....

FWIW, Jeff, it would be great to see a list of common SQL problems that people ought to be able to solve. I'd add in

• running totals
• deserializing...

Ah... now I understand.  Heh... I thought "WTH? ...