Compare SQL query Performance statements

Compare SQL query Performance statements

--First Use set STATISTICS io and set STATISTICS time to see if performance tuning is valid.

--set STATISTICS time on

--SET STATISTICS time on

Before starting our example, run the following two commands ( do not execute on the server you are using ).
These two commands will clear the SQL Server data and process buffers so that we can be at the same starting point each time we execute the query, otherwise the result of each execution of the query is not comparable:
DBCC dropcleanbuffers
DBCC Freeproccache

SET STATISTICS time

SET STATISTICS time on

Select SUM (totalamount) Sumat,tenderno from if_middle_test--your sql

SET STATISTICS Time OFF

Results comparison:
--CPU time = 1560 milliseconds, elapsed time = 7229 milliseconds.
--todaydateindex:cpu time = 1435 milliseconds, occupied time = 10340 milliseconds.
--shonoindex:cpu time = 1606 milliseconds, occupied time = 7918 milliseconds.
--***both index***: CPU time = 1295 milliseconds, occupied time = 2624 milliseconds.

Because CPU time is relatively stable, you can use this data as a way to measure whether your throttling measures improve query performance or reduce query performance.

SET STATISTICS IO

The output information for SET STATISTICS io is displayed at the end of the output, and here is an example of what it shows:

Table ' Order Details '. Scan count 1, logical reads, physical reads 1, Read-ahead reads 9.

Scan Count: The number of times the table that is involved in the query is accessed. In our case, the table is accessed only 1 times, and this information is not very useful because the connection command is not included in the query, but this information is useful if the query contains one or more connections.

Logical Reads: This is the most useful data provided by the set STATISTICS IO or set STATISTICS time command. We know that SQL Server must first read the data into its data buffer before it can manipulate any data. In addition, we know when SQL Server reads data from the data buffer and reads the data into a page of size 8K bytes.
So what is the meaning of logical reads? Logical reads refers to the number of pages that SQL Server must read from the data buffer to get the results in the query. When executing a query, SQL Server does not read more or less data than is actually required, so when executing the same query on the same dataset, the resulting logical reads numbers are always the same.
Why is it important to know that the logical reads value of SQL Server when executing a query in tuning query performance? Because this number does not change each time the same query is executed. So, when it comes to tuning query performance, this is a good standard to measure the success of your adjustment measures.
When tuning the performance of a query, if the logical reads value drops, it indicates that the query uses fewer server resources and the query has improved performance. If the logical reads value is increased, the throttling action reduces the performance of the query. In the case of other conditions, the less logical reading a query uses, the more efficient it is, and the faster the query will become.

Physical Reads:
Physical reading means that SQL Server must read the data it needs from disk to the data buffer before performing a real query operation. Before SQL Server starts executing a query, the first thing it does is to check if the data it needs is in the data buffer, if it reads from it, and if not, SQL Server must first read the data it needs from the disk into the data buffer.
As we can imagine, SQL Server requires more server resources to perform a physical read than to perform a logical read. Therefore, in an ideal situation, we should try to avoid physical reading operations.

Indeed, the physical reads that SQL Server needs to perform queries are not likely to be reduced by performance tuning. reducing the number of physical reads is an important task for DBAs, but it involves tuning the performance of the entire server, not just the performance of the query. when performing query performance tuning, we cannot control the size of the data buffer or how busy the server is, whether the data required to complete the query is in the data buffer or on disk, and the only data that we can control is the number of logical reads that need to be performed to get the results of the query.

Summary:

It is important to measure the effectiveness of your adjustment measures with some scientific criteria when adjusting the performance of a query. The problem is that the load on SQL servers is dynamically changing , and it is not a reasonable approach to use the total run time of the query to measure whether the performance of the query you are tuning is improving or not .

A better approach is to compare multiple data, such as the number of logical reads or the CPU time used by the query. Therefore, when tuning the performance of a query, you need to first use the set STATISTICS io and set STATISTICS time commands to provide you with the necessary data to determine whether the measures you have tuned to the performance of the query have really got the purpose.

Other Related:
SET STATISTICS IO on
Set STATISTICS PROFILE on
SET STATISTICS IO off
Set STATISTICS profile off