question

jlj avatar image
0 Votes"
jlj asked Cathyji-msft edited

New sqlserver server performance cpu issue

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

2 Answers

TomPhillips-1744 avatar image
3 Votes"
TomPhillips-1744 answered

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

5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

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


5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.