Query /SP running VERY slow on Sqlserver Replicated DB but fine on publisher and test databases

Seeberg, Paul (EHS) 21

Running sqlserver 2017 on 3 servers, Prod, Test, Replication. All are identical. Set up identically. Prod replicates (transactional) to (shockingly) Replication. Test is standalone . The replication process runs very good.

We are having 2 SPs (and the query within when run by itself) run in 25 seconds on Production and Test. We kill the query after 20 minutes on the replication database. Rebooted countless times. Undated statistics, rebuilt indexes and such.

Me (DBA) and my team, all walked through the Article properties to ensure all the indexes and constraints and everything got into the snapshot. We can see everything on the Replication database. The database runs great except for these 2 SPs (well the query within). It is hitting some very large tables and is relatively complicated. With both Prod and REP effectively being the same, we are bashing our heads trying to figure out why the performance difference.

As a test, I copied the PROD data to TEST.. and it performed exactly the same, within a few seconds, of PROD.

None of the servers are showing us running out of memory or CPU on the windows server (2012 R2)

Any idea what I can look at, what settings or something that would make the replication database run differently than the publisher? Since i can move the db to other databases and get same results as prod, there must me something with the replication that is missing or wrong.

Any thoughts ?

Shivam Kumar 541 Reputation points

2022-09-28T19:10:25.477+00:00

Hi,
Check for any difference in Compatibility Level setting on both sql servers.
See if there are some implicit type conversions happening.
Run procedure queries with option (recompile) and see if you still observe the problem if not it may be due to a bad execution plan for some specific parameter (we will need to look at the execution plan or will have to see query or know what it is doing to understand the problem and give solution accordingly)
how are you making sure its not network latency ? any logic set for testing replication latency ?
Seeberg, Paul (EHS) 21 Reputation points

2022-09-28T19:29:41.8+00:00

Thanks for the reply

I will run the SP with recompile and check.. will check compatibility as well.

I don't remember selecting any type conversion when setting the parameters for the articles. How can I tell if there is any type conversion being done ?

Since the query/SP is the same on all the servers, I would assume the execution plan would be the same. I am relatively new to sqlserver, how do we see the execution plan ?

I created a new snapshot due to a lot of changes on production. Once it was on the REP server, the users reported the issue. I made yet another snapshot , rebooted, and still had the issue. We did this well after working hours 10pm, so there were no new transactions. I just use the replication manager to see the performance, and have an alert if there is any latency.

Thanks!
Shivam Kumar 541 Reputation points

2022-09-28T19:49:59.89+00:00

You will see type conversion warnings in execution plan if its happening, mostly happens when you are comparing two different data type columns for example.
Execution plan can be seen using CTRL+M in ssms there is also option in one of the menu bar to include it, click on it before running the procedure.
Plus just in case you are doing large number of inserts or deletes do it in chunks.
Even if your sql servers have same configuration, what about memory cpu etc on OS that they are running on top of is it same too?
Also check for any blocking and waits on server during the slowness is observed you can use sp_who2 and look for blkby column.
Erland Sommarskog 101.6K Reputation points MVP

2022-09-28T22:04:18.703+00:00

Since the query/SP is the same on all the servers, I would assume the execution plan would be the same

No, that does not have to be the case. There are many thing that affects optimisation, including hardware configuration.

We would need to see the query plans to get some understanding about this. Preferably we would like to see the actual query plan, with the run-time values as that reveals any mis-estimates. But since the query does not complete, we have to do this a little different.

For the fast version, select Include Actual Execution Plan from the Query menu. When you have the plan, select Save AS XMl from the context menu.

For the slow version, instead select Live Query Statistics from the Query meny. Let the query run for five minutes or so before you give up. Again, save the xml.

Then you can upload the plans on http://www.pastetheplan.com and post the links here.
YufeiShao-msft 7,061 Reputation points

2022-09-30T01:09:18.35+00:00

Could you please validate all the answers so far and provide any update?
Please do not forget to share anything useful

Regards
Seeberg, Paul (EHS) 21 Reputation points

2022-10-03T12:42:46.063+00:00

I was off for a few days, I will update this with more information.

The servers are virtual servers spun up similarly. The REP server has 32g memory, where our TEST has 12g memory (and it runs faster) . Both have same CPU, software installed identically . The only significant difference is the REP is being replicated to, and TEST was backed up/restored .
Seeberg, Paul (EHS) 21 Reputation points

2022-10-03T17:17:08.907+00:00

Here are the plans

Here is the slow query from the replication server (10 minutes)

https://www.brentozar.com/pastetheplan/?id=rJSOCddzj

Here is the query on the publisher (25 seconds)
https://www.brentozar.com/pastetheplan/?id=Bk8Pdc_Mo
Seeberg, Paul (EHS) 21 Reputation points

2022-10-03T17:29:05.127+00:00

Here are the plans

Here is the slow query from the replication server (10 minutes)

