My method came from this Dan Guzman answer. I found the Drop Exisiting hint didn't work so I manually dropped and re-created.

sql server - Splitting Existing Partitions in SQL 2014...

You indexing needs to be the same if you switch to new tables. I had to switch partitions  because there was a columnstore index and it had been neglected so...

Hi

Michael - sorry your systems don't have a proper maintenance window. IT architects, who don't design maintenance windows into their systems, should be taken out and shot (too much!). Just...

Hi Michael

I can only think of 2 ways defragging indexes could impact performance.

1. you're running queries at the same time as the defrag process, in which case you really need...

I'm sure there's a valid argument there Paul, but then amount of energy it'd take to convince my colleagues, plus the fact that removing defragging would be blamed for every...

Thanks for that Scott. I amazed that this isn't documented better, Microsoft document changing recovery mode before defragging indexes but no mention of this!

Thanks Grant, is there a way to configure SQL to make it more likely that the plans will remain in the cache from day to day

Hi Durai, I don't want to share the plan. I was specifically looking for, what seemed to me, to be a weird calculation of the Hash Match.

Thank you for taking...

I'm just wondering if there's something, outside of stats, that would cause the cardinality estimator, to estimate a single row output. Like table variables always have an  estimate of a...

Thanks for the reply.

This is from the estimated plan. The full plan is large, and the SP is taking hours to run, so that's why I can't  post from the...

Hi

Thanks for the reply but that is the wrong DNN. The DNN you've linked to is a CMS platform. The one I'm interested in is a VIP that can route...

Thanks for the reply Grant.

I read here, that sp_unprepare would discard the execution plan.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-unprepare-transact-sql?view=sql-server-ver16

In profiler I see an sp_unprepare run after every sp_prepexec statement won't that mean there...

Hi Ant

all those issues are more detail than  want to go into in a forum post. TBH I'm not looking for advice on whether I should do it. I'm more...