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?
We go through bunch of database material and found Database engine tuning advisor helpful to identify required database structure changes. It also suggests required indexes & statistics with expected enhancement rate.