January 31, 2020 at 1:25 pm
Hi,
I have used this simple code to check CPU single thread performance
DECLARE
@n numeric(16,6) = 0,
@a DATETIME,
@b DATETIME
DECLARE
@f int
SET @f = 1
SET @a = CURRENT_TIMESTAMP
WHILE @f <= 10000000
BEGIN
SET @n = @n % 999999 + sqrt(@f)
SET @f = @f + 1
END
SET @b = CURRENT_TIMESTAMP
PRINT ‘Timing = ‘ + ISNULL(CAST(DATEDIFF(MS, @a, @b)AS VARCHAR),”)
The results are very astonishing. My desctop PC (i5-8600K - about 4,1GHz effective turbo clock) needs about 6 seconds to reach the end of calculation. One of my SQL server needs about 8s (it has lower clock - 3.4GHz, on VM). The problem is with another server (2,4GHz) which has few VMs with SQL. One of them (VM) needs about 40s while another only 12s. I have chacked single thread performance too using CPU-Z but results are normal - in short, proportional to CPU clockWhat could cause (in SQL configuration? installation?) so huge SQL performans impact. I know, there is many things to check / config, but this disproportion of SQL calculation time is to big...
January 31, 2020 at 1:44 pm
Not sure I can answer your question but we used to have some SQL Server instances running on VMs, each server was running on a dedicated machine with just one VM. There was about a 20% reduction in performance running it on the VM compared to running it directly on the server.
Also, your code is testing two things, the speed of floating-point maths operations (SQRT(@f)) and the speed of iterating through the loop. Do you know if the performance of one is more affected by the machines than the other?
January 31, 2020 at 1:46 pm
as far as I can see, that looks about correct.
why are you checking CPU speed, SQL needs disk as its main resource, with RAM a close contender.
I know you used a loop to test performance , but if you have loops in you code...we you can kiss your performance goodbye.
I know this is a silly thing to ask, but did you force the MAXDOP setting for all of the tests? - did you also get the same query plan? did you drop the proc cache and the buffers on all of them first ?
only asking because these can contribute and I've been doing it for 20 years and still forget to think about recompiles 🙂
MVDBA
January 31, 2020 at 2:47 pm
Jonathan
I have not tested it separetly yet
Mike
"(...) SQL needs disk as its main resource (...)"
I know that generally but this is not a only one test. I have started collect data from hammerDB benchmark. It was a firts step whitch is abstract for me.
"I know you used a loop to test performance (...)"
Of course, but why this impact concern only one instance?
"(...)did you force the MAXDOP setting for all of the tests?(...)"
Yes, are the same. Changing it doesn't matter querung this "benchmark". Query plan? "Include Actual Execution Plan" show nothing for this query...
look at the problem this way, please:
Single hardware machine, two VMs with SQL instances. Less or more configured identical. One of them [1] with 18 cores and 200GB RAM (disks doesn't matter here), second [2] 4 cores and 16GB RAM. Both SQL's are same version, same edition, same CU but [1] calculates this same code in 40s and [2] in 12s. For sure it's normal case?
January 31, 2020 at 3:37 pm
we have to ask these questions.... you have no idea how many new DBAs don't know these things and get given a project like yours.
plus we are blindfolded from your environment
I don't quite understand a bit of your reply (I think it's a language thing, so I apologise)
"I know you used a loop to test performance (...)"
Of course, but why this impact concern only one instance?
maybe you misunderstood my comment - I meant that I saw that you used a loop to test, is this because there are lots of loops in your real code ?
MVDBA
January 31, 2020 at 4:20 pm
"maybe you misunderstood my comment - I meant that I saw that you used a loop to test, is this because there are lots of loops in your real code ?"
I understood very well and know that loops are very rare, but it's not the key if they are used or not (the code is used to check performance of single CPU thread only - synthetic test, nothing more). The point is why there are so huge differens while procesing this same (never mind it isn't similar to real OLTP code) on same hardware machine. Why one SQL instance is so different from the second.
I wonder if general performance problem I'm struggling with on this instance [1] has some connection with this observation
January 31, 2020 at 4:28 pm
February 5, 2020 at 11:03 am
There is something wrong with that instance. HammerDB results confirm the problem. On the same VM I have installed second instance of SQL and guess! No CPU performance issue and this same calculation takes less than 10s (HammerDB results are appropriate too).
I'm planning in short to backup all DBs including system DBs, uninstall default SQL instance, install again and restore DBs (for now on copy of VM on second hardware).
February 10, 2020 at 11:47 am
The problem was... Extended Events (one active session), which logged queries on one - hardly used DB. Incomprehensible...
Switching it off caused above calculation time decline from 40s to less than 12s what is visible in HammerDB TPM results too (50% more transactions per minute while middle workload).
February 10, 2020 at 12:18 pm
good of you to post us your update
you should have a quick look at Grant Fritchey's site - he despises profiler traces and loves XE
https://www.scarydba.com/2020/02/03/profiler-time-to-go/
you might pick up a few tips on using XE (even though it sounds like you didn't set this XE trace up yourself)
MVDBA
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply