Identify objects which requires partitioning

Question

We are having an enterprise application which runs with microsoft sql server database.

Currently we are experiencing lot of performance issues with the same for which one of the reason we found is extreme usage of memory(large data loaded in bufferpool). As i mentioned large data, our application running with +- 30 million data, few historic data used rarely. One of the approach I found to overcome this problem is Sql Server table partitioning, learned how to apply the same using Enterprise edition but cannot found resources to find out pain points.

Can anyone please guide me, How to find objects which requires table partitioning? How to find parameters/columns based on which partitions should be define?

Answer 1

The literature about SQL Server Partitioning mentions various benefits that you should expect from partitioning, but reduced memory usage is not one of them. You speak of "extreme usage of memory" and you say "one of the approach I found to overcome this problem is Sql Server table partitioning", but I have not been able verify this claim.

The most common kind of partitioning is horizontal partitioning, where you divide a table into groups of rows. With this kind of partitioning, if you really know what you are doing, (if you structure things in such a way as to keep the vast majority of your queries hitting only one of the partitions,) you should be able to expect no net increase or decrease in memory consumption. But if your queries keep hitting multiple partitions, then your server might experience an increase in the number of rows it needs to keep cached.

The other kind of partitioning is vertical partitioning, where you divide a table into groups of columns. With this kind of partitioning, you may experience an improvement in memory usage, but only to the extent that SQL Server is dumb enough to keep unused fields in memory, so by moving unused columns into a separate partition you would theoretically prevent these unused fields from ever being loaded into memory. However, I would assume that SQL Server is not so dumb as to keep unused fields in memory, so by vertically partitioning a table I would expect you to gain no more memory than you would gain by replacing each SELECT * (100 columns) with a SELECT col_1, col_5, col_17. (Only the columns you need.)

Furthermore, if we were to take it for granted that partitioning is the solution to whatever problem you are having, the literature about SQL Server Partitioning does not appear to mention any kind of generally applicable methodology or tools that you could use to determine where and how you should partition your tables: partitioning decisions seem to be based on careful consideration of the structure of the database schema, the numbers of rows and columns in various tables, and good knowledge of how the data are utilized in day to day operations.

So, for example, if you have sales rows from previous years that are rarely accessed, you may want to partition your sales table based on years, so that only the partition of the current year will be seeing much action. This decision is the result of deep knowledge and careful reasoning about the structure of the data set and the actual usage of the data.

So, if you had posted your database schema, the row count of each table, and a full essay describing how your tables are expected to be used, then one could theoretically give a reasonable suggestion as to how to partition your database. But since you did not give any of that information, you are essentially asking for a generally applicable methodology for finding objects requiring partitions and partitioning columns, and the answer to this is that there is no such generally applicable methodology.