Menu
After doing my research, on how to speed up queries executed in SQL server, the majority of resources suggesting to reduce logical reads, by using the proper where clause. What I am really interested in is to know about the work flow in SQL server: when a stored procedure is being called by a request from an endpoint user or external system, and some tips on do's and don'ts'.
610
You can reduce the scan count by proper indexing for Orders and Ordershistory. You may be able also to restructure the query to use JOINs instead.
The I/O from an instance of the SQL Server Database Engine includes logical and physical reads. 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 first copies the page from disk into the cache.
Using the Entity Framework Profiler, I've received the suggestion to reduce the number of joins and, instead, perform several separate queries: link. Each join requires the database to perform additional work, and the complexity and cost of the query grows rapidly with each additional join.
Physical reads focus on transferring data from disk storage to memory in a data cache (sometimes called a data buffer pool). Logical reads refer to reads from the data cache. SQL Server builds an execution plan for data only after it is transferred from disk storage to the data cache.
From Microsoft SQL Server Documentation (Pages and Extents Architecture -> Reading Pages) has a good definition:
The I/O from an instance of the SQL Server Database Engine includes logical and physical reads. 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 first copies the page from disk into the cache.
So, a logical read is when the query engine needs to read data. First, it looks in memory. If the page is already in SQL Server's memory, then it uses that. If it can't find it in memory, then that triggers a physical read and the data page is read from disk. A logical read without a subsequent physical read is a "cache hit," basically.
The buffer cache (also known as the buffer pool) is SQL Server's primary working memory for solving queries. When you set the amount of memory that SQL Server will use, you're controlling the size of the available buffer cache.
However, telling you what you need to do without seeing the query or knowing what the table contains and what the data look like and how the data are indexed and organized is basically impossible.
Large numbers of logical reads may not necessarily be bad -- or, rather, not necessarily preventable. What's bad is an inordinate number of logical reads. If you're returning 3 rows of data, but the query engine had to scan 200 million rows of the table to do it, that's going to be very slow and you can probably improve that by rewriting the query or adding an index.
I would start by looking at how complex the queries in your stored procedure are. Notably, I'd look for missing indexes. If you're running SELECT * FROM BigTable WHERE ProductDate >= '01/01/2014', then I'd look to see that there was an index on ProductDate. If you're running SELECT * FROM BigTable ORDER BY ProductDate DESC, however, then, yes, an index will still help, but you'll still need to return the entire data set so you have to read the whole table anyways. Additionally, note that logical reads refer to page reads, so if the ProductDate in question is evenly distributed around the disk, you might need to read every page or nearly every page anyways.
Beyond that, it could be that the statistics on the table are out-of-date. If you've added 20,000 rows to a table and SQL Server still thinks there's only 2000 there, it's going to completely throw of the query planning.
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
