SQL Server Large Data volume paging proposal scenario

In short, that's it.

Select Top * FROM (

SELECT *, rowID = Row_number () over (order by XXX) from TB with (NOLOCK)

) data where rowID > 0

ORDER BY rowID

or write like this.

SELECT * FROM (

SELECT *, rowID = Row_number () over (order by XXX) from TB with (NOLOCK)

) data where rowID > 0 and rowID <= 20

Previously stressed, for such queries, must use the following way (or similar, mainly to first remove the primary key to meet the conditions), but from this message, we still use the previous old method

--Recommended method:

SELECT * from TB with (NOLOCK)

Where primary key in (

Select Primary Key from (

Select Primary KEY, rowID = Row_number () over (order by XXX) from TB with (NOLOCK)

) data where rowID > 0 and rowID <= 20

)

The previous emphasis on this is performance, because the old method, the data is taken at the same time in the Row_number, that is, before paging, actually take all the data, but eventually only return a part of it

Now the newly discovered problem is:

When the table contains some fields of a larger length, such as XML, Varchar/nvarchar (max), or thousands of of the length (such as the number of the table in the message is 8000)

Memory overhead evaluation can become very scary, the memory allocated for the query will reach a very large value, such as this query, the allocation of memory is 4.4G, our server is 64G memory, a few 10 of such queries will cause the server because there is no memory available to respond to subsequent requests

So please pay special attention to this attachment in the query, arrange the relevant personnel adjustment

The attachment has one that was previously adjusted, that's more scary, press 10G of memory

reducing the memory overhead is primarily ORDER by , including Row_number this has ORDER by when using the rank function of an operation, try to exclude irrelevant columns from the columns in the query, especially XML, Nvarchar/varchar This long column (whether or not the columns contain data, should be avoided as long as they are defined; Memory evaluation is based on the structure, not the actual stored data)

SQL Server Large Data volume paging proposal scenario