The SQL Server partitioning literature mentions various benefits to be expected from partitioning, but reducing memory usage is not one of them. You talk about “extreme memory usage,” and you say, “One approach I have found to overcome this problem is partitioning into Sql Server tables,” but I could not verify this statement.
The most common type of splitting is horizontal partitioning, where you break a table into groups of rows. With this partitioning, if you really know what you are doing (if you structure things in such a way that the vast majority of your queries fall into only one of the sections), you should expect that there is no net increase or decrease in memory consumption. But if your requests hit several sections, your server may face an increase in the number of lines that need to be stored in the cache.
Another type of partitioning is vertical partitioning, where you divide a table into groups of columns. With this partitioning, you may encounter an improvement in memory usage, but only to the extent that SQL Server is dumb enough to store unused fields in memory, so by moving unused columns to a separate section, you would theoretically prevent these unused fields from either loaded into memory. However, I would suggest that SQL Server is not so stupid as to save unused fields in memory, so by vertically partitioning the table I expect you to get more memory than you would win by replacing each SELECT * (100 columns) on a SELECT col_1, col_5, col_17 . (Only the columns you need.)
Also, if we should take it for granted that partitioning is the solution to any problem you encounter, the SQL Server partitioning literature does not mention any generally applicable methodology or tools that could be used to determine where and how you should break up their tables: partitioning decisions are apparently based on a careful consideration of the structure of the database schema, the number of rows and columns in different tables, and a good knowledge of how data is used in everyday operations operations
So, for example, if you have sales lines from previous years that you rarely refer to, you can split your sales table into years, so only the current year section will see a lot of actions. This decision is the result of deep knowledge and careful analysis of the structure of the data set and the actual use of the data.
So, if you placed the schema of your database, the number of rows in each table, and a complete essay describing how your tables will be used, then theoretically you can offer a reasonable suggestion on how to partition your database. But since you did not provide any information, you basically ask for a generally applicable methodology for finding objects that require sections and column sections, and the answer to this question is that there is no such generally applicable methodology.