How is SQL Sever able to improve the performance using partitioned tables in a SAN storage environment?



  • Link: https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver15#:%7E:text=SQL%20Server%20supports%20table%20and,are%20mapped%20into%20individual%20partitions .

    SQL Server supports table and index partitioning. The data of partitioned tables and indexes is divided into units that may optionally be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions.

    The benefit of partitions comes from the fact that each partition can be placed on a separate disk, there by allowing for parallel disk access.

    However in a SAN storage environment is there any benefit of partitioning because all files are scattered amongst SAN disks.



  • The benefit of partitions comes from the fact that each partition can be placed on a separate disk, there by allowing for parallel disk access.

    Not at all. You can spread a single filegroup across multiple disks without partitioning. And partitioning using separate disks tends to reduce parallel disk access, as queries are often looking for data in a single partition.

    The main performance benefit of partitioning are through partition elimination in queries, and rolling-window partition pruning for removing data from very large tables.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2