ddambra asked a question.
Has anyone upgraded from SQL Standard to Enterprise to support their Snow Platform?
Hi all,
Looking for some feedback from those who have upgraded from SQL Standard to Enterprise to support their on-premise Snow Platform based on Snow's best practice guide.
The environment in question is approx. 150,000 devices.
Analysis performed by the system administrators seemed to suggest that the SQL server is not under considerable load.
So, trying to understand what will be improved by upgrading to Enterprise and why.
If your DBA's say the SQL server isn't underload then the additional resources that can be assigned to Enterprise probably won't help.
Is your SLM portal responding slowly or your nightly Data Update Jobs running too long? what is an example DUJ run time?
The SLM portal is not very responsive, however DUJ averages 7 hours which isn't bad when the platform is only required during business hours.
Could Enterprise make better use of the available resources and improve portal responsiveness?
7 Hours for a DUJ is quite long but given you have 150k devices probably to be expected.
SQL Enterprise will remove any limitations on assigning hardware etc. to the server so you can give it TB's of memory and lots of CPU's but if your DBA says it's not using them you'll gain very little.
Most impact in performance is felt in getting the drives created correctly with fast IOPS disks.
Is the server virtualised? if so posting the specs here maybe helpful. Especially if using VMWare due to a quirk in how it handles CPU's in SQL Standard.
That SQL query confirmed all are visible..... Sorry, believe that should be 1 vCPU with 16 Cores.
Thanks Joseph.
The SQL server resides in Azure running an Intel’s Xeon Platinum 8272CL with 16 vCPUs, 128GB RAM and 6 x P40 2TB drives (7,500 IOPS / 250MB/Sec)
It shouldn't be underspec'd then but one thing to have your DBA check is, is SQL standard using all your vCPU's as it does have a 4 vCPU or 24 Core limit:
select scheduler_id, cpu_id, status, is_online
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'
Will show if all are online and able to be used by SQL. If not then You'll need to go to Enterprise to use the rest of those cores.
We had performance issues in a customer's 30.000 devices environment, databases resided on a massive SQL cluster.
At the end of the day, the disks were the key to better performance, as Joe already pointed out.
Doing so, we reduced searching for an application from 8 minutes to 75 seconds.