question

SeHor-8331 avatar image
0 Votes"
SeHor-8331 asked SeHor-8331 rolled back

SQL performance drop after run

I have a SQL 2014 with which I have some performance issues, mostly read/disk related. the system is VMWare/SSD SAN. Both OS and SQL have all available patches. The environment is mainly used for DSS characterized by large queries, an average of 80% read and 20% write. The data volume is 6 TB with 256 MB memory (90% allocated to SQL). I am just testing disk speeds (which proves most of the time the bottleneck) by running SELECT query with an index hint to force the queries to do a clustered index scan or table scan of 2 relatively large tables (the 2 selects will use all virtual CPUs). Testing the disk throughput shows 900 Mb/s right after VM restarts. After 1 day or so the disk throughput drops down to 300 Mb/s (I am testing only when there is no any other activity on the system for consistent results). I restarted a couple of times and same behavior.
The part that I do not understand is why there is such a drop?
Thank you

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

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

Hi @SeHor-8331, welcome to Microsoft Q&A! When throughput is 300 Mb/s, did you try to run SELECT in SQL Server and get a slow performance? Have you set auto-growth for your SQL Server? This will impace SQL Server performance.

0 Votes 0 ·

Hi CarrinWu,
Yes, I get slow performance. to benchmark I use same 2 queries running in parallel:
SELECT COUNT() FROM dbo.table1 WITH (INDEX(0));
SELECT COUNT(
) FROM dbo.table2 WITH (INDEX(0));

If i run this soon after the VM restart then in Perfmon I get disk throughput of 900 Mb/s (i measure same summing the readahead + physical reads and divide by run time, each read is 8Kb).

if i test after 1 day or some large databases maintenance (overnight or weekend) I get 300 Mb/s. After that until the next patching stays approx at 300. Right after new patching, goes up to 900, then back to 300 after a day or some activity during the first day. this is the part i do not get why there is large drop.

there is no autogrowth enabled on the files, they are preset.

Thank you

0 Votes 0 ·

1 Answer

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered SeHor-8331 rolled back

There are many, many, many variables when you are dealing with a VM. You should first start with the VM host performance. You should not test from SQL Server itself, you should be using the OS.

You should use diskspd to test your disk performance. https://docs.microsoft.com/en-us/azure-stack/hci/manage/diskspd-overview

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

Also, what you are describing is almost always related to RAM size. You need to make sure your HOST is not page swapping and the VM has dedicated RAM.

· 1
5 |1600 characters needed characters left characters exceeded

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

Thank you Tom very much for replying to my post.

"You should not test from SQL Server itself, you should be using the OS." Can you please let me know how to test from the OS?

Also "You need to make sure your HOST is not page swapping and the VM has dedicated RAM." I am able to capture the Perfmon VMWare counters, are there any one of them that will show me this? Or how else to troubleshoot?

0 Votes 0 ·