Performance issue

Question

Experts ,

If I have a SQL Server 2019 having high physical reads , can it be said that it is a performance issue or do we need to look into some other parameters

Answer 1

Hi @Vishu ,

As Erland mentioned, did any one complaint SQL server performance issue? If not, you do not have performance issue.

The I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory.

If you find performance problems when tuning a single statement and only appear when there is physical I/O, then there are a few things you need to do.

(1) Check whether the production server has a memory bottleneck, and whether there is a phenomenon of frequent page changes.

If there is no memory bottleneck in the production environment, or there are few Page Out/Page In actions, it means that SQL Server can maintain data pages in memory, and the performance problems you see are unlikely to occur, so there is no need to go too far. concern.

If there is an obvious memory bottleneck, the first thing to solve is the memory bottleneck at the SQL Server server level, because this bottleneck will not only cause this statement to run slowly, but also affect the performance of other statements.

(2) Check whether this statement and the data it accesses is used frequently or occasionally.

If the problem statement is often called by different users, then the data it accesses should be cached in memory. If this sentence is only occasionally called by a specific user or task, and the amount of data it accesses is large and other people do not use it frequently, then it is normal for SQL Server not to put them in memory. For this Statement, it is reasonable to have physical I/O time in its running time. This may involve communicating with end users, setting their reasonable performance expectations, and arranging them to run during non-business peak hours, so as not to affect other key businesses.

(3) Check whether the statement execution plan can reduce the amount of data accessed.

The smaller the amount of data accessed by the statement, the less physical I/O to be done, and of course the faster it can be. From this perspective, it is also useful to tune the statement itself.

(4) Check the performance of the disk subsystem.

If the data accessed by the statement is probably not in the memory, and its amount is large, and its performance must be improved, the only way out is to improve the performance of the disk subsystem. In this case, the internal settings of SQL Server may help Nothing is busy, the work to be done may be more at the hardware level.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

Answer 2

Well, again, are users screaming and kicking?

High physical reads does not sound good, but if no one is complaining, you don't really have an issue.

How to address high physical reads? There two ways to do: 1) add more memory until reads are gone. 2) Tune slow queries and scan big tables.