In my case, the culprit ended up being a popular SQL Server third party monitoring solution (I will decline to name the vendor). Expensive monitoring configured within SQL Server including certain extended events, trace flags, profiling, or traces.
VMware’s Enhanced vMotion Compatibility (EVC) – force a CPU to run on an older instruction set for compatibility reasons.Enabling hyperthreading (CPU reporting within SQL server doesn’t have the same meaning).OS patching, especially side channel vulnerability fixes.Power plan and other OS configuration settings.Running down the root cause of the issue can be difficult and time consuming because there are a lot of possible causes. With that said, it’s definitely suspicious and not something that you want to unexpectedly see. It does not necessarily mean that there’s some kind of severe issue with the server that takes more CPU time to perform the same work. To be clear, the only thing that you can safely conclude in this kind of situation is that there’s some kind of unknown configuration difference between the two servers. I definitely wouldn’t expect an 8X difference for two servers that were supposed to have identical performance! There are of course a lot of factors in play here, but I would expect a healthy machine to take somewhere around 200-800 ms of CPU work. The first server that I tested took about 2400 CPU ms to execute the code. Otherwise, there would be no reason to write a blog post. Going back to the intro, you can probably guess that I did not see identical CPU times when comparing the two servers. Paul White explains why the ASYNC_NETWORK_IO waits appear when the same code is run as part of a batch in this stack exchange answer. A temporary stored procedure is used to avoid ASYNC_NETWORK_IO waits. It’s designed to spend as little time on waits and latches as possible. The code is extremely efficient (from a certain point of view) and CPU time will closely match elapsed time as as long as there’s no other processes on the lucky CPU that runs my beautiful code. SELECT cpu_time, DATEDIFF(MILLISECOND, SYSUTCDATETIME()) elapsed_time To get a single CPU core to 100% within SQL Server with minimal setup, the best method known to me uses a temporary procedure that does a simple operation within a WHILE loop: CREATE OR ALTER PROCEDURE #p ASĭECLARE BIGINT = 0, DATETIME2 = SYSUTCDATETIME() Yesterday, I was doing a sort of basic health check on a few servers that I hadn’t worked with much and I wanted to verify that they got the same performance for a very simple query. Sometimes I get a spider sense while working with virtualized SQL Server instances and want to check for problems. For example, I might be switching from old hardware to new hardware and I want to immediately see a faster query to know that I got my money’s worth. Sometimes I have a need to run a quick CPU comparison test between two different SQL Server instances.
0 kommentar(er)
