You sure you got the question right? Looking through that list I can only see one thing that IS a benefit. Replication does not load balance. Replication doesn't distribute queries....

I am not a fan of debugging within T-SQL queries, however, if you want to do it, SQL Server Management Studio is free and it does include debugging.

The...

It's the code. I know you've said you can't affect that, but that's where the problem is. You have to use the correct data types. That's how you fix these...

I checked this table it has no FK, or triggers, and character fields are defined as Nvarchar.   There are NO indexes or a PK set of this table.

Are you...

Yeah, absolutely.

Although, I have good news. There is an open source software that does a lot of this, OpenQueryStore. I saw this when it was an in-house private bit...

I appreciate the feedback.

One reason I've used index rebuilds in the past is because some of the third-party apps we rely on can sometimes end up with a query...

Oh!

yeah, the query store data is stored with the database. If you back it up, which you are absolutely NOT doing, when you restore, you will get that data back....

Effectively a varchar(50) and a varchar(4000) are the same, so you won't see an implicit conversion there. It's the varchar to int that you're going to see the most.

All problems won't be fixed in the code, but I'd start there. Get that right, then figure out how the queries really behave. From there, you can make some good...

Query Store is not overwritten during a restore. In fact, it remains completely intact. All Query Store information is in system tables, stored with each database and will get backed...

Piling on.

Sounds like statistics are getting out of date. Focus there, not on index rebuilds (although, those will also update statistics).

ALL cases.

All of them. Statistics are what the optimizer uses to determine how many rows are likely to be returned from a given table or index. Without statistics, the optimizer...

You could provide them with PowerShell commands that run the script. Or, assuming they have a client tool installed, a batch file to run the command. I'd lean towards Powershell....

Check all the settings as suggested. Maybe an application server is misconfigured and using different ANSI settings. Look at the query_hash values to make sure they're the same. Normal behavior,...

Woof!

So, your parameters don't match the data types of the columns. I know you said you can't change the code, but it's wrong. For example, tinyint, which is what's in...