https://www.brentozar.com/pastetheplan/?id=rJSOCddzj

Here is the query on the publisher (25 seconds)
https://www.brentozar.com/pastetheplan/?id=Bk8Pdc_Mo
Shivam Kumar 541 Reputation points

2022-10-03T20:37:11.347+00:00

Hi @Seeberg, Paul (EHS)

I see on slow server the plan is reporting a missing index for this query while on the other server where its fast its not, can you make sure you have same indexes on both Publisher and Subscriber(Replications server).

Missing index which is being reported on replication server :
Missing Index (Impact 45.2337): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[BtBAudit] ([LabTestID],[DiseaseTestID])
Shivam Kumar 541 Reputation points

2022-10-03T21:08:15.47+00:00
There are also high cost sort operations being reported in the plans you might want to optimize your order by and try not to use star but the column names that you actually need during select.

SELECT DISTINCT * FROM #c_rpt1 ORDER BY RecievedDate, Provider, TestName, LabNumber

The part that you need to look into is Hash join where in one plan the estimated no of rows were just 3209.55 and actual rows returned are 2867 while in the other plan Actual Number of Rows returned were same 2867 but sql server Estimated Number of Rows to be 115393.

You need to update stats for index IX_LabTest for [BTBLEAD].[dbo].[LabTest].[IX_LabTest].[L] on slow server it seems they are outdated and thus sql server Estimated different Number of Rows ( 174605 and 2787.06 )on both servers

In this plan look for hash join with small yellow warning sign when you hover or click on it you will see the details:
https://www.brentozar.com/pastetheplan/?id=Bk8Pdc_Mo
Seeberg, Paul (EHS) 21 Reputation points

2022-10-03T21:11:35.983+00:00
Thank you for the reply.

I did see that too. I did check. Both have the same index (for a pk) on it. Not, however, the way it is described in the report, on either publisher or subscriber.

Perhaps adding an index like that will help? Can we add the index to the subscriber without breaking anything ?

The following is identical on both servers for that table. the only index is for the PK:

