SQL Server Provider - Read Rating. What is considered “good” or “bad”?

Question

I am profiling (SQL Server 2008) some of our views and queries to determine their effectiveness with respect to CPU usage and reading. I understand that Reads is the number of logical disks read on 8K pages. But I find it difficult to determine what I should be satisfied with.

For example, when I request one of our views, which in turn joins another view and has three OUTER APPLYs with UDF table values, I get a Reads value of 321 with a CPU value of 0. My first thought is that I should be satisfied with this . But how can I evaluate the value of 321? This tells me that 2,654,208 bytes of data were logically read to satisfy the request (which returned one row and 30 columns).

How would some of you decide if this is sufficient or requires more fine-tuning? What criteria would you use?

Also, I'm curious what is included in the reading of logical data at 2654208 bytes. Does this include all the data contained in 30 columns in one row?

Answer 1

2.5MB includes all data on 321 pages, including other rows on the same pages as those received for your request, as well as index pages obtained to search for your data. Note that these are logical reads, not physical reads, for example. reading from a cached page will make reading "cheaper" - during optimization, you can also get an indicator of the cost of the processor and profiler.

wrt How to determine the optimal "target" for reading.

FWIW I compare the actual data with the optimal value, which I can present as the minimum number of pages needed to return data to your request in an "ideal" world.

eg. if you calculate about 5 rows per page from table x, and your query returns 20 rows, the “ideal” number of reads will be 4, as well as some overhead for navigation indexes (assuming, of course, that the rows are grouped “perfectly” for your query) so utopia will be approximately 5-10 pages.

For a critical performance request, you can use the actual vs vs utopian read for micro-optimization, for example:

• Is it possible to place more rows on a page in a cluster (table), for example. replacing unexplored strings with varchar () not char or using varchar rather than nvarchar () or using smaller integer types, etc.
• Is it possible to change the clustered index so that it is necessary to extract fewer pages (for example, if 20 rows for the above query were scattered across different pages, then the reading will be> 4)
• Otherwise (since you can use only one CI), can covering indexes replace the need to switch to tabular data (cluster) in general, since covering indexes matching your query will have a higher density of "rows"
• And for indexes, density improvements, such as fillfactors or narrower indexing for indexes, may mean fewer indexes to read

You may find this article helpful.

NTN!

Answer 2

321 reading with a CPU value of 0 sounds pretty good, but it all depends.

How often is this query executed? Why are table return UDFs used instead of UDF? What is the context of database usage (how many users, number of transactions per second, database size, is it OLTP or data storage)?

Additional data is read:

• All other data on the pages needed to complete a read made in the execution plan. Note that these are clustered and non-clustered indexes. Studying the execution plan will give you a better idea of ​​what is being read. You will see links to all kinds of indexes and tables and the need to search or scan. Note that validation means that every page in the entire index or table has been read. This is why queries are desirable for scanning.

• All the related data in the INNER JOINed tables in the views, regardless of whether these JOINs are needed to get the right results for your query, because the optimizer does not know that these INNER JOINs will or will not exclude / include rows until they will join them.

If you provide requests and implementation plans, as requested, I can probably give you the best advice. Since you are using UDF table values, I will also need to see the UDF itself, or at least the UDF execution plan (which is possible only by tearing out its meat and running functions out of context or converting it into a stored procedure).