Forum Discussion

Copper Contributor

Jul 19, 2021

Slow WHILE loop on some servers

A simple issue demonstration script below. On some servers (mostly development, physical and VMs with 2-4 cores) it is executed ~3 seconds. On other servers (mostly production VMs with 16-20 cores...

AnatoliD

Copper Contributor

Jul 20, 2021

bake13
1. Majority are 2017, but noticed on 2019 and 2014. Yes. Majority of slow servers are prod, but I have one slow and one fast both Prod (Enterprise edition) both running on the same physical ESXi.

2. This code does not touch any table and SSMS/SqlSentry does not return actual execution plan. Estimated execution plan is the same (COND + ASSIGNs)

3. Default 0 everywhere. The loop is single thread, it cannot be parallelized anyway.

bake13

Microsoft

Jul 22, 2021

Hi AnatoliD -- I spent some time working with this on various platforms (virtualized, PaaS) with various versions of SQL Server running on both Windows and Linux. I'm not really seeing any consistency or patterns with execution times. The vast majority of the time the loop executes in less than three seconds. Is this related to an application/database issue or is this more of a proof-of-concept? Take care.

AnatoliD
Copper Contributor
Jul 22, 2021
HIbake13 Thanks for looking into this.
I have performance issues with a few "applications" (a set of SPs/UDFs for data processing / transformation). It runs fine on dev with 2 cores, and not fine on prod with 16 cores.
I don't really hope it would work 8 times faster on prod, but 6 times slower - totally unacceptable.

This test is free of any other factors (disk/network IO, blocking, old statistics and not optimal execution plan, etc) which could affect performance of complex system.