Cloud + Tuning = Money Saving, a big deal!

Hi Guys,

Welcome back!

Today only a small post for a small thought.

Today it is as if we were back to many years ago.

I remember the first years when (at least in Italy) the connection was not absolutely flat!

Every minute of internet browsing had a cost and it was not cheap.

After some time things changed a bit and for Browsing internet we have to pay for the amount of data downloaded.

Now with the cloud it’s a bit the same logic.

If you have a database on the cloud, the more data you read, the more you pay.

So that’s our "favorite weapon" to avoit to pay many money on the cloud? 

Well, the tuning! it is still really useful.

So in this case, we don’t just use tuning to make our queries faster and to eliminate or reduce locks and deadlocks.

We can and must also use tuning to reduce the number of data to read from the database. Which is particularly welcome if our database is on the cloud.

I will so a the simply example just to give to you a concept.

Example

Suppose we have a simple table named OrdTes

This table has only two fields.

An Id field of type integer, autoinc and a datedoc field of type datetime.

The id field is the primary key.

Now suppose we need to exctract all the rows where the datedoc field in in the year 2025

We can simply write the following statement:

SELECT * FROM ordtes WHERE YEAR(datedoc) = 2025

Since we have no index on the datedoc field SQL Server will scan all the OrdTes table reading all the rows:

The result is that we will read all the pages of data!

Table 'ordtes'. Scan count 1, logical reads 28, physical reads 0, page server reads 0, read-aheadreads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

In this case we read 28 data pages (each page is 8KB wide) but if our table has millions of rows it would be a problem.

Remember that it is a problem not only for the long run time (which depends on the number of records in the table). 

Keep in mind infact that the more data we read, the more likely it is that my select can read for example a blocked line or in transaction.

When did this question cost us? Right now we have read the whole table. This time we will pay in proportion to the 28 pages of data read.

Now we can think to add an index on the datedoc field! ...good idea!

Suppose we add the following index:

create index idx_ordtes_datdoc on ordtes(datedoc)

If now we execute again the previous statement we can see  that this time the new index is used but since the year function is not sargable the index is scanned (index scan). 

We does not read all the table but we read all the ....index!

Since the index is composed of only one field, what happens is that we read less data. It is not the optimal way to use an index. An index must be used in seek for the benefit it is designed for.

This time we have read 25 pages and si we will pay proportionally to this number of pages.

Table 'ordtes'. Scan count 1, logical reads 25, physical reads 0, page server reads 0, read-aheadreads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 

One step further!

This time we can think to modify our statement to make it sargable.

As said in other previous posts rarely the functions are sargable and in the case of the dates we have shown how to make them sargable. So just search older posts...

For example we can rewrite the statement as follows:

SELECT * FROM ordtes WHERE datedoc >= '2025/01/01' and datedoc <= '2025/12/31'

Ho many pages of data we will read?

Table 'ordtes'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-aheadreads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 

Wow... we will read only 3 pages of data and we will read the same amount of data regardless of the number of rows in our table.

Most important: We will pay the traffic of data on the cloud proportionally to 3 page instead of 28 so ..

....its a big big deal! ...Did I convince you?

That'all for today guys and stay tuned for the next posts

~Luke

Previous post: SQL Server 2022: Accelerated Database Recovery enhancements from SQL 2019 to SQL 2022