This is an excellent example of what you can get by teaming up with our part-time SQL DBA Service provider.
Our daily checks include many aspects of SQL Server and some aspects of hardware, such as CPU, memory, disk space, etc.
This is a proactive action to prevent possible issues and problems that may cause service disruption or downtime.
It is like having a firefighter putting out the fire before it becomes a forest fire.
During our daily monitoring, we got a query that was returning 90k rows.
It was also on the top worst statement of the client`s database.
Here is the 30 days report. It shows executions and waits for that view.
As you can see, the query was using a view which sometimes was executed 750 times per day.
What have we done?
The report was sent to the client. We also have made some suggestions to modify the main to limit the number of rows reducing the I/o consumption.
Most SQL Servers bottleneck is on Disk access (or disk “reads”).
It’s not CPU or RAM – which most customers often suspect first.
And that makes much sense. Here is why.
Inefficient queries scan (or read) much data. Data read in is stored in RAM. As more data is read in, “older” data is pushed out from RAM.
If there isn’t enough RAM to keep ALL data in memory (which is often not possible), SQL Server has to read from disk – and that is the slowest operation SQL Server can do.
When the query can be tuned to read 10 rows vs. 10M – less CPU and RAM automatically are necessary.
Therefore, tuning for less disk “reads” is often the primary goal.
To the end-user, nothing is more important than Speed (or Duration of the query), in any case.
Tuning to reduce CPU/RAM resources is helpful too.
When queries are tuned to need less CPU & RAM, it means that the same server now has more capacity.