Would upgrading SAS HDDs to SATA SSDs for MSSQL help in this case?

[Levent]

Greetings,

I am currently managing company servers as company website is not having a good time handling particular data heavy queries. I have profiled website backend and came to the conclusion that it is the queries that are slowing it down, I also confirmed with tracing all SQL queries last 2 weeks and issue is queries to one table take around 100 times longer (CPU time isnt abnormally high) and sadly we cannot reduce or divide query with the current version of the website.

 

We are in the process of rewriting the entire website and will use different approach to these data heavy queries but I dont except we will use less data so I suspect we are due for an upgrade either way. I also monitored disk IO operations I havent seen any obvious disk related bottlenecks but I thought maybe upgrading to SSDs would reduce the query times just enough so that we can get away not having to rewrite current back end.

 

Server specs:

2x X5687 (9 threads for MSSQL VM)

Unknown SAS HDDs (I have no access to hypervisor or the hardware)

48G RAM in total (20 assigned to MSSQL VM)

 

Any ideas?

[leadeater]

Have you made sure there is a table index that matches the query, this will massively increase performance if there isn't a relevant index associated with the query and will be an absolute killer on server resources and much of that disk.

 

Sorry for asking the dumb things but you do have maintenance plans setup to do Index Reconstructions and Rebuilds, Update Statistics etc?

[leadeater]

Also another suggestion would be to configure and enable Data Collection for a while, couple of days at least and analyze that data.

 

https://www.c-sharpcorner.com/article/sql-server-data-collection-and-management-data-warehouse/

https://www.sqlshack.com/sql-server-performance-monitoring-data-collector/

[Nick7]

First, you need to verify it's I/O bottleneck.

How is response time from drives? What I/O numbers you get (and how many spindles)?

How big is your data - is it possible it's already mostly in database buffer, so it's not even read from HDD during queries?

Do you have multiple queries, and have locking on DB - this can also cause longer times?

As leadeator said - did you check you are not missing any indexes?

 

When you are certain it's I/O bottleneck, and there is no other way to reduce I/O - in that case SSD's will help.

[Levent]

3 minutes ago, leadeater said:

Have you made sure there is a table index that matches the query, this will massively increase performance if there isn't a relevant index associated with the query and will be an absolute killer on server resources and much of that disk.

 

Sorry for asking the dumb things but you do have maintenance plans setup to do Index Reconstructions and Rebuilds, Update Statistics etc?

No worries I am new to managing systems this big (and windows). All of the tables have Indexes, I also rebuilt all the indexes just yesterday (they werent previously maintained at all) I also made changes to data heavy table's index as recommended by MSSQL Database Engine Tuning Advisor. I havent heard of Update Statistics, will look into it.

1 minute ago, leadeater said:

Also another suggestion would be to configure and enable Data Collection for a while, couple of days at least and analyze that data.

 

https://www.c-sharpcorner.com/article/sql-server-data-collection-and-management-data-warehouse/

https://www.sqlshack.com/sql-server-performance-monitoring-data-collector/

I will look into those right now but I do check SQL statistics on a regular basis and that was initially how I came to the conclusion of having issues with one data heavy table.

2 minutes ago, Nick7 said:

First, you need to verify it's I/O bottleneck.

How is response time from drives? What I/O numbers you get (and how many spindles)?

How big is your data - is it possible it's already mostly in database buffer, so it's not even read from HDD during queries?

Do you have multiple queries, and have locking on DB - this can also cause longer times?

When you are certain it's I/O bottleneck, and there is no other way to reduce I/O - in that case SSD's will help.

Database isnt that big, it is around 8gigs. You are right about buffer, DB might be completely in buffer. As far as locking, is there a decent way about diagonosing that? I did log Lock stats in my SQL traces for awhile but that made traces very noisy and I wasnt sure what to look for. This might not be IO bottleneck, you are right I also kept IO stats with performance monitor and disk barely reached 90mb reads and 40mb writes at the peak. I am not sure if it helps or not but this particulary querys response is around 100-150mb.

[leadeater]

43 minutes ago, Levent said:

Server specs:

2x X5687 (9 threads for MSSQL VM)

Unknown SAS HDDs (I have no access to hypervisor or the hardware)

48G RAM in total (20 assigned to MSSQL VM)

So this could well be a problem. What you have here is a VM that cannot be assigned both cores and memory to a single socket/NUMA domain and this can cause real performance problems. This is even more so the case if there are multiple VMs on the host.

 

Ask you VM engineers so pull some VM performance stats, things like CPU ready time, memory utilization per NUMA node (remote memory).

 

You should never create a VM larger than a single socket, this is a VM issue not a MSSQL issue with being able to deal with multiple sockets, which it can very well so long as it's a physical OS install not a VM. This applies to all database engines btw.

https://www.varonis.com/blog/sql-server-best-practices-in-virtualized-environments/

 

https://www.virten.net/vmware/esxtop/

Having data in the NRMEM column is bad, you don't want this.

 

%VMWAIT and %RDY should be as near to 0 as possible, never above 5%.

[leadeater]

7 minutes ago, Levent said:

I did log Lock stats in my SQL traces for awhile but that made traces very noisy and I wasnt sure what to look for. This might not be IO bottleneck, you are right I also kept IO stats with performance monitor and disk barely reached 90mb reads and 40mb writes at the peak.

The Data Collector will allow you to drill down to queries and view statistics on the query like time spent waiting on a lock etc. Also what were the disk queues and the file latencies on the database data file, log file, tempdb and templog? You might have low throughput but high access latencies which would make things slow.

 

My gut feeling at this point though is the above issue with the VM and host sizing, it's very much not ideal.

[Levent]

30 minutes ago, leadeater said:

The Data Collector will allow you to drill down to queries and view statistics on the query like time spent waiting on a lock etc. Also what were the disk queues and the file latencies on the database data file, log file, tempdb and templog? You might have low throughput but high access latencies which would make things slow.

 

My gut feeling at this point though is the above issue with the VM and host sizing, it's very much not ideal.

I asked for VM to be resized to 6 threads tonight, I will also start looking into Data Collector.

[leadeater]

1 hour ago, Levent said:

I asked for VM to be resized to 6 threads tonight, I will also start looking into Data Collector.

Just make sure the last 2 threads aren't getting assigned to socket 2, if they are then you'll need to do core pinning at the hypervisor level for those vCPUs and statically assign them to 6 threads on a single socket. Some hypervisors will preference real cores over HT threads and place vCPUs across sockets even if you don't want them to. If you need more CPU threads you can do up to 8 so long as you can keep them on that single socket.

 

Better to replace the host completely but if you can source some X5680/X5690 for cheap that would actually be a worthwhile upgrade, 6 cores per socket over 4 cores makes a surprisingly large difference.

[derr12]

13 hours ago, Levent said:

at it is the queries that are slowing it down, I also confirmed with tracing all SQL queries last 2 weeks and issue is queries to one table take around 100 times longer (CPU time isnt abnorm

 

in my experience,  going from spinning rust to ssd's in the same drive configuration made firebird database stuff about 10x faster.  mind you thats without touching anything else.  your situation may be different.