/****** Object: Index [PK__BtBAudit__3D3FBAEF] Script Date: 10/3/2022 5:05:47 PM ******/ ALTER TABLE [dbo].[BtBAudit] ADD CONSTRAINT [PK__BtBAudit__3D3FBAEF] PRIMARY KEY CLUSTERED ( [AuditID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Shivam Kumar 541 Reputation points

2022-10-03T22:29:06.617+00:00

Hi @Seeberg, Paul (EHS)

As confirmed by @Erland Sommarskog and was also requested earlier check for difference in compatibility level on both server for this database.
Go to ssms right click on this database > options > compatibility level
It effects the query optimizer cardinality estimator and thus query plans.

You can create non clustered index without breaking replication but I would not recommend doing it on production without testing other performance issues on test environment that might occur as a result of fixing one problem.
Seeberg, Paul (EHS) 21 Reputation points

2022-10-04T13:12:55.567+00:00

Thank you.

The compatibility level is the same, 140.

I will run it by the team to create the index that was suggested...and update the stats for IX_LabTest..and have them remove the * in the query.

I do appreciate the help!
Seeberg, Paul (EHS) 21 Reputation points

2022-10-04T13:17:14.517+00:00

Thank you for the help..

I will look at adding that index, and update the stats.. and remove the * from the query.
Seeberg, Paul (EHS) 21 Reputation points

2022-10-04T13:18:04.907+00:00

Thank you for the help..

I will look at adding that index, and update the stats.. and remove the * from the query.
Seeberg, Paul (EHS) 21 Reputation points

2022-10-04T13:25:12.137+00:00

Thank you for the help..

I will look at adding that index, and update the stats.. and remove the * from the query.
Erland Sommarskog 101.6K Reputation points MVP

2022-10-05T10:47:56.183+00:00

The compatibility level is the same, 140.

There are more ways to control the cardinality estimator. Run DBCC TRACESTATUS on both machines to compare. Also check sys.database_scoped_configurations and compare.

Did you try the optimizer hint I suggested?

Accepted answer

Erland Sommarskog 101.6K Reputation points MVP

2022-10-03T21:28:50.83+00:00
A general comment is that this query is need of a major overhaul. There are all sorts of things in this query that can act as go-slower button. Unfortunately, I think the work for that rewrite goes beyond what you can expect from a forum. One tip, though, is to insert the result from these string-splitting operations into a temp table.

However, sometimes you may be lucky, and that seems to be the case on the subscriber. To wit, I found a configuration difference. In the slow plan, the optimizer has CardinalityEstimationModelVersion="140", whereas the fast plan has CardinalityEstimationModelVersion="70". Microsoft rewrote the cardinality estimator in SQL 2014 and has refined it since. Often to the better, but as often with optimization, it can sometimes backfire. Not the least with a query that was not good from the start, but where you were lucky, so you need saw any need for tuning.

To test this hypothesis, add this hint to the end of the query on the slow server:

OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110'))

The cardinality estimator can also be controlled by a database-scoped configuration or the compatibility level. I would guess that the subscriber has compatibility level < 120.
Please sign in to rate this answer.
Seeberg, Paul (EHS) 21 Reputation points

2022-10-04T13:25:02.78+00:00

Thank you, I will look at that setting.

My main intent with the original question is , I expected the subscriber to be identical as the publisher, set everything up the same...guess that was not the case. I don't expect a rewrite (I didnt write this query! I am just the dba) , just if there was some known issue , setting or something, with a replicated db that I was overlooking.

But will look at adding that hint!

Erland Sommarskog 101.6K Reputation points MVP

2022-10-04T21:10:02.663+00:00

My main intent with the original question is , I expected the subscriber to be identical as the publisher, set everything up the same...guess that was not the case

Some general notes. One of the features with replication is that the databases do not have to be identical. Replication permits you to replicate only some of the tables, and you can also have filter to only replicate parts of the table.

When it comes to indexes, non-clustered indexes are not replicated by default (which I learnt myself just a couple of days back from this forum!). The idea is that you may want a different set of indexes in the subscriber. The publisher may be an OLTP database, where you don't want a lot of indexes that slows down updates. Whereas the subscriber may be a reporting database, where you want a lot more indexes.

But if even the databases are exact copies of each other, there can still be differences in the execution plan. In this case, the compatibility level is the main suspect. But you mentioned that the two machines have a different amount of RAM. The optimizer considers how much memory that is available, and with some bad luck you can get worse performance when you have more memory! (Because the optimizer thinks that it can use a plan that needs more memory, but the optimizer underestimates exactly how much memory, and the execution spills to disk.)

Shivam Kumar 541 Reputation points

2022-10-05T12:11:37.687+00:00

Example of bad luck:
Like wrong estimation of number of rows(~2k) will make sql server think it needs less memory to run the query when in actual it needed memory to process lot more rows (>150k rows) as can be observed happening in execution plan.

Also non clustered indexes on replication server can be different yes but as transactions are still coming from Publisher and getting reapplied to Subscribers we still need to keep things in check on tables which are replicated when it comes to number of indexes etc, on the tables which are not replicated we do have more flexibility on creation of indexes.

Seeberg, Paul (EHS) 21 Reputation points

2022-10-05T12:51:54.797+00:00

.
We might have a winner!

Changed the compatibility of the subscriber to match the publisher, and the query completed in a more appropriate time (about 30 seconds)

We have released it to the business to try and waiting to hear back to confirm the fix.

Seeberg, Paul (EHS) 21 Reputation points

2022-10-05T13:00:10.137+00:00

When it comes to indexes, non-clustered indexes are not replicated by default (which I learnt myself just a couple of days back from this forum!).

Yup found that out too! I often do forget about that when replicating something new. Not something I do regularly!

But yes.. I do see the case where the subscriber and publisher could have different indexes .. We want to keep them the same just in case we need to fall back to the publisher in case the replicator messes up. The team still does not have full confidence in the replication process! This issue does not help with their confidence!

That is why we replicated to yet another server as a test, to see if the server was the issue, or replication.

But as I mentioned in my reply a minute ago, looks like changing the compatibility level was the fix

Shivam Kumar 541 Reputation points

2022-10-05T13:08:00.18+00:00

Finally :) Thank you for confirming and the update, it helps.

Seeberg, Paul (EHS) 21 Reputation points

2022-10-05T13:12:14.44+00:00

Thanks, this was incredibly helpful.

You guys are all rock stars!

I am primarily an Oracle DBA (since Oracle 6 on VAX/VMS!) , sqlserver is just different enough to throw me some significant challenges!

Erland Sommarskog 101.6K Reputation points MVP

2022-10-05T21:12:15.113+00:00

Yes, SQL Server and Oracle are certainly not xerox copies of each other! I have never worked with Oracle, but if I would have to I would sure feel like a beginner again!

(And, oh, you mention VAX/VMS - a wave of nostalgia sweeps over my mind. VMS was my first love in the computer world.)
Sign in to comment

1 additional answer

YufeiShao-msft 7,061 Reputation points

2022-09-29T08:35:46.51+00:00

Hi @Seeberg, Paul (EHS) ,

There are many factors to SQL Server that can cause the same query to perform differently across different environment
The more common, the hardware, OS, the version of SQL Server, CU or SP, compatibility, the amount of data and execution plan and so on , one of which can result in very different query plans and performance

You can choose to monitor the query or sp to trace the cause of the problem
From this article, you can choose some ways like SQL monitor, review the query plan and query execution statistics to find the expensive queries
https://www.red-gate.com/hub/product-learning/sql-monitor/why-is-my-query-suddenly-slow

Troubleshoot a query that shows a significant performance difference between two servers

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment