Share via

New sqlserver server performance cpu issue

jonjoseph@gmail.com 1 Reputation point
2020-08-18T16:31:15.71+00:00

I am having issues with a new sqlserver 2019 server.
It is a virtual server running on 2 cores and 6 logical cores with 64GB ram.

It seems to not be maxing out the cpus, for example when I do a rebuild of a clustered column store index on the old server, the activity monitor shows cpu usage at 97% and DB I/O at 90MB/s.
The new server only gets 50% and 20MB/s, and takes 3 times as long.
I also noticed that the cube processing sql plans are not using parallelism.
I don't know anything about VM side of things, but my sys admin says everything is fine.
Is there any settings that can be tweaked to improve the performance?
Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,340 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,721 Reputation points
    2020-08-18T18:20:17.91+00:00

    There are many variables on a VM. The fact you are only getting 20MB/s indicates you have a disk io problem, not a CPU problem.

    I suggest you read this:
    https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf

    3 people found this answer helpful.
    0 comments
  2. CathyJi-MSFT 21,126 Reputation points Microsoft Vendor
    2020-08-19T06:32:03.163+00:00

    Hi,

    Did you want to improve SQL server performance? There are many variables effect this. Please check if below blogs could help you. If i misunderstood, please let me know.

    How to Tune Microsoft SQL Server for Performance
    SQL Server Performance Tuning Tips

    If the response helped, do "Accept Answer" and upvote it.

    Best regards,
    Cathy

    0 comments