Timeline for Unexpected workload differences between SQL servers
Current License: CC BY-SA 4.0
23 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 28 at 20:22 | vote | accept | Ryan at Cloud Retailer | ||
| Mar 28 at 16:51 | answer | added | StrayCatDBA | timeline score: 0 | |
| Mar 25 at 18:47 | history | edited | Ryan at Cloud Retailer | CC BY-SA 4.0 |
added 709 characters in body
|
| Mar 25 at 18:34 | comment | added | Ryan at Cloud Retailer | I'm circling back here after a long vaca, a longer catch-up time, and more fun in-between. The winner here was @StrayCatDBA with the power setting. Simple thing, but we had to do a fair bit to replicate and test without actually recreating the problem for the customer. Once we were able to do this, we confirmed flipping the power settings from "Balanced" to "High Performance" removed the problem. If you wanted to post this to an answer I'd happily accept it! I will post a few other notes in an update to the original post. | |
| Feb 11 at 11:27 | review | Close votes | |||
| Feb 18 at 3:02 | |||||
| Jan 2 at 20:45 | comment | added | StrayCatDBA | Confirm that windows power option is set to "high performance" | |
| Jan 2 at 13:29 | comment | added | Martin Smith | You say "we don't make use of QueryStore" like this is some immutable fact. Why don't you enable it? Personally rather than wonder about what's different I'd likely just see if there are specific problematic queries that are using lots of resources on the new server - and if so you can start investigating those and either just tune them if they are doing something obviously inefficient or at least have something specific to compare with the other server | |
| Dec 31, 2023 at 13:31 | comment | added | J.D. | And to Charlieface's point regarding: "We run Ola Hallengren's amazing SQL Server Index and Statistics Maintentnce script nightly", every time you do this, the cached execution plans for those indexes get flushed from cache, and a new one is compiled the next time that query runs. This is certainly one way you can see execution plans change for a given query when seemingly nothing else has changed on the server. Even more so between two different servers, which have different characteristics. | |
| Dec 30, 2023 at 20:29 | comment | added | Charlieface | "In an Entity Framework world without full query logging, it gets messy." You can use an XEvents trace to get the long running queries, then run them in manually in SSMS "Query tuning also does not help explain the differences between servers, which is key here. I need to approach these as two distinct matters." So you say, but I've seen no evidence that it's not the case. The compiler certainly might compile differently, if statistics are slightly different etc. EF is known to often produce poor plans, and needs to be coaxed to get the right result. | |
| Dec 30, 2023 at 20:26 | comment | added | Charlieface | Side note: "We run Ola Hallengren's amazing SQL Server Index and Statistics Maintentnce script nightly, reorganizing each table at 5% fragmentation and rebuilding at 30% fragmentation AND Updating INDEX statistics." sounds like a bad idea. Fragmentation is much less of an issue on SSDs, and reorganizing is anyway mostly a pointless exercise. Wait til it's seriously fragmented and just rebuild. | |
| Dec 30, 2023 at 14:45 | comment | added | J.D. | ...even when nothing else changed but the server itself. The SQL engine is complex, there are many variables at play, and the execution plan your query is getting one day can be completely different and change to something that's regressed and worse another day, even on the same server with no apparent changes. When there's a performance issue, comparing execution plans is almost always the first place to look which will then highlight what that issue is, whether it's a performance tuning issue or at least to show you what the wait stats are so you know where to look next. | |
| Dec 30, 2023 at 14:42 | comment | added | J.D. | @RyanatCloudRetailer "In an Entity Framework world without full query logging, it gets messy." - If you're not able to enable the output of the generated SQL in EF, you can still easily capture the query itself with the SQL Server Profiler so that you can re-run it manually in SSMS and grab the execution plan. "Query tuning also does not help explain the differences between servers" - Yes, it likely does. Query regression is a very common thing that occurs when changing servers... | |
| Dec 29, 2023 at 23:36 | comment | added | David Browne - Microsoft | You can use the DEA or RML tools to capture a workload on the old server and replay it on the new server as part of the upgrade validation. learn.microsoft.com/en-us/sql/dea/… learn.microsoft.com/en-us/troubleshoot/sql/tools/… | |
| Dec 29, 2023 at 23:09 | comment | added | Ryan at Cloud Retailer | @DanGuzman I missed answering before that Compat levels are the same (150) and config is the same. | |
| Dec 29, 2023 at 23:09 | comment | added | Ryan at Cloud Retailer | @J.D. thanks for that answer. In a perfect world, that is an easy thing to do! In an Entity Framework world without full query logging, it gets messy. Query tuning also does not help explain the differences between servers, which is key here. I need to approach these as two distinct matters. I appreciate your response though! | |
| Dec 29, 2023 at 22:14 | comment | added | J.D. | @RyanatCloudRetailer "I'm afraid I don't have an easy way to validate" - Easy, take one of the queries that run slowly on the new server, and run it manually on both servers with the actual execution plan enabled in SSMS. Repeat for a couple of other key queries that you noticed performance regression on. Maybe there's a common denominator or maybe you guys follow a similar pattern in a lot of your queries that just needs to be re-written. But either way, it shouldn't be hard to get the execution plans for a couple examples at least, to confirm it's a query tuning problem. | |
| Dec 29, 2023 at 21:46 | comment | added | Ryan at Cloud Retailer | @DanGuzman I'm afraid I don't have an easy way to validate that as we don't make use of QueryStore and our monitoring tools don't capture this adequately. Comparing plans has been challenging, to say the least. What I can say is other databases with an identical (or nearly identical) structure, are not having the same problems once migrated. | |
| Dec 29, 2023 at 21:43 | comment | added | Ryan at Cloud Retailer | @Sergey, we use Ola Hallengren's scripts for this, see UPDATE 1 above for details. | |
| Dec 29, 2023 at 21:42 | history | edited | Ryan at Cloud Retailer | CC BY-SA 4.0 |
added 358 characters in body
|
| Dec 29, 2023 at 21:30 | comment | added | Dan Guzman |
Did you verify the execution plans are the same on the old/new server? Make sure the problem database compatibility level and database-scoped config (SELECT * FROM sys.database_scoped_configurations) is the same on both.
|
|
| Dec 29, 2023 at 20:33 | history | migrated | from stackoverflow.com (revisions) | ||
| Dec 29, 2023 at 20:14 | comment | added | Sergey | Could you please clarify if you execute indexes rebuild and/or sp_updatestats for this database on your new server ? | |
| Dec 29, 2023 at 20:02 | history | asked | Ryan at Cloud Retailer | CC BY-SA 4.0 |